openpyxl Page Setup "Fit To" vs "Adjust To"

Question

I'm trying to set my worksheet to automatically fit all columns on one page using openpyxl. The way I have approached this is to set the fitToWidth property and the fitToHeight property. So far I can modify the page setup to have a fitToWidth value of 1 and the fitToHeight value of 0 but when I open the spreadsheet, it is still in the "Adjust to" selection and not the "Fit to" selection where the fitToWidth and fitToHeight values have been applied. How can I change that selection in the page setup to be "Fit to" instead of "Adjust to"?

I think the PageSetup.zoom property which needs to be False but I can't figure out how to do that with openpyxl. Here's the MSDN article on it: https://msdn.microsoft.com/en-us/library/office/ff197028.aspx

Here is my code so far:

from openpyxl import load_workbook
wb = load_workbook('test.xlsx')
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
ws.sheet_view.view = "pageLayout"

Thanks!

Answer 1

The following works for me:

import openpyxl, os

f = r'test.xlsx'
wb = openpyxl.load_workbook(f)
ws = wb.active

ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.page_setup.fitToHeight = False

wb.save(f)

Of course, if you wanted to fit the height to one page and didn't care about the number of pages wide, you would replace

ws.page_setup.fitToHeight = False

with

ws.page_setup.fitToWidth = False

If you want the entire worksheet on a single page, then drop the ws.page_setup... line entirely.

If you want the height/width to fit on, for example, 3 pages, use:

ws.page_setup.fitToWidth = 3

But, the key element that I struggled with is that you have to set ws.sheet_properties.pageSetUpPr.fitToPage = True prior to setting the fitToHeight and/or fitToWidth properties.

You do not need to set the scale percentage explicitly. That would undo the settings here.

Answer 2

openpyxl implements the file specification which may vary from what any particular SDK offers. This refers to scale. See § 18.3.1.64 of the ECMA 476 specification. Eg. ws.page_setup.scale = 400 should work.

It's worth noting that the various options for displaying and printing are spread somewhat counter-intuitively within the file.