You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by margotsunshine <ma...@mail.nih.gov> on 2012/11/13 21:42:10 UTC
Excel 2010 error writing new header in existing table - 'Excel
found unreadable content in ...'
I am receiving the following error: “Excel found unreadable content in
‘myfile.xlsx’. Do you want to recover the contents of this workbook?” when I
open my workbook. I have read numerous posts about this error message but
the issue does not seem to be the same as the one I am having. I am able to
open it anyway and everything looks fine but I can’t release it to my users
that way. I receive the message “Repaired Records: Table from
/xl/tables/table2.xml part (Table)”. I have narrowed it down to one line
that is causing the error but I need to do what that line is doing! Maybe
someone can tell me another way to do it. I start with a template that
contains some predefined tables and charts. When I update the column headers
with a new cell value I receive the error. The setting of the table values
causes no problem. Is there some special code to update table headers? I am
simply using the XSSFCell cell.setCellValue(“column name”);'
I have attached my template and a simple java class that produces the error
if anyone is willing to look at it.
I am using the poi-3.8-20120326.jar and associated similarly dated jar
files.
I am running window xp
Thanks for your help,
Margot
Zscore_2010_template.xlsx
<http://apache-poi.1045710.n5.nabble.com/file/n5711478/Zscore_2010_template.xlsx>
Excel2010Test.java
<http://apache-poi.1045710.n5.nabble.com/file/n5711478/Excel2010Test.java>
Excel2010Workbook.java
<http://apache-poi.1045710.n5.nabble.com/file/n5711478/Excel2010Workbook.java>
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Excel-2010-error-writing-new-header-in-existing-table-Excel-found-unreadable-content-in-tp5711478.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Excel 2010 error writing new header in existing table - 'Excel
found unreadable content in ...'
Posted by Yegor Kozlov <ye...@dinom.ru>.
Your are updating a region of cells that belongs to a Table. Open your
input template in Excel and click on cell A83. You should see Table
Tools in the toolbar ribbon with the table name ProbeIntensities and
cell range $A$83:$B$150.
It turns out that table definition *must* be synchronyzed with the
worksheet data, otherwise Excel fails to load the file.
On the low level table data loks as follows, see /xl/tables/table2.xml :
<tableColumns count="2">
<tableColumn id="1" name=" " dataDxfId="262"/>
<tableColumn id="2" name="Probe" dataDxfId="261"/>
</tableColumns>
The name attribute refers to the header cel and if you change that cel
in the worksheet you must update the table too.
So after update the table should look like this:
<tableColumns count="2">
<tableColumn id="1" name="Header1" dataDxfId="262"/>
<tableColumn id="2" name="Header2" dataDxfId="261"/>
</tableColumns>
I committed the fix in r1416166. Now POI automatically updates table
headers. Please try with the latest build from trunk, the link to
daily builds is on http://poi.apache.org/
If you are using an older version of POI you have to care about
tables yourself and call updateTableHeaders procedure before saving
worksheet.
Here is a simplified version of your code that demonstrates the approach:
public class Main {
public static void main(String[] args) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook("Zscore_2010_template.xlsx");
int headerRow = 82;
XSSFSheet sheet = wb.getSheet("gene");
XSSFRow row = sheet.getRow(headerRow);
for (int i=1; i < 60; i++) {
XSSFCell cell = row.getCell(i);
if (cell == null) {
cell = row.createCell(i);
}
cell.setCellValue("Header"+ i);
}
//must sync table headers before saving
updateTableHeaders(sheet);
FileOutputStream out = new FileOutputStream("test/test2.xlsx");
wb.write(out);
out.close();
}
static void updateTableHeaders(XSSFSheet sheet){
for(XSSFTable tbl : sheet.getTables()){
CellReference ref = tbl.getStartCellReference();
int headerRow = ref.getRow();
int firstHeaderColumn = ref.getCol();
XSSFRow row = sheet.getRow(headerRow);
if(row != null) for(CTTableColumn col :
tbl.getCTTable().getTableColumns().getTableColumnList()){
int colIdx = (int)col.getId() - 1 + firstHeaderColumn;
XSSFCell cell = row.getCell(colIdx);
if(cell != null) {
col.setName(cell.getStringCellValue());
}
}
}
}
}
Yegor
Yegor
On Mon, Nov 26, 2012 at 9:56 PM, margotsunshine <ma...@mail.nih.gov> wrote:
> Sorry. I have revised Excel2010Test.java to only need poi and standard java
> classes. I turned Excel2010Workbook into a slimmer embedded class. I also
> created a tiny junit test class that will run the whole thing and create the
> problem file if you're setup to run junit. Excel2010Test accessor is just
> passed a path to the template file.
>
> Thanks, Margot
>
> Excel2010Test.java
> <http://apache-poi.1045710.n5.nabble.com/file/n5711558/Excel2010Test.java>
> ExcelTest.java
> <http://apache-poi.1045710.n5.nabble.com/file/n5711558/ExcelTest.java>
> Zscore_2010_template.xlsx
> <http://apache-poi.1045710.n5.nabble.com/file/n5711558/Zscore_2010_template.xlsx>
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Excel-2010-error-writing-new-header-in-existing-table-Excel-found-unreadable-content-in-tp5711478p5711558.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Excel 2010 error writing new header in existing table - 'Excel
found unreadable content in ...'
Posted by margotsunshine <ma...@mail.nih.gov>.
Sorry. I have revised Excel2010Test.java to only need poi and standard java
classes. I turned Excel2010Workbook into a slimmer embedded class. I also
created a tiny junit test class that will run the whole thing and create the
problem file if you're setup to run junit. Excel2010Test accessor is just
passed a path to the template file.
Thanks, Margot
Excel2010Test.java
<http://apache-poi.1045710.n5.nabble.com/file/n5711558/Excel2010Test.java>
ExcelTest.java
<http://apache-poi.1045710.n5.nabble.com/file/n5711558/ExcelTest.java>
Zscore_2010_template.xlsx
<http://apache-poi.1045710.n5.nabble.com/file/n5711558/Zscore_2010_template.xlsx>
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Excel-2010-error-writing-new-header-in-existing-table-Excel-found-unreadable-content-in-tp5711478p5711558.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Excel 2010 error writing new header in existing table - 'Excel
found unreadable content in ...'
Posted by Yegor Kozlov <ye...@dinom.ru>.
What class and method should I call to reproduce the problem ?
The attached code depends on classes from the
gov.nih.nci.lmp.shared package. Can you post isolated code so that I
can compile and run?
Yegor
On Wed, Nov 14, 2012 at 12:42 AM, margotsunshine <ma...@mail.nih.gov> wrote:
> I am receiving the following error: “Excel found unreadable content in
> ‘myfile.xlsx’. Do you want to recover the contents of this workbook?” when I
> open my workbook. I have read numerous posts about this error message but
> the issue does not seem to be the same as the one I am having. I am able to
> open it anyway and everything looks fine but I can’t release it to my users
> that way. I receive the message “Repaired Records: Table from
> /xl/tables/table2.xml part (Table)”. I have narrowed it down to one line
> that is causing the error but I need to do what that line is doing! Maybe
> someone can tell me another way to do it. I start with a template that
> contains some predefined tables and charts. When I update the column headers
> with a new cell value I receive the error. The setting of the table values
> causes no problem. Is there some special code to update table headers? I am
> simply using the XSSFCell cell.setCellValue(“column name”);'
> I have attached my template and a simple java class that produces the error
> if anyone is willing to look at it.
> I am using the poi-3.8-20120326.jar and associated similarly dated jar
> files.
> I am running window xp
> Thanks for your help,
> Margot
> Zscore_2010_template.xlsx
> <http://apache-poi.1045710.n5.nabble.com/file/n5711478/Zscore_2010_template.xlsx>
> Excel2010Test.java
> <http://apache-poi.1045710.n5.nabble.com/file/n5711478/Excel2010Test.java>
> Excel2010Workbook.java
> <http://apache-poi.1045710.n5.nabble.com/file/n5711478/Excel2010Workbook.java>
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Excel-2010-error-writing-new-header-in-existing-table-Excel-found-unreadable-content-in-tp5711478.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org