0

I want to set the paper size print option to A6 or a custom user size template with openpyxl, the code would be as follows:

    workbook = Workbook()
    ws = workbook.active
    ws.page_setup.paperSize = ws.PAPERSIZE_CUSTOM

unfortunately, from the docs, it seems the only enumerated sizes available are:

PAPERSIZE_LETTER = '1'
PAPERSIZE_LETTER_SMALL = '2'
PAPERSIZE_TABLOID = '3'
PAPERSIZE_LEDGER = '4'
PAPERSIZE_LEGAL = '5'
PAPERSIZE_STATEMENT = '6'
PAPERSIZE_EXECUTIVE = '7'
PAPERSIZE_A3 = '8'
PAPERSIZE_A4 = '9'
PAPERSIZE_A4_SMALL = '10'
PAPERSIZE_A5 = '11'

I've tried setting the paper height and paper width to my custom properties as such:

    #ws.page_setup.paperSize = ws.PAPERSIZE_A6
    ws.page_setup.paperHeight = '105mm'
    ws.page_setup.paperWidth = '148mm'

however, when printing thru excel, I still have to use the drop down dialog to select 'A6' before I can print to the correct paper size.

Is there a way I can do this? or achieve a similar effect? I'm on Mac OS if that matters.

randomfigure
  • 420
  • 5
  • 13

1 Answers1

1

Question: set custom paper size for printing

from the docs, it seems the only enumerated sizes available are ...

The missing values, from OpenOffice:

  • A6: 70
  • Custom: 0

The values have to be of type int.

Tried to setting it using:

ws.page_setup.paperSize = 70
# or
ws.set_printer_settings(70, ORIENTATION_PORTRAIT)

does not change Format => Page => [Page] settings.

This works for me: 

ws.page_setup.paperHeight = '105mm'
ws.page_setup.paperWidth = '148mm'

The Format => Page => [Page] setting are changed to User: with the given Height/Width.

I recommend to use the following:

ws.set_printer_settings(0, ORIENTATION_PORTRAIT)
ws.page_setup.paperHeight = '105mm'
ws.page_setup.paperWidth = '148mm'

Tested with Python: 3.4.2 - openpyxl: 2.4.1 - LibreOffice: 5.2.7.2

stovfl
  • 14,998
  • 7
  • 24
  • 51
  • thanks for the suggestion, but no luck with `openpyxl-3.0.0`, in excel the print dialog just defaults to `US Letter` for unknown enumeration `0`. Though, `70` works for `A6`, which helps for one of my use cases. – randomfigure Oct 21 '19 at 15:07
  • @randomfigure: ***"print dialog just defaults to US Letter"***: Confirm that, tried `ws.page_setup.usePrinterDefaults = False` without success. – stovfl Oct 21 '19 at 16:19
  • yes, I have tried `ws.page_setup.usePrinterDefaults = False` but it causes my other settings such as `scale` and `orientation` to be ignored/reset. – randomfigure Oct 21 '19 at 19:09
  • 1
    Excel also stores printer-specific settings as BLOBs in an Excel file: these are completely opaque to openpyxl. – Charlie Clark Oct 22 '19 at 14:16
  • @CharlieClark: Thanks for that info, could we consider, it's out of control for any 3trd party `XLSX` tool. – stovfl Oct 22 '19 at 14:38
  • 1
    Yep, like actual macro code. It's also OS specific. AFAIK page settings are for Excel's internal use only, which is why it has its own preview panes. – Charlie Clark Oct 22 '19 at 17:12