Incorrect height reported and missing bottom border in PDF

Aspose.Cells Product Family
1

Hi Team,
We encountered 2 issues when we are using aspose to generate files.

  1. When we are getting height from aspose code then it is giving us 15, while when we are opening the file in excel then the row height is totally different.
  2. There should be a bottom border on each page when we are generating the PDF, as we can check from the print preview in excel.

Below are the steps to reproduce issue 1 (please follow these steps while try to generate file from aspose code)
Step 1. Open a workbook
Step 2. Set the column width to less than the contents, so that the complete column content can’t be displayed properly.
Step 3. Autofit the rows
Step 4. Wrap the text
Step 5. Save the file to another file
Step 6. Open the newly saved file using aspose, try to find out the row height, height reported by excel would be 15.
Step 7. Now open the same workbook in excel and check for the row height, it should be some other value.

To reproduce issue 2 please follow below steps
Step 1. Open a workbook with merged cells and some border setup
Step 2. Save it as PDF
Step 3. Open the print preview of the workbook in excel, there would be border on all the pages
Step 4. Now open the PDF generated by aspose, set zoom to 100%, the bottom border would be missing on all the pages, except the last page.

This issue is impacting our production environment and user are complaining about the formatting issues, would really appreciate if we can get those fixed ASAP.

We had already attached the sample code, sample files and screenshot for your kind reference, please let us know in case any further details are needed from our side.

Sample Files.zip (13.7 KB)
Sample Images.zip (108.0 KB)
Sample Code.zip (21.7 KB)

2

@rgandhi
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s):

  1. CELLSNET-52759 (Merged area missing border when saving file to PDF)
  2. CELLSNET-52760 (Incorrect height when calling Row.Height)

You can obtain Paid Support services if you need support on a priority basis, along with the direct access to our Paid Support management team.

3

@John.He Thanks, could you please help with some ETA for the resolution of the same, so that we can plan our course of action accordingly

4

@rgandhi,
We have recorded the issues in the internal system, and we need time to evaluate the issues.

5

@John.He found another issue related to height, to reproduce this please follow the steps below:

Step 1. Open Workbook
Step 2. Set Content Style to Bold
Step 3. Set font to Calibri and Font size to 16
Step 4. Set horizontal alignment center
Step 5. Save to another workbook
Step 6. Load the new workbook again
Step 7. Read height of the row
Step 8. Open the file in excel, check the height of the row

Aspose will report height to 14.5 while excel would provide height as 21

Please find attached sample files, screenshots for the same.
Also updated the code containing all the defects all together

Sample Files.zip (5.5 KB)
Sample Images.zip (49.0 KB)
Sample Code.zip (27.4 KB)

6

@rgandhi
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-52763

You can obtain Paid Support services if you need support on a priority basis, along with the direct access to our Paid Support management team.

7

@John.He We can understand there would be sometime needed to get fix for this, but it would be really better if you could let us know about weather you are able to reproduce this issue or not. Also, if we can have tentative timeline about these issues to be resolved

8

@rgandhi,
We can reproduce these issues, and we are already studying and solving them. Once fixed, we will provide the fix version as soon as possible.

9

@rgandhi,

1). Please call Worksheet.AutoFitRows() after setting all data and style and before saving the file.
See the following sample code for your reference:

work_sheet.Cells[0, 1].SetStyle(style);
work_sheet.AutoFitRows();
work_book.Save("result.xlsx");

2). About your test Defect1 with above codes, you can get 33.75 pt with Aspose.Cells. But the row height is 45pt in MS Excel. it’s very strange that MS Excel displays a blank line and we think the value can display with 3 lines, so Aspose.Cells returns 33.75pt.

10

@simon.zhao thanks for the workaround, but in our case we would not be able to set the auto height after the style is applied, since we are creating these workbooks dynamically with the styles available in our database, so we couldn’t do the autofit after the style is applied.

Let me add a simple case for you, for 1 worksheet user had set the height of a row to 10pt. intentionally in their styles, but when we apply autofit rows then the row size would be changed to something else, it would be more problem to us and the formatting will be totally disturbed.

Also, please let us know how we could get the height according to MS Excel, we must need to get it the same. EPPlus was able to do that without any issues.

11

@rgandhi,

Thanks for your feedback and sharing your concerns.

We will evaluate your issue further and get back to you with more details.

12

@rgandhi

We are afraid we cannot support to fire the auto-fit operation automatically when one cell is changed. As a library, changing cells data is a very frequent operation in user’s application, frequent auto-fit operations will seriously affect the performance.

Please change work_sheet.AutoFitRows(); to work_sheet.AutoFitRows(true);, this will only autofit rows that are auto height, it will not autofit rows that are custom height.

When you open source file into Excel, Excel will also autofit rows that are auto height automatically.

Or, you may determine which one row needs and when to perform the auto-fit operation by calling methods like:
AutoFitRow, AutoFitRow, …etc

13

Thanks @Peyton.XuI

Would try to use this and update you about the result, but just curious to know about once we open the file in MS Excel and save it back again and then try to read the height from aspose then it is correct. Could you please help us to understand why this is happening

14

@rgandhi,

When ms excel loads one template file, it will perform the auto-fit operation automatically. When you save it again the updated data of row height will be saved to the re-saved file. So when the re-saved file be loaded by aspose.cells, we can get the correct row height and output it. Please note, for performance consideration, we cannot do the same(do auto-fit) like ms excel when loading a template file.

15

@rgandhi
If you open and save the file in Excel, Excel will automatically refit the height of rows which are automatic row heights. And these resized heights of rows will be saved into the file,Aspose.Cells will return the stored height in the file if you do not call Worksheet.AutoFitRows method.
For Example about Defect1 :
33.75pt returned after Worksheet.AutoFitRows -> 45pt displayed in Excel and save file with Excel -> 45pt returned when reading by Aspose.Cells.

16

Hi Team,
Understood that due to performance issues aspose could not replicate the autofit behavior as MS Excel, we would try to resolve this with something from our side.

Could you please provide the updates for defect 2 and defect 3 since they are also critical for us and need to be resolved

17

@rgandhi,

Thank you for your understanding. For defect 2, we have fixed the issue and fix will be included into our next official version 23.3 which may be released in the second week of March.

For defect 3, it is same with defect 1. If you do not call AutoFit for the row(s) after setting styles to cells, we do not do auto-fit operation automatically. So, when you saving the workbook, old row height value(which was read from your template file 3.xlsx) will be saved back to the resultant file and be read back from the re-saved file again. To get the updated row height, you need to call AutoFit in your code after setting style to the cell. Or you may open and re-save the generated file by ms excel, then the correct row height value will be saved by ms excel and we can get it when reading the re-saved file.

18

The issues you have found earlier (filed as CELLSNET-52759) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi