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

来源：互联网发布：php curl 设置cookie 编辑：程序博客网时间：2024/05/05 12:15

定义一个枚举区别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;        }     }