Thanks Dominik for checking the issue
I was able to resolve the issue. I think the problem was the way we create
the table and add column information. Please let me know if the code
changes are valid, also wanted to know how do we create tables when we want
to have dynamic no of rows and cells.
The code that caused the issue was once after creating the table we had to
assign Id and column names , in-order to that we used to add a new column .
CTTable cttable = table.getCTTable();
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));
}
Since we had already shared the start and end cell reference while
creating table like below
CellReference startCellReference = *new* CellReference(0, 0);
CellReference endCellReference = *new* CellReference(2,
colNames.size() - 1);
AreaReference areaReference = *new* AreaReference(
startCellReference, endCellReference,
SpreadsheetVersion.*EXCEL2007*);
XSSFTable table = sheet.createTable(areaReference);
Since table had the details of start and end cell values we really don’t
have add a new column , hence I changed the code to using existing
columns and changed whole code and this resolved the issue
XSSFSheet sheet = workbook.createSheet("Sheet");
List<String> colNames = Arrays.*asList*("Column1", "Column2",
"Column3");
CellReference startCellReference = *new* CellReference(0, 0);
CellReference endCellReference = *new* CellReference(2,
colNames.size() - 1);
AreaReference areaReference = *new* AreaReference(
startCellReference, endCellReference,
SpreadsheetVersion.*EXCEL2007*);
XSSFTable table = sheet.createTable(areaReference);
CTTable cttable = table.getCTTable();
CTTableColumns columns = cttable.getTableColumns();
columns.setCount(colNames.size());
*for* (*int* i = 1; i <= colNames.size(); i++) {
CTTableColumn column = columns.getTableColumnArray(i
-1);
column.setId(i);
column.setName(colNames.get(i - 1));
}
As mentioned earlier, please let me know if the code changes are valid and
what is the need to create new rows based on the records in the database
when I am not aware of any rows beforehand. How do we create tables in this
scenario?
Thanks,
Vicky
On Sun, May 15, 2022 at 5:10 AM Dominik Stadler <[email protected]>
wrote:
> Hi,
>
> can you try to narrow down the test-case so that it only does the bare
> minimum steps necessary to show the problem? Some of the steps may be
> unrelated, so making the code-snippet shorter would help us a lot to take a
> look.
>
> Also the formatting of the code-snippet is broken, maybe you can attach it
> or upload it somewhere?
>
> Thanks... Dominik.
>
> On Sat, May 14, 2022 at 3:33 AM Vicky B <[email protected]> wrote:
>
> > 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*
> >
>
--
*Thanks & Regards Vickyb*
