源代码下载地址:http://download.csdn.net/source/1883223 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.IO; namespace ImportDataFormExcelDemo { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { //获取 Excel 中的数据并显示到 dataGridView 控件中 //Excel 中包含的字段 //CustomerKey FirstName LastName EmailAddress AddressLine //示例的 excel 在 bin/debug 目录下 string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ImportData.xls"); DataTable importData = this.GetDataFromExcel(fileName); this.dataGridView1.DataSource = importData; } private void button2_Click(object sender, EventArgs e) { //获取 excel 中的数据并填充到指定结构的 DataTable 中。 //如果字段在 excel 中不存在默认以 Null 填充。 //Excel 中包含的字段 //CustomerKey FirstName LastName EmailAddress AddressLine //定义一个新表包含字段 //FirstName LastName EmailAddress AddressLine Phone Birthday //示例的 excel 在 bin/debug 目录下 string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ImportData.xls"); DataTable sourceData = new DataTable(); sourceData.Columns.AddRange(new DataColumn[] { new DataColumn("FirstName", typeof(String)), new DataColumn("LastName", typeof(String)), new DataColumn("EmailAddress", typeof(String)), new DataColumn("AddressLine", typeof(String)), new DataColumn("Phone", typeof(String)), new DataColumn("Birthday", typeof(String)) }); sourceData.Columns[4].AllowDBNull = false; DataTable importData = this.GetDataFromExcel(fileName); foreach (DataRow importRow in importData.Rows) { DataRow newRow = sourceData.NewRow(); foreach (DataColumn c in sourceData.Columns) { if (importData.Columns.Contains(c.ColumnName) && importData.Columns[c.ColumnName].DataType.Equals(c.DataType)) { newRow[c.ColumnName] = importRow[c.ColumnName]; } else { c.AllowDBNull |= true; newRow[c.ColumnName] = DBNull.Value; } } sourceData.Rows.Add(newRow); } this.dataGridView1.DataSource = sourceData; } /// <summary> /// 获取 Excel 文件中指定索引的工作表名称 /// </summary> /// <param name="fileName">Excel 的文件名</param> /// <param name="sheetIndex">要获取的索引</param> /// <returns></returns> private String GetExcelSheetNameByIndex(String fileName, int sheetIndex) { string connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1/";", fileName); using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); return schemaTable.Rows.Count > sheetIndex ? schemaTable.Rows[sheetIndex]["TABLE_NAME"].ToString() : String.Empty; } } /// <summary> /// 获取指定 Excel 文件中工作表的数据。默认取第一个工作表。 /// </summary> /// <param name="fileName">Excel 的文件名</param> /// <returns></returns> private DataTable GetDataFromExcel(String fileName) { string sheetName = this.GetExcelSheetNameByIndex(fileName, 0); if (!String.IsNullOrEmpty(sheetName)) { string commandText = String.Format("SELECT * FROM [{0}]", sheetName); return this.ExecuteDataTable(fileName, commandText); } return null; } /// <summary> /// 获取指定 Excel 文件中工作表的数据。 /// </summary> /// <param name="fileName">Excel 的文件名</param> /// <param name="commandText">查询 SQL </param> private DataTable ExecuteDataTable(String fileName, String commandText) { string connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1/";", fileName); using (OleDbDataAdapter da = new OleDbDataAdapter(commandText, connectionString)) { DataSet ds = new DataSet(); da.Fill(ds); return ds.Tables[0]; } } } }