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
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
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.
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
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,
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;
}
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.
Hi
is there any ETA for this? Please provide if there is any ETA so that we can plan accordingly.
Thanks
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.
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.