This article focuses on windows Under the system Python3.5 The third party in the market excel Operation Library -openpyxl;

Actually Python Third party libraries have a lot to work with Excel, Such as :xlrd,xlwt,xlwings Even annotated data analysis module Pandas Also provide pandas.read_excel、pandas.DataFrame.to_excel function .

that openpyxl What are the advantages and disadvantages of our library :

advantage :

1、openpyxl Provide right pandas Of dataframe Object perfect support ;

2、openpyxl Support background silent open excel file ;

3、 It supports excel Some of sort、filter Screening 、 Sorting function , Support rich cells style( style ) Design ;

4、 It also supports reading off the shelf excel file & Create a new excel file ;

5、 It supports the latest xlsx Format file , And update frequently , The operation is simple .

shortcoming :

1、 The operation efficiency is relatively low , When there are too many table row items , Operation relative pandas It's slow to wait ;

2、 Some of the styles are designed for rows or columns bug, Grammar can fail ;

3、 Yes sort and filter Although it supports , But it needs to be opened manually excel After refreshing the data, the search criteria will take effect , It's semi-automatic ;

4、 I won't support it excel Native automatic column width function , It's a little more complicated to achieve the same effect .

The simple properties and methods are as follows :

New workbook object :

>>> from openpyxl import Workbook
>>> wb = Workbook()

Locate the currently active worksheet :

>>> ws =

Create a new worksheet :

ws1 = wb.create_sheet("Sheet2")

Change the name of the worksheet :

ws.title = "New Sheet"

If you want to select an inactive sheet , You can specify a worksheet name , Such as :

ws=wb["New Sheet"]

Copy the worksheet as a copy :

>>> ws1 =
>>> ws2 = wb.copy_worksheet(ws1)

There are two ways to access cells , For example, access cells A3, Can write :

>>> ws['A3'] = ”hello"

or :

>>> ws.cell(row=3,column=1).value = ”hello"

It can also be simplified as :

>>> ws.cell(3,1).value = ”hello"

Access multiple cell ranges Range:

>>> cell_range = ws['A1':'B5']

Empathy , Visit a line ( Like the third line ) Can write :

>>> row3 = ws[3]

Access a column ( Such as C Column ) Can write :

>>> colC = ws['C']

If you want to access multiple rows and columns , Then use “:” Separate , Such as :

>>> col_range = ws['C:D']

>>> row_range = ws[5:10]

After the operation on the worksheet is completed, save it :

>>> wb = Workbook()
>>>'test.xlsx')  # You can specify fullname, If only the name itself , The file will be saved in py Script in the same level directory .

PS: If the file name you want to save already exists , Then this operation will override the existing file without warning .

Import openpyxl.load_workbook() Come on Open an existing workbook :

>>> from openpyxl import load_workbook
>>> wb = load_workbook('test.xlsx')

adopt append Method line by line write excel, Write from scratch 10 Row data can be traversed through these :

>>> for row in range(1, 11):
...     ws1.append(range(10))

ps:workbook Object's -data_only attribute There's a formula for controlling cells The formula ( Default ) or Excel Read the last stored value table .

Write formulas to cells , The method is as follows :

>>> ws["C1"] = "=average(A1, B1)"

Common cell merging and unmixing , The method is as follows :

>>> ws.merge_cells('A1:B5')
>>> ws.unmerge_cells('A1:B5')

Main examples :1、 utilize openpyxl The module splits worksheets into multiple workbooks based on multiple fields ,

2、 At the same time meet the style requirements , Import only part of the data marked in red , At the same time, color label specific line items ,

3、 utilize pandas The module of excel Sort files 、 Screening , Write back excel. Raw data header header as follows :

The main codes are as follows :

The main function :
1、 Generate the required fields into corresponding tables ;2、 Make the amount positive (S) Line items are marked yellow ;
3、 Import only specific materials ;4、 Restrict the naming of data sources ;
5、 Add a message reminder after the program runs ;6、 increase padas Sort 、 Filtering function
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.styles import PatternFill,Border,Side,Alignment,Protection,Font,GradientFill,Color,Colors
import easygui as eg,pandas as pd
import os,time pwd = os.getcwd()
writer = pd.ExcelWriter(' Estimation table _ By company & Supplier ranking .xlsx')
df1 = pd.DataFrame(pd.read_excel(os.path.join(pwd,' Estimation table .xlsx'),dtype={' supplier ':str,' materiel ':str,' Purchase order ':str})) # take excel Read in pandas Of DataFrame object , At the same time “ supplier 、 materiel 、 Purchase order ” And other fields are of string type , To avoid being pandas Convert to number
df1=df1.sort_values(by=[" company "," supplier "],ascending=True) # Based on the company 、 The supplier fields are arranged in ascending order , There are primary and secondary sorting of fields
df1.to_excel(writer,'Sheet1',index=False) #index=False, Indicates import excel Do not write DataFrame The index column of the object thin = Side(border_style="thin", color="") # Border style , Defined as objects if not os.path.exists(pwd+"\\ Estimation classification table "):
os.mkdir(pwd+"\\ Estimation classification table ")
wb = load_workbook(filename=pwd+u"\\ Estimation table _ By company & Supplier ranking .xlsx")
navigation=[' company ',' supplier ',' Name Description ',' materiel ',' Material description ',' Number ',' Company ',' Posting date ',' amount of money ',' Purchase order ']
ubound=ws.max_row while True: k=2
ws1.append(navigation) # The required header is fixed content , use append Method to write the first line through the list
if ubound>1:
for i in range(ubound,1,-1): # The loop range is variable , The scope gradually decreases , The number of cycles per cycle decreases , Simultaneous use break Statement to complete the interruption of the loop , In steps of -1, Indicates that the data is read from the bottom of the table to the top
for j in range(1,21): # Traverse all columns
if ws.cell(1,j).value in navigation and ws.cell(i,18).value !=None and ws.cell(i,18).value[0:7] ==" raw material - Spare parts ": # Sum up to the same excel Conditions : Suppliers in the upper and lower lines 、 The company is the same
ws1.cell(k,col).value=ws.cell(i,j).value # New table No K The row is taken from the source table i Row data if ws.cell(i,12).value=="S": # The amount is specially marked in the timing color
ws1.cell(k,col).fill=GradientFill(stop=['FFFF00', 'F5DEB3']) # Gradient yellow shading background
ws1.cell(k,8).number_format="yyyy-mm-dd" # Format cells to date
k=k+1 # The new table is written down from the first row , The source table is read up from the last line if ws.cell(i-1,1).value !=ws.cell(i,1).value or ws.cell(i-1,3).value != ws.cell(i,3).value: # Judge the condition : Different companies or different suppliers
break ubound=i-1
companyCode=ws1.cell(2,1).value # Store the company code corresponding to each sub table
vendorCode=ws1.cell(2,2).value # Store the supplier number corresponding to each sub table
if companyCode !=None:
for col in ["A","B","C","D","E","F","G","H","J"]:
#ws1.column_dimensions[col] .border=Border(top=thin, left=thin, right=thin, bottom=thin)
ws1.column_dimensions[col].width=25 # Set fixed column width
for col in range(1,11):
ws1.cell(1,col).font=Font(name='Microsoft YaHei',size=13,bold=True,color=colors.RED) # Set the font style of the title line
ws1.cell(1,col).border=Border(top=thin, left=thin, right=thin, bottom=thin) # Set the border style of the title line"\\ Estimation classification table \\%s_%s.xlsx"%(companyCode,vendorCode)) # The split form is based on the company code 、 Supplier number sorting else:
break eg.msgbox(msg='( Splitting of estimation table is completed )', title='Information', ok_button=' determine ', image=None, root=None)

The resulting table style is as follows :

Python openpyxl、pandas operation Excel Methods introduction and specific examples of more related articles

  1. turn Python - openpyxl Read and write operations Excel

    Python - openpyxl Read and write operations Excel   openpyxl characteristic   openpyxl( read-write excel surface ) Designed to handle Excel2007 And above xlsx file ,xls and xlsx Between ...

  2. Python - openpyxl Read and write operations Excel

    openpyxl characteristic   openpyxl( read-write excel surface ) Designed to handle Excel2007 And above xlsx file ,xls and xlsx It's easy to switch between Be careful : If the text code is “gb2312” After reading, it will show ...

  3. Python Pandas operation Excel

    Python Pandas operation Excel Antecedents feed * Used in this chapter Python3.6 Pandas==0.25.3 You need to use excel Too many file fields for Taking into account the subsequent changes in field naming and Chinese / english / Japan ...

  4. Python utilize pandas Handle Excel Application of data

    Python utilize pandas Handle Excel Application of data   Recently, I've been fascinated by efficient data processing pandas, In fact, this is used for data analysis , If you're doing big data analysis and testing , So this is very useful !! But in fact, we usually do ...

  5. C++ builder operation Excel Method ( According to the information on the Internet )

    c++ builder operation Excel Method , Here are some good tips from the Internet , To learn : use OLE operation Excel( The most complete information at present )(04.2.19 to update ) Part of the information in this document comes from the Internet , For the most part ccrun( ...

  6. poi-3.11-beta2-20140822.jar operation excel Method

    poi-3.11-beta2-20140822.jar operation excel Method How to read values according to different types : // Get different types of values in a cell public String getValueByType(HSSF ...

  7. Python use Pandas Reading and writing Excel

    Pandas yes python A data analysis package of , It includes a large number of databases and some standard data models , Provides the tools needed to operate large datasets efficiently .Pandas Provides a large number of functions and methods that enable us to process data quickly and conveniently . Pandas Official documents ...

  8. Python Use Pandas Read Excel Learning notes of

    Here are Python Use in Pandas Read Excel Methods One . Software environment : OS:Win7 64 position Python 3.7 Two . Document preparation 1. Project structure : 2. Create a... In the current experiment folder Source Folder ...

  9. 09 python Learning notes - operation excel( Nine )

    python operation excel Use xlrd.xlwt and xlutils modular ,xlrd Modules are read excel Of ,xlwt The module is to write excel Of ,xlutils It's for modification excel Of . These modules can be used pip install , ...

Random recommendation

  1. django+uwsgi+nginx+postgresql Memo

    install pg Create database xxx Set user password 111111 apt-get install postgresql su - postgres psql create database xxx; alter us ...

  2. 【 Build your own from scratch .NET Core Api frame 】( Two ) Build the overall structure of the project

    Series catalog One .  Create projects and integrate swagger 1.1 establish 1.2 perfect Two .  Build the overall structure of the project 3、 ... and . Integrated lightweight ORM frame ——SqlSugar 3.1 Set up the environment 3.2 Actual combat : utilize SqlSuga ...

  3. Vue Self use axios encapsulation

    [ This article is from the blog Garden of tianwaiguiyun ] This is my Vue In the project request.js file , The request is wrong. Look console There will be specific request information , Convenient debugging . Share it . Which USES axios and element-ui The components of ,ax ...

  4. java Learning notes 25(Collections class )

    Collections Algorithm class : Collections It's an algorithm class , Provides a series of static methods , To sort a collection . Replace . In exchange for . Search for . Copy and so on : usage :Collections. Method name ( The set to operate on ): It's like ...

  5. centos7 Configure Internet access

    The process is shown in the picture : just so so!

  6. 5249: [2018 Multi provincial team joint test ]IIIDX

    5249: [2018 Multi provincial team joint test ]IIIDX link analysis : greedy . Sort the given weights from large to small , Assign values one by one from the first , Consider the i You can assign values to those trees . First of all, there must be at least siz[i] I didn't choose a weight , If saved ...

  7. update t_conference set c_address = replace(c_address,' The People's Republic of China ','');

    update t_conference set c_address = replace(c_address,' The People's Republic of China ',' China ');      // hold c_address In the field ‘ The People's Republic of China ...

  8. tomcat After normal startup http://localhost:8080/ Report errors 404

    Illness : tomcat stay eclipse It's going to work , And access it in a browser http://localhost:8080/ Cannot access , And report 404 error . At the same time, other project pages cannot be accessed . close eclipse Inside tomca ...

  9. C# Winform Applet : LAN settings NTP The server 、 Time synchronization

    Set up NTP The server : NTP It's network time protocol (Network Time Protocol), It's a protocol used to synchronize the time of each computer in the network . LAN can't connect Internet, You can set up a computer to NTP The server . ...

  10. ctf subject writeup(2)

    2019.1.29 Title address : 1. Click on the link : include "flag.php";$a = @$_REQ ...