def style_thead():
style = NamedStyle(name='thead')
style.font = create_font(bold=True, color='00FFFFFF')
style.fill = PatternFill(patternType='solid', fgColor='00000000')
side = Side(style='thin', color='00FFFFFF')
style.border = Border(left=side, right=side, top=side, bottom=side)
return style
def join_Database():
xlsx_to_formated_xlsx = 'media/qbreports/uploads/xlsx_to_formated_xlsx.xlsx'
downlaod_file_name = 'media/qbreports/uploads/custom_qb_report.xlsx'
joined_table_file_name = pandas_df_join()
csv_to_xlsx, google_sheet = finial_file_creation(joined_table_file_name)
dfUpdated = pd.read_csv(csv_to_xlsx, encoding='latin-1')
dfgdoc = pd.read_csv(google_sheet, encoding='latin-1')
print("creating xlsx files")
wb = Workbook()
ws = wb.active
ws.title = 'Matching_Updated'
ws2 = wb.create_sheet()
ws2.title = 'Vendor Price Check Gdoc'
bold = NamedStyle(name='bold')
bold.font = Font(bold=True)
wb.add_named_style(bold)
cell = WriteOnlyCell(ws)
cell.style = 'bold'
greenFill = PatternFill(start_color='32CD32',
end_color='32CD32',
fill_type='solid')
for row in dataframe_to_rows(dfUpdated, index=False, header=True):
ws.append(row)
for row in dataframe_to_rows(dfgdoc, index=False, header=True):
ws2.append(row)
wb.save(xlsx_to_formated_xlsx)
wb = load_workbook(filename=xlsx_to_formated_xlsx)
ws = wb.active
for row in ws.iter_rows():
if row[1].value == "Vendor Name":
for cell in row:
ws[str(cell.coordinate)].font = Font(bold=True)
if row[1].value == None:
for cell in row:
if cell.value != None:
ws[str(cell.coordinate)].fill = greenFill
if row[1].value != None:
for cell in row:
ws[str(cell.coordinate)].font = Font(bold=True)
wb.save(downlaod_file_name)
print("created xlsx files")
qb_Import_File_delete_everything()
return downlaod_file_name
def _set_style(self):
my_style = NamedStyle(name="my_style")
my_style.font = Font(name='Calibri')
bd = Side(style='thin')
my_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)
self.wb.add_named_style(my_style)
return "my_style"
def __init__(self, excel_name, name):
self.excel_name = "{}.xlsx".format(excel_name)
self.workbook = openpyxl.Workbook()
self.worksheet = self.workbook.active
self.worksheet.title = name
border_style = NamedStyle(name="border_style")
bian = Side(style="medium", color="000000")
border = Border(top=bian, bottom=bian, left=bian, right=bian)
border_style.border = border
alignment = Alignment(horizontal="center", vertical="center")
border_style.alignment = alignment
self.border_style = border_style
self.workbook.add_named_style(border_style)
title_style = NamedStyle(name="title_style")
ft = Font(name="Noto Sans CJK SC Regular",
color="FFFFFF",
size=11,
b=False)
fill = PatternFill("solid", fgColor="00A3FF")
title_style.font = ft
title_style.fill = fill
title_style.border = border
title_style.alignment = alignment
self.title_style = title_style
self.workbook.add_named_style(title_style)
def create_font_style():
border_style = NamedStyle(name="BorderAndFont")
border_style.font = Font(name='Times New Roman', size=10, color='000000')
bd = Side(style='thin', color='000000')
border_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)
border_style.alignment.wrap_text = True
return border_style
def style_base(name):
style = NamedStyle(name=name)
style.font = create_font(size=9)
style.alignment = Alignment(vertical='top', wrap_text=True)
side = Side(style='thin')
style.border = Border(left=side, right=side, top=side, bottom=side)
return style
def dispansery_plan_base(ws1, d1, d2):
style_border = NamedStyle(name="style_border_ca")
bd = Side(style='thin', color="000000")
style_border.border = Border(left=bd, top=bd, right=bd, bottom=bd)
style_border.font = Font(bold=True, size=14)
style_border.alignment = Alignment(wrap_text=True,
horizontal='justify',
vertical='center')
ws1.cell(row=1, column=1).value = 'Д-учет план:'
ws1.cell(row=2, column=1).value = f'{d1} {d2}'
columns = [
('№ карты', 13),
('ФИО', 55),
('Дата рождения', 15),
('Диагноз', 65),
('Месяц план', 20),
]
for idx, column in enumerate(columns, 1):
ws1.cell(row=4, column=idx).value = column[0]
ws1.column_dimensions[get_column_letter(idx)].width = column[1]
ws1.cell(row=4, column=idx).style = style_border
return ws1
def style_base(name):
style = NamedStyle(name=name)
style.font = create_font(size=9)
style.alignment = Alignment(vertical='top', wrap_text=True)
side = Side(style='thin')
style.border = Border(left=side, right=side, top=side, bottom=side)
return style
def write_to_spreadsheet(self):
'''Formats and writes data to spreadsheet.'''
wb = Workbook()
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=False, size=11)
bd = Side(style='thin', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight) # Register named style
sh1 = wb.active
sh1.title = 'Purchasing Exceptions'
sh1.append(['Part Number'])
sh1['A1'].font = Font(bold=True, size=11)
sh1['A1'].border = Border(left=bd, top=bd, right=bd, bottom=bd)
sh1['A1'].alignment = Alignment(horizontal='center')
rnum = 2
sh1.column_dimensions['A'].width = (30)
# col_width2 = 0
for item in self.part_list:
# sh1_tool_list_data = item
sh1.cell(row=rnum, column=1).value = item
sh1.cell(row=rnum, column=1).style = 'highlight'
# if len(str(sh1_description)) > col_width2:
# col_width2 = len(str(sh1_description))
rnum += 1
# sh1.column_dimensions['A'].width = (col_width2 * 1.125)
save_name = (('{}/Purchasing Exception Report.xlsx').format(self.idir))
self.saved_as_string = ('Results file: {}').format(save_name)
self.file_listbox.insert(tk.END, self.count_string)
self.file_listbox.insert(tk.END, self.saved_as_string)
self.file_listbox.see(tk.END)
wb.save(save_name)
os.startfile(save_name)
def paint_par(ws,
longHeader,
lenData,
num_col,
row=11,
col_money=[],
col_porc=[],
col_nro_dec=[],
col_nro_int=[]):
rowPar = NamedStyle(name="rowPar")
rowPar.fill = PatternFill("solid", fgColor="E0ECF8")
for column in range(1, longHeader + 1):
column_letter = get_column_letter(column)
for rowD in range(row, lenData + row + 1):
if (rowD % 2 == 0):
ws[column_letter + str(rowD)].style = rowPar
if (column > num_col):
ws[column_letter + str(rowD)].number_format = '#,##0.00'
if (column in col_nro_int):
ws[column_letter + str(rowD)].number_format = '#0'
if (column in col_nro_dec):
ws[column_letter + str(rowD)].number_format = '#,##0.00'
if (column in col_money):
ws[column_letter + str(rowD)].number_format = '#,##0.00 $'
if (column in col_porc):
ws[column_letter + str(rowD)].number_format = '#,##0.00 %'
return ws
def total_summary(ws,
listTotal,
numberRow,
lonTableHeader,
font_color="FFFFFF",
fill_color="afbcd7",
formatPercent=[],
formatMoney=[],
formatNumberInteger=[],
formatNumberDecimal=[]):
ws.append(listTotal)
totalOpe = NamedStyle(name="totalOpe")
totalOpe.alignment = Alignment(horizontal='center')
totalOpe.fill = PatternFill("solid", fgColor=fill_color)
totalOpe.font = Font(color=font_color, size=12, bold=True)
for row in ws.iter_rows('A' + str(numberRow) + ':' +
get_column_letter(lonTableHeader) +
str(numberRow)):
for cell in row:
cell.style = totalOpe
for col_letter in formatPercent:
ws[col_letter + str(numberRow)].number_format = '#,##0.00 %'
for col_letter in formatMoney:
ws[col_letter + str(numberRow)].number_format = '#,##0.00 $'
for col_letter in formatNumberDecimal:
ws[col_letter + str(numberRow)].number_format = '#,##0.00'
for col_letter in formatNumberInteger:
ws[col_letter + str(numberRow)].number_format = '#0'
return ws
def style_thead():
style = NamedStyle(name='thead')
style.font = create_font(bold=True, color='00FFFFFF')
style.fill = PatternFill(patternType='solid', fgColor='00000000')
side = Side(style='thin', color='00FFFFFF')
style.border = Border(left=side, right=side, top=side, bottom=side)
return style
def save_price(a):
wb = openpyxl.Workbook()
wb.guess_types = True
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=15)
highlight.alignment = Alignment(horizontal='center', vertical='center')
wb.add_named_style(highlight)
for i in range(len(a)):
salename = a[i].get('sale')
url = a[i].get('url')
wb.create_sheet(index=i, title=salename)
wb[salename]['A1'] = "{}型号".format(salename)
wb[salename]['B1'] = "价格"
wb[salename]['A1'].style = highlight
wb[salename]['B1'].style = highlight
wb[salename].column_dimensions['A'].width = 50
c = 2
for each in find_url(url)[0]:
wb[salename].append(each)
ws = wb[salename]['A{}'.format(c)]
ws1 = wb[salename]['D{}'.format(c)]
ws.alignment = Alignment(horizontal='center', vertical='center')
ws1.alignment = Alignment(horizontal='center', vertical='center')
c += 1
wb[salename]['C1'] = find_url(url)[1]
wb[salename].merge_cells('C1:D1')
wb[salename]['C1'].style = highlight
time.sleep(0.5)
wb.save('报价.xlsx')
def patologistology_buh_base(ws1):
style_border = NamedStyle(name="style_border")
bd = Side(style='thin', color="000000")
style_border.border = Border(left=bd, top=bd, right=bd, bottom=bd)
style_border.font = Font(bold=True, size=11)
style_border.alignment = Alignment(wrap_text=True,
horizontal='center',
vertical='center')
ws1.cell(row=2, column=1).value = 'Период:'
columns = [
('Медицинская организация ', 36),
('ФИО пациента ', 25),
('Дата рождения ', 17),
('Номер полиса ОМС ', 30),
('СНИЛС ', 15),
('Дата регистрации', 17),
('Код МКБ 10 заключение ', 12),
('Источник ', 12),
('Код оплаты (категория)', 30),
('Цель ', 26),
('Код мед услуги и категория сложности', 35),
('Направление', 30),
]
for idx, column in enumerate(columns, 1):
ws1.cell(row=4, column=idx).value = column[0]
ws1.column_dimensions[get_column_letter(idx)].width = column[1]
ws1.cell(row=4, column=idx).style = style_border
return ws1
def make_style(wb): # создаем именованный стиль для серой заливки:
from openpyxl.styles import NamedStyle, Border, Side, PatternFill
ns = NamedStyle(name='Grey')
ns.fill = PatternFill("solid", fgColor="909090")
border = Side()
ns.border = Border(left=border, top=border, right=border, bottom=border)
wb.add_named_style(ns)
def create_output_file(IP_list, output):
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = "Reputations"
highlight = NamedStyle(name="highlight")
highlight.font = Font(name='Verdana',
size=9,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FFFFFF')
highlight.fill = PatternFill(fill_type="solid", bgColor='FF000000')
workbook.add_named_style(highlight)
cell_obj = sheet.cell(row=1, column=1)
cell_obj.value = "IP"
sheet.merge_cells('A1:A2')
sheet['A1'].style = highlight
row = 3
col = 1
for IP in IP_list:
cell_obj = sheet.cell(row=row, column=col)
cell_obj.value = IP
row = row + 1
workbook.save(output + ".xlsx")
def create(data):
workbook = Workbook()
sheet = workbook.active
file_name = 'utils/tugs_scheduler.xlsx'
# setting
sheet.title = "scheduler"
sheet["A1"] = "ID"
sheet["B1"] = "REMOLCADOR"
sheet["C1"] = "ACTUAL"
sheet["D1"] = "PRÓXIMA"
sheet["E1"] = "TIPO"
# Let's create a style template for the header row
header = NamedStyle(name="header")
header.font = Font(bold=True)
header.border = Border(bottom=Side(border_style="thin"))
header.alignment = Alignment(horizontal="center", vertical="center")
header_row = sheet[1]
for cell in header_row:
cell.style = header
# put data into the file
for item in data:
sheet.append(item)
# validate date (coming date <= today date)
# whether validation is TRUE, add RED color to date
big_red_text = Font(color=colors.BLUE)
for i in sheet["D"][1:]:
if i.value <= datetime.date.today():
i.font = big_red_text
i.value = i.value.strftime("%d/%m/%Y")
# save data into the file
workbook.save(filename=file_name)
def custom_research_base(ws1, d1, d2, result_query, research_title):
style_border = NamedStyle(name="style_border_ca")
bd = Side(style='thin', color="000000")
style_border.border = Border(left=bd, top=bd, right=bd, bottom=bd)
style_border.font = Font(bold=True, size=11)
style_border.alignment = Alignment(wrap_text=True,
horizontal='center',
vertical='center')
ws1.cell(row=1, column=1).value = 'Услуга:'
ws1.cell(row=1, column=2).value = research_title
ws1.cell(row=2, column=1).value = 'Период:'
ws1.cell(row=3, column=1).value = f'c {d1} по {d2}'
columns = [
('Направление', 15),
('Пациент', 45),
('Пол', 10),
('Дата рождения', 26),
('Возраст', 10),
('Адрес', 40),
('Исполнитель', 35),
('Код врача', 15),
]
columns2 = [(i, 25) for i in result_query["custom_fields"]]
columns.extend(columns2)
row = 5
for idx, column in enumerate(columns, 1):
ws1.cell(row=row, column=idx).value = column[0]
ws1.column_dimensions[get_column_letter(idx)].width = column[1]
ws1.cell(row=row, column=idx).style = style_border
return ws1
def test_style_dict_to_style(self):
d = StyleDict({'font-weight': 'bold'})
s = NamedStyle(name='Style {}'.format(len(known_styles) + 1), font=Font(bold=True),
alignment=Alignment(horizontal='general', vertical=None, wrap_text=False))
self.assertEqual(style_dict_to_named_style(d), s)
default_alignment = Alignment(horizontal='general', wrap_text=False)
d = StyleDict({'color': 'ff0000'})
s = NamedStyle(name='Style {}'.format(len(known_styles) + 1), font=Font(bold=False, color='ff0000'),
alignment=default_alignment)
self.assertEqual(style_dict_to_named_style(d), s)
d = StyleDict({'text-align': 'left'})
s = NamedStyle(name='Style {}'.format(len(known_styles) + 1), alignment=Alignment(horizontal='left', wrap_text=False))
self.assertEqual(style_dict_to_named_style(d), s)
d = StyleDict({'background-color': '#ff0000'})
s = NamedStyle(name='Style {}'.format(len(known_styles) + 1), fill=PatternFill(fill_type=FILL_SOLID, start_color="ff0000"),
alignment=default_alignment)
self.assertEqual(style_dict_to_named_style(d), s)
# Make sure we reuse a style when it already exists
known_styles_length = len(known_styles)
d = StyleDict({'background-color': '#ff0000'})
style_dict_to_named_style(d)
self.assertEqual(len(known_styles), known_styles_length)
# Create new one when it doesn't
known_styles_length = len(known_styles)
d = StyleDict({'background-color': '#ff0000', 'class': 'TYPE_NUMERIC'})
style_dict_to_named_style(d)
self.assertEqual(len(known_styles), known_styles_length + 1)
def add_default_styles(workbook):
""" plug in reusable styles """
border = Side(style="thin", color="000000")
thick_border = Side(style="thick", color="000000")
default_style = NamedStyle(name="default_style")
default_style.font = Font(size=15)
default_style.border = Border(top=border,
left=border,
bottom=border,
right=border)
workbook.add_named_style(default_style)
top_row = copy(default_style)
top_row.name = "top_row"
top_row.border = Border(top=thick_border,
left=border,
bottom=border,
right=border)
workbook.add_named_style(top_row)
centered = Alignment(horizontal="center", vertical="center")
for style in (default_style, top_row):
style = copy(style)
style.name = style.name + "_centered"
style.alignment = centered
workbook.add_named_style(style)
style = copy(style)
style.name = style.name + "_no_letter"
style.fill = PatternFill("solid", fgColor="b7b7b7")
workbook.add_named_style(style)
def writeObjectValues(self, objName):
self.write_logger.info(self.wb.named_styles)
if ('style1' not in self.wb.named_styles):
style1 = NamedStyle(name="style1")
style1.font = Font(name='Calibri', size=8, color='FF000000')
style1.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
style1.fill = PatternFill(start_color='BDD7EE',
end_color='BDD7EE',
fill_type='solid')
else:
style1 = self.wb._named_styles['style1']
if ('style2' not in self.wb.named_styles):
style2 = NamedStyle(name="style2")
style2.font = Font(name='Calibri', size=8, color='FF000000')
style2.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
style2.fill = PatternFill(start_color='00B050',
end_color='00B050',
fill_type='solid')
else:
style2 = self.wb._named_styles['style2']
username_json_path = config.project_path + '\\log\\' + config.customer + "\\" + objName + "\\" + objName + '_output.json'
with open(username_json_path, 'r') as username_file:
nokiaData = json.load(username_file)
startingRow = self.dataRowNumber
for nokiaRecord in nokiaData:
cell = self.ws.cell(column=1, row=startingRow)
try:
cell.style = style2
except:
self.write_logger.debug("style already exist ")
self.ws.cell(column=1, row=startingRow, value='ADD')
for nokiaAttr in nokiaRecord:
#self.write_logger.debug(self.validationRules[nokiaAttr])
self.applyValidationToCell(self.ws, startingRow, nokiaAttr)
#Create data validation object for this
cell = self.ws.cell(column=self.cols[nokiaAttr] + 1,
row=startingRow)
try:
cell.style = style1
except:
self.write_logger.debug("style already exist ")
self.ws.cell(column=self.cols[nokiaAttr] + 1,
row=startingRow,
value=nokiaRecord[nokiaAttr])
startingRow = startingRow + 1
#w_worksheet.write(startingRow, self.cols[nokiaAttr], nokiaRecord[nokiaAttr])
self.write_logger.info("Finished writing Excel")
def __cell_style():
#adding style
highlight = NamedStyle(name="highlight")
highlight.font = Font(name='Ebrima',size=8,)
highlight.alignment=Alignment(horizontal='center')
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
return highlight
class CellStyle(object):
NONE = NamedStyle(name='normal')
BLUE = NamedStyle(name='fill_blue')
BLUE.fill = PatternFill(fill_type='solid', fgColor='D9E1F2')
RED = NamedStyle(name='fill_red')
RED.fill = PatternFill(fill_type='solid', fgColor='FCE4D6')
GREEN = NamedStyle(name='fill_green')
GREEN.fill = PatternFill(fill_type='solid', fgColor='CCFFCC')
def normal_style():
style2 = NamedStyle("normal")
style2.font = Font(size=12, color="000000")
# XSSFFont font = (XSSFFont) workbook.CreateFont()
# font.FontHeight = 400.0
# style2.SetFont(font)
style2.alignment = Alignment(horizontal='center', wrap_text=True)
return style2
def make_problems(self,
dirname=default_dir,
name=default_name,
ftype=default_ftype,
size=None,
random_seed=None):
# Define filename
name = FileGenerator.DEFAULT_NAME(name, dirname)
filename = os.path.join(dirname, name + ftype[1:])
# Shuffle query list
random.seed(a=random_seed)
random.shuffle(self.queries)
queries = self.queries[:size if size is None else len(self.queries)]
# Make newfile
newfile = Workbook()
newfile.active.cell(row=1, column=2).value = "Problems"
newfile.active.cell(row=1, column=3).value = "Answers"
for idx, query in enumerate(queries):
newfile.active.cell(row=idx + 2, column=1).value = idx + 1
newfile.active.cell(row=idx + 2, column=2).value = query
# Style assignment
base_style = NamedStyle(name='base_problem',
alignment=self.al,
font=self.ft,
border=self.bd)
index_style = NamedStyle(name='index_problem',
alignment=self.al,
font=self.bold_ft,
border=self.bd)
for ridx, row in enumerate(newfile.active):
for cidx, cell in enumerate(row):
if ridx == 0 or cidx == 0: cell.style = index_style
else: cell.style = base_style
# Cell width and height assignment
side_col_width = 0
query_col_width = 50
answer_col_width = 50
for idx, row in enumerate(newfile.active.values):
side_col_width = max(side_col_width, len(str(row[0])) * 1.2)
query_col_width = max(query_col_width, len(row[1]) * 1.5)
newfile.active.row_dimensions[idx + 1].height = 30
newfile.active.column_dimensions['A'].width = side_col_width
newfile.active.column_dimensions['B'].width = query_col_width
newfile.active.column_dimensions['C'].width = answer_col_width
newfile.save(filename)
return filename
def xmind2xlsx(xmind_file):
"""Convert XMind file to a xlsx file"""
xmind_file = get_absolute_path(xmind_file)
logging.info('Start converting XMind file(%s) to zentao file...',
xmind_file)
testcases = get_xmind_testcase_list(xmind_file)
wb = Workbook()
ws = wb.active
ws.title = 'testcase sheet'
# TODO: modify excel style
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = True
ws.page_setup.fitToWidth = 3
fileheader = [
"产品名称", "所属模块", "功能子模块", "用例标题", "步骤", "预期", "关键词", "优先级", "用例类型",
"适用阶段"
]
testcase_rows = []
for testcase in testcases:
row = gen_a_testcase_row(testcase)
testcase_rows.append(row)
ws.append(fileheader)
for row in testcase_rows:
ws.append(row)
column_widths = []
for row in ws:
for i, cell in enumerate(row):
if len(column_widths) > i:
if len(str(cell.value)) > column_widths[i]:
column_widths[i] = len(str(cell.value))
else:
column_widths += [len(str(cell.value))]
for i, column_width in enumerate(column_widths):
ws.column_dimensions[get_column_letter(i + 1)].width = column_width
style = NamedStyle(name='style')
style.alignment = Alignment(vertical='center', horizontal='left')
wb.add_named_style(style)
excel_file = xmind_file[:-6] + '.xlsx'
wb.save(excel_file)
# zentao_file = xmind_file[:-6] + '.csv'
# if os.path.exists(zentao_file):
# logging.info('The zentao csv file already exists, return it directly: %s', zentao_file)
# return zentao_file
#
# with open(zentao_file, 'w', encoding='utf-8') as f:
# writer = csv.writer(f)
# writer.writerows(testcase_rows)
# logging.info('Convert XMind file(%s) to a zentao csv file(%s) successfully!', xmind_file, zentao_file)
return excel_file
def _build_styles(self):
# Builds new styles for the spreadsheet
title = NamedStyle("title")
title.font = self.WHITE_FONT
title.fill = self.BLACK_FILL
title.alignment = self.MID_ALIGN
self.workbook.add_named_style(title)
def generar_codigos_excel(self):
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
)
response[
'Content-Disposition'] = 'attachment; filename={date}-codigos.xlsx'.format(
date=datetime.now().strftime('%Y%m%d'), )
titulo = NamedStyle(name="titulo")
titulo.font = Font(bold=True, size=10, color='ffffff')
titulo.alignment = Alignment(
horizontal="center",
vertical="center",
wrap_text=True,
)
titulo.fill = PatternFill(
start_color='244062',
end_color='244062',
fill_type='solid',
)
wb = Workbook()
wb.add_named_style(titulo)
sheet = wb.active
column_dimensions = sheet.column_dimensions['A']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['B']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['C']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['D']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['E']
column_dimensions.width = 20
sheet['A1'] = "CODIGO PRINCIPAL"
sheet['A1'].style = titulo
sheet['B1'] = "CODIGO EXTERNO"
sheet['B1'].style = titulo
sheet['C1'] = "CODIGO PROVEEDOR"
sheet['C1'].style = titulo
sheet['D1'] = "CODIGO TESTIGO"
sheet['D1'].style = titulo
sheet['E1'] = "CODIGO BOLSA"
sheet['E1'].style = titulo
for codigo in self.__codigos:
sheet.append(
(codigo.numero, codigo.cod_externo, codigo.cod_proveedor,
codigo.cod_testigo, codigo.cod_bolsa))
wb.save(response)
return response
def insert_data_to_excel(worksheet=None, data_dict=dict()):
# styling for headers
header = NamedStyle(name='header')
header.font = Font(bold=True)
header.alignment = Alignment(horizontal='center', vertical='center')
# to keep track on which row should we write next (useful when getting the cell range for calculations)
next_row = 1
# sort everything based on the date and insert the data into the excel sheet
for year in sorted(data_dict):
data_dict[year].sort()
worksheet.append(['YEAR', 'MONTH', 'INFLOW', 'OUTFLOW', 'NETFLOW'])
for col in ('A', 'B', 'C', 'D', 'E'):
worksheet[f'{col}{next_row}'].style = header
next_row += 1
# counts how many e-Statements in the same year
num_of_months = len(data_dict[year])
for month, *data in data_dict[year]:
worksheet.append([year, month, *data])
next_row += 1
# row ranges for the actual data (month, inflow, outflow, netflow)
start_range = next_row - num_of_months
end_range = next_row - 1
# appends the TOTAL and AVERAGE rows and its data into the worksheet
for desc, op in (('TOTAL', 'SUM'), ('AVERAGE', 'AVERAGE')):
worksheet.merge_cells(f'A{next_row}:B{next_row}')
cell = worksheet[f'A{next_row}']
cell.style = header
cell.value = f'{desc} ({year})'
for col in ('C', 'D', 'E'):
cell = worksheet[f'{col}{next_row}']
cell.value = f'={op}({col}{start_range}:{col}{end_range})'
next_row += 1
# create and add the chart into the worksheet
chart = create_annual_chart(worksheet=worksheet,
year=year,
min_row=start_range - 1,
max_row=end_range)
worksheet.add_chart(chart, f'G{start_range - 1}')
# padding for filling the missing months
for row in range(12 - num_of_months):
worksheet.append([])
next_row += 1
worksheet.append([]) # spacing for the next year
worksheet.append([])
next_row += 2
def format_cell_style(ws, language_dict):
style_int = NamedStyle('int')
style_int.number_format = '0'
style_str = NamedStyle('str')
style_str.number_format = '@'
style_pcnt = NamedStyle('pcnt')
style_pcnt.number_format = '0.0%'
for cell in ws[column_index[language_dict['id']]][1:]:
cell.style = style_int
for cell in ws[column_index[language_dict['total_submitted']]][1:]:
cell.style = style_int
for cell in ws[column_index[language_dict['total_acs']]][1:]:
cell.style = style_int
for cell in ws[column_index[language_dict['title']]][1:]:
cell.style = style_str
for cell in ws[column_index[language_dict['slug']]][1:]:
cell.style = style_str
for cell in ws[column_index[language_dict['difficulty']]][1:]:
cell.style = style_str
for cell in ws[column_index[language_dict['paid_only']]][1:]:
cell.style = style_str
for cell in ws[column_index[language_dict['status']]][1:]:
cell.style = style_str
for cell in ws[column_index[language_dict['acceptance']]][1:]:
cell.style = style_pcnt
def build_annex_overview():
workbook = Workbook()
workbook.create_sheet('Bilagsoversigt', 0)
sheet = workbook.active
sheet['A1'] = 'Bilag'
sheet['B1'] = 'Rapport'
sheet['C1'] = 'Sideantal'
sheet.column_dimensions['A'].width = 10
sheet.column_dimensions['B'].width = 80
sheet.column_dimensions['C'].width = 9
# Header layout
header = NamedStyle(name="header")
header.font = Font(bold=True)
header_row = sheet[1]
for cell in header_row:
cell.style = header
# Insert data from annex_list
for row in range(0, len(annex_list)):
sheet.cell(column=1, row=row + 2, value=annex_list[row].annex_number)
sheet.cell(column=2,
row=row + 2,
value=annex_list[row].base_filename.replace(
'.pdf',
'').replace('{' + annex_list[row].annex_number + '} - ',
''))
sheet.cell(column=3, row=row + 2, value=annex_list[row].num_pages)
# Conditional statement - Show doublets in "bilagsnumre"
red_fill = PatternFill(bgColor="FFC7CE")
dxf = DifferentialStyle(fill=red_fill)
duplicate_rule = Rule(type="duplicateValues", dxf=dxf, stopIfTrue=None)
sheet.conditional_formatting.add(f'A1:A{len(annex_list) + 1}',
duplicate_rule)
# Printsettings
sheet.page_setup.orientation = sheet.ORIENTATION_PORTRAIT
sheet.page_setup.paperSize = sheet.PAPERSIZE_A4
sheet.sheet_properties.pageSetUpPr.fitToPage = True
sheet.page_setup.fitToWidth = True
sheet.page_setup.fitToHeight = False
sheet.oddHeader.center.text = operation_titel
sheet.oddFooter.center.text = "Side &[Page] af &N"
sheet.print_area = 'A:C'
# Sorter bilagskolonnen
# sheet.auto_filter.add_sort_condition(f'A1:A{len(annex_list) + 1}')
# sheet.auto_filter.add_sort_condition('A:A')
global destination_folder
annex_overview_filename = destination_folder + f'/Bilagsoversigt {datetime.now().date()}.xlsx'
workbook.save(filename=annex_overview_filename)
def create_styles(self, workbook):
"""
styles:
style_title
"""
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
workbook.add_named_style(highlight)
def list_files(startpath):
contents_file = os.path.join(startpath, "content.xlsx")
if os.path.exists(contents_file):
os.remove(contents_file)
wb = openpyxl.Workbook()
ws = wb.active
ws.page_setup.fitToWidth = 1
none_border = Side(border_style=None, color="000000")
thin_border = Side(border_style="thin", color="000000")
#目录样式
dir_style = NamedStyle(name="dir_style")
dir_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False)
#dir_style.fill = GradientFill(stop=("ecF2ec", "ecF2ec"))
dir_style.border = Border(top=thin_border, left=thin_border, right=thin_border, bottom=thin_border)
#文件样式
file_style = NamedStyle(name="file_style")
file_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False)
#file_style.fill = GradientFill(stop=("e9edf0", "e9edf0"))
file_style.border = Border(top=thin_border, left=thin_border, right=none_border, bottom=thin_border)
row = 2
for root, dirs, files in os.walk(startpath):
level = root.replace(startpath, '').count(os.sep)
root_basename = os.path.basename(root)
dir_indent = "| " * (level-1) + "|-- "
file_indent = "| " * level + "|-- "
for i in range(len(dirs)-1, -1, -1):
if not is_vaild_dir(dirs[i]):
dirs.pop(i)
#避免空目录
have_file = False
for f in files:
if is_vaild_txt_filename(f):
have_file = True
break
if have_file:
if not level:
print('.')
else:
print('{}{}'.format(dir_indent, root_basename))
cell = ws.cell(row=row, column=level)
cell.value = root_basename
cell.style = dir_style
cell.hyperlink = root
for f in files:
if is_vaild_txt_filename(f):
print('{}{}'.format(file_indent, f))
cell = ws.cell(row=row, column=level+1)
cell.value = f
cell.style = file_style
cell.hyperlink = root
row += 1
#excel 修饰
#合并单元格
print('{} {}'.format(ws.max_column, ws.max_row))
#标题日期
ws.cell(row=1, column=1).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ws.max_column)
title_style = NamedStyle(name="title_style")
title_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False)
title_style.border = Border(top=thin_border, left=thin_border, right=thin_border, bottom=thin_border)
for c in range(1, ws.max_column+1):
ws.cell(row=1, column=c).style = title_style
for c in range(1, ws.max_column + 1):
row = 2
width = 0
for r in range(2, ws.max_row + 1):
cell = ws.cell(row=r, column=c)
if cell.value:
if c < (ws.max_column - 1):
if r -1 > row:
ws.merge_cells(start_row=row, start_column=c, end_row=r-1, end_column=c)
for _r in range(row, r):
ws.cell(row=_r, column=c).style = dir_style
row = r
w = len(str(cell.value)) * 1.1
width = max(width, w)
#调整单元格大小
ws.column_dimensions[get_column_letter(c)].width = width
wb.save(contents_file)
# ws3 = wb.create_sheet(title="Data")
# # for row in range(10, 20):
# # for col in range(27, 54):
# # _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
# print(ws2['AA10'].value)
# wb.save(filename = '3sheet.xlsx')
# wb = load_workbook(filename = '3sheet.xlsx')
# sheet_ranges = wb['first']
# print(sheet_ranges['B1'].value)
# #Edit Page Setup
wb=Workbook()
ws=wb.active
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
#Creating a Named Style
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
#use st yle
wb.add_named_style(highlight)
ws['A1'].style = highlight
ws['D5'].style = 'highlight'
wb.save(filename = 'stylename1.xlsx')
def style_header2():
style = NamedStyle(name='header2')
style.font = create_font(size=16)
return style
def style_header():
style = NamedStyle(name='header')
style.font = create_font(size=20)
return style
def export_to_excel(self, bag, export_path=None):
def as_text(value):
if value is None:
return ""
return str(value)
if len(bag) == 0:
return None
try:
# create workbook
wb = Workbook()
# create header style
highlight = NamedStyle(name="highlight")
highlight.font = Font(name='DejaVu Sans', bold=True, size=10)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight)
# create data style
normal = NamedStyle(name="normal")
normal.font = Font(name='DejaVu Sans', bold=False, size=10)
wb.add_named_style(normal)
# Get worksheet
ws = wb.active
# build header
header = []
for field in HEADER_FIELDS:
header.append(str(HEADER[field]))
ws.append(header)
# Set data
data = []
for sid in bag:
row = []
metadata = self.srvdtb.get_sapnote_metadata(sid)
for field in HEADER_FIELDS:
if field == 'collections':
cols = ', '.join([self.srvclt.get_name_by_cid(col) for col in metadata[field]])
row.append(cols)
elif field == 'releasedon':
excel_date = self.srvutl.get_excel_date(metadata[field])
row.append(excel_date)
else:
row.append(str(metadata[field]))
ws.append(row)
data.append(row)
# assign style to header
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
cell = col + '1'
ws[cell].style = 'highlight'
# assign style to data
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
for i in range(len(bag)):
cell = col + '%d' % (i + 2)
ws[cell].style = 'normal'
# adjust columns width
try:
for column_cells in ws.columns:
length = max(len(as_text(cell.value)) for cell in column_cells)
ws.column_dimensions[COL[column_cells[0].column]].width = length
except Exception as error:
self.log.error(error)
self.log.error(self.get_traceback())
self.log.error("This piece of code isn't working on Windows...")
# header autofilter and sorting
ws.auto_filter.ref = "A1:J1"
ws.auto_filter.add_sort_condition("I2:I%d" % len(bag))
# save to export path
wb.save(export_path)
return True
except Exception as error:
self.log.error(error)
self.log.error(self.get_traceback())
return False
def create_groups_xlsx(day):
activate("de")
day = GroupAssignment.objects.monday(day)
days = [day + timedelta(days=i) for i in range(5)]
wb = Workbook()
ws = wb.active
thin_border = Side(border_style="thin", color="00000000")
medium_border = Side(border_style="medium", color="00000000")
font = Font(name="Calibri", size=14)
centered = NamedStyle("centered")
centered.font = font
centered.alignment = Alignment(horizontal="center", vertical="center")
wb.add_named_style(centered)
dark = NamedStyle("dark")
dark.font = font
dark.fill = PatternFill("solid", "cccccc")
dark.border = Border(
top=thin_border, right=thin_border, bottom=thin_border, left=thin_border
)
wb.add_named_style(dark)
darker = NamedStyle("darker")
darker.border = Border(top=thin_border, bottom=thin_border)
darker.font = Font(name="Calibri", size=14, bold=True)
darker.fill = PatternFill("solid", "aaaaaa")
wb.add_named_style(darker)
darker_border_left = NamedStyle("darkerBorderLeft")
darker_border_left.border = Border(
top=thin_border, bottom=thin_border, left=medium_border
)
darker_border_left.font = darker.font
darker_border_left.fill = darker.fill
wb.add_named_style(darker_border_left)
border = NamedStyle("borderThickLeft")
border.border = Border(
top=thin_border, right=thin_border, bottom=thin_border, left=medium_border
)
border.font = font
wb.add_named_style(border)
border = NamedStyle("borderThickBottom")
border.border = Border(bottom=medium_border)
border.font = font
wb.add_named_style(border)
border = NamedStyle("borderThinLeft")
border.border = Border(left=thin_border)
border.font = font
wb.add_named_style(border)
border = NamedStyle("borderThinBottom")
border.border = Border(bottom=thin_border)
border.font = font
wb.add_named_style(border)
borderThin = NamedStyle("borderThin")
borderThin.border = Border(
top=thin_border, right=thin_border, bottom=thin_border, left=thin_border
)
borderThin.font = font
wb.add_named_style(borderThin)
vertical_text = Alignment(text_rotation=90)
def day_column(weekday):
return 2 + 9 * weekday
def style_row(row, style):
ws[c(0, row)].style = style
ws[c(1, row)].style = style
ws[c(day_column(5), row)].style = (
"darkerBorderLeft" if style == "darker" else style
)
for i in range(5):
for j in range(9):
ws[c(day_column(i) + j, row)].style = style
if style == "darker":
ws[c(day_column(i), row)].style = "darkerBorderLeft"
def column_width(column, width):
ws.column_dimensions[columns[column]].width = width
def row_height(row, height):
ws.row_dimensions[row + 1].height = height
ws[c(0, 1)].style = "borderThickBottom"
ws[c(1, 1)].style = "borderThickBottom"
ws[c(day_column(5), 1)].style = "borderThickBottom"
for i, cell in enumerate(
[
date_format(day, "F y"),
"Woche %s" % date_format(day, "W"),
"Auftragsnummer Arbeit",
"LEITUNG",
"ZIVIS",
]
):
ws[c(0, i + 1)] = cell
ws[c(day_column(5), i + 1)] = cell
ws[c(0, i + 1)].style = "borderThinBottom"
ws[c(1, i + 1)].style = "borderThinBottom"
if i > 0:
ws[c(day_column(5), i + 1)].style = "borderThickLeft"
if i < 2:
ws[c(0, i + 1)].style = centered
ws[c(day_column(5), i + 1)].style = centered
column_width(0, 35)
column_width(1, 15)
column_width(day_column(5), 35)
ws[c(0, 1)].style = "borderThickBottom"
ws[c(1, 1)].style = "borderThickBottom"
ws[c(0, 1)].alignment = centered.alignment
ws[c(1, 1)].alignment = centered.alignment
ws[c(0, 2)].style = "borderThinBottom"
ws[c(0, 2)].alignment = centered.alignment
ws[c(day_column(5), 1)].style = "borderThickBottom"
ws[c(day_column(5), 1)].alignment = centered.alignment
ws[c(day_column(5), 2)].style = "borderThickLeft"
ws[c(day_column(5), 2)].alignment = centered.alignment
for i, current in enumerate(days):
ws[c(day_column(i), 0)] = date_format(current, "l")
ws[c(day_column(i), 1)] = date_format(current, "d.m.y")
ws[c(day_column(i), 0)].style = centered
ws[c(day_column(i), 1)].style = "borderThickBottom"
ws[c(day_column(i), 1)].alignment = centered.alignment
ws.merge_cells("%s:%s" % (c(day_column(i), 0), c(day_column(i + 1) - 1, 0)))
ws.merge_cells("%s:%s" % (c(day_column(i), 1), c(day_column(i + 1) - 1, 1)))
ws[c(day_column(i), 2)] = "Absenz"
for k in range(2, 499):
ws[c(day_column(i), k)].style = "borderThickLeft"
ws[c(day_column(i) + 1, k)].style = "borderThin"
for j in range(1, 9):
ws[c(day_column(i) + j, 2)] = "%s)" % j
style = "borderThin" if j % 2 else "dark"
for k in range(2, 499):
ws[c(day_column(i) + j, k)].style = style
ws[c(day_column(i) + j, 2)].alignment = vertical_text
column_width(day_column(i) + j, 7)
ws[c(day_column(i), 2)].alignment = vertical_text
column_width(day_column(i), 7)
row_height(2, 250)
row_height(3, 60)
row_height(4, 60)
# ZIVIS line
style_row(5, "darker")
assignments = defaultdict(list)
seen_assignments = set()
for ga in GroupAssignment.objects.filter(week=day).select_related(
"assignment__drudge__user"
):
assignments[ga.group_id].append(ga.assignment)
seen_assignments.add(ga.assignment_id)
free_assignments = (
Assignment.objects.for_date(day)
.exclude(pk__in=seen_assignments)
.select_related("drudge__user")
)
absences = defaultdict(dict)
for absence in Absence.objects.filter(days__overlap=days):
for day in absence.days:
absences[absence.assignment_id][day] = absence
def add_group(row, group_name, assignments):
ws[c(0, row)] = group_name
ws[c(day_column(5), row)] = group_name
style_row(row, "darker")
# TODO courses (UNA/MSK)
for assignment in assignments:
row += 1
ws[c(0, row)] = assignment.drudge.user.get_full_name()
ws[c(day_column(5), row)] = assignment.drudge.user.get_full_name()
ws[c(0, row)].style = "borderThinBottom"
ws[c(1, row)].style = "borderThinBottom"
ws[c(day_column(5), row)].style = "borderThickLeft"
row_height(row, 35)
if assignment.date_from in days:
ws[c(1, row)] = "NEU"
elif assignment.determine_date_until() in days:
ws[c(1, row)] = "ENDE"
else:
ws[c(1, row)] = date_format(assignment.determine_date_until(), "d.m.y")
for i, current in enumerate(days):
if current < assignment.date_from:
ws[c(day_column(i), row)] = "Vor Beginn"
elif current > assignment.determine_date_until():
ws[c(day_column(i), row)] = "Nach Ende"
elif current in absences[assignment.id]:
ws[c(day_column(i), row)] = absences[assignment.id][
current
].pretty_reason()
# Skip some lines
for i in range(0, max(3, 6 - len(assignments))):
row += 1
row_height(row, 35)
ws[c(0, row)].style = "borderThinBottom"
ws[c(1, row)].style = "borderThinBottom"
ws[c(day_column(5), row)].style = "borderThickLeft"
row += 1
return row
row = 6
for group in Group.objects.active():
row = add_group(row, group.name, assignments[group.id])
row = add_group(row, "Nicht zugeteilt", free_assignments)
return wb
def __init__(self, datetimenow=datetime.datetime.now()):
dirname = "Logs/%s" % datetimenow.strftime("%Y-%m-%d_%H_%M_%S")
if not os.path.exists(dirname):
os.mkdir(dirname)
self.wbname = "%s/Report.xlsx" % dirname
self.wb = Workbook()
ft = Font(name=u'Courier New',
size=16,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type="solid",
start_color='FF88FFFF',
end_color='FF008800')
cellfill = PatternFill(fill_type="solid",
start_color='FFFFFFFF',
end_color='FF000000')
bd = Border(left=Side(border_style="thin",
color='FF001000'),
right=Side(border_style="thin",
color='FF110000'),
top=Side(border_style="thin",
color='FF110000'),
bottom=Side(border_style="thin",
color='FF110000'),
diagonal=Side(border_style=None,
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None,
color='FF000000'),
vertical=Side(border_style=None,
color='FF000000'),
horizontal=Side(border_style=None,
color='FF110000')
)
alignment = Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
headerstyle = NamedStyle(name="header")
headerstyle.font = ft
headerstyle.fill = fill
headerstyle.border = bd
headerstyle.alignment = alignment
self.wb.add_named_style(headerstyle)
cellstyle = NamedStyle(name="cell")
cellstyle.font = ft
cellstyle.fill = cellfill
cellstyle.border = bd
cellstyle.alignment = alignment
self.wb.add_named_style(cellstyle)
self.current_row = 0
self.chart_row = 0
self.linkbd = bd
pass
def list_files_v2(startpath):
contents_file = os.path.join(startpath, "content.xlsx")
if os.path.exists(contents_file):
os.remove(contents_file)
wb = openpyxl.Workbook()
ws = wb.active
ws.page_setup.fitToWidth = 1
none_border = Side(border_style=None, color="000000")
thin_border = Side(border_style="thin", color="000000")
#目录样式
dir_style = NamedStyle(name="dir_style")
dir_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False)
#dir_style.fill = GradientFill(stop=("ecF2ec", "ecF2ec"))
dir_style.border = Border(top=thin_border, left=thin_border, right=thin_border, bottom=thin_border)
#文件样式
file_style = NamedStyle(name="file_style")
file_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False)
#file_style.fill = GradientFill(stop=("e9edf0", "e9edf0"))
file_style.border = Border(top=thin_border, left=thin_border, right=none_border, bottom=thin_border)
#计算出文件需要显示在哪一列
file_colum = 1
for root, dirs, files in os.walk(startpath):
for f in files:
if is_vaild_txt_filename(f):
level = root.replace(startpath, '').count(os.sep) + 1
file_colum = max(level, file_colum)
break
for f in files:
if is_vaild_html_filename(f):
level = root.replace(startpath, '').count(os.sep) + 1
file_colum = max(level, file_colum)
dirs[:] = []
break
#开始处理
row = 3
for root, dirs, files in os.walk(startpath):
#去掉隐藏目录
for i in range(len(dirs)-1, -1, -1):
if not is_vaild_dir(dirs[i]):
dirs.pop(i)
#处理单独的文档文件，txt
#避免空目录
have_file = False
for f in files:
if is_vaild_txt_filename(f):
have_file = True
break
if not have_file:
for d in dirs:
if is_have_vaildfile(os.path.join(root, d)):
have_file = True
break
if have_file:
relative_filename = root.replace(startpath, '')
level = relative_filename.count(os.sep)
root_basename = os.path.basename(root)
dir_indent = "| " * (level-1) + "|-- "
file_indent = "| " * level + "|-- "
if not level:
print('.')
else:
print('{}{}'.format(dir_indent, root_basename))
file_dirs = relative_filename.split(os.sep)
for f in files:
if is_vaild_txt_filename(f):
print('{}{}'.format(file_indent, f))
#填写目录
file_link = startpath
for di in range(1, len(file_dirs)):
cell = ws.cell(row=row, column=di)
cell.value = file_dirs[di]
file_link = os.path.join(file_link, file_dirs[di])
cell.hyperlink = file_link
cell = ws.cell(row=row, column=file_colum)
cell.value = f
cell.style = file_style
ref = get_column_letter(file_colum) + str(row)
cell.hyperlink = Hyperlink(ref = ref, tooltip=os.path.join(root, f), display=f)
cell.hyperlink.target = file_link
row += 1
#处理一个网站集合
#该目录下拥有一个html文件，则表示这是一个统一网站集合
for f in files:
if is_vaild_html_filename(f):
dirs[:] = []
relative_filename = root.replace(startpath, '')
file_dirs = relative_filename.split(os.sep)
file_link = startpath
for di in range(1, len(file_dirs)):
cell = ws.cell(row=row, column=di)
cell.value = file_dirs[di]
file_link = os.path.join(file_link, file_dirs[di])
cell.hyperlink = file_link
cell = ws.cell(row=row, column=file_colum)
cell.value = f
cell.style = file_style
ref = get_column_letter(file_colum) + str(row)
cell.hyperlink = Hyperlink(ref = ref, tooltip=os.path.join(root, f), display=f)
cell.hyperlink.target = os.path.join(root, f)
row += 1
break
#excel 修饰
print('{} {}'.format(ws.max_column, ws.max_row))
#标题日期
ws.cell(row=1, column=1).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ws.max_column)
title_style = NamedStyle(name="title_style")
title_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False)
title_style.border = Border(top=thin_border, left=thin_border, right=thin_border, bottom=thin_border)
for c in range(1, ws.max_column+1):
ws.cell(row=1, column=c).style = title_style
#设置标题
for c in range(1, ws.max_column):
cell = ws.cell(row=2, column=c)
cell.value = str(c) + "级目录"
cell = ws.cell(row=2, column=ws.max_column)
cell.value = "文档名称"
#设置内容的样式
for c in range(1, ws.max_column + 1):
row = 3
width = 0
for r in range(1, ws.max_row + 1):
cell = ws.cell(row=r, column=c)
if cell.value:
w = len(str(cell.value)) * 1.1
width = max(width, w)
cell.style = dir_style
#调整单元格大小
ws.column_dimensions[get_column_letter(c)].width = width
wb.save(contents_file)
