• 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;
    using System.IO;
    
    /*
    当 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:myFoldermyExcel2007file.xlsm;Extended Properties = "Excel 12.0 Macro;HDR=YES";
            //Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:myFoldermyBinaryExcel2007file.xlsb;Extended Properties="Excel 12.0;HDR=YES";
            //Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:myFoldermyOldExcelFile.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;
    
                        string extension = Path.GetExtension(fileurl);  //GetFileNameWithoutExtension
                        this.textBox1.Text = openFileDialog1.FileName;
                        if (extension == ".xls") //extension==".xls";
                        {
                            connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source=" + fileurl + ";" +
                            @"Extended Properties=" + ""Excel 8.0;HDR=YES;"";
                        }
                        else if (extension==".xlsx")//extension==".xlsx";
                        {
                            connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                            "Data Source=" + fileurl + ";" +
                            @"Extended Properties=" + ""Excel 12.0;HDR=YES;"";
                        }
                    }
                }
            }
            /// <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 + "
    " +
                        "Message: " + myException.Errors[i].Message + "
    " +
                        "Native: " +
            myException.Errors[i].NativeError.ToString() + "
    " +
                        "Source: " + myException.Errors[i].Source + "
    " +
                        "SQL: " + myException.Errors[i].SQLState + "
    ");
                    }
                }
    
                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 XML;"; //
                //props["Data Source"] = fileurl;
    
                // XLS - Excel 2003 and Older
                //props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
                //props["Extended Properties"] = "Excel 8.0;";//HDR=Yes;IMEX=1
                //props["Data Source"] = fileurl;
                //fileurl = this.textBox1.Text.Trim();
                string extension = Path.GetExtension(fileurl);
                if (extension == ".xls") //extension==".xls";
                {
                    connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + fileurl + ";" +
                    @"Extended Properties=" + ""Excel 8.0;HDR=YES;"";
    
                    // XLS - Excel 2003 and Older
                    props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
                    props["Extended Properties"] = "Excel 8.0";//HDR=Yes;IMEX=1
                    props["Data Source"] = fileurl;
                }
                else if (extension == ".xlsx")//extension==".xlsx";
                {
                    connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                    "Data Source=" + fileurl + ";" +
                    @"Extended Properties=" + ""Excel 12.0;HDR=YES;"";
                    // XLSX - Excel 2007, 2010, 2012, 2013
                    props["Provider"] = "Microsoft.ACE.OLEDB.12.0";
                    props["Data Source"] = fileurl;
                    props["Extended Properties"] = "Excel 12.0 XML"; //Excel 12.0 XML
                    
                }
    
                StringBuilder sb = new StringBuilder();
    
                foreach (KeyValuePair<string, string> prop in props)
                {
                    sb.Append(prop.Key);
                    sb.Append('=');
                    sb.Append(prop.Value);
                    sb.Append(';');
                }
    
                return sb.ToString();
            }
            /// <summary>
            /// 创建工作表table1
            /// </summary>
            private void WriteExcelFile()
            {
                string connectionString = GetConnectionString();
    
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;
    
                    cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";//创建工作表table1 及表头
                    cmd.ExecuteNonQuery();
    
                    cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
                    cmd.ExecuteNonQuery();
    
                    cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');";
                    cmd.ExecuteNonQuery();
    
                    cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');";
                    cmd.ExecuteNonQuery();
    
                    cmd.CommandText = "UPDATE [table1] SET name = 'DDDD' WHERE id = 3;";
                    cmd.ExecuteNonQuery();
    
                    conn.Close();
                }
            }
            /// <summary>
            /// 读工作表
            /// </summary>
            /// <returns></returns>
            private DataSet ReadExcelFile()
            {
                DataSet ds = new DataSet();
    
                string connectionString = GetConnectionString();
    
                try
                {
                    using (OleDbConnection conn = new OleDbConnection(connectionString))
                    {
                        conn.Open();
                        OleDbCommand cmd = new OleDbCommand();
                        cmd.Connection = conn;
    
                        // Get all Sheets in Excel File
                        DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    
                        // Loop through all Sheets to get data
                        foreach (DataRow dr in dtSheet.Rows)
                        {
                            string sheetName = dr["TABLE_NAME"].ToString();
    
                            if (!sheetName.EndsWith("$"))
                                continue;
    
                            // Get all rows from the Sheet
                            cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
    
                            DataTable dt = new DataTable();
                            dt.TableName = sheetName;
    
                            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                            da.Fill(dt);
    
                            ds.Tables.Add(dt);
                        }
    
                        cmd = null;
                        conn.Close();
                    }
    
                }
                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
                return ds;
            }
            /// <summary>
            /// 添加一个新工作表
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void button3_Click(object sender, EventArgs e)
            {
                WriteExcelFile();
            }
            /// <summary>
            /// 查询值
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void button4_Click(object sender, EventArgs e)
            {
                //create a string for the query
    
                string ExcelQuery;
    
                //Sheet1 is the sheet name
                //create the query:
                //read column with heading A from the Excel file
                ArrayList ad = new ArrayList();
                ExcelQuery = "SELECT * FROM [Sheet1$] WHERE 序号=5"; // from Sheet1";         
                DataSet ds = new DataSet();
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    //1.
                    OleDbCommand cmd = new OleDbCommand(ExcelQuery, conn);
                    conn.Open();
                    OleDbDataReader myReader = cmd.ExecuteReader();
                    if (myReader.Read())
                    {
                        ad.Add(myReader[0].ToString()+","+myReader[1].ToString()+","+myReader[2].ToString()+","+myReader[3].ToString()+","+myReader[4].ToString());
                    }
                     this.textBox2.Text= String.Join(",", ad.ToArray());
    
    
    
                    //2.
                    //OleDbDataAdapter command = new OleDbDataAdapter(ExcelQuery, connectionString);
                    //command.Fill(ds, "ds");
                    //dataGridView1.DataSource = ds.Tables[0];
                }
                
            }
    
        }
       
    }
    

      数据类型:

    Short System.Int16 2
    Long System.Int32 3
    Single System.Single 4
    Double System.Double 5
    Currency System.Decimal 6
    DateTime System.DateTime 7
    Bit System.Boolean 11
    Byte System.Byte 17
    GUID System.Guid 72
    BigBinary System.Byte[] 204
    LongBinary System.Byte[] 205
    VarBinary System.Byte[] 204
    LongText System.String 203
    VarChar System.String 202
    Decimal System.Decimal 131

    /*
    MetaDataCollections
    DataSourceInformation
    DataTypes
    Restrictions
    ReservedWords
    Columns
    Indexes
    Procedures
    Tables
    Views
    */

  • 相关阅读:
    求100内的数和
    汉诺塔(印度传说)
    ORA-06502:PL/SQL:数字或值错误:数值精度太高
    触发器的编写
    mysql改密码
    LeetCode-238 Product of Array Except Self
    LeetCode-236 Lowest Common Ancestor of a Binary Tree
    LeetCode-233 Number of Digit One
    LeetCode-230 Kth Smallest Element in a BST
    LeetCode-229 Majority Element II
  • 原文地址:https://www.cnblogs.com/geovindu/p/4366012.html
Copyright © 2020-2023  润新知