The issue was not really something to do with code, but with sheet name . Since one of the sheet had % character in the name , this caused an issue , after removing % in the sheet name it worked fine.
On Mon, May 23, 2022 at 10:56 AM Vicky B <[email protected]> wrote: > 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* > -- *Thanks & Regards Vickyb*
