涂聚文--信息智能化学习者,豆腐渣工程投诉者

Excel Metadata

2015-3-25 16:09:11 人评论 次浏览

: nothing at of , which is

分类:Access

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 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;

            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();

            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();
            }

            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

            ExcelQuery = "SELECT * FROM [Sheet1$] WHERE 序号=5"; // from Sheet1";         
            DataSet ds = new DataSet();
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                //OleDbCommand cmd = new OleDbCommand();
                //cmd.Connection = conn;
                //cmd.CommandText = ExcelQuery;//创建工作表table1 及表头
                //cmd.ExecuteNonQuery();

                OleDbDataAdapter command = new OleDbDataAdapter(ExcelQuery, connectionString);
                command.Fill(ds, "ds");
                
            }
            dataGridView1.DataSource = ds.Tables[0];
        }

    }
   
}