current position:Home>Playing excel office automation with Python
Playing excel office automation with Python
2022-01-30 00:29:52 【Look at that yard farmer】
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 .
copyright notice
author[Look at that yard farmer],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/01/202201300029488214.html
The sidebar is recommended
- Install tensorflow and python 3.6 in Windows 7
- Python collects and monitors system data -- psutil
- Getting started with Python - object oriented - special methods
- Teach you how to use Python to transform an alien invasion game
- You can easily get started with Excel. Python data analysis package pandas (VI): sorting
- Implementation of top-level design pattern in Python
- Using linear systems in python with scipy.linalg
- How to get started quickly? How to learn Python
- Modifying Python environment with Mac OS security
- Better use atom to support jupyter based Python development
guess what you like
-
Better use atom to support jupyter based Python development
-
Fast power modulus Python implementation of large numbers
-
Python architects recommend the book "Python programmer's Guide" which must be read by self-study Python architects. You are welcome to take it away
-
Decoding the verification code of Taobao slider with Python + selenium, the road of information security
-
Python game development, pyGame module, python implementation of skiing games
-
Python collects and monitors system data -- psutil
-
Python + selenium automated test: page object mode
-
You can easily get started with Excel. Python data analysis package pandas (IV): any grouping score bar
-
Opencv skills | saving pictures in common formats as transparent background pictures (with Python source code) - teach you to easily make logo
-
Python ThreadPoolExecutor restrictions_ work_ Queue size
Random recommended
- Python generates and deploys verification codes with one click (Django)
- With "Python" advanced, you can catch all the advanced syntax! Advanced function + file operation, do not look at regret Series ~
- At the beginning of "Python", you must see the series. 10000 words are only for you. It is recommended to like the collection ~
- [Python kaggle] pandas basic exercises in machine learning series (6)
- Using linear systems in python with scipy.linalg
- The founder of pandas teaches you how to use Python for data analysis (mind mapping)
- Using Python to realize national second-hand housing data capture + map display
- Python image processing, automatic generation of GIF dynamic pictures
- Pandas advanced tutorial: time processing
- How to make Python run faster? Six tips!
- Django: use of elastic search search system
- Python 3.10 official release
- Python chat room (Tkinter writing interface, streaming, socket to realize private chat, group chat, check chat records, Mysql to store data)
- This pandas exercise must be successfully won
- [algorithm learning] sword finger offer 64 Find 1 + 2 +... + n (Java / C / C + + / Python / go / trust)
- leetcode 58. Length of Last Word(python)
- Problems encountered in writing the HTML content of articles into the database during the development of Django blog
- Understand Python's built-in function and add a print function yourself
- Python implements JS encryption algorithm in thousands of music websites
- leetcode 35. Search Insert Position(python)
- leetcode 1829. Maximum XOR for Each Query(python)
- [introduction to Python visualization]: 12 small examples of complete data visualization, taking you to play with visualization ~
- Learning this Python library can reduce at least 100 lines of code
- leetcode 67. Add Binary(python)
- Regular re parameter replacement of Python 3 interface automation test framework
- V. pandas based on Python
- Only 15 lines of code is needed for face detection! (using Python and openCV)
- [Python crawler Sao operation] you can crawl Sirius cinema movies without paying
- leetcode 69. Sqrt(x)(python)
- Teach you to read the source code of Cpython (I)
- Snowball learning started in the fourth quarter of Python. One needs three meals. I have a new understanding of Python functional programming, process-oriented, object-oriented and functional
- leetcode 88. Merge Sorted Array(python)
- Don't you know more about a python library before the end of 2021?
- Python crawler web page parsing artifact XPath quick start teaching!!!
- Use Python and OpenCV to watermark the image
- String and related methods of Python data type introduction
- Heapq module of Python module
- Introduction to beautiful soup of Python crawler weapon, detailed explanation, actual combat summary!!!
- Event loop of Python collaboration series
- Django docking pin login system