c#使用NPOI导入excel

使用NPOI可以很方便的处理excel.记录下导入excel生成DataTable的方法.

public static DataTable ExcelToDataTable(string fileName, bool isFirstRowColumn, string sheetName = null)
    {
        ISheet sheet = null;
        DataTable data = new DataTable();
        FileStream fs = null;
        IWorkbook workbook = null;
        int startRow = 0;
        try
        {
            fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook(fs);
            else if (fileName.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook(fs);

            if (sheetName != null)
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue;
                            if (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }
                Func<ICell, string> GetFormULAValue = cell =>
                {
                    object returnValue = new object();
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.STRING:
                            returnValue = cell.StringCellValue;
                            cell.SetCellValue(cell.StringCellValue);
                            break;
                        case CellType.NUMERIC:
                            returnValue = cell.NumericCellValue.ToString();
                            cell.SetCellValue(cell.NumericCellValue);
                            break;
                        case CellType.BOOLEAN:
                            returnValue = cell.BooleanCellValue.ToString();
                            cell.SetCellValue(cell.BooleanCellValue);
                            break;
                        default:
                            break;
                    }
                    return returnValue.ToString();
                };



                //最后一列的标号
                int rowCount = sheet.LastRowNum;
                for (int i = startRow; i <= rowCount; ++i)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue; //没有数据的行默认是null       

                    DataRow dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null && cell.CellType != CellType.BLANK) //同理,没有数据的单元格都默认是null
                        {
                            object value = new object();
                            switch (cell.CellType)
                            {
                                case CellType.NUMERIC:
                                    // Date comes here
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                                    {
                                        value = cell.DateCellValue;
                                    }
                                    else
                                    {
                                        // Numeric type
                                        value = cell.NumericCellValue;
                                    }
                                    break;
                                case CellType.BOOLEAN:
                                    // Boolean type
                                    value = cell.BooleanCellValue;
                                    break;
                                case CellType.FORMULA:
                                    value = GetFormULAValue(cell);
                                    break;
                                //value = cell.CellFormula;
                                //break;

                                default:
                                    // String type
                                    value = cell.StringCellValue;
                                    break;
                            }

                            dataRow[j] = value.ToString();
                        }
                    }
                    data.Rows.Add(dataRow);
                }
            }

            return data;
        }
        catch (Exception ex)
        {
            // Console.WriteLine("Exception: " + ex.Message);
            return null;
        }
    }

发表评论

电子邮件地址不会被公开。 必填项已用*标注