尝试使用NPOI编辑现有Excel文件的单元格值 [英] Trying to edit cell value of existing Excel file using NPOI

问题描述

我已经编写了以下代码来编辑使用C＃和NPOI库的Excel文件。没有错误，但运行代码后，如果我打开文件，单元格的值不会被编辑。我做错了什么？

I have written the following code to edit an Excel file using C# and NPOI library. There are no errors, but after running the code if I open the file, the value of the cell is not edited. What am I doing wrong?

namespace Project37
{
    class Class1
    {
        public static void Main()
        {
            string pathSource = @"C:\Users\mvmurthy\Downloads\VOExportTemplate.xlsx";

            FileStream fs = new FileStream(pathSource, FileMode.Open, FileAccess.ReadWrite); 
            HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
            HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("ImportTemplate");
            HSSFRow dataRow = (HSSFRow)sheet.GetRow(4);

            dataRow.GetCell(1).SetCellValue("foo");

            MemoryStream ms = new MemoryStream();
            templateWorkbook.Write(ms);
        }     
    }
}

推荐答案

您没有看到更改的主要原因是因为您正在将工作簿写入 MemoryStream 而不是写回文件。你应该做的是这样的：

The main reason that you are not seeing the changes is because you are writing the workbook to a MemoryStream instead of writing back to the file. What you should be doing is this:

1. 在<$ c中使用 FileStream $ c>读取模式以完全阅读电子表格文件;


2. 进行所需的更改，然后


3. 使用 FileStream 在中写入模式以写回文件（或者如果不想要的话可​​选择写入不同的文件破坏原来的 - 这可能会更好的测试）。

1. using a FileStream in Read mode to read the spreadsheet file completely;
2. making the changes you want, then
3. using a FileStream in Write mode to write back to the file (or optionally write to a different file if you don't want to destroy the original -- this may be better for testing).

另外请注意使用在使用实现 IDisposable （所有 Streams do）的类时使用语句。这将确保该文件已关闭，流中使用的所有资源都将被正确清理。

Also note it is good practice to use using statements when working with classes that implement IDisposable (all Streams do). This will ensure that the file is closed and all resources used by the stream are cleaned up properly.

我的代码中还有另一个问题，那就是你显然是试图使用 HSSFWorkbook 与 .xlsx 文件。那不行。 HSSFWorkbook （和所有 HSSF 类）用于 .xls 文件。如果您需要使用 .xlsx 文件，那么您应该使用 XSSFWorkbook 和相关的 XSSF 。请注意，两种类型的类实现了常见的接口，如 IWorkbook ， ISheet ， IRow 等，以帮助减少代码重复，如果您需要支持两种类型的文件。我建议尽可能使用它们。但是您可能会发现您仍然需要下载以访问某些未被界面覆盖的功能。

There is another problem I see in your code, and that is you are apparently trying to use an HSSFWorkbook with an .xlsx file. That won't work. HSSFWorkbook (and all HSSF classes) are for .xls files. If you need to work with .xlsx files, then you should be using XSSFWorkbook and related XSSF classes instead. Note that both flavors of classes implement common interfaces like IWorkbook, ISheet, IRow, etc. to help reduce code duplication should you need to support both types of files. I recommend using them where possible. But you may find you still need to downcast occasionally to access certain features that are not covered by the interfaces.

另一件事我应该提到：如果特定行 x 在原始工作簿中不包含单元格，则 GetRow（x）将返回null。类似地，如果单元格 y 为空，则 GetCell（y）将返回null。如果您想要能够设置单元格的值，则需要检查空值，并使用 CreateRow（x）和/或 CreateCell（y），以确保每个实体存在。

One other thing I should mention: if a particular row x contains no cells in the original workbook, then GetRow(x) will return null. Similarly, GetCell(y) will return null if cell y is empty. If you want to be able to set the value of the cell regardless, you will need to check for nulls and use CreateRow(x) and/or CreateCell(y) as appropriate to ensure each respective entity exists.

这是修改后的代码：

string pathSource = @"C:\Users\mvmurthy\Downloads\VOExportTemplate.xlsx";

IWorkbook templateWorkbook;
using (FileStream fs = new FileStream(pathSource, FileMode.Open, FileAccess.Read))
{
    templateWorkbook = new XSSFWorkbook(fs);
}

string sheetName = "ImportTemplate";
ISheet sheet = templateWorkbook.GetSheet(sheetName) ?? templateWorkbook.CreateSheet(sheetName);
IRow dataRow = sheet.GetRow(4) ?? sheet.CreateRow(4);
ICell cell = dataRow.GetCell(1) ?? dataRow.CreateCell(1);
cell.SetCellValue("foo");

using (FileStream fs = new FileStream(pathSource, FileMode.Create, FileAccess.Write))
{
    templateWorkbook.Write(fs);
}

这篇关于尝试使用NPOI编辑现有Excel文件的单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！