Excel封装

import openpyxl
#也可以写成 from openpyxl import load_workbook
from pprint import pprint


class ExcelHandler:

    def __init__(self, path):
        """初始化"""
        self.path = path
        self.workbook = None

    def open_file(self):
        """打开工作簿"""
        workbook = openpyxl.load_workbook(self.path)
        self.workbook = workbook

    def get_sheet(self,name):#name是sheet名
        """获取sheet"""
        self.open_file()
        return self.workbook[name]

    def read_data_dict(self, name):  # name是sheet名
        """读取所有行，并保存到字典中"""
        sheet = self.get_sheet(name)

        # 获取所有行
        rows = list(sheet.rows)

        title = []
        data = []

        for header in rows[0]:
            title.append(header.value)

        for row in rows[1:]:
            row_data = {}

            for idx, cell in enumerate(row):
                row_data[title[idx]] = cell.value

            data.append(row_data)

        return data

    def read_data_list(self,name): #name是sheet名
        """读取所有行，并保存到列表中"""
        sheet = self.get_sheet(name)

        #获取所有行
        rows = list(sheet.rows)

        title = []

        for header in rows[0]:
            title.append(header.value)

        data = []
        for row in rows:
            cell_list = []
            for cell in row:
                cell_list.append(cell.value)
            data.append(cell_list)

        return data

    def write(self,sheet_name,row,colum,data):
        """写入数据到单元格"""
        sheet = self.get_sheet(sheet_name)
        cell = sheet.cell(row,colum)
        cell.value = data
        #写入后需要保存并关闭才能保存成功
        self.save()
        self.close()

    def save(self):
        """保存工作簿"""
        self.open_file()
        self.workbook.save(self.path)

    def close(self):
        """关闭工作簿"""
        self.open_file()
        self.workbook.close()