Hi All,
I am trying to create table in an excel sheet , when opening excel i get
message "We found problem with some content in excel, would you like to
recover".
When we open the exel file I do see the table with column but still i get
error
"Repair to Excel"
Removed Part: /xl/tables/table1.xml part with XML error. (Table) Load
error. Line 2, column 345.
Not sure what is the issue with code , I am using POI 5.2.2 , below is the
code , can you please let me know what is wrong with the code.
*import* org.apache.poi.ss.SpreadsheetVersion;
*import* org.apache.poi.ss.util.AreaReference;
*import* org.apache.poi.ss.util.CellReference;
*import* org.apache.poi.xssf.usermodel.XSSFCell;
*import* org.apache.poi.xssf.usermodel.XSSFRow;
*import* org.apache.poi.xssf.usermodel.XSSFSheet;
*import* org.apache.poi.xssf.usermodel.XSSFTable;
*import* org.apache.poi.xssf.usermodel.XSSFWorkbook;
*import* org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
*import* org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
*import* org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
*import*
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;
*public* *class* CreateTablePOI3 {
*private* *static* *void* createTable(XSSFSheet sheet, List<String>
colNames) {
CellReference startCellReference = *new* CellReference(0, 0);
// CellReference endCellReference = new CellReference(2,
colNames.size()); //one
// column too wide
CellReference endCellReference = *new* CellReference(2,
colNames.size() - 1);
// AreaReference areaReference
//
=xssfWorkBook.getCreationHelper().createAreaReference(startCellReference,
// endCellReference);
AreaReference areaReference = *new* AreaReference(
startCellReference, endCellReference,
SpreadsheetVersion.*EXCEL2007*);
XSSFTable table = sheet.createTable(areaReference);
CTTable cttable = table.getCTTable();
//
// CellReference startCellReference = new CellReference(0, 0);
// //CellReference endCellReference = new CellReference(2,
colNames.size()); //one column too wide
// CellReference endCellReference = new CellReference(2,
colNames.size()-1);
cttable.setDisplayName("SummaryData_" + sheet.getSheetName());
// cttable.setId(1); // Don't set table's Id manually. The
sheet.createTable()
// is doing that properly.
cttable.setName("SummaryData_" + sheet.getSheetName());
// cttable.setRef(areaReference.formatAsString());
cttable.setTotalsRowShown(*false*);
CTTableStyleInfo styleInfo = cttable.addNewTableStyleInfo();
styleInfo.setName("TableStyleMedium13");
styleInfo.setShowColumnStripes(*false*);
styleInfo.setShowRowStripes(*true*);
CTTableColumns columns = cttable.addNewTableColumns();
columns.setCount(colNames.size());
*for* (*int* i = 1; i <= colNames.size(); i++) {
CTTableColumn column = columns.addNewTableColumn();
column.setId(i);
column.setName(colNames.get(i - 1));
}
}
*public* *static* *void* main(String[] args) *throws* Exception {
List<String> sheetNames = Arrays.*asList*("Sheet1", "Sheet2",
"Sheet3");
XSSFWorkbook workbook = *new* XSSFWorkbook();
*for* (String sheetName : sheetNames) {
XSSFSheet sheet = workbook.createSheet(sheetName);
List<String> colNames = Arrays.*asList*("Column1",
"Column2", "Column3");
*createTable*(sheet, colNames);
*for* (*int* r = 0; r <= 2; r++) {
XSSFRow row = sheet.createRow(r);
*for* (*int* c = 0; c < colNames.size(); c++) {
XSSFCell cell = row.createCell(c);
// cell.setCellValue("some value");
//sheet's cell values must match the table's
// column names
*if* (r == 0) {
cell.setCellValue(colNames.get(c));
} *else* {
cell.setCellValue("some value");
}
}
}
*for* (*int* i = 0; i < colNames.size(); i++) {
sheet.autoSizeColumn(i);
}
}
FileOutputStream out = *new* FileOutputStream("test.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
}
--
*Thanks & Regards Vickyb*
