When reading or modifying some user-created .xlsx files, I get the following error message:
阅读或修改一些用户创建的.xlsx文件时,我收到以下错误消息:
We found a problem with some content in 'test.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.
Clicking Yes gets me another message:
单击是获取另一条消息:
Excel cannot open the file 'test.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
Example of a problem .xlsx file here (before put in NPOI).
此处出现问题.xlsx文件的示例(在放入NPOI之前)。
Here's the same file, now corrupted after being read from and written back with iWorkbook.Write(filestream); here.
这是同一个文件,现在在用iWorkbook.Write(文件流)读取和写回后损坏;这里。
I have no issues creating a new .xlsx file with the following code:
使用以下代码创建新的.xlsx文件时没有问题:
string newPath = @"C:\MyPath\test.xlsx";
using (FileStream fs = new FileStream(newPath, FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
wb.CreateSheet();
ISheet s = wb.GetSheetAt(0);
IRow r = s.CreateRow(0);
r.CreateCell(0);
ICell c = r.GetCell(0);
c.SetCellValue("test");
wb.Write(fs);
fs.Close();
}
That works fine.
这很好。
Even opening one of the problem child .xlsx files, setting it to an IWorkbook and writing it back to the file works:
即使打开一个问题子.xlsx文件,将其设置为IWorkbook并将其写回文件也可以:
string newPath = @"C:\MyPath\test.xlsx";
using (FileStream fs = new FileStream(newPath, FileMode.Open, FileAccess.ReadWrite))
{
IWorkbook wb = new XSSFWorkbook(fs);
wb.Write(fs);
fs.Close();
}
However, after running through code that reads from it, gets ISheets, IRows, ICells, etc.... it corrupts the .xlsx file. Even though I specifically removed anything that modifies the workbook. No Creates, Sets, Styles, etc. with NPOI.
但是,在运行从中读取的代码之后,获取ISheets,IRows,ICell等等......它会破坏.xlsx文件。即使我专门删除了修改工作簿的任何内容。 NPOI没有创建,设置,样式等。
I can't really include my code because it would just be confusing, but for the sake of completeness I'm really only using the following types and functions from NPOI during this test:
我不能真正包含我的代码,因为它只会令人困惑,但为了完整起见,我在这个测试中只使用NPOI的以下类型和函数:
IWorkbook
XSSFWorkbook
ISheet
IRow
ICell
.GetSheetAt
.GetRow
.GetCell
.LastRowNum
So one of those causes corruption. I would like to eventually set values again and get it working like I have for .xls.
因此其中一个导致腐败。我想最终再次设置值,让它像我对.xls一样工作。
Has anyone experienced this? What are some NPOI functions that could cause corruption? Any input would be appreciated.
有没有人经历过这个?什么是可能导致腐败的NPOI功能?任何输入将不胜感激。
Edit: Using NPOI v2.2.1.
编辑:使用NPOI v2.2.1。
1 个解决方案
#1
3
I think the problem is that you are reading from, and writing to, the same FileStream. You should be doing the read and write using separate streams. Try it like this:
我认为问题在于您正在读取和写入相同的FileStream。您应该使用单独的流进行读写。试试这样:
string newPath = @"C:\MyPath\test.xlsx";
// read the workbook
IWorkbook wb;
using (FileStream fs = new FileStream(newPath, FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(fs);
}
// make changes
ISheet s = wb.GetSheetAt(0);
IRow r = s.GetRow(0) ?? s.CreateRow(0);
ICell c = r.GetCell(1) ?? r.CreateCell(1);
c.SetCellValue("test2");
// overwrite the workbook using a new stream
using (FileStream fs = new FileStream(newPath, FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
#1
3
I think the problem is that you are reading from, and writing to, the same FileStream. You should be doing the read and write using separate streams. Try it like this:
我认为问题在于您正在读取和写入相同的FileStream。您应该使用单独的流进行读写。试试这样:
string newPath = @"C:\MyPath\test.xlsx";
// read the workbook
IWorkbook wb;
using (FileStream fs = new FileStream(newPath, FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(fs);
}
// make changes
ISheet s = wb.GetSheetAt(0);
IRow r = s.GetRow(0) ?? s.CreateRow(0);
ICell c = r.GetCell(1) ?? r.CreateCell(1);
c.SetCellValue("test2");
// overwrite the workbook using a new stream
using (FileStream fs = new FileStream(newPath, FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}