python openqyxl 操作excel

xujunhui / 2023-07-31 / 原文

import os
import sys
from openpyxl import load_workbook

class Excel:  # 读取数据

    def __init__(self, excel_path, sheet_name):
        """

        :param excel_path: excel文件路径 : xx/xxx.xlsx
        :param sheet_name: 需要打开表格名字 : sheet1
        """
        self.work_book = load_workbook(excel_path)
        self.work_sheet = self.work_book.get_sheet_by_name(sheet_name)

        # 拷贝
        self.new_sheet = self.work_book.copy_worksheet(self.work_sheet)

        # 获取总行，获取总列
        self.row = self.work_sheet.max_row  # 获取总行数
        self.columns = self.work_sheet.max_column  # 获取总列数


    def read_excel_date(self):
        """
        :return: 把列表第一行标题与每一行数据，拉链字典，然后append添加到list中，返回: [{},{}]
        """
        # 先判断表格中是否有内容
        if self.row > 1:
            lst = []
            key = list(map(lambda x: x.value, self.work_sheet[1]))  # 第一列的数值
            for row in self.work_sheet.iter_rows(min_row=2):
                value = list(map(lambda x: x.value, row))
                lst.append(dict(zip(key, value)))  # 拉链成一个列表字典
            return lst

    def write_excel_data(self, column, value, row=2):
        """

        :param column: 第几列
        :param value: 写入内容
        :param row: 第几行，默认从第二行开始，因为第一行是标题
        :return:
        """
        script_path = os.path.dirname(os.path.realpath(sys.argv[0]))
        relative_path = os.path.join(script_path, 'excel\修改结果.xlsx')
        self.new_sheet.cell(row=row, column=column, value=value)
        self.work_book.save(relative_path)

    def new_close(self,):
        """
        关闭excel 文件
        :return:
        """

        self.work_book.close()




# a = Excel(excel_path='D:\PyCharm\水保护\data.xlsx', sheet_name='Sheet1')
#
# new_row = 2
# for row in range(6):
#     a.write_excel_data(row=new_row, column=6, value='通过')
#     new_row += 1
#
# print('ok')

使用

from new_Flask.rendExcelopenpyxl import Excel


a = Excel(excel_path='D:\PyCharm\水保护\data.xlsx', sheet_name='Sheet1')

new_row = 2
for row in range(22):
    a.write_excel_data(row=new_row, column=6, value='通过')  # 写入功能
    new_row += 1

print('ok')