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
*/