Qypt5 Excel 转 PDF 连接RabbitMQ自动打印
CyberSicko
hava a nice day.
介绍
自动打印程序，Server端产生单子，Excel格式，将文件地址根据组发送到rabbitmq队列，程序订阅rabbitmq，抓取文件地址下载并调用打印机打印。
这里的订阅分组，每个组一个routing-key，程序可以自由订阅N个组。
Python实现，GUI框架为Pyqt5。
连接打印机
程序关闭会最小化到托盘，同时托盘拥有选择默认打印机功能。
class TrayIcon(QSystemTrayIcon):
def __init__(self, parent=None, ):
super(TrayIcon, self).__init__(parent)
self.quit_action = QAction("退出", self, triggered=self.quit)
self.default_printer = QAction('当前打印机:' + DEFAULT_PRINTER, self)
self.refresh_action = QAction('刷新')
self.refresh_action.triggered.connect(parent.init_data)
self.printer_menus = QMenu()
self.menu = QMenu()
self.setToolTip("监控打印程序")
self.show_menu()
self.other()
def show_menu(self):
" 设计托盘的菜单，这里我实现了一个二级菜单 "
print(DEFAULT_PRINTER)
self.menu.addAction(self.default_printer)
self.menu.addAction(self.refresh_action)
self.menu.addSeparator()
self.menu.addMenu(self.printer_menus, )
self.menu.addAction(self.quit_action)
self.printer_menus.setTitle("打印机列表")
self.get_all_printer_actions()
self.setContextMenu(self.menu)
def get_all_printer_actions(self):
for item in PRINTERS:
action = QAction(item, self)
action.triggered.connect(lambda: self.set_use_printer(self.sender()))
if item == DEFAULT_PRINTER:
action.setCheckable(True)
action.setChecked(True)
self.printer_menus.addAction(action)
# self.printer_menus.addActions(printers)
def set_use_printer(self, action):
for item in self.printer_menus.actions():
item.setCheckable(False)
item.setChecked(False)
action.setCheckable(True)
action.setChecked(True)
win32print.SetDefaultPrinterW(action.text())
self.default_printer.setText('当前打印机:' + action.text())
self.showMessage("信息", "当前打印机为:" + action.text(), self.icon)
def other(self):
self.activated.connect(self.iconClied)
# 把鼠标点击图标的信号和槽连接
# self.messageClicked.connect(self.mClied)
# 把鼠标点击弹出消息的信号和槽连接
self.setIcon(QIcon(":/images/logo.ico"))
self.icon = self.MessageIcon()
# 设置图标
def iconClied(self, reason):
"鼠标点击icon传递的信号会带有一个整形的值，1是表示单击右键，2是双击，3是单击左键，4是用鼠标中键点击"
if reason == 2 or reason == 3:
pw = self.parent()
if pw.isVisible():
pw.hide()
else:
pw.show()
print(reason)
def quit(self):
"保险起见，为了完整的退出"
self.setVisible(False)
self.parent().exit()
qApp.quit()
sys.exit()
程序运行会获取所有本地打印机：
if file.tryLock(): # 防止多开
for it in win32print.EnumPrinters(2):
PRINTERS.append(it[2])
app.setQuitOnLastWindowClosed(False)
w = window()
RabbitMQ 管理
RabbitMQ 订阅，取消订阅，文件下载，都在这了。
import logging
import os
import shutil
import threading
import time
from urllib import request
import pika
import win32api
import win32print
import xlwings as xw
logging.basicConfig(level=logging.DEBUG, # 控制台打印的日志级别
filename='./printer.log',
filemode='a',
# a是追加模式，默认如果不写的话，就是追加模式
format=
'%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'
# 日志格式
)
import pythoncom
EXCHANGE_NAME = 'exchange-printOrder'
SAVE_PATH = os.path.abspath('.') + '\\download'
LOCK = 0
import PDF_Util
class MQ(threading.Thread):
def __init__(self, keys, window):
super().__init__()
credentials = pika.PlainCredentials('rabbitmq', 'P@ssw0rd') # mq用户名和密码，没有则需要自己创建
self.conn = pika.BlockingConnection(
pika.ConnectionParameters(host='10.86.13.83', virtual_host='/', credentials=credentials))
self.channel = self.conn.channel()
self.current_keys = keys
self.log_area = window.log_area
self.log_area.append('初始化RabbitMQ 成功')
self.window = window
self.window.statusbar.showMessage('已连接')
self.lock = False
# threading.Thread(target=self.clear_file).start()
self.clear_file()
def clear_file(self):
try:
if not os.path.exists(SAVE_PATH):
os.mkdir(SAVE_PATH)
else:
shutil.rmtree(SAVE_PATH)
os.mkdir(SAVE_PATH)
logging.info('清空文件')
print('clear')
except PermissionError as p:
print('文件被占用,wait')
logging.info('文件被占用,wait')
pass
def refresh(self, key, flag):
if flag == 0:
self.channel.queue_unbind(self.queue_name, exchange=EXCHANGE_NAME, routing_key=key)
print('解绑:', key)
logging.info('解绑:' + key)
self.log_area.append('解绑:' + key + '成功')
elif flag == 1:
self.channel.queue_bind(self.queue_name, exchange=EXCHANGE_NAME, routing_key=key)
logging.info('绑定:' + key)
print('绑定:', key)
self.log_area.append('绑定:' + key + '成功')
else:
pass
pass
def call_back(self, ch, method, properties, body):
print(ch, method, properties)
self.lock = True
logging.info('收到来自: %s 的消息: %s' % (method.routing_key, body))
logging.info('下载来自: %s 的文件: %s' % (method.routing_key, body))
self.log_area.append('下载来自: %s 的文件: %s' % (method.routing_key, body))
if not os.path.exists(SAVE_PATH):
os.makedirs(SAVE_PATH)
self.download(str(body)[2:][0:-1])
def get_file_name(self, url: str):
return url[url.rindex('/') + 1:]
def download(self, URL):
try:
req = request.Request(URL)
res = request.urlopen(url=req, timeout=5)
get_img = res.read()
with open(SAVE_PATH + '\\' + self.get_file_name(URL), 'wb') as f:
f.write(get_img)
f.close()
self.window.ti.showMessage('提示', "打印: " + self.get_file_name(URL), self.window.ti.icon)
self.log_area.append('文件: %s 下载成功，开始打印' % (self.get_file_name(URL)))
logging.info('文件: %s 下载成功，开始打印' % (self.get_file_name(URL)))
currentPrinter = win32print.GetDefaultPrinterW()
# hinstance = win32api.ShellExecute(0, "print", SAVE_PATH + '\\' + self.get_file_name(URL),
# '/d:"%s"' % currentPrinter,
# None, 0)
# logging.info('打印返回值 %s' % hinstance)
# order = '{0} {1} {2}'.format(GS_PRINT, pdf, currentPrinter)
# os.system(order)
pythoncom.CoInitialize()
pdf_util = PDF_Util.PDFConverter(SAVE_PATH + '\\' + self.get_file_name(URL))
pdf_util.run_conver()
self.log_area.append('打印完成')
logging.info('打印完成')
self.lock = False
except Exception as e:
logging.error(str(e))
self.log_area.append(str(e))
def run(self):
self.channel.exchange_declare(EXCHANGE_NAME, 'topic', True)
result = self.channel.queue_declare('', exclusive=True)
self.queue_name = result.method.queue
logging.info('队列名称:{0}'.format(self.queue_name))
print('队列名称:{0}'.format(self.queue_name))
self.log_area.append('当前队列名称:' + self.queue_name)
# routing_key = 'printOrder[06-15-KIT-B-R-01]'
for key in self.current_keys:
self.channel.queue_bind(exchange=EXCHANGE_NAME, queue=self.queue_name, routing_key=key)
self.channel.basic_consume(queue=self.queue_name, on_message_callback=self.call_back, auto_ack=True)
currentPrinter = win32print.GetDefaultPrinterW()
self.log_area.append('当前默认打印机:' + currentPrinter)
self.channel.start_consuming()
if __name__ == '__main__':
mq = MQ(['printOrder[06-15-KIT-B-R-01]', 'printOrder[06-15-KIT-B-R-02]', 'printOrder[06-15-KIT-B-R-03]'])
mq.start()
mq.refresh(['printOrder[06-15-KIT-B-R-01]', 'printOrder[06-15-KIT-B-R-02]'])
我这里还做了本地持久化，会把订阅的信息存在C盘User下的.monitor文件夹的setting.ini里面，也就是程序关闭后，再次打开会自动订阅上次已订阅的组。
class Config(object):
def __init__(self):
self.conf = ConfigParser()
self.path = os.path.expanduser('~') + '\\' + '.monitor'
self.name = "setting.ini"
self.dir = self.path + '\\' + self.name
if not os.path.isdir(self.path):
os.makedirs(self.path)
if not os.path.isfile(self.dir):
fd = open(self.dir, mode="w", encoding="utf-8")
fd.close()
self.init_data()
# 初始化数据
def init_data(self):
self.conf.add_section("TOPIC")
self.conf.set("TOPIC", "DATA", json.dumps(GROUPS))
self.conf.write(open(self.dir, 'a'))
def refresh(self):
self.conf.remove_section('TOPIC')
self.conf.add_section('TOPIC')
self.conf.set("TOPIC", "DATA", json.dumps(GROUPS))
self.conf.write(open(self.dir, 'w'))
def get_groups(self):
self.conf.read(self.dir)
data = self.conf.get('TOPIC', 'DATA')
arr = json.loads(data)
return arr
打印Excel
我本以为打印Excel没什么难的，可是客户机器上的Excel版本都是2016，太老了，在打印Excel的时候是要调用Excel程序的，而Excel打印，然后还会弹出 ‘当前文件已被修改，XXXXXX’，这就很不友好，需要人去点，就做不到静默打印的效果，
这问题无解，我总不能去动Excel，最后憋急了，将Excel转PDF，再用第三方工具实现静默打印。
# -*- coding:utf-8 -*-
import logging
import os
import subprocess
import win32api
import win32print
from win32com.client import Dispatch, constants, gencache, DispatchEx
SAVE_PATH = os.path.abspath('.') + '\\download'
# GS_WIN32 = r'C:\Program Files (x86)\gs\gs9.55.0\bin\gswin32.exe'
GS_PRINT = os.path.abspath('.') + '\\PDFtoPrinter.exe'
logging.basicConfig(level=logging.DEBUG, # 控制台打印的日志级别
filename='./printer.log',
filemode='a',
# a是追加模式，默认如果不写的话，就是追加模式
format=
'%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'
# 日志格式
)
def print_pdf(export_file: str):
currentPrinter = win32print.GetDefaultPrinterW()
order = '{0} {1} {2}'.format(GS_PRINT, export_file, currentPrinter)
res = subprocess.call(order, shell=True, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
print('打印返回值{0}'.format(res))
logging.info('打印返回值{0}'.format(res))
class PDFConverter:
def __init__(self, pathname, export='.'):
self._handle_postfix = ['doc', 'docx', 'ppt', 'pptx', 'xls', 'xlsx']
self._filename_list = list()
self._export_folder = SAVE_PATH
if not os.path.exists(self._export_folder):
os.mkdir(self._export_folder)
self._enumerate_filename(pathname)
def _enumerate_filename(self, pathname):
'''
读取所有文件名
'''
full_pathname = os.path.abspath(pathname)
if os.path.isfile(full_pathname):
if self._is_legal_postfix(full_pathname):
self._filename_list.append(full_pathname)
else:
raise TypeError('文件 {} 后缀名不合法！仅支持如下文件类型：{}。'.format(pathname, '、'.join(self._handle_postfix)))
elif os.path.isdir(full_pathname):
for relpath, _, files in os.walk(full_pathname):
for name in files:
filename = os.path.join(full_pathname, relpath, name)
if self._is_legal_postfix(filename):
self._filename_list.append(os.path.join(filename))
else:
raise TypeError('文件/文件夹 {} 不存在或不合法！'.format(pathname))
def _is_legal_postfix(self, filename):
return filename.split('.')[-1].lower() in self._handle_postfix and not os.path.basename(filename).startswith(
'~')
def run_conver(self):
'''
进行批量处理，根据后缀名调用函数执行转换
'''
print('需要转换的文件数：', len(self._filename_list))
for filename in self._filename_list:
postfix = filename.split('.')[-1].lower()
funcCall = getattr(self, postfix)
print('原文件：', filename)
funcCall(filename)
print('转换完成！')
def doc(self, filename):
'''
doc 和 docx 文件转换
'''
name = os.path.basename(filename).split('.')[0] + '.pdf'
exportfile = os.path.join(self._export_folder, name)
print('保存 PDF 文件：', exportfile)
gencache.EnsureModule('{00020905-0000-0000-C000-000000000046}', 0, 8, 4)
w = Dispatch("Word.Application")
doc = w.Documents.Open(filename)
doc.ExportAsFixedFormat(exportfile, constants.wdExportFormatPDF,
Item=constants.wdExportDocumentWithMarkup,
CreateBookmarks=constants.wdExportCreateHeadingBookmarks)
w.Quit(constants.wdDoNotSaveChanges)
def docx(self, filename):
self.doc(filename)
def xls(self, filename):
'''
xls 和 xlsx 文件转换
'''
name = os.path.basename(filename).split('.')[0] + '.pdf'
exportfile = os.path.join(self._export_folder, name)
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
books = xlApp.Workbooks.Open(filename, False)
books.ExportAsFixedFormat(0, exportfile)
books.Close(False)
print('保存 PDF 文件：', exportfile)
print_pdf(exportfile)
xlApp.Quit()
def xlsx(self, filename):
self.xls(filename)
def ppt(self, filename):
'''
ppt 和 pptx 文件转换
'''
name = os.path.basename(filename).split('.')[0] + '.pdf'
exportfile = os.path.join(self._export_folder, name)
gencache.EnsureModule('{00020905-0000-0000-C000-000000000046}', 0, 8, 4)
p = Dispatch("PowerPoint.Application")
ppt = p.Presentations.Open(filename, False, False, False)
ppt.ExportAsFixedFormat(exportfile, 2, PrintRange=None)
print('保存 PDF 文件：', exportfile)
p.Quit()
def pptx(self, filename):
self.ppt(filename)
def print_pdf(export_file: str):
currentPrinter = win32print.GetDefaultPrinterW()
order = '{0} {1} {2}'.format(GS_PRINT, export_file, currentPrinter)
res = subprocess.call(order, shell=True, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
print('打印返回值{0}'.format(res))
logging.info('打印返回值{0}'.format(res))
打印PDF需要借助PDFPrinter.exe，下载地址: http://s3.z100.vip:28592/%E7%A8%8B%E5%BA%8F/AutoPrint/PDFtoPrinter.exe
评论
还没有评论