def test_printer_settings(worksheet, write_worksheet):
ws = worksheet
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
ws.print_options.horizontalCentered = True
ws.print_options.verticalCentered = True
page_setup_prop = PageSetupProperties(fitToPage=True)
ws.sheet_properties.pageSetUpPr = page_setup_prop
xml = write_worksheet(ws, None)
expected = """
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
<pageSetUpPr fitToPage="1"/>
</sheetPr>
<dimension ref="A1:A1"/>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth="10" defaultRowHeight="15"/>
<sheetData/>
<printOptions horizontalCentered="1" verticalCentered="1"/>
<pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5"/>
<pageSetup orientation="landscape" paperSize="3" fitToHeight="0" fitToWidth="1"/>
</worksheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def page_setup (self, sheet, landscape=False, fitHeight=False, fitWidth=False):
ws = self._getTable (sheet)
ws.page_setup.orientation = (ws.ORIENTATION_LANDSCAPE if landscape
else ws.ORIENTATION_PORTRAIT)
if fitHeight or fitWidth:
wsprops = ws.sheet_properties
wsprops.pageSetUpPr = PageSetupProperties (fitToPage=True)
# ws.page_setup.fitToPage = True
if not fitHeight:
ws.page_setup.fitToHeight = False
elif not fitWidth:
ws.page_setup.fitToWidth = False
def sheetProperties(self,
paper='A4',
landscape=False,
fitWidth=False,
fitHeight=False):
if landscape:
self._ws.page_setup.orientation = self._ws.ORIENTATION_LANDSCAPE
self._ws.page_setup.paperSize = getattr(self._ws, 'PAPERSIZE_' + paper)
# Property settings
if fitWidth or fitHeight:
wsprops = self._ws.sheet_properties
wsprops.pageSetUpPr = PageSetupProperties(fitToPage=True,
autoPageBreaks=False)
# self._ws.page_setup.fitToPage = True
if not fitWidth:
self._ws.page_setup.fitToWidth = False
if not fitHeight:
self._ws.page_setup.fitToHeight = False
'transitionEntry' 'tabColor' ''' ###### 页面设置属性的可用字段Available fields for page setup properties ''' 'autoPageBreadks' 'fitToPage' ''' ######## 可用的字段轮廓Available fields for outlines ''' 'applyStyles' 'summaryBelow' 'summaryRight' 'showOutlineSymbols' ''' # 默认情况下，大纲属性(outline)是初始化的，因此您可以直接修改它们的4个属性，而页面设置属性(page setup properties)没有。如果您想修改后者，您应该首先初始化一个openpyxpath .worksheet.properties。具有所需参数的PageSetupProperties对象。完成之后，如果需要，可以在以后由例程直接修改它们。 from openpyxl.workbook import Workbook from openpyxl.worksheet.properties import WorksheetProperties, PageSetupProperties wb = Workbook() ws = wb.active wsprops = ws.sheet_properties wsprops.tabColor = "1072BA" wsprops.filterMode = False wsprops.pageSetUpPr = PageSetupProperties(fitToPage=True, autoPageBreaks=False) wsprops.outlinePr.summaryBelow = False wsprops.outlinePr.applyStyles = True wsprops.pageSetUpPr.autoPageBreaks = True
def print_settings(sheet,
widths=None,
header=None,
one_page=True,
landscape=True):
"""
With specifying the sheet, this can assign the sheets
print settings to be one page, landscape/portrait,
as well as assign column sizes with the width argument,
where each item in the list should be a number,
and defines x amount of columns.
For excel, the width for each column in pixels is x*7
"""
# Allowing header arg to not be set
if header is None:
header = []
# Allowing widths arg to not be set
if widths is None:
widths = []
# Creating a list of column indices for excel. From 'A' to 'Z'
# Will expand alpha to accomodate if you need to set the width
# or header of more than 26 columns
alpha = []
m_char = 65
if len(header) > 26:
m_char = 65 + (len(header) + 26)
for letter in range(65, 91):
alpha.append(chr(letter))
for letter2 in range(65, m_char):
a = chr(letter)
b = chr(letter2)
alpha.append(a + b)
for i in range(len(widths)):
sheet.column_dimensions[alpha[i]].width = widths[i]
# Allowing header arg to not be set
if header is None:
header = []
for i in range(len(header)):
sheet[f'{alpha[i]}1'] = header[i]
main_props = sheet.sheet_properties
sheet.print_options.horizontalCentered = True
sheet.print_options.verticalCentered = True
if one_page:
main_props.pageSetUpPr = PageSetupProperties(autoPageBreaks=False,
fitToPage=True)
sheet.print_options.horizontalCentered = True
sheet.print_options.verticalCentered = True
else:
main_props.pageSetUpPr = PageSetupProperties(autoPageBreaks=False)
sheet.page_setup.fitToHeight = False
sheet.page_setup.fitToWidth = True
if landscape:
sheet.page_setup.orientation = sheet.ORIENTATION_LANDSCAPE
else:
sheet.page_setup.orientation = sheet.ORIENTATION_PORTRAIT
sheet.print_title_rows = '1:1'
def generate_expense_report(salesman,
date_range,
expense_list,
r_format='xls'):
expense_report_stream = BytesIO()
total_expenses = 0
if r_format == 'xls':
expense_report = Workbook()
main_sheet = expense_report.active
# Set up some initial styles
main_sheet.freeze_panes = 'B2'
main_sheet.row_dimensions[2].height = 32
main_sheet.row_dimensions[3].height = 22
main_sheet.row_dimensions[4].height = 22
main_sheet.column_dimensions['A'].width = 22
# Enter the name of the Salesman
main_sheet['A2'] = 'Name'
main_sheet['A2'].font = Font(name='Helvetica Neue',
size=12,
bold=True,
color='FFFFFF')
main_sheet['A2'].fill = PatternFill(start_color='004C7F',
end_color='004C7F',
fill_type='solid')
main_sheet['B2'] = str(salesman)
# Loop through the days and add the columns
date_range_it = maya.MayaInterval(
start=maya.when(date_range.split(' - ')[0]),
end=maya.when(date_range.split(' - ')[1]).add(days=1))
col_idx = 2
day_col_labels = {}
all_col_labels = []
for event in date_range_it.split(duration=timedelta(days=1)):
col_label = get_column_letter(col_idx)
day_col_labels[event.start.iso8601().split('T')[0]] = col_label
# Apply styles to the columns
main_sheet.column_dimensions[col_label].width = 16
main_sheet[col_label + '2'].font = Font(name='Helvetica Neue',
size=12,
bold=True,
color='FFFFFF')
main_sheet[col_label + '2'].fill = PatternFill(
start_color='004C7F', end_color='004C7F', fill_type='solid')
main_sheet[col_label + '3'].font = Font(name='Helvetica Neue',
size=11,
bold=True,
color='FFFFFF')
main_sheet[col_label + '3'].fill = PatternFill(
start_color='2F7115', end_color='2F7115', fill_type='solid')
main_sheet[col_label + '3'] = \
event.start.iso8601().split('T')[0]
all_col_labels.append(col_label)
col_idx += 1
last_col_label = get_column_letter(col_idx)
all_col_labels.append(last_col_label)
# Set the date range in the sheet
main_sheet[all_col_labels[-2] + '2'] = 'Date Range'
main_sheet.column_dimensions[last_col_label].width = 16
main_sheet[last_col_label + '2'] = date_range
main_sheet[last_col_label + '2'].alignment = Alignment(wrap_text=True)
main_sheet[last_col_label + '2'].font = Font(name='Helvetica Neue',
size=12,
bold=True,
color='FFFFFF')
main_sheet[last_col_label + '2'].fill = PatternFill(
start_color='004C7F', end_color='004C7F', fill_type='solid')
main_sheet[last_col_label + '3'] = '(Total)'
main_sheet[last_col_label + '3'].font = Font(name='Helvetica Neue',
size=11,
bold=True,
color='FFFFFF')
main_sheet[last_col_label + '3'].fill = PatternFill(
start_color='2F7115', end_color='2F7115', fill_type='solid')
# Loop through the paid by and add it as a group
cur_row = 4
for paid_by in expense_list.keys():
# Set the header for cash expenses
main_sheet['A%s' % cur_row] = paid_by
main_sheet['A%s' % cur_row].font = Font(name='Helvetica Neue',
size=10,
bold=True,
color='FFFFFF')
main_sheet['A%s' % cur_row].fill = PatternFill(
start_color='004C7F', end_color='004C7F', fill_type='solid')
for col_label in all_col_labels:
main_sheet[col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True, color='FFFFFF')
main_sheet[col_label + str(cur_row)].fill = PatternFill(
start_color='004C7F',
end_color='004C7F',
fill_type='solid')
cur_row += 1
# Add all the cash expenses here
for expense_type, amounts in expense_list[paid_by].items():
if expense_type != 'total':
main_sheet['A%s' % cur_row] = expense_type
main_sheet['A%s' % cur_row].font = Font(
name='Helvetica Neue', size=10, bold=True)
main_sheet['A%s' %
cur_row].alignment = Alignment(wrap_text=True)
line_amount = 0
for t_date, amount in amounts.items():
col_label = day_col_labels[t_date]
main_sheet[col_label + str(cur_row)] = amount
main_sheet[col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=False)
line_amount += amount
main_sheet[last_col_label + str(cur_row)] = line_amount
main_sheet[last_col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True)
cur_row += 1
# Set the trailer for cash expenses
main_sheet.row_dimensions[cur_row].height = 22
main_sheet['A%s' % cur_row] = 'Total %s Expenses' % paid_by
main_sheet['A%s' % cur_row].font = Font(name='Helvetica Neue',
size=10,
bold=True)
line_amount = 0
for t_date, amount in expense_list[paid_by]['total'].items():
col_label = day_col_labels[t_date]
main_sheet[col_label + str(cur_row)] = amount
main_sheet[col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True)
line_amount += amount
main_sheet[last_col_label + str(cur_row)] = line_amount
main_sheet[last_col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True)
total_expenses += line_amount
cur_row += 1
# Set the trailer for the whole file
main_sheet.row_dimensions[cur_row].height = 22
main_sheet[all_col_labels[-2] + str(cur_row)] = 'Total:'
main_sheet[all_col_labels[-2] + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True)
main_sheet[last_col_label + str(cur_row)] = total_expenses
main_sheet[last_col_label + str(cur_row)].font = Font(
name='Helvetica Neue', size=10, bold=True)
# Finally create a table for this
wsprops = main_sheet.sheet_properties
wsprops.pageSetUpPr = PageSetupProperties(fitToPage=True,
autoPageBreaks=False)
# Create the report and create the django response from it
expense_report.save(expense_report_stream)
elif r_format == 'pdf':
expense_report = SimpleDocTemplate(expense_report_stream,
rightMargin=72,
leftMargin=72,
topMargin=30,
bottomMargin=72,
pagesize=landscape(A4))
# Loop through the days and add the columns
date_range_it = maya.MayaInterval(
start=maya.when(date_range.split(' - ')[0]),
end=maya.when(date_range.split(' - ')[1]).add(days=1))
report_days = []
for event in date_range_it.split(duration=timedelta(days=1)):
report_days.append(event.start.iso8601().split('T')[0])
total_columns = len(report_days) + 1
report_data = [['Name', str(salesman)] +
[''] * (total_columns - 3) + ['Date Range', date_range],
[''] + report_days + ['(Total)']]
report_style = [
('INNERGRID', (0, 0), (-1, -1), 0.5, colors.black),
('BOX', (0, 0), (-1, -1), 0.5, colors.black),
('FONTSIZE', (0, 0), (-1, -1), 8),
('BACKGROUND', (0, 0), (total_columns, 0), '#004C7F'),
('FONTNAME', (0, 0), (total_columns, 0), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (total_columns, 0), 10),
('TEXTCOLOR', (0, 0), (total_columns, 0), colors.white),
('BACKGROUND', (1, 1), (total_columns, 1), '#2F7115'),
('FONTNAME', (1, 1), (total_columns, 1), 'Helvetica-Bold'),
('FONTSIZE', (1, 1), (total_columns, 1), 9),
('TEXTCOLOR', (1, 1), (total_columns, 1), colors.white)
]
cur_row = 2
for paid_by in expense_list.keys():
# Add the header for this paid_by
report_data.append([paid_by] + [''] * total_columns)
report_style.extend([('BACKGROUND', (0, cur_row),
(total_columns, cur_row), '#004C7F'),
('FONTNAME', (0, cur_row),
(total_columns, cur_row), 'Helvetica-Bold'),
('FONTSIZE', (0, cur_row), (total_columns,
cur_row), 8),
('TEXTCOLOR', (0, cur_row),
(total_columns, cur_row), colors.white)])
cur_row += 1
# Add all the expenses for this paid_by
for expense_type, amounts in expense_list[paid_by].items():
if expense_type != 'total':
line = [expense_type]
line_amount = 0
for day in report_days:
line.append(amounts.get(day, ''))
line_amount += amounts.get(day, 0)
line.append(line_amount)
report_data.append(line)
report_style.extend([
('FONTNAME', (0, cur_row), (0, cur_row),
'Helvetica-Bold'),
('FONTNAME', (total_columns, cur_row),
(total_columns, cur_row), 'Helvetica-Bold'),
])
cur_row += 1
# Add the trailer for this paid_by
pd_trailer = ['Total %s Expenses' % paid_by]
line_totals = 0
for day in report_days:
pd_trailer.append(expense_list[paid_by]['total'].get(day, ''))
line_totals += expense_list[paid_by]['total'].get(day, 0)
pd_trailer.append(line_totals)
report_data.append(pd_trailer)
report_style.append(('FONTNAME', (0, cur_row),
(total_columns, cur_row), 'Helvetica-Bold'))
total_expenses += line_totals
cur_row += 1
# Add the report trailer
report_data.append([''] * (total_columns - 1) +
['Total:', total_expenses])
report_style.append(('FONTNAME', (0, cur_row),
(total_columns, cur_row), 'Helvetica-Bold'))
# Create the table and Set the style
report_table = Table(report_data)
report_table.setStyle(TableStyle(report_style))
expense_report.build([report_table])
return expense_report_stream.getvalue()
def add_ws(line: int, df: DataFrame, wb: Workbook):
df = df.iloc[line]
ws = wb.create_sheet()
ws.column_dimensions["A"].width = 40
ws.column_dimensions["B"].width = 27
ws.page_setup.fitToHeight = 1
ws.page_setup.fitToWidth = 1
wsprops = ws.sheet_properties
wsprops.pageSetUpPr = PageSetupProperties(fitToPage=True, autoPageBreaks=False)
ws.print_options = PrintOptions(horizontalCentered=True, gridLines=True)
skola = df.loc["skola"].split("(")[0]
cell = ws["A1"]
cell.value = skola
cell.font = Font(size=36)
ws.append([""])
osoba = df.pop(
"Vaše jméno a příjmení (kontaktní osoba pro účely této objednávky)"
)
telefon = df.pop(
"Vaše telefonní číslo (kontaktní osoba pro účely této objednávky)"
)
email = df.pop("Váš e-mail (kontaktní osoba pro účely této objednávky)")
poznamka = df.pop("Jakékoliv další poznámky k objednávce či dopravě")
cislo = df.pop("Číslo popisné")
ulice = df.pop("Ulice")
psc = df.pop("PSČ")
obec = df.pop(
"Obec (název obce nebo části obce případně městská část nebo městský obvod)"
)
ws.append([ulice, cislo])
ws.append([psc, obec])
ws.append(["Kontaktní osoba: ", osoba])
ws.append(["Tel.: ", telefon])
ws.append(["E-mail: ", email])
ws.append([""])
cell = ws["A8"]
cell.value = poznamka
cell.alignment = Alignment(wrap_text=True)
ws.merge_cells('A8:C8')
rd = ws.row_dimensions[8]
rd.height = 30
ws.append([""])
ws.append(["Název", "Autor", "ks"])
celkem = 0
for n in range(len(df)):
try:
ks = int(df[n])
except ValueError:
continue
except TypeError:
continue
celkem += ks
name = df.axes[0][n]
if "; " in name:
name = name.split("; ")[1]
author, book = name.split(": ", 1)
else:
book = name
author = ""
ws.append([book, author, ks])
ws.append([""])
ws.append(["", "CELKEM KS", celkem])