excel 2007 unreadable content using apache poi (.xlsx)

Question

I'm having issue using the apache poi for excel 2007 utilizing the XSSF

I have two code snippets below (fat-fingered). The first seems to work, as the workbook opens correctly in excel 2007, and there is some change.

If I run the second code snippet then open in excel 2007, I get an error about unreadable content. I have to click a confirmation box, then excel opens my file.

Why is the second code snippet causing this error? the only difference is in the second one I am trying to shift all rows from row 2(zero indexed) up two rows...

All i really want to do is remove some header rows, and after I get that down some footer rows from a file. I'm not doing anything fancy. I'm guessing I'm misunderstanding the API, but I've been fighting with this for a while now.

Any ideas? Also should I be shifting the rows, or removing them, or some combination of both?

String filename = "C:\\file.xlsx";
FileInputStream file = new FileInputStream( new File(fileName) );

XSSFWorkbook wb = new XSSFWorkbook(file);

XSSFSheet sheet = wb.getSheetAt(0);
int lastRow = sheet.getlastRow();

sheet.shiftRows(1, lastRow, -1);


file.close()
FileOutputStream out = new FileOutputStream(fileName);
wb.write(out);
out.flush();
out.close();

String filename = "C:\\file.xlsx";
FileInputStream file = new FileInputStream( new File(fileName) );

XSSFWorkbook wb = new XSSFWorkbook(file);

XSSFSheet sheet = wb.getSheetAt(0);
int lastRow = sheet.getlastRow();

sheet.shiftRows(2, lastRow, -2);


file.close()
FileOutputStream out = new FileOutputStream(fileName);
wb.write(out);
out.flush();
out.close();

Edit: Actually it apears that the first snippet is also causing an issue...sometimes???? I'm under the impression I am doing something wrong... Any suggestions?

That's error is because your code is not writing excel sheet properly. You might misplace the position of a cell/row. Such kind of errors are common. You need to verify that in your code, probably in this method - sheet.shiftRows(2, lastRow, -2);. — Lion
I'm aware that something is wrong. I simply don't understand why. The javadoc doesn't tell much, and I can't dfind documentation on doign this. — Brian
.... when I "See what happens" I "get an error about unreadable content. I have to click a confirmation box, then excel opens my file." I have made that clear. The issue is likely with my code, this has been established. I have posted this question in hopes that someone with more experience than me with poi can recognize the issue.... Further if you read the question you would see I am not writing cells/rows but simply trying to remove them from an existing file. — Brian
Could it be a bug: issues.apache.org/bugzilla/buglist.cgi?quicksearch=shiftRows — Darius X.
Gosh, I'd hope not, although bug Bug 53798 does sound familiar... I'd hate to think that such simple behavior isn't worked out in the API. I have been under the, perhaps false, assumption that apache poi is relatively robust and well developed. — Brian

Sandro Sandro · Accepted Answer · 2015-02-18T11:11:20

I have run into a similar problem. I think that the problem might lie with the handling of comments. In my case, the excel file was reported as "damaged" by Excel whenever a shift was done when comments were present. Excel repaired the file and reported that it deleted the comments part and the rest was fine. Excel files without comments but with the same processing code, did not cause any problems.

Sorry that this does not solve the problem, but maybe it provides for a workaround. In my case, I delete all the comments programmatically (ugly).

excel 2007 unreadable content using apache poi (.xlsx)

1 Answers