Python operations Excel

OPENPYXL is a common Python library used to read and write data to and from Excel's common format and its templates.

Installation

Installation

pip install openpyxl

Pillow: Need to use images (JPEG, PNG, BMP,...) in the file , you need to install the Pillow Library.

Note: When you write, close the Excel file that you want to work on, otherwise the write is unsuccessful

Loading xlsx Action Content

To load an xlsx step for external Excel:
Load the xlsx file and get a handle to the file for workbook

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

Create a new workbook in memory workbook

Starting with OPENPYXL, it is not necessary to create a file on the file system.
You only need to import the workbook class and start using it. You can think of Wordbook as an Excel file.

from openpyxl import Workbookwb = Workbook()

At least one worksheet must be created after workbook is created. Useopenpyxl.workbook.Workbook.active()

ws = wb.active

The method uses the _active_sheet_index property, which is set by default to 0

create Worksheet

Use openpyxl.workbook.Workbook.create_sheet() to create a new worksheet

ws1 = wb.create_sheet("new_sheet_1") # 新建sheet,插入到最后(默认)ws2 = wb.create_sheet("new_sheet_2", 0) # 插入到最开始的位置

Specify the name of the worksheet

Automatically grow according to Sheet,sheet1,sheet2 when sheet name is not specified

ws3 = wb.create_sheet()

custom worksheet name, specifying a name by the Title property, setting the name of the sheet

ws3.title = "new_sheet_3"

Output WB now all SheetName view
Through openpyxl.workbook.Workbook.sheetnames() the output WB now all SheetName

print wb.get_sheet_names()print(wb.sheetnames)

Cycle

for sheet in wb:print(sheet.title)

Specifies the color of the worksheet tab button

The New sheet tab color is white and you can specify the color of the sheet tab button separately

ws3.sheet_properties.tabColor = "1072BA"

Activating a worksheet

Activate a sheet using the worksheet name
Name as a key in workbook

ws3 = wb["new_sheet_3"]

By _active_sheet_index activating a sheet to use

wb._active_sheet_index = 1 # 获取第二个sheet

Create a worksheet copy

Using the openpyxl.workbook.Workbook.copy_worksheet() Create worksheet copy

source = wb.activetarget = wb.copy_worksheet(source)

Only the cell and style can be copied. You cannot copy workbooks between worksheets.

Manipulating cells

When worksheet is created in memory, it does not contain cells, cells are created on first visit
A cell can be considered a worksheet key to access the data in a cell by key

c = ws['A3'] # 访问单元格,不存在则会自动创建一个print c.valuews['A4'] = 'a4 value' # 指定单元格的值print ws['A4'].valued = ws.cell(row=4, column=2, value='通过cell指定') # 通过row column数字指定print d.value

Loop creates cells in memory

You can create it in memory without specifying its value

for i in range(1, 10):for j in range(1, 10):ws.cell(row=i, column=j)

Specify many cells by slicing ranges

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

You can also ranges rows or columns

print ws['C']print ws['C:D']print ws[10]print ws[5:10]

You can also use Iter_rows () to specify line-by-row, cutoff column

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):for c in row:print(c)

Iter_cols () Specify column-by-column, cutoff line

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):for c in row:print(c)

Traverse the rows or columns of all files

ws['C9'] = 'hello world'tuple(ws.rows) # 转化成tuple方便for in操作tuple(ws.columns)

Save File

wb = Workbook()wb.save("assets/sample.xlsx")

This action overwrites the existing file without warning.

All operations need to be saved to Excel to see

Save As Template

as_template=TrueYou can save the document as a template by specifying a property. xltx

wb = load_workbook('document.xlsx')wb.template = Truewb.save('document_template.xltx')

or set this property to False (the default) and save as a document:

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

Working with Formulas

wb = load\_workbook\('assets/sample.xlsx'\)ws = wb.activews\["A5"\] = "=SUM\(2, 1\)"wb.save\("assets/sample.xlsx"\)