如何使用NPOI读取文件

Question

我发现 NPOI 非常适合用 C# 编写 Excel 文件。

但我想用 C# 打开、读取和修改 Excel 文件。

我该怎么做？

Answer 1

简单阅读下面的示例：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

//.....

private void button1_Click(object sender, EventArgs e)
{
    HSSFWorkbook hssfwb;
    using (FileStream file = new FileStream(@"c:\test.xls", FileMode.Open, FileAccess.Read))
    {
        hssfwb= new HSSFWorkbook(file);
    }

    ISheet sheet = hssfwb.GetSheet("Arkusz1");
    for (int row = 0; row <= sheet.LastRowNum; row++)
    {
        if (sheet.GetRow(row) != null) //null is when the row only contains empty cells 
        {
            MessageBox.Show(string.Format("Row {0} = {1}", row, sheet.GetRow(row).GetCell(0).StringCellValue));
        }
    }
}  

顺便说一下：在 NPOI 网站 这里 的下载部分有示例package - 一组 C# 示例。如果你还没有尝试过的话。 :)

Answer 2

依赖 Workbook 工厂来实例化工作簿对象可能会有所帮助，因为工厂方法将为您检测 xls 或 xlsx。参考：http://apache-poi.1045710.n5.nabble.com/How-to-check-for-valid-excel-files-using-POI-without-checking-the-file-extension- td2341055.html

IWorkbook workbook = WorkbookFactory.Create(inputStream);

如果您不确定工作表的名称，但确定索引（从 0 开始），则可以像这样抓取工作表：

ISheet sheet = workbook.GetSheetAt(sheetIndex);

然后，您可以使用接受的代码提供的代码遍历行mj82的回答

Answer 3

我发现 NPOI 对于处理 Excel 文件非常有用，这是我的实现（注释是西班牙语，对此感到抱歉）：

此方法打开 Excel（xls 或 xlsx）文件并将其转换为 DataTable。

        /// <summary>Abre un archivo de Excel (xls o xlsx) y lo convierte en un DataTable.
    /// LA PRIMERA FILA DEBE CONTENER LOS NOMBRES DE LOS CAMPOS.</summary>
    /// <param name="pRutaArchivo">Ruta completa del archivo a abrir.</param>
    /// <param name="pHojaIndex">Número (basado en cero) de la hoja que se desea abrir. 0 es la primera hoja.</param>
    private DataTable Excel_To_DataTable(string pRutaArchivo, int pHojaIndex)
    {
        // --------------------------------- //
        /* REFERENCIAS:
         * NPOI.dll
         * NPOI.OOXML.dll
         * NPOI.OpenXml4Net.dll */
        // --------------------------------- //
        /* USING:
         * using NPOI.SS.UserModel;
         * using NPOI.HSSF.UserModel;
         * using NPOI.XSSF.UserModel; */
        // AUTOR: Ing. Jhollman Chacon R. 2015
        // --------------------------------- //
        DataTable Tabla = null;
        try
        {
            if (System.IO.File.Exists(pRutaArchivo))
            {

                IWorkbook workbook = null;  //IWorkbook determina si es xls o xlsx              
                ISheet worksheet = null;
                string first_sheet_name = "";

                using (FileStream FS = new FileStream(pRutaArchivo, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(FS);          //Abre tanto XLS como XLSX
                    worksheet = workbook.GetSheetAt(pHojaIndex);    //Obtener Hoja por indice
                    first_sheet_name = worksheet.SheetName;         //Obtener el nombre de la Hoja

                    Tabla = new DataTable(first_sheet_name);
                    Tabla.Rows.Clear();
                    Tabla.Columns.Clear();

                    // Leer Fila por fila desde la primera
                    for (int rowIndex = 0; rowIndex <= worksheet.LastRowNum; rowIndex++)
                    {
                        DataRow NewReg = null;
                        IRow row = worksheet.GetRow(rowIndex);
                        IRow row2 = null;
                        IRow row3 = null;

                        if (rowIndex == 0)
                        {
                            row2 = worksheet.GetRow(rowIndex + 1); //Si es la Primera fila, obtengo tambien la segunda para saber el tipo de datos
                            row3 = worksheet.GetRow(rowIndex + 2); //Y la tercera tambien por las dudas
                        }

                        if (row != null) //null is when the row only contains empty cells 
                        {
                            if (rowIndex > 0) NewReg = Tabla.NewRow();

                            int colIndex = 0;
                            //Leer cada Columna de la fila
                            foreach (ICell cell in row.Cells)
                            {
                                object valorCell = null;
                                string cellType = "";
                                string[] cellType2 = new string[2];

                                if (rowIndex == 0) //Asumo que la primera fila contiene los titlos:
                                {
                                    for (int i = 0; i < 2; i++)
                                    {
                                        ICell cell2 = null;
                                        if (i == 0) { cell2 = row2.GetCell(cell.ColumnIndex); }
                                        else { cell2 = row3.GetCell(cell.ColumnIndex); }

                                        if (cell2 != null)
                                        {
                                            switch (cell2.CellType)
                                            {
                                                case CellType.Blank: break;
                                                case CellType.Boolean: cellType2[i] = "System.Boolean"; break;
                                                case CellType.String: cellType2[i] = "System.String"; break;
                                                case CellType.Numeric:
                                                    if (HSSFDateUtil.IsCellDateFormatted(cell2)) { cellType2[i] = "System.DateTime"; }
                                                    else
                                                    {
                                                        cellType2[i] = "System.Double";  //valorCell = cell2.NumericCellValue;
                                                    }
                                                    break;

                                                case CellType.Formula:                                                      
                                                    bool continuar = true;
                                                    switch (cell2.CachedFormulaResultType)
                                                    {
                                                        case CellType.Boolean: cellType2[i] = "System.Boolean"; break;
                                                        case CellType.String: cellType2[i] = "System.String"; break;
                                                        case CellType.Numeric:
                                                            if (HSSFDateUtil.IsCellDateFormatted(cell2)) { cellType2[i] = "System.DateTime"; }
                                                            else
                                                            {
                                                                try
                                                                {
                                                                    //DETERMINAR SI ES BOOLEANO
                                                                    if (cell2.CellFormula == "TRUE()") { cellType2[i] = "System.Boolean"; continuar = false; }
                                                                    if (continuar && cell2.CellFormula == "FALSE()") { cellType2[i] = "System.Boolean"; continuar = false; }
                                                                    if (continuar) { cellType2[i] = "System.Double"; continuar = false; }
                                                                }
                                                                catch { }
                                                            } break;
                                                    }
                                                    break;
                                                default:
                                                    cellType2[i] = "System.String"; break;
                                            }
                                        }
                                    }

                                    //Resolver las diferencias de Tipos
                                    if (cellType2[0] == cellType2[1]) { cellType = cellType2[0]; }
                                    else
                                    {
                                        if (cellType2[0] == null) cellType = cellType2[1];
                                        if (cellType2[1] == null) cellType = cellType2[0];
                                        if (cellType == "") cellType = "System.String";
                                    }

                                    //Obtener el nombre de la Columna
                                    string colName = "Column_{0}";
                                    try { colName = cell.StringCellValue; }
                                    catch { colName = string.Format(colName, colIndex); }

                                    //Verificar que NO se repita el Nombre de la Columna
                                    foreach (DataColumn col in Tabla.Columns)
                                    {
                                        if (col.ColumnName == colName) colName = string.Format("{0}_{1}", colName, colIndex);
                                    }

                                    //Agregar el campos de la tabla:
                                    DataColumn codigo = new DataColumn(colName, System.Type.GetType(cellType));
                                    Tabla.Columns.Add(codigo); colIndex++;
                                }
                                else
                                {
                                    //Las demas filas son registros:
                                    switch (cell.CellType)
                                    {
                                        case CellType.Blank: valorCell = DBNull.Value; break;
                                        case CellType.Boolean: valorCell = cell.BooleanCellValue; break;
                                        case CellType.String: valorCell = cell.StringCellValue; break;
                                        case CellType.Numeric:
                                            if (HSSFDateUtil.IsCellDateFormatted(cell)) { valorCell = cell.DateCellValue; }
                                            else { valorCell = cell.NumericCellValue; } break;
                                        case CellType.Formula:
                                            switch (cell.CachedFormulaResultType)
                                            {
                                                case CellType.Blank: valorCell = DBNull.Value; break;
                                                case CellType.String: valorCell = cell.StringCellValue; break;
                                                case CellType.Boolean: valorCell = cell.BooleanCellValue; break;
                                                case CellType.Numeric:
                                                    if (HSSFDateUtil.IsCellDateFormatted(cell)) { valorCell = cell.DateCellValue; }
                                                    else { valorCell = cell.NumericCellValue; }
                                                    break;
                                            }
                                            break;
                                        default: valorCell = cell.StringCellValue; break;
                                    }
                                    //Agregar el nuevo Registro
                                    if (cell.ColumnIndex <= Tabla.Columns.Count - 1) NewReg[cell.ColumnIndex] = valorCell;
                                }
                            }
                        }
                        if (rowIndex > 0) Tabla.Rows.Add(NewReg);
                    }
                    Tabla.AcceptChanges();
                }
            }
            else
            {
                throw new Exception("ERROR 404: El archivo especificado NO existe.");
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return Tabla;
    }

第二种方法则相反，将 DataTable 保存到 Excel 文件中，是的，它可以是 xls 或新的 xlsx，您可以选择！

        /// <summary>Convierte un DataTable en un archivo de Excel (xls o Xlsx) y lo guarda en disco.</summary>
    /// <param name="pDatos">Datos de la Tabla a guardar. Usa el nombre de la tabla como nombre de la Hoja</param>
    /// <param name="pFilePath">Ruta del archivo donde se guarda.</param>
    private void DataTable_To_Excel(DataTable pDatos, string pFilePath)
    {
        try
        {
            if (pDatos != null && pDatos.Rows.Count > 0)
            {
                IWorkbook workbook = null;
                ISheet worksheet = null;

                using (FileStream stream = new FileStream(pFilePath, FileMode.Create, FileAccess.ReadWrite))
                {
                    string Ext = System.IO.Path.GetExtension(pFilePath); //<-Extension del archivo
                    switch (Ext.ToLower())
                    {
                        case ".xls":
                            HSSFWorkbook workbookH = new HSSFWorkbook();
                            NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
                            dsi.Company = "Cutcsa"; dsi.Manager = "Departamento Informatico";
                            workbookH.DocumentSummaryInformation = dsi;
                            workbook = workbookH;
                            break;

                        case ".xlsx": workbook = new XSSFWorkbook(); break;
                    }

                    worksheet = workbook.CreateSheet(pDatos.TableName); //<-Usa el nombre de la tabla como nombre de la Hoja

                    //CREAR EN LA PRIMERA FILA LOS TITULOS DE LAS COLUMNAS
                    int iRow = 0;
                    if (pDatos.Columns.Count > 0)
                    {
                        int iCol = 0;
                        IRow fila = worksheet.CreateRow(iRow);
                        foreach (DataColumn columna in pDatos.Columns)
                        {
                            ICell cell = fila.CreateCell(iCol, CellType.String);
                            cell.SetCellValue(columna.ColumnName);
                            iCol++;
                        }
                        iRow++;
                    }

                    //FORMATOS PARA CIERTOS TIPOS DE DATOS
                    ICellStyle _doubleCellStyle = workbook.CreateCellStyle();
                    _doubleCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.###");

                    ICellStyle _intCellStyle = workbook.CreateCellStyle();
                    _intCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");

                    ICellStyle _boolCellStyle = workbook.CreateCellStyle();
                    _boolCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("BOOLEAN");

                    ICellStyle _dateCellStyle = workbook.CreateCellStyle();
                    _dateCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy");

                    ICellStyle _dateTimeCellStyle = workbook.CreateCellStyle();
                    _dateTimeCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy HH:mm:ss");

                    //AHORA CREAR UNA FILA POR CADA REGISTRO DE LA TABLA
                    foreach (DataRow row in pDatos.Rows)
                    {
                        IRow fila = worksheet.CreateRow(iRow);
                        int iCol = 0;
                        foreach (DataColumn column in pDatos.Columns)
                        {
                            ICell cell = null; //<-Representa la celda actual                               
                            object cellValue = row[iCol]; //<- El valor actual de la celda

                            switch (column.DataType.ToString())
                            {
                                case "System.Boolean":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = fila.CreateCell(iCol, CellType.Boolean);

                                        if (Convert.ToBoolean(cellValue)) { cell.SetCellFormula("TRUE()"); }
                                        else { cell.SetCellFormula("FALSE()"); }

                                        cell.CellStyle = _boolCellStyle;
                                    }
                                    break;

                                case "System.String":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = fila.CreateCell(iCol, CellType.String);
                                        cell.SetCellValue(Convert.ToString(cellValue));
                                    }
                                    break;

                                case "System.Int32":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = fila.CreateCell(iCol, CellType.Numeric);
                                        cell.SetCellValue(Convert.ToInt32(cellValue));
                                        cell.CellStyle = _intCellStyle;
                                    }
                                    break;
                                case "System.Int64":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = fila.CreateCell(iCol, CellType.Numeric);
                                        cell.SetCellValue(Convert.ToInt64(cellValue));
                                        cell.CellStyle = _intCellStyle;
                                    }
                                    break;
                                case "System.Decimal":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = fila.CreateCell(iCol, CellType.Numeric);
                                        cell.SetCellValue(Convert.ToDouble(cellValue));
                                        cell.CellStyle = _doubleCellStyle;
                                    }
                                    break;
                                case "System.Double":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = fila.CreateCell(iCol, CellType.Numeric);
                                        cell.SetCellValue(Convert.ToDouble(cellValue));
                                        cell.CellStyle = _doubleCellStyle;
                                    }
                                    break;

                                case "System.DateTime":
                                    if (cellValue != DBNull.Value)
                                    {
                                        cell = fila.CreateCell(iCol, CellType.Numeric);
                                        cell.SetCellValue(Convert.ToDateTime(cellValue));

                                        //Si No tiene valor de Hora, usar formato dd-MM-yyyy
                                        DateTime cDate = Convert.ToDateTime(cellValue);
                                        if (cDate != null && cDate.Hour > 0) { cell.CellStyle = _dateTimeCellStyle; }
                                        else { cell.CellStyle = _dateCellStyle; }
                                    }
                                    break;
                                default:
                                    break;
                            }
                            iCol++;
                        }
                        iRow++;
                    }

                    workbook.Write(stream);
                    stream.Close();
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

使用这 2 种方法，您可以打开 Excel 文件，将其加载到数据表中，进行修改并将其保存回 Excel 文件中。

希望你们觉得这很有用。

Answer 4

正如 Janoulle 指出的，如果您使用 WorkbookFactory，您不需要检测它是哪个扩展，它会为您做这件事。我最近必须实现一个使用 NPOI 读取 Excel 文件并将电子邮件地址导入 SQL 数据库的解决方案。我的主要问题是，我可能会从不同的客户那里收到大约 12 种不同的 Excel 布局，因此我需要一些可以快速更改而无需太多代码的东西。我最终使用了 Npoi.Mapper，这是一个很棒的工具！强烈推荐！

这是我的完整解决方案：

using System.IO;
using System.Linq;
using Npoi.Mapper;
using Npoi.Mapper.Attributes;
using NPOI.SS.UserModel;

namespace JobCustomerImport.Processors
{
    public class ExcelEmailProcessor
    {
        private UserManagementServiceContext DataContext { get; }

        public ExcelEmailProcessor(int customerNumber)
        {
            DataContext = new UserManagementServiceContext();
        }

        public void Execute(string localPath, int sheetIndex)
        {
            IWorkbook workbook;
            using (FileStream file = new FileStream(localPath, FileMode.Open, FileAccess.Read))
            {
                workbook = WorkbookFactory.Create(file);
            }

            var importer = new Mapper(workbook);
            var items = importer.Take<MurphyExcelFormat>(sheetIndex);
            foreach(var item in items)
            {
                var row = item.Value;
                if (string.IsNullOrEmpty(row.EmailAddress))
                    continue;

                UpdateUser(row);
            }

            DataContext.SaveChanges();
        }

        private void UpdateUser(MurphyExcelFormat row)
        {
            //LOGIC HERE TO UPDATE A USER IN DATABASE...
        }

        private class MurphyExcelFormat
        {
            [Column("District")]
            public int District { get; set; }

            [Column("DM")]
            public string FullName { get; set; }

            [Column("Email Address")]
            public string EmailAddress { get; set; }

            [Column(3)]
            public string Username { get; set; }

            public string FirstName
            {
                get
                {
                    return Username.Split('.')[0];
                }
            }

            public string LastName
            {
                get
                {
                    return Username.Split('.')[1];
                }
            }
        }
    }
}

我对 NPOI + Npoi.Mapper 非常满意（来自 Donny Tian）作为 Excel 导入解决方案，我写了一篇关于它的博客文章，详细介绍了上面的代码。如果您愿意，可以在这里阅读：导入 Excel 文件的最简单方法。该解决方案最好的一点是它可以在无服务器的 Azure/云环境中完美运行，这是其他 Excel 工具/库无法实现的。

Answer 5

private DataTable GetDataTableFromExcel(String Path)
    {
        XSSFWorkbook wb;
        XSSFSheet sh;
        String Sheet_name;

        using (var fs = new FileStream(Path, FileMode.Open, FileAccess.Read))
        {
            wb = new XSSFWorkbook(fs);

               Sheet_name= wb.GetSheetAt(0).SheetName;  //get first sheet name
        }
        DataTable DT = new DataTable();
        DT.Rows.Clear();
        DT.Columns.Clear();

        // get sheet
        sh = (XSSFSheet)wb.GetSheet(Sheet_name);

        int i = 0;
        while (sh.GetRow(i) != null)
        {
            // add neccessary columns
            if (DT.Columns.Count < sh.GetRow(i).Cells.Count)
            {
                for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
                {
                    DT.Columns.Add("", typeof(string));
                }
            }

            // add row
            DT.Rows.Add();

            // write row value
            for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
            {
                var cell = sh.GetRow(i).GetCell(j);

                if (cell != null)
                {
                    // TODO: you can add more cell types capatibility, e. g. formula
                    switch (cell.CellType)
                    {
                        case NPOI.SS.UserModel.CellType.Numeric:
                            DT.Rows[i][j] = sh.GetRow(i).GetCell(j).NumericCellValue;
                            //dataGridView1[j, i].Value = sh.GetRow(i).GetCell(j).NumericCellValue;

                            break;
                        case NPOI.SS.UserModel.CellType.String:
                            DT.Rows[i][j] = sh.GetRow(i).GetCell(j).StringCellValue;

                            break;
                    }
                }
            }

            i++;
        }

        return DT;
    }

Answer 6

private static ISheet GetFileStream(string fullFilePath)
    {
        var fileExtension = Path.GetExtension(fullFilePath);
        string sheetName;
        ISheet sheet = null;
        switch (fileExtension)
        {
            case ".xlsx":
                using (var fs = new FileStream(fullFilePath, FileMode.Open, FileAccess.Read))
                {
                    var wb = new XSSFWorkbook(fs);
                    sheetName = wb.GetSheetAt(0).SheetName;
                    sheet = (XSSFSheet) wb.GetSheet(sheetName);
                }
                break;
            case ".xls":
                using (var fs = new FileStream(fullFilePath, FileMode.Open, FileAccess.Read))
                {
                    var wb = new HSSFWorkbook(fs);
                    sheetName = wb.GetSheetAt(0).SheetName;
                    sheet = (HSSFSheet) wb.GetSheet(sheetName);
                }
                break;
        }
        return sheet;
    }

    private static DataTable GetRequestsDataFromExcel(string fullFilePath)
    {
        try
        {
            var sh = GetFileStream(fullFilePath);
            var dtExcelTable = new DataTable();
            dtExcelTable.Rows.Clear();
            dtExcelTable.Columns.Clear();
            var headerRow = sh.GetRow(0);
            int colCount = headerRow.LastCellNum;
            for (var c = 0; c < colCount; c++)
                dtExcelTable.Columns.Add(headerRow.GetCell(c).ToString());
            var i = 1;
            var currentRow = sh.GetRow(i);
            while (currentRow != null)
            {
                var dr = dtExcelTable.NewRow();
                for (var j = 0; j < currentRow.Cells.Count; j++)
                {
                    var cell = currentRow.GetCell(j);

                    if (cell != null)
                        switch (cell.CellType)
                        {
                            case CellType.Numeric:
                                dr[j] = DateUtil.IsCellDateFormatted(cell)
                                    ? cell.DateCellValue.ToString(CultureInfo.InvariantCulture)
                                    : cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
                                break;
                            case CellType.String:
                                dr[j] = cell.StringCellValue;
                                break;
                            case CellType.Blank:
                                dr[j] = string.Empty;
                                break;
                        }
                }
                dtExcelTable.Rows.Add(dr);
                i++;
                currentRow = sh.GetRow(i);
            }
            return dtExcelTable;
        }
        catch (Exception e)
        {
            throw;
        }
    }

Answer 7

由于您要求阅读并修改 xls 文件，我已更改@mj82 的答案以满足您的需求。

HSSFWorkbook 没有 Save 方法，但它有 Write 到流。

static void Main(string[] args)
        {
            string filepath = @"C:\test.xls";

            HSSFWorkbook hssfwb;

            using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read))
            {
                hssfwb = new HSSFWorkbook(file);
            }

            ISheet sheet = hssfwb.GetSheetAt(0);

            for (int row = 0; row <= sheet.LastRowNum; row++)
            {
                if (sheet.GetRow(row) != null) //null is when the row only contains empty cells 
                {
                    // Set new cell value
                    sheet.GetRow(row).GetCell(0).SetCellValue("foo");
                    Console.WriteLine("Row {0} = {1}", row, sheet.GetRow(row).GetCell(0).StringCellValue);
                }
            }

            // Save the file
            using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Write))
            {
                hssfwb.Write(file);
            }

            Console.ReadLine();
        }

Answer 8

如果您不想使用 NPOI.Mapper，那么我建议您查看此解决方案 - 它可以将 excel 单元格读取为各种类型，并且还有一个简单的导入帮助程序： https://github.com/hidegh/NPOI.Extensions

var data = sheet.MapTo<OrderDetails>(true, rowMapper =>
{
  // map singleItem
  return new OrderDetails()
  {
    Date = rowMapper.GetValue<DateTime>(SheetColumnTitles.Date),

    // use reusable mapper for re-curring scenarios
    Region = regionMapper(rowMapper.GetValue<string>(SheetColumnTitles.Region)),

    Representative = rowMapper.GetValue<string>(SheetColumnTitles.Representative),
    Item = rowMapper.GetValue<string>(SheetColumnTitles.Item),
    Units = rowMapper.GetValue<int>(SheetColumnTitles.Units),
    UnitCost = rowMapper.GetValue<decimal>(SheetColumnTitles.UnitCost),
    Total = rowMapper.GetValue<decimal>(SheetColumnTitles.Total),

    // read date and total as string, as they're displayed/formatted on the excel
    DateFormatted = rowMapper.GetValue<string>(SheetColumnTitles.Date),
    TotalFormatted = rowMapper.GetValue<string>(SheetColumnTitles.Total)
  };
});