C# NPOI 读写excel工具类-马育民老师

# 添加依赖库

通过 NuGet 添加：

[![](https://www.malaoshi.top/upload/0/0/1GW1dPZVa1go.png)](https://www.malaoshi.top/upload/0/0/1GW1dPZVa1go.png)

# 代码

```

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

namespace netframeword46_WindowsFormsApp1.docutil
{
    public class NPOIExcelUtil
    {
        FileStream stream = null;
        IWorkbook workbook = null;
        ISheet worksheet = null;
        string file;
        public void Open(string file)
        {
            this.file = file;
            string extension = Path.GetExtension(file).ToLower();
            stream = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            if (extension == ".xls")
            {
                workbook = new HSSFWorkbook(stream); // .xls格式
            }
            else if (extension == ".xlsx")
            {
                workbook = new XSSFWorkbook(stream); // .xlsx格式
            }
        }
        public IList<string> GetWorksheets()
        {
            IList<string> worksheets = new List<string>();
            // 遍历所有工作表
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                ISheet sheet = workbook.GetSheetAt(i);
                if (sheet == null) continue;

// 获取工作表基本信息
                string sheetName = sheet.SheetName;

worksheets.Add(sheetName);
            }
            return worksheets;
        }
        /// <summary>
        /// 选用sheet
        /// </summary>
        /// <param name="sheetIndex">从0开始</param>
        public void UseSheet(int sheetIndex)
        {
            worksheet = workbook.GetSheetAt(sheetIndex);
        }
        public DataSet ReadAllWorkbook()
        {
            System.Data.DataSet dataSet = new System.Data.DataSet();

for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
            { 
                ISheet worksheet = workbook.GetSheetAt(sheetIndex);

System.Data.DataTable dataTable = new System.Data.DataTable(worksheet.SheetName);
                dataSet.Tables.Add(dataTable);

UseSheet(sheetIndex);
                // 获取工作表的最大列数（用于创建DataTable列）
                int maxColumnCount = GetMaxColumnCount();
                if (maxColumnCount <= 0)
                {
                    continue; // 无数据列，跳过该表
                }

// 创建DataTable列（使用默认列名：Column1, Column2...）
                for (int col = 0; col < maxColumnCount; col++)
                {
                    dataTable.Columns.Add($"Column{col + 1}");
                }

// 读取所有行数据（从第0行开始，所有行均为数据行）
                for (int rowIndex = worksheet.FirstRowNum; rowIndex <= worksheet.LastRowNum; rowIndex++)
                {
                    IRow dataRow = worksheet.GetRow(rowIndex);
                    if (dataRow == null) continue;

DataRow dtRow = dataTable.NewRow();
                    bool hasData = false; // 标记行是否有有效数据

// 填充单元格数据
                    for (int col = 0; col < maxColumnCount; col++)
                    {
                        // 允许单元格不存在（返回空）
                        ICell cell = dataRow.GetCell(col, MissingCellPolicy.RETURN_BLANK_AS_NULL);
                        object cellValue = GetCellValueByType(cell);

if (cellValue!=null)
                        {
                            hasData = true;
                        }

dtRow[col] = cellValue ?? DBNull.Value;
                    }

// 只添加有数据的行
                    if (hasData)
                    {
                        dataTable.Rows.Add(dtRow);
                    }
                }
            }
            return dataSet;
        }

/// <summary>
        /// 获取工作表的最大列数（用于确定DataTable的列数）
        /// </summary>
        public int GetMaxColumnCount()
        {
            int maxColumns = 0;
            for (int rowIndex = worksheet.FirstRowNum; rowIndex <= worksheet.LastRowNum; rowIndex++)
            {
                IRow row = worksheet.GetRow(rowIndex);
                if (row != null && row.LastCellNum > maxColumns)
                {
                    maxColumns = row.LastCellNum;
                }
            }
            return maxColumns;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="column">列名是A、B、C</param>
        /// <param name="rowStart">行索引从1开始</param>
        /// <param name="rowEnd">行索引从1开始</param>
        /// <returns></returns>
        public System.Data.DataTable Read(int sheet, string column, int rowStart, int rowEnd)
        {
            return Read(sheet, new string[] { column }, rowStart, rowEnd);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheetIndex"></param>
        /// <param name="columns">列名是A、B、C</param>
        /// <param name="rowStart">行索引从1开始</param>
        /// <param name="rowEnd">行索引从1开始</param>
        /// <returns></returns>
        public System.Data.DataTable Read(int sheetIndex, string[] columns, int rowStart, int rowEnd)
        {
            rowStart--;// npoi第一行索引是0，为了符合excel行号从1开始，所以-1
            rowEnd--;// npoi第一行索引是0，为了符合excel行号从1开始，所以-1

ISheet worksheet = workbook.GetSheetAt(sheetIndex);

System.Data.DataTable dataTable = new System.Data.DataTable(worksheet.SheetName);
            foreach (string item in columns)
            {
                dataTable.Columns.Add(item);

}

// 遍历每一行每一列
            for (int rowIndex = rowStart; rowIndex <= rowEnd; rowIndex++)
            {
                var newRow = dataTable.NewRow();
                dataTable.Rows.Add(newRow);

for (int i = 0; i < columns.Length; i++)
                {
                    int columnIndex = GetColumnIndexByName(columns[i]);
                    ICell cell = worksheet.GetRow(rowIndex).GetCell(columnIndex);
                    var cellValue = GetCellValueByType(cell);
                    // 去掉左右空格，在根据列比对是否相等时，因为空格导致查询不到
                    //newRow[i] = cellValue?.ToString().Trim() ?? "";
                    //newRow[columnIndex] = cellValue?.ToString() ?? "";
                    newRow[i] = cellValue;
                }

}

return dataTable;
        }

/// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell">目标单元格</param>
        /// <returns></returns>
        private object GetCellValueByType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return null;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Numeric:
                    return cell.NumericCellValue;
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Error:
                    return cell.ErrorCellValue;
                case CellType.Formula:
                default:
                    return "=" + cell.CellFormula;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="columns">列名是A、B、C</param>
        /// <param name="rowStart">行索引从1开始</param>
        /// <param name="rowEnd">行索引从1开始</param>
        /// <param name="datas"></param>
        public void Write(int sheet, string[] columns, int rowStart, int rowEnd, IList<object[]> datas)
        {
            rowStart--;// npoi第一行索引是0，为了符合excel行号从1开始，所以-1
            rowEnd--;// npoi第一行索引是0，为了符合excel行号从1开始，所以-1

ISheet worksheet = workbook.GetSheetAt(sheet);

int rowIndexOfDatas = 0;
            // 遍历每一行每一列
            for (int rowIndex = rowStart; rowIndex <= rowEnd; rowIndex++)
            {
                IRow row = worksheet.CreateRow(rowIndex);
                object[] rowOfDatas = datas[rowIndexOfDatas];
                rowIndexOfDatas++;

//int colIndexOfDatas = 0;
                for (int i = 0; i < columns.Length; i++)
                {
                    int colIndex = GetColumnIndexByName(columns[i]);
                    ICell cell = row.CreateCell(colIndex);
                    SetCellValue(cell,rowOfDatas[i]);
                    //worksheet.Cells[rowIndex, columns[colIndex]] = rowOfDatas[colIndex];
                    //colIndexOfDatas++;
                }

}
        }
        /// <summary>
        /// 根据数据类型设置单元格值
        /// </summary>
        private static void SetCellValue(ICell cell, object value)
        {
            if (value == null)
            {
                cell.SetCellValue("");
                return;
            }

// 根据值的类型设置单元格数据类型
            switch (value)
            {
                case string str:
                    cell.SetCellValue(str);
                    break;
                case int num:
                    cell.SetCellValue(num);
                    break;
                case double num:
                    cell.SetCellValue(num);
                    break;
                case decimal num:
                    cell.SetCellValue((double)num);
                    break;
                case DateTime date:
                    cell.SetCellValue(date);
                    break;
                case bool b:
                    cell.SetCellValue(b);
                    break;
                default:
                    // 其他类型转为字符串
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="column">列名是A、B、C</param>
        /// <param name="rowStart">行索引从1开始</param>
        /// <param name="rowEnd">行索引从1开始</param>
        /// <param name="datas"></param>
        public void Write(int sheet, string column, int rowStart, int rowEnd, object[] datas)
        {
            IList<object[]> list = new List<object[]>() { datas };
            Write(sheet, new string[] { column }, rowStart, rowEnd, list);
        }

/// <summary>
        /// 
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="column">列名是A、B、C</param>
        /// <param name="rowIndex">行索引从1开始</param>
        /// <param name="data"></param>
        public void Write(int sheet, string column, int rowIndex, object data)
        {
            rowIndex--;// npoi第一行索引是0，为了符合excel行号从1开始，所以-1
            int colIndex = GetColumnIndexByName(column);

ISheet worksheet = workbook.GetSheetAt(sheet);

ICell cell = worksheet.GetRow(rowIndex).GetCell(colIndex);
            //IRow row = worksheet.CreateRow(rowIndex);
            //ICell cell = row.CreateCell(colIndex);
            // 设置单元格值（根据数据类型处理）
            SetCellValue(cell, data);
        }

public void Create(string file)
        {
            string extension = Path.GetExtension(file).ToLower();
            stream = new FileStream(file, FileMode.Create, FileAccess.Write);
            if (extension == ".xls")
            {
                workbook = new HSSFWorkbook(stream); // .xls格式
            }
            else if (extension == ".xlsx")
            {
                workbook = new XSSFWorkbook(stream); // .xlsx格式
            }
        }
        public void Save()
        {
            Save(file);
        }
        public void Save(string file)
        {

using (FileStream fileStream = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fileStream);
            }
            //workbook.Write(stream);

}
        public void Close()
        {
            if(workbook!=null)workbook.Close();
            if(stream!=null)stream.Close();
        }

/// <summary>
        /// 将单个字符的字符串(A-Z)转换为对应数字(1-26)
        /// </summary>
        private static int GetColumnIndexByName(string input)
        {
            if (string.IsNullOrEmpty(input) || input.Length != 1)
            {
                throw new ArgumentException("输入必须是单个字符的字符串");
            }

char letter = char.ToUpper(input[0]);
            if (!char.IsLetter(letter))
            {
                throw new ArgumentException("输入必须是字母A-Z");
            }

return letter - 'A';
        }
    }
}
```

原文出处：http://www.malaoshi.top/show_1GW1dPZmbTZq.html