首页 > 编程语言 >Python基础-模块、Python操作excel

Python基础-模块、Python操作excel

时间:2024-04-24 20:58:05浏览次数:31  
标签:obj Python excel cell 模块 import workbook sheet row

模块和面向对象-day9

今日概要

  • 模块

    • 自定义模块

    • 内置模块

    • 第三方模块

      [!Caution]

      这个地方讲的会比较多,而且有的东西网上搜不到!!!

      自动化办公相关模块 -- Excel和Word
      
  • 面向对象

1. 面向对象中的概念

对象,什么是对象?

比如说我桌子上好多东西,有铅笔、钢笔、圆珠笔,玻璃杯、保温杯、塑料杯,电脑,华为手机、荣耀手机、小米手机、oppo手机、vivo手机等等。我要把这些东西带到公司 -- 我会找个大袋子把这些东西全都打包在一起,形成一个东西再带到公司。此外,我也可以分类,把手机放在一起,杯子放在一起,打包成不同的小包。

  • 内部包含了很多值和功能的"包裹"
  • 有一个归类的概念

Excel为例:

  • 单元格 -- 对象(值、边框、颜色);写入时,对象(边框、颜色)

  • sheet,对象(名字/很多单元格)

  • workbook,对象

2. 模块

2.1 自定义模块

···已讲

2.2 内置模块

...已讲

2.3 第三方模块

2.3.1 Excel操作

注意:openpyxl不支持以前的Excel格式(xls)

pip install openpyxl

2.3.1.1 读

Clip_2024-04-14_19-05-12

import os
from openpyxl import load_workbook

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet名字
sheet_list= workbook_obj.sheetnames
print(sheet_list)

运行结果:['RegData', 'StatProForecast1', 'Data']

import os
from openpyxl import load_workbook

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 获取所有Worksheet对象
v2 = workbook_obj.worksheets
print(v2)

# 3. 获取某一个对象
v2[0]
v2[1]

# 4. 直接获取sheet对象
sheet_obj= workbook_obj["RegData"]

运行结果:[<Worksheet "RegData">, <Worksheet "StatProForecast1">, <Worksheet "Data">]

import os
from openpyxl import load_workbook

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[0]

# 4. 读取sheet中的单元格对象
cell_obj = sheet_obj.cell(row=1, column=1)

# 5. 读取cell对象中的文本
print(cell_obj.value)

Clip_2024-04-14_19-05-21

import os
from openpyxl import load_workbook

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[0]

# 4. 读取sheet中的单元格对象 -- 方式1
cell_obj = sheet_obj.cell(row=1, column=1)

# 4. 读取sheet中的单元格对象 -- 方式2
cell_obj_1 = sheet_obj["A1"]

# 5. 读取cell对象中的文本
print(cell_obj.value)
print(cell_obj_1.value)

Clip_2024-04-14_19-09-05

读取某一行:

[!Caution]

python操作excel的过程中,读取一行是从1开始的 而不是从0开始的

import os
from openpyxl import load_workbook

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[0]
row_tuple = sheet_obj[1]
print(row_tuple)

for cell_obj in row_tuple:
    print(cell_obj.value)

Clip_2024-04-14_19-13-07

获取所有行数据:

import os
from openpyxl import load_workbook

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[0]

# 4. 读取sheet中的单元格对象 -- 方式1
cell_obj = sheet_obj.cell(row=1, column=1)

# 4. 读取sheet中的单元格对象 -- 方式2
cell_obj_1 = sheet_obj["A1"]

# 7. 读取所有行
for row in sheet_obj.rows:
    row_text_list = []
    for cell_obj in row:
        row_text_list.append(cell_obj.value)
    print(row_text_list)

Clip_2024-04-14_19-20-45

读取某一列:

import os
from openpyxl import load_workbook

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[0]

# 4. 读取sheet中的单元格对象 -- 方式1
cell_obj = sheet_obj.cell(row=1, column=1)

# 4. 读取sheet中的单元格对象 -- 方式2
cell_obj_1 = sheet_obj["A1"]
# 8. 读取所有列
for row in sheet_obj.rows:
    cell_obj_0 = row[0]
    cell_obj_1 = row[1]
    print(cell_obj_0.value, cell_obj_1.value)

Clip_2024-04-14_19-25-14

如果有合并单元格的情况:

[!Caution]

注意:合并单元格后,只有合并之前的第一个单元格有数据(Cell对象),被合并的单元格为空(MergeCell对象)!!!

import os
from openpyxl import load_workbook

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[3]

# 10. 获取合并的单元格
cell_1_obj = sheet_obj.cell(1, 1)
cell_2_obj = sheet_obj.cell(1, 2)

print(cell_1_obj, cell_2_obj)
print(cell_1_obj.value, cell_2_obj.value)

Clip_2024-04-14_19-37-45

Clip_2024-04-14_19-37-59

判断是否是被合并的单元格

import os
from openpyxl import load_workbook
from openpyxl.cell.cell import Cell, MergedCell

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[3]

# # 10. 获取合并的单元格
# cell_1_obj = sheet_obj.cell(1, 1)
# cell_2_obj = sheet_obj.cell(1, 2)
#
# print(cell_1_obj, cell_2_obj)
# print(cell_1_obj.value, cell_2_obj.value)

# # 原始内容:
# for row in sheet_obj.rows:
#     text_list = []
#     for cell in row:
#         text_list.append(cell.value)
#     print(text_list)

# 如果是被合并的单元格 让其默认值等于 -
for row in sheet_obj.rows:
    text_list = []
    for cell in row:
        # 判断 这个单元格是Cell对象还是MergeCell对象
        if type(cell) is MergedCell:
            text_list.append("-")
        elif type(cell) is Cell:
            text_list.append(cell.value)
        else:
            text_list.append("Dont Know")
    print(text_list)

Clip_2024-04-14_19-51-13

[!Note]

扩展:让合并的单元格也能读到和合并之前第一个单元格一样的信息

import os
from openpyxl import load_workbook
from openpyxl.cell.cell import Cell, MergedCell

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[3]

# 获取当前sheet中所有的合并的单元格
def get_merged_cell(coordinate, sheet_obj):
 """

 Args:
     coordinate (): 坐标 需要这种形式: "A5"
     sheet_obj (): 某一个sheet对象

 Returns:

 """
 for item in sheet_obj.merged_cells:
     # print(item, type(item))
     if coordinate in item:
         return item.start_cell.value


# 如果是被合并的单元格 让其默认值等于第一个单元格的值
for row in sheet_obj.rows:
 text_list = []
 for cell in row:
     # 判断 这个单元格是Cell对象还是MergeCell对象
     if type(cell) is MergedCell:
         # 获取单元格的内容:
         merged_text = get_merged_cell(cell.coordinate, sheet_obj)
         text_list.append(merged_text)
     elif type(cell) is Cell:
         text_list.append(cell.value)
     else:
         text_list.append("Dont Know")
 print(text_list)

关于坐标:

import os
from openpyxl import load_workbook
from openpyxl.cell.cell import Cell, MergedCell

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[3]

# 4. 获取cell对象
cell_object = sheet_obj.cell(1, 2)

# 5. 获取坐标位置
cell_coordinate = cell_object.coordinate

从第n行开始读取:

import os
from openpyxl import load_workbook
from openpyxl.cell.cell import Cell, MergedCell

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[3]

# 从第n行开始读 读到第m行
for row in sheet_obj.iter_rows(min_row=3, max_row=4):
    print(row[0].value)

当前sheet总共有几行

import os
from openpyxl import load_workbook
from openpyxl.cell.cell import Cell, MergedCell

file_name = os.path.join("files", "Amtrak.xlsx")
# 1. 获取对象
workbook_obj = load_workbook(file_name)

# 2. 读取workbook里面的所有sheet对象
sheet_obj_list = workbook_obj.worksheets

# 3. 获取某一个sheet对象
sheet_obj = sheet_obj_list[3]

# 总共有几行 总共有几列
print(sheet_obj.max_row)
print(sheet_obj.max_column)

2.3.1.2 Excel案例1

  1. 根据excel文档,读取某几列,不要表头

    import os
    from openpyxl import load_workbook
    
    file_path = os.path.join("files", "Amtrak.xlsx")
    
    workbook_obj = load_workbook(file_path)
    
    sheet_obj = workbook_obj.worksheets[1]
    
    for row_obj in sheet_obj.iter_rows(min_row=2):
        item = (row_obj[4].value, row_obj[5].value, row_obj[6].value, row_obj[10].value)
        print(item)
    

    Clip_2024-04-14_20-57-38

  2. 需求:获取时间(整形)

Clip_2024-04-14_21-01-49

import os
import re
from openpyxl import load_workbook

file_path = os.path.join("files", "Amtrak.xlsx")

workbook_obj = load_workbook(file_path)

sheet_obj = workbook_obj.worksheets[0]

for row_obj in sheet_obj.iter_rows(min_row=2):
    time_str = row_obj[6].value
    if not time_str:
        continue
    time_num = int(re.findall(r"(\d+)分钟", time_str)[0])
    if time_num > 10:
        print(time_num)
  1. 找到bankloan这一个工作表,并且将收入综合计算出来
    Clip_2024-04-14_21-18-01

    from openpyxl import load_workbook
    
    
    wb = load_workbook('./files/bankloan.xlsx')
    sheet_obj = None
    sum_ = 0
    sheet_names = wb.sheetnames
    for i in range(0, len(sheet_names)):
        if sheet_names[i] == "bankloan":
            sheet_obj = wb.worksheets[i]
    
    for row_obj in sheet_obj.iter_rows(min_row=2):
        cell_income_obj = row_obj[4]
        if not cell_income_obj.value:
            continue
        sum_ += cell_income_obj.value
        print(cell_income_obj.value)
    print("sum:", sum_)
    

    Clip_2024-04-14_21-39-34

  2. 获取所有sheet里面的收入数量总和

    import os
    import re
    from openpyxl import load_workbook
    
    file_path = os.path.join("files", "Amtrak.xlsx")
    
    workbook_obj = load_workbook(file_path)
    
    sheet_obj = workbook_obj.worksheets[0]
    
    for row_obj in sheet_obj.iter_rows(min_row=2):
        time_str = row_obj[6].value
        if not time_str:
            continue
        time_num = int(re.findall(r"(\d+)分钟", time_str)[0])
        if time_num > 10:
            print(time_num)
    
    # 老师的
    import os
    from openpyxl import load_workbook
    
    file_path = os.path.join("files", "Amtrak.xlsx")
    
    workbook_obj = load_workbook(file_path)
    
    result = {}
    
    for sheet_obj in workbook_obj.worksheets:
        sheet_name = sheet_obj.title
    
        for row_list in sheet_obj.iter_rows(min_row=2):
            count = row_list[1].value
            if not count:
                continue
            total_count += count
        result[sheet_name] = total_count
    print(result)
    
  3. 牛逼的来啦!找到2020年的excel文件 计算这些excel文件中bankloan这一个sheet的所有收入的总和 -- 只要2020年的
    Clip_2024-04-14_22-11-33

    # 输出形式
    
    result = {
        "2020年1月份营收": {
            "sheet1": 10025,
            "sheet2": 15864,
            "sheet3": 15864,
            "sheet4": 15864,
        },
        "2020年2月份营收": {
            "sheet1": 10025,
            "sheet2": 15864,
            "sheet3": 15864,
            "sheet4": 15864,
        },
        ...
    }
    
    import os
    from pprint import pprint
    from openpyxl import load_workbook
    
    # 列出所有的文件名
    
    result = {}
    
    
    file_folder = os.path.join("files", "营收报表")
    file_list = os.listdir(file_folder)
    
    # 遍历所有文件
    for file_name_str in file_list:
        if not file_name_str.startwith("2020"):
            continue
        # 去掉后缀名,形成字典的key
        file_delete_postfix_name = file_name_str.split(".")[0]
    
        # 初始化result字典
        result[file_delete_postfix_name] = dict()
    
        # 初始化工作簿对象
        workbook_obj = load_workbook(os.path.join(file_folder, file_name_str))
    
        # 遍历一个文件里面的所有sheet
        for sheet_name in workbook_obj.sheetnames:
            # workbook_obj.sheetnames : 是个列表,里面包含了所有的sheet名称,每个元素都是字符串
            sum_ = 0  # 用以计算总收入
    
            # 获取sheet对象
            sheet_obj = workbook_obj[sheet_name]
            # 遍历所有行
            for row_obj in sheet_obj.iter_rows(min_row=2):
                num_income = row_obj[4].value  # 获取每一行的数据
                if not num_income:
                    continue
                sum_ += int(num_income)  # 不空的话求和
            result[file_delete_postfix_name][sheet_name] = sum_  # 保存数据
    pprint(result)
    

    Clip_2024-04-14_23-08-45

    [!Important]

    补充:获取sheet有两种方式:

    # 第一种
    for sheet_name in workbook_obj.sheetnames:
     print(sheet_name, workbook_obj[sheet_name])
    #第二种
    for worksheet_obj in workbook_obj.worksheets:
     print(worksheet_obj.title, worksheet_obj)
    

2.3.1.2 写

  • 修改

    · 读取,将所有的内容都读取到内存
    · 都是在内存中进行修改的
    · 保存
    
    import os
    from openpyxl import load_workbook
    
    FILE_FOLDER = os.path.join("files", "营收报表", "2020年1月份营收.xlsx")
    
    # 获取workbook对象
    workbook_obj = load_workbook(FILE_FOLDER)
    
    sheet_obj = workbook_obj.worksheets[0]
    
    cell_obj = sheet_obj.cell(row=1, column=1)
    print(cell_obj.value)
    
    cell_obj.value = "age"
    print(cell_obj.value)
    
    # 将内存中的数据写入到磁盘
    workbook_obj.save(os.path.join("files",  "2020年1月份营收-修改.xlsx"))
    
  • 新建

    · 读取空内容
    · 在内存中修改操作
    · 保存
    
    # 新建excel文件
    
    import os
    from openpyxl.workbook import Workbook
    
    FILE_FOLDER = os.path.join("files", "营收报表", "2020年1月份营收.xlsx")
    workbook_obj = Workbook()
    sheet_obj = workbook_obj.worksheets[0]
    cell_obj = sheet_obj.cell(row=1, column=1)
    
    cell_obj.value = "age"
    workbook_obj.save("./files/create.xlsx")
    
    import os
    from openpyxl.workbook import Workbook
    
    FILE_FOLDER = os.path.join("files", "营收报表", "2020年1月份营收.xlsx")
    workbook_obj = Workbook()
    
    workbook_obj.save("./files/create_1.xlsx")
    
    import os
    from openpyxl.workbook import Workbook
    
    FILE_FOLDER = os.path.join("files", "营收报表", "2020年1月份营收.xlsx")
    
    # 创建一个workbook对象 创建好以后会自动生成一个默认的worksheet--sheet1
    workbook_obj = Workbook()
    sheet_0 = workbook_obj.worksheets[0]
    sheet_0.title = "数据集"
    sheet_0.cell(row=1, column=1, value="A")
    sheet_0.cell(row=1, column=2, value="B")
    sheet_0.cell(row=1, column=3, value="C")
    sheet_0.cell(row=1, column=4, value="D")
    
    # 创建sheet
    sheet_1 = workbook_obj.create_sheet(title="上海", index=1)
    sheet_1.cell(row=1, column=1, value="A")
    sheet_1.cell(row=1, column=2).value = "B"
    sheet_1.cell(row=1, column=3).value = "C"
    sheet_1.cell(row=1, column=4).value = "D"
    
    sheet_2 = workbook_obj.create_sheet(title="北京", index=2)
    sheet_3 = workbook_obj.create_sheet(title="广州", index=3)
    sheet_4 = workbook_obj.create_sheet(title="深圳", index=4)
    
    workbook_obj.save("./files/create_1.xlsx")
    
  • 拷贝sheet

    import os
    from openpyxl.workbook import Workbook
    
    FILE_FOLDER = os.path.join("files", "营收报表", "2020年1月份营收.xlsx")
    
    # 创建一个workbook对象 创建好以后会自动生成一个默认的worksheet
    workbook_obj = Workbook()
    sheet_0 = workbook_obj.worksheets[0]
    
    workbook_obj
    
    import os
    from openpyxl.workbook import Workbook
    
    FILE_FOLDER = os.path.join("files", "营收报表", "2020年1月份营收.xlsx")
    
    # 创建一个workbook对象 创建好以后会自动生成一个默认的worksheet
    workbook_obj = Workbook()
    sheet_0 = workbook_obj.worksheets[0]
    
    new_sheet = workbook_obj.copy_worksheet(sheet_0)
    
    new_sheet.title = "备份"
    
    workbook_obj.save("./files/create_new.xlsx")
    
  • 删除sheet

    import os
    from openpyxl import load_workbook
    
    file_name = os.path.join("files", "create_new.xlsx")
    # 1. 获取对象
    workbook_obj = load_workbook(file_name)
    
    del workbook_obj["备份"]
    
    workbook_obj.save("./files/create_new.xlsx")
    
  • 单元格中写数据:

    from openpyxl.workbook import Workbook
    
    workbook_obj = Workbook()
    
    sheet_0 = workbook_obj.worksheets[0]
    
    # 操作cell方式一
    cell_obj = sheet_0.cell(1, 1)
    cell_obj.value = "学生ID"
    
    # 操作cell方式二
    sheet_0["B1"] = "姓名"
    
    # 操作cell方式三
    cell_obj_3 = sheet_0["C1"]
    cell_obj_3.value = "年龄"
    
    workbook_obj.save("./files/op_cell.xlsx")
    
  • 设置对齐方式

    from openpyxl.styles import Alignment
    from openpyxl.workbook import Workbook
    
    workbook_obj = Workbook()
    
    sheet_0 = workbook_obj.worksheets[0]
    
    # 居中 换行
    cell_obj = sheet_0.cell(1, 1)
    cell_obj.value = "学生ID学生ID学生ID学生ID学生ID"
    cell_obj.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)  # 设置居中、wrap_text自动换行
    
    workbook_obj.save("./files/op_cell.xlsx")
    
    # 多个单元格同时居中
    from openpyxl.styles import Alignment
    from openpyxl.workbook import Workbook
    
    workbook_obj = Workbook()
    
    sheet_0 = workbook_obj.worksheets[0]
    
    name_list = ["张三", "里斯", "王五", "二麻子"]
    
    # 居中 换行
    sheet_0.cell(row=1, column=1).value = "姓名"
    sheet_0.cell(row=1, column=1).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    for row, text in enumerate(name_list, 2):
        cell_obj = sheet_0.cell(row, 1)
        cell_obj.value = text
        cell_obj.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)  # 设置居中、wrap_text自动换行
    
    workbook_obj.save("./files/op_cell.xlsx")
    
    # 边框
    from openpyxl.styles import Alignment, Border, Side
    from openpyxl.workbook import Workbook
    
    workbook_obj = Workbook()
    
    sheet_0 = workbook_obj.worksheets[0]
    
    name_list = ["张三", "里斯", "王五", "二麻子"]
    
    # 居中 换行
    sheet_0.cell(row=1, column=1).value = "姓名"
    sheet_0.cell(row=1, column=1).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    for row, text in enumerate(name_list, 2):
        cell_obj = sheet_0.cell(row, 1)
        cell_obj.value = text
        cell_obj.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)  # 设置居中、wrap_text自动换行
        cell_obj.border = Border(top=Side(style="thin", color="0000FF"),
                                 left=Side(style="thin", color="0000FF"),
                                 right=Side(style="medium", color="0000FF"),
                                 bottom=Side(style="medium", color="0000FF"))
    
    workbook_obj.save("./files/op_cell.xlsx")
    
  • 设置字体颜色、大小、样式等

    from openpyxl.styles import Alignment, Border, Side, Font
    from openpyxl.workbook import Workbook
    
    workbook_obj = Workbook()
    
    sheet_0 = workbook_obj.worksheets[0]
    
    name_list = ["张三", "里斯", "王五", "二麻子"]
    
    # 居中 换行
    sheet_0.cell(row=1, column=1).value = "姓名"
    sheet_0.cell(row=1, column=1).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    for row, text in enumerate(name_list, 2):
        cell_obj = sheet_0.cell(row, 1)
        cell_obj.value = text
        cell_obj.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)  # 设置居中、wrap_text自动换行
        cell_obj.border = Border(top=Side(style="thin", color="0000FF"),
                                 left=Side(style="thin", color="0000FF"),
                                 right=Side(style="medium", color="0000FF"),
                                 bottom=Side(style="medium", color="0000FF"))
        cell_obj.font = Font(name="宋体", size=12, bold=True, italic=False, color="FFFF00")
    
    workbook_obj.save("./files/op_cell.xlsx")
    
  • 行高、宽

    from openpyxl.styles import Alignment, Border, Side, Font
    from openpyxl.workbook import Workbook
    
    workbook_obj = Workbook()
    
    sheet_0 = workbook_obj.worksheets[0]
    
    name_list = ["张三", "里斯", "王五", "二麻子"]
    
    # 设置行高 宽度
    sheet_0.row_dimensions[1].height = 50
    sheet_0.column_dimensions["A"].width = 80
    sheet_0.column_dimensions["B"].width = 100
    
    # 居中 换行
    sheet_0.cell(row=1, column=1).value = "姓名"
    sheet_0.cell(row=1, column=1).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    for row, text in enumerate(name_list, 2):
        cell_obj = sheet_0.cell(row, 1)
        cell_obj.value = text
        cell_obj.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)  # 设置居中、wrap_text自动换行
        cell_obj.border = Border(top=Side(style="thin", color="0000FF"),
                                 left=Side(style="thin", color="0000FF"),
                                 right=Side(style="medium", color="0000FF"),
                                 bottom=Side(style="medium", color="0000FF"))
        cell_obj.font = Font(name="宋体", size=12, bold=True, italic=False, color="FFFF00")
    
    workbook_obj.save("./files/op_cell.xlsx") 
    
  • 设置背景色

    from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, Color
    from openpyxl.workbook import Workbook
    
    workbook_obj = Workbook()
    
    sheet_0 = workbook_obj.worksheets[0]
    
    name_list = ["张三", "里斯", "王五", "二麻子"]
    
    # 设置行高 宽度
    sheet_0.row_dimensions[1].height = 50
    sheet_0.column_dimensions["A"].width = 80
    sheet_0.column_dimensions["B"].width = 100
    
    # 居中 换行
    sheet_0.cell(row=1, column=1).value = "姓名"
    sheet_0.cell(row=1, column=1).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    
    # 背景色
    sheet_0.cell(row=1, column=1).fill = PatternFill(patternType='solid', fgColor=Color(rgb="808080"))
    
    for row, text in enumerate(name_list, 2):
        cell_obj = sheet_0.cell(row, 1)
        cell_obj.value = text
        cell_obj.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)  # 设置居中、wrap_text自动换行
        cell_obj.border = Border(top=Side(style="thin", color="0000FF"),
                                 left=Side(style="thin", color="0000FF"),
                                 right=Side(style="medium", color="0000FF"),
                                 bottom=Side(style="medium", color="0000FF"))
        cell_obj.font = Font(name="宋体", size=12, bold=True, italic=False, color="FFFF00")
    
    workbook_obj.save("./files/op_cell.xlsx")
    
  • 整合起来

    from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, Color
    from openpyxl.workbook import Workbook
    
    workbook_obj = Workbook()
    
    sheet_0 = workbook_obj.worksheets[0]
    
    name_list = ["张三", "里斯", "王五", "二麻子"]
    
    # 设置行高 宽度
    sheet_0.row_dimensions[1].height = 50
    sheet_0.column_dimensions["A"].width = 80
    sheet_0.column_dimensions["B"].width = 100
    
    # 居中 换行
    sheet_0.cell(row=1, column=1).value = "姓名"
    sheet_0.cell(row=1, column=1).alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    sheet_0.cell(row=1, column=1).fill = PatternFill(patternType='solid', fgColor=Color(rgb="808080"))
    
    for row, text in enumerate(name_list, 2):
        cell_obj = sheet_0.cell(row, 1)
        cell_obj.value = text
        cell_obj.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)  # 设置居中、wrap_text自动换行
        cell_obj.border = Border(top=Side(style="thin", color="0000FF"),
                                 left=Side(style="thin", color="0000FF"),
                                 right=Side(style="medium", color="0000FF"),
                                 bottom=Side(style="medium", color="0000FF"))
        cell_obj.font = Font(name="宋体", size=12, bold=True, italic=False, color="FFFF00")
    
    workbook_obj.save("./files/op_cell.xlsx")
    

2.3.1.3 excel案例2

# 将文件内容写到excel里面
from openpyxl.workbook import Workbook

workbook_obj = Workbook()

sheet_obj = workbook_obj.worksheets[0]
sheet_obj.title = "用户信息"

sheet_obj.cell(row=1, column=1).value = "用户名"
sheet_obj.cell(row=1, column=2).value = "密码"
sheet_obj.cell(row=1, column=3).value = "个人邮箱"

with open("./db/users/account.txt", "r", encoding='utf-8') as fp:
    line_num = 2
    for line in fp:
        line = line.strip()
        if not line:
            continue
        user_msg = line.split(",")
        for col, msg in enumerate(user_msg, start=1):
            sheet_obj.cell(line_num, col).value = msg
        line_num += 1


workbook_obj.save("./files/db/user_info.xlsx")

Clip_2024-04-15_10-56-23

Clip_2024-04-15_10-56-59

  • 在此基础上,给表头加上背景色、边框、居中、字体颜色。
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Alignment, Color, PatternFill, Border, Side

workbook_obj = Workbook()

sheet_obj = workbook_obj.worksheets[0]
sheet_obj.title = "用户信息"

# 表头 居中 字体 背景颜色
sheet_obj.cell(row=1, column=1).value = "用户名"
sheet_obj.cell(row=1, column=1).alignment = Alignment(horizontal="center", vertical="center")
sheet_obj.cell(row=1, column=1).fill = PatternFill(fgColor="DCDCDC", patternType="solid")
sheet_obj.cell(row=1, column=1).font = Font(bold=True, size=12, color=Color(rgb="FF0000"))
sheet_obj.cell(row=1, column=1).border = Border(left=Side(border_style="medium", color="000000"),
                                                right=Side(border_style="medium", color="000000"),
                                                top=Side(border_style="medium", color="000000"),
                                                bottom=Side(border_style="medium", color="000000"),
                                                )

sheet_obj.cell(row=1, column=2).value = "密码"
sheet_obj.cell(row=1, column=2).alignment = Alignment(horizontal="center", vertical="center")
sheet_obj.cell(row=1, column=2).fill = PatternFill(fgColor="DCDCDC", patternType="solid")
sheet_obj.cell(row=1, column=2).font = Font(bold=True, size=12, color=Color(rgb="FF0000"))
sheet_obj.cell(row=1, column=2).border = Border(left=Side(border_style="medium", color="000000"),
                                                right=Side(border_style="medium", color="000000"),
                                                top=Side(border_style="medium", color="000000"),
                                                bottom=Side(border_style="medium", color="000000"),
                                                )

sheet_obj.cell(row=1, column=3).value = "个人邮箱"
sheet_obj.cell(row=1, column=3).alignment = Alignment(horizontal="center", vertical="center")
sheet_obj.cell(row=1, column=3).fill = PatternFill(fgColor="DCDCDC", patternType="solid")
sheet_obj.cell(row=1, column=3).font = Font(bold=True, size=12, color=Color(rgb="FF0000"))
sheet_obj.cell(row=1, column=3).border = Border(left=Side(border_style="medium", color="000000"),
                                                right=Side(border_style="medium", color="000000"),
                                                top=Side(border_style="medium", color="000000"),
                                                bottom=Side(border_style="medium", color="000000"),
                                                )

with open("./db/users/account.txt", "r", encoding='utf-8') as fp:
    line_num = 2
    for line in fp:
        line = line.strip()
        if not line:
            continue
        user_msg = line.split(",")
        for col, msg in enumerate(user_msg, start=1):
            sheet_obj.cell(line_num, col).value = msg
        line_num += 1


workbook_obj.save("./files/db/user_info.xlsx")
  • 写得简洁一点:
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Alignment, Color, PatternFill, Border, Side

workbook_obj = Workbook()

sheet_obj = workbook_obj.worksheets[0]
sheet_obj.title = "用户信息"

header_name_list = ["用户名", "密码", "个人邮箱"]

# 表头 居中 字体 背景颜色 边框
for col, header_name in enumerate(header_name_list, start=1):
    sheet_obj.cell(row=1, column=col).value = header_name
    sheet_obj.cell(row=1, column=col).alignment = Alignment(horizontal="center", vertical="center")
    sheet_obj.cell(row=1, column=col).fill = PatternFill(fgColor="DCDCDC", patternType="solid")
    sheet_obj.cell(row=1, column=col).font = Font(bold=True, size=12, color=Color(rgb="FF0000"))
    sheet_obj.cell(row=1, column=col).border = Border(left=Side(border_style="medium", color="000000"),
                                                      right=Side(border_style="medium", color="000000"),
                                                      top=Side(border_style="medium", color="000000"),
                                                      bottom=Side(border_style="medium", color="000000"),
                                                      )

with open("./db/users/account.txt", "r", encoding='utf-8') as fp:
    line_num = 2
    for line in fp:
        line = line.strip()
        if not line:
            continue
        user_msg = line.split(",")
        for col, msg in enumerate(user_msg, start=1):
            sheet_obj.cell(line_num, col).value = msg
        line_num += 1

workbook_obj.save("./files/db/user_info.xlsx")
  • 设置列宽:
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Alignment, Color, PatternFill, Border, Side
from openpyxl.utils import get_column_letter


workbook_obj = Workbook()

sheet_obj = workbook_obj.worksheets[0]
sheet_obj.title = "用户信息"

header_name_list = ["用户名", "密码", "个人邮箱"]

for i in range(1, 4):
    sheet_obj.column_dimensions[get_column_letter(i)].width = 30


# 表头 居中 字体 背景颜色 边框
for col, header_name in enumerate(header_name_list, start=1):
    sheet_obj.cell(row=1, column=col).value = header_name
    sheet_obj.cell(row=1, column=col).alignment = Alignment(horizontal="center", vertical="center")
    sheet_obj.cell(row=1, column=col).fill = PatternFill(fgColor="DCDCDC", patternType="solid")
    sheet_obj.cell(row=1, column=col).font = Font(name="楷体", bold=True, size=12, color=Color(rgb="FF0000"))
    sheet_obj.cell(row=1, column=col).border = Border(left=Side(border_style="medium", color="000000"),
                                                      right=Side(border_style="medium", color="000000"),
                                                      top=Side(border_style="medium", color="000000"),
                                                      bottom=Side(border_style="medium", color="000000"),
                                                      )

with open("./db/users/account.txt", "r", encoding='utf-8') as fp:
    line_num = 2
    for line in fp:
        line = line.strip()
        if not line:
            continue
        user_msg = line.split(",")
        for col, msg in enumerate(user_msg, start=1):
            sheet_obj.cell(line_num, col).value = msg
        line_num += 1

workbook_obj.save("./files/db/user_info.xlsx")
  • 设置合并单元格 -- 有两种方法
from openpyxl.workbook import Workbook

workbook_obj = Workbook()
sheet_obj = workbook_obj.worksheets[0]
cell_obj = sheet_obj.cell(row=2, column=3)
cell_obj.value = "年龄"

# 合并单元格 -- 方法一
sheet_obj.merge_cells("D2:F8")

# 合并单元格 -- 方法二
sheet_obj.merge_cells(start_row=10, start_column=10, end_row=20, end_column=20)

workbook_obj.save("./files/merge_sheet.xlsx")
from openpyxl.workbook import Workbook
from openpyxl.styles import Alignment

workbook_obj = Workbook()
sheet_obj = workbook_obj.worksheets[0]
cell_obj = sheet_obj.cell(row=1, column=1)
cell_obj.value = "瑾瑜"
cell_obj.alignment = Alignment(horizontal="center", vertical="center")

# Merge cells A1:C1
sheet_obj.merge_cells(start_row=1, start_column=1, end_row=1, end_column=3)

workbook_obj.save("./files/merge_sheet.xlsx")
  • 整合
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Alignment, Color, PatternFill, Border, Side
from openpyxl.utils import get_column_letter


workbook_obj = Workbook()

sheet_obj = workbook_obj.worksheets[0]
sheet_obj.title = "用户信息"

header_name_list = ["用户名", "密码", "个人邮箱"]

for i in range(1, 4):
    sheet_obj.column_dimensions[get_column_letter(i)].width = 30

sheet_obj.cell(row=1, column=1).value = "瑾瑜"
sheet_obj.cell(row=1, column=1).alignment = Alignment(horizontal="center", vertical="center")
sheet_obj.merge_cells(start_row=1, start_column=1, end_row=1, end_column=3)

# 表头 居中 字体 背景颜色 边框
for col, header_name in enumerate(header_name_list, start=1):
    sheet_obj.cell(row=2, column=col).value = header_name
    sheet_obj.cell(row=2, column=col).alignment = Alignment(horizontal="center", vertical="center")
    sheet_obj.cell(row=2, column=col).fill = PatternFill(fgColor="DCDCDC", patternType="solid")
    sheet_obj.cell(row=2, column=col).font = Font(name="楷体", bold=True, size=12, color=Color(rgb="FF0000"))
    sheet_obj.cell(row=2, column=col).border = Border(left=Side(border_style="medium", color="000000"),
                                                      right=Side(border_style="medium", color="000000"),
                                                      top=Side(border_style="medium", color="000000"),
                                                      bottom=Side(border_style="medium", color="000000"),
                                                      )

with open("./db/users/account.txt", "r", encoding='utf-8') as fp:
    line_num = 3
    for line in fp:
        line = line.strip()
        if not line:
            continue
        user_msg = line.split(",")
        for col, msg in enumerate(user_msg, start=1):
            sheet_obj.cell(line_num, col).value = msg
        line_num += 1

workbook_obj.save("./files/db/user_info.xlsx")

Clip_2024-04-15_21-25-09

2.3.1.4 补充

  • 写入公式
from openpyxl.workbook import Workbook

workbook_obj = Workbook()

sheet_0 = workbook_obj.worksheets[0]

# 数量
sheet_0["A1"] = 10
sheet_0["A2"] = 20
sheet_0["A3"] = 8

# 单价
sheet_0["B1"] = 3
sheet_0["B2"] = 8
sheet_0["B3"] = 9

# 写入公式
sheet_0["C1"] = "=A1*B1"
sheet_0["C2"] = "=A2*B2"
sheet_0["C3"] = "=A3*B3"

sheet_0["D1"] = "=sum(A1,B1)"
sheet_0["D2"] = "=sum(A2,B2)"
sheet_0["D3"] = "=sum(A3,B3)"

workbook_obj.save("./files/equal_excel.xlsx")
  • 删除
from openpyxl.workbook import Workbook

workbook_obj = Workbook()

sheet_0 = workbook_obj.worksheets[0]

# 数量
sheet_0["A1"] = 10
sheet_0["A2"] = 20
sheet_0["A3"] = 8

# 单价
sheet_0["B1"] = 3
sheet_0["B2"] = 8
sheet_0["B3"] = 9

# 写入公式
sheet_0["C1"] = "=A1*B1"
sheet_0["C2"] = "=A2*B2"
sheet_0["C3"] = "=A3*B3"

sheet_0["D1"] = "=sum(A1,B1)"
sheet_0["D2"] = "=sum(A2,B2)"
sheet_0["D3"] = "=sum(A3,B3)"

sheet_0.delete_rows(idx=2, amount=1)  # 从第二行开始删除 删除的行数为1 amount默认值为1
sheet_0.delete_columns(idx=3, amount=1)  # 从第三列开始删除 删除的列数为1 amount默认值为1

workbook_obj.save("./files/equal_excel.xlsx")
  • 插入
from openpyxl.workbook import Workbook

workbook_obj = Workbook()

sheet_0 = workbook_obj.worksheets[0]

# 数量
sheet_0["A1"] = 10
sheet_0["A2"] = 20
sheet_0["A3"] = 8

# 单价
sheet_0["B1"] = 3
sheet_0["B2"] = 8
sheet_0["B3"] = 9

# 写入公式
sheet_0["C1"] = "=A1*B1"
sheet_0["C2"] = "=A2*B2"
sheet_0["C3"] = "=A3*B3"

sheet_0["D1"] = "=sum(A1,B1)"
sheet_0["D2"] = "=sum(A2,B2)"
sheet_0["D3"] = "=sum(A3,B3)"

# sheet_0.delete_rows(idx=2, amount=1)  # 从第二行开始删除 删除的行数为1
# sheet_0.delete_columns(idx=3, amount=1)  # 从第三列开始删除 删除的列数为1

sheet_0.insert_rows(idx=1, amount=3)
sheet_0.insert_cols(idx=1, amount=2)

workbook_obj.save("./files/equal_excel.xlsx")

Clip_2024-04-15_22-20-00

  • 追加

[!Caution]

注意,这里面最大行max_row这个参数,如果元Excel没有数据,那么max_row的值为1,如果有n(n>=1)行数据,那么max_row的值为n,这一点要额外注意,下面的代码演示了如何通过条件判断来避免产生错误。

import os
from openpyxl import load_workbook, Workbook


def write_excel(user_name, password):
    """
    将用户名和密码写入excel文件。
    Args:
        user_name (str): 用户名
        password (str): 密码

    Returns:

    """
    # 1. 文件是否存在
    file_path = os.path.join("files", "db.xlsx")
    if os.path.exists(file_path):
        # 2. 打开文件
        workbook_obj = load_workbook(file_path)
    else:
        # 3. 创建文件
        workbook_obj = Workbook()
    sheet_obj = workbook_obj.worksheets[0]

    # 4. 写入数据
    first_row_data = sheet_obj.cell(row=1, column=1).value
    if not first_row_data:  # 第一行没有数据
        row_index = 1
    else:  # 第一行有数据
        row_index = sheet_obj.max_row + 1

    sheet_obj.cell(row=row_index, column=1).value = user_name
    sheet_obj.cell(row=row_index, column=2).value = password
    workbook_obj.save(file_path)


def run():
    """
    程序入口。
    Returns:

    """
    while True:
        print("输入用户名和密码,输入q/Q退出:")
        user_name = input("用户名 >>> ")
        password = input("密  码 >>> ")
        if user_name == "q" or user_name == "q":
            break
        write_excel(user_name, password)


if __name__ == '__main__':
    run()

[!Important]

如果在开发过程中遇到不知道的问题,有下列一些方法:

  • 搜索

    - 百度(最不可信)
    - Google
    - stackoverflow
    
  • 官方文档和源码(需要知道面向对象等知识)

2.3.2 Word操作

pip install python-docx
  • .docx文件 -- 本质上是一个压缩包

  • .docx文件 -- 底层存储:xml格式 python-docx本质上就是去解析xml文件

    <x1>fesdj9fjw0efsk</x1>
    <xx>
    	<mm>fewgregrthytjuykdgtr<mm>
    <xx>
    
    • 段落对象 -- pragraph
    • 小段 -- run
    import docx
    
    doc_obj = docx.Document("./docx_files/demo.docx")
    
    # 获取某一个段落
    p1 = doc_obj.paragraphs[1]
    
    # 获取段落文本
    print(p1.text)
    
    # 获取段落样式
    print(p1.style.name)
    
    # # 获取段落中所有的runs -- run对象
    # runs = p1.runs
    
    run_0 = p1.runs[3]
    print(run_0.text)  # 文本是什么
    print(run_0.bold)  # 是否加粗
    print(run_0.italic)  # 是否斜体
    print(run_0.underline)  # 是否下划线
    print(run_0.font.color.rgb)  # 字体颜色
    print(run_0.font.name)  # 字体名称
    print(run_0.font)  # 字体对象
    print(run_0.font.size)  # 字体大小
    
    import docx
    
    doc_obj = docx.Document("./docx_files/demo.docx")
    
    # 获取所有段落
    paragraph = doc_obj.paragraphs
    
    for p in paragraph:
        print(p.text)
    

    Clip_2024-04-16_15-37-36

    下图是word的样式:

Clip_2024-04-16_15-54-26

问题 -- paragraph无法读取图片,只能获取普通编写的文本。 -- 解决方法(难 需要进一步分析xml文档)

问题 -- paragraph无法读取表格,只能获取普通编写的文本。 -- 解决方法(简单 + 难)

# 简单:段落和表格独立操作
for data in doc_obj.tables:
    print(data)
    
# 难:逐行读取,需要进一步分析xml文档

写在最后

由于这些笔记都是从typora里面粘贴过来的,导致图片会加载失败,如果想要带图片的笔记的话,我已经上传至github,网址(​​https://github.com/wephiles/python-foundation-note​​​)如果github上不去的话也可以去我的gitee下载,网址(​​https://gitee.com/wephiles/python-django-notes​​)。欢迎大家来下载白嫖哦,最后,如果可爱又善良的你能够给我github点个star,那你将会是这个世界上运气最好的人喔。

标签:obj,Python,excel,cell,模块,import,workbook,sheet,row
From: https://www.cnblogs.com/wephilos/p/18156321

相关文章

  • 交点法公路计算的一些相关方法函数(Python)
    1#弧度归一化2@staticmethod3defnormalize_radian(radian):4returnradian%tau56#获取坐标差7@staticmethod8defget_coord_diff(start_x,start_y,end_x,end_y):9returnend_x-start_x,end_y-st......
  • Python对商店数据进行lstm和xgboost销售量时间序列建模预测分析|附代码数据
    全文下载链接:http://tecdat.cn/?p=17748最近我们被客户要求撰写关于销售量时间序列建模的研究报告,包括一些图形和统计输出。在本文中,在数据科学学习之旅中,我经常处理日常工作中的时间序列数据集,并据此做出预测我将通过以下步骤:探索性数据分析(EDA)问题定义(我们要解决什么)变量......
  • 模块(pickle、subprocess、正则re)
    【一】序列化模块【1】json模块将python对象序列化成json字符串将json字符串反序列化成python对象importjsonjson.dump()#写文件json.dumps()#转换字符串json.load()#读数据json.loads()#将字符串转回对象【2】pickle模块用于python特有的类型和python的......
  • Python基础-模块和包(hashlib、random、json、time、datetime和os模块)
    什么是模块和包?模块:python中的.py文件,将一些功能按照某一种维度进行划分;自定义、内置。、第三方.包:文件夹里面好多个.py文件。在讨论的时候,一般统称为:模块。学习:自定义模块和包+使用内置模块+使用第三方模块+使用1自定义模块和包1.1快速上手-项目文件夹(......
  • Python中利用长短期记忆模型LSTM进行时间序列预测分析 - 预测电力负荷数据|附代码数据
    原文链接:http://tecdat.cn/?p=6663  此示例中,神经网络用于使用2011年4月至2013年2月期间的数据预测公民办公室的电力消耗 ( 点击文末“阅读原文”获取完整代码数据******** )。每日数据是通过总计每天提供的15分钟间隔的消耗量来创建的。LSTM简介LSTM(或长短期记忆人工神......
  • Python函数参数为列表问题
    defADD(a):print(3,a,hex(id(a)))a.remove(2)print(3,a,hex(id(a)))a=a.append(10)print(3,a,hex(id(a)))returnaa=[1,2,3]print(1,a,hex(id(a)))b=ADD(a)print(2,a,hex(id(a)))print(2,b,hex(id(b)))函数传参的......
  • 使用 Python 循环创建多个列表
    前言在Python中,我们可以使用循环来动态创建多个列表,这在处理数据、进行数据分析或进行算法实现时非常有用。本文将介绍几种常见的方法,以帮助大家学习如何使用循环创建多个列表。方法一:使用列表推导式列表推导式是Python中一种简洁的语法,可以快速生成列表。我们可以结合循......
  • 修改文件内容 python
    第一种方法,简单直接粗暴的,缺点就是如果文件非常大时,把所有内容都读出来,会非常占用内存:f=open('a.txt','a+',encoding='utf-8')#不能用w+,用w+直接给文件清空了,读不出来内容了f.seek(0)result=f.read()new_result=result.replace('abc','ABC')f.seek(0)f.truncate......
  • 新手下载python和anaconda3注意事项
    新手下载python和anaconda3要注意哪些1、python关于python下载其实很简单,直接在官网下载就行。官网:WelcometoPython.org当然,到了官网下载是默认最新版本,如果你需要旧版本,那就需要找一下了,这里提供一下windows的各版本的官网链接:PythonReleasesforWindows|Python.org......
  • 深度学习Python代码小知识点(备忘,因为没有脑子)
    现在是2024年4月24日16:58,今天摸鱼有点多,备忘一下,都写到一篇内容里面,免得分散。 1.np.concatenate()函数'np.concatenate'是NumPy库中用来合并两个或多个数组的函数。它可以在任意指定的轴上连接数组,是数据预处理和特征工程中常用的工具。基本语法:numpy.concatenate((a1,a2......