Playing excel office automation with Python

This article has participated in 「 Digging force Star Program 」, Win a creative gift bag , Challenge creation incentive fund .

Abstract

Python operation Excel It may be the hottest demand for automated office at present ,

Today, let's share how to use Python Get along well with Excel.

Environment configuration

• Python 3.10.0
• Openpyxl 3.0.9
• xlrd 1.3.0

Talking about using Python operation excel, I have to introduce these two Python library ,

• Openpyxl 3.0.9
• xlrd 1.3.0

Openpyxl

Openpyxl It's a tool for dealing with xlsx Format Excel The third party of the form file python library , It supports Excel Most of the basic operations of tables .

Xlrd

xlrd And the matching xlwt The library is used for python Read and write in bulk excel file

namely xlrd Yes. excel,xlwt Yes. excel The library of .

 

Now the latest version of xlrd2.0.1 Opening is not supported .xlsx file ,  

Official explanation ：

xlrd is a library for reading data and formatting information from Excel files in the historical .xls format. # Can only operate xls Old version file , How to operate xlsx Need to call other libraries

Therefore, the library , Dimensionality reduction installation is required :

pip unistall xlrd
 Copy code 

Then use the domestic image source to install

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

Play with automation

obtain Excel Table name in table file

from openpyxl import load_workbook
#  obtain Excel Table name in file 
workbook = load_workbook(filename = "test.xlsx")
print(workbook.sheetnames)
 Copy code 

Output is ：

adopt sheet Name get table

#  adopt sheet Name get table 
sheet=workbook[" The summary table "]
print(sheet)
 Copy code 

Output is ：

Get the size of the table

#  Output sheet（ The summary table ） Size 
print(sheet.dimensions)
 Copy code 

Output is :

Get the data of a grid in the table

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

Output is

Get the number of rows in a grid 、 Number of columns 、 coordinate

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

Output is ：

Get the values of a series of grids

#  obtain A1:C4 The value of the area 
cell = sheet["A1:C4"]
print(cell)
for i in cell:
    for j in i:
        print(j.value)
 Copy code 

Output is ：

Write content to a grid and save

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

When performing this step , Need to close first excel file

Output is ：

notes ： belong Excel Code operation when the file is closed .

Use Excel function

Actually in Python In the operation Excel You can also use Excel function

for example ： Calculation C The largest number in the column

#  Calculation C The largest number in the column 
sheet["C5"]='=Max(C2:C4)'
workbook.save(filename = "test.xlsx")
 Copy code 

Output is ：

Use the following command to view Python What support Excel Function formula

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

Output is ：

Delete operation

#  Delete the first 1 Column , The first 1 That's ok 
sheet.delete_cols(idx=1)
sheet.delete_rows(idx=1)
 Copy code 

Create a new table

workbook.create_sheet(" New table 00")
workbook.save(filename = "test.xlsx")
 Copy code 

Delete a table

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

Change the name of a table

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

To complete the degree 10% Of Excel Portable processing system

Finally, offer a combination Tkinter, To complete the degree 10% Of Excel Portable processing system ：

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 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 the Manon ', 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 path where the file is located ：', font=(' In black ', 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=(' In black ', 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=' Inquire about ', width=6, height=2,command=cmd1)
        self.btn1.place(x=670, y=3)

        self.lab03 = tk.Label(self.f1, text=' The Excel Tables that exist in ：', font=(' In black ', 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=(' In black ', 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=115)

        def showdata(self, data):
            #  Define tree view table functions 
            ''' frame: Containers  data： data , The data type is list  '''

            nrows = len(data)

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

            """  Definition Treeview self.Frame2 Parent container  columns Set column names  show="headings" Indicates the display header  """
            tree = ttk.Treeview(self, columns=columns, show="headings")

            #  Define the width and alignment of each column 
            for item in columns:
                tree.column(item, width=50, anchor="center")

            tree.heading(heading[0], text=heading[0])  #  The header of the first column is empty 

            #  Define header 
            for i in range(1, len(columns)):
                tree.heading(heading[i], text=str(i))

            #  Set table contents 
            i = 0
            for v in data:
                v.insert(0, i + 1)  #  The display of the first column ( Serial number )
                tree.insert('', i, values=(v))
                i += 1

            #  Place control ,rel* Indicates the use of relative positioning , Positioning relative to the parent container 
            # 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=' Inquire about ', width=6, height=1,command=lambda:cmd2(self.f1))
        self.btn2.place(x=670, y=110)

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

Function function ：

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(" Tips ", " Please enter the full 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(" Tips ", " Please enter the full information 2") 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 values  Copy code 

Operation output

At present, the function of the software interface is to input the path of the file first , Then enter the folder , This... Will appear after pressing query Excel Tables that exist in , Then enter the table you want to open. The table is below . As well as the subsequent improvement of some functions .

The whole completion degree of the system 10%, To be further optimized , After the finished product is completed, it will be released on Github in .

It's wonderful , Do pay attention to .