Simple Applications of Python in Office Automation - Minority#
#Omnivore
Matrix Home Recommendations
Matrix is the writing community of Minority, where we advocate sharing genuine product experiences and practical insights. We periodically select the highest quality articles from Matrix to showcase the most authentic experiences and viewpoints from users.
The articles represent the personal views of the authors, and Minority only makes slight modifications to the titles and formatting.
Preface#
The original intention of this article is to help more people quickly get started with simple applications of Python in office automation. The initial idea was to demonstrate each application scenario with no more than ten lines of code, but upon writing it down, I found that ten lines were still a bit short. To achieve this goal, I temporarily wrote a simple package and uploaded it to PyPI, which encapsulates some commonly used functions in office work. The relevant scripts in this article will mainly explain using this toolkit.
Many functions in this package sacrifice flexibility for ease of use, with the main goal being to allow everyone to use it first and experience the convenience, which will motivate further learning. Once you can proficiently write scripts similar to those in this article, you can directly look at the source code in my package for further study, which contains some typical examples.
Since the code will use some third-party packages unique to the Windows system, if you are using macOS or similar systems, you may not be able to reproduce all examples.
Python is an object-oriented scripting language and is considered one of the easiest programming languages to learn. If you have studied C language in college, getting started with Python can be quite easy. Even those who have never been exposed to programming can learn to use it in a short time. I’m not just saying this; I have trained colleagues before, and those willing to learn were able to get started after just a few meetings. Of course, after training, it is essential to review and consolidate on your own, as mastering new skills always requires time and effort, which I believe everyone understands.
Reading Basics#
This article mainly discusses application examples and will not introduce the basic syntax of Python. Therefore, before formally reading the subsequent content, readers need to familiarize themselves with the basic knowledge of Python. It doesn’t need to be too in-depth, but you must grasp the basic data types, loops, conditional control, functions, and classes. If you don’t know where to start, I recommend looking at the Python 3 Tutorial on the Runoob website and patiently following along with the code examples in the tutorial. If you encounter any advanced features that you cannot understand during your studies, don’t get stuck; just skip them.
Theoretically, after setting up the Python environment, you can proceed with a text editor, but if you want a better programming experience, I recommend installing PyCharm, a free Python integrated development tool.
Note: To ensure that subsequent tutorials can proceed normally, please install Python version 3.8 or above. If the official pip source downloads too slowly, you can search online for tutorials to change the source to a domestic one.
Installing Dependencies#
Use the following command to install the third-party packages that will be used in the subsequent examples:
pip install xoffice pandas openpyxl
Path Related#
Since the focus of this article is on batch operations, traversing files is an unavoidable basic content. Here I will introduce two methods; the first one is relatively simple, and you must master it; the second one is slightly more difficult, and if you don’t understand it, you can set it aside for now and consider using it later when your basics are solid.
glob.glob()#
Rename all png files in the rename folder to numbered files.
import glob
import os
if __name__ == '__main__':
# Number used during renaming
index = 1
for file in glob.glob("rename/*.png"):
# Split the path into directory path and file name
dir_path, file_path = os.path.split(file)
# Split the file name into base name and extension
base_name, ext = os.path.splitext(file_path)
# Rename the file to a format like 0001.png
os.rename(file, os.path.join(dir_path, "%04d" % index + ext))
# Effect is equivalent to index = index + 1
index += 1
The glob.glob() function is very simple to use; it can traverse all path names within a directory level and also supports the simplest regular expression filtering. The main rules are as follows:
- “*”: Matches 0 or more characters;
- “?”: Matches a single character;
- “[]”: Matches characters within a specified range, e.g., [0-9] matches digits.
In regular use, actually, a “” is enough; “.ext” matches files of a specified format; “.” matches all files with extensions, filtering out folders; “*” matches all files or folders.
By default, this function can only traverse files within one folder and cannot traverse files in subfolders, but you can achieve recursive traversal by setting recursive=True. At this point, you need to use it in conjunction with “**”, which can represent any level of directory.
for file in glob.glob("rename/**/*", recursive=True):
print(file)
Output:
rename\0001.png
rename\0002.png
rename\0003.png
rename\0004.png
rename\0005.png
rename\deep_dir
rename\deep_dir\Snipaste_2021-08-31_22-45-16.png
rename\deep_dir\Snipaste_2021-08-31_22-45-57.png
rename\deep_dir\Snipaste_2021-08-31_22-48-29.png
rename\deep_dir\Snipaste_2021-08-31_22-48-37.png
rename\deep_dir\Snipaste_2021-08-31_22-48-52.png
For beginners, it’s okay if you don’t understand the recursive traversal syntax; using “*” for the simplest single directory level traversal is sufficient.
Lastly, one point to note is that the order of files returned by glob.glob() is fixed, generally equivalent to the result of sorting by name in the file manager. In some cases where the order of files is sensitive, you need to add additional sorting code.
os.walk()#
This function is a bit convoluted; let’s look at an example:
import os
if __name__ == '__main__':
for root, dirs, files in os.walk("rename", topdown=False):
for name in files:
print(os.path.join(root, name))
for name in dirs:
print(os.path.join(root, name))
Output:
rename\deep_dir\Snipaste_2021-08-31_22-45-16.png
rename\deep_dir\Snipaste_2021-08-31_22-45-57.png
rename\deep_dir\Snipaste_2021-08-31_22-48-29.png
rename\deep_dir\Snipaste_2021-08-31_22-48-37.png
rename\deep_dir\Snipaste_2021-08-31_22-48-52.png
rename\0002.png
rename\0003.png
rename\0004.png
rename\0005.png
rename\0006.png
rename\deep_dir
First, let’s explain the basic concepts:
rootrefers to the address of the current folder being traversed;dirsis a list containing the names of all directories in that folder (excluding subdirectories);filesis also a list containing all files in that folder (excluding subdirectories).
Understanding the above example, root is successively “rename\deep_dir” and “rename”, while dirs and files contain the folder list and file list under the root directory. Note that in the code above, topdown=False means that it prioritizes traversing subfolders first. If topdown=True, then the order of root would change to “rename” and “rename\deep_dir”. If this parameter is not specified, it defaults to prioritizing the top-level directory, so you can omit it if there are no special requirements.
This function actually has several other optional parameters, but they are rarely used; you can learn them on your own if needed.
PDF Related#
Starting from this section, I will present some office automation examples I have used before, which are all relatively basic applications. More complex applications will require special customization, which this article will not cover.
Image to PDF#
I wonder if anyone has encountered the need to batch scan files for archiving. Although many times this can be solved by taking pictures with mobile software, properly archived files are better done with professional scanners. The output from scanners is generally in image format, and some scanners come with software that can convert scanned images into PDF files, while others require manual conversion in PDF software like Adobe Acrobat. Scanning and converting a single file is not too troublesome with these graphical software, but when you need to scan files for an entire afternoon, manual conversion can be quite torturous.
Here, I will use a function from the package I encapsulated, and first, I will provide the function definition:
def img_to_pdf(
dir_path: str,
free: bool = False,
size: Sequence = None,
compress: bool = False,
compress_target: int = 200,
out_path: str = None
):
"""
Convert images to PDF files
:param dir_path: The folder where the images are located; please number the images in order, e.g., 0001.jpg, 0002.jpg
:param free: If set to True, the page size dynamically changes with the image size, keeping them consistent
:param size: Page size, A0~A6, A0_L~A6_L, default A4
:param compress: If set to True, compress the images to reduce quality. Enabling this feature will significantly extend the PDF file generation time
:param compress_target: The target size for image compression, in kB
:param out_path: The output PDF file address; if empty, a PDF file with the same name will be created in the directory of the image folder
:return:
"""
The example script's function is to traverse all folder names in the pdf folder and generate a PDF file with the same name as the folder.
import glob
from xoffice import img_to_pdf
from xoffice.pdf import A4
if __name__ == '__main__':
for path in glob.glob("pdf/*"):
img_to_pdf(path, size=A4)
PDF to Image#
This scenario is relatively rare, generally used when sending files to colleagues who are temporarily unable to access a computer.
Here’s the function definition:
def pdf_to_img(
file_path: str,
index: Sequence[int] = None,
out_dir: str = None,
zoom_x: float = 4,
zoom_y: float = 4,
rotation_angle: float = 0
):
"""
Convert PDF files to PNG images
:param file_path: PDF file path
:param index: You can specify which pages to convert to images, starting from 0; defaults to all pages
:param out_dir: Output folder; defaults to creating a folder with the same name as the PDF file in the folder where the PDF file is located
:param zoom_x: X-axis zoom size
:param zoom_y: Y-axis zoom size
:param rotation_angle: Page rotation angle, clockwise is positive
:return:
"""
The example script's function is to convert the p1.pdf file to image format.
from xoffice import pdf_to_img
if __name__ == '__main__':
pdf_to_img("pdf/p1.pdf")
Email Related#
In slightly more formal daily work settings, emails may be used, and one advantage of emails is that they can leave a trace, preventing many meaningless disputes later. In most cases, sending emails via code is meaningless unless it is to send a large number of identical template notification emails, which I have encountered before, so I will demonstrate it here.
There are many packages for sending emails in Python, both official and third-party. The official one is smtplib combined with email, while unofficial ones include zmail, yagmail, etc. Below, I will demonstrate a class I encapsulated from the official package, mainly for demonstration purposes. If you seek stability, I recommend using the aforementioned third-party libraries.
Before we formally begin, I need to provide some basic knowledge about sending emails on behalf of others. Sending emails on behalf of others also requires verifying the username and password; however, the password here is not the normal password used to log in via the web but an email authorization code. You need to check the help documentation of the corresponding email service to find out how to obtain this authorization code. Here are the relevant links for the two most common email services, QQ and Sina:
First, here’s the class and function definition:
class EMail(object):
"""
Used for sending emails
"""
def __init__(self, user: str, password: str, host: str = None, port: int = 465, ssl: bool = True):
"""
Initialization
:param user: Email account
:param password: Email authorization code
:param host: Server address
:param port: Server port
:param ssl: Whether to use SSL encrypted transmission
"""
def connect(self):
"""
Connect to the email server
:return:
"""
def close(self):
"""
Disconnect from the email server
:return:
"""
def send(self, to: Union[str, Sequence[str]], title: str, content: str, attachments: Sequence[str] = None):
"""
Standard version of sending emails; needs to be used in conjunction with connect and close methods
:param to: Recipient, can be multiple
:param title: Email subject
:param content: Email content
:param attachments: Email attachments, just provide the attachment addresses
:return:
"""
def easy_send(self, to: Union[str, Sequence[str]], title: str, content: str, attachments: Sequence[str] = None):
"""
Simplified version of sending emails, integrating the connection and disconnection of the server, used for sending a single email
:param to:
:param title:
:param content:
:param attachments:
:return:
"""
def md_to_html(text: str, css: str = None) -> str:
"""
Convert md format text to html string
:param text: The md format string to be converted
:param css: CSS file
:return:
"""
The example script's function is to convert a markdown formatted text to HTML format and send it to a specified email. This function can currently only be said to work, as different email services support HTML to varying degrees, so the final presentation effect may not be consistent.
from xoffice import EMail
from xoffice.utils import md_to_html
if __name__ == '__main__':
e = EMail("user@qq.com", "password")
with open("test.md", "r", encoding="utf-8") as f:
e.easy_send(["to_user@sina.com"], "Test Subject", md_to_html(f.read()), ["pdf/p1/1.png"])
Excel Related#
Starting from this section, we will delve into the main topic. However, there are too many operations related to Excel and Word, and elaborating on them could take tens of thousands of words without fully explaining everything. I will only introduce a few of the most basic and commonly used automation operations. In the following sections, I will mainly use pywin32 to directly call the Windows system's API to operate Office software. This operation is actually not much different from writing VBA macros directly, so all operations can be found in Microsoft's Development Documentation.
In fact, there are quite a few third-party libraries with more concise and user-friendly interfaces to manipulate these Office documents. The reason I am not using them here is that some units with strong confidentiality awareness may encrypt internal files, which can only be opened normally with the Office software on encrypted computers. Friends who have encountered this should understand.
Let’s first introduce a scenario: in work, you inevitably encounter some data aggregation tasks. The specific manifestation is that a table is sent in a group for everyone to fill out, and finally, it is sent to a specific person for aggregation into a master table. This type of table can now also be summarized through cloud documents, giving everyone permission to fill in the data directly into the master table, but there are still limitations, as many pieces of information cannot be transparent to everyone. If the number of people is small, it doesn’t take too long to open each one and copy and paste, but if the scale expands, relying solely on manpower can be a bit of a waste of time.
First, here’s the class definition:
class Excel:
"""
Common automation operations for Excel, mainly to work with Word, so only methods for obtaining data are encapsulated
"""
def __init__(self, path: str, display_alerts: bool = False, visible: bool = True):
"""
Initialization
:param path:
:param display_alerts: Whether to show warning prompts when saving overrides
:param visible: Whether the program window is visible
"""
def close(self):
"""
Close the document and exit the application
:return:
"""
def get_value(self, sht_name: Union[int, str], index: str):
"""
Get data from the specified cell
:param sht_name:
:param index:
:return:
"""
def get_text(self, sht_name: Union[int, str], index: str):
"""
Get data from the specified cell, uniformly converted to text format
:param sht_name:
:param index:
:return:
"""
def get_comment(self, sht_name: Union[int, str], index: str):
"""
Get comments from the specified cell
:param sht_name:
:param index:
:return:
"""
def copy_chart(self, sht_name: Union[int, str], index: int):
"""
Copy the specified chart
:param sht_name:
:param index: Chart index
:return:
"""
def copy_shape(self, sht_name: Union[int, str], index: Union[str, int]):
"""
Copy the specified image; note that charts are also included, so be careful when using indices
:param sht_name:
:param index: Can be the name of the image or its index; here, the image can only be a floating image
:return:
"""
The example script's function is to aggregate the data from cells A2, B2, and C2 in all Excel files into a results.xlsx file.
import glob
import pandas as pd
from xoffice import Excel
if __name__ == '__main__':
results = []
for file in glob.glob("excel/*"):
with Excel(file, visible=False) as e:
data = [
e.get_value("Sheet1", "A2"),
e.get_value("Sheet1", "B2"),
e.get_value(0, "C2"),
]
results.append(data)
df = pd.DataFrame(results, columns=["a", "b", "c"])
df.to_excel("excel/results.xlsx", index=False)
The above script would be simpler to implement using xlwings. If you want to further learn about Excel automation, I recommend taking a good look at its official documentation.
Word Related#
The automation related to Word generally has only one demand: to batch produce reports based on templates. In fact, the Word software itself has such a function, but its name can be misleading; it is called mail merge. By importing external tables or other data sources and inserting mail merge fields into the Word template, each row of data can generate a corresponding document. If it is just simple text data replacement, I recommend using the software's built-in graphical interface, which allows for previewing and is simple and easy to use. The only hassle is that it requires summarizing into an Excel table first.
I will provide two optional code solutions: one using mail merge and the other using pywin32, which supports not only text replacement but also chart insertion.
generate_docx_by_mailmerge#
First, let’s introduce how to insert mail merge fields. Taking WPS as an example, select “Insert - Document Parts - Field,” and in the options that appear, choose the mail merge field name, then fill in any string in the field code on the right to mark it.
Here’s the function definition:
def generate_docx_by_mailmerge(contents: dict, template: str, output: str):
"""
Quickly produce docx documents using mail merge
:param contents: The content to be replaced in the template
:param template: Document template
:param output: Output file path
:return:
"""
The example script's function is to complete the replacement of mail merge fields in the template with the data in the dictionary within the code.
from xoffice import generate_docx_by_mailmerge
if __name__ == '__main__':
contents = {
"Header": "Wei Intelligent",
"Textbox": "Number",
"Title": "Test 123",
"Body": "abc",
"Table a": [
{"Table a": 1, "Table b": 2, "Table c": 3},
{"Table a": 2, "Table b": 3, "Table c": 4},
{"Table a": 3, "Table b": 4, "Table c": 5},
{"Table a": 4, "Table b": 5, "Table c": 6},
]
}
generate_docx_by_mailmerge(contents, "word/mailmerge.docx", "word/output.docx")
There are many third-party libraries in Python that can accomplish such operations, such as docx-mailmerge and docx-mailmerge2. I have only provided a simple encapsulation of their functions for demonstration purposes, making it easier for everyone to get started. The only thing to note here is that the mail merge fields in the tables can automatically increase the number of rows based on the data.
In fact, I rarely use scripts to implement mail merge because if it is simple data, it can be summarized using an Excel table. If it is data that requires complex calculations, I also prefer to store the results in table form first. With the table data, directly using the graphical interface of Word software is not too troublesome.
excel2word#
Next, I will introduce a method I used frequently for automating report generation. I used to often produce standard experimental reports, where the report template was fixed, and the entire report consisted of various tables, charts, sample images, etc. Since the data needed to be processed in Excel first, each experimental report had a fixed Excel processing template. After filling out the Excel table, I had to manually copy the data, charts, etc., into the Word template. To be honest, after filling it out once, I completely lost the desire to do it again. I naturally had an idea: after filling out the Excel table, let the script generate a report directly based on that Excel table and the Word template.
Here’s the class definition used:
class Word:
"""
Common operations for Word automation
"""
def __init__(self, path: str, display_alerts: bool = False, visible: bool = True):
"""
Initialization
:param path: Word file address
:param display_alerts: Whether to show warning prompts when saving overrides
:param visible: Whether the program window is visible
"""
def close(self):
"""
Close the document and exit the application
:return:
"""
def save(self, path: str):
"""
Save as another file
:param path:
:return:
"""
@property
def text(self) -> str:
"""
Get all strings in the document, including body text, headers, footers, and text within Shapes
:return:
"""
def replace_text(self, key: str, value: str):
"""
Replace specified text in the document
:param key:
:param value:
:return:
"""
def replace_content_inline_shape(self, key: int, value: str):
"""
Replace the specified inline shape in the main content of the document, keeping the width of the replaced image equal to the original image
:param key: The index of the inline shape to be replaced in the document, starting from 0
:param value: The path of the image to be used for replacement
:return:
"""
def select_content_text(self, text: str):
"""
Select specified text in the main content of the document, which can be used with paste for some interaction between Office software
:param text:
:return:
"""
def paste(self):
"""
Execute paste operation
:return:
"""
def set_content_inline_shape_size(self, index: int = None, width: float = None, height: float = None):
"""
Set the width of the specified inline image while maintaining the aspect ratio
:param index: Index starting from 0
:param width: Image width, in cm
:param height: Image height, in cm
:return:
"""
The above class, combined with the Excel class mentioned earlier, can easily achieve the initial requirement. The idea is simple: first, define the identifiers in the Word template, such as {{ 0/A1 }} representing the data in cell A1 of the first sheet in the Excel table (Python indexing starts from 0), {{ Sheet1/A2 }} representing the data in cell A2 of the sheet named Sheet1, {{ Sheet2/s:weizhineng }} representing the floating image named weizhineng in Sheet2, and {{ 1/c:0 }} representing the first inserted chart in the second sheet. Then let the script read these identifiers in the Word template, parse their meanings to find the target data in the specified location in the Excel table, and paste this data back into Word, finally saving it to generate a report.
Translating the above idea into code looks like this:
import re
from tqdm import tqdm
from xoffice.excel import Excel
from xoffice.word import Word
def excel2word(word: str, excel: str, output: str):
"""
Automatically find specified data in the Excel file based on the Word template to generate a Word report
:param word: Word template file address
:param excel: Excel data file
:param output: Output Word document address
:return:
"""
word = Word(word, visible=False)
excel = Excel(excel, visible=False)
targets = re.findall(r'({{ (.+?)/(.+?) }})', word.text)
for target in tqdm(targets):
text, sht, ind = target
if sht.isdigit():
sht = int(sht)
try:
if re.match(r'^[a-z]+[0-9]+$', ind, re.I) is not None:
word.replace_text(text, excel.get_text(sht, ind))
elif re.match(r'^c:(.+)$', ind) is not None:
chart = re.match(r'^c:(.+)$', ind).group(1)
if chart.isdigit():
chart = int(chart)
excel.copy_chart(sht, chart)
word.select_content_text(text)
word.paste()
elif re.match(r'^s:(.+)$', ind) is not None:
shape = re.match(r'^s:(.+)$', ind).group(1)
if shape.isdigit():
shape = int(shape)
excel.copy_shape(sht, shape)
word.select_content_text(text)
word.paste()
else:
print(text + ":failed")
print(text + ":success")
except Exception:
print(text + ":failed")
word.save(output)
word.close()
excel.close()
It looks quite complicated, but when you actually use it, you don’t need to write so much; a simple few lines can complete a script.
import glob
import os
from xoffice import excel2word
if __name__ == '__main__':
for path in glob.glob("word2/*.xlsx"):
dir_path, file_path = os.path.split(path)
base_name, ext = os.path.splitext(file_path)
output = os.path.join(dir_path, base_name + ".docx")
excel2word("word2/template.docx", path, output)
Currently, the excel2word function is not perfect; text replacement is all-encompassing, applicable to body text, headers, footers, and text boxes, but for images and charts, it only supports replacement and insertion within the body text. However, it is sufficient for normal use, and you generally wouldn’t think of dynamically inserting images into headers or footers. If there is such a need, it can also be implemented, but since I don’t use it, I haven’t added it. The sizes of images and charts replaced in Word are exactly the same as in Excel, so the image dimensions need to be planned in advance.
I am not sharing the source code of the excel2word function to make everyone learn it; I just want everyone to have a concept of Python's third-party packages. You don’t need to care about how complex their source code is; you just need to understand the function parameters and effects, and then directly import and use them in your scripts. All the automation scripts demonstrated in this article are around ten lines long, which is not difficult for beginners. As long as you are willing to learn, you can definitely master it in a short time.
Advanced Learning#
The scripts I introduced in this article are relatively general, but everyone will encounter different problems and scenarios. When you want to solve more personalized needs, you will need to learn some lower-level packages, such as xlwings, python-docx, pikepdf, etc. The method of learning is also very simple; just check their official documentation. You don’t need to read all of it; just look at the parts you will use, focusing on what it can do rather than how it can do it.
If you want to further expand the functionalities I demonstrated above, I recommend directly checking the source code I wrote in the Lib\site-packages\xoffice folder under the Python installation directory. The comments there are relatively complete, and if you have a foundation, it’s not difficult to understand. The example folder inside also contains several test materials that can be used to verify the effectiveness of the code. The source code uses many commonly used packages in office automation, and if you want to write scripts more flexibly, I recommend studying them in depth when needed.
If you have further interest in data processing, I recommend learning pandas, which is a powerful data processing tool in Python. It has now been integrated into Microsoft’s Excel software, and it should become widely popular sooner or later, so learning it will be beneficial. I generally use Excel for simple data processing tasks, while slightly more complex ones are directly handled using pandas. The efficiency gap between the two is significant, especially when dealing with large amounts of data. If you have ever tried to open a one or two GB-sized spreadsheet file in Excel, you should understand what I mean.
Postscript#
All of these scripts can actually be further encapsulated into command-line tools or graphical software. I have written some for colleagues before, and interested friends can give it a try to practice. In modern society, most work involves dealing with computers, and writing code is not just the privilege of programmers. Mastering some simple programming skills can make certain tasks quite simple. Python is a good entry point; you don’t need to understand how these third-party packages implement complex functions; you just need to find the packages that meet your needs, import them, and call them.
Download Minority 2.0 Client and follow Minority WeChat Official Account to unlock a new reading experience 📰
Practical and useful genuine software, presented by Minority 🚀
generate_docx_by_mailmerge
© This article is copyrighted by the author and authorized for exclusive use by Minority. Unauthorized reproduction or use without permission from Minority is prohibited.