Due to the COVID19 pandemic my son has to stay at home. So I give him some math quiz compiled by myself for practice. Like addition, substraction, etc. I used MS Excel for the layout and print and I used Python to generate some quiz as well. Each time I generate the quiz I had to manually import in Excel to keep the format and layout in order to fill in one print page. But I was tired with that so I searched online to find the new way.
Unfortunately there is no one module can manipulate the xlsx directly. I got some ideas and decided to copy from template and update the value in new file and try to print it automatically.
import openpyxl
from openpyxl import load_workbook, Workbook
infile = load_workbook('../tmp/tmpl.xlsx')
outfile = Workbook()
new_sheet = outfile.active
default_sheet = infile.active
from copy import copy
for row in default_sheet.rows:
for cell in row:
#new_cell = new_sheet.cell(row=cell.row, column=cell.col_idx,
new_cell = new_sheet.cell(row=cell.row, column=cell.column,
value= cell.value)
if cell.has_style:
new_cell.font = copy(cell.font)
new_cell.border = copy(cell.border)
new_cell.fill = copy(cell.fill)
new_cell.number_format = copy(cell.number_format)
new_cell.protection = copy(cell.protection)
new_cell.alignment = copy(cell.alignment)
# copy the height and width
for rowno in range(1, 13):
new_sheet.row_dimensions[rowno].height = default_sheet.row_dimensions[rowno].height
for col in 'ABCDE':
new_sheet.column_dimensions[col].width = default_sheet.column_dimensions[col].width
# the merged file was not copied
new_sheet.merge_cells('A1:E1')
# save to new file
outfile.save('../tmp/out.xlsx')The code is a simple update from Stackoverflow, based on Charlie’s answer
I’m trying to print on Windows 10 so to code is:
import win32api
import win32print
win32api.ShellExecute (
0,
"print",
r"C:\fc\local\tmp\out.xlsx",
'/d:"%s"' % win32print.GetDefaultPrinter (),
".",
0
)This is from this link
For Linux platform you can find at this stackoverflow page, at the answer from Anuj