.NET-Conversion项目记录(5)：C#导入Excel到DataTable

这里提供的方法是借助NPOI（官方文档：https://archive.codeplex.com/?p=npoi）

引用这个库的好处就是不用再启动Excel（之前因为要写入Excel所以没有办法），然后它的运行速度也非常快。

添加这个库的方法是NuGet，具体步骤参考https://www.cnblogs.com/RicardoIsLearning/p/12111040.html

添加完成之后，写入命名空间

using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

 

具体转化的代码如下

private System.Data.DataTable Excel2DataTable(string filepath, int indexOfsheet = 0)
{
    System.Data.DataTable dt = new System.Data.DataTable();
    IWorkbook workbook = null;//another way: var hssfworkbook = new HSSFWorkbook(file);
    FileStream file = null;
    try {
        using (file = new FileStream(filepath, FileMode.Open, FileAccess.Read)) {

            if (filepath.IndexOf(".xlsx") > 0)
                workbook = new XSSFWorkbook(file);//read .xlsx file
            else if (filepath.IndexOf(".xls") > 0)
                workbook = new HSSFWorkbook(file);//read .xls file

            if (workbook != null) {
                //int indexOfsheet = 0;//get the 1st sheet
                ISheet sheet = workbook.GetSheetAt(indexOfsheet);
                IEnumerator rows = sheet.GetRowEnumerator();//get all rows

                //add cols
                IRow headerRow = sheet.GetRow(0); //get the row of column name
                int cellCount = headerRow.LastCellNum; //get the column number
                                                        //so that we can get all columns
                for (int i = 0; i < cellCount; i++) {
                    string ttname;
                    ttname = (sheet.GetRow(0).GetCell(i) != null) ? sheet.GetRow(0).GetCell(i).ToString() : "";
                    dt.Columns.Add(ttname, typeof(string));
                }
                ////get the assigned columns
                //int[] indexcolumns = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };//start from 0
                //for (int i = 0; j < indexcolumns.Length; i++) {
                //    string ttname;
                //    ttname = sheet.GetRow(0).GetCell(indexcolumns[i]).ToString();
                //    dt.Columns.Add(ttname, typeof(string));
                //}

                //add rows
                //for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) {}
                int irow = 0;
                while (rows.MoveNext()) {//start from the index of 0
                    if (irow == 0) {
                        irow++;
                        continue;
                    }                                
                    IRow row = (IRow)rows.Current;//IRow row = sheet.GetRow(irow);
                    System.Data.DataRow dr = dt.NewRow();
                    for (int i = row.FirstCellNum; i < row.LastCellNum; i++) {
                        ICell cell = row.GetCell(i);
                        if (cell == null) {
                            dr[i] = null;
                        } else {
                            switch (cell.CellType) {
                                case CellType.Blank:
                                    dr[i] = "[null]";
                                    break;
                                case CellType.Boolean:
                                    dr[i] = cell.BooleanCellValue;
                                    break;
                                case CellType.Numeric:
                                    dr[i] = cell.ToString();
                                    break;
                                case CellType.String:
                                    dr[i] = cell.StringCellValue;
                                    break;
                                case CellType.Error:
                                    dr[i] = cell.ErrorCellValue;
                                    break;
                                case CellType.Formula:
                                    try {
                                        dr[i] = cell.NumericCellValue;
                                    } catch {
                                        dr[i] = cell.StringCellValue;
                                    }
                                    break;
                                default:
                                    dr[i] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                    irow++;
                }
            }
            return dt;
        }
    } catch (Exception e){
        MessageBox.Show(e.Message, "Error", MessageBoxButtons.OK);
        if (file != null) {
            file.Close();
        }
        return null;
    }
}

 有几点需要注意

• NPOI对于不同的Excel文件类型处理的方式不同，我目前接触过.xls和.xlsx文件，需要创建不同的类（见第9-12行）。不过可以统一用IWorkbook类来接收。
• NPOI读到的表格，索引是从0开始（行、列都是）的
• 获取列数的办法是取某行，然后获取LastCellNum属性
• 添加列的代码为第23行到第27行。特别注意如果为空，需要自行补充值（第25行），否则会报错
• 若是提取特定列，则可以采用第29行到第34行代码
• 第37行和第39行给出了两种添加行的循环方式。特别注意，在添加列的时候，其实已经读取了原数据表中的第一行，所以添加行的其实位置是1。这就是第40-43行代码写入的原因。
• 从Excel数据表中读取行的方式有两种：1）当前行；2）按照索引读取（参见第44行）
• 当NPOI读到表赋给DataTable时，后者会默认将第一行作为列。