Pdf conversiion throwing error-XMLStreamException: Unexpected end of input block in start tag

Hi,


Sorry there is still no update on it. However, I have logged your concerns against your issue id “CELLSJAVA-40540”. I have asked the relevant developer to update on it or share an eta (if possible). Once we receive any response from him, we will let you know here immediately.

Sorry for any inconvenience caused!

Hi

any news on the issues?

Just for your reference, This is high priority issue for us, So please provide the ETA on this so that we can plan accordingly.



Thanks

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid, there is no update for you at this moment. However, we have logged your comment against this issue. We have requested the development team to provide ETA or update. Once, we will have some update for you, we will let you know asap.

Hi,


Please try the latest fix/version: Aspose.Cells for Java v7.5.0.2, we have fixed the issue now.

Let us know your feedback.

Thank you.

Thanks for the update.


Hi thanks for the reply and fix.

The exception while saving the file is fixed. now i am not getting any exception while opening excel after refreshing the pivot tables and saving it. But regarding data,

issue 1# the column headers are not coming up in PDF. (If i change the color to standard color instead of ribbon eg:RED its coming up ).
issue 2# some data is not coming up in PDF.
the higlighted data in excel with yellow color is coming wrongly in PDF. But if i see the print preview its coming up correctly.

Please look into these issues.

i am using the below code:

Workbook workbook = new Workbook(excelFileName);


// execute workbook formulas before pdf generation
//workbook.calculateFormula(true);

// Get the count of the worksheets in the workbook
int sheetCount = workbook.getWorksheets().getCount();
// loop through all wroksheets
for (int i = 0; i < sheetCount; i++) {
Worksheet ws = workbook.getWorksheets().get(i);
//if(excelFileName.contains(“Current_Plan”)){
//System.out.println(“current plan report block so skip the formatting…”);
//}else{

ws.autoFitRows();
ws.getPageSetup().setFitToPagesWide(10);
ws.getPageSetup().setFitToPagesTall(1);
ws.getPageSetup().setPaperSize(40);
ws.getPageSetup().setOrientation(1);
ws.getPageSetup().setZoom(20);
// }
ArrayList colsFromExcel1 = new ArrayList();
//if (excelFileName.contains(“Extract”)) {

Row row1 = ws.getCells().getRow(0);
Iterator im1 = row1.iterator();
while (im1.hasNext()) {
Cell cell = (Cell) im1.next();
colsFromExcel1.add(cell.getStringValue());
}
System.out.println(“cols size after formatting:”
+ colsFromExcel1.size());
//}

System.out.println(“in start…”);

for (int k = 0; k < ws.getPivotTables().getCount(); k++) {
System.out.println(“in start…”+k);
ws.getPivotTables().get(k)
.setRefreshDataOnOpeningFile(true);
ws.getPivotTables().get(k).setRefreshDataFlag(true);
if (excelFileName.contains(“Extract”)) {

} else {
ws.getPivotTables().get(k).refreshData();
}
try{
ws.getPivotTables().get(k).calculateData();
}catch(Exception el){
el.printStackTrace();

}
ws.getPivotTables().get(k).setRefreshDataFlag(false);
}


//if (excelFileName.contains(“Extract”)) {
for (int v = 0; v < colsFromExcel1.size(); v++) {
ws.autoFitColumn(v);
//ws.autoFitRows();
// if(v==0) continue;
System.out.println(“total count of rows:”+ws.getCells().getRows().getCount());
if(ws.getCells().getRows().getCount()==1 || ws.getCells().getRows().getCount()==0){
if(ws.getCells().getRows().getRowByIndex(0).get(v).getDisplayStringValue()!=""){
System.out.println(ws.getCells()
.getRows().getRowByIndex(0).getCellByIndex(v)
.getType());


// Style style = null;
StyleFlag flagin = null;
flagin = new StyleFlag();
Style style = workbook.getWorksheets().get(i)
.getCells().getRows().getRowByIndex(0)
.getStyle();
style.setCustom("@");
flagin.setNumberFormat(true);
// sheet.Cells.ApplyColumnStyle(dateCol.Column,
// style, flag);
ws.getCells()
.getColumns().getColumnByIndex(v)
.applyStyle(style, flagin);




}

}else{

if(ws.getCells().getRows().getRowByIndex(1).get(v).getDisplayStringValue()!=""){
System.out.println(ws.getCells()
.getRows().getRowByIndex(1).getCellByIndex(v)
.getType());


// Style style = null;
StyleFlag flagin = null;
flagin = new StyleFlag();
Style style = workbook.getWorksheets().get(i)
.getCells().getRows().getRowByIndex(1)
.getStyle();
style.setCustom("@");
flagin.setNumberFormat(true);
// sheet.Cells.ApplyColumnStyle(dateCol.Column,
// style, flag);
ws.getCells()
.getColumns().getColumnByIndex(v)
.applyStyle(style, flagin);




}

}

}

}
//}
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
if(excelFileName.contains(“Current_Plan”)){
System.out.println(“current plan report block so one page per sheet enabled…”);
//pdfSaveOptions.setOnePagePerSheet(true);
}



workbook.save(getPdfFileName(excelFileName));

Hi

i am able to reproduce the Unreadable content exception again with another report.

exception:java.lang.IndexOutOfBoundsException: Index: 27, Size: 27
at java.util.ArrayList.RangeCheck(Unknown Source)
at java.util.ArrayList.get(Unknown Source)
at com.aspose.cells.PivotFieldCollection.get(Unknown Source)
at com.aspose.cells.zs.m(Unknown Source)
at com.aspose.cells.zs.l(Unknown Source)
at com.aspose.cells.zs.a(Unknown Source)
at com.aspose.cells.aab.g(Unknown Source)
at com.aspose.cells.aab.b(Unknown Source)
at com.aspose.cells.aab.u(Unknown Source)
at com.aspose.cells.aab.a(Unknown Source)
at com.aspose.cells.aaa.a(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.(Unknown Source)
at com.steelwedge.util.demo.generatePdfReport1(demo.java:76)
at com.steelwedge.util.demo.main(demo.java:515)


Please find the attached reports.


Thanks

Hi,

[quote user="cagrawal"]Hi

i am able to reproduce the Unreadable content exception again with another report.

exception:java.lang.IndexOutOfBoundsException: Index: 27, Size: 27
at java.util.ArrayList.RangeCheck(Unknown Source)
at java.util.ArrayList.get(Unknown Source)
at com.aspose.cells.PivotFieldCollection.get(Unknown Source)
at com.aspose.cells.zs.m(Unknown Source)
at com.aspose.cells.zs.l(Unknown Source)
at com.aspose.cells.zs.a(Unknown Source)
at com.aspose.cells.aab.g(Unknown Source)
at com.aspose.cells.aab.b(Unknown Source)
at com.aspose.cells.aab.u(Unknown Source)
at com.aspose.cells.aab.a(Unknown Source)
at com.aspose.cells.aaa.a(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.<init>(Unknown Source)
at com.steelwedge.util.demo.generatePdfReport1(demo.java:76)
at com.steelwedge.util.demo.main(demo.java:515)


Please find the attached reports.


Thanks
[/quote]

Thanks for providing us the sample files.


After an initial test, I got the exception:
"exception:java.lang.IndexOutOfBoundsException" on opening an XLSX file.

Sample code:

Workbook workbook = new Workbook("_Audit_Trail_Report1.xlsx");

I have logged a ticket with an id "CELLSJAVA-40552" for your issue. We will look into your issue soon.

Once we have any update on it, we will let you know here.

Thank you.


Thanks for quick reply.

i placed the screen shots in a word doc (with reference to your excel file out_xlsx1.xlsx) i hope this will help you out.


Thanks

Hi,


Well, it may not help much. Since you are using FitToPages Tall/Wide options in your code to render long list of data in a single page (e.g first page), so it would be hard to see the data properly in the print preview of MS Excel. We request you to kindly provide your expected Excel file and output PDF file here that you may create/save them manually in MS Excel, it may help us a bit to evaluate your issue properly.

Thank you.

Hi

Thanks for the reply.

i have two issues
1)i have pivot tables in my excel i want to refresh my excel to see the latest data.
if i refresh the excel and generate the pdf i am not seeing the column header for the table. (see the pdf Current_Plan_Report_WITHDATA_with privt refresh.pdf)

some invalid data is being displayed as “grand total"

try {

//License license = new License();
/String licenseFilePath = Config.get(“ApplicationPath”)+ Constants.FILE_SEPARATOR + “WEB-INF”
+ Constants.FILE_SEPARATOR + “lib”
+ Constants.FILE_SEPARATOR + “Aspose.Cells.lic”;
System.out.println(“PDF licenseFilePath is::”+licenseFilePath);
license.setLicense(licenseFilePath);
/

// load excel file
Workbook workbook = new Workbook(excelFileName);


// execute workbook formulas before pdf generation
// workbook.calculateFormula(true);

// Get the count of the worksheets in the workbook
int sheetCount = workbook.getWorksheets().getCount();
// loop through all wroksheets
for (int i = 0; i < sheetCount; i++) {
Worksheet ws = workbook.getWorksheets().get(i);
//if(excelFileName.contains(“Current_Plan”)){
// System.out.println(“current plan report block so skip the formatting…”);
//}else{

// ws.autoFitRows();
// ws.getPageSetup().setFitToPagesWide(10);
// ws.getPageSetup().setFitToPagesTall(1);
// ws.getPageSetup().setPaperSize(40);
// ws.getPageSetup().setOrientation(1);
// ws.getPageSetup().setZoom(20);
// }
ArrayList colsFromExcel1 = new ArrayList();
//if (excelFileName.contains(“Extract”)) {

Row row1 = ws.getCells().getRow(0);
Iterator im1 = row1.iterator();
while (im1.hasNext()) {
Cell cell = (Cell) im1.next();
colsFromExcel1.add(cell.getStringValue());
}
System.out.println(“cols size after formatting:”
+ colsFromExcel1.size());
//}

System.out.println(“in start…”);

for (int k = 0; k < ws.getPivotTables().getCount(); k++) {
System.out.println(“in start…”+k);
ws.getPivotTables().get(k)
.setRefreshDataOnOpeningFile(true);
ws.getPivotTables().get(k).setRefreshDataFlag(true);
if (excelFileName.contains(“Extract”)) {

} else {
ws.getPivotTables().get(k).refreshData();
}
try{
ws.getPivotTables().get(k).calculateData();
}catch(Exception el){
el.printStackTrace();

}
ws.getPivotTables().get(k).setRefreshDataFlag(false);
}


//if (excelFileName.contains(“Extract”)) {
for (int v = 0; v < colsFromExcel1.size(); v++) {
ws.autoFitColumn(v);
// ws.autoFitRows();
// if(v==0) continue;
System.out.println(“total count of rows:”+ws.getCells().getRows().getCount());
if(ws.getCells().getRows().getCount()==1 || ws.getCells().getRows().getCount()==0){
if(ws.getCells().getRows().getRowByIndex(0).get(v).getDisplayStringValue()!=”"){
System.out.println(ws.getCells()
.getRows().getRowByIndex(0).getCellByIndex(v)
.getType());


// Style style = null;
StyleFlag flagin = null;
flagin = new StyleFlag();
Style style = workbook.getWorksheets().get(i)
.getCells().getRows().getRowByIndex(0)
.getStyle();
style.setCustom("@");
flagin.setNumberFormat(true);
// sheet.Cells.ApplyColumnStyle(dateCol.Column,
// style, flag);
ws.getCells()
.getColumns().getColumnByIndex(v)
.applyStyle(style, flagin);




}

}else{

if(ws.getCells().getRows().getRowByIndex(1).get(v).getDisplayStringValue()!=""){
System.out.println(ws.getCells()
.getRows().getRowByIndex(1).getCellByIndex(v)
.getType());


// Style style = null;
StyleFlag flagin = null;
flagin = new StyleFlag();
Style style = workbook.getWorksheets().get(i)
.getCells().getRows().getRowByIndex(1)
.getStyle();
style.setCustom("@");
flagin.setNumberFormat(true);
// sheet.Cells.ApplyColumnStyle(dateCol.Column,
// style, flag);
ws.getCells()
.getColumns().getColumnByIndex(v)
.applyStyle(style, flagin);




}

}

}

}
//}
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
if(excelFileName.contains(“Current_Plan”)){
System.out.println(“current plan report block so one page per sheet enabled…”);
// pdfSaveOptions.setOnePagePerSheet(true);
}



workbook.save(getPdfFileName(excelFileName));

} catch (Exception ex) {
System.out.println(“pdf exception:” + StackTraceUtil.getStackTrace(ex));
return false;
}



2) if i am not refreshing the excel and generating the pdf i am able to see the data but not able to see the latest data.
(see the pdf Current_Plan_Report_WITHDATA_without pivot refresh.pdf)

sample code:

try {
Workbook workbook = new Workbook(filename);
workbook.save(getPdfFileName(filename)) ;
} catch (Exception e) {
// TODO Auto-generated catch block


}



i am attaching the excel file from which i am generating the pdf.




Hi,

Thanks for the files.

Well, I think your pivot table cannot be refreshed properly whose source is based on some external data ranges which is not supported in the product at the moment. Do you confirm this? MS Excel also cannot perform "Refresh All" operation for the pivot table. And a ticket is already logged with an id "CELLSJAVA-40406" which is not fixed yet.

Thank you.

hi

Yes , we have pivot tables in excel for which the source will come from another sheet in same excel.




we can use refreshOnOPen=true in excel to refresh the excel pivot tables and other things to see the latest data which is not working for pdf generation Because aspose is generating the pdf before using the refreshonopen=true (see the excel which is generated).

how can i fix this issue is thre any other way to generate pdf with all the contents?

is thre any expected date for the issue CELLSJAVA-40406.??


Thanks



Hi,


Thanks for providing us details.

I have logged your provided details against your issue id “CELLSJAVA-40406” into our database. Our concerned developer will look into it soon. Unless we analyze your issue in details we cannot provide any eta or update on it. Hopefully, our developer will investigate the issue soon.

Once we have any update on it, we will update you here.

Thank you.

Hi,

We have analyzed your issue a bit and worked on it. I am afraid, we might not support refreshing pivot table whose source is based on some external data ranges. And, at the moment, we do not have plans at the moment to support this in near future too.


We are sorry for any inconvenience caused!


Thanks,

Hi,


The issue logged as “CELLSJAVA-40552” is fixed
in our latest version/fix Aspose.Cells for Java v7.5.0.3
: Aspose.Cells for Java v7.5.0.3., please try it.

Thank you.

Hi

External data ranges means, The pivot table is having the source in another sheet in same excel workbook? Aspose has some methods to refresh the pivot tables in a sheet? So These methods does’t support the refresh of the pivot tables?

i am following below steps:

1) refreshing the pivot tables in the excel sheet
2) saving the excel after refreshing
3) converting the excel to pdf



can you explain clearly what is the meaning of external source?

we have the source of the pivot table in the same workbook but in another sheet? So you mean ASPOSE doen’t support this feature?





Thanks

Hi,


Well, Aspose.Cells does support to refresh pivot tables whose source data is located in the same workbook, no matter whether it is in the same worksheet or in another sheet. It does not support to refresh or calculate pivot table/date whose source data is referred into other workbooks.

Please spare us a little time, we will look into your issue thoroughly and get back to you soon.

Thank you.

Hi,


To be precise, the External data ranges mean: A pivot table is having the source in another workbook or having a source that is referring to another source like SQL Server, etc.

After initial investigation, as we have checked it out, the pivot table in sheet “Current Plan Report” in template file “Current_Plan_Report_WITHDATA.xlsx” dose not use external range/cells, we may support (fix the issue) refreshing this type of pivot table. I have logged in a separate ticket with an id “CELLSJAVA-40555” for it, we will look into it to figure it out soon.

Once we have an update on it, we will let you know here.

Thank you.

Thanks for the analysis and reply amjad.