python 操作 excel_python 修改excel

1. 安装openpyxl

python 中与excel操作相关的模块：

• xlrd库：从excel中读取数据，支持xls、xlsx
• xlwt库：对excel进行修改操作，不支持xlsx格式的修改
• xlutils库：在xlw和xlrd中，对一个已存在的文件进行修改
• openpyxl库：主要针对xlsx格式的excel进行读取和编辑

2. Excel 中的三大对象

• WorkBook：工作对象
• Sheet：表单对象
• Cell：表格对象

3. openpyxl 对 Excel的操作

• 创建一个工作簿: wb = openpyxl.Workbook()
• 新增一个sheet表单：wb.create_sheet(‘sheet2’)
• 保存 demo.xlsx文件：wb.save(‘demo.xlsx’)
• 打开工作簿：wb = openpyxl.load_workbook(‘demo.xlsx’)
• 选取表单：sh = wb[‘sheet2’]
• 读取第一行、第一列的数据：ce = sh.cell(‘row=1, column=1’)
• 按行读取数据：row_data = list(sh.rows)
• 按列读取数据：columns_data = list(sh.clumns)
• 关闭工作簿：wb.close()

写入数据

写入数据之前，该文件一定要处于关闭状态

• 写入第一行、第二列数据：

value = 'reslut':sh.cell(row=1,column=2,value='result')
1

• 获取做大行总数、最大列总数：sh.max_row、sh.max_column
• del 删除表单的用法：del wb[‘sheet_name’]
• remove 删除表单的用法：

sh = wb['sheet_name']
wb.remove(sh)
1
2

4. excel数据

5. 操作案例

5.1 创建excel

import openpyxl

# 创建一个工作簿 实例化
wb = openpyxl.Workbook()

# 创建一个test_case的sheet表单
wb.create_sheet('StudentsInfo')

# 删除默认表
sh = wb['Sheet']
wb.remove(sh)

# 保存为一个xlsx格式的文件
wb.save('批量导入用户模板.xlsx')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

5.2 读取数据

import openpyxl
1

# 第一步：打开工作簿
wb = openpyxl.load_workbook('批量导入用户模板.xlsx')

# 第二步：选取表单
sh = wb['StudentsInfo']
1
2
3
4
5

5.2.1 读取指定单元格数据

# 第三步：读取指定单元格
# 参数 row:行  column：列
ce = sh.cell(row = 1,column = 2)   # 读取第一行，第一列的数据
print(ce.value)
1
2
3
4

输出：姓名
1

5.2.2按行读取数据

# 按行读取数据 list(sh.rows)
print(list(sh.rows)[1:])     # 按行读取数据，去掉第一行的表头信息数据

for cases in list(sh.rows)[1:]:
    stu_id =  cases[0].value
    stu_name = cases[1].value
    stu_sex = cases[2].value
    stu_age = cases[3].value
    print(stu_id, stu_name, stu_sex, stu_age)
1
2
3
4
5
6
7
8
9

输出：
[
(<Cell 'StudentsInfo'.A2>, <Cell 'StudentsInfo'.B2>, <Cell 'StudentsInfo'.C2>, <Cell 'StudentsInfo'.D2>), 
(<Cell 'StudentsInfo'.A3>, <Cell 'StudentsInfo'.B3>, <Cell 'StudentsInfo'.C3>, <Cell 'StudentsInfo'.D3>), 
(<Cell 'StudentsInfo'.A4>, <Cell 'StudentsInfo'.B4>, <Cell 'StudentsInfo'.C4>, <Cell 'StudentsInfo'.D4>), 
(<Cell 'StudentsInfo'.A5>, <Cell 'StudentsInfo'.B5>, <Cell 'StudentsInfo'.C5>, <Cell 'StudentsInfo'.D5>)
]
1 大佬 男 18
2 高手 女 18
3 大神 男 19
None 6 None None
1
2
3
4
5
6
7
8
9
10
11

5.2.2按行读取数据 结果转为json

all_row = []
for cases in list(sh.rows)[1:]:
    row = []
    stu_id =  cases[0].value
    stu_name = cases[1].value
    stu_sex = cases[2].value
    stu_age = cases[3].value
    # print(stu_id, stu_name, stu_sex, stu_age)
    data = {
        "stu_id": stu_id,
        "stu_name": stu_name,
        "stu_sex": stu_sex,
        "stu_age": stu_age
    }
    all_row.append(data)
    
print(all_row)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

输出：
[
{'stu_id': 1, 'stu_name': '大佬', 'stu_sex': '男', 'stu_age': 18}, 
{'stu_id': 2, 'stu_name': '高手', 'stu_sex': '女', 'stu_age': 18}, 
{'stu_id': 3, 'stu_name': '大神', 'stu_sex': '男', 'stu_age': 19}, 
{'stu_id': None, 'stu_name': 6, 'stu_sex': None, 'stu_age': None}
]
1
2
3
4
5
6
7

5.3 关闭工作薄

# 关闭工作薄
wb.close()
1
2

5.3 写数据

5.3.1 写入一行

# 第二步：选取表单
sh = wb['StudentsInfo']

# 第一行输入
sh.append(['1', '2', '3', '6'])
1
2
3
4
5

6. 样式

from openpyxl.styles import Font, colors, Alignment
1

6.1 改变 sheet 标签按钮颜色

二进制颜色代码大全（含图）

sh.sheet_properties.tabColor = "FFAA33"
1

6.2 设置单元格风格

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

sh = wb['StudentsInfo']

bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.BLUE, bold=True)
sh['A1'].font = bold_itatic_24_font
1
2
3
4

6.3 对齐方式

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

sh['B1'].alignment = Alignment(horizontal='center', vertical='center')
1

6.4 设置行高和列宽

# 第2行行高
sh.row_dimensions[2].height = 40
# C列列宽
sh.column_dimensions['C'].width = 30
1
2
3
4

6.5 合并和拆分单元格

sh.merge_cells('B1:G1') # 合并一行中的几个单元格
sh.merge_cells('A3:D6') # 合并一个矩形区域中的单元格
1
2

参考：
https://www.cnblogs.com/wanglle/p/11455758.html
http://www.52codes.net/develop/shell/58896.html