using System.Data; using Interop.Excel; using System.Reflection; /// <summary> /// 根据路径读取Excel /// </summary> /// <param name="path">Excel路径</param> /// <returns></returns> private static DataSet LoadExcelByPath(string path) { DataSet ds = new DataSet(); ApplicationClass excel = null; Workbook wb = null; Worksheet ws = null; Range column = null; try { excel = new ApplicationClass(); excel.Visible = false; wb = excel.Application.Workbooks.Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); int sheetnum = wb.Worksheets.Count; //分别获取各个sheet到DataSet for (int sheetno = 1; sheetno <= sheetnum; sheetno++) { ws = (Worksheet)wb.Worksheets[sheetno]; int rowcount = ws.UsedRange.Rows.Count; int colcount = ws.UsedRange.Columns.Count; System.Data.DataTable dt = new System.Data.DataTable(ws.Name); for (int col = 1; col <= colcount; col++) { dt.Columns.Add(col.ToString(), typeof(string)); } for (int i = 1; i <= rowcount; i++) { DataRow dr = dt.NewRow(); for (int j = 1; j <= colcount; j++) { column = (Range)(ws.get_Range(IndexToColumn(j) + i.ToString(), Missing.Value)); if (column != null && column.Value2 != null) { dr[j - 1] = column.Value2.ToString(); } } dt.Rows.Add(dr); } ds.Tables.Add(dt); } } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (excel != null) { if (wb != null) { if (ws != null) { if (column != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(column); column = null; } System.Runtime.InteropServices.Marshal.ReleaseComObject(ws); ws = null; } wb.Close(false, Missing.Value, Missing.Value); System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); wb = null; } } excel.Application.Workbooks.Close(); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; GC.Collect(); } return ds; }