关注小程序 找一找教程网-随时随地学编程

Python教程

python 中使用openpyxl处理excel

import openpyxl
from Contens.Log import HandleLog
from Contens.Config import HandleConfig

do_config = HandleConfig('Config.conf')
do_log = HandleLog().get_logger()
do_log.debug("aaaaaaaaaaa")
column1 = do_config.get_int('excel', 'column1')
column2 = do_config.get_int('excel', 'column2')


class HandleExcel:
    """
    处理excel表格类
    """

    def __init__(self, Filename, Sheetname=None):
        self.Filename = Filename
        self.Sheetname = Sheetname

    # 获取所有数据
    def get_datas(self):
        wb = openpyxl.load_workbook(self.Filename)
        try:
            if self.Sheetname is None:
                ws = wb.active
            else:
                ws = wb[self.Sheetname]
        except Exception as a:
            do_log.exception('表单名字输入错误')
            raise a
        else:
            get_title_data = tuple(ws.iter_rows(max_row=1, values_only=True))[0]
            get_value_data = tuple(ws.iter_rows(min_row=2, values_only=True))
            data_list = []
            for value_data in get_value_data:
                value_dict = dict(zip(get_title_data, value_data))
                data_list.append(value_dict)
            return data_list

    # 获取指定行数据
    def get_specify_data(self, row):
        return self.get_datas()[row - 1]

    # 写入指定列数据
    def write_datas(self, row, actual, result):
        write_wb = openpyxl.load_workbook(self.Filename)
        if self.Sheetname is None:
            write_ws = write_wb.active
        else:
            write_ws = write_wb[self.Sheetname]
        if isinstance(row, int) and (2 <= row <= write_ws.max_row):
            actual_cell = write_ws.cell(row=row, column=column1, value=actual)
            result_cell = write_ws.cell(row=row, column=column2, value=result)
            write_wb.save(self.Filename)
        else:
            print('行号输入错误')
            return '行号输入错误'
        pass


# if __name__ == '__main__':
#     # c =
#     do_excel = HandleExcel(do_config.get_value('excel', 'filename'))
#     do_excel.write_datas('c', actual='a', result='b')