【WinForm】杂记（5）：C#导入Excel到DataTable

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;
    }
}