很久没来写博客了，前段时间操作Excel的时候，踩了一个大坑儿，决定把它弄明白。于是就又来了一次种搜的过程。现将部分成果分享如下。今天有点累，不说了，直接上代码。

# _*_ coding:utf-8 _*_

import xlrd
import xlwt
from xlutils.copy import copy


class ReadWriteExcel:
    ”“”
    根据不同的需求读取Excel文件里的不同内容，可根据sheet/row/col/cell不同的粒度来读取
    “”“
    def __init__(self, file_path):
        self.file_path = file_path

    
    def read_dict_all(self, first_row, sheet_num):
        ”“”读取excel全部内容,返回字典！！！“”“
        list = []  # 放每一行
        dict_row = {}  # 放每一行
        list_sheet = []  # 放一个sheet
        workbook = xlrd.open_workbook(self.file_path)
        sheet = workbook.sheet_by_index(sheet_num - 1)
        for row_index in range(first_row - 1, sheet.nrows):
            row = sheet.row_values(row_index)
            for cell in row:
                list.append(cell)
            dict_row = {'is_run': list[0], 'case_num': list[1], 'case_name': list[2], 'is_return': list[3],
                        'case_flag': list[4], 'method': list[5], 'url': list[6], 'header': list[7], 'body': list[8],
                        'para': list[9], 'expect': list[10], 'return_para': list[11]}
            list_sheet.append(dict_row)
            list = []
        return list_sheet

    
    def read_all(self, first_row):
        “”“读取excel全部内容,放到列表里,返回一个列表:[sheet[row[col[]]]]”“”
        list_row = []
        list_sheet = []
        list_book = []
        workbook = xlrd.open_workbook(self.file_path)
        for sheet in workbook.sheets():
            for sheet_index in range(first_row - 1, sheet.nrows):
                row = sheet.row_values(sheet_index)
                for cell in row:
                    list_row.append(cell)
                list_sheet.append(list_row)
                list_row = []
            list_book.append(list_sheet)
            list_sheet = []
        return list_book

    
    def read_sheet(self, first_row, sheet_num):
        “”“读取excel指定sheet内容，放到列表里”“”
        list_row = []
        list_sheet = []
        sheet = xlrd.open_workbook(self.file_path).sheet_by_index(sheet_num - 1)
        for row_index in range(first_row - 1, sheet.nrows):
            for cell in sheet.row_values(row_index):
                list_row.append(cell)
            list_sheet.append(list_row)
            list_row = []
        return list_sheet

    
    def read_row(self, list_rowNum, sheet_num):
        “”“读取excel指定row内容，放到列表里”“”
        list_row = []
        list_rows = []
        sheet = xlrd.open_workbook(self.file_path).sheet_by_index(sheet_num - 1)
        for row_index in range(list_rowNum[0] - 1, list_rowNum[1]):
            for cell in sheet.row_values(row_index):
                list_row.append(cell)
            list_rows.append(list_row)
            list_row = []
        return list_rows

    
    def read_col(self, first_row, *args):
        “”“读取excel指定的列”“”
        print(args)
        list_row = []
        list_sheet = []
        list_book = []
        workbook = xlrd.open_workbook(self.file_path)
        for sheet in workbook.sheets():
            for row_index in range(first_row - 1, sheet.nrows):
                for col_index in args:
                    list_row.append(sheet.cell_value(row_index, col_index - 1))
                list_sheet.append(list_row)
                list_row = []
            list_book.append(list_sheet)
            list_sheet = []
        return list_book

    
    def read_cell(self, sheet_num, row_num, col_num):
        “”“读取excel指定的cell”“”
        workbook = xlrd.open_workbook(self.file_path)
        sheet = workbook.sheet_by_index(sheet_num - 1)
        cell = sheet.cell_value(row_num - 1, col_num - 1)
        return cell

   
    def write_func(self, sheet_num, row_num, col_num, content):
        “”“写入excel文件”“”
        reader = xlrd.open_workbook(self.file_path)
        workbook = copy(reader)
        sheet = workbook.get_sheet(sheet_num - 1)
        sheet.write(row_num - 1, col_num - 1, content)
        workbook.save(self.file_path)

    def testChange(self, first_row, *args):
        data1 = args[0]
        data2 = args[1]
        goal = args[2]
        from src.tools.ReadExcel import read_col
        list_nums = read_col(self.file_path, first_row, data1, data2)
        print(list_nums)
        test = 1
        for list_sheet in list_nums:
            for j in range(len(list_sheet)):
                list_num = list_sheet[j]
                sum = int(list_num[0]) * int(list_num[1])
                print(sum)
                from src.tools.ReadExcel import write_func
                write_func(self.file_path, test, j + 2, goal, sum)
            test += 1


if __name__ == '__main__':
    file_path = "/Users/helloworldliuyu/Desktop/nt1.xlsx"
    dre = ReadWriteExcel()
    ”“”在下面做你要做的事“”“