Calculating "PageSetup.Zoom" with HorizontalPageBreaks

wknauf · August 24, 2022, 10:32am

Hi,

I need your help: I want to calculate the “Worksheet.PageSetup.Zoom” so that in a sheet with custom “HorizontalPageBreak”, the highest section fits on a printed page.

Here is a screenshot of the excel dialog which shows the option that I want to set:
pagesetup.png (10.1 KB)

Attached sample reproduces my problem:
AsposeCellsScaling.zip (28.6 KB)

The first button creates a blank document with a horizontal page break. Here, everything works as expected. So I assume my code is basically OK.

The second button loads an existing file, which is created by our app. The file already defines a HorizontalPageBreak. But here, my code computes a wrong scaling factor (70% - it should be 64%).

I don’t see what I have missed.

My code for calculating the zoom factor:

  //Calculate Zoom:
  double pageHeightMax = 0;
  double pageHeightAktuell = 0;
  for (int intRow = 0; intRow <= sheet.Cells.MaxDataRow; intRow++)
  {
    pageHeightAktuell += sheet.Cells.GetRowHeightInch(intRow);

    if (Form1.ContainsHorizontalPageBreak(sheet.HorizontalPageBreaks, intRow) == true)
    {
      //Max. Height?
      pageHeightMax = Math.Max(pageHeightMax, pageHeightAktuell);

      pageHeightAktuell = 0;
    }
  }

  sheet.PageSetup.PaperSize = PaperSizeType.PaperA4;

  double heightA4 = sheet.PageSetup.PaperHeight;
  //Remove the margins:
  heightA4 -= sheet.PageSetup.TopMarginInch;
  heightA4 -= sheet.PageSetup.BottomMarginInch;

  double factorHeight = (heightA4 / pageHeightMax);
  //Don't increase beyond 1.0:
  factorHeight = Math.Min(1.0, factorHeight);

  sheet.PageSetup.Zoom = (int)(factorHeight * 100);

Best regards

Wolfgang

amjad.sahi · August 24, 2022, 1:00pm

@wknauf,

Thanks for the template file, screenshot and sample.

Please notice, after an initial test, I am able to reproduce the issue as you mentioned by using your template file and sample code. It looks like an issue with the API while calculating scaling factor. Anyways, we got to evaluate your sample code in details if it is actually an issue with the APIs. I have logged a ticket with an id “CELLSNET-51864” for your issue. We will look into the details of the problem.

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

peyton.xu · August 26, 2022, 9:14am

@wknauf

Calcaute page zoom is complex, please use SheetRender.PageScale instead of double factorHeight = (heightA4 / pageHeightMax);.

Please replace the method CalcZoom(Worksheet sheet) in your code with the following code:

private static void CalcZoom(Worksheet sheet)
{
    //Calculate Zoom:
    double pageHeightMax = 0;
    int pageStartRow =-1, pageEndRow = -1;

    List<int> hbreaks = new List<int>();
    hbreaks.Add(0);
    hbreaks.Add(sheet.Cells.MaxDataRow + 1);
    foreach(HorizontalPageBreak horizontalPageBreak in sheet.HorizontalPageBreaks)
    {
        if(!hbreaks.Contains(horizontalPageBreak.Row))
        {
            hbreaks.Add(horizontalPageBreak.Row);
        }
    }
    hbreaks.Sort();

    //locate the page with max height
    for(int i = 0; i < hbreaks.Count -1; i++)
    {
        double pageHeightAktuell = 0;
        for (int row = hbreaks[i]; row < hbreaks[i+1]; row++)
        {
            pageHeightAktuell += sheet.Cells.GetRowHeightInch(row);
        }

        if(pageHeightAktuell > pageHeightMax)
        {
            pageHeightMax = pageHeightAktuell;

            pageStartRow = hbreaks[i];
            pageEndRow = hbreaks[i + 1] - 1;
        }
    }

    sheet.PageSetup.PaperSize = PaperSizeType.PaperA4;

    if(pageStartRow > -1 && pageEndRow > -1)
    {
        string savedPrintArea = sheet.PageSetup.PrintArea;


        sheet.PageSetup.PrintArea = string.Format("{0}:{1}", pageStartRow+1, pageEndRow+1);
        sheet.PageSetup.SetFitToPages(0, 1);

        //calculate zoom
        SheetRender sr = new SheetRender(sheet, new ImageOrPrintOptions());
        sheet.PageSetup.Zoom = (int)(sr.PageScale * 100 + 0.5);

        //restore origin print area
        sheet.PageSetup.PrintArea = savedPrintArea;
    }

With the above code, it calculates a good zoom which is larger, but still works OK.

With the above code, it calculates 74% which is not right. It is becuase the default font of your source file is font “Segoe UI”, 6.5, which is not a normal setting. If you change default font to “Calibri”, 11, it shoud work OK.

wknauf · August 30, 2022, 6:23am

Hi Peyton,

the source file was generated by an external winforms component (C1FlexGrid by ComponentOne/GrapeCity), which provides a method to save it to excel. In our app, the users can change the default font for this control.This is the reason why the document has a default font “SegoeUI 6.5”. So, we don’t have much control over the default font.

Your sample results in a scaling factor that is even more wrong (74%) than the one calculated by my code (70%) .

When performing the steps of your sample code in excel (setting print area to rows 1 to 59, then set scaling to “one page high”), then the correct scaling factor “64%” is calculated:
scaling_factor_excel.png (10.0 KB)

Best regards

Wolfgang

peyton.xu · August 30, 2022, 6:48am

@wknauf

We will try to fix the issue.

amjad.sahi · August 30, 2022, 10:24am

@wknauf,

We are pleased to inform you that your issue has been resolved now. The fix will be included in our upcoming release (Aspose.Cells v22.9) scheduled in the second week of September 2022. You will also be notified when the next version is released.

wknauf · August 30, 2022, 10:58am

Will the fix be part of the “SheetRender” approach suggested by Peyton.Xu, or will the fix also affect my initial approach to calculate the necessary page height?

amjad.sahi · August 30, 2022, 1:07pm

@wknauf,

We will share the sample code that would figure your issue out.

peyton.xu · August 31, 2022, 1:10am

@wknauf

You will get the expected 64% page zoom with my suggested approach for your source file samplefile.xlsx.

aspose.notifier · September 8, 2022, 11:41am

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

wknauf · September 8, 2022, 7:54pm

Great - my sample now works. Will update our real app and test it next week.

Best regards

Wolfgang

amjad.sahi · September 9, 2022, 8:56am

@wknauf,

Thanks for your feedback.

Please take your time to evaluate the new version for your scenario. Hopefully it will work fine in your original application.

wknauf · September 20, 2022, 7:40am

Well, I tested it with another sheet, and here the zoom calculation does not work with 22.9.

Attached is a reworked sample - click the button “Existing new file
(new approach)”, which loads a new file “samplefile_new_result.xlsx”. This file was created by saving the C1FlexGrid to excel, then it was opened with Aspose.Cells and borders and page breaks were added and it was saved again.
The result is a wrong zoom. It should be 53%, but it calculates 65%.

AsposeCellsScaling_2022-09-20.zip (48.7 KB)

I also noticed that my code to set the horizontal/vertical page breaks had a 1-offset bug, so my initial sample had slightly wrong page breaks. Don’t know whether the fix of this causes the new problem.

Best regards

Wolfgang

amjad.sahi · September 20, 2022, 11:09am

@wknauf,

Thanks for the template files and sample.

Please notice, I did test your scenario/case using your template files and found the issue as you mentioned. The zoom calculation has some issue in the newer version. I have logged a separate ticket with an id “CELLSNET-52043” for your issue. We will look into it soon.

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

peyton.xu · September 29, 2022, 8:12am

@wknauf

The default font for the new source file samplefile_new_result.xlsx comes to “Segoe UI 8.25”.
The default font is important for the page layout. Generally, the default fonts are some normal values. e.g. “Calibri 11”, “Arial 10”.
We can’t do adaption for every default fonts, especially special ones.
You may need to find ways to limit the change of default fonts.

wknauf · September 29, 2022, 12:36pm

As I wrote before, the excel files are not generated by us, but by an external winforms table control (C1FlexGrid) which has a “SaveExcel” method. And unfortunately, we allowed the users to set a custom font size to the control. Thus the resulting document has the default font of the application (“SegoeUI”) with a possible range of font sizes.

According to your feedback, I tried to change the default font of the generated workbook before applying the scale calculation code:

  Style s = workbook.DefaultStyle;
  s.Font.Name = "Calibri";
  s.Font.Size = 11;
  workbook.DefaultStyle = s;

The result is: The excel printpreview still shows the same scale as before (65% for my last sample), but the rows now fit perfectly on one page.
But the column widths have changed - they are much wider. So, less cols fit to one page.

Do you have any idea how I could work around this problem? It seems that changing the DefaultStyle is not the correct resolution.
And creating a bug report ticket for the company that provides C1FlexGrid is also not possible, because I could not even tell them what they would have to change ;-).

amjad.sahi · September 29, 2022, 1:23pm

@wknauf,

We are sorry that you still could not sort out your issue. We will look into your issue and evaluate your concerns. We will update you with more details soon.

peyton.xu · September 30, 2022, 8:02am

@wknauf

The default font is import, it effects page layout and column width.

Is it possible to limit font size of default font to integer instead of float while generating source files?
If yes, we can do some adpations for font “Segoe UI” with integer font sizes.

wknauf · September 30, 2022, 12:18pm

Hi,

I would have to discuss this with my bosses. Unfortunately, we use a numeric up down with two decimal places. And due to historical reasons, the default is “8.25”, as the default font in .NET Framework applications is “Microsoft SansSerif 8.25”. And some users might have different settings.

If we had used a System.Windows.Forms.FontDialog, the font sizes would also have a fractional part, see e.g. https://stackoverflow.com/questions/3544926/font-size-discrepancy-in-net-gdi

Attached is a sample that displays the FontDialog in a loop for different font sizes and prints out the resulting size.
FontSizeTester.zip (10.3 KB)

So, to create a consistent behavior, we should probably support only those steps:
1.5 => 2.25 => 3 => 3.75 => (4.5 is missing) => 5.25 => 6 => 6.75 => (7.5 is missing) => 8.25 => 9 => 9.75 => (10.5 is missing) => 11.25 => 12 => 12.75 => (13.5 is missing)) => 14.25 …

What do you think? Does it sound reasonable to limit the font selection to those sizes?

Best regards

Wolfgang

amjad.sahi · September 30, 2022, 1:00pm

@wknauf,

Thanks for sharing your feedback and sample .NET project to show your proposed sizes.

We will evaluate it in details and get back to you.