Hi Team,
I am using ASPOSE cells for generating PDF from excel. Previously i was using ASPOSE cells 7.5.1.3 version. The PDF generated is proper and has all the data of the excel.Bu as It has some issues like.(see excel and pdf attached with 7.5.1.3)
1)performance issue while writing pdf if excel has large data.
2)look and feel issues.
3)size of the pdf generate is very high.
I updated my aspose jar to 7.7.0 the performance problem is resolved but the the pdf generate from excel is not refreshing and not showing up properly. (see the excel and pdf attached with 7.7.0)
My excel has pivot tables and it will fetch data from other sheet’s and gets refreshed. This has many problems with pivoting and charts and these got fixed in ASPOSE cells 7.5.1.3 and they were working fine. But now with latest jar some thing got broke.
Please provide your comments and solution as soon as possible.
code:
refreshExcel(“D:\_Current_Plan_Report.xlsx”);
generatePdfReport(“D:\_Current_Plan_Report.xlsx”, reportSheets);
----------------------
public static boolean generatePdfReport(String excelFileName,
List reportSheets) {
try {
//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
int count=0;
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{
if(i==0){
Iterator it = ws.getCells().getColumns().iterator();
while(it.hasNext()){
count++;
it.next();
}
}
System.out.println(“count is :”+count);
if(count<=20){
ws.autoFitRows();
//ws.getPageSetup().setFitToPagesWide(1);
ws.getPageSetup().setFitToPagesTall(1);
ws.getPageSetup().setPaperSize(40);
ws.getPageSetup().setOrientation(1);
ws.getPageSetup().setZoom(30);
}else if( count<=75){
System.out.println(“special case…75”);
ws.autoFitRows();
ws.getPageSetup().setFitToPagesWide(1);
ws.getPageSetup().setFitToPagesTall(1);
ws.getPageSetup().setPaperSize(40);
//PageSetup pageSetup = sheet.getPageSetup();
//pageSetup.setOrientation(PageOrientationType.PORTRAIT);
ws.getPageSetup().setOrientation(0);
ws.getPageSetup().setZoom(20);
}else {
System.out.println(“special case…>75”);
ws.autoFitRows();
ws.getPageSetup().setFitToPagesWide(1);
ws.getPageSetup().setFitToPagesTall(1);
ws.getPageSetup().setPaperSize(40);
//PageSetup pageSetup = sheet.getPageSetup();
//pageSetup.setOrientation(PageOrientationType.PORTRAIT);
ws.getPageSetup().setOrientation(0);
ws.getPageSetup().setZoom(10);
}
// }
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…”);
// if(excelFileName.contains(“Current_Plan”)){
// System.out.println(“current plan report block so skip the pivot refresh…”);
// }else{
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 { //sonar rule violations-emptyif blocks
ws.getPivotTables().get(k).refreshData();
}
try{
ws.getPivotTables().get(k).calculateData();
}catch(Exception el){
System.out.println(“Error is :”+el.getStackTrace());
}
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().equals("")){ //sonar rule violations- string literal equality
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.calculateFormula();
workbook.save(getPdfFileName(excelFileName));
} catch (Exception ex) {
System.out.println(“pdf exception:” + StackTraceUtil.getStackTrace(ex));
return false;
}
return true;
}
-----------------
public static void refreshExcel(String excelFileName) {
System.out.println(“before refresh call :” + excelFileName);
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);
Workbook workbook = new Workbook(excelFileName);
int sheetCount = workbook.getWorksheets().getCount();
System.out.println(“sheet count…” + sheetCount);
for (int i = 0; i < sheetCount; i++) {
Worksheet ws = workbook.getWorksheets().get(i);
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) {
System.out.println(“Error is :” + el.getStackTrace());
}
ws.getPivotTables().get(k).setRefreshDataFlag(false);
/
}
}
//workbook.getSettings().setEncoding(Encoding.getUTF8());
workbook.save(excelFileName);
} catch (Exception ex) {
System.out.println(“pdf exception:” + StackTraceUtil.getStackTrace(ex));
}
System.out.println(“after refresh call :” + excelFileName);
}
--------------
Thanks