The most complete summary | talking about excel in Python office automation (Part 2)

AirPython 2020-11-11 23:31:32
complete summary talking excel python


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
wb = xlsxwriter.Workbook(filename)
sheets = []
# newly added sheet
for worksheet_name in worksheet_names:
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 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
if row_index < 1 or column_index < 1:
print(' Incorrect parameter input , Write failure !')
# 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,
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:
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='')

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
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:
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
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
# 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:
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

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 :

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 )


  1. 利用Python爬虫获取招聘网站职位信息
  2. Using Python crawler to obtain job information of recruitment website
  3. Several highly rated Python libraries arrow, jsonpath, psutil and tenacity are recommended
  4. Python装饰器
  5. Python实现LDAP认证
  6. Python decorator
  7. Implementing LDAP authentication with Python
  8. Vscode configures Python development environment!
  9. In Python, how dare you say you can't log module? ️
  10. 我收藏的有关Python的电子书和资料
  11. python 中 lambda的一些tips
  12. python中字典的一些tips
  13. python 用生成器生成斐波那契数列
  14. python脚本转pyc踩了个坑。。。
  15. My collection of e-books and materials about Python
  16. Some tips of lambda in Python
  17. Some tips of dictionary in Python
  18. Using Python generator to generate Fibonacci sequence
  19. The conversion of Python script to PyC stepped on a pit...
  20. Python游戏开发,pygame模块,Python实现扫雷小游戏
  21. Python game development, pyGame module, python implementation of minesweeping games
  22. Python实用工具,email模块,Python实现邮件远程控制自己电脑
  23. Python utility, email module, python realizes mail remote control of its own computer
  24. 毫无头绪的自学Python,你可能连门槛都摸不到!【最佳学习路线】
  25. Python读取二进制文件代码方法解析
  26. Python字典的实现原理
  27. Without a clue, you may not even touch the threshold【 Best learning route]
  28. Parsing method of Python reading binary file code
  29. Implementation principle of Python dictionary
  30. You must know the function of pandas to parse JSON data - JSON_ normalize()
  31. Python实用案例,私人定制,Python自动化生成爱豆专属2021日历
  32. Python practical case, private customization, python automatic generation of Adu exclusive 2021 calendar
  33. 《Python实例》震惊了,用Python这么简单实现了聊天系统的脏话,广告检测
  34. "Python instance" was shocked and realized the dirty words and advertisement detection of the chat system in Python
  35. Convolutional neural network processing sequence for Python deep learning
  36. Python data structure and algorithm (1) -- enum type enum
  37. 超全大厂算法岗百问百答(推荐系统/机器学习/深度学习/C++/Spark/python)
  38. 【Python进阶】你真的明白NumPy中的ndarray吗?
  39. All questions and answers for algorithm posts of super large factories (recommended system / machine learning / deep learning / C + + / spark / Python)
  40. [advanced Python] do you really understand ndarray in numpy?
  41. 【Python进阶】Python进阶专栏栏主自述:不忘初心,砥砺前行
  42. [advanced Python] Python advanced column main readme: never forget the original intention and forge ahead
  43. python垃圾回收和缓存管理
  44. java调用Python程序
  45. java调用Python程序
  46. Python常用函数有哪些?Python基础入门课程
  47. Python garbage collection and cache management
  48. Java calling Python program
  49. Java calling Python program
  50. What functions are commonly used in Python? Introduction to Python Basics
  51. Python basic knowledge
  52. Anaconda5.2 安装 Python 库(MySQLdb)的方法
  53. Python实现对脑电数据情绪分析
  54. Anaconda 5.2 method of installing Python Library (mysqldb)
  55. Python implements emotion analysis of EEG data
  56. Master some advanced usage of Python in 30 seconds, which makes others envy it
  57. python爬取百度图片并对图片做一系列处理
  58. Python crawls Baidu pictures and does a series of processing on them
  59. python链接mysql数据库
  60. Python link MySQL database