Excel office Automation with Python

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

Abstract

Python Excel is probably the hottest need for office automation right now,

Today we will share how to use Python to Excel.

Environment configuration

Python 3.10.0
Openpyxl 3.0.9
XLRD 1.3.0

Speaking of using Python to manipulate Excel, I have to introduce you to these two Python libraries,

Openpyxl 3.0.9
XLRD 1.3.0

Openpyxl

Openpyxl is a third-party Python library for processing Excel table files in XLSX format that supports most basic operations on Excel tables.

Xlrd

XLRD and the accompanying XLWT library are used by Python to read and write Excel files in bulk

XLRD is for reading Excel and XLWT is for writing Excel.

Currently, the latest version of xlrd2.0.1 does not support opening.xlsx files.

Official explanation:

xlrd is a library for reading data and formatting information from Excel files in the historical .xls format. Only old XLS files can be manipulated, how to manipulate XLSX need to call other libraries

Therefore, the library needs a dimensionality reduction installation:

pip unistall xlrd
Copy the code

Then use the domestic image source to install

pip install -i https:/ / pypi.tuna.tsinghua.edu.cn/simple XLRD = = 1.2.0
Copy the code

Play with automation

Gets the table name in an Excel table file

from openpyxl importWorkbook = load_workbook(filename = load_workbook"test.xlsx")
print(workbook.sheetnames)
Copy the code

The output is:

Get the table by sheet name

Select * from sheet where sheet=workbook[Summary table]
print(sheet)
Copy the code

The output is:

Gets the size of the table

Print (sheet. Dimensions)Copy the code

The output is:

Gets data for a cell in a table

cell1 = sheet["A1"]
cell2 = sheet["C4"]
print(cell1.value)
print(cell2.value)
print(cell1.value, cell2.value)
Copy the code

The output is

Gets the number of rows, columns, and coordinates of a cell

# row Retrieves the number of rows in a cell; # columns Retrieves the number of columns in a column; # corordinate Retrieves the coordinates of a grid. print(cell1.value, cell1.row, cell1.column, cell1.coordinate) print(cell2.value, cell2.row, cell2.column, cell2.coordinate)Copy the code

The output is:

Gets the values of a series of cells

Cell = sheet["A1:C4"]
print(cell)
for i in cell:
    for j in i:
        print(j.value)
Copy the code

The output is:

Write to a cell and save

Write a sheet to a cell ["A5"] = "Zhang Xiaosan"
workbook.save(filename = "test.xlsx")
Copy the code

To perform this step, close the Excel file first

The output is:

Note: Code should be done with the Excel file closed.

use`Excel`function

You can actually use Excel functions when manipulating Excel files in Python

For example, calculate the largest number in column C

# calculate the maximum number of sheet["C5"] ='=Max(C2:C4)'
workbook.save(filename = "test.xlsx")
Copy the code

The output is:

Use the following command to see which Excel function formulas Python supports

from openpyxl.utils import FORMULAE
print(FORMULAE)
Copy the code

The output is:

Delete operation

# to delete the first1The column, the first1Line sheet. Delete_cols (independence idx =1)
sheet.delete_rows(idx=1)
Copy the code

Create a new table

workbook.create_sheet("The new table 00")
workbook.save(filename = "test.xlsx")
Copy the code

Delete a table

sheet=workbook['new']
workbook.remove(sheet)
workbook.save(filename = "test.xlsx")
Copy the code

Modify a table name

sheet=workbook['the new table 00']
sheet.title="The new table 001"
workbook.save(filename = "test.xlsx")
Copy the code

Excel portable processing system with 10% completion

Finally, here is an Excel portable processing system combined with Tkinter with a completion degree of 10% :

The main function:

import tkinter as tk
from excelFunc import ManageSystem
from excelGui import windows
if __name__ == '__main__':
    root = tk.Tk()
    root.title("Excel Portable Processing System")
    screenwidth = root.winfo_screenwidth()
    screenheight = root.winfo_screenheight()
    root.geometry('%dx%d+%d+%d' % (800.600, (screenwidth - 800) / 2, (screenheight - 600) / 2))
    manage = ManageSystem()
    windows(root,manage)
    root.mainloop()
Copy the code

GUI function

import tkinter as tk
from tkinter import ttk

class windows:
    def __init__(self.master.manage) :self.master=master
        self.manage = manage

        self.lbl = tk.Label(self.master, text='Excel Portable Processing System ', font=('HGBTS_CNKI'.15), fg='Black')
        self.lbl.place(x=330, y=0)

        self.lbl1 = tk.Label(self.master, text='Author:', font=('HGBTS_CNKI'.15), fg='Black')
        self.lbl1.place(x=600, y=30)

        self.lbl1 = tk.Label(self.master, text='Look at that code farmer.', font=('HGBTS_CNKI'.15), fg='Black')
        self.lbl1.place(x=660, y=30)

        self.f1 = None

        self.createWidgest()

    def createWidgest(self):

        if self.f1:
            self.f1.destroy()

        self.f1 = tk.Frame(self.master)
        self.f1['width'] = 800
        self.f1['height'] = 800

        self.lab01 = tk.Label(self.f1, text='Please enter the file path:', font=('bold'.15), width=23, fg='black')
        self.lab01.place(x=70, y=0)

        self.lab01_entry = tk.Entry(self.f1,width=50)
        self.lab01_entry.place(x=300, y=3)

        self.lab02 = tk.Label(self.f1, text='Please enter Excel file name:', font=('bold'.15), width=23, fg='black')
        self.lab02.place(x=66, y=30)

        str1 = tk.StringVar()

        self.lab02_entry = tk.Entry(self.f1, width=50)
        self.lab02_entry.place(x=300, y=35)

        def cmd1():
            str1=self.manage.select_file_message(self.lab01_entry.get(),self.lab02_entry.get())
            self.text.delete(0.0.'end')
            self.text.insert(0.0, str1)

        self.btn1 = tk.Button(self.f1, text='query', width=6, height=2,command=cmd1)
        self.btn1.place(x=670, y=3)

        self.lab03 = tk.Label(self.f1, text='Tables that exist in this Excel:', font=('bold'.15), width=23, fg='black')
        self.lab03.place(x=75, y=60)

        self.text = tk.Text(self.f1, width=50, height=3)
        self.text.place(x=300, y=65)

        self.lab04 = tk.Label(self.f1, text='Please enter the table you want to open:', font=('bold'.15), width=23, fg='black')
        self.lab04.place(x=72, y=111)

        self.lab04_entry = tk.Entry(self.f1, width=50)
        self.lab04_entry.place(x=300, y=115Def showdata(self, data): # define the tree table function' ''Frame: container data: data type list'' '

            nrows = len(data)

            ncols = len(data[0])
            columns = [""]
            for i in range(ncols):
                columns.append(str(i))
            heading = columns

            """Define Treeview self.Frame2 as the parent container.headings"Indicates display header"""
            tree = ttk.Treeview(self, columns=columns, show="headings") # define column width and alignmentfor item in columns:
                tree.column(item, width=50, anchor="center")

            tree.heading(heading[0], text=heading[0}) # define the table headerfor i in range(1, len(columns)): tree. Heading (heading[I], text= STR (I)) #0
            for v in data:
                v.insert(0, i + 1Tree.insert (tree.insert)' ', i, values=(v))
                i += 1# tree.place(relx=) # tree.place(relx=0, rely=0, relwidth=1, relheight=1)

            return tree

        def cmd2(sel):
            data=self.manage.select_excel_message(self.lab01_entry.get(),
                                             self.lab02_entry.get(),
                                             self.lab04_entry.get())
            tree = showdata(sel, data)
            tree.place(relx=0.05, rely=0.2, relheight=0.3, relwidth=0.9)


        self.btn2 = tk.Button(self.f1, text='query', width=6, height=1,command=lambda:cmd2(self.f1))
        self.btn2.place(x=670, y=110)

        self.f1.place(x=0, y=80)
Copy the code

Functions:

from openpyxl import load_workbook
from openpyxl.styles import Font
import xlrd
import tkinter.messagebox

class ManageSystem(object) :def __init__(self) :self.font = Font(name="Courier New", size=9, italic=True, bold=False)

    def select_file_message(self,lab01_entry,lab02_entry):
        if lab01_entry == ' ' or lab02_entry == ' ':
            tkinter.messagebox.showinfo("Tip"."Please enter complete information 1")

        workbook = load_workbook(filename = lab01_entry+"\"+lab02_entry+".xlsx") str=','.join(workbook.sheetnames) return str def select_excel_message(self,lab01_entry,lab02_entry,lab04_entry): if lab04_entry == '': tkinter.messagebox.showinfo("prompt","Please enter complete information2") filename = lab01_entry + "\" + lab02_entry + ".xlsx" book = xlrd.open_workbook(filename) sheet = book.sheet_by_name(lab04_entry) nrows = sheet.nrows values = [] for i in range(nrows): row_values = sheet.row_values(i) values.append(row_values) return valuesCopy the code

Run the output

At present, the function of the software interface is to enter the path of the file first and then enter the folder. After querying, the existing table in the Excel will appear. Then enter the table you want to open to open the table below. And the subsequent improvement of some functions.

The system is 10% complete and needs to be further optimized. It will be published on Github after the finished product is completed.

Wonderful article, please pay attention.

Abstract

Environment configuration

Openpyxl

Xlrd

Play with automation

Gets the table name in an Excel table file

Get the table by sheet name

Gets the size of the table

Gets data for a cell in a table

Gets the number of rows, columns, and coordinates of a cell

Gets the values of a series of cells

Write to a cell and save

useExcelfunction

Delete operation

Create a new table

Delete a table

Modify a table name

Excel portable processing system with 10% completion

Related Posts

How does debugger discourage crawlers in a minute

One article to understand CountDownLatch usage and source code!

Spring Use tutorial (2) : AOP use

use`Excel`function