C#代码实现把Excel文件转化为DataTable，根据Excel的文件后缀名不同，用不同的方法来进行实现，下面通过根据Excel文件的两种后缀名（*.xlsx和*.xls）分别来实现。获取文件后缀名的方法是：Path.GetExtension(fileName)方法，通过引用：using System.IO;实现代码如下：（其中以下代码中出现的filename都是带盘符的绝对路径）
根据Excel文件的后缀名不同调用的主方法
private DataTable FileToDataTable(string fileName) { DataTable dt = new DataTable(); string extendName = Path.GetExtension(fileName);//获取文件的后缀名 switch (extendName.ToLower()) { case ".xls": dt = XlsToDataTable(fileName); break; case ".xlsx": dt = XlsxToDataTable(fileName); break; default: break; } return dt; } XlsToDataTable()
private DataTable XlsToDataTable(string fileName) { DataTable dataTable = new DataTable(); Stream stream = null; try { stream = File.OpenRead(fileName); HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream); HSSFSheet hssfsheet = (HSSFSheet)hssfworkbook.GetSheetAt(hssfworkbook.ActiveSheetIndex); HSSFRow hssfrow = (HSSFRow)hssfsheet.GetRow(0); int lastCellNum = (int)hssfrow.LastCellNum; for (int i = (int)hssfrow.FirstCellNum; i < lastCellNum; i++) { DataColumn column = new DataColumn(hssfrow.GetCell(i).StringCellValue); dataTable.Columns.Add(column); } dataTable.TableName = hssfsheet.SheetName; int lastRowNum = hssfsheet.LastRowNum; //列名后,从TABLE第二行开始进行填充数据 for (int i = hssfsheet.FirstRowNum + 1; i < hssfsheet.LastRowNum; i++)// { HSSFRow hssfrow2 = (HSSFRow)hssfsheet.GetRow(i); DataRow dataRow = dataTable.NewRow(); for (int j = (int)hssfrow2.FirstCellNum; j < lastCellNum; j++)// { dataRow[j] = hssfrow2.GetCell(j);// } dataTable.Rows.Add(dataRow); } stream.Close(); } catch (Exception ex) { ScriptManager.RegisterStartupScript(Page, GetType(), "alertForm", "alert(' Xls to DataTable: " + ex.Message + "');", true); } finally { if (stream != null) { stream.Close(); } } return dataTable; } XlsxToDataTable()
public DataTable XlsxToDataTable(string vFilePath) { DataTable dataTable = new DataTable(); try { SLDocument sldocument = new SLDocument(vFilePath); dataTable.TableName = sldocument.GetSheetNames()[0]; SLWorksheetStatistics worksheetStatistics = sldocument.GetWorksheetStatistics(); int startColumnIndex = worksheetStatistics.StartColumnIndex; int endColumnIndex = worksheetStatistics.EndColumnIndex; int startRowIndex = worksheetStatistics.StartRowIndex; int endRowIndex = worksheetStatistics.EndRowIndex; for (int i = startColumnIndex; i <= endColumnIndex; i++) { SLRstType cellValueAsRstType = sldocument.GetCellValueAsRstType(1, i); dataTable.Columns.Add(new DataColumn(cellValueAsRstType.GetText(), typeof(string))); } for (int j = startRowIndex + 1; j <= endRowIndex; j++) { DataRow dataRow = dataTable.NewRow(); for (int i = startColumnIndex; i <= endColumnIndex; i++) { dataRow[i - 1] = sldocument.GetCellValueAsString(j, i); } dataTable.Rows.Add(dataRow); } } catch (Exception ex) { throw new Exception("Xlsx to DataTable: \n" + ex.Message); } return dataTable; }