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

Aspose.Cells Product Family
31

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.




32
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.
33

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



34

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.
35

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,

36

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.

37

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

38

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.
39

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.
40

Thanks for the analysis and reply amjad.



41

Hi

I analyzed the pivot issue a bit with latest version of aspose.

i am attaching the files:

1) Source excel file
2) PDF generated by manually saving the excel to pdf.
3) PDf generated by using ASPOSE.

i hope you can test the issue using these files. In the PDF generated by manually saving the excel to pdf every thing is good but in PDF generated using ASPOSE some data is missing (or wrongly generated).


Thanks

42

Hi,


Please find attached the output file using the code below with the latest version/fix v7.5.0.3.

Sample code:

String filePath=“Current_Plan_Report_WITHDATA.xlsx”;
Workbook workbook = new Workbook(filePath);
workbook.save(“outCurrent_Plan_Report_WITHDATA.pdf”,SaveFormat.PDF);



It looks like it works fine as per input Excel file.

Do you refresh the data and pivot table in your code, could you paste your code here, we will check it futher and precisely.


Thanks,

43

Thanks for the reply.

i am following below process to convert a excel file to PDF. I am refreshing the pivot tables in sheet because the data will come from another sheet (same workbook).

Step1: refresh the excel pivot tables and save the file with same name.
public static void refreshExcel(String excelFileName) {
try{
System.out.println(“11111”);
System.out.println(“before refresh call :” + excelFileName);

License license = new License();
String licenseFilePath =“D:\Aspose.Cells.lic”;
license.setLicense(licenseFilePath);



Workbook workbook = new Workbook(excelFileName);


workbook.calculateFormula(true);
int sheetCount = workbook.getWorksheets().getCount();
System.out.println(“sheet count…” + sheetCount);
for (int i = 0; i < sheetCount; i++) {
Worksheet ws = workbook.getWorksheets().get(i);
System.out.println(“worksheet name :”+ws.getName());
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);
// ws.getPivotTables().get(k).setRefreshDataFlag(false);

}

}

workbook.getSettings().setEncoding(Encoding.getUTF8());
workbook.save(excelFileName);




}catch(Exception e){
e.printStackTrace();
}



System.out.println(“after refresh call :” + excelFileName);
}


Step2: Use the refreshed excel and generate the PDF.

public static boolean generatePdfReport3(String excelFileName) {
try {

Workbook workbook = new Workbook(excelFileName);
int sheetCount = workbook.getWorksheets().getCount();

for (int i = 0; i < sheetCount; i++) {
Worksheet ws = workbook.getWorksheets().get(i);


ArrayList colsFromExcel1 = new ArrayList();


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());



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);




}

}

}

}



workbook.save(getPdfFileName(excelFileName));

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

44

Hi,


Thanks for providing us your code snippets.

I have logged it against your issue id “CELLSJAVA-40555” into our database, it may help us to investigate your issue accurately. Our concerned developer will look into it soon.

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

Thank you.
45

Hi

is there any ETA for this? Please provide if there is any ETA so that we can plan accordingly.


Thanks

46

Hi,

Please try the latest version/fix: Aspose.Cells for Java v7.5.0.5. It should fix your issue now.

Let us know your feedback.

Thank you.

47

The issues you have found earlier (filed as CELLSJAVA-40552;CELLSJAVA-40540;CELLSJAVA-40555) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
48

Hi

Thanks for the update.

i used the latest version of jar and found one issue while generating the pdf.


i am not getting the charts in the sheet and highlighted portion(yellow color) in excel are not getting generated properly in pdf.

refresh the _current_plan_report_original.xlsx (current plan report) sheet manually by right clicking and click on refresh data command to see the actual data.

i am attaching the files for reference.


sample code:

public static void savePDF(String filename){

try {
Workbook workbook = new Workbook(filename);

workbook.calculateFormula(true);
int sheetCount = workbook.getWorksheets().getCount();
System.out.println(“sheet count…” + sheetCount);
for (int i = 0; i < sheetCount; i++) {
Worksheet ws = workbook.getWorksheets().get(i);
System.out.println(“worksheet name :”+ws.getName());
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);
// ws.getPivotTables().get(k).setRefreshDataFlag(false);

}

}

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


}
}







thanks

49

Hi,


Thanks for the template files.

I have noticed the issue as the chart is not rendering fine in the generated PDF. For data I am not sure if it is properly refreshed in the pivot table or not because I cannot find any difference apparently. I have logged a ticket with an id “CELLSJAVA-40568” for your issue. We will look into your issue soon.

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

Thank you.


50

thanks for the update.

please find the attached template . the values jan-00 (repeated in yellow color) is being generated after refreshing the pivot tables but in the actual excel there are different values (see the orginal file attached previously by refreshing it manually) .


if i save the excel file after refreshing pivot tables manually and generate pdf i am getting values properly.