Python操纵Excel

引言：

　　对于测试来说，可能经常需要操纵excel，常用的框架有xlrd，openpyxl

from openpyxl import Workbook,load_workbook
import os
import datetime
class get_excel():
    def __init__(self,path=None,new_path=None):
        '''
        初始化
        :param path:
        '''
        try:
            self.wb=load_workbook(path)
            self.sheets_num=self.wb.get_sheet_names()
            self.sheet=self.sheets_num[0]
            self.ws=self.wb[self.sheet]
            self.newpath=new_path
        except Exception as e:
            print(e.args)

    def create_workbook(self,data,path):
  
        new_workbook = Workbook()
        ws = new_workbook.active
        ws.title = 'result'

        max_rows=len(data)
        max_col= len(max(data, key=lambda i: len(i)))
        # for i in max_rows:
        #     for j in max_col:
        #         ws.cell(row=i,column=j).value=data[i][j]
        for row in range(len(data)):
            ws.append(data[row])
        new_workbook.save(filename=path)
        return ws

    def max_area(self):
        '''
        获取指定表的数据
        :return:返回读取到的数据
        '''
        rows=self.ws.max_row
        colums=self.ws.max_column
        return [rows,colums]
    def get_source_value(self,row,column):
        cellvalue=self.ws.cell(row=row,column=column).value
        return cellvalue
    def get_col_value(self,column):
        max_area_list=self.max_area()
        max_rows=max_area_list[0]
        column_data=[]
        for i in range(max_rows+1):
            col_value=self.ws.cell(row=i,column=column).value
            column_data.append(col_value)
        return column_data
    def get_row_value(self,row=0):
        max_area_list=self.max_area()
        max_cols=max_area_list[1]
        row_data=[]
        for i in range(1,max_cols+1):
            row_value=self.ws.cell(row=row,column=i).value
            row_data.append(row_value)
        return row_data
    def set_cell_value(self,row,col,value,path):

        try:
            self.ws.cell(row=row,column=col).value=value
            self.ws.save(path)
        except:
            self.ws.cell(row=row,column=col).value='写入错误'

    def all_value(self):
        max_area=self.max_area()
        rows=max_area[0]
        col=max_area[1]
        data_list=[]
        for i in range(1,rows+1):
            a=self.get_row_value(row=i)
            data_list.append(a)
        return data_list