pageSetup fitToPage, fitToWidth, fitToHeight, scale confusing and documentation is incomplete.

[brucejo75]

I have been trying to output pages that will print all columns/rows on a page. The package was not operating as I had expected.

When I reverse engineer the .xlsx file I found this data:

Fit to page	Attribute(s) from XLSX	Attribute(s) that work	Comments
All columns	scale=XX fitToHeight="0"	fitToHeight="0"
All rows	scale=XX fitToWidth="0"	fitToWidth="0"
One page	scale=XX fitToWidth="1" fitToHeight="1"	fitToWidth="1" fitToHeight="1"

I think excel adds in the calculated scale for caching, if it doesn't find the scale then it will simply add it for later saves. The scale setting is ignored in the presence of fitToPage.

As a test, I took the output of a file that had "fit all columns on one page" print setting, the results were: scale="70", fitToPageHeight="0"

I manually changed the scale setting to scale="89"and found that it loaded up in "fit all columns on one page" print mode. Then I saved the file. The value was changed back to scale="70". So I am assuming that scale is ignored and is overridden by fitToPage

I propose that the settings:

fitToPage
fitToWidth
fitToHeight
scale

be emitted in a consistent way with how excel will interpret them.

The fitToPage documentation was a little confusing to me:

name	default	description
fitToPage		Whether to use fitToWidth and fitToHeight or scale settings. Default is based on presence of these settings in the pageSetup object - if both are present, scale wins (i.e. default will be false)

This kind of implies:

• fitToPage is calculated depending on the values of fitToWidth and fitToHeight.
• scale overrides fitToPage settings
• there is no default value

Looking through the code and testing it I do not think either of those statements are true.

Proposal code to add to worksheet-xform.js

    const PS = model.pageSetup;
    if (PS.fitToPage) {
      // if there are page fit constraints, ignore the scale
      PS.scale = undefined;
      if (PS.fitToWidth !== undefined || PS.fitToHeight !== undefined) {
        if (PS.fitToWidth === 0 && PS.fitToHeight === 0) {
          // 0 means unconstrained, this is essentially 100% scale
          // ignore the fitToPage settings because they will imply a weird
          // custom custom scaling which is essentially 100% scaling
          PS.fitToWidth = undefined;
          PS.fitToHeight = undefined;
          PS.fitToPage = false;
        }
      } else {
        // don't assume default settings for these
        PS.fitToWidth = 1;
        PS.fitToHeight = 1;
      }
    } else {
      // Don't output these if fitToPage is not set
      PS.fitToWidth = undefined;
      PS.fitToHeight = undefined;
    }

Update documentation changes with above

Clarify that setting fitToWidth or fitToHeight to 0 essentially says those parameters are not constrained. Clarify that setting both fitToWidth and fitToHeight to 0 is the same as scale=100 and will not emit fitToPage, fitToWidth or fitToColumn. Clarify that fitToPage is a setting that the user controls.

• if fitToPage is true, then fitToWidth & fitToColumn values will be used, scale will not be emitted.
• if fittoPage is falsy then fitToPage, fitToWidth & fitToColumn will not be emitted. Add an example of how to use these parameters for common scenarios:
• Sheet all on one page
• Sheet columns all on one page
• Sheet Rows all on one page

PR

I have a code PR ready to go. I can develop the documentation PR pending a discussion here and approval.

[brucejo75]

Hi @guyonroche,

Let me know if you want a pull request on this. I can update the tests and the documentation. I have the code ready to go.

[TonyDobbs]

@brucejo75 @guyonroche Has an alternative change been comitted? I can't seem to figure out how to make the export fit to 1 page with without it also trying to fit the content to 1 page in height.

[lucas2595]

@TonyDobbs I don't know if you found a solution already, but I managed to do it by setting:

worksheet.pageSetup.fitToPage = true
worksheet.pageSetup.fitToWidth = 1
worksheet.pageSetup.fitToHeight = 0

When setting fitToHeight = 0 it follows the fitToWidth defined and vice-versa.

[dogusev]

it seems that i have same issue,https://github.com/exceljs/exceljs/issues/816 , symptoms are similar =)

[MuyarSPX]

@TonyDobbs I don't know if you found a solution already, but I managed to do it by setting:

worksheet.pageSetup.fitToPage = true
worksheet.pageSetup.fitToWidth = 1
worksheet.pageSetup.fitToHeight = 0

When setting fitToHeight = 0 it follows the fitToWidth defined and vice-versa.

It works for me for 'Fit all columns on one page'. Thanks

[tairs216]

I remove (and win problem) OutlineProperties:

// worksheet.properties.outlineProperties = { summaryBelow: false, summaryRight: false };

about in source of ExcelJS they have some point-to-relation:

var sheetPropertiesModel = { outlineProperties: properties && properties.outlineProperties, tabColor: properties && properties.tabColor, pageSetup: pageSetup && pageSetup.fitToPage ? { fitToPage: pageSetup.fitToPage } : undefined };

[Sarsewar]

@brucejo75 @TonyDobbs @lucas2595 @guyonroche @alubbe

Please find attached Demo_Excel.xlsx, which we are currently using to convert to PDF. The conversion process is being conducted directly on DocuSign. However, we've encountered issues regarding page size and scaling, as illustrated in the screenshots below. Despite attempting conversion through other online free platforms specializing in direct XLSX to PDF conversion, we've encountered the same issues. Notably, the same Excel file converts correctly using Microsoft's "Save As" or "Print" functionality.

We have explored various combinations in the worksheet's XML, including adjustments to pageSize, scale, fitToWidth, and fitToHeight, yet none have proven successful. We seek your insights into the possible reasons for these challenges.

fitToPage	fitToWidth	fitToHeight	Scale
0	0	0	0
0	0	0	1
0	0	1	0
0	0	1	1
0	1	1	1
0	1	0	1
0	1	1	0
0	1	0	0
1	0	0	0
1	0	0	1
1	0	1	0

Your guidance in resolving this matter is greatly appreciated.