To add columns to a table you need to do two things:
1. update the "ref" attribute in CTTable which spe cifies the range of
cells this table applies to.
For example, if the old ref is "A83:B150" and you are adding 59
columns then the new value becomes "A83:BH150"
2. Add more tableColumn elements in the table. The dataDxfId attribute
isoptional and can be ommitted. It has to do with format to apply to
the data area of this column.
Below is a simplified version of your code that sets 60 headers on row
82 and expands the table appropriately.
package org.apache.poi.xssf.zscore;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import java.io.FileOutputStream;
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);
XSSFCellStyle headerStyle = row.getCell(0).getCellStyle();
for (int i = 1; i < 60; i++) {
XSSFCell cell = row.getCell(i);
if (cell == null) {
cell = row.createCell(i);
}
cell.setCellValue("Header" + i);
cell.setCellStyle(headerStyle);
}
XSSFTable tbl = getTable(sheet, headerRow);
CellReference ref1 = tbl.getStartCellReference();
CellReference ref2 = tbl.getEndCellReference();
// update the table. coumn headers must match the
corresponding cells in the sheet
int firstHeaderColumn = ref1.getCol();
CTTableColumns cols = tbl.getCTTable().getTableColumns();
cols.setTableColumnArray(null);
cols.setCount(61);
CTTableColumn col = cols.addNewTableColumn();
col.setName(" ");
col.setId(1);
for (int i = 1; i < 60; i++) {
col = cols.addNewTableColumn();
col.setName("Header" + i);
col.setId(i + 1);
}
// update the "ref" attribute
tbl.getCTTable().setRef(new CellRangeAddress(ref1.getRow(),
ref2.getRow(), ref1.getCol(), 59).formatAsString());
FileOutputStream out = new FileOutputStream("test2.xlsx");
wb.write(out);
out.close();
}
/**
* find a table with header starting on headerRow
*/
static XSSFTable getTable(XSSFSheet sheet, int headerRow) {
for (XSSFTable tbl : sheet.getTables()) {
CellReference ref = tbl.getStartCellReference();
int firstHeaderColumn = ref.getCol();
if (ref.getRow() == headerRow) return tbl;
}
return null;
}
}
Yegor
On Fri, Jan 11, 2013 at 12:26 AM, margotsunshine <[email protected]> wrote:
> I desperately need to know how to make this work. I return an Excel
> SpreadSheet containing the users results and I need to make these results
> 508 compliant. In order to do that I need my tables properly defined. I have
> tried numerous approaches for adding columns to existing tables and
> everything I try results in the entire table structure being blown away. I
> recieve the error “Excel found unreadable content in ‘myfile.xlsx’. Do you
> want to recover the contents of this workbook?”. When I say yes the
> spreadsheet opens and I get the message "Removed Part: /xl/tables/table2.xml
> part with XML error. (Table) Load error. Line 2, column 0." and the table
> structure is gone. The data is there but no table structure. I am attaching
> code that demonstrates this issue. Here is a snippet of the code from my
> test code.
> XSSFTable table = find2010Table(sheet, rowCnt - 1);
> CTTable cttable = table.getCTTable();
> CTTableColumns columns = cttable.getTableColumns();
> CTTableColumn col;
> for (int i=1; i < 5; i++) {
> cell = prepareCell(sheet, rowCnt - 1, colCnt);
> cell.setCellValue("header"+ i);
> col = columns.addNewTableColumn();
> col.setName("header"+ i);
> System.out.println("column count " + columns.getCount());
> columns.setTableColumnArray(colCnt, col);
> System.out.println("column count after set array " +
> columns.getCount());
> colCnt++;
> }
> cttable.setTableColumns(columns);
> I have run it in a debugger and every time I add a column to the table the
> column count remains the same (columns.getCount()) - even when I use the
> columns.setTableColumnArray(colCnt, col). When I look at the xml in the
> debugger, the column from the original table looks like this:
> "<xml-fragment id="1" name=" " dataDxfId="6"/>"
> The one I add looks like this:
> <xml-fragment name="header1"/>
> I see that you can set the id but what should it be? the column number of
> the column? I don't see how to set the dataDxFld. How is that supposed to be
> generated?
>
> What am I missing?
>
> Sincerely,
> Margot Sunshine Excel2010Test.java
> <http://apache-poi.1045710.n5.nabble.com/file/n5711832/Excel2010Test.java>
> ExcelTest.java
> <http://apache-poi.1045710.n5.nabble.com/file/n5711832/ExcelTest.java>
> Zscore_2010_template.xlsx
> <http://apache-poi.1045710.n5.nabble.com/file/n5711832/Zscore_2010_template.xlsx>
>
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Error-when-adding-new-columns-to-existing-tables-in-Excel-2010-tp5711832.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]
