C#导入Excel数据的方式（两种）

方式一、导入数据到数据集对象，只支持Excel的标准格式，即不能合并单元格等等 /// <summary> /// 导入数据到数据集中 /// 备注:此种方法只支持excel原文件 /// </summary> /// <param name="Path">文件路劲</param> /// <param name="exceptionMsg">异常信息</param> /// <returns></returns> public static System.Data.DataTable InputExcel(string Path, ref string exceptionMsg) { System.Data.DataTable dt = null; try { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;"; using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); System.Data.DataTable sheetDt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string[] sheet = new string[sheetDt.Rows.Count]; for (int i = 0; i < sheetDt.Rows.Count; i++) { sheet[i] = sheetDt.Rows[i]["TABLE_NAME"].ToString(); } string strExcel = string.Format("select * from [{0}]", sheet[0]); OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn); dt = new System.Data.DataTable(); myCommand.Fill(dt); conn.Close(); } } catch (Exception ex) { exceptionMsg = ex.Message; } return dt; } 方法二、读取Excel文件，然后根据里面的数据信息拼装 #region 读取Excel表格中数据到DataTable中 public static System.Data.DataTable ChangeExcelToDateTable(string _path) { System.Data.DataTable tempdt = new System.Data.DataTable(); tempdt.TableName = "Excel"; Application app = new Application(); object obj = System.Reflection.Missing.Value; try { Workbook _wBook = app.Workbooks.Open(_path, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj); Worksheet _wSheet = (Worksheet)_wBook.Worksheets.get_Item(1); DataRow newRow = null; DataColumn newColumn = null; for (int i = 2; i <= _wSheet.UsedRange.Rows.Count; i++) { newRow = tempdt.NewRow(); for (int j = 1; j <= _wSheet.UsedRange.Columns.Count; j++) { if (i == 2 && j == 1) { //表头 for (int k = 1; k <= _wSheet.UsedRange.Columns.Count; k++) { string str = (_wSheet.UsedRange[1, k] as Range).Value2.ToString(); newColumn = new DataColumn(str); newRow.Table.Columns.Add(newColumn); } } Range range = _wSheet.Cells[i, j] as Range; if (range != null && !"".Equals(range.Text.ToString())) { newRow[j - 1] = range.Value2; } } tempdt.Rows.Add(newRow); } _wSheet = null; _wBook = null; app.Quit(); Kill(app); int generation = System.GC.GetGeneration(app); app = null; System.GC.Collect(generation); return tempdt; } catch (Exception ex) { app.Quit(); Kill(app); int generation = System.GC.GetGeneration(app); app = null; throw ex; } } #endregion #region 结束进程 [DllImport("User32.dll", CharSet = CharSet.Auto)] private static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); private static void Kill(Microsoft.Office.Interop.Excel.Application excel) { IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 int k = 0; GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用 p.Kill(); //关闭进程k  } #endregion