Hi, row-labels and column-labels in excel pivot table are not converted into PDF. Instead of actual column names observed label names as Row Labels and Column Labels etc…
code is,
public static void main(String[] args) {
setAsposeExcelLicense();
Workbook workbook;
try {
workbook = new Workbook("pivot.xls"); // 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.getWorksheets().setRefreshAllConnections(true);// refreshAll();
//workbook.calculateFormula(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();
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;
}