C#使用NOPI导入Excel

使用NOPI导入Excel文档

NOPI版本：2.3.0,依赖于NPOI的SharpZipLib版本：0.86,经测试适用于.net4.0+

记录遇到的几个问题

1. NOPI中的IWorkbook接口：xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现

2. 日期转换，判断row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)

不能直接使用row.GetCell(j).DateCellValue,这玩意会直接抛出异常来~

1. 将文件流转换为DataTable

    /// <summary>

    /// 根据Excel格式读取Excel

    /// </summary>

    /// <param name="stream">文件流</param>

    /// <param name="type">Excel格式枚举类型，xls/xlsx</param>

    /// <param name="sheetName">表名，默认取第一张</param>

    /// <returns>DataTable</returns>

    private static DataTable ImportExcel(Stream stream, ExcelExtType type, string sheetName)

    {

        DataTable dt = new DataTable();

        IWorkbook workbook;

        try

        {

            //xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现

            switch (type)

            {

                case ExcelExtType.xlsx:

                    workbook = new XSSFWorkbook(stream);

                    break;

                default:

                    workbook = new HSSFWorkbook(stream);

                    break;

            }

            ISheet sheet = null;

            //获取工作表 默认取第一张

            if (string.IsNullOrWhiteSpace(sheetName))

                sheet = workbook.GetSheetAt(0);

            else

                sheet = workbook.GetSheet(sheetName);



            if (sheet == null)

                return null;

            IEnumerator rows = sheet.GetRowEnumerator();

            #region 获取表头

            IRow headerRow = sheet.GetRow(0);

            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)

            {

                ICell cell = headerRow.GetCell(j);

                if (cell != null)

                {

                    dt.Columns.Add(cell.ToString());

                }

                else

                {

                    dt.Columns.Add("");

                }

            }

            #endregion

            #region 获取内容

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

            {

                IRow row = sheet.GetRow(i);

                DataRow dataRow = dt.NewRow();



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

                {

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

                    {

                        //判断单元格是否为日期格式

                        if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))

                        {

                            if (row.GetCell(j).DateCellValue.Year >=1970)

                            {

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

                            }

                            else

                            {

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



                            }

                        }

                        else

                        {

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

                        }

                    }

                }

                dt.Rows.Add(dataRow);

            }

            #endregion



        }

        catch (Exception ex)

        {

            dt=null;

        }

        finally

        {

            //if (stream != null)

            //{

            //    stream.Close();

            //    stream.Dispose();

            //}

        }

        return dt;

    }

2. 文件上载导入

    /// <summary>

    /// 上传Excel导入

    /// </summary>

    /// <param name="file">上载文件对象</param>

    /// <param name="errorMsg">错误信息</param>

    /// <param name="sheetName">表名，默认取第一张</param>

    /// <returns></returns>

    public static DataTable Import(System.Web.HttpPostedFileBase file, ref string errorMsg, string sheetName = "")

    {

        if (file == null || file.InputStream == null || file.InputStream.Length == 0)

        {

            errorMsg = "请选择要导入的Excel文件";

            return null;

        }

        var excelType = GetExcelFileType(file.FileName);

        if (excelType == null)

        {

            errorMsg = "请选择正确的Excel文件";

            return null;

        }

        using (var stream = new MemoryStream())

        {

            file.InputStream.Position = 0;

            file.InputStream.CopyTo(stream);

            var dt = ImportExcel(stream, excelType.Value, sheetName);

            if (dt == null)

                errorMsg = "导入失败,请选择正确的Excel文件";

            return dt;

        }

    }

3. 本地路径读取导入

    /// <summary>

    /// 根据文件路径导入Excel

    /// </summary>

    /// <param name="filePath"></param>

    /// <param name="errorMsg">错误信息</param>

    /// <param name="sheetName">表名，默认取第一张</param>

    /// <returns>可能为null的DataTable</returns>

    public static DataTable Import(string filePath, ref string errorMsg, string sheetName = "")

    {

        var excelType = GetExcelFileType(filePath);

        if (GetExcelFileType(filePath) == null)

        {

            errorMsg = "请选择正确的Excel文件";

            return null;

        }

        if (!File.Exists(filePath))

        {

            errorMsg = "没有找到要导入的Excel文件";

            return null;

        }

        DataTable dt;

        using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))

        {

            dt = ImportExcel(stream, excelType.Value, sheetName);

        }

        if (dt == null)

            errorMsg = "导入失败,请选择正确的Excel文件";

        return dt;

    }

4.完整demo

附赠一个winform导入Excel的Demo。

https://github.com/yimogit/NopiExcelDemo