编辑现有的Excel文件C#npoi [英] Edit existing Excel file C# npoi

问题描述

我想使用控制台应用程序C#打开一个现有的excel文件并向其中添加内容.NPOI 2.1.1.0

I want to with a console application C# open an existing excel file and add content to it. NPOI 2.1.1.0

我的第一种方法只是在我解决的最后一个单元格图形上添加一个值，这将解决我的其他问题.
这将正确读取包含新内容的文件，但不会保存该文件.

My first approach was simply to add a value to last cell figure I solved that it will solve my other problem.
This will read the file correctly with the new content but it will not save it.

输出:

无法访问关闭的文件.".

"Cannot access a closed file.".

HSSFWorkbook hssfwb;
using (FileStream file = new FileStream(@"c:\testfile.xls", FileMode.Open, FileAccess.ReadWrite))
{
    hssfwb = new HSSFWorkbook(file);
    ISheet sheet = hssfwb.GetSheetAt(0);
    IRow row = sheet.GetRow(0);

    sheet.CreateRow(row.LastCellNum);
    ICell cell = row.CreateCell(row.LastCellNum);
    cell.SetCellValue("test");

    for (int i = 0; i < row.LastCellNum; i++)
    {
        Console.WriteLine(row.GetCell(i));
    }
    hssfwb.Write(file);
    file.Close();
}

我确实尝试添加以下内容，但是导致空白文件无法打开.

I did try add the following but it resulted in a blank file that wont open.

MemoryStream mstream = new MemoryStream();
hssfwb.Write(mstream);

byte[] bytes = new byte[mstream.Length];
mstream.Read(bytes, 0, (int)mstream.Length);
file.Write(bytes, 0, bytes.Length);
file.Close();
mstream.Close();

我还尝试将hssfwb单独放在使用范围内，将文件模式更改为Append/openorcreate/truncate，如果写入检查没有任何结果，则将其添加.

I also tried putting the hssfwb alone within the using scope, changed the filemode to Append/openorcreate/truncate, added if write check without any result.

但是，一种解决方案是读取文件，将其转换为数据表，创建新的Excel文件并用数据填充它.

However one solution would be to read the file, convert it into an datatable, create a new excel file and populate it with the data.

我做错了什么?谁能对此有所启发?我徒劳地看了看Npoi示例包.

What am I doing wrong? Can anyone shed some light on this? I'v looked over the Npoi Example package in vain.

推荐答案

好吧，看起来 HSSFWorkbook 构造函数从文件流中读取后就关闭了它.

Well, it looks like HSSFWorkbook constructor closes filestream after reading from it.

最简单直接的解决方案-打开文件进行读取，创建 HSSFWorkbook ，在该工作簿中执行所需的操作，然后再次打开文件进行写入和写入.

Simpliest and straightforward solution - open file for reading, create HSSFWorkbook, do what you want in that workbook, and then open file again for writing and write to it.

在 using 范围之外使用 hssfwb 是安全的，因为 HSSFWorkbook 本身并不保存对从中读取文件的引用(因为只要我在NPOI来源中看到它即可.

It is safe to use hssfwb outside of using scope because HSSFWorkbook itself does not holds reference to file from which it was read (as long as I see it in NPOI sources).

因此您的代码应如下所示:

So your code could look like:

HSSFWorkbook hssfwb;
using (FileStream file = new FileStream(@"c:\temp\testfile.xls", FileMode.Open, FileAccess.Read))
{
    hssfwb = new HSSFWorkbook(file);
    file.Close();
}

ISheet sheet = hssfwb.GetSheetAt(0);
IRow row = sheet.GetRow(0);

sheet.CreateRow(row.LastCellNum);
ICell cell = row.CreateCell(row.LastCellNum);
cell.SetCellValue("test");

for (int i = 0; i < row.LastCellNum; i++)
{
    Console.WriteLine(row.GetCell(i));
}

using (FileStream file = new FileStream(@"c:\temp\testfile.xls", FileMode.Open, FileAccess.Write))
{
    hssfwb.Write(file);
    file.Close();
}

这篇关于编辑现有的Excel文件C#npoi的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！