You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Vicky B <vi...@gmail.com> on 2022/05/14 01:33:48 UTC
Repair to Excel ( POI 5.2.2)
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*
Re: Repair to Excel ( POI 5.2.2)
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
Related web-pages state that percent is allowed but may need quoting, e.g.
https://www.keynotesupport.com/excel-basics/worksheet-names-characters-allowed-prohibited.shtml
Maybe you are using it in some reference where the sheet-name needs to be
quoted with single quotes?
Dominik.
On Mon, May 23, 2022 at 6:55 PM Vicky B <vi...@gmail.com> wrote:
> 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 <vi...@gmail.com> 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 <do...@gmx.at>
> > 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 <vi...@gmail.com> 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 <
> dominik.stadler@gmx.at
> >> >
> >> > 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 <vi...@gmail.com>
> 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*
>
Re: Repair to Excel ( POI 5.2.2)
Posted by Vicky B <vi...@gmail.com>.
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 <vi...@gmail.com> 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 <do...@gmx.at>
> 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 <vi...@gmail.com> 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 <dominik.stadler@gmx.at
>> >
>> > 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 <vi...@gmail.com> 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*
Re: Repair to Excel ( POI 5.2.2)
Posted by Vicky B <vi...@gmail.com>.
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 <do...@gmx.at>
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 <vi...@gmail.com> 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 <do...@gmx.at>
> > 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 <vi...@gmail.com> 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*
Re: Repair to Excel ( POI 5.2.2)
Posted by Dominik Stadler <do...@gmx.at>.
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 <vi...@gmail.com> 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 <do...@gmx.at>
> 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 <vi...@gmail.com> 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*
>
Re: Repair to Excel ( POI 5.2.2)
Posted by Vicky B <vi...@gmail.com>.
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 <do...@gmx.at>
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 <vi...@gmail.com> 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*
Re: Repair to Excel ( POI 5.2.2)
Posted by Dominik Stadler <do...@gmx.at>.
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 <vi...@gmail.com> 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*
>