The introduction

Python, in recent years in the field of office automation, has been really hot! It is really much more convenient than VBA for batch processing.

Today I’m going to talk about some of the problems Python has with Excel files. In Python, there are several common libraries dedicated to working with Excel files: XLRD, XLWT, and OpenPyXL. A brief introduction:

  • XLRD can only read data and process XLS and XLSX.
  • XLWT can only write data and only process XLS;
  • Openpyxl can read and write data, but can only handle XLSX;

If you have both XLS and XLSX files to work with, I find it more convenient to convert them to a unified format and modify them twice.

There is also an Excel file, XLSM format, which is rarely supported by other libraries. In this case, if we have Microsoft Excel software installed on the computer (not WPS), we can call the local Excel software to format the table file, and then we can do other operations.

The body of the

So before I give you the code, I’m still going to give you a little bit of information, just so you can read it.

  • In the last article, because we were dealing with A Word document, we called a Word program here, so we’re dealing with an Excel document, so we need to call an Excel program here;
  • In Python, Documents are represented by Documents and Workbooks by Workbooks, so we need to call different properties to open the corresponding files for different files.
WordApp = win32com.client.dispatch ("Word.Application"WordApp = win32com.client.dispatch ()"Excel.Application") # Open Word documents.open (path) # Open Excel workbooks.open (path) # Run in background, no display, no warning wordApp.visible =0
WordApp.DisplayAlerts = 0
Copy the code

There are a lot of Excel file formats, so let me give you one more summary to give you a better impression.

It is important to note that in the following code, when SaveAs is used, we will use a FileFormat attribute, where:

  • FileFormat=51, indicates the XLSX extended file.
  • FileFormat=56, indicating XLS extended files.
  • FileFormat=52, XLSM extension file;
  • FileFormat=23, CSV extension file;

Well, the preparatory knowledge is finished, here directly to you on the code.

import os
import time
import win32com
from win32com.client import Dispatch

def xls_xlsx(path):
   w = win32com.client.Dispatch('Excel.Application')
   w.Visible = 0
   w.DisplayAlerts = 0Wb = w.wokbooks.open (path) # newPath = allPath +'\\ Converted document.xlsx '

   wb.SaveAs(newpath,FileFormat = 51)# doc.close () will delete DXLS w.Quit()# exitreturn newpath
allpath = os.getcwd()
print(allpath)
xls_xlsx(allpath+'\\ Document before conversion. XLS ')
Copy the code

Final result:

The end of this article, the code with notes, I believe you can see, an article always leave you a little space to think, here is no longer too redundant.

Read more

Top 10 Best Popular Python Libraries of 2020 \

2020 Python Chinese Community Top 10 Articles \

5 minutes to quickly master the Python timed task framework \

Special recommendation \

\

Click below to read the article and join the community