Python3 win32com处理Excel，部分接口调试代码 备忘
#!/usr/bin/python # coding=utf-8 ''' Created on 2017-10-27 @author: humingwei ''' import os import win32com.client as win32 from threading import current_thread, _MainThread # 下划线种类: XlUnderlineStyle 枚举 (Word) # xlUnderlineStyleNone -4142 无下划线。 # xlUnderlineStyleSingle 2 单下划线。 # xlUnderlineStyleDouble -4119 粗双下划线。 # xlUnderlineStyleSingleAccounting 4 不支持。 # xlUnderlineStyleDoubleAccounting 5 彼此靠近的两条细下划线。 # ColorIndex: XlColorIndex 枚举 (Excel) # xlColorIndexNone -4142 无颜色。 # xlColorIndexAutomatic -4105 自动配色。 # 1-黑、2-白、3-红、4-绿、5-青、6-黄、7-紫、8-蓝 class Excel(object): def __init__(self, file_path=''): '''初始''' if not isinstance(current_thread(), _MainThread): from pythoncom import CoInitialize CoInitialize() # 为调用线程初始化COM库。 self.xlapp = win32.gencache.EnsureDispatch('Excel.Application') self.xlapp.Visible = False self.xlapp.DisplayAlerts = False # 不警告 if os.path.isfile(file_path): self.workbook = self.xlapp.Workbooks.Open(file_path) else: self.workbook = self.xlapp.Workbooks.Add() self.workbook.SaveAs(file_path) def save(self, new_file_name=None): '''保存''' if new_file_name: self.workbook.SaveAs(new_file_name) else: self.workbook.Save() def get_sheet(self, sheet_name=1): '''获取页签''' try: sheet = self.workbook.Worksheets(sheet_name) except: if isinstance(sheet_name, str): sheet = self.workbook.Worksheets.Add() sheet.Name = sheet_name else: raise Exception('The number of sheet is out of range!\n') return Sheet(sheet) def close(self): '''保存并退出''' self.save() self.xlapp.Application.Quit() def __del__(self): '''''' self.close() del self.xlapp class Sheet(object): def __init__(self, sheet): '''''' self.sheet = sheet def get_cell_rows(self): '''获取已使用行数''' return self.sheet.UsedRange.Rows.Count def get_cell_cols(self): '''获取已使用列数''' return self.sheet.UsedRange.Columns.Count def get_cell(self, row, col): '''获取单元格数据''' return self.sheet.Cells(row, col).Value def set_cell(self, row, col, value, bold=False, under_line=-4142, color_index=-4142): '''设置单元格数据''' the_cell = self.sheet.Cells(row, col) the_cell.Value = value the_cell.Font.Bold = bold the_cell.Font.Underline = under_line the_cell.Font.ColorIndex = color_index def get_range(self, row1=1, col1=1, row2=None, col2=None): '''获取块数据''' if not row2: row2 = self.get_cell_rows() if not col2: col2 = self.get_cell_cols() return self.sheet.Range(self.sheet.Cells(row1, col1), self.sheet.Cells(row2, col2)).Value def set_range(self, row1=1, col1=1, value=[], bold=False, under_line=-4142, color_index=-4142): '''块赋值''' rows = len(value) cols = len(value[0]) the_range = self.sheet.Range(self.sheet.Cells(row1, col1), self.sheet.Cells(row1 + rows - 1, col1 + cols - 1)) the_range.Value = value the_range.Font.Bold = bold the_range.Font.Underline = under_line the_range.Font.ColorIndex = color_index # xchart = self.sheet.Shapes.AddChart() xchart = self.sheet.Shapes.AddChart2() # 简单图表 xchart # cols = None # 按行写，有利于节约内存，不利于过滤出问题数据 # for row_value in value: # if not cols: # cols = col1 + len(row_value) - 1 # self.sheet.Range(self.sheet.Cells(row1, col1), self.sheet.Cells(row1, cols)).Value = row_value # row1 += 1 def merge_cells(self, row1, col1, row2, col2, value='', bold=False): '''合并单元格''' the_range = self.sheet.Range(self.sheet.Cells(row1, col1), self.sheet.Cells(row2, col2)) the_range.MergeCells = True if value: the_range.Value = value the_range.Font.Bold = bold def add_hyperlink(self, row, col, address, sub_address='', value='', tip=''): ''' @todo: 添加超链接 @param address: 超链接的地址 @param sub_address: 超链接的子地址 @param value: 要显示的超链接的文本 @param tip: 要显示的超链接的文本 ''' the_cell = self.sheet.Cells(row, col) self.sheet.Hyperlinks.Add(Anchor=self.sheet.Range(the_cell, the_cell), Address=address, SubAddress=sub_address, ScreenTip=tip, TextToDisplay=value) def add_picture(self, picture_path, left, top, width, height, link_file=False, save_with_document=True): ''' @todo: 插入图片 @param picture_path: 要创建的 OLE 对象的源文件 @param left: 相对于文档的左上角，以磅为单位给出图片左上角的位置 @param top: 相对于文档的顶部，以磅为单位给出图片左上角的位置 @param width: 以磅为单位给出图片的宽度 @param height: 以磅为单位给出图片的高度 @param link_file: 要链接至的文件 @param save_with_document: 将图片与文档一起保存 ''' self.sheet.Shapes.AddPicture(picture_path, link_file, save_with_document, left, top, width, height) if __name__ == '__main__': import threading print(isinstance(threading.current_thread(), threading._MainThread)) file_name = 'test.xlsx' file_path = os.path.join(os.path.split(os.path.realpath(__file__))[0], file_name) excel = Excel(file_path) sheet = excel.get_sheet('hmw') # excel.workbook.ChartObject.Add() # for i in range(1, 20): # sheet.set_cell(i, i, i, color_index=i) # print(sheet.get_cell_rows()) # print(sheet.get_cell_cols()) # print(sheet.get_cell(1, 1)) sheet.set_range(1, 1, [['', 'd1', 'd2', 'd3', 'd4'], ['a', 1, 2, 3, 4], ['b', 21, 22, 23, 24], ['c', '31', '32', '33', '34'],]) # ], True, 2, 5) # print(sheet.get_cell_rows()) # print(sheet.get_cell_cols()) # print(sheet.get_range()) # sheet.merge_cells(1, 1, 2, 2, 'merge_cells') # sheet.add_hyperlink(5, 5, 'https://www.zhihu.com/people/wei_ai_lu') # sheet.add_hyperlink(6, 6, file_name, "'Sheet1'!A1") # sheet.add_picture(r'E:\Img\000001.png', 7, 1, 160, 40) del excel print('__END__')
记得之前在Python2.7环境下封装过exce相关的处理接口，由于工作原因，现在很少处理excel，而且环境变成3.5；
后来封装了些简单类似的接口，不过透视表函数调用等模块现在没用到，就没有封装了。
