Reading and writing excel table with Python

Full stack programmer webmaster 2021-04-07 12:26:58
reading writing excel table python

Python Can read and write Excel Is it a form ?

Certainly. . Python There are many class libraries that can do it , openpyxl Is one of the best .

openpyxl The design is very beautiful , You're going to love it ! If you don't believe me, please look down :


Start openpyxl front , There's no need to build a workbook in advance ( Workbook ). Just import Workbook class , Then you can create a new workbook in memory and start :

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

By default, a worksheet is created in advance for a new workbook , adopt active Property to get :

>>> ws =

annotation If the workbook contains multiple worksheets , This property returns the first .

adopt create_sheet Method , You can create new worksheets . The creation can be done by appending :

>>> ws1 = wb.create_sheet('Mysheet')

perhaps , Insert before :

>>> ws1 = wb.create_sheet('Mysheet', 0)

If you do not specify a table name, it will be automatically generated , The sequence looks like : SheetSheet1Sheet2 , And so on . Yes, of course , If you don't think it's appropriate, you can modify it :

>>> ws.title = 'New Title'

The background color of the sheet title label is white by default . Use one RGB Color code settings sheet_properties.tabColor Property can be modified :

>>> ws.sheet_properties.tabColor = "1072BA"

Once you name your worksheet , You can use the name to locate :

>>> ws3 = wb["New Title"]

adopt sheetnames attribute , You can take out all sheet names :

>>> print(wb.sheetnames)
['Sheet2', 'New Title', 'Sheet1']

Yes, of course , Traverse all worksheets , direct for-in More elegant :

>>> for sheet in wb:
... print(sheet.title)

Use copy_worksheet Method , You can copy worksheets in the workbook :

>>> source =
>>> target = wb.copy_worksheet(source)

Load from file

If you have a workbook , It can be done by openpyxl.load_workbook Function to load :

>>> from openpyxl import load_workbook
>>> wb2 = load_workbook('test.xlsx')
>>> print(wb2.sheetnames)
['Sheet2', 'New Title', 'Sheet1']

Data processing

Single cell

Operation sheet , Start by modifying the cell contents . Cells can be accessed directly through the worksheet key :

>>> cell = ws['A4']

This statement will return A4 Cell , Or create a cell when it doesn't exist . You can assign values directly :

>>> ws['A4'] = 10

Another way is to use cell Method to access cells , Specify rows and columns :

>>> cell = ws.cell(row=4, column=2, value=10)

annotation After the worksheet is created , Does not contain any cells , Cells are created automatically the first time they are accessed .


Multiple consecutive cells can be obtained by slicing :

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

It's also possible to take a row or column as a unit :

# Take out C Column
>>> colC = ws['C']
# Take out C to D Column
>>> col_range = ws['C:D']
# Take out No 10 That's ok
>>> row10 = ws[10]
# Take out No 5 to 10 That's ok
>>> row_range = ws[5:10]

Use iter_rows The method can be :

# From 1 Line starts traversing , Until the first 2 That's ok , Return at most... Per line 3 Column
>>> for row in ws.iter_rows(min_row=1, max_row=2, max_col=3):
... for cell in row:
... print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>

To traverse all the rows or columns of a table , You can use related properties . Use rows Property traverses all rows :

>>> ws =
>>> ws['C9'] = 'hello world'
>>> tuple(ws.rows)
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))

Use columns Property traverses all columns :

>>> tuple(ws.columns)
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
<Cell Sheet.A4>,
<Cell Sheet.A5>,
<Cell Sheet.A6>,
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
<Cell Sheet.C2>,
<Cell Sheet.C3>,
<Cell Sheet.C4>,
<Cell Sheet.C5>,
<Cell Sheet.C6>,
<Cell Sheet.C7>,
<Cell Sheet.C8>,
<Cell Sheet.C9>))

data storage

Excel Tables store data in cells , Direct assignment :

>>> cell.value = 'hello, world'
>>> print(cell.value)
'hello, world'
>>> cell2.value = 3.14
>>> print(cell2.value)

meanwhile , You can also attach type and formatting information to cells , When creating a workbook, you need to specify guess_types Parameters :

>>> wb = Workbook(guess_types=True)

thus , Text ( Include percentage ) Will be automatically converted to floating point numbers :

>>> cell.value = '31.50'
>>> print(cell.value)
>>> cell2.value = '12%'
>>> print(cell2.value)

Dates can be directly derived from native datetime Object to set :

>>> import datetime
>>> cell.value =
>>> print cell.value
datetime.datetime(2010, 9, 10, 22, 25, 18)

Save to file

The safest way to save it is to call save Method to save to the specified file :

>>> wb = Workbook()

Warning This operation will override the existing file , There's no hint !

With the help of template attribute , You can save a worksheet as a template document :

>>> wb = load_workbook('document.xlsx')
>>> wb.template = True

Or save it as a normal document :

>>> wb = load_workbook('document_template.xltx')
>>> wb.template = False
>>>'document.xlsx', as_template=False)

Save to stream

stay FlaskDjango etc. Web application , You may need to save the file to the stream ( stream ). With a temporary file ( NamedTemporaryFile ) Can be easily implemented :

>>> from tempfile import NamedTemporaryFile
>>> from openpyxl import Workbook
>>> wb = Workbook()
# Save to a temporary file first , Then read out the contents of the file
>>> with NamedTemporaryFile() as tmp:
... stream =

Participation of this paper Tencent cloud media sharing plan , You are welcome to join us , share .

本文为[Full stack programmer webmaster]所创,转载请带上原文链接,感谢

  1. Python brush questions - letter graphics
  2. Python数据分析入门(七):Pandas层级索引
  3. Introduction to Python data analysis (7): Pandas hierarchical index
  4. Python 操作腾讯云短信(sms)详细教程
  5. Python operation Tencent cloud SMS (SMS) detailed tutorial
  6. Python数据可视化,完整版实操指南 !
  7. Python data visualization, full version of the practical guide!
  8. 上手Pandas,带你玩转数据(2)-- 使用pandas从多种文件中读取数据
  9. 上手Pandas,带你玩转数据(1)-- 实例详解pandas数据结构
  10. Using pandas to read data from various files
  11. Hands on pandas, take you to play with data (1) -- detailed explanation of pandas data structure with examples
  12. Pandas数据结构基础用法
  13. Basic usage of pandas data structure
  14. Python读取ini配置文件,保存到对象属性
  15. Python reads the INI configuration file and saves it to the object properties
  16. Foundation of Python: classes in Python
  17. python刷题-闰年判断
  18. python刷题-01字串
  19. How to judge leap year
  20. Python brush title-01 string
  21. 安装python
  22. 按尺寸切片pandas数据集DataFrame到多个文件
  23. Install Python
  24. Slice the pandas dataset dataframe to multiple files by size
  25. python 求最大值、最小值、平均值
  26. Finding maximum, minimum and average in Python
  27. 认识Python解释器和PyCharm编辑器
  28. Know Python interpreter and pycharm editor
  29. Python 小数据池和代码块缓存机制
  30. Python small data pool and code block caching mechanism
  31. python刷题-序列求和
  32. python刷题-圆的面积
  33. Sequence summation in Python
  34. The area of a circle
  35. Python functions, advanced syntax and usage
  36. Teach you to crawl novels in Python! Who can pay for novels these days!
  37. Python入门学习之:10分钟1500访问量
  38. Introduction to Python: 1500 visits in 10 minutes
  39. 数据分析之Pandas合并操作总结
  40. OpenCV-Python 雪花飘落特效
  41. Pandas merge operation summary of data analysis
  42. Opencv Python snowflake falling effect
  43. python logging模块“另一个程序正在使用此文件,进程无法访问。”问题解决办法
  44. Python logging module "this file is being used by another program and cannot be accessed by the process." Problem solving
  45. Mac 下python3 [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed 解决方法
  46. Python 3 [SSL: Certificate] on MAC_ VERIFY_ Failed] certificate verify failed solution
  47. Python学习之解决python下载第三方依赖速度慢的问题
  48. Python learning to solve the problem of slow download speed of third party dependence on Python
  49. python操作Excel文件报lrd.biffh.XLRDError
  50. How to operate excel file with Python lrd.biffh.XLRDError
  51. 2021的挑战与机遇,今年Python数据分析岗位会很香!
  52. The challenge and opportunity of 2021, python data analysis post will be very popular this year!
  53. 【C++简明教程】Python和C++指定元素排序比较
  54. Comparison of Python and C + + specified element sorting
  55. Python Flask使用Nginx做代理时如何获取真实IP
  56. How to get real IP address when Python flash uses nginx as proxy
  57. Python培训出来好找工作吗?好找工作的关键是什么?
  58. Is Python training easy to find a job? What is the key to finding a good job?
  59. 从零开始学python | 什么是Python JSON?
  60. Learn Python from scratch | what is Python JSON?