Python tutorial on reading and writing excel using openpyxl

Python uses openpyxl to read and write excel files

This is a third-party library that can handle Excel files in the xlsx format. pip install openpyxlInstallation. If you use Aanconda, it should come with it.

Reading Excel files

Needs to import related functions.

from openpyxl import load_workbook# 默认可读写，若有需要可以指定write_only和read_only为Truewb = load_workbook('mainbuilding33.xlsx')

The file opened by default is readable and writable. If necessary, you can specify the parameter read_only to True.

Get the worksheet--Sheet

# 获得所有sheet的名称print(wb.get_sheet_names())# 根据sheet名字获得sheeta_sheet = wb.get_sheet_by_name('Sheet1')# 获得sheet名print(a_sheet.title)# 获得当前正在显示的sheet, 也可以用wb.get_active_sheet()sheet = wb.active

Get the cell

# 获取某个单元格的值，观察excel发现也是先字母再数字的顺序，即先列再行b4 = sheet['B4']# 分别返回print(f'({b4.column}, {b4.row}) is {b4.value}')  # 返回的数字就是int型# 除了用下标的方式获得，还可以用cell函数, 换成数字，这个表示B2b4_too = sheet.cell(row=4, column=2)print(b4_too.value)

b4.column Return B, b4.row returns 4, and value is the value of that cell. In addition, cell also has an attribute coordinate. Cells like b4 return coordinates B4.

Get the maximum row and maximum column

# 获得最大列和最大行print(sheet.max_row)print(sheet.max_column)

Get the row and column

• sheet.rows It is a generator, which contains the data of each row, and each row is wrapped by a tuple.

• sheet.columns is similar, but each tuple is a cell in each column.

# 因为按行，所以返回A1, B1, C1这样的顺序for row in sheet.rows:for cell in row:print(cell.value)# A1, A2, A3这样的顺序for column in sheet.columns:for cell in column:print(cell.value)

The above code can get the data of all cells. What if you want to get the data of a certain row? Just give it an index, Because sheet.rows is a generator type, index cannot be used, convert it to list and then use index , list(sheet.rows)[ 2]In this way, the tuple object of the second row is obtained.

for cell in list(sheet.rows)[2]:print(cell.value)

How to get cells in any range?

You can use the range function. The following writing method obtains all the cells in the rectangular area with A1 as the upper left corner and B3 as the lower right corner. Noterange starts from 1, because in openpyxl, in order to be consistent with the expression in Excel, it is not consistent with the convention of programming languages ​​to use 0 to represent the first value.

for i in range(1, 4):for j in range(1, 3):print(sheet.cell(row=i, column=j))        
# out<Cell mainbuilding33.A1><Cell mainbuilding33.B1><Cell mainbuilding33.A2><Cell mainbuilding33.B2><Cell mainbuilding33.A3><Cell mainbuilding33.B3>

Can also be used like a slice. sheet['A1':'B3']Returns a tuple. The tuple is also a tuple inside, and a tuple is composed of cells in each row.

for row_cell in sheet['A1':'B3']:for cell in row_cell:print(cell)        

for cell in sheet[&#39;A1&#39;:&#39;B3&#39;]:print(cell)# out(<Cell mainbuilding33.A1>, <Cell mainbuilding33.B1>)
(<Cell mainbuilding33.A2>, <Cell mainbuilding33.B2>)
(<Cell mainbuilding33.A3>, <Cell mainbuilding33.B3>)

Get the column number based on the letters, and return the letters based on the column number

Need to be imported, these two functions exist in openpyxl.utils

from openpyxl.utils import get_column_letter, column_index_from_string# 根据列的数字返回字母print(get_column_letter(2))  # B# 根据字母返回列的数字print(column_index_from_string('D'))  # 4

Write data to Excel

Worksheet related

Requires importWorkBook

from openpyxl import Workbook

wb = Workbook()

This creates a new worksheet (it has just not been saved yet).

To specify write-only mode, you can specify the parameter write_only=True. Generally, the default writable and readable mode is sufficient.

print(wb.get_sheet_names())  # 提供一个默认名叫Sheet的表，office2016下新建提供默认Sheet1# 直接赋值就可以改工作表的名称sheet.title = 'Sheet1'# 新建一个工作表，可以指定索引，适当安排其在工作簿中的位置wb.create_sheet('Data', index=1)  # 被安排到第二个工作表，index=0就是第一个位置# 删除某个工作表wb.remove(sheet)del wb[sheet]

Writing cells

You can also use formulas

# 直接给单元格赋值就行sheet['A1'] = 'good'# B9处写入平均值sheet['B9'] = '=AVERAGE(B2:B8)'

But if it is read When you need to add data_only=True, the number returned after reading B9 is the number. If you do not add this parameter, the formula itself will be returned '=AVERAGE(B2:B8)'

append function

You can add multiple rows of data at one time, starting from the first blank line (the following are all blank lines).

# 添加一行row = [1 ,2, 3, 4, 5]
sheet.append(row)# 添加多行rows = [
    ['Number', 'data1', 'data2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]

Since the append function can only write row by row. What if we want to write column by column. Can append meet the requirements? If you think of the nested list above as a matrix. Just transpose the matrix. This can be achieved using the zip() function, but the internal list becomes a tuple. They are all iterable objects and have no effect.

list(zip(*rows))# out[('Number', 2, 3, 4, 5, 6, 7),
 ('data1', 40, 40, 50, 30, 25, 50),
 ('data2', 30, 25, 30, 10, 5, 10)]

Explain the above list(zip(*rows))First *rowsbreak up the list, which is equivalent to filling in Several parameters, zip extract the first value from a list and combine it into a tuple, then extract the second value from each list and combine it into a tuple, until the shortest list ends after the last value is extracted, and subsequent values ​​in the longer list are discarded. In other words, The final number of tuples is determined by the shortest length of each original parameter (iterable object) . For example, if you delete a value at will, the shortest list length is 2, and all the values ​​in the data2 column (viewed vertically) will be discarded.

rows = [
    ['Number', 'data1', 'data2'],
    [2, 40],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]# out[('Number', 2, 3, 4, 5, 6, 7), ('data1', 40, 40, 50, 30, 25, 50)]

Finally, zip returns a zip object, and the data cannot be seen. Just use list to convert it. Using zip can facilitate writing data in columns.

Save the file

After all operations are completed, be sure to save the file. Specify the path and file name, with the suffix xlsx.

wb.save(r'D:\example.xlsx')

Set cell style--Style

First import the required classesfrom openpyxl.styles import Font, colors, Alignment

You can specify font related, color, and alignment methods respectively.

Font

bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)

sheet['A1'].font = bold_itatic_24_font

上面的代码指定了等线24号加粗斜体，字体颜色红色。直接使用cell的font属性，将Font对象赋值给它。

对齐方式

也是直接使用cell的属性aligment，这里指定垂直居中和水平居中。除了center，还可以使用right、left等等参数。

# 设置B1中的数据垂直居中和水平居中sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')

设置行高和列宽

有时候数据太长显示不完，就需要拉长拉高单元格。

# 第2行行高sheet.row_dimensions[2].height = 40# C列列宽sheet.column_dimensions['C'].width = 30

合并和拆分单元格

所谓合并单元格，即以合并区域的左上角的那个单元格为基准，覆盖其他单元格使之称为一个大的单元格。

相反，拆分单元格后将这个大单元格的值返回到原来的左上角位置。

# 合并单元格， 往左上角写入数据即可sheet.merge_cells('B1:G1') # 合并一行中的几个单元格sheet.merge_cells('A1:C3') # 合并一个矩形区域中的单元格

合并后只可以往左上角写入数据，也就是区间中:左边的坐标。

如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃。换句话说若合并前不是在左上角写入数据，合并后单元格中不会有数据。

以下是拆分单元格的代码。拆分后，值回到A1位置。

sheet.unmerge_cells('A1:C3')

这里就拿常用的说，具体的去看openpyxl文档

The above is the detailed content of Python tutorial on reading and writing excel using openpyxl. For more information, please follow other related articles on the PHP Chinese website!