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

1、C#导入Excel数据的方式（两种） 方式一、导入数据到数据集对象，只支持Excel的标准格式，即不能合并单元格等等/ / 导入数据到数据集中 / 备注:此种方法只支持excel原文件 / / 文件路劲 / 异常信息 / 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= +

2、 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 stringsheetDt.Rows.Count; for (int i = 0; i sheetDt.Rows.Count; i+) sheeti = s

3、heetDt.RowsiTABLE_NAME.ToString(); string strExcel = string.Format(select * from 0, sheet0); OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn); dt = new System.Data.DataTable(); myCommand.Fill(dt); conn.Close(); catch (Exception ex) exceptionMsg = ex.Message; return dt; 方法二、读取Exce

4、l文件，然后根据里面的数据信息拼装#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 Workb

5、ook _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

6、(); 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.UsedRange1, k as Range).Value2.ToString(); newColumn = new DataColumn(str); newRow.Table.Columns.Add(newColumn); Range range = _wSheet.Ce

7、llsi, j as Range; if (range != null & !.Equals(range.Text.ToString() newRowj - 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

8、) 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