Hi Team, The code that worked for one sheet now when applied for multiple sheets get warning message "we found problem with some content in excel" basically i still get Repair excel issue when i am trying to create the excel with multiple sheets but i don't get it when i use the same code for single sheet, not really sure what's wrong .
On Tue, May 17, 2022 at 4:01 PM Dominik Stadler <[email protected]> wrote: > Hi, > > I am glad that you could solve it. Unfortunately I actually know next to > nothing about this part of the MS formats so probably cannot tell why it > was happening and if there are better ways to fix it. > > Dominik. > > > On Mon, May 16, 2022 at 8:24 PM Vicky B <[email protected]> wrote: > > > 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* > > > -- *Thanks & Regards Vickyb*
