python代码-linux系统读取excel模版转化成图片

windows系统excel转换成图片

用python仅仅限于windows平台的包

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import win32com.client
from PIL import ImageGrab
import logging

logging.basicConfig(level=logging.DEBUG,format=LOG_FORMAT,datefmt=DATE_FORMAT,filename='/py_pj/esc_hour.log',filemode='a')

def save_pic(excel_file,sheet_name,pic_file,excel_rg):
    w = win32com.client.Dispatch('Excel.Application')
    wb = w.Workbooks.Open(excel_file)
    ws = wb.Worksheets(sheet_name)
    ws.Range(excel_rg).CopyPicture(1,2)
    im = ImageGrab.grabclipboard()
    im.save(pic_file,"png")
    wb.Save()
    wb.Close()
    logging.info("====图片保存成功")

后面需要在linux服务器上调度，继而需要找等替代win32com的包

win32com, xlwings,xlsxwriter,DataNitro,pandas,openpyxl,xlutils都只能在windows或者Mac系统

linux上实现excel转换成图片

后来发现aspose的产品，不依赖操作系统能够在linux上运行且不需要安装Excel程序就能够将excel转换成图片，它不仅能够将excel转换成图片，各种文件类型之间都能够想换转换，官方文档链接如下：

https://reference.aspose.com/

如下为linux下读取excel模版转换成图片的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
#coding=utf-8
import time
import pandas as pd
import datetime
import pymysql
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
import shutil
import requests
import json
import logging
import jpype
import asposecells
jpype.startJVM()
from asposecells.api import LoadOptions,IndividualFontConfigs,Workbook,ImageOrPrintOptions,SheetRender,ImageType
from PIL import Image
from come.util import DBUtilN, SendMessageInWetchat, FileDeal


LOG_FORMAT = "%(asctime)s - %(levelname)s - %(message)s"
DATE_FORMAT = "%Y-%m-%d %A %H:%M:%S"

# logging.basicConfig(level=logging.DEBUG,format=LOG_FORMAT,datefmt=DATE_FORMAT)

# logging.basicConfig(level=logging.DEBUG,format=LOG_FORMAT,datefmt=DATE_FORMAT,filename= r'D:\auto_file\EF\EF-DAILY\moban\efdaily.log', filemode = 'a')

logging.basicConfig(level=logging.DEBUG,format=LOG_FORMAT,datefmt=DATE_FORMAT,filename= r'./py_pj/USED_SYS_MAIL/log/esc_hour.log', filemode = 'a')

day_date = (datetime.datetime.now()).strftime('%Y%m%d')

class daily_report():
        def __init__(self,con):
                self.con=con
                #ef应用配置


                self.Secret = 'XXXXXX'
                self.agentid = XXXXX  # esc 应用ID
                self.toparty = "1"  # esc 信息接收用户组  9

                self.path_moban = r'./auto_file/eSC/eSC-DAILY-DAY/moban' #模板模块路径
                self.path_l = r'./auto_file/eSC/eSC-DAILY-DAY/use'   #应用操作模块
                self.path_bak = r'./auto_file/eSC/eSC-DAILY-DAY/bak' #备份模块
                self.excel_name="daily_report_esc_byday"        #生成excel文件名称
                self.exel_moban=self.path_moban +"/"+self.excel_name + ".xlsx"  #模板文件名称
                self.filename_today = self.path_l +"/"+ self.excel_name + "_" + day_date + ".xlsx"   #应用文件
                #pic1
                # self.picname = self.path_l + "\\" + 'order.png'  # 保存图片名称
                self.picname = self.path_l + "/" + 'pic1.jpeg'  # 保存图片名称
                self.sheet_name = 'Summary'  # 截图Excel sheet名称 数据页sheet
                # self.range_excel_tab = 'F1:O46'  # 定位截图位置
                self.range_excel_tab = 'F1:L47'  # 定位截图位置

                self.sheet_detail = 'eSC_RawDATA'  # 截图Excel sheet名称 明细页sheet名称

                #pic2
                # self.picname1 = self.path_l + "\\" + 'pic2.png'  # 保存图片名称
                # self.sheet_name1 = 'outbound_seller'  # 截图Excel sheet名称 数据页sheet
                # self.range_excel_tab1 = 'D1:Z59'  # 定位截图位置
                # self.sheet_detail1 = 'data2'  # 截图Excel sheet名称 明细页sheet名称

                # self.sql_str="select time_solt,warehouse_code,cnt,flag,etl_time  from  t_outbound_wecom"  # 查询sql
                # self.pd_sql = """select count(1) num  from t_outbound_wecom where etl_time>=date_format(now(),'%Y-%m-%d %H:00:00');"""  #判断底层etl 是否延迟sql

                self.sql_str = "SELECT `platform_code`,`on_offshore`,`type`,`country`,`hour`,`so_date`,`so_qty`,`total_cost`,`etl_time` FROM ads.ads_esc_t_push_so_tracking_revenue"  # 查询sql
                # self.pd_sql = """select count(1) num  from datamart.t_isc_push_OneSales_order"""  # 判断底层etl 是否延迟sql
                self.pd_sql = """select count(1) num  from ads.ads_esc_t_push_so_tracking_revenue where etl_time>=date_format(now(),'%Y-%m-%d %H:00:00');"""  # 判断底层etl 是否延迟sql
                self.times=1  #重复扫描15次 预估15分钟

                #pic2
                # self.sql_str1 = "select time_solt,warehouse_code,seller_ocid,cnt,flag,etl_time from t_outbound_wecom_vip"  # 查询sql

        #复制模板  从原始位置到新位置
        def copyfile(self):
                # logging.info("copyfile"+"  "+self.excel_moban+"  "+self.filename_today)
                shutil.copyfile(self.exel_moban, self.filename_today)
                logging.info("===模板备份完成===")

        # 将文件移到其他文件夹
        def move_file(self):
                FileDeal.fileDeal(self.path_l).remove_file(self.path_bak)
                logging.info("====文件移动成功====")


        def get_token(self,Secret,CorpID='XXXXX'):
                tagurl = 'https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=' + CorpID + '&corpsecret=' + Secret
                f = requests.get(tagurl)
                resultJson = json.loads(f.text)  # 加载口令json
                token = resultJson['access_token']  # 获取口令
                return token

        def send_message(self,token,data):
                send_url = "https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=" + token
                requests.post(url=send_url, data=json.dumps(data))  # 提交请求
                logging.info("====信息推送成功====")
                # data = {"toparty": "5", "msgtype": "image", "agentid": 1000003, "image": {"media_id": mediaid}, "safe": 0}


        def get_pic_media(self,token,file_name,file_type='image'):
                upload_url = 'https://qyapi.weixin.qq.com/cgi-bin/media/upload?access_token=' + token + '&type=' + file_type +'&debug=1'
                print(upload_url)
                data = {"media": open(file_name, 'rb')}
                print(file_name)
                print(data)
                r = requests.post(url=upload_url, files=data)
                print(r.text)
                resp = json.loads(r.text)  # 加载json
                return resp['media_id']

        def excel_to_pic(self,excel_file,pic_file,excel_rg):
                print('excel_file:'+excel_file+'\n'+'pic_file:'+pic_file+'\n'+'excel_rg:'+excel_rg+'\n')
                loadOptions = LoadOptions()
                configs = IndividualFontConfigs()
                configs.setFontFolder("/data/fonts/chinese", True)
                loadOptions.setFontConfigs(configs)
                workbook = Workbook(excel_file, loadOptions)
                workbook.calculateFormula()
                imgOptions = ImageOrPrintOptions()
                # imgOptions.setDesiredSize(desiredWidth, desiredHeight)
                imgOptions.setImageType(ImageType.JPEG)
                worksheet = workbook.getWorksheets().get(0)
                worksheet.getPageSetup().setPrintArea(excel_rg)
                worksheet.getPageSetup().setLeftMargin(0)
                worksheet.getPageSetup().setRightMargin(0)
                worksheet.getPageSetup().setTopMargin(1)
                worksheet.getPageSetup().setBottomMargin(0)
                print(worksheet)
                sheetRender = SheetRender(worksheet, imgOptions)
                with open(pic_file, "wb") as w:
                        content = sheetRender.toImageBytes(0)
                        w.write(content)
                jpype.shutdownJVM()

        def edit_pic(self,pic_file):
                print('图片保存路径为>>>'+pic_file)
                img = Image.open(pic_file)
                width, height = img.size
                print(width, height)
                box = (0, 28, 800, 980)
                region = img.crop(box)
                region.save(pic_file)
                print('图片处理成功')


        def push_data(self,ws, df, r_x, c_x):
                rows = dataframe_to_rows(df, index=False)
                for r_idx, row in enumerate(rows, 1):
                        for c_idx, value in enumerate(row, 1):
                                ws.cell(row=r_idx + r_x, column=c_idx + c_x, value=value)


        def insert_date_excel(self,sql,sheet_name,startx=0,starty=0):
                print('insert_date_excel>>>'+sql+'\n'+'sheet_name>>>'+sheet_name)
                print('filename_today>>>'+self.filename_today)
                df = pd.read_sql(sql, self.con)
                print(df)
                wb = openpyxl.load_workbook(self.filename_today)
                ws2 = wb[sheet_name]
                self.push_data(ws2, df, startx, starty)
                wb.save(self.filename_today)

        #重复刷新考虑etl是否运行完成
        def get_sub_condition(self):
                res=0
                for i in range(self.times):
                        ss = DBUtilN.exeSelectSql(self.pd_sql, self.con)
                        # print(ss)
                        if ss[0][0] !=0:
                                res=1
                                break
                        else:
                                time.sleep(1)
                                print("====等待===")
                return res

        def cal_process(self):
                #判断底层etl是否处理完成
                res=self.get_sub_condition()
                # print("res={}".format(res))  0-1
                if res==1:
                        # 移动文件到备份文件夹
                        self.move_file()
                        # 删除备份文件夹之前文件及文件夹
                        FileDeal.fileDeal(self.path_bak).deletefile()
                        # step0 复制Excel模板
                        self.copyfile()
                        self.tuisong(self.sql_str,self.sheet_detail,self.sheet_name,self.picname,self.range_excel_tab)
                        print('最终推送图片路径为>>>'+self.picname)
                        logging.info("===按esc数据发送完成===")
                        # self.tuisong(self.sql_str1, self.sheet_detail1, self.sheet_name1, self.picname1, self.range_excel_tab1)
                        # logging.info("===按seller分析数据发送完成===")

                        print("===推送完成===")

                else:
                        logging.info("===day数据，底层etl 同步数据延迟，未推送===")
                        self.errorts("测试消息：==day数据，esc底层etl同步延迟，请上线查看")

        def errorts(self,msg):
                # 报警应用
                SendMessageInWetchat.send_txt_msg(msg,toparty=self.toparty1,agentid=self.agentid1,Secret=self.Secret1)

        def tuisong(self,sql,sheet_detail,sheet_name,picname,range_excel_tab):
                # step1 将数据插入Excel
                self.insert_date_excel(sql, sheet_detail)

                # sep2 将Excel数据保存为图片
                self.excel_to_pic(self.filename_today, picname, range_excel_tab)

                # sep3 保存图片并裁剪
                self.edit_pic(self.picname)

                # 获取token
                print('开始获取token')
                token = self.get_token(self.Secret)
                print('获取token成功')
                # 获取mediaid
                mediaid = self.get_pic_media(token, picname)
                print('获取mediaid成功')
                # 组装data
                data = {"toparty": self.toparty, "msgtype": "image", "agentid": self.agentid, "image": {"media_id": mediaid},"safe": 0}
                # step3 发送内容
                self.send_message(token, data)
                 # 移动文���到备份文件夹
                 # self.move_file()
                 # logging.info("===单次推送完成===")

if __name__ == "__main__":

        mysqlCon =  pymysql.connect(database='XXX',
                        host='XXXX',
                        user='XXX',
                        password='XXXX',
                        port=9030,
                        charset='utf8mb4',
                        connect_timeout=120)
        tt = daily_report(mysqlCon)
        try:
                tt.cal_process()
        except Exception as e:
                error = str(e)
                tt.errorts("测试消息：esc每日数据异常，请上线查看:" + error)
                logging.info("esc每日数据异常：{}".format(e))

        mysqlCon.close()

Aspose Cells Excel模板转换PDF/图片Linux中文乱码解决

第一步：把 C:\Windows\Fonts目录下字体拷贝到Linux( /usr/share/fonts/chinese)目录下，Linux目录可随意指定

第二步：在Aspose加载WorkBook时配置字体路径

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
##Java 
IndividualFontConfigs configs = new IndividualFontConfigs();
     configs.setFontFolder("/usr/share/fonts/chinese", true);
     LoadOptions loadOptions  = new LoadOptions();
     loadOptions.setFontConfigs(configs);
 FileInputStream fi = new FileInputStream("D://MOBAN.xlsx");
 Workbook wb = new Workbook(fi, loadOptions);

##Python
loadOptions = LoadOptions()
             configs = IndividualFontConfigs()
             configs.setFontFolder("/data/fonts/chinese", True)
             loadOptions.setFontConfigs(configs)
             workbook = Workbook(excel_file, loadOptions)

Aspose Cells 读取不到Excel模板包含公式的数据

workbook.calculateFormula()

增加这一行代码即可

Aspose Cells 设置PDF或图片边框大小

1
2
3
4
5
6
worksheet = workbook.getWorksheets().get(0)
worksheet.getPageSetup().setPrintArea(excel_rg)
worksheet.getPageSetup().setLeftMargin(0)
worksheet.getPageSetup().setRightMargin(0)
worksheet.getPageSetup().setTopMargin(1)
worksheet.getPageSetup().setBottomMargin(0)

Aspose Cells 读取指定excel位置的数据

1
worksheet.getPageSetup().setPrintArea(excel_rg)

觉得不错的话，支持一根棒棒糖吧 ୧(๑•̀⌄•́๑)૭



wechat pay



alipay

Python
#Python
python代码-linux系统读取excel模版转化成图片
http://yuting0907.github.io/posts/f1574eb6.html
作者
Echo Yu
发布于
2022年12月2日
许可协议