使用Microsoft.Office.Interop.Excel提取Excel文件数据
将Excel数据提取出来放到二维数组中:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.OleDb; using Microsoft.Office.Interop.Excel; namespace ConsoleApplication1 { class Program { static void Main(string[] args) { DataSet ds = ExcelToDataTable(@"c:\aaa.xls", "aaa"); System.Data.DataTable ExcelTable = ds.Tables["aaa"]; int iColums = ExcelTable.Columns.Count;//列数 int iRows = ExcelTable.Rows.Count;//行数 string[,] storedata = new string[iRows, iColums]; for (int i = 0; i < ExcelTable.Rows.Count; i++) for (int j = 0; j < ExcelTable.Columns.Count; j++) { storedata[i, j] = ExcelTable.Rows[i][j].ToString(); } string strSql = GetStrSql(storedata, iRows); } public static DataSet ExcelToDataTable(string strExcelFileName, string strSheetName) { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 5.0;"; string strExcel = string.Format("select * from [{0}$]", strSheetName); DataSet ds = new DataSet(); using (OleDbConnection conn = new OleDbConnection(strConn)) { Application app = new Application(); app.Application.Workbooks.Open(strExcelFileName); //app.Visible = true; conn.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn); adapter.Fill(ds, strSheetName); conn.Close(); } return ds; } public static string GetStrSql(string[,] datainfo, int rows) { string strSql = ""; for (int i = 0; i < datainfo.GetLength(0); i++) { strSql = "insert into tmp_localfile_data(finish_time,data_value,product_id,prod_group,proj_code,parameter_id," + " machine,category_flag,partno,fixture,marking,head_id,shift,batch,id_1,id_2)" + " VALUES(to_date('" + datainfo[i, 0] + "','mm/dd/yyyy hh24:mi:ss'),'" + datainfo[i, 1] + "','" + datainfo[i, 2] + "','" + datainfo[i, 3] + "','" + datainfo[i, 4] + "'," + "'" + datainfo[i, 5] + "','" + datainfo[i, 6] + "','" + datainfo[i, 7] + "','" + datainfo[i, 8] + "','" + datainfo[i, 9] + "','" + datainfo[i, 10] + "','" + datainfo[i, 11] + "','" + datainfo[i, 12] + "','" + datainfo[i, 13] + "','" + datainfo[i, 14] + "','" + datainfo[i, 15] + "')"; } return strSql; } } }