[Improve work efficiency with Python] Request: Make all Excel PDFs in this folder.

Introduction

There was such a thing. Senior "I want you to convert only the 130 Excel files in the folder and the sheet written in the sheet name to PDF. Thank you!" I "OK, I'll do it soon!" work···· I "I'm done!" Senior "Oh, thank you. It was saved!" I "Hmm, I'm tired ..." Python "Eh. Don't you know I can convert an Excel file to PDF?" I said, "Seriously. I will continue to do this kind of work, so I'll try to find out how to improve efficiency this time!"

So, I would like to explain the code to convert many Excel files in the folder to PDF, which also serves as a memorandum.

Convert Excel file to PDF

To operate Excel, we use a module called win32com. If you have never installed this module, install it with pip.

pip install pywin32

First of all, I wrote a program to convert the first sheet of one Excel file to PDF.

code.py

import win32com.client 
excel = win32com.client.Dispatch("Excel.Application")
file = excel.Workbooks.Open(r"C:\aa\bb.xlsx")
file.WorkSheets(1).Select()
file.ActiveSheet.ExportAsFixedFormat(0,r"C:\aa\bb")
file.Close()

The first line is the import of win32com The second line is the setting that operates Excel. The third line is the absolute path of the Excel file you want to convert to PDF

• By using'r', it is treated as a raw character string. The 4th row specifies the index of the sheet (in this case, the 1st sheet)
• When specifying by sheet name, use "sheet name". If you want to select multiple sheets, use a list to specify them. The 5th line is written in this folder as a PDF with the same name as Excel. Please convert it to PDF by setting the first argument to 0. Close the file opened on line 6. If you don't do this, Excel will remain read-only.

Convert all Excel in a folder to PDF

Next, I would like to write a program that converts all Excel in the folder to PDF.

First, I specified a folder and stored all the file names in that folder in the list including the extension. Then, convert the files to PDF one by one. Exception handling is used to close the file so that it closes properly even if the conversion is not successful. I used splitext () to separate the file name and extension name so that the Excel name could be a PDF name, and then passed it to the ExportAsFixedFormat () argument.

code.py

import win32com.client 
import os

excel = win32com.client.Dispatch("Excel.Application")

path = 'C:/aa/'
files = []

for filename in os.listdir(path):
    if os.path.isfile(os.path.join(path, filename)):
        files.append(filename)

for i in range(0,len(files)):
  try:
    file = excel.Workbooks.Open(path + '/' + files[i])
    file.WorkSheets("AA").Select()
    name, ext = os.path.splitext(files[i])
    file.ActiveSheet.ExportAsFixedFormat(0, path + '/' + name)
  except Exception as e:
    print(e)
  finally:
    file.Close()

at the end

Now you can easily convert any number of Excel to PDF! Python is amazing!