Having an odd behavior when we convert a spreadsheet to a PDF. I’ve broken down the scenario into a sample for posting purposes.
1) We have a spreadsheet with a defined print area (see bad_print_area.xlsx). For the sake of background the # of columns and rows in our sheet are dynamic normally however I’ve just included fixed output here. If you print preview you’ll note that only a portion of the content would output.
2) In our workflow we iterate over the sheets in the workbook and want to set the print area to contain all of our content. We have a working function that does this and pass it via setPrintArea. This “works” in that the resulting new XLSX file we output works great (spreadsheet_output.xlsx, 4 pages in print preview). This is identical to the result we get in Excel itself when we apply our function via the Print_Area formula.
3) When we convert the spreadsheet to PDF in the code…the resulting PDF is 54 pages (not 4 like we would expect).
4) If I take the “good” XLSX file (i.e. spreadsheet_output.xlsx) with the right print area and run it through the Aspose.PDF library to convert it outside this code scope and without calling setPrintArea…the spreadsheet converts right and I get 4 pages. Very strange.
5) I’ve tried both passing the function directly to setPrintArea as well as defining the function (i.e. DynaPrint) in the spreadsheet and passing a shorter “=DynaPrint”. I get the same results all the way around. Thought being maybe the function was being garbled by the library.
Here is a code snippet (Groovy) to give you an idea:
def generatePDFfromXLS() {
Workbook workbook = new Workbook(gDataDir + “bad_print_area.xlsx”);
workbook.calculateFormula();
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
// Take Pdfs of each sheet - only one in this eample
for (int j = 0; j < workbook.getWorksheets().getCount(); j++)
{
Worksheet ws = workbook.getWorksheets().get(j);
//Print Area Test
// Obtaining the reference of the PageSetup of the worksheet
PageSetup pageSetup = ws.getPageSetup();
// Specifying the print area
pageSetup.setPrintArea("=OFFSET($A$1,0,0,(MAX(IF(NOT($A$1:$A$400=""),ROW($A$1:$A$400),0))),(MAX(IF(NOT($A$5:$AZ$5=""),COLUMN($A$5:$AZ$5),0))))")
// pageSetup.setPrintArea("=DynaPrint")
pageNames.add(ws.getName())
println “Adding sheet:” + ws.getName();
workbook.save(gWorkingDir + “pdf_output.pdf”, pdfSaveOptions);
}
workbook.save(gWorkingDir + “spreadsheet_output.xlsx”, FileFormatType.XLSX)
}
I’m also attaching the PDF output (54 pages mostly blank) and the spreadsheet output which you’ll not has a corrected print area compared to the input and shows 4 pages in print preview.
I originally discovered this issue on (Java) Aspose.Cells 16.11.0 and Aspose.PDF 16.10.0. I have replicated on the 17.5 versions of both.
Any ideas? Thanks!