Shulou(Shulou.com)06/03 Report--

Today, I will talk to you about how to use Python to automate Microsoft Excel and Word. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Usually, every week we combine the two and take advantage of them in some way. Although automation is not required for general day-to-day use, sometimes automation may be necessary. That is, when you have a large number of charts, graphs, tables, and reports to generate, it can become an extremely tedious task if you choose to do it manually. There's no need for that. In fact, there is a way to create a pipe in Python, and you can seamlessly integrate the two, generate a spreadsheet in Excel, then transfer the results to Word, and generate a report almost instantly.

Openpyxl

Openpyxl is arguably one of the most general-purpose packages in Python, and it is very easy to use the Excel interface. With it, you can read and write all the current and earliest excel formats, namely xlsx and xls. Openpyxl allows you to populate rows and columns, execute formulas, create 2D and 3D charts, mark axes and headers, and many other very useful features. Most importantly, however, this package allows you to traverse an infinite number of rows and columns in Excel, avoiding all the annoying digital processing and drawing that you had to do before.

Python-docx

Then there is Python-docx, which is to Word what Openpyxl is to Excel. If you haven't learned their documentation, you might want to take a look at it. It is no exaggeration to say that Python-docx is one of the simplest and most self-evident toolkits I have used since I started using Python. It allows you to automatically generate documents by inserting text, populating tables, and automatically rendering images in the report.

Back to the point, let's create our own automation assembly line. Continue to use Anaconda (or any other IDE you choose) and install the following packages:

Pip install openpyxl pip install python-docxMicrosoft Excel Automation

First, we will load an Excel workbook that has been created (as shown below):

Workbook = xl.load_workbook ('Book1.xlsx') sheet_1 = workbook [' Sheet1']

We will then iterate through all the rows in the spreadsheet and calculate and insert the power value by multiplying the current by the voltage:

For row in range (2, sheet_1.max_row + 1): current = sheet_1.cell (row, 2) voltage = sheet_1.cell (row, 3) power = float (current.value) * float (voltage.value) power_cell = sheet_1.cell (row, 1) power_cell.value = power

Once done, we will use the calculated power value to generate a line chart, which will be inserted into the specified cell, as shown in the following figure:

Values = Reference (sheet_1, min_row = 2, max_row = sheet_1.max_row, min_col = 1, max_col = 1) chart = LineChart () chart.y_axis.title = 'Power' chart.x_axis.title =' Index' chart.add_data (values) sheet_1.add_chart (chart, 'e2') workbook.save (' Book1.xlsx')

Now that we have generated the chart, we need to extract it into an image in order to use it in the Word report. First, we will determine the exact location of the Excel file and where the output chart image should be saved:

Input_file = "C:/Users/.../Book1.xlsx" output_image = "C:/Users/.../chart.png"

Then use the following methods to access the spreadsheet:

Operation = win32com.client.Dispatch ("Excel.Application") operation.Visible = 0 operation.DisplayAlerts = 0 workbook_2 = operation.Workbooks.Open (input_file) sheet_2 = operation.Sheets (1)

You can iterate over all the chart objects in the spreadsheet (if there is more than one) and save them in the specified location as follows:

For x, chart in enumerate (sheet_2.Shapes): chart.Copy () image = ImageGrab.grabclipboard () image.save (output_image, 'png') passworkbook_2.Close (True) operation.Quit () Microsoft Word Automation

Now that we have generated the chart image, we must create a template document, which is basically a normal Microsoft Word document (.docx), which is exactly what we want the report to look like, including font, font size, format, and page structure. Then all we need to do is create placeholders for our automatic content, that is, table values and images, and declare them with variable names, as shown below.

Any automatic content can be declared in double curly braces {{variable_name}}, including text and images. For a table, you need to create a template row table that contains all the columns, and then you need to add a row above and a line below, with the following symbols:

First line:

{% tr for item in variable_name%}

The last line:

{% tr endfor%}

In the figure above, the variable name is:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Python dictionary used by table_contents to store tabular data

Index of the dictionary key (first column)

Power, current and voltage of dictionary values (second, third and fourth columns)

Then we import the template document into Python and create a dictionary to store the values in the table:

Template = DocxTemplate ('template.docx') table_contents = [] for i in range (2, sheet_1.max_row + 1): table_contents.append ({' Index': imur1, 'Power': sheet_1.cell (I, 1). Value,' Current': sheet_1.cell (I, 2). Value, 'Voltage': sheet_1.cell (I, 3). Value})

Next, we will import the chart image previously generated by Excel and create another dictionary to instantiate all placeholder variables declared in the template document:

Image = InlineImage (template,'chart.png',Cm (10)) context = {'title':' Automated Report', 'day': datetime.datetime.now (). Strftime ('% d'), 'month': datetime.datetime.now (). Strftime ('% b'), 'year': datetime.datetime.now (). Strftime ('% Y'), 'table_contents': table_contents,' image': image}

Finally, we will render the report with our values table and chart image:

Template.render (context) template.save ('Automated_report.docx') result

Okay, this is an automatically generated Microsoft Word report with numbers and charts created in Microsoft Excel. This way you have a fully automated pipeline that can be used to create as many tables, charts, and documents as possible.

After reading the above, do you have any further understanding of how to use Python to automate Microsoft Excel and Word? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.