利用Npoi操作excel（支持excel2003，excel2007）

最新推荐文章于 2023-07-06 12:59:20 发布

X___V

最新推荐文章于 2023-07-06 12:59:20 发布

阅读量1.4k

点赞数

分类专栏：库应用

本文链接：https://blog.csdn.net/X___V/article/details/8799855

版权

库应用专栏收录该内容

1 篇文章 0 订阅

订阅专栏

定义一个枚举区别excel版本：

 enum ExcelVersion
    {
        Excel2003,
        Excel2007
    }

用Npoi将Excel文档数据读取到DataTable中：

    class NpoiExcelOperate
    {
        private ExcelVersion _excelVersion;
        private string _filePath;
        public NpoiExcelOperate(string fileName,ExcelVersion excelVersion)
        {
            if (File.Exists(fileName) == false)
            {
                _filePath = null;
                throw new Exception("文件路径错误，文件不存在，类型构造失败");
            }
            _excelVersion = excelVersion;
            _filePath = fileName;
        }

        public DataTable LoadExcelData(int _sheetId)
        {
            if (_filePath == null||_sheetId<1) return null;
            FileStream _fileStream = new FileStream(_filePath, FileMode.Open, FileAccess.Read);
            IWorkbook _workBook;
            if (_excelVersion == ExcelVersion.Excel2003)
            {
                _workBook = new HSSFWorkbook(_fileStream);
            }
            else
            {
                _workBook = new XSSFWorkbook(_fileStream);
            }
            DataTable _dataTable = new DataTable();
            ISheet sheet = _workBook.GetSheetAt(_sheetId-1);
            IRow _firstRow = sheet.GetRow(0);
            //_dataTable.Columns.Add(new DataColumn(_firstRow.Cells[0].StringCellValue));
            for (int i = _firstRow.FirstCellNum; i < _firstRow.LastCellNum; i++)
            {
                DataColumn column = new DataColumn(_firstRow.GetCell(i).StringCellValue);
                _dataTable.Columns.Add(column);
            }
            for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
            {
                IRow row;
                if (_excelVersion == ExcelVersion.Excel2003)
                {
                    row = (HSSFRow)sheet.GetRow(i);
                }
                else
                {
                    row = (XSSFRow)sheet.GetRow(i);
                }
                DataRow dataRow = _dataTable.NewRow();

                for (int j = row.FirstCellNum; j < _firstRow.LastCellNum; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                _dataTable.Rows.Add(dataRow);
            }
            _workBook = null;
            sheet = null;
            _fileStream.Close();
            return _dataTable;
        }
     }