Hi, Pivot sheet was NOT getting converted into PDF. I didn’t see any error thrown. Not sure any issue with my code. Can you please help. I am enclosing the input file and below is the code used for conversion. FYI, i am creating a separate pdf for each worksheet.
public class ExcelToPdf2 {
public static void main(String[] args) {
setAsposeExcelLicense();
Workbook workbook;
try {
workbook = new Workbook("24518.xlsx"); // pivot-out2
int sheetCount = workbook.getWorksheets().getCount();
for (int i = 1; i < sheetCount; i++) {
workbook.getWorksheets().get(i).setVisible(false);
}
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.setAllColumnsInOnePagePerSheet(true);
for (int i = 0; i < sheetCount; i++) {
Worksheet sheet = workbook.getWorksheets().get(i);
System.out.println(sheet.getName());
sheet.getPageSetup().setFitToPagesWide(i);
sheet.getPageSetup().setFitToPagesTall(0);
workbook.calculateFormula(true);
// Save the PDF file
workbook.save("OutputFile-" + sheet.getName() + ".pdf", pdfSaveOptions);
addLogoAndFilters("OutputFile-" + sheet.getName() + ".pdf", null, null, false, null, null);
if (i < sheetCount - 1) {
workbook.getWorksheets().get(i + 1).setVisible(true);
workbook.getWorksheets().get(i).setVisible(false);
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static License setAsposeExcelLicense() {
License license = new License();
try (InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("Aspose_Total.lic")) {
license.setLicense(is);
} catch (IOException e) {
e.printStackTrace();
;
}
return license;
}
private static void addLogoAndFilters(String pdfFilePath, String title, String requestedDate, boolean showFilters,
Map<String, OutputFilter> reportFilters, Font font) {
setAsposePdfLicense();
Document pdfDocument = new Document(pdfFilePath);
Page page = pdfDocument.getPages().get_Item(1);
Table table = new Table();
table.setColumnWidths("25 125");
// Add an image to the header
Image image = new Image();
image.setImageStream(
Thread.currentThread().getContextClassLoader().getResourceAsStream("splash-icon-30X30.jpg"));
image.setFixWidth(20); // Set the image width
image.setFixHeight(20); // Set the image height
Row imageRow = table.getRows().add();
Row hrRow = table.getRows().add();
Row executedOn = table.getRows().add();
com.aspose.pdf.Cell imageCell = imageRow.getCells().add();
imageCell.setRowSpan(3);
// imageCell.setAlignment(HorizontalAlignment.Right);
imageCell.getParagraphs().add(image);
imageCell.setWidth(50);
com.aspose.pdf.Cell titleCell = imageRow.getCells().add();
titleCell.getParagraphs().add(new TextFragment("report name"));
// titleCell.setAlignment(HorizontalAlignment.Left);
titleCell.setWidth(300);
TextState tState = titleCell.getDefaultCellTextState();
tState.setForegroundColor(Color.getBlue());
// tState.setFont(font);
tState.setFontSize(6);
com.aspose.pdf.Cell hrCell = hrRow.getCells().add();
// hrCell.setAlignment(HorizontalAlignment.Left);
hrCell.setWidth(300);
hrCell.getParagraphs().add(new HtmlFragment("<HR>"));
com.aspose.pdf.Cell executedOncell = executedOn.getCells().add();
// executedOncell.setAlignment(HorizontalAlignment.Left);
executedOncell.getParagraphs().add(new TextFragment("Report Executed On : " + "19-07-2024 00:20:30"));
executedOncell.setWidth(300);
tState = executedOncell.getDefaultCellTextState();
tState.setForegroundColor(Color.getGray());
// tState.setFont(font);
tState.setFontSize(6);
page.getParagraphs().add(table);
// addFilters(page, showFilters, reportFilters);
savePdfDocument(pdfDocument, pdfFilePath);
}
private static void savePdfDocument(Document pdfDocument, String outputFile) {
// Optimize resources in the PDF document
Document.OptimizationOptions optimizationOptions = new Document.OptimizationOptions();
// Save the optimized PDF
pdfDocument.optimizeResources(optimizationOptions);
pdfDocument.save(outputFile);
pdfDocument.close();
}
private static License setAsposePdfLicense() {
License license = new License();
try (InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("Aspose_Total.lic")) {
license.setLicense(is);
} catch (Exception e) {
e.printStackTrace();
;
}
return license;
}
Thanks for the template Excel file and cod snippet.
You need to refresh and calculate the pivot table(s) in code before rendering to PDF. You may add a line of code (in bold) to your code segment. I tested it and it works fine.
Workbook workbook;
try {
workbook = new Workbook(“24518.xlsx”); // pivot-out2
int sheetCount = workbook.getWorksheets().getCount();
for (int i = 1; i < sheetCount; i++) {
workbook.getWorksheets().get(i).setVisible(false);
}
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.setAllColumnsInOnePagePerSheet(true);
for (int i = 0; i < sheetCount; i++) {
Worksheet sheet = workbook.getWorksheets().get(i);
System.out.println(sheet.getName());
//Refresh pivot tables in the worksheet sheet.refreshPivotTables();
Thanks @amjad.sahi and @simon.zhao
Below code worked for me. //Refresh pivot tables in the worksheet sheet.refreshPivotTables();
And i didn’t find the refreshall method, might be because the totals version that i am using, unable to find the workbook.Worksheets.RefreshAll(); method.
One more issue, The converted pivot doesn’t have borders, is there any easy way to set the borders for pivot table. Please suggest.
Good to know that sheet.refreshPivotTables(); works to some extent for your requirements.
By the way, I am using Aspose.Cells for Java v24.7 and workbook.getWorksheets().refreshAll() method is there in the APIs.
Yes, pivot table in Excel spreadsheet would be rendered with normal style or with raw data (without formatting) to PDF. If you want to set borders or other formatting, you have to specify and apply to the pivot table range in code by yourselves before rendering to PDF. See the sample (updated) code segment to accomplish your task for your reference.
e.g., Sample code:
Workbook workbook;
try {
workbook = new Workbook("d:\\files\\24518.xlsx"); // pivot-out2
int sheetCount = workbook.getWorksheets().getCount();
for (int i = 1; i < sheetCount; i++) {
workbook.getWorksheets().get(i).setVisible(false);
}
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.setAllColumnsInOnePagePerSheet(true);
workbook.calculateFormula(true);
for (int i = 0; i < sheetCount; i++) {
Worksheet sheet = workbook.getWorksheets().get(i);
System.out.println(sheet.getName());
if(sheet.getPivotTables().getCount()>=1) {
sheet.refreshPivotTables();
for (int j = 0; j < sheet.getPivotTables().getCount(); j++) {
CellArea area = sheet.getPivotTables().get(j).getTableRange2();
Range range = sheet.getCells().createRange(area.StartRow,area.StartColumn,area.EndRow - area.StartRow+1,area.EndColumn -area.StartColumn +1);
range.setOutlineBorders(CellBorderType.THIN, com.aspose.cells.Color.getBlack());
}
}
sheet.getPageSetup().setFitToPagesWide(i);
sheet.getPageSetup().setFitToPagesTall(0);
// Save the PDF file
workbook.save("d:\\files\\OutputFile-" + sheet.getName() + ".pdf", pdfSaveOptions);
addLogoAndFilters("OutputFile-" + sheet.getName() + ".pdf", null, null, false, null, null);
if (i < sheetCount - 1) {
workbook.getWorksheets().get(i + 1).setVisible(true);
workbook.getWorksheets().get(i).setVisible(false);
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
@koteswaragunda
Please call Range.SetInsideBorders as the document
And as there are outline borders in the pivot table, but no borders are exported after refreshing pivottables, we have opened a ticket CELLSNET-56271 for it . We will try to fix it.
Please check 24.7.2.zip (95.0 KB) which is generated by inner hot fix 24.7.2.
It works as MS Excel, the outline border is visible .But the range “A3:AO5” is black filled as MS Excel.
If it’s not your excepted, please the style of pivot table.
The issues you have found earlier (filed as CELLSNET-56271) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi