使用python处理excel数据

本文最后更新于：2024年2月1日下午

使用python读取和处理excel数据，在数学建模绘图笔记中有绘图的整理，这里主要记录代码处理的一些要点。

基本操作

补充： xls和xlsx

简单来说：
xls是excel2003及以前版本所生成的文件格式
xlsx是excel2007及以后版本所生成的文件格式

（excel 2007之后版本可以打开上述两种格式，但是excel2013只能打开xls格式）

需要导入的库

1 2	`import os import openpyxl`

打开Excel文档，查看所有sheet表

使用openpyxl模块打开Excel文档，并查看所有sheet表：

1.打开excel文档：

openpyxl.load_workbook()接受文件名，返回一个workbook数据类型的值。

这个workbook对象代表这个Excel文件，类似File对象代表一个打开的文本文件。

import os
import openpyxl

path = r"C:\Users\cyr\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')	
# 返回一个workbook数据类型的值

print(workbook.sheetnames)	
# 打印Excel表中的所有表


# 结果：
# ['Sheet1', 'Sheet2']

2.查看所有sheet表

2.1 通过sheet名称获取表格

sheet = workbook['Sheet1']  # 获取指定sheet表
print(sheet)

# 结果：
# <Worksheet "Sheet1">

2.2 使用 `workbook.active` 获取活动表

(1)活动表：当前正在操作的工作表。
(2) sheet = workbook.active

获取活动表

3.获取表格尺寸

(1)表格的尺寸：excel表格中的数据有几行几列，针对的是不同的sheet而言

import os
import openpyxl

path = r"C:\Users\cyr\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook['Sheet1']  # 获取指定sheet表
print(sheet.dimensions)     # 获取表格的尺寸大小

# 结果：
# A1:B7  (从A1沿对角线到B7的矩形区域)

4. 获取单元格中的数据

方法一：指定坐标的方式

cell1 = sheet['A1']         # 获取A1单元格的数据
cell2 = sheet['B7']         # 获取B7单元格的数据
# cell2 = sheet['B7'].value		# 另一种写法

# cell1.value获取单元格A1中的值
# cell2.value获取单元格B7中的值
print(cell1.value,cell2.value)

方法二：指定行列的方式

cell1 = sheet.cell(row=1,column=1)         # 获取第1行第1列的数据
cell2 = sheet.cell(row=3,column=2)         # 获取第3行第4的数据
# cell1.value获取单元格A1中的值
# cell2.value获取单元格B7中的值
print(cell1.value,cell2.value)

获取单元格的行、列、坐标

.row 获取某个格子的行数；
.columns 获取某个格子的列数；
.coordinate 获取某个格子的坐标；

1
2
3

cell = sheet.cell(row=3, column=2)  # 获取第3行第4列的数据

print(cell.value, cell.row, cell.column, cell.coordinate)

获取区间内的数据

获取单行单列数据的时候，使用一层for循环；

获取多行多列、指定区间的数据时，使用两层for循环

获取指定区间的数据

step 1 : 使用sheet['A1:A5']拿到指定区间
step 2 : 使用两个for循环拿到数据
- 为什么要使用两层循环：
  外层循环迭代了从A1到A5的行（实际上只有一行）；
  而内层循环则迭代了这一行中的单个单元格，这样即可访问并打印每个单元格的值了

import os
import openpyxl

path = r"C:\Users\cyr\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：')
print(sheet)

cell = sheet['A1:A5']  # 获取A1到A5的数据

print(cell)

#打印A1到A5的数据
for i in cell:
    for j in i:
        print(j.value)

获取指定行列的数据

sheet[“A”] — 获取A列的数据
sheet[“A:C”] — 获取A,B,C三列的数据
sheet[5] — 只获取第5行的数据

例如:
(1)获取一列数据:

cell = sheet['2']  # 获取第2行的数据

#打印A1到A5的数据
for i in cell:
    print(i.value)

(2)获取两列数据:

cell = sheet['A:B']  # 获取AB列的数据

#打印AB列数据
for i in cell:
    for j in i:
        print(j.value)

按行、列获取值

iter_rows()：按行读取
iter_cols()：按列读取

# 按行获取值
print('按行获取值')
for i in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=2):
    for j in i:
        print(j.value)

# 按列获取值
print('按列获取值')
for i in sheet.iter_cols(min_row=2, max_row=5, min_col=1, max_col=2):
    for j in i:
        print(j.value)

获取活动表的行列数

rows = sheet.max_row        # 获取行数
column = sheet.max_column   # 获取列数

print(rows)
print(column)

表格内容编辑

1.创建新的excel

import os
import openpyxl

path = r"C:\Users\cyr\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = '1号sheet'  # 指定创建的excel的活动表的名字
workbook.save('1.xlsx')

2.修改单元格、excel另存为

workbook.save('test.xlsx')
保存时如果使用原来的（第7行）名字，就直接保存；如果使用了别的名字，就会另存为一个新文件
修改单元格内容的两种写法：
- sheet['B1'].value = 'age'
- sheet['A1'] = 'name'

import os
import openpyxl

path = r"C:\Users\cyr\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表

sheet['A1'] = 'name'
workbook.save('test.xlsx')

3.添加数据

3.1 添加有效数据

使用append()方法，在原来数据的后面，按行插入数据

data = [
    ['素子',23],
    ['巴特',24],
    ['塔奇克马',2]
]
for row in data:
    sheet.append(row)   # 使用append插入数据
workbook.save('test.xlsx')

示意图

3.2 插入空行空列

insert_rows(idx=数字编号, amount=要插入的行数)，插入的行数是在idx行数的下方插入
insert_cols(idx=数字编号, amount=要插入的列数)，插入的列数是在idx列数的左侧插入

1
2
3

sheet.insert_rows(idx=3, amount=2)
sheet.insert_cols(idx=2, amount=1)
workbook.save('test.xlsx')

示意图

4.删除行、列

delete_rows(idx=数字编号, amount=要删除的行数)
delete_cols(idx=数字编号, amount=要删除的列数)

1
2
3

sheet.delete_rows(idx=10)           # 删除第10行
sheet.delete_cols(idx=1, amount=2)  # 删除第1列，及往右共2列
workbook.save('test.xlsx')

示意图

5.移动指定区间的单元格(move_range)

move_range(“数据区域”,rows=,cols=)
正整数为向下或向右、负整数为向左或向上

1 2	`sheet.move_range('D11:F12',rows=0,cols=-3) # 移动D11到F12构成的矩形格子 workbook.save('test.xlsx')`

示意图

格式查看和修改

字母列号与数字列号之间的转换

核心代码：

from openpyxl.utils import get_column_letter, column_index_from_string

# 根据列的数字返回字母
print(get_column_letter(2))  # B
# 根据字母返回列的数字
print(column_index_from_string('D'))  # 4

字体样式

查看字体样式

font = cell.font
font.name , font.size , font.bold , font.italic , font.color

cell = sheet['A1']
font = cell.font
print('当前单元格的字体样式是')
print(font.name, font.size, font.bold, font.italic, font.color)

'''
当前活动表是：<Worksheet "1号sheet">
当前单元格的字体样式是
等线 11.0 False False <openpyxl.styles.colors.Color object>
Parameters:
rgb=None, indexed=None, auto=None, theme=1, tint=0.0, type='theme'
'''

修改字体样式

openpyxl.styles.Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
- 其中，字体颜色中的color是RGB的16进制表示

import os
import openpyxl
import openpyxl.styles

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print(sheet)

cell = sheet['A1']
cell.font = openpyxl.styles.Font(name="微软雅黑", size=20, bold=True, italic=True, color="FF0000")

workbook.save('test.xlsx')

示意图

使用for循环，修改多行多列的数据

import os
import openpyxl
import openpyxl.styles

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print(sheet)

cell = sheet['A']
for i in cell:
    i.font = openpyxl.styles.Font(name="微软雅黑", size=20, bold=True, italic=True, color="FF0000")

workbook.save('test.xlsx')

设置对齐格式

Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
水平对齐：‘distributed’，‘justify’，‘center’，‘left’， ‘centerContinuous’，’right，‘general’

垂直对齐：‘bottom’，‘distributed’，‘justify’，‘center’，‘top’


cell = sheet['A1']
alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
cell.alignment = alignment

调用for循环实现多行多列操作

cell = sheet['A']
alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
for i in cell:
    i.alignment = alignment
    
workbook.save('test.xlsx')

设置行高列宽

教程看这里

设置所有单元格

（显示的结果是设置所有，有数据的单元格的）

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import os

os.chdir(r'C:\Users\asuka\Desktop')

workbook = load_workbook('1.xlsx')
print(workbook.sheetnames)  # 打印所有的sheet表
ws = workbook[workbook.sheetnames[0]]  # 选中最左侧的sheet表

width = 2.0  # 设置宽度
height = width * (2.2862 / 0.3612)  # 设置高度

print("row:", ws.max_row, "column:", ws.max_column)  # 打印行数，列数
for i in range(1, ws.max_row + 1):
    ws.row_dimensions[i].height = height
for i in range(1, ws.max_column + 1):
    ws.column_dimensions[get_column_letter(i)].width = width

workbook.save('test.xlsx')

合并、拆分单元格

如果要合并的格子中有数据，即便python没有报错，Excel打开的时候也会报错。

方法一：merge_cells(待合并的格子编号)
方法二：merge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)

import os
import openpyxl
import openpyxl.styles

path = r"C:\Users\asuka\Desktop"
os.chdir(path)  # 修改工作路径

workbook = openpyxl.load_workbook('test.xlsx')  # 返回一个workbook数据类型的值
sheet = workbook.active  # 获取活动表
print('当前活动表是：' + str(sheet))

# 方法1：
sheet.merge_cells('A12:B13')
# 方法2：
sheet.merge_cells(start_row=12, start_column=3, end_row=13, end_column=4)

# 加一个居中对齐
cell = sheet['A12']
alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
cell.alignment = alignment

cell = sheet['C12']
alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
cell.alignment = alignment

workbook.save('test.xlsx')

拆分单元格的方法同上

方法一：unmerge_cells(待合并的格子编号)
方法二：unmerge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)

xlsxwriter模块

xlsxwriter模块一般是和xlrd模块搭配使用的，
- xlsxwriter：负责写入数据，
- xlrd：负责读取数据。
worksheet.write_row('A1', headings) 添加表头

worksheet.write(行,列,数据) 写入数据

1
2
3

data = ["苹果", 500, 8.9]
for i in range(len(headings)):
    worksheet.write(1, i, data[i])

完整的代码：

import xlsxwriter
import os

path = r"C:\Users\cyr\Desktop"
os.chdir(path)

# 创建一个名为【demo.xlsx】工作簿；
workbook = xlsxwriter.Workbook("demo.xlsx")

# 创建一个名为【2018年销售量】工作表；
worksheet = workbook.add_worksheet("2018年销售量")

# 使用write_row方法，为【2018年销售量】工作表，添加一个表头；
headings = ['产品', '销量', "单价"]
worksheet.write_row('A1', headings)
# 使用write方法，在【2018年销售量】工作表中插入一条数据；
# write语法格式：worksheet.write(行,列,数据)

data = ["苹果", 500, 8.9]
for i in range(len(headings)):
    worksheet.write(1, i, data[i])

workbook.close()

示意图

建模技能

#数据分析和处理

使用python处理excel数据

http://zoechen04616.github.io/2024/01/31/使用python处理excel数据/

作者

Yunru Chen

发布于

2024年1月31日

许可协议

美赛论文学习笔记上一篇

24.1.29会议记录下一篇

使用python处理excel数据

基本操作

补充： xls和xlsx

需要导入的库

打开Excel文档，查看所有sheet表

1.打开excel文档：

2.查看所有sheet表

2.1 通过sheet名称获取表格

2.2 使用 workbook.active 获取活动表

3.获取表格尺寸

4. 获取单元格中的数据

方法一：指定坐标的方式

方法二：指定行列的方式

获取单元格的行、列、坐标

获取区间内的数据

获取指定区间的数据

获取指定行列的数据

按行、列获取值

获取活动表的行列数

表格内容编辑

1.创建新的excel

2.修改单元格、excel另存为

3.添加数据

3.1 添加有效数据

3.2 插入空行空列

4.删除行、列

5.移动指定区间的单元格(move_range)

格式查看和修改

字母列号与数字列号之间的转换

字体样式

查看字体样式

修改字体样式

使用for循环，修改多行多列的数据

设置对齐格式

调用for循环实现多行多列操作

设置行高列宽

设置所有单元格

合并、拆分单元格

拆分单元格的方法同上

xlsxwriter模块

2.2 使用 `workbook.active` 获取活动表