【xlwings api语言参考】Range.NumberFormat 属性

from sqlalchemy import create_engine from win32com.client import Dispatch, DispatchEx import pythoncom from PIL import ImageGrab, Image import uuid import pandas as pd import os import xlwings as xw class Excel: def __init__(self, engine): self.engine = engine # 查询数据库，新建excel保存 def save_excel(self, sql, savename, sheetname): # 查询简称 df = pd.read_sql(sql, self.engine, index_col='id') # 校验地址是否已存在 if not os.path.exists(os.path.dirname(savename)): os.makedirs(os.path.dirname(savename)) app = xw.App(visible=False, add_book=False) if os.path.exists(savename): workbook = app.books.open(savename) print('存在文件，打开文件！！') else: workbook = app.books.add() print('不存在文件，创建文件！！') sheet_names = [j.name for j in workbook.sheets] # 获取打开的工作簿中所有工作表的名称 if sheetname not in sheet_names: worksheet = workbook.sheets.add(sheetname) else: workbook.sheets.add('a') workbook.sheets[sheetname].delete() worksheet = workbook.sheets.add(sheetname) workbook.sheets['a'].delete() if 'Sheet1' in sheet_names: workbook.sheets['Sheet1'].delete() worksheet.range('a:az').api.NumberFormat = '@' worksheet.range('A1').value = df # worksheet['A1'].value = '序号' worksheet.range('a:az').api.font.size = 11 worksheet.range('a:az').api.font.name = "微软雅黑" worksheet.range('a:az').api.NumberFormat = '@' worksheet.autofit(axis='columns') worksheet.range('a:az').row_height = 24 worksheet.range('a:az').api.HorizontalAlignment = -4108 worksheet.range('a:az').api.VerticalAlignment = -4108 workbook.save(savename) workbook.close() app.kill() print("%s已完成" % savename) # 将数据插入excel指定行和列 def insert_excel(self, sql, filename, sheetname, range, savename): app = xw.App(visible=False, add_book=False) wb = app.books.open(filename) ws = wb.sheets[sheetname] df = pd.read_sql(sql, self.engine, index_col=None, parse_dates=None) df.index += 1 ws.range(range).value = df ws[range].value = '序号' # 校验地址是否已存在 if not os.path.exists(os.path.dirname(savename)): os.makedirs(os.path.dirname(savename)) if os.path.exists(savename): os.remove(savename) wb.save(savename) wb.close() app.kill() print("%s已完成" % savename) # excel截图 def excel_catch_screen(self, filename, sheetname, screen_area, img_name): if os.path.exists(img_name + ".PNG"): os.remove(img_name + ".PNG") # 对excel的表格区域进行截图——用例：excel_catch_screen(ur"D:\Desktop\123.xlsx", "Sheet1", "A1:J10") excel = DispatchEx("ket.Application") # 启动excel excel.Visible = True # 可视化 excel.DisplayAlerts = False # 是否显示警告 wb = excel.Workbooks.Open(filename) # 打开excel ws = wb.Sheets(sheetname) # 选择sheet ws.Range(screen_area).CopyPicture() # 复制图片区域 img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据 img.save(img_name + ".PNG") # 保存图片 wb.Close(SaveChanges=0) # 关闭工作薄，不保存 excel.Quit() # 退出excel del excel print("%s.png已完成" % img_name)