1. Preface
I mentioned earlier Python Handle Excel The two most common ways to file , namely ：xlrd/xlwt、openpyxl
among ,
xlrd/xlwt This combination ,xlrd Can be responsible for reading data , and xlwt Is responsible for writing data , The disadvantage is that it doesn't support xlsx
openpyxl And support for Excel Document reading 、 Write operation , The disadvantage is that it doesn't support xls
This article will continue to talk about Python operation Excel Several other ways of documentation
2. xlsxwriter
xlsxwriter It is mainly used to transfer data 、 The chart is written into Excel In file , It can be configured to use smaller memory to write data quickly
Its disadvantages are ： Can't read 、 Modify the existing Excel file ; If you need to read and modify Excel file , It can only be used with other dependent libraries , such as ：xlrd
First installation xlsxwriter The dependency package of
# Install dependency packages
pip3 install xlsxwriter
xlsxwriter Provides Workbook(filename) Method , Used to create a workbook object
Using the workbook object add_worksheet(sheet_name) function , You can create... In the workbook Sheet 了
def create_workbook_and_worksheet(filename, worksheet_names):
"""
Create workbooks and Sheet
:param filename: File name
:param worksheet_names: sheet Name list
:return:
"""
wb = xlsxwriter.Workbook(filename)
sheets = []
# newly added sheet
for worksheet_name in worksheet_names:
sheets.append(wb.add_worksheet(worksheet_name))
return wb, sheets
next , You can go to some Sheet Data has been written into the cell
If you need to customize the cell style , such as ： font size 、 typeface 、 Color 、 background 、 Whether it is bold or not , You can use the... Of the workbook object add_format() Method to create a style
def create_format_styles(wb, format_stuyles):
"""
Create a style , contain ： font size 、 typeface 、 Color 、 background 、 Whether it is bold or not
:param wb:
:param format_stuyles:
:return:
"""
return wb.add_format(format_stuyles)
# Cell font style
self.title_style = {'bold': True, 'bg_color': '#B0C4DE', 'font_size': 10,'font_name': 'Microsoft yahei'}
# Create title font styles
title_font_style = create_format_styles(self.wb, self.title_style)
Sheet Object's write(...) Function is used to write data to a cell , Parameters include ： Row index 、 Column index 、 value 、 Font style, etc
It should be noted that , Default xlsxwriter The row index of 、 Column indexes are all from 0 Start , namely ： 0 That's the first row
Write data and configure cell style as follows ：
def write_to_cell(sheet, row_index, column_index, value, format_styles=None):
"""
Write data to cells
:param row_index: Row index ,1： first line
:param column_index: Column index ,1： First column
:param format_styles Font style
:return:
"""
if row_index < 1 or column_index < 1:
print(' Incorrect parameter input , Write failure ！')
else:
# Be careful ： Default xlsxwriter The row index of 、 Column index from 0 Start
sheet.write(row_index - 1, column_index - 1, value, format_styles)
# Go to worksheet Middle write data
# first line
write_to_cell(self.current_sheet, 1, 1, " full name ", title_font_style)
write_to_cell(self.current_sheet, 1, 2, " Age ", title_font_style)
# The second line
write_to_cell(self.current_sheet, 2, 1, 'xingag')
write_to_cell(self.current_sheet, 2, 2, 23)
xlsxwriter It also supports inserting pictures into cells , contain ： Local pictures and online pictures
The way to do it is ：insert_image()
Parameters include ： Cell row index （ Index from 0 Start ）、 Unit lattice index 、 Picture file 、 Optional parameters （ Picture location 、 The zoom 、url Hyperlinks 、image_data Picture byte stream, etc ）
Take inserting a web image for example
First , Define a picture to show the optional parameters , Specifies the zoom ratio of the picture 、url Hyperlinks
def create_image_options(x_offset=0, y_offset=0, x_scale=1, y_scale=1, url=None, tip=None, image_data=None,
positioning=None):
"""
Insert the parameter configuration of the picture
contain ： Offset 、 The zoom ratio 、 Web image links 、 Hyperlinks 、 Hover light
:param x_offset:
:param y_offset:
:param x_scale:
:param y_scale:
:param url:
:param tip:
:param image_data:
:param positioning:
:return:
"""
image_options = {
'x_offset': x_offset,
'y_offset': y_offset,
'x_scale': x_scale,
'y_scale': y_scale,
'url': url,
'tip': tip,
'image_data': image_data,
'positioning': positioning,
}
return image_options
image_options = create_image_options(x_scale=0.5, y_scale=0.5, url='https://www.jianshu.com/u/f3b476549169')
next , Turn network pictures into byte streams
from io import BytesIO
import ssl
def get_image_data_from_network(url):
"""
Get network picture byte stream
:param url: Picture address
:return:
"""
ssl._create_default_https_context = ssl._create_unverified_context
# Get the byte stream of network picture
image_data = BytesIO(urlopen(url).read())
return image_data
Last , Insert a picture into a cell
def insert_network_image(sheet, row_index, column_index, url, filepath, image_options=None):
"""
Insert network picture
:param sheet:
:param row_index:
:param column_index:
:param url:
:param filepath:
:param image_options:
:return:
"""
if row_index < 1 or column_index < 1:
return " Wrong parameter input , Insert the failure ！"
# Get picture byte stream
image_data = get_image_data_from_network(url)
if image_options:
image_options['image_data'] = image_data
print(image_options)
sheet.insert_image(row_index - 1, column_index - 1, filepath, image_options)
insert_network_image(self.current_sheet, 1, 1, url, '1.png', image_options4)
Use set_column() Method to set the column width
and openpyxl similar , Yes 2 Method of use , Namely ： String index 、 Column index number index
def set_column_width(sheet, index_start, index_end, width):
"""
Set column width
:param sheet:
:param index_start: Starting position , from 1 Start
:param index_end: End position
:param width: Width
:return:
"""
# Choose one of the two ways
# self.current_sheet.set_column('A:C', width)
# Default 0 For the first column
sheet.set_column(index_start - 1, index_end - 1, width)
# Set column width
# Set the first 1 Column to the first 3 The width of the column is ：100
set_column_width(self.current_sheet, 1, 3, 100)
Line height use set_row() Method , Pass in the index and height of the row
def set_row_height(sheet, row_index, height):
"""
Set row height
:param sheet:
:param row_index: Row index , from 1 Start
:param height:
:return:
"""
sheet.set_row(row_index - 1, height)
# Set row height
set_row_height(self.current_sheet, 1, 50)
set_row_height(self.current_sheet, 2, 100)
After writing the data , Close the workbook , The file will be saved locally automatically
def teardown(self):
# write file , And close the file
self.wb.close()
xlsxwriter It also supports inserting charts , such as ： Bar chart 、 Histogram 、 Radar chart, etc , Limited by length , This part of the content will not be expanded to explain
3. The other way
Another common way is ：xlwings
xlwings Is an open source free dependency Library , Support at the same time Excel File reading 、 write in 、 modify
It's very powerful , Can also be combined with Matplotlib、Numpy and Pandas Seamless connection , Support reading and writing Numpy、Pandas data type ; meanwhile ,xlwings Can be called directly Excel In file VBA Program
It should be noted that ,xlwings Depend on Microsoft Excel Software , So use WPS 's users suggest direct use of openpyxl
Official documents ：
https://docs.xlwings.org/zh_CN/latest/quickstart.html
in addition , There's another operation Excel The more powerful way , namely ：Pywin32
among ,
Pywin32 Equivalent to calling Win The system under API To operate Excel file
Advantage is ： Data tables that can handle complex charts
The disadvantages are also very obvious , contain ： Slow speed 、 Occupy CPU high , Support only Win System
4. Last
Comprehensive discovery ,xlrd/xlwt、openpyxl、xlsxwriter Basically, it can satisfy most of the daily life Excel The document operation
To get all the source code , Official account 「 AirPython 」, The background to reply 「 excel 」 You can get all the source code
If you think the article is good , Please give the thumbs-up 、 Share 、 Leaving a message. Next , Because this will be the strongest driving force for me to continue to output more quality articles ！
Recommended reading
The most comprehensive summary | Chat Python Office automation Excel（ On ）
The most comprehensive summary | Chat Python Office automation Excel（ in ）