python win32com excel

class EasyExcel:
    from win32com.client import Dispatch
    import win32com.client
    import collections
    """A utility to make it easier to get at Excel.    Remembering
    to save the data is your problem, as is    error handling.
    Operates on one workbook at a time."""
    def __init__(self, filename=None):  # 打开文件或者新建文件（如果不存在的话）
        self.xlApp = win32com.client.Dispatch('Excel.Application')
        self.xlApp.Visible = 0  #0代表隐藏对象，但可以通过菜单再显示-1代表显示对象2代表隐藏对象，但不可以通过菜单显示，只能通过VBA修改为显示状态"""
        self.xlApp.DisplayAlerts = 0  # 后台运行，不显示，不警告
        if os.path.isfile(filename) is True:
            self.filename = filename
            self.xlBook = self.xlApp.Workbooks.Open(filename)
        else:
            self.xlBook = self.xlApp.Workbooks.Add()
            self.filename = ''

    def save(self, newfilename=None):  # 保存文件
        if newfilename:
            self.filename = newfilename
            self.xlBook.SaveAs(newfilename)
        else:
            self.xlBook.Save()

    def close(self):  # 关闭文件
        self.xlBook.Close(SaveChanges=0)
        del self.xlApp

    def get_rows(self,sheet = "Sheet1"):
        sht = self.xlBook.Worksheets(sheet)
        return sht.usedrange.rows.count

    def get_cols(self,sheet = "Sheet1"):
        sht = self.xlBook.Worksheets(sheet)
        return sht.usedrange.columns.count

    def getCell(self, sheet="Sheet1", row=1, col=1):  # 获取单元格的数据
        "Get value of one cell"
        sht = self.xlBook.Worksheets(sheet)
        return sht.Cells(row, col).Value

    def setCell(self, sheet, row, col, value):  # 设置单元格的数据
        "set value of one cell"
        sht = self.xlBook.Worksheets(sheet)
        sht.Cells(row, col).Value = value

    def getRange(self, sheet, row1, col1, row2, col2):  # 获得一块区域的数据，返回为一个二维元组
        "return a 2d array (i.e. tuple of tuples)"
        sht = self.xlBook.Worksheets(sheet)
        return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value

    def addPicture(self, sheet, pictureName, Left, Top, Width, Height):  # 插入图片
        "Insert a picture in sheet"
        sht = self.xlBook.Worksheets(sheet)
        sht.Shapes.AddPicture(pictureName, 1, 1, Left, Top, Width, Height)

    def cpSheet(self, before):  # 复制工作表
        "copy sheet"
        shts = self.xlBook.Worksheets
        shts(1).Copy(None, shts(1))
    
    #将一个字典列表写入excel当中
    #datalist 传入的字典列表
    #sheet工作表，默认为sheet1，
    # key_List 字典的keys,是一个列表，若有顺序，需要手动输入，如不设置，系统自动排序
    # header，excel头，如不设置，默认为key_List
    def set_content(self,data_list,sheet="Sheet1",key_list = None,header = None):
        if type(data_list)!=list:
            print("not list")
            return False
        elif type(data_list[0])!=dict:
            print("not dict")
            return False
        if key_list == None:
            key_list = list(data_list[0].keys())
        if header ==None:
            header = key_list
        cols = len(key_list)
        for col in range(0,cols):
            self.setCell(sheet=sheet,row=1,col=col+1,value=header[col])#设置头
        for row,item in enumerate(data_list):
            for col in range(0,cols):                    #设置内容
                self.setCell(sheet=sheet,row=row+2,col=col+1,value=item[key_list[col]])

    # 获取整个工作表内容，返回一个字典列表。key为header也就是excel首行内容。
    def get_content(self,sheet="Sheet1"):
        key_list =[];data_list =[]
        rows = self.get_rows(sheet)
        cols = self.get_cols(sheet)
        for col in range(0, cols):
            key_list.append((self.getCell(sheet=sheet,row=1, col=col + 1)))
        for row in range(rows):
            data = collections.OrderedDict()   #创建有序字典
            for col in range(cols):
                # self.__addWord(data,key_list[col],self.getCell(sheet=sheet,row=row+2,col=col+1))
                # print(type(self.getCell(sheet=sheet,row=row + 2, col=col + 1)))
                data[key_list[col]] = str(self.getCell(sheet=sheet,row=row + 2, col=col + 1))
            data_list.append(data)
        return data_list

    def get_header(self,sheet="Sheet1"):
        key_list = []
        cols = self.get_cols(sheet)
        for col in range(0, cols):
            key_list.append(self.getCell(sheet=sheet, row=1, col=col + 1))
        return key_list
    # def rename_sheet(self,sheet="Sheet1",newname = None):
    #     if newname == None:
    #         return False
    #     else:

    def row_delete(self,sheet = "Sheet1",row_start=0,row_stop=0):
        sht = self.xlBook.Worksheets(sheet)
        # for i in range(row_start,row_stop)
        avg = "{}:{}".format(str(row_start),str(row_stop))
        sht.rows(avg).delete

    def __addWord(self,theIndex, word, pagenumber):
        theIndex.setdefault(word, []).append(pagenumber)  # 存在就在基础上加入列表，不存在就新建个字典

附个键位码表：
字母和数字键     数字小键盘的键       功能键         其它键 
      键   键码   键   键码     键   键码    键      键码 
      A   65       0   96        F1   112     Backspace    8 
      B   66       1   97        F2   113     Tab       9 
      C   67       2   98        F3   114     Clear      12 
      D   68       3   99        F4   115     Enter      13 
      E   69       4   100       F5   116     Shift      16 
      F   70       5   101       F6   117     Control     17 
      G   71       6   102       F7   118      Alt       18 
      H   72       7   103       F8   119     Caps Lock    20 
      I   73       8   104       F9   120     Esc       27 
      J   74       9   105       F10  121     Spacebar    32 
      K   75       *   106       F11  122     Page Up     33 
      L   76       +   107       F12  123     Page Down    34 
      M   77       Enter 108       --   --      End       35 
      N   78       -   109       --   --       Home      36 
      O   79       .   110       --   --      Left Arrow   37 
      P   80       /   111       --   --      Up Arrow    38 
      Q   81       --   --       --   --      Right Arrow   39 
      R   82       --   --       --   --      Down Arrow    40 
      S   83       --   --       --   --      Insert      45 
      T   84       --   --       --   --      Delete      46 
      U   85       --   --       --   --      Help       47 
      V   86       --   --       --   --      Num Lock     144 
      W   87          
      X   88      
      Y   89      
      Z   90      
      0   48      
      1   49      
      2   50       
      3   51       
      4   52       
      5   53       
      6   54       
      7   55       
      8   56       
      9   57

简书原文链接