public class ExcelUtil { /// <summary> /// 从Excel文件导入DataSet /// Excel的连接字符串说明(详细参看 http://www.connectionstrings.com/excel ): /// HDR=Yes 表示第一行是表头,没有数据,读取时忽略第一行。设置为 No 时则从第一行读取。 /// IMEX=1 表示告诉OleDb驱动,所有数据将作为字符串读取(numbers,dates等) /// 注意:有时候因为ISAM驱动没有安装,设置HDR和IMEX会出异常。去掉即可。 /// </summary> /// <param name="fileName">Excel文件路径</param> /// <param name="firstRowIsHeader">第一行是否是表头</param> /// <returns>Excel数据,一个Sheet对应一个DataTable</returns> public static DataSet GetDataSetFromExcel(string fileName, string sheetName = "", bool firstRowIsHeader = true) { if (!System.IO.File.Exists(fileName)) throw new ArgumentException("file is not exist!"); var ds = new DataSet(); var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=Excel 8.0;"; //if (firstRowIsHeader) // strConn += "HDR=Yes;"; strConn = string.Format(strConn, fileName); using (var conn = new OleDbConnection(strConn)) { conn.Open(); DataTable excelSchema = null; string sql = "select * from [{0}]"; var adapter = new OleDbDataAdapter(); adapter.SelectCommand = conn.CreateCommand(); if (string.IsNullOrEmpty(sheetName)) { excelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow row in excelSchema.Rows) { var sheet = row["TABLE_NAME"].ToString(); sql = string.Format(sql, sheet); adapter.SelectCommand.CommandText = sql; adapter.Fill(ds, sheet); } } else { sql = string.Format(sql, sheetName + "{1}quot;); adapter.SelectCommand.CommandText = sql; adapter.Fill(ds, sheetName); } conn.Close(); } return ds; }
}
上面代码中46行的 "quot;" 应为 "$"
Excel查询的时候,加上范围也可以。这样就能指定范围获得数据。比如: (1) string sql = "select * from [{0}A:C]"; (2) string sql = "select * from [{0}A1:C100]";
使用:
private void btnExcel2Data_Click(object sender, EventArgs e) { openFileDialog1.Filter = "Excel Files | *.xls"; var ret = openFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { DataSet ds = GetDataSetFromExcel(openFileDialog1.FileName, false); this.dataGridView1.DataSource = ds.Tables[0]; } }