你的位置:首页 > 数据库

[数据库]Excel Metadata


using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.OleDb;using System.Collections;/*当 IMEX=0 时为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。当 IMEX=1 时为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。当 IMEX=2 时为“连結模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。*//*列类型	经典 ADO	ADO.Net (OleDb)数字	5-adDouble	5-OleDbType.Double货币	6-adCurrency	6-OleDbType.Currency日期/时间	7-adDate	7-OleDbType.Date布尔值	11-adBoolean	11-OleDbType.Boolean文本 < 255	202-adVarWChar	130-OleDbType.WChar备注	203-adLongVarWChar	130-OleDbType.WChar */namespace ExcelGenerator{  /// <summary>  /// http://support.microsoft.com/zh-cn/kb/318452  /// http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled  /// 20150325  /// 涂聚文  /// </summary>  public partial class ExcelFileForm : Form  {    //"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\test.xls;Extended Properties=Excel 8.0";    //Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties = "Excel 12.0 Macro;HDR=YES";    //Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;Extended Properties="Excel 12.0;HDR=YES";    //Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;Extended Properties = "Excel 8.0;HDR=YES";    //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls; Extended Properties = "Excel 8.0;HDR=Yes;IMEX=1";    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\test.xls;Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;";    private OleDbConnection cn;        private DataTable dtTables;    private CurrencyManager cm;    private DataTable dtColumns;    private DataView dvColumns;    string fileurl = string.Empty;    /// <summary>    /// GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new Object[]{null,null, strTable, null});    /// </summary>    /// <returns></returns>    private DataTable setTable()    {      DataTable dt = new DataTable();      dt.Columns.Add("id",typeof(int));      dt.Columns.Add("name", typeof(string));      dt.Rows.Add(1, "tables");//工作表      dt.Rows.Add(2, "Columns");      //dt.Rows.Add(3, "");      //dt.Rows.Add(4, "");      //dt.Rows.Add(5, "");      //dt.Rows.Add(6, "");      return dt;    }    /// <summary>    /// /    /// </summary>    public ExcelFileForm()    {      InitializeComponent();    }    /// <summary>    ///     /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void ExcelFileForm_Load(object sender, EventArgs e)    {      this.comboBox1.DataSource = setTable();      this.comboBox1.DisplayMember = "name";      this.comboBox1.ValueMember = "id";    }    /// <summary>    ///     /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void button1_Click(object sender, EventArgs e)    {      openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);      //JPEG Files (*.jpeg)|*.jpeg|PNG Files (*.png)|*.png|JPG Files (*.jpg)|*.jpg|GIF Files (*.gif)|*.gif      openFileDialog1.FileName = "";      openFileDialog1.Filter = "Excel 2000-2003 files(*.xls)|*.xls|Excel 2007 files (*.xlsx)|*.xlsx";//|(*.xlsx)|*.xlsx Image Files(*.BMP;*.JPG;*.GIF)|*.BMP;*.JPG;*.GIF|All files (*.*)|*.* txt files (*.txt)|*.txt|All files (*.*)|*.*"       openFileDialog1.FilterIndex = 1;      openFileDialog1.RestoreDirectory = true;      if (openFileDialog1.ShowDialog() == DialogResult.OK)      {        if (!openFileDialog1.FileName.Equals(String.Empty))        {          connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=Excel 8.0;";          fileurl = openFileDialog1.FileName;          this.textBox1.Text = openFileDialog1.FileName;        }      }    }    /// <summary>    /// TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_GUID,DESCRIPTION,TABLE_PROPID,DATE_CREATED,DATE_MODIFIED    /// TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_GUID,COLUMN_PROPID,ORDINAL_POSITION,COLUMN_HASDEFAULT,COLUMN_DEFAULT,COLUMN_FLAGS,IS_NULLABLE,DATA_TYPE,TYPE_GUID,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_CATALOG,CHARACTER_SET_SCHEMA,CHARACTER_SET_NAME,COLLATION_CATALOG,COLLATION_SCHEMA,COLLATION_NAME,DOMAIN_CATALOG,DOMAIN_SCHEMA,DOMAIN_NAME,DESCRIPTION    /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void button2_Click(object sender, EventArgs e)    {      System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection(connectionString);      ExcelConnection.Open();      //dtTables = ExcelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });      //dtTables = ExcelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, null, "Sheet1$" });      //dtTables = ExcelConnection.GetSchema("tables");      //this.dataGridView1.DataSource = dtTables;      //ExcelConnection.Close();      //this.textBox2.Text = GetColumnNames(dtTables);      GetTablesList();      this.dataGridView2.DataSource= ReadExcelFile().Tables[0];    }    /// <summary>    ///    /// </summary>    /// <param name="table"></param>    /// <returns></returns>    public static string GetColumnNames(System.Data.DataTable table)    {      if (table != null)      {        List<string> lstColumn = new List<string>();        foreach (System.Data.DataColumn col in table.Columns)        {          lstColumn.Add(col.ColumnName);        }        return String.Join(",", lstColumn.ToArray());      }      return string.Empty;      //foreach (DataRow row in table.Rows)      //{      //  foreach (DataColumn column in table.Columns)      //  {      //    ColumnName = column.ColumnName;      //    ColumnData = row[column].ToString();      //  }      //}    }    /// <summary>    ///     /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)    {    }    /// <summary>    ///     /// </summary>    private void GetTablesList()    {      try      {        cn = new OleDbConnection(connectionString);        cn.Open();        dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });        this.dataGridView3.DataSource = dtTables;        //dataGridView1.ReadOnly = true;        cn.Close();      }      catch (System.Data.OleDb.OleDbException myException)      {        for (int i = 0; i < myException.Errors.Count; i++)        {          MessageBox.Show("Index #" + i + "\n" +          "Message: " + myException.Errors[i].Message + "\n" +          "Native: " +    myException.Errors[i].NativeError.ToString() + "\n" +          "Source: " + myException.Errors[i].Source + "\n" +          "SQL: " + myException.Errors[i].SQLState + "\n");        }      }      GetColumnsList();    }    /// <summary>    ///     /// </summary>    private void GetColumnsList()    {      try      {        if (cm == null)          cm = (CurrencyManager)this.BindingContext[dtTables];        cm.PositionChanged += new EventHandler(cm_PositionChanged);        int r = cm.Position;        String strTable = dtTables.Rows[r]["TABLE_NAME"].ToString();        cn = new OleDbConnection(connectionString);        cn.Open();        dtColumns = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, strTable, null });//字段名        dvColumns = new DataView(dtColumns);        dvColumns.Sort = "ORDINAL_POSITION";        dataGridView1.DataSource = dvColumns;        //dataGridView1.ReadOnly = true;        cn.Close();        this.textBox2.Text = GetColumnNames(dtColumns);      }      catch (Exception ex)      {        ex.Message.ToString();      }    }    /// <summary>    ///     /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void cm_PositionChanged(object sender, System.EventArgs e)    {      GetColumnsList();    }    private void comboBox1_SelectedValueChanged(object sender, EventArgs e)    {          }    /// <summary>    ///     /// </summary>    /// <param name="dt2format"></param>    private void FormatTablesGrid(DataTable dt2format)    {      DataGridTableStyle gs = new DataGridTableStyle();      gs.MappingName = dt2format.TableName;      DataGridColumnStyle cs = new DataGridTextBoxColumn();      cs.MappingName = "TABLE_NAME";      cs.HeaderText = "Table Name";      cs.Width = 75;      gs.GridColumnStyles.Add(cs);      cs = new DataGridTextBoxColumn();      cs.MappingName = "TABLE_TYPE";      cs.HeaderText = "Table Type";      cs.Width = 75;      gs.GridColumnStyles.Add(cs);      CurrencyManager cm = (CurrencyManager)this.BindingContext[dt2format];      PropertyDescriptor pd = cm.GetItemProperties()["DATE_CREATED"];      cs = new DataGridTextBoxColumn(pd, "d");      cs.MappingName = "DATE_CREATED";      cs.HeaderText = "Date Created";      cs.Width = 75;      gs.GridColumnStyles.Add(cs);      cm = (CurrencyManager)this.BindingContext[dt2format];      pd = cm.GetItemProperties()["DATE_MODIFIED"];      cs = new DataGridTextBoxColumn(pd, "d");      cs.MappingName = "DATE_MODIFIED";      cs.HeaderText = "Date Modified";      cs.Width = 75;      gs.GridColumnStyles.Add(cs);      //dataGridView1.TabIndex.Add(gs);      button2.Enabled = false;    }    /// <summary>    /// http://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB    /// </summary>    /// <returns></returns>    private string GetConnectionString()    {      Dictionary<string, string> props = new Dictionary<string, string>();      // XLSX - Excel 2007, 2010, 2012, 2013      //props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";      //props["Extended Properties"] = "=Excel 12.0