"GetRowHeightInch" returns slightly wrong height

Aspose.Cells Product Family
1

See attached sample: I try to measure the grid rows to calculate where horizontal page breaks are required on a worksheet (e.g. because I want to keep specific sets of rows together on the same page).
AsposeCellsPaperHeight.zip (14.9 KB)

In the sample, the sum of GetRowHeightInch does not match the size that is measured when I print the excel file. My code in the end prints a height of 16,74812 cm for the data range and thus decides that all 22 rows fit on the first page.
But in page break view, excel inserts a page break after row 21 and moves row 22 to page 2.

pagebreakview.png849×680 35.6 KB
pagebreakview.png
When printing, the same happens.

When measuring the first printed page in the “real world” with a ruler, I see that the printed area on page 1 has a height of about 16.7 cm, while my trace output says that the sum of the row heights is 16,2983 inch.

I can’t identify a row that causes this difference, it seems that for each row get “GetRowHeightInch” results differs a bit from the printed height, so that finally the last row does not fit on the page.

Do you have any idea?

This might be a followup to "GetRowHeightInch" returns wrong height for autofitted wrapped text with Calibri 10 - the excel file used in this sample was created with AutoFitterOptions.ForRendering) and is an instance of the report that created the initial problem.

Best regards

Wolfgang

2

@wknauf

It appears you are encountering discrepancies between the row heights calculated using GetRowHeightInch and the actual printed heights in Excel. This issue can arise from various factors, including differences in how Excel calculates row heights for printing compared to how the Aspose.Cells API retrieves them.

Here are some suggestions to troubleshoot and potentially resolve the issue:

  1. Check for Hidden Rows or Columns: Ensure that there are no hidden rows or columns that might affect the total height calculation.

  2. Consider Page Margins: The page margins set in Excel can impact the printable area. Make sure to account for these margins when calculating the total height.

  3. Row Height Calculation: If you are using AutoFitterOptions.ForRendering, verify that the row heights are being calculated correctly. You may want to manually set the row heights to see if that resolves the issue.

  4. Print Preview: Utilize Excel’s print preview feature to see how the rows will be printed. This can help identify if any specific rows are causing page breaks.

  5. Adjusting for Font Size and Style: The font size and style can influence the height. Ensure that the font settings in your Aspose.Cells code match those in Excel.

  6. Testing with Different Formats: If feasible, try exporting the worksheet to a different format (such as PDF) to see if the row heights match as expected.

If the issue persists, consider sharing a minimal reproducible example on the Aspose forum for more targeted assistance.

Feel free to reach out if you have further questions!

3

@wknauf,

After initial testing, I am able to reproduce the issue as you mentioned by using your template Excel file and sample code snippet. I found “GetRowHeightInch” returns slightly wrong height.

We require thorough evaluation of the issue. 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-59157

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.

4

@wknauf ,

While calculating page breaks(Excel printview), it is not simply summing row height. Please use WorkSheet.GetPrintingPageBreaks(ImageOrPrintOptions options) to get the right page breaks.

Workbook wb = new Workbook("sample.xlsx");
Worksheet worksheet = wb.Worksheets[0];

CellArea[] cellAreas = worksheet.GetPrintingPageBreaks(new ImageOrPrintOptions());
5

Thanks! I modified my code, and it seems to work now as expected.

To help others who might have the same issue:
My use case is a report which prints schedules for persons. Data for each person might span several rows. The goal is to keep the rows for a person together and don’t do page breaks inside persons.
So the first thought was to sum GetRowHeightInch and insert a page break if a row range does not fit on a page. This does not work.

Now my code uses GetPrintingPageBreaks and this algorithm:

  • set “current row” to “0”
  • loop until “current row” reaches the end of the table
    • call GetPrintingPageBreaks and search the first CellArea which ends after “current row”. This list contains also the custom page breaks inserted by my code
    • for this CellArea.EndRow, search the last person range which ends before this row (or which ends exactly on this row). It might happen that a person spans more than one page, in this situation use CellArea.EndRow.
    • If we found a person range before “current row”: insert a page break in the row after the range end: _sheet.HorizontalPageBreaks.Add(person_range_end + 1) .
    • else: the person spans more than one page, so copy the automatic page break: _sheet.HorizontalPageBreaks.Add(current_row + 1) .
    • set “current row” to the end row of the person range (or the end row of the cell area if the person spans more than one page).
    • repeat the loop
6

@wknauf,

It is good to know that the suggested code/approach works for your needs. Also, thanks for sharing your use case with algorithms which might help others who have similar scenarios. Should you have further queries and comments, please feel free to write us back.