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 <[email protected]> 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: [email protected]
> For additional commands, e-mail: [email protected]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
