def auto_width_column(df):
for i, col in enumerate(df.columns):
# find length of column i
column_len = df[col].astype(str).str.len().max()
# Setting the length if the column header is larger than the max column value length
column_len = max(column_len, len(col)) + 2
worksheet.set_column(i, i, column_len)
def add_sheet(self, workbook, sheet: str, data: list):
worksheet = workbook.add_worksheet(name=sheet)
worksheet.autofilter(0, 0, len(data), len(data[0]) - 1)
for index in range(len(data[0])):
worksheet.set_column(
index,
index,
width=self.get_column_width(data, index),
)
bold = workbook.add_format({"bold": 1})
worksheet.set_row(0, cell_format=bold)
self.write_rows(worksheet, data)
def add_org_unit_sheet(self, workbook, org_units):
worksheet = workbook.add_worksheet(name='Org')
rows = [(org_unit['uuid'], org_unit['combined'])
for org_unit in org_units]
worksheet.set_column(0,
0,
width=self.get_column_width(org_units, 'uuid'))
worksheet.set_column(1,
1,
width=self.get_column_width(
org_units, 'combined'))
rows.insert(0, ('UUID', 'Navn'))
self.write_rows(worksheet, rows)
def add_indsigt_and_udfoerende_sheet(self, workbook, kle_numbers,
org_units):
for sheet_name in ['Indsigt', 'Udførende']:
worksheet = workbook.add_worksheet(name=sheet_name)
rows = [('EnhedNavn', 'KLE')]
worksheet.data_validation(*self.get_org_unit_validation('A'))
worksheet.data_validation(*self.get_kle_validation('B'))
worksheet.set_column(0,
0,
width=self.get_column_width(
org_units, 'combined'))
worksheet.set_column(1,
1,
width=self.get_column_width(
kle_numbers, 'name'))
self.write_rows(worksheet, rows)
def autofit_worksheet_columns(worksheet: xlsxwriter.worksheet,
df: pd.DataFrame,
autofit_header=True):
offset = 1
if isinstance(df.index, pd.MultiIndex):
offset = df.index.nlevels
#Iterate through each column and set the width to the max length of values in that column.
for i, col in enumerate(df.columns):
column_len = df[col].astype(str).str.len().max()
# Considers the header if the flag is set.
if autofit_header:
column_len = max(column_len, len(col)) + 2
worksheet.set_column(i + offset, i + offset, column_len)
# Also, format the index as well.
if not isinstance(df.index, pd.MultiIndex):
max_index = df.index.astype(str).str.len().max()
worksheet.set_column(0, 0, max_index)
worksheet.freeze_panes(1, 1)
def add_ansvarlig_sheet(self, workbook, kle_numbers, org_units):
worksheet = workbook.add_worksheet(name='Ansvarlig')
def calculate_level(kle_number: str):
"""
We calculate the level, by how many dots are in the key
E.g. 00 is 1, 00.01 is 2, 00.01.32 is 3
"""
return str(kle_number.count('.') + 1)
rows = [(kle['level'], kle['user_key'], kle['name'], '')
for kle in kle_numbers]
rows.insert(0, ('Niveau', 'EmneNr', 'EmneTitel', 'EnhedNavn'))
worksheet.data_validation(*self.get_org_unit_validation(column='D'))
worksheet.set_column(1,
1,
width=self.get_column_width(
kle_numbers, 'user_key'))
worksheet.set_column(2,
2,
width=self.get_column_width(kle_numbers, 'name'))
worksheet.set_column(3,
3,
width=self.get_column_width(
org_units, 'combined'))
self.write_rows(worksheet, rows)
def writeHeaderLine(worksheet, department, semesterName, subjectName,
col1Width, col2Width, col3Width, orm: ORM, row) -> int:
"""
Writes the worksheet header with creation date and department name.
Also sets the width of the first three columns.
Args:
worksheet: The worksheet to write the header in.
department: The name of the timetable's department.
semesterName: The name of the semester.
subjectName: The subject of the worksheet's timetable. e.g. Teachers or Students.
col1Width: Width of the first column.
col2Width: Width of the second column.
col3Width: Width of the third column.
orm: The ORM file with all objects of the timetable.
row: The last row in the worksheet.
Returns: The next row after the written timetables.
"""
columnCount = len(orm.getTimeslots()) + 3
# Write row with the current date.
worksheet.write(row, 0, "Stand:", formatMap["simpleBoldFormat"])
worksheet.write_datetime(row, 1, date.today(),
formatMap["simpleBoldGermanDateFormat"])
row += 1
# Main header line.
# Add a background to the header row.
makeBackground(worksheet, columnCount, row, formatMap["mainHeaderFormat"])
# Set column widths.
worksheet.set_column(0, 0, col1Width)
worksheet.set_column(1, 1, col2Width)
worksheet.set_column(2, 2, col3Width)
worksheet.set_column(
3, columnCount,
7) # Width of the columns that contain the timetable Lessons.
# Write main header contents.
worksheet.write(row, 0, "Stundenplanung", formatMap["mainHeaderFormat"])
worksheet.write(row, 3, "Fachbereich " + department,
formatMap["mainHeaderFormat"])
worksheet.write(row, 8, subjectName, formatMap["mainHeaderFormat"])
# Set row height.
worksheet.set_row(row, 27.75)
row += 1
# Weekday and timeslot line.
row = writeDayAndTimeLine(worksheet, semesterName, orm, row)
return row
def add_kle_sheet(self, workbook: xlsxwriter.Workbook, kle_numbers: list):
worksheet = workbook.add_worksheet(name='KLE')
rows = [(kle['uuid'], kle['user_key'], kle['name'])
for kle in kle_numbers]
rows.insert(0, ('UUID', 'EmneNr', 'EmneTitel'))
worksheet.set_column(0,
0,
width=self.get_column_width(kle_numbers, 'uuid'))
worksheet.set_column(1,
1,
width=self.get_column_width(
kle_numbers, 'user_key'))
worksheet.set_column(2,
2,
width=self.get_column_width(kle_numbers, 'name'))
self.write_rows(worksheet, rows)
dec_fmt = workbook.add_format({'num_format': '0.000'})
percent_fmt = workbook.add_format({'num_format': '0.0%'})
summary.to_excel(writer, sheet_name='Summary')
sum_sheet = writer.sheets['Summary']
autofit_worksheet_columns(sum_sheet, summary)
# Set decimal and percentage formatting
for label in ['idf1', 'idp', 'precision', 'motp']:
column_num = summary.columns.get_loc(label) + 1
column_len = max(6, len(label)) + 2
sum_sheet.set_column(column_num, column_num, column_len, dec_fmt)
for label in ['mota']:
column_num = summary.columns.get_loc(label) + 1
column_len = max(8, len(label)) + 2
sum_sheet.set_column(column_num, column_num, column_len, percent_fmt)
for i in range(len(tests)):
results_meta[i].to_excel(writer, sheet_name=tests[i])
worksheet = writer.sheets[tests[i]]
autofit_worksheet_columns(worksheet, results_meta[i])
d_column = results_meta[i].columns.get_loc('D') + 2
worksheet.set_column(d_column, d_column, 8, dec_fmt)
sp_breakdown.to_excel(writer, sheet_name='Species Breakdown')
autofit_worksheet_columns(writer.sheets['Species Breakdown'], sp_breakdown)
writer.save()
print("\nWrote output to '{OUT}'.\n".format(OUT=args.output_path))
def saveSheet(filename='', unit='', table=QTableWidget):
workbook = xlsxwriter.Workbook(filename)
worksheet = workbook.add_worksheet(table.objectName())
if worksheet.get_name() == 'tableWidget':
#设置页面宽度
worksheet.set_column('A:A', 13)
worksheet.set_column('B:B', 110)
# worksheet.set_column('C:C', 90)
titleFormat = workbook.add_format()
# titleFormat.set_bold() # 设置粗体字
titleFormat.set_font_size(18) # 设置字体大小为18
titleFormat.set_font_name('黑体') # 设置字体样式为雅黑
titleFormat.set_align('center') # 设置水平居中对齐
titleFormat.set_align('vcenter') # 设置垂直居中对齐
#合并title区域单元格
worksheet.merge_range(0, 0, 0, 1, "历史概况履历表", titleFormat)
title2Format = workbook.add_format()
title2Format.set_bold()
title2Format.set_font_name('宋体') # 设置字体样式为雅黑
title2Format.set_font_size(10) # 设置字体大小为10
title2Format.set_align('center')
title2Format.set_align('vcenter') # 设置垂直居中对齐
# title2left = "部门（工队）：定西供电工队 肃技-网1"
worksheet.merge_range(1, 0, 1, 1, fillspaces('部门（工队）' + unit, '肃技-网1'),
title2Format)
title3Format = workbook.add_format()
title3Format.set_bold()
title3Format.set_font_name('宋体') # 设置字体样式为雅黑
title3Format.set_font_size(10) # 设置字体大小为10
title3Format.set_align('center')
title3Format.set_align('vcenter') # 设置垂直居中对齐
title3Format.set_border()
worksheet.write(2, 0, '年度', title3Format)
worksheet.write(2, 1, '概况记录', title3Format)
# 再添加一个样式rowFormat，将作为数据行的格式
yearFormat = workbook.add_format()
yearFormat.set_font_size(10)
yearFormat.set_font_name('宋体')
yearFormat.set_align('center')
yearFormat.set_align('vcenter')
# yearFormat.set_align('vjustify')
yearFormat.set_border()
yearFormat.set_text_wrap()
profileFormat = workbook.add_format()
profileFormat.set_font_size(10)
profileFormat.set_font_name('宋体')
profileFormat.set_align('left')
# profileFormat.set_align('vjustify')
profileFormat.set_border()
profileFormat.set_text_wrap() # 设置自动换行
rowFormat = workbook.add_format()
rowFormat.set_text_wrap()
for row in range(table.rowCount()):
for column in range(table.columnCount()):
item = QTableWidgetItem(table.item(row, column))
# print(item.text())
# worksheet.write(row + 3, column, item.text(),merge_format)
if column == 0:
worksheet.write(row + 3, column, item.text(), yearFormat)
elif column == 1:
# worksheet.merge_range(row + 3, 1, row + 3, 2, item.text(),merge_format)
worksheet.write(row + 3, column, item.text(),
profileFormat)
else:
return Exception
pass
elif worksheet.get_name() == 'tableWidget_2':
pass
elif worksheet.get_name() == 'tableWidget_3':
pass
elif worksheet.get_name() == 'tableWidget_4':
pass
elif worksheet.get_name() == 'tableWidget_5':
pass
elif worksheet.get_name() == 'tableWidget_6':
pass
elif worksheet.get_name() == 'tableWidget_7':
pass
elif worksheet.get_name() == 'tableWidget_8':
pass
elif worksheet.get_name() == 'tableWidget_9':
pass
workbook.close()
return 0
def testsheet():
workbook = xlsxwriter.Workbook('d:\\test.xlsx')
worksheet = workbook.add_worksheet('tableWidget')
if worksheet.get_name() == 'tableWidget':
#设置页面宽度
worksheet.set_column('A:A', 13)
worksheet.set_column('B:B', 110)
# worksheet.set_column('C:C', 90)
# add_format() 为当前workbook添加一个样式名为titleFormat
titleFormat = workbook.add_format()
# titleFormat.set_bold() # 设置粗体字
titleFormat.set_font_size(18) # 设置字体大小为18
titleFormat.set_font_name('黑体') # 设置字体样式为雅黑
titleFormat.set_align('center') # 设置水平居中对齐
titleFormat.set_align('vcenter') # 设置垂直居中对齐
# 将titleFormat应用在第一行，此行为标题
# worksheet.set_row(0, None, titleFormat)
#合并title区域单元格
worksheet.merge_range(0, 0, 0, 1, "历史概况履历表", titleFormat)
# title2RightFormat = workbook.add_format()
# title2RightFormat.set_bold()
# title2RightFormat.set_font_name('宋体') # 设置字体样式为雅黑
# title2RightFormat.set_font_size(10) # 设置字体大小为10
# title2RightFormat.set_align('right')
# title2RightFormat.set_align('vcenter') # 设置垂直居中对齐
# # worksheet.set_row(2, None, title2RightFormat)
# title2right = "肃技-网1"
# worksheet.write(1, 1, title2right, title2RightFormat)
title2Format = workbook.add_format()
title2Format.set_bold()
title2Format.set_font_name('宋体') # 设置字体样式为雅黑
title2Format.set_font_size(10) # 设置字体大小为10
title2Format.set_align('center')
title2Format.set_align('vcenter') # 设置垂直居中对齐
# title2left = "部门（工队）：" + "定西供电工队"
title2left = "部门（工队）：定西供电工队 肃技-网1"
# worksheet.write(1, 0, title2left, title2Format)
# worksheet.set_row(1, None, title2Format)
worksheet.merge_range(1, 0, 1, 1, title2left, title2Format)
title3Format = workbook.add_format()
title3Format.set_bold()
title3Format.set_font_name('宋体') # 设置字体样式为雅黑
title3Format.set_font_size(10) # 设置字体大小为10
title3Format.set_align('center')
title3Format.set_align('vcenter') # 设置垂直居中对齐
title3Format.set_border()
worksheet.write(2, 0, '年度', title3Format)
worksheet.write(2, 1, '概况记录', title3Format)
# 再添加一个样式rowFormat，将作为数据行的格式
rowFormat = workbook.add_format()
rowFormat.set_font_size(10)
rowFormat.set_font_name('Microsoft yahei')
rowFormat.set_align('left')
rowFormat.set_align('vcenter')
rowFormat.set_text_wrap() # 设置自动换行
# rangetable = 'leftupcorner:rightdowncorner', 如'A1:D20', 用左上角的单元格编号与右下角的单元格编号标识表格中的一块矩形区域。
pass
workbook.close()
df3.to_excel(writer, sheet_name=sheetname3, index=False)
# create function for excel column auto width
def auto_width_column(df):
for i, col in enumerate(df.columns):
# find length of column i
column_len = df[col].astype(str).str.len().max()
# Setting the length if the column header is larger than the max column value length
column_len = max(column_len, len(col)) + 2
worksheet.set_column(i, i, column_len)
### Excel formatting
workbook = writer.book
cellformat = workbook.add_format({'num_format': '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'})
worksheet = writer.sheets[sheetname1]
worksheet.set_column('H:H', cell_format=cellformat)
worksheet.set_column('J:J', cell_format=cellformat)
auto_width_column(df1)
worksheet = writer.sheets[sheetname2]
worksheet.set_column('M:M', cell_format=cellformat)
auto_width_column(df2)
worksheet = writer.sheets[sheetname3]
auto_width_column(df3)
### Save the Excel Workbook
writer.save()
### Email sending code
email_user = '******'