C# Excel 读取为Datatable

最近项目用到的读取Excel 为DataTable 兼容2003、2007、2010。记录一下,以后会用到

引用 NPOI.dll 和 EPPlus.dll

 using System;

 using System.Collections.Generic;

 using System.Linq;

 using System.Text;

 using System.Data;

 using OfficeOpenXml;

 using System.IO;

 using NPOI.SS.UserModel;

 using NPOI.HSSF.Util;

 using NPOI.HSSF.UserModel;

 public class ExcelUtil

     {

         /// <summary>

         /// Excel 转 DataTable 约定 Excel第一行为列名，即为转换后的Datatable中的DataColumn列

         /// </summary>

         /// <param name="file">Excel文件路径</param>

         /// <param name="sheet">sheet名称</param>

         /// <returns></returns>

         public static DataTable ExcelSheet2DataTable(string file, string sheet)

         {

             DataTable dt = null;

             FileInfo existingFile = new FileInfo(file);

             try

             {

                 ExcelPackage package = new ExcelPackage(existingFile);

                 int vSheetCount = package.Workbook.Worksheets.Count; //获取总Sheet页

                 ExcelWorksheet worksheet = package.Workbook.Worksheets[sheet];//选定 指定页

                 int maxColumnNum = worksheet.Dimension.End.Column;//最大列

                 int minColumnNum = worksheet.Dimension.Start.Column;//最小列

                 int maxRowNum = worksheet.Dimension.End.Row;//最小行

                 int minRowNum = worksheet.Dimension.Start.Row;//最大行

                 DataTable vTable = new DataTable();

                 DataColumn vC;

                 for (int j = ; j <= maxColumnNum; j++)

                 {

                     string colname = worksheet.Cells[, j].Text;

                     if (vTable.Columns.Contains(colname))

                     {

                         colname += "";//对于可能的重名列的处理

                     }

                     vC = new DataColumn(colname, typeof(string));

                     vTable.Columns.Add(vC);

                 }

                 for (int n = ; n <= maxRowNum; n++)

                 {

                     DataRow vRow = vTable.NewRow();

                     for (int m = ; m <= maxColumnNum; m++)

                     {

                         vRow[m - ] = worksheet.Cells[n, m].Value;

                     }

                     vTable.Rows.Add(vRow);

                 }

                 dt = vTable;

                 worksheet = null;

                 sheet = null;

             }

             catch (Exception vErr)

             {

                 try

                 {

                     using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))

                     {

                         using (HSSFWorkbook workbook = new HSSFWorkbook(fs))  //根据EXCEL文件流初始化工作簿

                         {

                             //var sheet1 = workbook.GetSheetAt(0); //获取第一个sheet

                             var sheet1 = workbook.GetSheet(sheet); //获取sheet

                             DataTable table = new DataTable();//

                             var row1 = sheet1.GetRow();//获取第一行即标头

                             int cellCount = row1.LastCellNum; //第一行的列数

                             int rowCount = sheet1.LastRowNum; //总行数

                             //把第一行的数据添加到datatable的列名

                             for (int i = row1.FirstCellNum; i < cellCount; i++)

                             {

                                 string colname = string.Empty;

                                 if (row1.GetCell(i).CellType == NPOI.SS.UserModel.CellType.STRING)

                                 {

                                     colname = row1.GetCell(i).StringCellValue;

                                 }

                                 else

                                 {

                                     colname = row1.GetCell(i).ToString();

                                 }

                                 if (table.Columns.Contains(colname))

                                 {

                                     colname += "";

                                 }

                                 DataColumn column = new DataColumn(colname);

                                 table.Columns.Add(column);

                             }

                                 //把每行数据添加到datatable中

                             for (int i = (sheet1.FirstRowNum + ); i < sheet1.LastRowNum; i++)

                             {

                                 HSSFRow row = sheet1.GetRow(i) as HSSFRow;

                                 DataRow dataRow = table.NewRow();

                                 for (int j = row.FirstCellNum; j < cellCount; j++)

                                 {

                                     if (row.GetCell(j) != null)

                                         dataRow[j] = row.GetCell(j).ToString();

                                 }

                                 table.Rows.Add(dataRow);

                             }

                             dt = table;

                         }

                     }

                 }

                 catch

                 {

                 }

             }

             return dt;

         }

     }