POI：追加行现有工作簿 [英] POI: Append rows to existing workbook
问题描述
使用XSSFWorkbook，是有可能追加行到现有的表？我做的多次写入（这是一个PITA解决由于错误），虽然我可以多次写出来的新表，它不会出现，我可以追加。
什么我目前做的是以下内容：
- 阅读表到我的工作簿。
- 加载工作簿。
- 行附加到内存中工作簿
- 重新写出来的。
4似乎不工作，只是忽略它彻底！
我知道SXSSFWorkbook存在，但试图把我的现有XSSFWorkbook转换成一个工作簿流写入时产生腐败。
是否有可能解决这一concundrum？
更新：改变code的基础上建议，但得到的流关闭错误
。code：（物理行返回正确，但没有被写出来）
私人无效writeToSheetMultipleTimes（SXSSFWorkbook WB，
ReportTemplateStructure的appA，文件wbFile）
{ 苫布苫布= wb.getSheetAt（0）; log.info（试图多写表：+ sheet.getSheetName（））;
的for（int i = 0;我小于10;我++）
{ 鳞次栉比= sheet.getRow（I） 如果（行== NULL）{
行= sheet.createRow（I）
}
细胞细胞= row.getCell（0，Row.CREATE_NULL_AS_BLANK）; cell.setCellValue（写值：+ I）; INT其行= sheet.getPhysicalNumberOfRows（）; log.info（当前的行数：+其行）; 尝试{
XSSFWorkbook xssfBook =（XSSFWorkbook）writeOutAndReadBack（WB）;
wb.dispose（）; WB =新SXSSFWorkbook（xssfBook）;
}赶上（例外五）
{
log.error（无法执行到同一张纸上多次写，E）;
}
}
} 公共工作簿writeOutAndReadBack（练习册WB）{
如果（！（WB的instanceof SXSSFWorkbook））{
抛出新抛出：IllegalArgumentException（预期SXSSFWorkbook的实例）;
} 工作簿的结果;
尝试{
FileOutputStream中BAOS =新的FileOutputStream（streamingWorkBookFile）;
wb.write（BAOS）;
InputStream为=新的FileInputStream（streamingWorkBookFile）;
结果=新XSSFWorkbook（是）; }赶上（IOException异常五）{
抛出新的RuntimeException（E）;
}
返回结果;
}
您似乎总是在更改表0，但你打电话 createRow 每次。这将不顺利，如果有一些已经存在，例如在你第二遍！你要么需要每次添加一个新的工作表，或检查如果该行被那里为的getRow（INT）第一和电话，如果它是空只能创建。
如果我们看一下您的code片断：
表表= wb.getSheetAt（0）;
的for（int i = 0;我小于10;我++）
{
鳞次栉比= sheet.createRow（I）
细胞细胞= row.createCell（0）;
这要么是这样的：
表表= wb.createSheet（）;
的for（int i = 0;我小于10;我++）
{
鳞次栉比= sheet.createRow（I）
细胞细胞= row.createCell（0）;
或者，你应该检查第一个也是唯一创建丢失行/细胞，如：
表表= wb.getSheetAt（0）;
的for（int i = 0;我小于10;我++）
{
鳞次栉比= sheet.getRow（I）
如果（行== NULL）{
行= sheet.createRow（I）
}
细胞细胞= row.getCell（0，Row.CREATE_NULL_AS_BLANK）;
Using XSSFWorkbook, is it possible to append rows to an existing sheet? I am doing multiple writes (which was a PITA to solve due to a bug) and while I can write out new sheets multiple times, it does not appear that I can append.
What I am currently doing is the following:
- Read sheet to my workbook.
- Load workbook.
- Append rows to workbook in memory
- Write out again.
4 Does not appear to work, just ignores it completely!
I am aware that SXSSFWorkbook exists, but attempting to convert my existing XSSFWorkbook into a streaming workbook creates corruption upon write.
Is it possible to solve this concundrum?
Update: Changed code based on suggestion, but getting stream closed error.
Code: (The physical rows returns correctly, but nothing gets written out)
private void writeToSheetMultipleTimes(SXSSFWorkbook wb,
ReportTemplateStructure appA, File wbFile)
{
Sheet sheet = wb.getSheetAt(0);
log.info("Attempting multi-write to sheet: " + sheet.getSheetName());
for(int i = 0; i < 10; i++)
{
Row row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
Cell cell = row.getCell(0, Row.CREATE_NULL_AS_BLANK);
cell.setCellValue("Written value:" + i);
int numRows = sheet.getPhysicalNumberOfRows();
log.info("Current row count: " + numRows);
try{
XSSFWorkbook xssfBook = (XSSFWorkbook)writeOutAndReadBack(wb);
wb.dispose();
wb = new SXSSFWorkbook(xssfBook);
} catch (Exception e)
{
log.error("Unable to perform multiple write to same sheet", e);
}
}
}
public Workbook writeOutAndReadBack(Workbook wb) {
if(!(wb instanceof SXSSFWorkbook)) {
throw new IllegalArgumentException("Expected an instance of SXSSFWorkbook");
}
Workbook result;
try {
FileOutputStream baos = new FileOutputStream(streamingWorkBookFile);
wb.write(baos);
InputStream is = new FileInputStream(streamingWorkBookFile);
result = new XSSFWorkbook(is);
} catch (IOException e) {
throw new RuntimeException(e);
}
return result;
}
You appear to be always making changes to Sheet 0, but you're calling createRow every time. This won't go well if there's already something there, eg on your second pass! You either need to add a new Sheet every time, or check if the Row is there with a call to getRow(int) first and only create if it is null.
If we look at your code snippet:
Sheet sheet = wb.getSheetAt(0);
for(int i = 0; i < 10; i++)
{
Row row = sheet.createRow(i);
Cell cell = row.createCell(0);
That should either be something like:
Sheet sheet = wb.createSheet();
for(int i = 0; i < 10; i++)
{
Row row = sheet.createRow(i);
Cell cell = row.createCell(0);
Or you should check first and only create missing rows/cells, eg
Sheet sheet = wb.getSheetAt(0);
for(int i = 0; i < 10; i++)
{
Row row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
Cell cell = row.getCell(0, Row.CREATE_NULL_AS_BLANK);
这篇关于POI：追加行现有工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
