Excel操作-openpyxl

基础

创建时间:2022-10-22 17:21

阅读:

1.openyxl与xlwt区别
 2.openyxl写入数据
 3.openyxl读取数据
 4.openyxl追加/修改数据
 5.添加多个工作表
 6.设置颜色
 7.设置对齐方式
 8.设置自动换行
 9.设置列宽

1.openyxl与xlwt区别

两者区别:
    1.openpyxl单个sheet可以存储101万行，xlwt单个表格只能存储65000多行
    2.openpyxl的文件名后缀为xlsx，xlwt的文件名后缀为xls
    3.openpyxl从1行1列开始；xlwt写入数据时从0行0列开始
模块安装: pip3 install openyxl

其它：
excel设置自适应宽度
https://blog.csdn.net/Girione/article/details/130797562

2.openyxl写入数据

# *_*coding:utf-8
import openpyxl

wb = openpyxl.Workbook()

# 新建sheet
# 方法一 (推荐使用这个方法，excel中只会有一个sheet表)
sheet1 = wb.active  # 激活一个sheet
sheet1.title = "sheet1"  # sheet的名字

# 方法二 (要新建多个sheet就用这个方法)
# sheet1 = wb.create_sheet("sheet1")  # 会新建多一个sheet页出来

# 写入值的方法
# 方法一：通过appen([列表]) 会直接添加一行数据
sheet1.append(["列1", "列2", "列3"])
sheet1.append(["数据1", "", "数据3", "数据4"])

# 方法二：通过excel的坐标去写入值
sheet1["B5"] = "单元格1"
sheet1["C5"] = "单元格2"
sheet1["D5"] = "单元格3"

# 方法三：通过cell(row,col,value)去添加值，行和列都是从1开始算起
sheet1.cell(7, 1, "单元格值1")
sheet1.cell(7, 2, "单元格值2")
sheet1.cell(7, 3, "单元格值3")
# 保存
wb.save(file)

3.openyxl读取数据

# 读取刚才写入的Excel文件

# *_*coding:utf-8
import openpyxl

file = "./file/demo.xlsx"

wb = openpyxl.load_workbook(file)

# 获取sheet对象
# 方法一：通过sheet的位置
sheet_names = wb.get_sheet_names()
ws = wb.get_sheet_by_name(sheet_names[0]) # 获取第一个sheet表

# 方法二：通过指定的sheet名称
# sheet1 = wb["sheet1"]

# 获取所有的sheet值
# for sheet in wb:
#     print(sheet)  # 工作表对象
#     print(sheet.title)  # 工作表名称

# 获取有多少行、列
"""
工作表最大行：sheet1.max_row，获取存在数据的最大一行索引
工作表最小行：sheet1.min_row，获取存在数据的最小一行索引
工作表最大列：sheet1.max_column，获取存在数据的最大一列索引
工作表最小列：sheet1.min_column，获取存在数据的最小一列索引
"""
print("有多少行数据:%s" % sheet1.max_row)
print("有多少列数据:%s" % sheet1.max_column)
print()

# 获取值
# 方法一：通过excel的坐标去获取值
value1 = sheet1["B5"].value
value2 = sheet1["C5"].value
value3 = sheet1["D5"].value
print(value1, value2, value3)
print()

# 方法： 通过坐标去获取值
for i in range(1, 4):
    cell = sheet1.cell(7, i).value
    print(cell)

4.openyxl追加/修改数据

# 修改刚才写入的Excel文件

# *_*coding:utf-8
import openpyxl

file = "./file/demo.xlsx"

wb = openpyxl.load_workbook(file)

# 获取sheet对象
sheet1 = wb["sheet1"]

sheet1.cell(7, 1, "新单元格值1")
sheet1.cell(7, 2, "新单元格值2")
sheet1.cell(7, 2, "新单元格值3")

for i in range(1, 6):
    sheet1.cell(8, i, "新值%s" % i)

wb.save(file)

5.添加多个工作表(sheet)

# 使用"wb.create_sheet(title="xx")"添加新的sheet
import openpyxl

wb = openpyxl.Workbook()

# 写第一个sheet
sheet1 = wb.active  
sheet1.title = "一班"  # sheet的名字
sheet1.append(["姓名", "年龄"])

# 写第二个sheet
sheet2 = wb.create_sheet(title="二班")
sheet2.append(["姓名", "年龄"])

# 写第三个sheet
sheet3 = wb.create_sheet(title="三班")
sheet3.append(["姓名", "年龄"])

# 添加值
sheet1.cell(2, 1, "小明")
sheet1.cell(2, 2, "18")
sheet1.cell(3, 1, "小李")
sheet1.cell(3, 2, "19")

sheet2.cell(2, 1, "小明")
sheet2.cell(2, 2, "18")
sheet2.cell(3, 1, "小李")
sheet2.cell(3, 2, "19")

sheet3.cell(2, 1, "小明")
sheet3.cell(2, 2, "18")
sheet3.cell(3, 1, "小李")
sheet3.cell(3, 2, "19")

# 保存
wb.save("./demo.xlsx")

6.设置颜色

# *_*coding:utf-8
import openpyxl
from openpyxl.styles import Font

def set_color(row, column):
    # 绿色，颜色需要使用16进制，"#"不用写
    # 16进制参考表 https://tool.oschina.net/commons?type=3
    font = Font(color='67C23A')

    # 要给哪个单元格添加颜色
    cell = sheet1.cell(row=row, column=column)

    # 设置单元格的字体样式
    cell.font = font

wb = openpyxl.Workbook()

# 第一个sheet
sheet1 = wb.active
sheet1.title = "一班"
sheet1.append(["姓名", "年龄"])

# 添加值
sheet1.cell(2, 1, "小明")
sheet1.cell(2, 2, "18")
sheet1.cell(3, 1, "小李")
sheet1.cell(3, 2, "19")

# 给多个单元格设置颜色
set_color(2, 1)
set_color(3, 1)

# 保存
wb.save("./demo.xlsx")

7.设置对齐方式

# *_*coding:utf-8
import openpyxl
from openpyxl.styles import Alignment


def set_color(row, column):
    # 设置对齐方式，参数有：left、center、right
    alignment = Alignment(horizontal='center')

    # 要设置的单元格
    cell = sheet1.cell(row=row, column=column)
    cell.alignment = alignment


wb = openpyxl.Workbook()

# 第一个sheet
sheet1 = wb.active
sheet1.title = "一班"
sheet1.append(["姓名", "年龄"])

# 添加值
sheet1.cell(2, 1, "小明")
sheet1.cell(2, 2, "18")
sheet1.cell(3, 1, "小李")
sheet1.cell(3, 2, "19")

# 给多个单元格进行设置
set_color(2, 1)
set_color(3, 1)

# 保存
wb.save("./demo.xlsx")

8.设置自动换行

# 单元格里面有换行符才会自动换行

# *_*coding:utf-8
import openpyxl
from openpyxl.styles import Alignment


def set_color(row, column):
    alignment = Alignment(wrapText=True)
    # 要设置的单元格
    cell = sheet1.cell(row=row, column=column)
    cell.alignment = alignment


wb = openpyxl.Workbook()

# 第一个sheet
sheet1 = wb.active
sheet1.title = "一班"
sheet1.append(["姓名", "年龄"])

# 添加值
sheet1.cell(2, 1, "小明")
sheet1.cell(2, 2, "18\n喜欢打篮球\n有车有房")
sheet1.cell(3, 1, "小李")
sheet1.cell(3, 2, "19\n喜欢游泳\n有房")
sheet1.cell(4, 1, "小红")
sheet1.cell(4, 2, "哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈")

# 给多个单元格进行设置
set_color(2, 2)
set_color(3, 2)

# 保存
wb.save("./demo.xlsx")

9.设置列宽

# *_*coding:utf-8
import openpyxl
import re

wb = openpyxl.Workbook()

# 第一个sheet
sheet1 = wb.active
sheet1.title = "一班"
sheet1.append(["姓名", "年龄", "其它"])

# 添加值
sheet1.cell(2, 1, "小明")
sheet1.cell(2, 2, "18\n喜欢打篮球\n有车有房")
sheet1.cell(2, 3, "哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈")
sheet1.cell(3, 1, "小李")
sheet1.cell(3, 2, "19\n喜欢游泳\n有房")
sheet1.cell(4, 1, "小红")
sheet1.cell(4, 2, "哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈")

# 设置列宽
for col in sheet1.columns:
    # 先计算出每一列最大单元格的长度
    max_length = max([
        len(str(cell.value)) + 0.7 * len(re.findall(r'([\u4e00-\u9fa5])', str(cell.value)))
        for cell in col
    ])

    # 设置列宽
    sheet1.column_dimensions[col[0].column_letter].width = (max_length + 2) * 1.2

# 单独设置列宽，给哪一列设置，就写上哪一列对应的字母
sheet1.column_dimensions["C"].width = 10

# 保存
wb.save("./demo.xlsx")

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。