def test_write(self, Chartsheet):
sheetview = ChartsheetView(tabSelected=True,
zoomScale=80,
workbookViewId=0,
zoomToFit=True)
chartsheetViews = ChartsheetViewList(sheetView=[sheetview])
pageMargins = PageMargins(left=0.7,
right=0.7,
top=0.75,
bottom=0.75,
header=0.3,
footer=0.3)
drawing = Drawing("rId1")
item = Chartsheet(sheetViews=chartsheetViews,
pageMargins=pageMargins,
drawing=drawing)
expected = """
<chartsheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetViews>
<sheetView tabSelected="1" zoomScale="80" workbookViewId="0" zoomToFit="1"/>
</sheetViews>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
<drawing r:id="rId1"/>
</chartsheet>
"""
xml = tostring(item.to_tree())
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write(self, CustomChartsheetViews):
from ..custom import CustomChartsheetView
pageMargins = PageMargins(left=0.2362204724409449,
right=0.2362204724409449,
top=0.7480314960629921,
bottom=0.7480314960629921,
header=0.3149606299212598,
footer=0.3149606299212598)
customChartsheetView = CustomChartsheetView(
guid="{C43F44F8-8CE9-4A07-A9A9-0646C7C6B826}",
scale=88,
zoomToFit=1,
pageMargins=pageMargins)
customChartsheetViews = CustomChartsheetViews(
customSheetView=[customChartsheetView])
expected = """
<customSheetViews>
<customSheetView guid="{C43F44F8-8CE9-4A07-A9A9-0646C7C6B826}" scale="88" state="visible" zoomToFit="1">
<pageMargins left="0.2362204724409449" right="0.2362204724409449" top="0.7480314960629921" bottom="0.7480314960629921" header="0.3149606299212598" footer="0.3149606299212598" />
</customSheetView>
</customSheetViews>
"""
xml = tostring(customChartsheetViews.to_tree())
diff = compare_xml(xml, expected)
assert diff is None, diff
def create_com(self):
self.ws_com = self.wb.create_sheet('商务标', 0)
col_titles = ['序号', '内容', '页码']
content = [['一', '同类物资出口业绩一览表及报关单'], ['二', '向受援国出口货物业绩一览表及报关单']]
col_width = [10, 60, 10]
col_num = 3
row_num = 4
# # 创建专用样式
# special_alignment = Alignment(
# horizontal='left',
# vertical='center',
# wrap_text=True,
# indent=0)
# special_font = Font(name='仿宋_GB2312', size=12)
# 初始化表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_com.cell(row=i + 1, column=j + 1)
self.ws_com.row_dimensions[i + 1].height = 45 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
else: # 其余
cell_now.font = Content.normal_font
if i != row_num - 1:
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2][1]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = content[i - 2][0]
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_com.column_dimensions[letters[i]].width = col_width[i]
# 填写抬头
self.ws_com.merge_cells('A1:C1')
header = self.ws_com['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_com.row_dimensions[1].height = 50
# 打印设置
self.ws_com.print_options.horizontalCentered = True
self.ws_com.print_area = 'A1:C{}'.format(row_num)
self.ws_com.page_setup.fitToWidth = 1
self.ws_com.page_margins = PageMargins(top=0.5,
bottom=0.5,
header=0.1,
footer=0.1)
def _finalize_styling(self, sheet: Worksheet):
sheet.page_setup.paperSize = Worksheet.PAPERSIZE_A4
sheet.sheet_properties.pageSetUpPr.fitToPage = True
sheet.page_margins = PageMargins(left=self.excel_config.MARGIN_LENGTH, right=self.excel_config.MARGIN_LENGTH)
sheet.column_dimensions[self.__LEFT_COLUMNS[0]].width = 4
sheet.column_dimensions[self.__LEFT_COLUMNS[1]].width = 42
sheet.column_dimensions[self.__RIGHT_COLUMNS[0]].width = 4
sheet.column_dimensions[self.__RIGHT_COLUMNS[1]].width = 42
for rows in sheet.iter_rows(min_row=3):
for row in rows:
sheet.row_dimensions[row.row].height = self.excel_config.ROW_HEIGHT
def generate(self, team_list: SaarTeamList):
self._wb = load_workbook(filename="templates/Wertungsbogen_Master.xlsx")
assert isinstance(self._wb, Workbook)
master_ws = self._wb.active
version_string = master_ws["F2"].value
check_version(version_string)
i = 0
for team in team_list:
assert isinstance(team, SaarTeam)
for apparatus_f, apparatus_m in APPARATUS:
title = "{}_{}_{}".format(team.name[:10], apparatus_f[:2], apparatus_m[:2])
ws = self._wb.create_sheet(title=title)
ws_copy = WorksheetCopy(master_ws, ws)
ws_copy.copy_worksheet()
# set smaller page margins
assert isinstance(ws, Worksheet)
ws.page_margins = PageMargins(.2, .2, .75, .75, .314, .314)
# set contents
ws["F1"] = apparatus_f
ws["F18"] = apparatus_m
ws["A2"] = team.name
ws["A19"] = team.name
offset = 4
for num, gymnast in enumerate(team.get_gymnasts(SaarGymnast.FEMALE)):
row = offset + num
ws["A{}".format(row)] = num + 1
ws["B{}".format(row)] = gymnast.name
ws["C{}".format(row)] = gymnast.surname
offset = 21
for num, gymnast in enumerate(team.get_gymnasts(SaarGymnast.MALE)):
row = offset + num
ws["A{}".format(row)] = num + 1
ws["B{}".format(row)] = gymnast.name
ws["C{}".format(row)] = gymnast.surname
i += 1
self._wb.remove(self._wb["Master"])
def get_cover_page(wb, template_directory, date_end):
wb_cur = wb
ws_cur = wb_cur.create_sheet('Cover')
ht_logo = Image(template_directory + 'ht_logo.png')
ht_logo.anchor(ws_cur.cell(row=7, column=3), 'absolute')
ws_cur.add_image(ht_logo)
companies_logo = Image(template_directory + 'companies_logo.png')
companies_logo.anchor(ws_cur.cell(row=31, column=1), 'absolute')
ws_cur.add_image(companies_logo)
for c in range(1, 13):
ws_cur.column_dimensions[(dcc.get(c))].width = 8.5
c1 = ws_cur.cell(row=17, column=1, value='Consolidated Financial Results')
c1.alignment = Alignment(horizontal='center')
c1.font = Font(bold='true', size=22)
ws_cur.merge_cells(start_row=17, end_row=17, start_column=1,
end_column=12)
c1 = ws_cur.cell(row=22, column=1,
value=datetime.strftime(date_end, '%B %Y'))
c1.alignment = Alignment(horizontal='center')
c1.font = Font(bold='true', size=22)
ws_cur.merge_cells(start_row=22, end_row=22, start_column=1,
end_column=12)
ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT
ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID
ws_cur.page_setup.fitToPage = True
ws_cur.page_setup.fitToHeight = 1
ws_cur.page_setup.fitToWidth = 1
ws_cur.print_options.horizontalCentered = True
ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5,
footer=.5)
return wb_cur
def page_setup_landscape_1X1(self, ws, print_rows):
wb_new = self.wb
ws_cur = ws
ws_cur.page_setup.orientation = ws_cur.ORIENTATION_LANDSCAPE
ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID
ws_cur.page_setup.fitToPage = True
ws_cur.page_setup.fitToHeight = 1
ws_cur.page_setup.fitToWidth = 1
ws_cur.print_options.horizontalCentered = True
ws_cur.add_print_title(print_rows)
ws_cur.page_margins = PageMargins(left=.5,
right=.5,
top=.5,
bottom=.5,
footer=.5)
date_cur = datetime.strftime(
datetime.today(),
"%a {dt.month}/{dt.day}/%Y %I:%M %p".format(dt=datetime.today()))
ws_cur.oddFooter.right.text = date_cur
ws_cur.oddFooter.right.size = 8
ws_cur.oddFooter.right.font = 'Arial'
return wb_new
def create_tech(self):
"""创建技术标目录"""
self.ws_tech = self.wb.create_sheet('技术标', 0)
col_titles = ['序号', '内容', '页码']
# 存放固定内容
content = [
'技术偏离表', '物资选型部分', '供货清单（一）中各项物资选型一览表', '供货清单（一）中各项物资相关资料', '包装方案',
'运输相关文件', '物资自检验收方案', '物资第三方检验相关文件', '对外实施工作主体落实承诺书',
'物资生产企业三体系认证相关资料', '物资节能产品认证相关资料', '物资环境标志产品认证相关资料'
]
# 存放中文序号
num = [
'一', '二', '三', '四', '五', '六', '七', '八', '九', '十', '十一', '十二', '十三'
]
col_width = [10, 60, 10]
col_num = 3
# 确定行数
com_num = len(self.project.commodities)
row_num = com_num + 14
if self.project.is_cc:
row_num += 1
content.insert(9, '来华培训方案及相关材料')
if self.project.is_qa:
row_num += 1
content.insert(9, '售后服务方案及相关材料')
if self.project.is_tech:
row_num += 1
content.insert(9, '技术服务方案及相关材料')
# 创建专用样式
third_alignment = Alignment(horizontal='left',
vertical='center',
wrap_text=True,
indent=3)
third_font = Font(name='仿宋_GB2312', size=12)
# 填写抬头
self.ws_tech.merge_cells('A1:C1')
header = self.ws_tech['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_tech.row_dimensions[1].height = 50
# 初始化表格,双循环扫描先行后列扫描表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_tech.cell(row=i + 1, column=j + 1)
self.ws_tech.row_dimensions[i + 1].height = 30 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
elif 1 < i < 4: # 头两行
cell_now.font = Content.normal_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = num[i - 2]
elif i == 4 or i == 5: # 3、4行
cell_now.font = Content.normal_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2]
else:
cell_now.alignment = Content.ctr_alignment
elif 5 < i < com_num + 6: # 填写物资名称
cell_now.font = third_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = third_alignment
cell_now.value = '{}、{}'.format(
i - 5, self.project.commodities[i - 5][0])
else:
cell_now.alignment = Content.ctr_alignment
else: # 其余的一起填写
cell_now.font = Content.normal_font
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - com_num - 2]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = num[i - com_num - 4]
if i != row_num - 1:
cell_now.border = Content.normal_border
# for i in (9, 11): # 修改两处格式
# self.ws_tech.cell(row=com_num + i, column=2).font = third_font
# self.ws_tech.cell(
# row=com_num + i,
# column=2).alignment = third_alignment
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_tech.column_dimensions[letters[i]].width = col_width[i]
# 打印设置
self.ws_tech.print_options.horizontalCentered = True
self.ws_tech.print_area = 'A1:C{}'.format(row_num)
self.ws_tech.page_setup.fitToWidth = 1
self.ws_tech.page_margins = PageMargins(top=0.5,
bottom=0.5,
header=0.1,
footer=0.1)
def parse_margins(self, element):
margins = dict(element.items())
self.page_margins = PageMargins(**margins)
def parse_margins(self, element):
self.page_margins = PageMargins.from_tree(element)
def getValue(self, name=''):
_dict_ = {
1: "RTL00000",
2: "RTL0000",
3: "RTL000",
4: "RTL00",
5: "RTL0",
6: "RTL",
}
temp = int(name)
if len(name) in _dict_:
for i in range(1, 14):
for j in range(1, 6):
self.sheet.cell(
i, j
).value = _dict_[len(name)] + str(temp) + self.rutilink
temp = temp + 1
word_wrap_string = Alignment(wrapText=True,
horizontal="center",
vertical='center')
double_border_side = Side(border_style='dotted')
square_border = Border(top=double_border_side,
right=double_border_side,
bottom=double_border_side,
left=double_border_side)
self.sheet.page_margins = PageMargins(left=self.margins_tblr,
right=self.margins_tblr,
top=self.margins_tblr,
bottom=self.margins_tblr)
self.sheet.sheet_properties.pageSetUpPr.fitToPage = True
self.sheet.print_area = "A1:E13"
self.sheet.page_setup = PrintPageSetup(
worksheet=self.sheet,
orientation='portrait',
paperSize=self.sheet.PAPERSIZE_A4,
fitToHeight=1,
fitToWidth=1,
scale=100,
horizontalDpi=300,
verticalDpi=300)
self.sheet.print_options = PrintOptions(horizontalCentered=True,
verticalCentered=True)
for i in range(1, 6):
for j in range(1, 14):
self.sheet.cell(j, i).border = square_border
self.sheet.cell(j, i).font = self.fontType
self.sheet.cell(j, i).alignment = word_wrap_string
for cols in self.colsVal:
for row in self.rowVal:
self.sheet.column_dimensions[cols].width = self.value[1]
self.sheet.row_dimensions[row].height = self.value[0]
self.wb.save(QDir.homePath() + '/Desktop/' + self.filename)
self.wb.close()
def parse_margins(self, element):
self.ws.page_margins = PageMargins.from_tree(element)
def generer_registre(registre, file="registre_des_certificats.xls"):
"""
Generates a complete excel file with several sheets that represents the register
The pages can be printed and physically displayed at relevant locations in the school.
"""
wb = Workbook()
# main page
wb.active.title = ls.strings.REGISTER
# names cannot be longer than 15 characters
wb.active.column_dimensions['A'].width = 15
wb.active.row_dimensions[1].height = cm_to_points(0.5)
wb.active.row_dimensions[2].height = cm_to_points(4)
wb.active.page_margins = PageMargins(
MARGINS, MARGINS, MARGINS,
MARGINS) # most printers should handle these margins
# first two rows
j = 2
cell = wb.active.cell(2, 1)
cell.value = ls.strings.REGISTER_OF_CERTIFICATES
cell.alignment = Alignment(wrap_text=True,
horizontal="center",
vertical="center")
cell.border = thin_border
cell.font = Font(name='arial', size=15, underline='single')
for cat in registre.categories:
cell = wb.active.cell(1, j)
cell.value = cat
cell.alignment = Alignment(horizontal="center", vertical="center")
cell.border = thick_border
first = True
for c in registre.get_certificats(cat):
cell = wb.active.cell(2, j)
cell.value = c.nom
cell.alignment = Alignment(horizontal="center",
vertical="bottom",
textRotation=90)
cell.font = Font(name='arial', size=10)
cell.border = thick_border_left if first else thin_border
if first: first = False
wb.active.column_dimensions[get_column_letter(j)].width = 3
j += 1
cell.border = thick_border_right
wb.active.merge_cells(start_row=1,
start_column=j -
len(registre.get_certificats(cat)),
end_row=1,
end_column=j - 1)
"""
ValueError: Value must be one of {'lightDown', 'darkGray', 'lightHorizontal',
'lightVertical', 'darkDown', 'gray0625', 'solid', 'gray125', 'lightGray', 'darkGrid',
'lightGrid', 'lightUp', 'mediumGray', 'darkTrellis', 'darkVertical', 'lightTrellis', 'darkUp',
'darkHorizontal'
"""
# member rows
i = 3
k = 0
for m in registre.membres:
j = 2
header_height = wb.active.row_dimensions[
1].height + wb.active.row_dimensions[2].height
height_hint = (cm_to_points(29.7) - header_height - inches_to_points(
2 * MARGINS) - OFFSET) / len(registre.membres)
if height_hint < MIN_ROW_HEIGHT and height_hint > MIN_ROW_HEIGHT / 2:
wb.active.row_dimensions[i].height = 2 * height_hint
elif height_hint > MAX_ROW_HEIGHT:
wb.active.row_dimensions[i].height = MAX_ROW_HEIGHT
else:
wb.active.row_dimensions[i].height = height_hint
cell = wb.active.cell(i, 1)
cell.value = m.id
cell.alignment = Alignment(horizontal="center", vertical="center")
cell.border = thick_border_right_bottom if i % 4 == 2 else thick_border_right
for cat in registre.categories:
first = True
for cert in registre.get_certificats(cat):
cell = wb.active.cell(i, j)
if registre.registre[m, cert] == Registre.NonCertifie:
cell.fill = PatternFill(bgColor="000000", fill_type=None)
k += 1
elif registre.registre[m, cert] == Registre.Certifie:
cell.fill = PatternFill(bgColor="00FF00",
fill_type="gray0625")
elif registre.registre[m, cert] == Registre.Certificateur:
cell.fill = PatternFill(bgColor="0000FF",
fill_type="gray0625")
elif registre.registre[m, cert] == Registre.CertificatPerdu:
cell.fill = PatternFill(bgColor="888888",
fill_type="gray0625")
cell.alignment = Alignment(horizontal="center",
vertical="center")
if first:
cell.border = thick_border_left_bottom if i % 4 == 2 else thick_border_left
else:
cell.border = thick_border_bottom if i % 4 == 2 else thin_border
first = False
j += 1
cell.border = thick_border_right_bottom if i % 4 == 2 else thick_border_right
i += 1
# page for each category
for cat in registre.categories:
wb.create_sheet(cat)
wb.active = wb[cat]
wb.active.column_dimensions['A'].width = 15
wb.active.row_dimensions[1].height = cm_to_points(0.5)
wb.active.row_dimensions[2].height = cm_to_points(4)
wb.active.page_margins = PageMargins(
MARGINS, MARGINS, MARGINS,
MARGINS) # most printers should handle these margins
# first two rows
cell = wb.active.cell(2, 1)
cell.value = f"Certificats {cat}"
cell.alignment = Alignment(wrap_text=True,
horizontal="center",
vertical="center")
cell.font = Font(name='arial', size=15, underline='single')
cell.border = thin_border
cell = wb.active.cell(1, 2)
cell.value = f"{cat}"
cell.alignment = Alignment(horizontal="center", vertical="center")
cell.border = thick_border
wb.active.merge_cells(start_row=1,
start_column=2,
end_row=1,
end_column=len(registre.get_certificats(cat)) +
1)
j = 2
first = True
for cert in registre.get_certificats(cat):
cell = wb.active.cell(2, j)
cell.value = cert.nom
cell.alignment = Alignment(horizontal="center",
vertical="bottom",
textRotation=90)
cell.font = Font(name='arial', size=10)
cell.border = thick_border_left if first else thin_border
first = False
j += 1
cell.border = thick_border_right
i = 3
# member rows
for m in registre.membres:
header_height = wb.active.row_dimensions[
1].height + wb.active.row_dimensions[2].height
height_hint = (cm_to_points(29.7) - header_height -
inches_to_points(2 * MARGINS) - OFFSET) / len(
registre.membres)
if height_hint < MIN_ROW_HEIGHT and height_hint > MIN_ROW_HEIGHT / 2:
wb.active.row_dimensions[i].height = 2 * height_hint
elif height_hint > MAX_ROW_HEIGHT:
wb.active.row_dimensions[i].height = MAX_ROW_HEIGHT
else:
wb.active.row_dimensions[i].height = height_hint
cell = wb.active.cell(i, 1)
cell.value = m.id
cell.alignment = Alignment(horizontal="center", vertical="center")
cell.border = thick_border_right_bottom if i % 4 == 2 else thick_border_right
j = 2
first = True
for cert in registre.get_certificats(cat):
cell = wb.active.cell(i, j)
if registre.registre[m, cert] == Registre.NonCertifie:
cell.fill = PatternFill(bgColor="FFFFFF", fill_type=None)
elif registre.registre[m, cert] == Registre.Certifie:
cell.fill = PatternFill(bgColor="00FF00",
fill_type="gray0625")
cell.value = "c"
elif registre.registre[m, cert] == Registre.Certificateur:
cell.fill = PatternFill(bgColor="0000FF",
fill_type="gray0625")
cell.value = "C"
elif registre.registre[m, cert] == Registre.CertificatPerdu:
cell.fill = PatternFill(bgColor="888888",
fill_type="gray0625")
cell.value = "P"
cell.alignment = Alignment(horizontal="center",
vertical="center")
if first:
cell.border = thick_border_left_bottom if i % 4 == 2 else thick_border_left
else:
cell.border = thick_border_bottom if i % 4 == 2 else thin_border
first = False
wb.active.column_dimensions[get_column_letter(j)].width = 5
j += 1
cell.border = thick_border_right_bottom if i % 4 == 2 else thick_border_right
i += 1
wb.save(file)
# lauching libreoffice
# on windows
if os.name == "nt":
import winreg
try:
value = winreg.QueryValue(
winreg.HKEY_LOCAL_MACHINE,
"SOFTWARE\\LibreOffice\\UNO\\InstallPath")
os.system(f"start /B \"{value}\\soffice\" {file}")
except FileNotFoundError:
try:
value = winreg.QueryValue(
winreg.HKEY_LOCAL_MACHINE,
"SOFTWARE\\Wow6432Node\\LibreOffice\\UNO\\InstallPath")
os.system(f"start /B \"{value}\\soffice\" {file}")
except FileNotFoundError:
dialog(ls.strings.CANT_OPEN_LIBREOFFICE)
os.system(f"explorer {os.getcwd()}/printable")
# on linux
elif os.name == "posix":
os.system(f"libreoffice {file}&")
def parse_margins(self, element):
self.page_margins = PageMargins(**element.attrib)
def build_consolidated_bs(wb, dict_db, date_end):
wb_cur = wb
date_prior_ye = (date_end
+ relativedelta.relativedelta(months=-date_end.month))
gl_data = retrieve_bs_data(dict_db)
assets = sorted(set([x[1] for x in gl_data if x[0] == 'Asset']))
liabilities = sorted(set([x[1] for x in gl_data if x[0] == 'Liability']))
equities = sorted(set([x[1] for x in gl_data if x[0] == 'Equity']))
ws_cur = wb_cur.create_sheet('BS Consolidated')
c1=ws_cur.cell(row=1,
column=1,
value='Consolidated Balance Sheet - (HT/MYOP/RAC)')
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
ws_cur.merge_cells(start_row=1,
end_row=1,
start_column=1,
end_column=3)
ws_cur.cell(row=3, column=2,
value=datetime.strftime(date_end, '%B %d, %Y'))
ws_cur.cell(row=3, column=3,
value=datetime.strftime(date_prior_ye, '%B %d, %Y'))
for c in range(2, 4):
c1 = ws_cur.cell(row=3, column=c)
c1.fill = PatternFill(start_color='1e90ff',
end_color='1e90ff',
fill_type='solid')
c1.font = Font(bold='true', color='f8f8ff')
c1.alignment = Alignment(horizontal='center')
r_next = 4
c1=ws_cur.cell(row=r_next, column=1, value='ASSETS:')
c1.font = Font(bold='true')
r_next += 1
#Border Style
top_border = Border(top=Side(style='thin'))
#Assets
r = 0
for a in assets:
ws_cur.cell(row=r_next + r, column=1, value=a)
cur_period_bal = sum([x[3] for x in gl_data
if x[0] == 'Asset'
and x[1] == a
and x[2] == date_end])
ws_cur.cell(row=r_next + r, column=2, value=cur_period_bal)
py_end_bal = sum([x[3] for x in gl_data
if x[0] == 'Asset'
and x[1] == a
and x[2] == date_prior_ye])
ws_cur.cell(row=r_next + r, column=3, value=py_end_bal)
r += 1
r_next = r_next + r
r_asset = r_next
c1 = ws_cur.cell(row=r_next, column=1, value='Total Assets')
c1.font = Font(bold='true')
formula1 = '=sum(B{r1}:B{r2})'.format(
r1=r_next - len(assets), r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
formula1 = '=sum(C{r1}:C{r2})'.format(
r1=r_next - len(assets), r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
#Liabilities
r_next += 2
c1 = ws_cur.cell(row=r_next, column=1, value='LIABILITIES AND EQUITY:')
c1.font = Font(bold='true')
r_next += 1
r = 0
for liab in liabilities:
ws_cur.cell(row=r_next + r, column=1, value=liab)
cur_period_bal = -sum([x[3] for x in gl_data
if x[0] == 'Liability'
and x[1] == liab
and x[2] == date_end])
ws_cur.cell(row=r_next + r, column=2, value=cur_period_bal)
py_end_bal = -sum([x[3] for x in gl_data
if x[0] == 'Liability'
and x[1] == liab
and x[2] == date_prior_ye])
ws_cur.cell(row=r_next + r, column=3, value=py_end_bal)
r += 1
r_next = r_next + r
r_liability = r_next
c1 = ws_cur.cell(row=r_next, column=1, value='Total Liabilities')
c1.font = Font(bold='true')
formula1 = '=sum(B{r1}:B{r2})'.format(
r1=r_next - len(liabilities), r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
formula1 = '=sum(C{r1}:C{r2})'.format(
r1=r_next - len(liabilities), r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
#Equity
r_next += 2
r = 0
for e in equities:
ws_cur.cell(row=r_next + r, column=1, value=e)
cur_period_bal = -sum([x[3] for x in gl_data
if x[0] == 'Equity'
and x[1] == e
and x[2] == date_end])
ws_cur.cell(row=r_next + r, column=2, value=cur_period_bal)
py_end_bal = -sum([x[3] for x in gl_data
if x[0] == 'Equity'
and x[1] == e
and x[2] == date_prior_ye])
ws_cur.cell(row=r_next + r, column=3, value=py_end_bal)
r += 1
#Net Income
r_next = r_next + r
r_net_income = r_next
r_retained_earning = r_net_income - 1
ws_cur.cell(row=r_next, column=1, value='Net Income')
cur_period_bal = sum([x[3] for x in gl_data
if x[0] == 'Net Income'
and x[1] == 'Net Income'
and x[2] == date_end])
ws_cur.cell(row=r_next, column=2, value=cur_period_bal)
py_end_bal = sum([x[3] for x in gl_data
if x[0] == 'Net Income'
and x[1] == 'Net Income'
and x[2] == date_prior_ye])
ws_cur.cell(row=r_next, column=3, value=py_end_bal)
r_next += 1
r_equity = r_next
c1 = ws_cur.cell(row=r_next, column=1, value='Total Equity')
c1.font = Font(bold='true')
formula1 = '=sum(B{r1}:B{r2})'.format(
r1=r_next - len(equities) - 1, r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
formula1 = '=sum(C{r1}:C{r2})'.format(
r1=r_next - len(equities) - 1, r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
#Total Liabilities and Equity
r_next += 2
c1=ws_cur.cell(row=r_next, column=1, value='Total Liabilities and Equity')
c1.font = Font(bold='true')
formula1 = '=B{r1}+B{r2}'.format(
r1=r_liability, r2=r_equity)
c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
c1.font = Font(bold='true')
formula1 = '=C{r1}+C{r2}'.format(
r1=r_liability, r2=r_equity)
c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
c1.font = Font(bold='true')
r_next += 2
c1 = ws_cur.cell(row=r_next, column=1, value='Accounting Equation')
c1.font = Font(bold='true')
formula1='=B{r1}-B{r2}'.format(
r1=r_asset, r2=r_next - 2)
c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
c1.font = Font(bold='true')
formula1='=C{r1}-C{r2}'.format(
r1=r_asset, r2=r_next - 2)
c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
c1.font = Font(bold='true')
#Reclass non YTD Net Income to Retained Earnings---------------------------
net_income_cur = ws_cur.cell(row=r_net_income, column=2).value
net_income_cur_ytd = sum([x[3] for x in gl_data
if x[0] == 'Net Income YTD'
and x[1] == 'Net Income YTD'
and x[2] == date_end])
net_income_remainder = net_income_cur - net_income_cur_ytd
retained_earning_cur = ws_cur.cell(row=r_retained_earning, column=2).value
retained_earning_new = retained_earning_cur + net_income_remainder
ws_cur.cell(row=r_retained_earning, column=2, value=retained_earning_new)
ws_cur.cell(row=r_net_income, column=2, value=net_income_cur_ytd)
net_income_pye = ws_cur.cell(row=r_net_income, column=3).value
net_income_py_ytd = sum([x[3] for x in gl_data
if x[0] == 'Net Income YTD'
and x[1] == 'Net Income YTD'
and x[2] == date_prior_ye])
net_income_remainder = net_income_pye - net_income_py_ytd
retained_earning_pye = ws_cur.cell(row=r_retained_earning, column=3).value
retained_earning_new = retained_earning_pye + net_income_remainder
ws_cur.cell(row=r_retained_earning, column=3, value=retained_earning_new)
ws_cur.cell(row=r_net_income, column=3, value=net_income_py_ytd)
#-------------------------------------------------------------------------
#Format Cells
format_number = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
for r in range(5, ws_cur.max_row + 1):
for c in range(2, 4):
ws_cur.cell(row=r, column=c).number_format = format_number
#Column widths
ws_cur.column_dimensions['A'].width = 44
ws_cur.column_dimensions['B'].width = 20
ws_cur.column_dimensions['C'].width = 20
#Page Setup
ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT
ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID
ws_cur.page_setup.fitToPage = True
ws_cur.page_setup.fitToHeight = False
ws_cur.page_setup.fitToWidth = 1
ws_cur.print_options.horizontalCentered = True
ws_cur.add_print_title(3)
ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5)
#Freeze Panes
c1 = ws_cur.cell(row=4, column=1)
ws_cur.freeze_panes = c1
return wb_cur
def create_qual(self):
self.ws_qual = self.wb.create_sheet('资格后审', 0)
col_titles = ['序号', '内容', '页码']
content = [['一', '资格后审申请函'], ['二', '证明文件']]
content2 = [
'投标人的法人营业执照（复印件）和援外物资项目实施企业资格证明文件（复印件）', '法定代表人证明书和授权书（复印件）',
'无重大违法记录的声明函', '财务审计报告（复印件）', '依法缴纳社会保障资金的证明和税收的证明（复印件）',
'特殊物资经营资格、资质许可证明文件（复印件）', '关联企业声明', '其它'
]
col_width = [10, 60, 10]
col_num = 3
row_num = 12
# 创建专用样式
special_alignment = Alignment(horizontal='left',
vertical='center',
wrap_text=True,
indent=0)
special_font = Font(name='仿宋_GB2312', size=12)
# 初始化表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_qual.cell(row=i + 1, column=j + 1)
self.ws_qual.row_dimensions[i + 1].height = 45 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
elif 1 < i < 4: # 头两行
cell_now.font = Content.normal_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2][1]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = content[i - 2][0]
else: # 其余的一起填写
cell_now.font = special_font
if j == 1:
cell_now.alignment = special_alignment
cell_now.value = '{}、{}'.format(i - 3, content2[i - 4])
else:
cell_now.alignment = Content.ctr_alignment
if i != row_num - 1:
cell_now.border = Content.normal_border
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_qual.column_dimensions[letters[i]].width = col_width[i]
# 填写抬头
self.ws_qual.merge_cells('A1:C1')
header = self.ws_qual['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_qual.row_dimensions[1].height = 50
# 打印设置
self.ws_qual.print_options.horizontalCentered = True
self.ws_qual.print_area = 'A1:C{}'.format(row_num)
self.ws_qual.page_setup.fitToWidth = 1
self.ws_qual.page_margins = PageMargins(top=0.5,
bottom=0.5,
header=0.1,
footer=0.1)
def build_consolidated_p_and_l(wb, date_start, date_ytd_start, dict_db,
date_end):
wb_cur = wb
level_1 = retrieve_level_1(date_start, date_ytd_start, dict_db)
level_2 = retrieve_level_2(date_start, date_ytd_start, dict_db)
level_1_month = {}
level_1_ytd = {}
for x in range(0, len(level_1)):
level_1_month[level_1[x][0]] = level_1[x][1]
level_1_ytd[level_1[x][0]] = level_1[x][2]
level_2_month = {}
level_2_ytd = {}
for x in range(0, len(level_2)):
level_2_month[level_2[x][0]] = level_2[x][1]
level_2_ytd[level_2[x][0]] = level_2[x][2]
ws_cur = wb_cur.create_sheet('P&L Consolidated')
c1 = ws_cur.cell(row=1, column=1, value='Consolidated P&L - (HT/MYOP/RAC)')
c1 = ws_cur.cell(row=2, column=1, value='Income Statement')
report_date = '{d1:%B} {d1.day}, {d1.year}'.format(d1=date_end)
c1 = ws_cur.cell(row=3, column=1, value=report_date)
row_header_1 = 5
border_tb = Border(top=Side(style='thin'), bottom=Side(style='thin'))
border_tb2 = Border(top=Side(style='thin'), bottom=Side(style='double', ))
border_all = Border(top=Side(style='thin'),
bottom=Side(style='thin'),
left=Side(style='thin'),
right=Side(style='thin'))
for r in range(1, 4):
ws_cur.merge_cells(start_row=r,
end_row=r,
start_column=1,
end_column=11)
c1 = ws_cur.cell(row=r, column=1)
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
c1 = ws_cur.cell(row=row_header_1, column=1, value='Month')
ws_cur.merge_cells(start_row=row_header_1,
end_row=row_header_1,
start_column=1,
end_column=5)
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
c1 = ws_cur.cell(row=row_header_1, column=7, value='Year to Date')
ws_cur.merge_cells(start_row=row_header_1,
end_row=row_header_1,
start_column=7,
end_column=11)
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
row_header_2 = 6
c1 = ws_cur.cell(row=row_header_2, column=1, value='Actual')
c1 = ws_cur.cell(row=row_header_2, column=2, value='% Sales')
c1 = ws_cur.cell(row=row_header_2, column=3, value='Budget')
c1 = ws_cur.cell(row=row_header_2, column=4, value='% Sales')
c1 = ws_cur.cell(row=row_header_2, column=5, value='Variance')
for c in range(1, 6):
c1 = ws_cur.cell(row=row_header_2, column=c)
c1.fill = PatternFill(start_color='1e90ff',
end_color='1e90ff',
fill_type='solid')
c1.font = Font(bold='true', color='f8f8ff')
c1.alignment = Alignment(horizontal='center')
c1 = ws_cur.cell(row=row_header_2, column=7, value='Actual')
c1 = ws_cur.cell(row=row_header_2, column=8, value='% Sales')
c1 = ws_cur.cell(row=row_header_2, column=9, value='Budget')
c1 = ws_cur.cell(row=row_header_2, column=10, value='% Sales')
c1 = ws_cur.cell(row=row_header_2, column=11, value='Variance')
for c in range(7, 12):
c1 = ws_cur.cell(row=row_header_2, column=c)
c1.fill = PatternFill(start_color='1e90ff',
end_color='1e90ff',
fill_type='solid')
c1.font = Font(bold='true', color='f8f8ff')
c1.alignment = Alignment(horizontal='center')
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=row_header_2, column=c)
c1.border = border_all
#------Revenue------
r_next = row_header_2 + 1
row_sales = r_next
ws_cur = level_1_income(ws_cur, r_next, row_sales, 'Total Revenue',
level_1_month, level_1_ytd, 'Sales')
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#------Cost------
r_next += 1
ws_cur = level_1_expense(ws_cur, r_next, row_sales, 'Cost of Sales',
level_1_month, level_1_ytd, 'Cost')
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#------Gross Margin------
r_next += 1
row_gm = r_next
c1 = ws_cur.cell(row=r_next, column=6, value='Gross Margin')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "={col_letter}{r1}-{col_letter}{r2}".format(
col_letter=dcc.get(c), r1=r_next - 2, r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#------Adjustments to Margin------
r_next += 2
c1 = ws_cur.cell(row=r_next, column=6, value='Adjustments to Margin:')
c1.font = Font(bold='true')
#------Rebates------
r_next += 1
ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Rebates',
level_2_month, level_2_ytd, 'Rebates')
#------Cash Discounts------
r_next += 1
ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Cash Discounts',
level_2_month, level_2_ytd, 'Cash Discounts')
#------Customer Discounts------
r_next += 1
ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Customer Discounts',
level_2_month, level_2_ytd, 'Customer Discounts')
#------Cost of Goods Adjustments------
r_next += 1
ws_cur = level_2_income(ws_cur, r_next, row_sales,
'Cost of Goods Adjustments', level_2_month,
level_2_ytd, 'Cost of Goods Adjustments')
#------Other------
r_next += 1
ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Other', level_2_month,
level_2_ytd, 'Other Adjustments to Margin')
#------Total Adjustments to Margin------
r_next += 1
c1 = ws_cur.cell(row=r_next, column=6, value='Total Adjustments to Margin')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "=sum({col_letter}{r1}:{col_letter}{r2})".format(
col_letter=dcc.get(c), r1=r_next - 5, r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#------Adjusted Gross Margin------
r_next += 1
c1 = ws_cur.cell(row=r_next, column=6, value='Adjusted Gross Margin')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "={col_letter}{r1}+{col_letter}{r2}".format(
col_letter=dcc.get(c), r1=r_next - 9, r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#------Commissions------
r_next += 2
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Commissions',
level_2_month, level_2_ytd, 'Commissions')
#------Total Commissions------
r_next += 1
c1 = ws_cur.cell(row=r_next, column=6, value='Total Commissions')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "=sum({col_letter}{r1}:{col_letter}{r2})".format(
col_letter=dcc.get(c), r1=r_next - 1, r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_b}{r_cur}-{col_a}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#% To Margin
r_next += 1
c1 = ws_cur.cell(row=r_next, column=6, value='% To Margin')
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = (""
"=if({col1}{r_gm}=0,0,{col1}{r_cur}/{col1}{r_gm})").format(
col1=dcc.get(c), r_gm=row_gm, r_cur=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(italic='true')
#------Other Personnel Expenses------
r_next += 2
c1 = ws_cur.cell(row=r_next, column=6, value='Other Personnel Expenses:')
c1.font = Font(bold='true')
#------Salaries------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Salaries',
level_2_month, level_2_ytd, 'Salaries')
#------Bonuses------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Bonuses',
level_2_month, level_2_ytd, 'Bonuses')
#------Benefits------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Benefits',
level_2_month, level_2_ytd, 'Benefits')
#------Payroll Taxes------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Payroll Taxes',
level_2_month, level_2_ytd, 'Payroll Taxes')
#------Total Other Personnel Expenses------
r_next += 1
c1 = ws_cur.cell(row=r_next,
column=6,
value='Total Other Personnel Expenses')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "=sum({col_letter}{r1}:{col_letter}{r2})".format(
col_letter=dcc.get(c), r1=r_next - 4, r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_b}{r_cur}-{col_a}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#------Other Operating Expenses------
r_next += 2
c1 = ws_cur.cell(row=r_next, column=6, value='Other Operating Expenses:')
c1.font = Font(bold='true')
#------Travel------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Travel',
level_2_month, level_2_ytd, 'Travel')
#------Meals & Entertainment------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales,
'Meals & Entertainment', level_2_month,
level_2_ytd, 'Meals & Entertainment')
#------Facility Rent------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Facility Rent',
level_2_month, level_2_ytd, 'Facility Rent')
#------Utilities------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Utilities',
level_2_month, level_2_ytd, 'Utilities')
#------Facility Maintenance------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Facility Maintenance',
level_2_month, level_2_ytd,
'Facility Maintenance')
#------Fleet Fuel------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Fleet Fuel',
level_2_month, level_2_ytd, 'Fleet Fuel')
#------Fleet Repair & Maintenance------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales,
'Fleet Repair & Maintenance', level_2_month,
level_2_ytd, 'Fleet Repair & Maintenance')
#------Vehicle Rent Expense------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Vehicle Rent Expense',
level_2_month, level_2_ytd,
'Vehicle Rent Expense')
#------Outsourced Delivery------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Outsourced Delivery',
level_2_month, level_2_ytd, 'Outsourced Delivery')
#------Outbound Freight------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Outbound Freight',
level_2_month, level_2_ytd, 'Outbound Freight')
#------Outbound Freight Rebates------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales,
'Outbound Freight Rebates', level_2_month,
level_2_ytd, 'Outbound Freight Rebates')
#------Equipment Rental------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Equipment Rental',
level_2_month, level_2_ytd, 'Equipment Rental')
#------MRO Supplies------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'MRO Supplies',
level_2_month, level_2_ytd, 'MRO Supplies')
#------Office Freight & Postage------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales,
'Office Freight & Postage', level_2_month,
level_2_ytd, 'Office Freight & Postage')
#------Outside Services------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Outside Services',
level_2_month, level_2_ytd, 'Outside Services')
#------IT Services & Maintenance------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales,
'IT Services & Maintenance', level_2_month,
level_2_ytd, 'IT Services & Maintenance')
#------Telecom------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Telecom Expense',
level_2_month, level_2_ytd, 'Telecom Expense')
#------Business Insurance------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Business Insurance',
level_2_month, level_2_ytd, 'Business Insurance')
#------Training, Dues & Subscriptions------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales,
'Training, Dues & Subscriptions', level_2_month,
level_2_ytd, 'Training, Dues & Subscriptions')
#------Advertising & Marketing------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales,
'Advertising & Marketing', level_2_month,
level_2_ytd, 'Advertising & Marketing')
#------Diversity Partner Fees------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales,
'Diversity Partner Fees', level_2_month,
level_2_ytd, 'Diversity Partner Fees')
#------e-Commerce Fees------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'E-Commerce Fees',
level_2_month, level_2_ytd, 'e-Commerce Fees')
#------Professional Fees------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Professional Fees',
level_2_month, level_2_ytd, 'Professional Fees')
#------Bank Charges------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Bank Charges',
level_2_month, level_2_ytd, 'Bank Charges')
#------Bad Debt Expense------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Bad Debt Expense',
level_2_month, level_2_ytd, 'Bad Debt Expense')
#------Other Taxes------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Other Taxes',
level_2_month, level_2_ytd, 'Other Taxes')
#------Other Expenses------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Other Expenses',
level_2_month, level_2_ytd, 'Other Expenses')
#------Total Other Operating Expenses------
r_next += 1
c1 = ws_cur.cell(row=r_next,
column=6,
value='Total Other Operating Expenses')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "=sum({col_letter}{r1}:{col_letter}{r2})".format(
col_letter=dcc.get(c), r1=r_next - 27, r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_b}{r_cur}-{col_a}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#------Total Operating Expenses------
r_next += 1
c1 = ws_cur.cell(row=r_next, column=6, value='Total Operating Expenses')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "={col_letter}{r1}+{col_letter}{r2}+{col_letter}{r3}".format(
col_letter=dcc.get(c),
r1=r_next - 39,
r2=r_next - 31,
r3=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_b}{r_cur}-{col_a}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#------Other Income / (Expense)------
r_next += 2
c1 = ws_cur.cell(row=r_next, column=6, value='Other Income / (Expense):')
c1.font = Font(bold='true')
#------Corporate Allocation------
r_next += 1
ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Corporate Allocation',
level_2_month, level_2_ytd, 'Corporate Allocation')
#------Deferred Partner Revenue------
r_next += 1
ws_cur = level_2_income(ws_cur, r_next, row_sales,
'Deferred Partner Revenue', level_2_month,
level_2_ytd, 'Deferred Partner Revenue')
#------Excluded Expenses------
r_next += 1
ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Excluded Expenses',
level_2_month, level_2_ytd, 'Excluded Expenses')
#------Misc. Income------
r_next += 1
ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Misc. Income',
level_2_month, level_2_ytd, 'Misc. Income')
#------Gain/(Loss) on Fixed Asset Disposal------
r_next += 1
ws_cur = level_2_income(ws_cur, r_next, row_sales,
'Gain / (Loss) Fixed Asset Disposal',
level_2_month, level_2_ytd,
'Gain / (Loss) Fixed Asset Disposal')
#------Total Other Income / (Expense)------
r_next += 1
c1 = ws_cur.cell(row=r_next,
column=6,
value='Total Other Income / (Expense)')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "=sum({col_letter}{r1}:{col_letter}{r2})".format(
col_letter=dcc.get(c), r1=r_next - 5, r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#-----EBITDA------
r_next += 2
c1 = ws_cur.cell(row=r_next, column=6, value='EBITDA')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "={col_letter}{r1}-{col_letter}{r2}+{col_letter}{r3}".format(
col_letter=dcc.get(c),
r1=r_next - 52,
r2=r_next - 10,
r3=r_next - 2)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
c1.fill = PatternFill(start_color='ffd700',
end_color='ffd700',
fill_type='solid')
#------Interest Expense------
r_next += 2
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Interest Expense',
level_2_month, level_2_ytd, 'Interest Expense')
#------Depreciation------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Depreciation',
level_2_month, level_2_ytd, 'Depreciation')
#------Amortization------
r_next += 1
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Amortization',
level_2_month, level_2_ytd, 'Amortization')
#------Net Income Before Taxes------
r_next += 1
c1 = ws_cur.cell(row=r_next, column=6, value='Net Income Before Taxes')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = ("={col_letter}{r3}-"
"sum({col_letter}{r1}:{col_letter}{r2})").format(
col_letter=dcc.get(c),
r1=r_next - 3,
r2=r_next - 1,
r3=r_next - 5)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#------State Income Taxes------
r_next += 2
ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'State Income Taxes',
level_2_month, level_2_ytd, 'State Income Taxes')
#------Net Income------
r_next += 1
c1 = ws_cur.cell(row=r_next, column=6, value='Net Income')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = ("={col_letter}{r1}-{col_letter}{r2}").format(
col_letter=dcc.get(c), r1=r_next - 3, r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb2
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
#------Excluded Expenses------
r_next += 2
c1 = ws_cur.cell(row=r_next, column=6, value='EXCLUDED EXPENSES')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "=-{col_letter}{r1}".format(col_letter=dcc.get(c),
r1=r_next - 15)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
c1.fill = PatternFill(start_color='ffd700',
end_color='ffd700',
fill_type='solid')
#------Adjusted EBITDA------
r_next += 2
c1 = ws_cur.cell(row=r_next, column=6, value='ADJUSTED EBITDA')
#calculation
for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]:
formula1 = "={col_letter}{r1}+{col_letter}{r2}".format(
col_letter=dcc.get(c), r1=r_next - 12, r2=r_next - 2)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#% sales
for c in [x for x in range(2, 11, 2) if x != 6]:
formula1 = (
""
"=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format(
col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#variance
for c in range(5, 12, 6):
formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(
col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#border
for c in [x for x in range(1, 12) if x != 6]:
c1 = ws_cur.cell(row=r_next, column=c)
c1.border = border_tb
for c in range(1, 12):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
c1.fill = PatternFill(start_color='ffd700',
end_color='ffd700',
fill_type='solid')
#Change font size and name
for r in range(1, ws_cur.max_row + 1):
for c in range(1, ws_cur.max_column + 1):
c1 = ws_cur.cell(row=r, column=c)
c1.font = Font(bold=c1.font.bold,
italic=c1.font.italic,
name='Arial',
size=8,
color=c1.font.color)
#Row height
for r in range(1, ws_cur.max_row + 1):
ws_cur.row_dimensions[r].height = 11.5
#Column width
for x in [x for x in range(1, 12) if x in [1, 3, 5, 7, 9, 11]]:
ws_cur.column_dimensions[dcc.get(x)].width = 11
for x in [x for x in range(1, 12) if x in [2, 4, 8, 10]]:
ws_cur.column_dimensions[dcc.get(x)].width = 6.5
ws_cur.column_dimensions[dcc.get(6)].width = 27
#Page Setup
ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT
ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID
ws_cur.page_setup.fitToPage = True
ws_cur.page_setup.fitToHeight = 1
ws_cur.page_setup.fitToWidth = 1
ws_cur.print_options.horizontalCentered = True
ws_cur.add_print_title(6)
ws_cur.page_margins = PageMargins(left=.5,
right=.5,
top=.5,
bottom=.5,
footer=.5)
date_cur = datetime.strftime(
datetime.today(),
"%a {dt.month}/{dt.day}/%Y %I:%M %p".format(dt=datetime.today()))
ws_cur.oddFooter.right.text = date_cur
ws_cur.oddFooter.right.size = 8
ws_cur.oddFooter.right.font = 'Arial'
#Freeze Panes
c1 = ws_cur.cell(row=7, column=1)
ws_cur.freeze_panes = c1
return wb_cur
def excelupdate(Workbook, dict_db, date_end, month_actual, month_budget):
#Get data from data warehouse
gl_data = retrieve1(dict_db=dict_db)
wb_new = Workbook
ws_cur = wb_new.create_sheet('EBITDA Cash Analysis')
col_actual_start = 2
col_actual_end = col_actual_start + len(month_actual) - 1
col_budget_start = col_actual_end + 1
col_budget_end = col_budget_start + len(month_budget) - 1
c1 = ws_cur.cell(row=1, column=1, value="Consolidated (HT/MYOP/RAC)")
c1.alignment = Alignment(horizontal='center')
c1.font = Font(bold='true')
c1 = ws_cur.cell(row=2, column=1, value="EBITDA Cash Analysis")
c1.alignment = Alignment(horizontal='center')
c1.font = Font(bold='true')
str_period = ("For Period Ending {d1:%B} "
"{d1.day}, {d1.year}").format(d1=date_end)
c1 = ws_cur.cell(row=3, column=1, value=str_period)
c1.alignment = Alignment(horizontal='center')
c1.font = Font(bold='true')
row_date = 7
ws_cur.merge_cells(start_row=1, end_row=1, start_column=1, end_column=14)
ws_cur.merge_cells(start_row=2, end_row=2, start_column=1, end_column=14)
ws_cur.merge_cells(start_row=3, end_row=3, start_column=1, end_column=14)
for c in range(2, 14):
c1 = ws_cur.cell(row=row_date,
column=c,
value=calendar.month_name[c - 1])
c1.font = Font(underline='single', bold='true')
c1.alignment = Alignment(horizontal='center')
c1 = ws_cur.cell(row=row_date,
column=14,
value='Total ' + str(date_end.year))
c1.font = Font(underline='single', bold='true')
c1.alignment = Alignment(horizontal='center')
col_last = ws_cur.max_column
#Actual Header
c1 = ws_cur.cell(row=row_date - 1, column=col_actual_start, value='Actual')
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
ws_cur.merge_cells(start_row=row_date - 1,
end_row=row_date - 1,
start_column=col_actual_start,
end_column=col_actual_end)
for c in range(col_actual_start, col_actual_end + 1):
c1 = ws_cur.cell(row=row_date - 1, column=c)
c1.fill = PatternFill(start_color='b0e0e6',
end_color='b0e0e6',
fill_type='solid')
#Budget Header
c1 = ws_cur.cell(row=row_date - 1, column=col_budget_start, value='Budget')
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
ws_cur.merge_cells(start_row=row_date - 1,
end_row=row_date - 1,
start_column=col_budget_start,
end_column=col_budget_end)
for c in range(col_budget_start, col_budget_end + 1):
c1 = ws_cur.cell(row=row_date - 1, column=c)
c1.fill = PatternFill(start_color='bdb76b',
end_color='bdb76b',
fill_type='solid')
r_next = row_date + 1
#Sales
c1 = ws_cur.cell(row=r_next, column=1, value='Sales')
sales = []
for x in range(1, len(month_actual) + 1):
sales.append(
sum(r[1] for r in gl_data if r[0].month == x
if r[0].year == date_end.year))
for c in range(2, 2 + len(month_actual)):
c1 = ws_cur.cell(row=r_next, column=c, value=sales[c - 2])
adj_gross_margin = []
for x in range(1, len(month_actual) + 1):
adj_gross_margin.append(
sum(r[2] for r in gl_data if r[0].month == x
if r[0].year == date_end.year))
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Adjusted Gross Margin')
for c in range(2, 2 + len(month_actual)):
c1 = ws_cur.cell(row=r_next, column=c, value=adj_gross_margin[c - 2])
for c in range(2, 15):
for r in range(r_next - 1, r_next + 1):
c1 = ws_cur.cell(row=r, column=c)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#Adjusted Gross Margin %
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='%')
for c in range(2, 15):
formula1 = ("=if({col_letter}{row1}=0,0,"
"{col_letter}{row2}/{col_letter}{row1})").format(
col_letter=dcc.get(c),
row1=r_next - 2,
row2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#Adjusted EBITDA
adj_ebitda = []
for x in range(1, len(month_actual) + 1):
adj_ebitda.append(
sum(r[7] for r in gl_data if r[0].month == x
if r[0].year == date_end.year))
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Adjusted EBITDA')
for c in range(col_actual_start, col_actual_end + 1):
c1 = ws_cur.cell(row=r_next, column=c, value=adj_ebitda[c - 2])
for c in range(col_actual_start, col_last + 1):
c1 = ws_cur.cell(row=r_next, column=c)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#Adj EBITDA %
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='%')
for c in range(2, 15):
formula1 = ("=if({col_letter}{row1}=0,0,"
"{col_letter}{row2}/{col_letter}{row1})").format(
col_letter=dcc.get(c),
row1=r_next - 4,
row2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
c1.number_format = '0.0%'
#EBITDA Adjustment
r_next += 2
c1 = ws_cur.cell(row=r_next, column=1, value='EBITDA Adjustment')
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Adjusted EBITDA')
c1.alignment = Alignment(indent=2)
for c in range(col_actual_start, col_actual_end + 1):
c1 = ws_cur.cell(row=r_next, column=c, value=adj_ebitda[c - 2])
def_revenue = []
for x in range(1, len(month_actual) + 1):
def_revenue.append(
sum(r[5] for r in gl_data if r[0].month == x
if r[0].year == date_end.year))
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Deferred Revenue')
c1.alignment = Alignment(indent=2)
for c in range(col_actual_start, col_actual_end + 1):
c1 = ws_cur.cell(row=r_next, column=c, value=def_revenue[c - 2])
excl_expense = []
for x in range(1, len(month_actual) + 1):
excl_expense.append(
sum(r[4] for r in gl_data if r[0].month == x
if r[0].year == date_end.year))
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Excluded Expenses')
c1.alignment = Alignment(indent=2)
for c in range(col_actual_start, col_actual_end + 1):
c1 = ws_cur.cell(row=r_next, column=c, value=excl_expense[c - 2])
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Cash EBITDA')
for c in range(col_actual_start, col_last + 1):
formula1 = "=sum({col_letter}{row1}:{col_letter}{row2})".format(
col_letter=dcc.get(c), row1=r_next - 3, row2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
#Cash Needs
r_next += 2
c1 = ws_cur.cell(row=r_next, column=1, value='Cash Needs')
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Cash Interest (1)')
c1.alignment = Alignment(indent=2)
def_finance_fees = setup1.def_finance_fees()
interest_expense = []
for x in range(1, len(month_actual) + 1):
interest_expense.append(
sum(r[6] - def_finance_fees for r in gl_data if r[0].month == x
if r[0].year == date_end.year))
for c in range(col_actual_start, col_actual_end + 1):
c1 = ws_cur.cell(row=r_next, column=c, value=interest_expense[c - 2])
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Principal Payments (2)')
c1.alignment = Alignment(indent=2)
for c in range(col_actual_start, col_actual_end + 1):
c1 = ws_cur.cell(row=r_next,
column=c,
value=setup1.principal_payments())
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='CAPEX')
c1.alignment = Alignment(indent=2)
capex = []
for x in range(1, len(month_actual) + 1):
capex.append(
sum(r[8] for r in gl_data if r[0].month == x
if r[0].year == date_end.year))
for c in range(col_actual_start, col_actual_end + 1):
c1 = ws_cur.cell(row=r_next, column=c, value=capex[c - 2])
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Total Cash Needs')
for c in range(col_actual_start, col_last + 1):
formula1 = "=sum({col_letter}{row1}:{col_letter}{row2})".format(
col_letter=dcc.get(c), row1=r_next - 3, row2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
#Free Cash Flow
r_next += 2
c1 = ws_cur.cell(row=r_next,
column=1,
value='Free Cash Flow (EBITDA Coverage')
for c in range(col_actual_start, col_last + 1):
formula1 = "={col_letter}{row1}-{col_letter}{row2}".format(
col_letter=dcc.get(c), row1=r_next - 8, row2=r_next - 2)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
#Budgeted FCF
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Budged FCF')
#Variance
r_next += 1
c1 = ws_cur.cell(row=r_next, column=1, value='Variance')
for c in range(col_actual_start, col_last + 1):
formula1 = "={col_letter}{row1}-{col_letter}{row2}".format(
col_letter=dcc.get(c), row1=r_next - 2, row2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=c, value=formula1)
#Total Year
sum_rows = [8, 9, 11, 15, 16, 17, 21, 22, 23, 27]
for r in range(0, len(sum_rows)):
ws_cur.cell(row=sum_rows[r],
column=col_last,
value=excel_formulas.sum_row_1(sum_rows[r], 2,
col_last - 1))
#Format Cells
row_start = row_date + 6
row_last = ws_cur.max_row
for c in range(2, col_last + 1):
for r in range(row_start, row_last + 1):
c1 = ws_cur.cell(row=r, column=c)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
#Footnotes
r_next = row_last + 3
str_note = (
"1) Deferred Financing Fees are deducted from interest expense "
"since it is non cash.")
c1 = ws_cur.cell(row=r_next, column=1, value=str_note)
#Column Width
ws_cur.column_dimensions[dcc.get(1)].width = 35
for c in range(2, 15):
ws_cur.column_dimensions[dcc.get(c)].width = 13
#Page Setup
ws_cur.page_setup.orientation = ws_cur.ORIENTATION_LANDSCAPE
ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID
ws_cur.page_setup.fitToPage = True
ws_cur.page_setup.fitToHeight = False
ws_cur.page_setup.fitToWidth = 1
ws_cur.print_options.horizontalCentered = True
ws_cur.add_print_title(7)
ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5)
#Freeze Panes
c1 = ws_cur.cell(row=8, column=1)
ws_cur.freeze_panes = c1
return wb_new
def excelupdate(wb, dict_db, date_start, date_end, date_ytd_start,
date_trail_12_start):
result = retrieve1(dict_db=dict_db)
wb_cur = wb
prod_class = sorted(set([r[1] for r in result]))
ws_cur = wb_cur.create_sheet('Schedule B')
c1 = ws_cur.cell(row=1, column=1, value='HiTouch Business Services, LLC')
str_value = ("Schedule B - Office Products Business Unit Product Mix")
c1 = ws_cur.cell(row=2, column=1, value=str_value)
str_value = ("For the Period ending {d1:%B} {d1.day}, {d1.year}").format(
d1=date_end)
c1 = ws_cur.cell(row=3, column=1, value=str_value)
#4 Prior Months in descending order
month_prior_4 = []
month_prior_4.append(date_start)
for m in range(1, 4):
month_prior_4.append(date_start + relativedelta(months=-m))
#Reverse in ascending order
month_prior_4.reverse()
row_start = 8
#Product Class
r_next = row_start
for x in range(0, len(prod_class)):
c1 = ws_cur.cell(row=r_next, column=1, value=prod_class[x])
r_next += 1
#Headers
r_next = row_start - 1
c1 = ws_cur.cell(row=r_next, column=1, value='Product Class')
for c in range(2, 13, 2):
c1 = ws_cur.cell(row=r_next, column=c, value='Revenues')
for c in range(3, 14, 2):
c1 = ws_cur.cell(row=r_next, column=c, value='% of Rev')
r_next = row_start - 2
c_next = 2
for x in range(0, len(month_prior_4)):
mth_name = calendar.month_name[month_prior_4[x].month]
c1 = ws_cur.cell(row=r_next, column=c_next, value=mth_name)
c_next += 2
c1 = ws_cur.cell(row=r_next,
column=10,
value=str(date_end.year) + ' ' + 'Year-to-Date')
c1 = ws_cur.cell(row=r_next, column=12, value='Trailing 12 Months')
#Revenue Trailing 4 Months
r_next = row_start
for p in prod_class:
c_next = 2
for c in month_prior_4:
rev = sum([x[2] for x in result if x[0] == c if x[1] == p])
c1 = ws_cur.cell(row=r_next, column=c_next, value=rev)
c_next += 2
r_next += 1
#Revenue YTD
r_next = row_start
c_next = 10
for p in prod_class:
rev = sum([
x[2] for x in result if date_ytd_start <= x[0] <= date_end
if x[1] == p
])
c1 = ws_cur.cell(row=r_next, column=c_next, value=rev)
r_next += 1
#Revenue Trailing 12 Months
r_next = row_start
c_next = 12
for p in prod_class:
rev = sum([
x[2] for x in result if date_trail_12_start <= x[0] <= date_end
if x[1] == p
])
c1 = ws_cur.cell(row=r_next, column=c_next, value=rev)
r_next += 1
#Totals
r_last = ws_cur.max_row
r_total = r_last + 2
c_next = 2
for c in range(c_next, 14):
formula1 = excel_formulas.sum_col_1(row1=row_start,
row2=r_last,
col1=c)
c1 = ws_cur.cell(row=r_total, column=c, value=formula1)
#% of Rev
c_next = 3
for c in range(c_next, 14, 2):
for r in range(row_start, r_last + 1):
formula1 = excel_formulas.pct_of_total1(c, r_total, r)
c1 = ws_cur.cell(row=r, column=c, value=formula1)
#Format
c_last = ws_cur.max_column
ws_cur.merge_cells(start_row=1,
end_row=1,
start_column=1,
end_column=c_last)
ws_cur.merge_cells(start_row=2,
end_row=2,
start_column=1,
end_column=c_last)
ws_cur.merge_cells(start_row=3,
end_row=3,
start_column=1,
end_column=c_last)
for r in range(1, 4):
c1 = ws_cur.cell(row=r, column=1)
c1.alignment = Alignment(horizontal='center')
c1.font = Font(bold='true')
border_left = Border(left=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
border_right = Border(right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
r_next = row_start - 2
for c in range(2, 13, 2):
ws_cur.merge_cells(start_row=r_next,
end_row=r_next,
start_column=c,
end_column=c + 1)
c1 = ws_cur.cell(row=r_next, column=c)
c1.alignment = Alignment(horizontal='center')
c1.font = Font(bold='true')
c1.border = border_left
c1 = ws_cur.cell(row=r_next, column=c + 1)
c1.border = border_right
r_next = row_start - 1
for c in range(1, c_last + 1):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
for c in range(2, 13, 2):
for r in range(row_start, r_total + 1):
c1 = ws_cur.cell(row=r, column=c)
c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
for c in range(3, 14, 2):
for r in range(row_start, r_total + 1):
c1 = ws_cur.cell(row=r, column=c)
c1.number_format = '0.0%'
#Column widths
ws_cur.column_dimensions['A'].width = 27
for c in range(2, c_last + 1):
ws_cur.column_dimensions[dcc.get(c)].width = 12.5
#Page Setup
ws_cur.page_setup.orientation = ws_cur.ORIENTATION_LANDSCAPE
ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID
ws_cur.page_setup.fitToPage = True
ws_cur.page_setup.fitToHeight = False
ws_cur.page_setup.fitToWidth = 1
ws_cur.print_options.horizontalCentered = True
ws_cur.add_print_title(7)
ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5)
#Freeze Panes
c1 = ws_cur.cell(row=8, column=1)
ws_cur.freeze_panes = c1
return wb_cur
def build_report(dict_db, date_end, wb):
rebates = get_rebate_data(date_end, dict_db)
sales = get_sales_data(date_end, dict_db)
date_reverse = sorted(set([x[0] for x in sales]), reverse=1)
wb_cur = wb
ws_cur = wb_cur.create_sheet('Schedule C')
ws_cur.cell(row=1, column=1, value='Schedule C - Rebate Components')
ws_cur.cell(row=2, column=1, value='Consolidated (HT/MYOP)')
ws_cur.cell(row=3,
column=1,
value='For the 12 months ending {d1}'.format(
d1=datetime.strftime(date_end, '%B %d, %Y')))
for r in range(1, 4):
ws_cur.cell(row=r, column=1).font = Font(bold='true')
date_reverse = sorted(set([x[0] for x in sales]), reverse=1)
r_next = 5
ws_cur.cell(row=r_next, column=2, value='Pricing')
ws_cur.cell(row=r_next, column=3, value='Wholesalers')
ws_cur.cell(row=r_next, column=4, value='Manufacturing')
ws_cur.cell(row=r_next, column=5, value='Direct Buy')
ws_cur.cell(row=r_next, column=6, value='Total')
for c in range(2, 7):
c1 = ws_cur.cell(row=r_next, column=c)
c1.font = Font(bold='true', underline='single')
c1.alignment = Alignment(horizontal='center')
ws_cur.merge_cells(start_row=1, end_row=1, start_column=1, end_column=6)
ws_cur.merge_cells(start_row=2, end_row=2, start_column=1, end_column=6)
ws_cur.merge_cells(start_row=3, end_row=3, start_column=1, end_column=6)
for r in range(1, 4):
c1 = ws_cur.cell(row=r, column=1)
c1.alignment = Alignment(horizontal='center')
format_number = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
border_tl = Border(top=Side(style='thin'),
left=Side(style='thin'))
border_t = Border(top=Side(style='thin'))
border_tr = Border(top=Side(style='thin'),
right=Side(style='thin'))
border_bl = Border(bottom=Side(style='thin'),
left=Side(style='thin'))
border_b = Border(bottom=Side(style='thin'))
border_br = Border(bottom=Side(style='thin'),
right=Side(style='thin'))
r_next += 2
for d in date_reverse:
month_sales = sum([s[1] for s in sales if s[0] == d])
ws_cur.cell(row=r_next, column=1,
value = datetime.strftime(d, '%B %Y'))
pricing = sum([r[2] for r in rebates
if r[0] == d
if r[1] == 'Pricing'])
c1 = ws_cur.cell(row=r_next, column=2,
value = pricing)
c1.border = border_tl
wholesaler = sum([r[2] for r in rebates
if r[0] == d
if r[1] == 'Wholesalers'])
ws_cur.cell(row=r_next, column=3,
value = wholesaler)
manufacturing = sum([r[2] for r in rebates
if r[0] == d
if r[1] == 'Manufacturing'])
ws_cur.cell(row=r_next, column=4,
value = manufacturing)
direct_buy = sum([r[2] for r in rebates
if r[0] == d
if r[1] == 'Direct Buy Savings'])
ws_cur.cell(row=r_next, column=5,
value = direct_buy)
formula_total = '=sum(B{r1}:E{r1})'.format(r1=r_next)
c1 = ws_cur.cell(row=r_next, column=6, value=formula_total)
c1.border = border_tr
for c in range(2, 7):
ws_cur.cell(row=r_next, column=c).number_format = format_number
for c in range(3, 6):
c1 = ws_cur.cell(row=r_next, column=c).border = border_t
pricing_ratio = pricing / month_sales if month_sales else 0
wholesaler_ratio = wholesaler / month_sales if month_sales else 0
manufacturing_ratio = manufacturing / month_sales if month_sales else 0
direct_buy_ratio = direct_buy / month_sales if month_sales else 0
ws_cur.cell(row=r_next + 1, column=1, value='% of Revenue')
ws_cur.cell(row=r_next + 1, column=2, value=pricing_ratio)
ws_cur.cell(row=r_next + 1, column=3, value=wholesaler_ratio)
ws_cur.cell(row=r_next + 1, column=4, value=manufacturing_ratio)
ws_cur.cell(row=r_next + 1, column=5, value=direct_buy_ratio)
formula_total = '=sum(B{r1}:E{r1})'.format(r1=r_next + 1)
ws_cur.cell(row=r_next + 1, column=6, value=formula_total)
for c in range(2, 7):
ws_cur.cell(row=r_next + 1, column=c).number_format = '0.0%'
ws_cur.cell(row=r_next + 1, column=2).border = border_bl
for c in range(3, 6):
ws_cur.cell(row=r_next + 1, column=c).border = border_b
ws_cur.cell(row=r_next + 1, column=6).border = border_br
r_next += 3
#Total
ws_cur.cell(row=r_next, column=1, value='Rolling 12 Month Total')
for c in range(2, 7):
r = 3
cat_total = '='
for x in range(1, 13):
cat_total = cat_total + '{c1}{r1}+'.format(
c1=dcc.get(c), r1=r_next - r)
r += 3
cat_total = cat_total[:-1]
ws_cur.cell(row=r_next, column=c, value=cat_total)
ws_cur.cell(row=r_next + 1, column=1, value='% of Revenue')
pricing = sum([r[2] for r in rebates
if r[1] == 'Pricing'])
wholesaler = sum([r[2] for r in rebates
if r[1] == 'Wholesalers'])
manufacturing = sum([r[2] for r in rebates
if r[1] == 'Manufacturing'])
direct_buy = sum([r[2] for r in rebates
if r[1] == 'Direct Buy Savings'])
rebate_total = sum([r[2] for r in rebates])
sales_total = sum([s[1] for s in sales])
pricing_ratio = pricing / sales_total if sales_total else 0
wholesaler_ratio = wholesaler / sales_total if sales_total else 0
manufacturing_ratio = manufacturing / sales_total if sales_total else 0
direct_buy_ratio = direct_buy / sales_total if sales_total else 0
rebate_total_ratio = rebate_total / sales_total if sales_total else 0
ws_cur.cell(row=r_next + 1, column=2, value=pricing_ratio)
ws_cur.cell(row=r_next + 1, column=3, value=wholesaler_ratio)
ws_cur.cell(row=r_next + 1, column=4, value=manufacturing_ratio)
ws_cur.cell(row=r_next + 1, column=5, value=direct_buy_ratio)
ws_cur.cell(row=r_next + 1, column=6, value=rebate_total_ratio)
for c in range(2, 7):
ws_cur.cell(row=r_next, column=c).number_format = format_number
ws_cur.cell(row=r_next + 1, column=c).number_format = '0.0%'
ws_cur.cell(row=r_next, column=2).border = border_tl
ws_cur.cell(row=r_next + 1, column=2).border = border_bl
for c in range(3, 6):
ws_cur.cell(row=r_next, column=c).border = border_t
ws_cur.cell(row=r_next + 1, column=c).border = border_b
ws_cur.cell(row=r_next, column=6).border = border_tr
ws_cur.cell(row=r_next + 1, column=6).border = border_br
#Page Setup
ws_cur.column_dimensions[dcc.get(1)].width = 22
for c in range(2, 7):
ws_cur.column_dimensions[dcc.get(c)].width = 15
ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT
ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID
ws_cur.page_setup.fitToPage = True
ws_cur.page_setup.fitToHeight = 1
ws_cur.page_setup.fitToWidth = 1
ws_cur.print_options.horizontalCentered = True
ws_cur.add_print_title(5)
ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5,
footer=.5)
#Freeze Panes
c1 = ws_cur.cell(row=6, column=1)
ws_cur.freeze_panes = c1
return wb_cur
c1 = ws_cur.cell(row=row_next, column=1, value='POS Margin %')
for c in range(3, col_end + 1, 2):
c1 = ws_cur.cell(row=row_next,
column=c,
value='''=IF({col_letter1}{row1}=0,0,
({col_letter1}{row1}-{col_letter2}{row1})/
{col_letter1}{row1})'''.format(
col_letter1=dcc.get(c - 1),
row1=row_next - 1,
col_letter2=dcc.get(c)).replace('\n',
'').replace(' ', ''))
c1.number_format = '0.0%'
#Set Page Margins
ws_cur = wb['HiTouch']
ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5)
ws_cur = wb['MYOP']
ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5)
ws_cur = wb['HiTouch_and_MYOP']
ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5)
save_path = 'c:\\temp\\'
wb.save(save_path +
'SALES ANALYSIS SUMMARY MTD {d1.year}{dmth}{dday}.xlsx'.format(
d1=date_end,
dmth=str(date_end.month).zfill(2),
dday=str(date_end.day).zfill(2)))
print('Done')
def location_inventory_update(wb):
wb_cur = wb
ws_cur = wb.create_sheet('Location Inventory')
file_path = "Z:/Accounting/Accounting/Financial Package/"
dir_list = listdir(file_path)
inv_file = [x for x in dir_list if 'Inventory By Location' in x]
if len(inv_file) > 1:
print('Multiple Inventory By Location Files, Process Cancelled!')
sys.exit()
msg_text = "Use Inventory File {f1}?".format(f1=inv_file)
root = Tk()
root.withdraw()
answer_loc_file = messagebox.askquestion('Location File', msg_text)
if answer_loc_file == 'no':
print('Wrong Location File in Package Folder, Process Cancelled!')
err_index = 1
cont_return = [wb_cur, err_index]
sys.exit()
wb_source = load_workbook(file_path + inv_file[0], read_only=True)
#Last Worksheet
ws_source = wb_source.worksheets[len(wb_source.worksheets) - 1]
#Last data row
r = 1
while True:
if ws_source.cell(row=r, column=2).value == 'TOTAL INVENTORY':
break
if r > ws_source.max_row:
break
r += 1
if r > ws_source.max_row:
print(("Location Inventory: Can't find TOTAL INVENTORY record, "
"Process Cancelled"))
err_index = 1
cont_return = [wb_cur, err_index]
sys.exit()
row_end = r - 1
#Header data row
r = 1
while True:
if ws_source.cell(row=r, column=2).value == 'Location':
break
if r > ws_source.max_row:
break
r += 1
if r > ws_source.max_row:
print(("Location Inventory: Can't find Location column header record, "
"Process Cancelled"))
err_index = 1
cont_return = [wb_cur, err_index]
sys.exit()
row_header = r
titles = []
titles.append(ws_source.cell(row=1, column=1).value)
titles.append(ws_source.cell(row=2, column=1).value)
titles.append(ws_source.cell(row=3, column=1).value)
header1 = [
ws_source.cell(row=row_header, column=x).value for x in range(1, 7)
]
#flat list of records
records = []
for r in range(row_header + 1, row_end + 1):
for c in range(1, 5):
records.append(ws_source.cell(row=r, column=c).value)
#row list of records
records2 = []
for x in range(0, len(records), 4):
records2.append(records[x:x + 4])
for x in range(0, len(records2)):
if records2[x][0] == None:
records2[x][0] = records2[x - 1][0]
if records2[x][0] == 'Managed':
records2[x][0] = 'Vendor Managed'
#remove blanks
records3 = [
x for x in records2 if float(x[2] or 0) + float(x[3] or 0) != 0
]
#Write to Financial Package
c1 = ws_cur.cell(row=1, column=1, value=titles[0])
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
c1 = ws_cur.cell(row=2, column=1, value=titles[1])
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
c1 = ws_cur.cell(row=3, column=1, value=titles[2])
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
ws_cur.merge_cells(start_row=1, end_row=1, start_column=1, end_column=6)
ws_cur.merge_cells(start_row=2, end_row=2, start_column=1, end_column=6)
ws_cur.merge_cells(start_row=3, end_row=3, start_column=1, end_column=6)
#Header
r_next = 5
for c in range(1, 7):
c1 = ws_cur.cell(row=r_next, column=c, value=header1[c - 1])
c1.fill = PatternFill(start_color='cdc9c9',
end_color='cdc9c9',
fill_type='solid')
#Records
r_next += 1
c_next = 1
for r in range(0, len(records3)):
for e in range(0, len(records3[r])):
ws_cur.cell(row=r_next, column=c_next, value=records3[r][e])
c_next += 1
c_next = 1
r_next += 1
r_next = 6
for r in range(r_next, ws_cur.max_row + 1):
formula1 = "={col_1}{row_cur}-{col_2}{row_cur}".format(
row_cur=r, col_1=dcc.get(4), col_2=dcc.get(3))
ws_cur.cell(row=r, column=5, value=formula1)
for r in range(r_next, ws_cur.max_row + 1):
formula1 = ("=if({col_1}{row_cur}=0,0,"
"{col_2}{row_cur}/{col_1}{row_cur})").format(
row_cur=r, col_1=dcc.get(3), col_2=dcc.get(5))
ws_cur.cell(row=r, column=6, value=formula1)
#Count region records
regions = sorted(set([x[0] for x in records3]))
region_count = {}
for r in regions:
region_count[r] = [x[0] for x in records3 if x[0] == r].count(r)
#Totals
r_next = ws_cur.max_row + 2
ws_cur.cell(row=r_next, column=1, value='Total')
inv_pm = sum([x[2] for x in records3])
ws_cur.cell(row=r_next, column=3, value=inv_pm)
inv_cm = sum([x[3] for x in records3])
ws_cur.cell(row=r_next, column=4, value=inv_cm)
formula1 = "={col_1}{row_cur}-{col_2}{row_cur}".format(row_cur=r_next,
col_1=dcc.get(4),
col_2=dcc.get(3))
ws_cur.cell(row=r_next, column=5, value=formula1)
formula1 = ("=if({col_1}{row_cur}=0,0,"
"{col_2}{row_cur}/{col_1}{row_cur})").format(row_cur=r_next,
col_1=dcc.get(3),
col_2=dcc.get(5))
ws_cur.cell(row=r_next, column=6, value=formula1)
for c in range(1, 7):
ws_cur.cell(row=r_next, column=c).font = Font(bold=True)
#Region Totals
r_next = ws_cur.max_row + 2
regions_supplies = [x for x in regions if x != 'Vendor Managed']
for r in range(0, len(regions_supplies)):
ws_cur.cell(row=r_next, column=1, value=regions_supplies[r])
inv_pm = sum([x[2] for x in records3 if x[0] == regions_supplies[r]])
ws_cur.cell(row=r_next, column=3, value=inv_pm)
inv_cm = sum([x[3] for x in records3 if x[0] == regions_supplies[r]])
ws_cur.cell(row=r_next, column=4, value=inv_cm)
formula1 = "={col_1}{row_cur}-{col_2}{row_cur}".format(
row_cur=r_next, col_1=dcc.get(4), col_2=dcc.get(3))
ws_cur.cell(row=r_next, column=5, value=formula1)
formula1 = ("=if({col_1}{row_cur}=0,0,"
"{col_2}{row_cur}/{col_1}{row_cur})").format(
row_cur=r_next, col_1=dcc.get(3), col_2=dcc.get(5))
ws_cur.cell(row=r_next, column=6, value=formula1)
r_next += 1
r_next += 1
ws_cur.cell(row=r_next, column=1, value='Total Supplies Inventory')
inv_pm_supplies = sum([x[2] for x in records3 if x[0] != 'Vendor Managed'])
ws_cur.cell(row=r_next, column=3, value=inv_pm_supplies)
inv_cm_supplies = sum([x[3] for x in records3 if x[0] != 'Vendor Managed'])
ws_cur.cell(row=r_next, column=4, value=inv_cm_supplies)
formula1 = "={col_1}{row_cur}-{col_2}{row_cur}".format(row_cur=r_next,
col_1=dcc.get(4),
col_2=dcc.get(3))
ws_cur.cell(row=r_next, column=5, value=formula1)
formula1 = ("=if({col_1}{row_cur}=0,0,"
"{col_2}{row_cur}/{col_1}{row_cur})").format(row_cur=r_next,
col_1=dcc.get(3),
col_2=dcc.get(5))
ws_cur.cell(row=r_next, column=6, value=formula1)
for c in range(1, 7):
ws_cur.cell(row=r_next, column=c).font = Font(bold=True)
#Vendor Managed Total
r_next += 2
ws_cur.cell(row=r_next, column=1, value='Vendor Managed')
inv_pm_vendor = sum([x[2] for x in records3 if x[0] == 'Vendor Managed'])
ws_cur.cell(row=r_next, column=3, value=inv_pm_vendor)
inv_cm_vendor = sum([x[3] for x in records3 if x[0] == 'Vendor Managed'])
ws_cur.cell(row=r_next, column=4, value=inv_cm_vendor)
formula1 = "={col_1}{row_cur}-{col_2}{row_cur}".format(row_cur=r_next,
col_1=dcc.get(4),
col_2=dcc.get(3))
ws_cur.cell(row=r_next, column=5, value=formula1)
formula1 = ("=if({col_1}{row_cur}=0,0,"
"{col_2}{row_cur}/{col_1}{row_cur})").format(row_cur=r_next,
col_1=dcc.get(3),
col_2=dcc.get(5))
ws_cur.cell(row=r_next, column=6, value=formula1)
for c in range(1, 7):
ws_cur.cell(row=r_next, column=c).font = Font(bold=True)
#Format
format_number = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
for r in range(6, ws_cur.max_row + 1):
for c in range(3, 6):
ws_cur.cell(row=r, column=c).number_format = format_number
for r in range(6, ws_cur.max_row + 1):
for c in range(6, 7):
ws_cur.cell(row=r, column=c).number_format = '0.0%'
for c in range(1, 7):
ws_cur.cell(row=5, column=c).alignment = Alignment(horizontal='center')
ws_cur.cell(row=5, column=c).font = Font(bold=True)
#Page Setup
ws_cur.column_dimensions[dcc.get(1)].width = 24
ws_cur.column_dimensions[dcc.get(2)].width = 33
ws_cur.column_dimensions[dcc.get(3)].width = 18
ws_cur.column_dimensions[dcc.get(4)].width = 18
ws_cur.column_dimensions[dcc.get(5)].width = 18
ws_cur.column_dimensions[dcc.get(6)].width = 13
ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT
ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID
ws_cur.page_setup.fitToPage = True
ws_cur.page_setup.fitToHeight = 1
ws_cur.page_setup.fitToWidth = 1
ws_cur.print_options.horizontalCentered = True
ws_cur.add_print_title(5)
ws_cur.page_margins = PageMargins(left=.5,
right=.5,
top=.5,
bottom=.5,
footer=.5)
#Freeze Panes
c1 = ws_cur.cell(row=6, column=1)
ws_cur.freeze_panes = c1
cont_return = [wb_cur]
return cont_return
class Content(object):
"""通过project实例创建目录"""
# 设置公用样式
title_font = Font(name='宋体', size=24, bold=True)
header_font = Font(name='仿宋_GB2312', size=14, bold=True)
normal_font = Font(name='仿宋_GB2312', size=14)
header_border = Border(bottom=Side(style='medium'))
normal_border = Border(bottom=Side(style='thin', color='80969696'))
ctr_alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
left_alignment = Alignment(horizontal='left',
vertical='center',
wrap_text=True,
indent=1)
margin = PageMargins()
def __init__(self, project):
self.project = project
self.wb = Workbook()
self.ws_lob = None
self.ws_tech = None
self.ws_qual = None
self.ws_eco = None
self.ws_com = None
def create_all(self):
"""生成目录总方法"""
self.create_qual()
self.create_com()
self.create_eco()
self.create_tech()
self.create_lob()
self.wb.save('目录—{}.xlsx'.format(self.project.name))
def create_lob(self):
"""创建投标函目录"""
self.ws_lob = self.wb.create_sheet('投标函', 0)
col_titles = ['序号', '内容', '页码']
content = [['一', '投标函'], ['二', '法定代表人身份证明书'], ['三', '法定代表人授权书'],
['四', '守法廉政承诺书'], ['五', '企业内控承诺'], ['六', '投标保证金银行保函']]
col_width = [10, 60, 10]
col_num = 3
row_num = 8
# 初始化表格
for i in range(row_num):
for j in range(col_num):
cell_now = self.ws_lob.cell(row=i + 1, column=j + 1)
self.ws_lob.row_dimensions[i + 1].height = 45 # 修改行高
if i > 0:
if i == 1:
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
else:
cell_now.font = Content.normal_font
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2][1]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = content[i - 2][0]
elif j == 2:
cell_now.value = i - 1
if i != row_num - 1:
cell_now.border = Content.normal_border
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_lob.column_dimensions[letters[i]].width = col_width[i]
# 填写抬头
self.ws_lob.merge_cells('A1:C1')
header = self.ws_lob['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_lob.row_dimensions[1].height = 50
# 打印设置
self.ws_lob.print_options.horizontalCentered = True
self.ws_lob.print_area = 'A1:C9'
self.ws_lob.page_setup.fitToWidth = 1
self.ws_lob.page_margins = Content.margin
def create_tech(self):
"""创建技术标目录"""
self.ws_tech = self.wb.create_sheet('技术标', 0)
col_titles = ['序号', '内容', '页码']
# 存放固定内容
content = [
'技术偏离表', '物资选型部分', '供货清单（一）中各项物资选型一览表', '供货清单（一）中各项物资相关资料', '包装方案',
'运输相关文件', '物资自检验收方案', '物资第三方检验相关文件', '对外实施工作主体落实承诺书',
'物资生产企业三体系认证相关资料', '物资节能产品认证相关资料', '物资环境标志产品认证相关资料'
]
# 存放中文序号
num = [
'一', '二', '三', '四', '五', '六', '七', '八', '九', '十', '十一', '十二', '十三'
]
col_width = [10, 60, 10]
col_num = 3
# 确定行数
com_num = len(self.project.commodities)
row_num = com_num + 14
if self.project.is_cc:
row_num += 1
content.insert(9, '来华培训方案及相关材料')
if self.project.is_qa:
row_num += 1
content.insert(9, '售后服务方案及相关材料')
if self.project.is_tech:
row_num += 1
content.insert(9, '技术服务方案及相关材料')
# 创建专用样式
third_alignment = Alignment(horizontal='left',
vertical='center',
wrap_text=True,
indent=3)
third_font = Font(name='仿宋_GB2312', size=12)
# 填写抬头
self.ws_tech.merge_cells('A1:C1')
header = self.ws_tech['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_tech.row_dimensions[1].height = 50
# 初始化表格,双循环扫描先行后列扫描表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_tech.cell(row=i + 1, column=j + 1)
self.ws_tech.row_dimensions[i + 1].height = 30 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
elif 1 < i < 4: # 头两行
cell_now.font = Content.normal_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = num[i - 2]
elif i == 4 or i == 5: # 3、4行
cell_now.font = Content.normal_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2]
else:
cell_now.alignment = Content.ctr_alignment
elif 5 < i < com_num + 6: # 填写物资名称
cell_now.font = third_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = third_alignment
cell_now.value = '{}、{}'.format(
i - 5, self.project.commodities[i - 5][0])
else:
cell_now.alignment = Content.ctr_alignment
else: # 其余的一起填写
cell_now.font = Content.normal_font
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - com_num - 2]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = num[i - com_num - 4]
if i != row_num - 1:
cell_now.border = Content.normal_border
# for i in (9, 11): # 修改两处格式
# self.ws_tech.cell(row=com_num + i, column=2).font = third_font
# self.ws_tech.cell(
# row=com_num + i,
# column=2).alignment = third_alignment
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_tech.column_dimensions[letters[i]].width = col_width[i]
# 打印设置
self.ws_tech.print_options.horizontalCentered = True
self.ws_tech.print_area = 'A1:C{}'.format(row_num)
self.ws_tech.page_setup.fitToWidth = 1
self.ws_tech.page_margins = PageMargins(top=0.5,
bottom=0.5,
header=0.1,
footer=0.1)
def create_eco(self):
self.ws_eco = self.wb.create_sheet('经济标', 0)
col_titles = ['序号', '内容', '页码']
content = ['投标报价总表', '物资对内分项报价表', '《供货清单（一）》中各项物资增值税退抵税额表']
col_width = [10, 60, 10]
num = ['一', '二', '三', '四', '五', '六', '七', '八', '九', '十']
col_num = 3
# 确定行数
row_num = 5
if len(self.project.qc) == 0:
row_num += 1
content.insert(3, '非法检物资检验一览表')
else:
if len(self.project.qc) == len(self.project.commodities):
row_num += 1
content.insert(3, '法检物资检验一览表')
else:
row_num += 2
content.insert(3, '非法检物资检验一览表')
content.insert(3, '法检物资检验一览表')
if self.project.is_cc:
row_num += 1
content.insert(3, '来华培训费报价表')
if self.project.is_tech:
row_num += 1
content.insert(3, '技术服务费报价表')
# 初始化表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_eco.cell(row=i + 1, column=j + 1)
self.ws_eco.row_dimensions[i + 1].height = 45 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
else: # 其余的一起填写
cell_now.font = Content.normal_font
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2]
else:
cell_now.alignment = Content.ctr_alignment
if i != row_num - 1:
cell_now.border = Content.normal_border
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_eco.column_dimensions[letters[i]].width = col_width[i]
# 填写序号
# self.ws_eco['A3'] = '经济标部分'
# self.ws_eco['A3'].font = Content.header_font
# if not self.project.is_lowprice:
# self.ws_eco_com['A{}'.format(row_num - 4)] = '商务标部分'
# self.ws_eco_com['A{}'.format(
# row_num - 4)].font = Content.header_font
# 填写序号
for i in range(3, row_num + 1):
self.ws_eco['A{}'.format(i)] = num[i - 3]
# 合并小标题
# self.ws_eco.merge_cells('A3:C3')
# if not self.project.is_lowprice:
# self.ws_eco.merge_cells('A{0}:C{0}'.format(row_num - 4))
# 填写抬头
self.ws_eco.merge_cells('A1:C1')
header = self.ws_eco['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_eco.row_dimensions[1].height = 50
# 打印设置
self.ws_eco.print_options.horizontalCentered = True
self.ws_eco.print_area = 'A1:C{}'.format(row_num)
self.ws_eco.page_setup.fitToWidth = 1
# self.ws_eco.page_margins = PageMargins(
# top=0.5, bottom=0.5, header=0.1, footer=0.1)
def create_com(self):
self.ws_com = self.wb.create_sheet('商务标', 0)
col_titles = ['序号', '内容', '页码']
content = [['一', '同类物资出口业绩一览表及报关单'], ['二', '向受援国出口货物业绩一览表及报关单']]
col_width = [10, 60, 10]
col_num = 3
row_num = 4
# # 创建专用样式
# special_alignment = Alignment(
# horizontal='left',
# vertical='center',
# wrap_text=True,
# indent=0)
# special_font = Font(name='仿宋_GB2312', size=12)
# 初始化表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_com.cell(row=i + 1, column=j + 1)
self.ws_com.row_dimensions[i + 1].height = 45 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
else: # 其余
cell_now.font = Content.normal_font
if i != row_num - 1:
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2][1]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = content[i - 2][0]
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_com.column_dimensions[letters[i]].width = col_width[i]
# 填写抬头
self.ws_com.merge_cells('A1:C1')
header = self.ws_com['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_com.row_dimensions[1].height = 50
# 打印设置
self.ws_com.print_options.horizontalCentered = True
self.ws_com.print_area = 'A1:C{}'.format(row_num)
self.ws_com.page_setup.fitToWidth = 1
self.ws_com.page_margins = PageMargins(top=0.5,
bottom=0.5,
header=0.1,
footer=0.1)
def create_qual(self):
self.ws_qual = self.wb.create_sheet('资格后审', 0)
col_titles = ['序号', '内容', '页码']
content = [['一', '资格后审申请函'], ['二', '证明文件']]
content2 = [
'投标人的法人营业执照（复印件）和援外物资项目实施企业资格证明文件（复印件）', '法定代表人证明书和授权书（复印件）',
'无重大违法记录的声明函', '财务审计报告（复印件）', '依法缴纳社会保障资金的证明和税收的证明（复印件）',
'特殊物资经营资格、资质许可证明文件（复印件）', '关联企业声明', '其它'
]
col_width = [10, 60, 10]
col_num = 3
row_num = 12
# 创建专用样式
special_alignment = Alignment(horizontal='left',
vertical='center',
wrap_text=True,
indent=0)
special_font = Font(name='仿宋_GB2312', size=12)
# 初始化表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_qual.cell(row=i + 1, column=j + 1)
self.ws_qual.row_dimensions[i + 1].height = 45 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
elif 1 < i < 4: # 头两行
cell_now.font = Content.normal_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2][1]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = content[i - 2][0]
else: # 其余的一起填写
cell_now.font = special_font
if j == 1:
cell_now.alignment = special_alignment
cell_now.value = '{}、{}'.format(i - 3, content2[i - 4])
else:
cell_now.alignment = Content.ctr_alignment
if i != row_num - 1:
cell_now.border = Content.normal_border
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_qual.column_dimensions[letters[i]].width = col_width[i]
# 填写抬头
self.ws_qual.merge_cells('A1:C1')
header = self.ws_qual['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_qual.row_dimensions[1].height = 50
# 打印设置
self.ws_qual.print_options.horizontalCentered = True
self.ws_qual.print_area = 'A1:C{}'.format(row_num)
self.ws_qual.page_setup.fitToWidth = 1
self.ws_qual.page_margins = PageMargins(top=0.5,
bottom=0.5,
header=0.1,
footer=0.1)