# -*- coding: utf-8 -*-
import openpyxl


class Excel:
    def __init__(self,filename,sheetname):
        self.filename = filename
        self.sheetname = sheetname
        self.wb = openpyxl.load_workbook(self.filename)     #生成文件对象，表示要操作的是哪个文件
        self.sheet = self.wb[self.sheetname]
        self.maxrow = self.sheet.max_row
        self.maxcolumn = self.sheet.max_column

#最大行
    def getMaxRow(self):
        maxrow = self.sheet.max_row
        return maxrow

#最大列
    def getMaxColumn(self):
        maxcolumn=self.sheet.max_column
        return maxcolumn
#读取xlsx中某一行的内容
    def listRow(self,rowname):
        list1=[]
        maxcolumn=self.sheet.max_column
        for i in range(maxcolumn):
            row1=self.sheet.cell(row=rowname,column=i+1).value
            list1.append(row1)
        return list1

    def dictRow(self, rownum=1):
        dict1={}
        maxcolumn = self.sheet.max_column
        for i in range(maxcolumn):
            key=self.sheet.cell(row=1,column=i+1).value
            row1 = self.sheet.cell(row=rownum, column=i + 1).value
            dict1[key]=row1
        return dict1
#获取某一列的内容
    def listColumn(self,maxco):
        list1 = []
        for i in range(self.sheet.max_row):
            row1 = self.sheet.cell(row=i+1, column=maxco).value
            list1.append(row1)
        return list1

    def dictColumn(self,maxco):
        dict1={}
        maxrow = self.sheet.max_row
        for i in range(maxrow):
            key = self.sheet.cell(row=1, column=i + 1).value
            row1 = self.sheet.cell(row=i+1, column=maxco).value
            dict1[key]=row1
        return dict1
#获取所有的数据,以列表里面套字典的方式读取出来
    def dictAll(self):
        list1=[]
        dict2={}
        list2=[]
        for i in range(1,self.maxcolumn+1):
            list1.append(self.sheet.cell(row=1,column=i).value)
        for n in range(2,self.maxrow+1):
            for x in range(1,self.maxcolumn+1):
                dict2[list1[x-1]]=self.sheet.cell(row=n,column=x).value
            list2.append(dict2)
            dict2={}
        return list2
#在指定单元格里写数据：
    def wtxls(self,rowunm,colnum,data):
        self.sheet.cell(row=rowunm,column=colnum).value=data
        self.wb.save(self.filename)
#获取所有的数据，用列表的方式
    def listAll(self):
        row1=[]
        row2=[]
        maxrow=self.sheet.max_row
        maxcol = self.sheet.max_column
        for i in range(1,maxrow):
            for x in range(maxcol):
                row1.append(self.sheet.cell(row=i + 1, column=x + 1).value)
            row2.append(row1)
            row1=[]
        return row2
#获取所有行的内容 但是指定列
    def listData(self,startCol,endCol):
        row1 = []
        row2 = []
        for i in range(1, self.sheet.max_row):
            for x in range(startCol,endCol+1):
                row1.append(self.sheet.cell(row=i + 1, column=x).value)
            row2.append(row1)
            row1 = []
        return row2