Cells to PDF Conversion - Print Area Issue

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!

Hi,


Thanks for the sample files and details.

Well, you should not set formula when specifying print area for the worksheet. Please try using range of cells/cell area for Print Area string, it would work fine and as expected. Please try using the following line of code instead, it works fine as I tested:
e.g
Sample code:

<span style=“font-family: “Courier New”; font-size: 10pt;”>pageSetup.<span style=“font-family: “Courier New”; font-size: 10pt; color: rgb(0, 112, 192);”>setPrintArea<span style=“font-family: “Courier New”; font-size: 10pt;”>(<span style=“font-family: “Courier New”; font-size: 10pt; color: rgb(163, 21, 21);”>“A1:I62”<span style=“font-family: “Courier New”; font-size: 10pt;”>);


Hope, this helps a bit.

Thank you.
Unfortunately that doesn't help. If you re-read the usage scenario the print area needs to be set dynamically based upon the output of the spreadsheet. If I knew the range of cells with data in them than certainly your suggestion would the route we'd take.

It's clear the function is executing (note the correct print area in the saved XLSX) but during the conversion it seems to pick-up additional cell data and convert that to blank pages. I've had other OFFSET function attempts work and the PDF represents what the Excel Print Preview/Results correctly...it just seems that in this situation and this particular function it is no good.

Is there the capability on your side to escalate this to someone who can run in a debug mode or something of the ilk and see why the PDF is picking up 50 blank pages when this is executed in the code...but when I just convert the XLSX output separately it properly generates 4 PDF pages?

Thanks.

Hi,

Well, PageSetup.setPrintArea() only works with cells range/area string as it does not work very well when specifying formulas. We did find the issue of calculating printarea when specifying formulas. A ticket is logged with an id “CELLSJAVA-42319” for your issue. We will look into it to figure it out soon.
In the meantime if you could calculate the formula and then specify the results while setting printable area, then it would work fine. Although it is not very good workaround but you may try, see the sample lines of code:
e.g.
Sample code:

Worksheet ws = workbook.getWorksheets().get(j);
//Print Area Test
// Obtaining the reference of the PageSetup of the worksheet
PageSetup pageSetup = ws.getPageSetup();
Object result = ws.calculateFormula("=DynaPrint");
// Specifying the print area
pageSetup.setPrintArea(result.toString());
ws.getCells().deleteBlankRows();
ws.getCells().deleteBlankColumns();

Thank you.

Amjad,


This is perfect thank you! It deletes some intentional spacer rows but we can make that work I think.

Thanks a lot for your help!


Hi,


Good to know that the devised workaround figures out your issue for now. But, as we have logged an official ticket for the issue into our database, so kindly spare us sometime to fix the issue precisely. We will provide you the fix, so you won’t have to use the workaround anymore.

Thank you.

Thanks Amjad. When I took the solution into the real scenario I did in fact run into the issue you documented. I’m hoping I am closer and can come up with a full work around in the meantime.


Is there a way for external Aspose users to monitor a bug like CELLSJAVA-42319 or be notified when it is patched?

Thanks.

Hi,


We will notify you when the issue is resolved by posting in this thread. The Issue tracking system is internal, so you always have to ask us to get an update on it. I am hopeful if everything goes according to our plans, you should get the fix (we will share the Download link for the fix here) before the end of next week or so.

Keep in touch.

Thank you.

Fantastic Amjad. Thanks.

Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix: Aspose.Cells for Java v17.5.9 and let us know your feedback.

The issues you have found earlier (filed as CELLSJAVA-42319) have been fixed in Aspose.Cells for Java 17.6.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.