读取Excel文件，转换成Table
1 #region-----EXCELデータを読み込む----- 2 2 /// <summary> 3 3 /// EXCELデータを読み込む 4 4 /// </summary> 5 5 /// <param name="filePath"></param> 6 6 /// <returns></returns> 7 7 private System.Data.DataTable ExcelToDataTable(string filePath) 8 8 { 9 13 //Excel対象 10 14 myExcel.Application excel = new Excel.Application(); 11 15 excel.Application.EnableEvents = false; 12 16 13 17 //Workbook対象 14 18 myExcel.Workbook workbook = excel.Workbooks._Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); 15 19 //Worksheet対象 16 20 myExcel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; 17 21 18 22 //シート名 19 23 string strSheetName = worksheet.Name; 20 21 if (excel != null) 22 { 23 // 设置或返回 Microsoft PowerPoint 在运行宏时是否显示警告。 24 excel.DisplayAlerts = false; 25 workbook.Close(false, filePath, null); 26 worksheet = null; 27 workbook = null; 28 excel.Quit(); 29 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); 30 excel = null; 31 System.GC.Collect(); 32 } 33 34 24 string sql_F = "SELECT * FROM [" + strSheetName + "$]"; 35 25 36 26 //连接对象 37 27 OleDbConnection conn = null; 38 28 //读取数据对象 39 29 OleDbDataAdapter da = null; 40 30 //读取excel数据的dataset 41 31 System.Data.DataSet dsItem = new System.Data.DataSet(); 42 32 43 33 int nDBErrorFlag = 0; 44 34 //连接参数 45 35 string connStr = ""; 46 36 try 47 37 { 48 38 //Provider → Excel 97-2003，Excel 2007 49 39 //HDR=YES第一行是标题，NO第一行不是标题；IMEX=1表示导入模式，这个模式开启的 Excel 档案只能用来做“写入”用途，还有个重要作用：强制将混合数据转换为文本，可读出excel的数字型的内容。 50 40 connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; 51 41 conn = new OleDbConnection(connStr); 52 42 conn.Open(); 53 43 54 44 da = new OleDbDataAdapter(sql_F, conn); 55 45 da.Fill(dsItem, strSheetName); 56 46 } 57 47 catch (Exception ex) 58 48 { 59 49 nDBErrorFlag = 1; 60 50 } 61 51 62 52 if (nDBErrorFlag == 1) 63 53 { 64 54 nDBErrorFlag = 0; 65 55 try 66 56 { 67 57 //Provider → Excel 97-2003 68 58 connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; 69 59 conn = new OleDbConnection(connStr); 70 60 conn.Open(); 71 61 da = new OleDbDataAdapter(sql_F, conn); 72 62 73 63 dsItem = new System.Data.DataSet(); 74 64 da.Fill(dsItem, strSheetName); 75 65 } 76 66 catch (Exception ex) 77 67 { 78 68 throw (ex); 79 69 } 80 70 } 81 71 82 72 if (conn.State == ConnectionState.Open) 83 73 { 84 74 conn.Close(); 85 75 da.Dispose(); 86 76 conn.Dispose(); 87 77 } 88 78 return dsItem.Tables[strSheetName]; 89 79 } 90 80 #endregion
参考：https://www.cnblogs.com/goodgirlmia/p/3793200.html（【c#操作office】--OleDbDataAdapter 与OleDbDataReader方式读取excel,并转换为datatable）
https://www.cnblogs.com/wx881208/p/4096728.html （Excel的 OleDb 连接串的格式(Provider=Microsoft.ACE.OLEDB)）
https://www.cnblogs.com/vaevvaev/p/6873367.html （C#连接OleDBConnection数据库的操作）
https://www.cnblogs.com/fengduandeai/p/8575494.html（System.IO.Path 文件名、路径、扩展名处理）
https://docs.microsoft.com/zh-cn/office/vba/api/excel.workbook.close(Workbook.Close 方法 )
