Hi,
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,
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
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,
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,
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,
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,
Hi,
Thanks for the analysis and reply amjad.