快捷導(dǎo)航

C#使用NPOI操作Excel工具類的實(shí)現(xiàn)

更新時(shí)間：2023年11月29日 10:44:17 作者：rjcql

NPOI是POI項(xiàng)目的.NET遷移版本,使用NPOI可以在沒(méi)有安裝Office或者相應(yīng)環(huán)境的機(jī)器上對(duì)Word或Excel文檔進(jìn)行讀寫(xiě)操作,下面我們就來(lái)學(xué)習(xí)一下如何使用NPOI編寫(xiě)操作Excel的工具類吧

寫(xiě)在前面

NPOI是POI項(xiàng)目的.NET遷移版本。POI是一個(gè)開(kāi)源的Java 讀寫(xiě) Excel、Word 等微軟Ole2組件文檔的項(xiàng)目；使用NPOI可以在沒(méi)有安裝Office或者相應(yīng)環(huán)境的機(jī)器上對(duì)Word或Excel文檔進(jìn)行讀寫(xiě)操作。

NPOI類庫(kù)中操作EXCEL有兩個(gè)模塊分別是：

1?.HSSF模塊，操作拓展名為.xls的Excel，對(duì)應(yīng)Excel2003及以前的版本。

2?.XSSF模塊，操作拓展名為.xlsx的Excel，對(duì)應(yīng)Excel2007及以后的版本，可向下兼容xls，故本例使用XSSF下的XSSFWorkbook來(lái)操作。

通過(guò)NuGet獲取NPOI

需要引用的命名空間如下：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections;
using System.Data;

代碼實(shí)現(xiàn)

   public class ExcelProcessor
    {
        #region 私有變量
 
        private int _perSheetCount = 40000;//每個(gè)sheet要保存的條數(shù)
 
        private string _filePath;
 
        private IWorkbook _currentWorkbook;
 
        private List<string> _sheets;
 
        private Dictionary<string, DataTable> _dataDict;
 
        #endregion
 
        #region 屬性
 
        public List<string> Sheets
        {
            get { return _sheets ?? (_sheets = GetSheets(_filePath)); }
        }
 
        #endregion
 
        #region 構(gòu)造器
 
        /// <summary>
        /// 默認(rèn)構(gòu)造器
        /// </summary>
        /// <param name="filePath"></param>
        public ExcelProcessor(string filePath)
        {
            _filePath = filePath;
            _dataDict = new Dictionary<string, DataTable>();
        }
 
        /// <summary>
        /// 加載數(shù)據(jù)
        /// </summary>
        public bool LoadData()
        {
            try
            {
                using (var fs = new FileStream(_filePath, FileMode.OpenOrCreate, FileAccess.Read))
                {
                    _currentWorkbook = new XSSFWorkbook(fs);
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
 
        /// <summary>
        /// 最大接收5萬(wàn)條每頁(yè)，大于5萬(wàn)時(shí)，使用系統(tǒng)默認(rèn)的值(4萬(wàn))
        /// </summary>
        /// <param name="perSheetCounts"></param>
        public ExcelProcessor(int perSheetCounts)
        {
            if (_perSheetCount <= 50000)
                _perSheetCount = perSheetCounts;
        }
 
        #endregion
 
        #region 公有方法
 
        public List<string> GetSheets(string fileName)
        {
            var sheets = new List<string>();
            if (_currentWorkbook == null)
                return sheets;
            for (int i = 0; i < _currentWorkbook.NumberOfSheets; i++)
            {
                sheets.Add(_currentWorkbook.GetSheetName(i));
            }
            return sheets;
        }
 
        public object GetNumericCellValue(string sheetName, int rowIndex, int colIndex)
        {
            if (!Sheets.Contains(sheetName))
                return null;
            var cell = _currentWorkbook.GetSheet(sheetName).GetRow(rowIndex).GetCell(colIndex - 1);
            return cell.NumericCellValue;
        }
 
        public object GetStringCellValue(string sheetName, int rowIndex, int colIndex)
        {
            if (!Sheets.Contains(sheetName))
                return null;
            var cell = _currentWorkbook.GetSheet(sheetName).GetRow(rowIndex).GetCell(colIndex - 1);
            if (cell == null)
                return null;
            if (cell.CellType == CellType.Formula)
            {
                if (cell.CachedFormulaResultType == CellType.String)
                    return cell.StringCellValue;
                if (cell.CachedFormulaResultType == CellType.Numeric)
                    return cell.NumericCellValue;
                if (cell.CachedFormulaResultType == CellType.Boolean)
                    return cell.BooleanCellValue;
                return null;
            }
            if (cell.CellType == CellType.Numeric)
                return cell.NumericCellValue;
            if (cell.CellType == CellType.Boolean)
                return cell.NumericCellValue;
            return cell.StringCellValue;
        }
 
        public object GetDateTimeCellValue(string sheetName, int rowIndex, int colIndex)
        {
            if (!Sheets.Contains(sheetName))
                return null;
            var cell = _currentWorkbook.GetSheet(sheetName).GetRow(rowIndex).GetCell(colIndex - 1);
            if (cell.CellType == CellType.String)
                return cell.StringCellValue;
            return cell.DateCellValue;
        }
 
        public ICell GetCell(string sheetName, int rowIndex, int colIndex)
        {
            if (!Sheets.Contains(sheetName))
                return null;
            var sheet = _currentWorkbook.GetSheet(sheetName);
            if (sheet == null)
                return null;
            var row = sheet.GetRow(rowIndex);
            if (row == null)
                return null;
            var cell = row.GetCell(colIndex - 1);
            if (cell == null)
                return null;
            return cell;
        }
 
        /// <summary>
        /// 獲取單元格里面的值
        /// </summary>
        /// <param name="sheetName">表名</param>
        /// <param name="x">行索引從1開(kāi)始</param>
        /// <param name="y">列索引從1開(kāi)始</param>
        /// <returns></returns>
        public object GetCellValue(string sheetName, int rowIndex, int colIndex)
        {
            if (!Sheets.Contains(sheetName))
                return null;
 
            DataTable dt = null;
            if (!_dataDict.ContainsKey(sheetName))
            {
                dt = Import(sheetName);
                _dataDict.Add(sheetName, dt);
            }
            else
            {
                dt = _dataDict[sheetName];
            }
            if (dt == null)
                return null;
 
            if (dt.Rows.Count < rowIndex)
                return null;
 
            var rowIdx = rowIndex - 1;
            var row = dt.Rows[rowIdx];
            var colIdx = colIndex - 1;
            return row[colIdx];
        }
 
        public void SetCellValues(ICell cell, string cellType, string cellValue)
        {
            switch (cellType)
            {
                case "System.String": //字符串類型
                    double result;
                    if (double.TryParse(cellValue, out result))
                        cell.SetCellValue(result);
                    else
                        cell.SetCellValue(cellValue);
                    break;
                case "System.DateTime": //日期類型
                    DateTime dateV;
                    DateTime.TryParse(cellValue, out dateV);
                    cell.SetCellValue(dateV);
                    break;
                case "System.Boolean": //布爾型
                    bool boolV = false;
                    bool.TryParse(cellValue, out boolV);
                    cell.SetCellValue(boolV);
                    break;
                case "System.Int16": //整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(cellValue, out intV);
                    cell.SetCellValue(intV);
                    break;
                case "System.Decimal": //浮點(diǎn)型
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(cellValue, out doubV);
                    cell.SetCellValue(doubV);
                    break;
                case "System.DBNull": //空值處理
                    cell.SetCellValue("");
                    break;
                default:
                    cell.SetCellValue("");
                    break;
            }
        }
 
        public DataTable Import(string sheetName)
        {
            sheetName = string.IsNullOrEmpty(sheetName) ? "Sheet1" : sheetName;
 
            ISheet sheet = _currentWorkbook.GetSheet(sheetName);
            if (sheet == null)
            {
                sheet = _currentWorkbook.GetSheetAt(0);
            }
 
            IEnumerator ie = sheet.GetRowEnumerator();
 
            IRow row = null;
            var maxCol = 0;
            while (ie.MoveNext())
            {
                row = ie.Current as IRow;//取一行,為了得到column的總數(shù)
                if (row.LastCellNum > maxCol)
                    maxCol = row.LastCellNum;
            }
 
            var dt = new DataTable();
            for (int i = 0; i < maxCol; i++)
            {
                dt.Columns.Add(string.Format("Col{0}", i));
            }
 
            ie.Reset();
            DataRow drow = null;
            ICell cell = null;
            var isHeader = true;
            while (ie.MoveNext())
            {
                if (isHeader)
                {
                    isHeader = false;
                    continue;
                }
                row = ie.Current as IRow;
                drow = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    if (row.GetCell(i) == null)
                    {
                        drow[i] = null;
                        continue;
                    }
                    cell = row.GetCell(i) as ICell;
 
                    switch (cell.CellType)
                    {
                        case CellType.Blank:
                            drow[i] = string.Empty;
                            break;
                        case CellType.Boolean:
                            drow[i] = cell.BooleanCellValue;
                            break;
                        case CellType.Error:
                            drow[i] = cell.ErrorCellValue;
                            break;
                        case CellType.Formula:
                            drow[i] = "=" + cell.CellFormula;
                            break;
                        case CellType.Numeric:
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                drow[i] = cell.DateCellValue;
                            }
                            else
                            {
                                drow[i] = cell.NumericCellValue;
                            }
                            break;
                        case CellType.String:
                            drow[i] = cell.StringCellValue;
                            break;
                        case CellType.Unknown:
                            break;
                        default:
                            drow[i] = null;
                            break;
                    }
                }
                dt.Rows.Add(drow);
            }
            return dt;
        }
 
        public string Export(string excelFileName, List<DataTable> dataTables)
        {
            var workbook = new HSSFWorkbook();
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
 
            var index = 0;
            foreach (var dataTable in dataTables)
            {
                var tableName = dataTable.TableName;
                if (string.IsNullOrEmpty(tableName))
                    tableName = "Sheet" + (++index);
                sheet = workbook.CreateSheet(tableName);
 
                //填充表頭
                row = sheet.CreateRow(0);
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    cell = row.CreateCell(i);
                    cell.SetCellValue(dataTable.Columns[i].ColumnName);
                }
 
                //填充內(nèi)容
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    row = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);
                        SetCellValues(cell, dataTable.Columns[j].DataType.ToString(), dataTable.Rows[i][j].ToString());
                    }
                }
            }
            if (File.Exists(excelFileName)) File.Delete(excelFileName);
            using (var fs = new FileStream(excelFileName, FileMode.CreateNew, FileAccess.Write)) workbook.Write(fs);
            return excelFileName;
        }
 
        public string Export(string excelFileName, DataTable dataTable)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
 
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int sheetCount = 1;//當(dāng)前的sheet數(shù)量
            int currentSheetCount = 0;//循環(huán)時(shí)當(dāng)前保存的條數(shù)，每頁(yè)都會(huì)清零
 
            //表頭樣式
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            //內(nèi)容樣式
            style = workbook.CreateCellStyle();
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
 
            sheet = workbook.CreateSheet("Sheet" + sheetCount);
            //填充表頭
            row = sheet.CreateRow(0);
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                cell = row.CreateCell(i);
                cell.SetCellValue(dataTable.Columns[i].ColumnName);
                cell.CellStyle = style;
            }
            //填充內(nèi)容
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                if (currentSheetCount >= _perSheetCount)
                {
                    sheetCount++;
                    currentSheetCount = 0;
                    sheet = workbook.CreateSheet("Sheet" + sheetCount);
                }
                if (sheetCount == 1)//因?yàn)榈谝豁?yè)有表頭，所以從第二頁(yè)開(kāi)始寫(xiě)
                    row = sheet.CreateRow(currentSheetCount + 1);
                else//以后沒(méi)有表頭了，所以從開(kāi)始寫(xiě)，都是基于0的
                    row = sheet.CreateRow(currentSheetCount);
                currentSheetCount++;
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);
                    cell.CellStyle = style;
                    SetCellValues(cell, dataTable.Columns[j].DataType.ToString(), dataTable.Rows[i][j].ToString());
                }
            }
            FileStream fs = new FileStream(excelFileName, FileMode.CreateNew, FileAccess.Write);
            workbook.Write(fs);
            fs.Close();
            return excelFileName;
        }
 
        #endregion
 
    }