从Execl导入数据返回DataTable

这个是完整的导入数据的方法,,搜索Excel的第一个Sheet里面的每个单元格,

从找到的第一个包含有数据的单元格开始建表,有数据的第一行为列名

以下行对应列为数据行,依次以String的类型 导入每个单元格的数据

当有数据的第一列没有数据的时候判断数据结束,然后返回包含数据的Datable

DataTable的名字为Excel第一个Sheet的,名字.

 

注:请不要尝试导入一个空的Excel,搜索每个单元格会导致去搜索整个 65536行 256列的单元格,需要很长时间.

 

之前写的一个方法，担心忘记了，留在这里，以备查阅

private System.Data.DataTable GetDataFromExcel(string FilePath) { System.Data.DataTable dt = new System.Data.DataTable(); try { //初始化Excel处理进程 m_App = new Microsoft.Office.Interop.Excel.Application(); m_App.Application.Workbooks.Add(true); //打开文件 m_App.Workbooks.Open(FilePath, 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); //获取Excel中的第一个Sheet _Worksheet worksheet = (_Worksheet)m_App.Worksheets.get_Item(1); int row = 1, col = 1, DataStartRow = 1, DataStartCol = 1, DataEndRow = 65536, DataEndCol = 256; int TotalCol = worksheet.Columns.Count; int TotalRow = worksheet.Rows.Count; bool IsDataBegain = false; bool IsDataEnd = false; bool IsDataColEnd = false; while (row <= TotalRow) { DataRow dr = dt.NewRow(); while (col <= TotalCol) { string Vairable = worksheet.get_Range(worksheet.Cells[row, col], worksheet.Cells[row, col]).Cells.Text.ToString().Trim(); if (Vairable != string.Empty && !IsDataBegain) { DataStartRow = row; DataStartCol = col; IsDataBegain = true; continue; } else if (IsDataBegain && Vairable == string.Empty && !IsDataColEnd) { DataEndCol = col - 1; TotalCol = DataEndCol; IsDataColEnd = true; break; } else if (IsDataBegain && col == DataStartCol && Vairable == string.Empty && !IsDataEnd) { DataEndRow = row - 1; TotalRow = DataEndRow; IsDataEnd = true; break; } //添加数据列头 if (IsDataBegain && DataStartRow == row) { dt.Columns.Add(Vairable, typeof(string)); } //添加数据 if (IsDataBegain && row > DataStartRow && col >= DataStartCol) { dr[col - DataStartCol] = Vairable; } col++; } //添加数据行 if (IsDataBegain && row > DataStartRow && !IsDataEnd) { dt.Rows.Add(dr); } //数据搜索结束 if (IsDataBegain && IsDataEnd) break; row++; col = DataStartCol; } m_App.Workbooks.Close(); m_App.Quit(); } catch (Exception ex) { throw ex; } return dt; }