Python-Excel - win32com with defined names or openpyxl printing?

Question

I have been automasing some big Excel spreadsheets through Python. I need to get a big input through a specific Excel tool created to provide an output. My spreadsheets have defined names for the input parameters (ie. Area insteead of C5). My input is stored in a dictionary with the keys of input parameters (Area is a key). I am trying to pass the input with name reference to the tool spreadsheet. I have managed to do that with openpyxl. However, after every itteration, I want python to print me the output to Pdf. Unfortunately, I didn't find a way with openpyxl and have resorted to putting win32com dispatch for that function.

Unfortunately when I run the combo of openpyxl and win32com , I lose some of the formating (someof the cells have a mixture of symbols and letters and openpyxl turns all to symbols in the relative cell).

The question here is:

1. Is there a way to print the file via openpyxl?

or

2. Is it possible to pass the input value with cell name into win32com (I have found only an option with cell address not cell name) so that I scrap the openpyxl?

Much appreciated!

Answer 1

You cannot print to PDF using openpyxl only change print settings per the Openpyxl Documentation. However you can print to PDF using win32com

wb.ActiveSheet.ExportAsFixedFormat(0, <path>/<pdf_filename.pdf>)

where 0 or 1 represent the type, so 0 is pdf;

class FixedFormatType:
    xlTypePDF = 0  # from enum XlFixedFormatType
    xlTypeXPS = 1  # from enum XlFixedFormatType

If you have cell C5 in your sheet with defined name 'Area' and wish to reference its value in win32com the syntax would be;

cell_value = wb.Names("Area").RefersToRange(1).Value