Converting excel with merged cells to PDF

Aspose.Cells Product Family
1

Hi, i’m seeing this issue when converting an excel file with merged rows (very long text) to pdf.
Something in the pdf’s page break calculation is wrong because as you can see in the sample pdf between pages 3,4 and 5 the text gets cut in the middle.

Is there any thing i can do about it?

here is the sample pdf and excel files:
Sample.zip (79.3 KB)

code sample:

var asposeWB = new Aspose.Cells.Workbook(pathExcel)
var saveOptions = new Aspose.Cells.PdfSaveOptions();
saveOptions.AllColumnsInOnePagePerSheet = true;
var worksheet = asposeWB.Worksheets[0];
asposeWB.Save(pathPDF, saveOptions);

2

@TopSolutions,

Thanks for the template file and sample code.

I have tested your scenario/case a bit using your template file and sample code. I noticed the issue as you mentioned. I think you may use PageSetup options (i.e., FitToPagesWide and FitToPagesTall, etc.) instead of PdfSaveOptions.AllColumnsInOnePagePerSheet Boolean property. See the samle code for your reference. I have tested it and it works fine:
e.g
Sample code:

var asposeWB = new Aspose.Cells.Workbook(“e:\test2\ExcelSample.xlsx”);
var saveOptions = new Aspose.Cells.PdfSaveOptions();
var worksheet = asposeWB.Worksheets[“Data”];
worksheet.PageSetup.SetFitToPages(1, 0);
asposeWB.Save(“e:\test2\out1.pdf”, saveOptions);

Let us know if we can be of any further help.

3

@Amjad_Sahi

Sorry, still the text gets cut in this example:
Capture.PNG (1.9 KB)
i also tried it without the 2 leftmost columns and the text still gets cut in the middle:
SetFitToPages.pdf (70.8 KB)

4

@TopSolutions,

Thanks for the screenshot and further details.

We already observed the issue as you mentioned by using your template file and sample code. The page breaks calculation is wrong when converting an Excel file with merged rows (containing very long text) to PDF file format. I also found when using FitToPages options instead, still some text is cut (as per your screenshot):
e.g
Sample code:

   var asposeWB = new Aspose.Cells.Workbook("e:\\test2\\ExcelSample.xlsx"); 
    var saveOptions = new Aspose.Cells.PdfSaveOptions(); 
    saveOptions.AllColumnsInOnePagePerSheet = true; 
    //var worksheet = asposeWB.Worksheets["Data"]; 
    //worksheet.PageSetup.SetFitToPages(1, 0); 
    asposeWB.Save("e:\\test2\\out1.pdf", saveOptions);

I have logged a ticket with an id “CELLSNET-45900” for your issue. We will look into your original issue.

Once we have an update on it, we will let you know here.

5

@TopSolutions,

We did evaluate your issue further.
When we manually set the print area to “D:D” and save it to PDF by Microsoft Excel 2016, you can also find that the text gets cut off between pages 3,4 and 5 in the output PDF (see the attachment: “ExcelSample_Ms.pdf” for your reference).

So, MS Excel also does cut the text in rendering the spreadsheet to PDF.ExcelSample_Ms.pdf (167.9 KB)

6

That is not something we can tell our clients,
this issue is very important to them and we can’t just blame MS for it and get on with our lives.
Is there any workaround you can think of for this issue?

7

@TopSolutions,

Well, generally, if MS Excel cannot render it fine, then Aspose may not do it as we follow MS Excel standards and specifications. Anyways, I have logged your concerns against your issue “CELLSNET-45900” into our database. Our concerned developer from product team will further evaluate your issue if we could provide you a workaround to cope with it.

Once we have an update on it, we will let you know here.

8

@TopSolutions,

We did evaluate your issue further. First of all, this is not an issue with the product. When a merged cell merges several rows and is being filled with text, a text line may be just on the line break, when it is being rendered to PDF, part of the text line will be on one page, and the other on the next page.

Workaround:
Unmerge the merged cell, and split it’s text to several parts/paragraphs, then put them to several cells. (see the attachment:“ExcelSample-2.xlsx”) for your reference.
file1.zip (16.8 KB)

Hope, this helps a bit.