python-Excel文件处理

from openpyxl import load_workbook

class ReadExcel:
    def __init__(self, wb_name, sheet_name):
        self.wb = load_workbook(wb_name)
        self.sheet = self.wb[sheet_name]

    def read_all_datas(self):
        res_excel = []
        for row_id in range(1,self.sheet.max_row+1):
            res_excel_row = []
            for col_id in range(1,self.sheet.max_column+1):
                res_excel_row.append(self.sheet.cell(row_id, col_id).value)
            res_excel.append(res_excel_row)
        return res_excel

    def read_test_data(self):
        res_test_datas = []
        for i in range(1, self.sheet.max_row+1):
            res_test_data = {}
            res_test_data['method'] = self.sheet.cell(i,1).value
            res_test_data['url'] = self.sheet.cell(i,2).value
            res_test_data['data'] = eval(self.sheet.cell(i,3).value)
            res_test_data['expected'] = self.sheet.cell(i,4).value
            res_test_datas.append(res_test_data)
        return res_test_datas

    def get_header(self):
        header = []
        for i in range(1,self.sheet.max_column+1):
            header.append(self.sheet.cell(1,i).value)
        return header

    def get_title_data(self):
        header = self.get_header()
        test_datas = []
        for i in range(2, self.sheet.max_row+1):
            test_data = {}
            for j in range(1,self.sheet.max_column+1):
                test_data[header[j-1]] = self.sheet.cell(i,j).value
            test_datas.append(test_data)
        return header, test_datas


if __name__ == '__main__':
    res = ReadExcel("test_excel01.xlsx", 'test').read_all_datas()
    res1 = ReadExcel("test_excel01.xlsx", 'test').read_test_data()
    header, res2 = ReadExcel("test_excel02.xlsx", 'test').get_title_data()
    print(header)
    print(res2)

修改单元格代码：

self.sheet.cell(rowid,columnid).value = text
self.wb.save(self.filename)

 

Excel文件格式一：

 

Excel文件格式二：