有勇气的牛排博客
☰

python 操作 excel

有勇气的牛排 291 Python 2023-05-18 20:49:32

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()

3.1 写入数据

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

  • 写入第一行、第二列数据：
value = 'reslut':sh.cell(row=1,column=2,value='result')
  • 获取做大行总数、最大列总数：sh.max_row、sh.max_column
  • del 删除表单的用法：del wb[‘sheet_name’]
  • remove 删除表单的用法：
sh = wb['sheet_name']
wb.remove(sh)

4 excel数据

image.png

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')

5.2 读取数据

import openpyxl

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

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

5.2.1 读取指定单元格数据

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

输出：姓名

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)

输出：
[
(<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

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)

输出：
[
{'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}
]

5.2.3 关闭工作薄

# 关闭工作薄
wb.close()

5.3 写数据

5.3.1 写入一行

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

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

6 样式

from openpyxl.styles import Font, colors, Alignment

6.1 改变 sheet 标签按钮颜色

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

sh.sheet_properties.tabColor = "FFAA33"

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

6.3 对齐方式

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

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

6.4 设置行高和列宽

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

6.5 合并和拆分单元格

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

留言

专栏
文章
加入群聊
程序员兼职接单交流
编程交流