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 write_excel(self):
left, right, top, bottom = [Side(style='thin', color='000000')] * 4
title = NamedStyle(name="title")
title.font = Font(name=u'宋体', size=11, bold=True)
title.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
title.border = Border(left=left, right=right, top=top, bottom=bottom)
content = NamedStyle(name="content")
content.font = Font(name=u'宋体', size=11)
content.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
content.border = Border(left=left, right=right, top=top, bottom=bottom)
content_long = NamedStyle(name="content_long")
content_long.font = Font(name=u'宋体', size=11)
content_long.border = Border(left=left, right=right, top=top, bottom=bottom)
content_long.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
self.ws.column_dimensions['A'].width = 15
self.ws.column_dimensions['B'].width = 15
self.ws.column_dimensions['C'].width = 80
self.ws.column_dimensions['D'].width = 55
self.ws.column_dimensions['E'].width = 55
self.ws.column_dimensions['F'].width = 10
self.ws_wrong.column_dimensions['A'].width = 20
self.ws_wrong.column_dimensions['B'].width = 15
self.ws_wrong.column_dimensions['C'].width = 15
self.ws_wrong.column_dimensions['D'].width = 80
self.ws_wrong.column_dimensions['E'].width = 80
for i in range(self.ws.max_row):
self.ws.row_dimensions[i + 1].height = 30
for i in range(self.ws_wrong.max_row):
self.ws_wrong.row_dimensions[i + 1].height = 50
for x in self.ws[1]:
x.style = title
for x in self.ws_wrong[1]:
x.style = title
for x in self.ws['A'][1:]:
x.style = content
for x in self.ws_wrong['A'][1:]:
x.style = content
for x in self.ws['B'][1:]:
x.style = content
for x in self.ws_wrong['B'][1:]:
x.style = content
for x in self.ws['C'][1:]:
x.style = content_long
for x in self.ws_wrong['C'][1:]:
x.style = content
for x in self.ws['D'][1:]:
x.style = content_long
for x in self.ws_wrong['D'][1:]:
x.style = content_long
for x in self.ws['E'][1:]:
x.style = content_long
for x in self.ws_wrong['E'][1:]:
x.style = content_long
for x in self.ws['F'][1:]:
x.style = content
print("格式正确个数 %d" % self.ws.max_row)
print("格式错误个数 %d" % self.ws_wrong.max_row)
self.wb.save(os.path.dirname(os.path.abspath(self.path)) + "\\" + os.path.basename(self.path).replace(".txt",
".xlsx"))
def build_styles():
global excel_file
from openpyxl.styles import NamedStyle, Font, PatternFill, Alignment, Border, Side
alignment = Alignment(
horizontal = 'center',
vertical = 'center',
wrap_text = True,
)
border = Border(
left = Side(style = 'thin'),
right = Side(style = 'thin'),
top = Side(style = 'thin'),
bottom = Side(style = 'thin'),
)
top_style = NamedStyle('top_style')
top_style.alignment = alignment
top_style.border = border
top_style.fill = PatternFill('solid', fgColor = "000000")
top_style.font = Font(
bold = True,
color = 'FFFFFF',
name = 'Calibri',
size = 10,
)
excel_file.add_named_style(top_style)
normal_style = NamedStyle('normal_style')
normal_style.alignment = alignment
normal_style.border = border
normal_style.fill = PatternFill('solid', fgColor = "FFFFFF")
normal_style.font = Font(
color = '000000',
name = 'Calibri',
size = 10,
)
excel_file.add_named_style(normal_style)
def addHeaderStyle(sheetTitle):
missingWB = openpyxl.load_workbook(missingWB_out)
unprocessedWB = openpyxl.load_workbook(unprocessedWB_out)
currentSheet1 = missingWB.get_sheet_by_name(sheetTitle)
currentSheet2 = unprocessedWB.get_sheet_by_name(sheetTitle)
if 'red_bold' not in missingWB._named_styles.names:
red_bold = NamedStyle(name="red_bold")
red_bold.font = Font(color='00FF0000', bold=True)
# red_bold.fill = PatternFill("solid", fgColor="FFCC00")
red_bold.alignment = Alignment(horizontal="center", vertical="center")
missingWB.add_named_style(red_bold)
if 'red_bold' not in unprocessedWB._named_styles.names:
red_bold = NamedStyle(name="red_bold")
red_bold.font = Font(color='00FF0000', bold=True)
# red_bold.fill = PatternFill("solid", fgColor="FFCC00")
red_bold.alignment = Alignment(horizontal="center", vertical="center")
unprocessedWB.add_named_style(red_bold)
for cell in currentSheet1["1:1"]:
cell.style = 'red_bold'
for cell in currentSheet2["1:1"]:
cell.style = 'red_bold'
missingWB.save(missingWB_out)
unprocessedWB.save(unprocessedWB_out)
def creationFichierResultat():
#nom = input("Nom du fichier:")
if p.ELECTRICITE_ET_AUTRES and p.INTRANTS_ET_FRET and p.EMBALLAGES_ET_SACHERIE and p.FRET_AVAL:
nom = "COMPLET "+str(int(100000*random.random()))
else:
nom = "test"+str(int(100000*random.random()))
nom = "Fichier Resultat - "+str(nom)+ " - "+str(p.ANNEE)+".xlsx"
fichierResultat = openpyxl.Workbook()
feuille_principale = fichierResultat.active
feuille_principale.title= "Résultats généraux"
#Tout ce qu'il y a ci après est esthétique
bd_dotted = Side(style="dotted", color="000000")
bd_thin = Side(style="thin", color="FF000000")
#Style du fond ffe2aa
styleFond = NamedStyle(name="Fond")
styleFond.fill = PatternFill("solid", fgColor= "ffe2aa")
fichierResultat.add_named_style(styleFond)
#Style du tete de tableau
styleTeteTab = NamedStyle(name="TeteTab")
styleTeteTab.border = Border(bottom = bd_dotted, top= bd_dotted)
styleTeteTab.alignment = Alignment(wrapText = "true",horizontal = "center")
styleTeteTab.fill = PatternFill("solid", fgColor= "FFFFFF")
fichierResultat.add_named_style(styleTeteTab)
#Style du valeurs de tableau
styleVal = NamedStyle(name="Valeurs")
styleVal.border = Border(left = bd_dotted, right = bd_dotted)
styleVal.fill = PatternFill("solid", fgColor= "FFF0E1")
fichierResultat.add_named_style(styleVal)
#Style des titres
styleTitre = NamedStyle(name="Titre")
styleTitre.border = Border(top=bd_dotted, bottom = bd_dotted)
styleTitre.fill = PatternFill("solid", fgColor= "EBEAF5")
styleTitre.alignment = Alignment(horizontal = "center", vertical="center")
styleTitre.font = Font(name = 'Calibri', size =36, italic = False, bold = True, color = 'FF000000')
fichierResultat.add_named_style(styleTitre)
#Style des entrees de tableau
styleEntree = NamedStyle(name="Entree")
styleEntree.border = Border(right = bd_thin)
styleEntree.fill = PatternFill("solid", fgColor= "FFFFFF")
fichierResultat.add_named_style(styleEntree)
#Style des tableaux en général
styleTableau = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True)
#On applique le fond à toutes les cellules
for lin in range(1,200):
for col in [x for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]:
case = col+str(lin)
feuille_principale[case].style=styleFond
for col in [x+y for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ" for y in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]:
case = col+str(lin)
feuille_principale[case].style=styleFond
def build_styles():
#the excel file we are editating now is a global variable
global excel_file
#this is for aligning the text inside the cells
alignment = Alignment(
horizontal='center',
vertical='center',
)
#aplying a border to the cell
border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'),
)
#the default font style used in the headlines
font = Font(
#this makes the font bold for the headlines
bold=True,
color='000000',
name='Calibri',
size=11,
)
#creating the top style for the headline cells
top_style = NamedStyle('top_style')
#applying our configuration to the style we just created
top_style.alignment = alignment
top_style.font = font
top_style.border = border
#adding the style to the file
excel_file.add_named_style(top_style)
#the normal style
normal_style = NamedStyle('normal_style')
#in this style we don't need an aligment nor a font, we're gonna use the default
#which is:
#horizontal = 'left',
#vertical = 'bottom',
#fontName = 'Calibri',
#fontSize = 11,
#color = '000000'
normal_style.border = border
#adding the style to the file
excel_file.add_named_style(normal_style)
#this is style is the same as the normal but with center aligning
center_style = NamedStyle('center_style')
center_style.alignment = alignment
center_style.border = border
excel_file.add_named_style(center_style)
def format_file(self):
self.ws_new.column_dimensions['A'].width = 20
self.ws_new.column_dimensions['B'].width = 14
self.ws_new.column_dimensions['C'].width = 14
self.ws_new.column_dimensions['D'].width = 70
self.ws_new.column_dimensions['E'].width = 10
self.ws_new.column_dimensions['F'].width = 14
self.ws_new.column_dimensions['G'].width = 14
self.ws_new.column_dimensions['H'].width = 14
self.ws_new.column_dimensions['I'].width = 14
self.ws_new.column_dimensions['J'].width = 14
self.ws_new.column_dimensions['K'].width = 14
self.ws_new.column_dimensions['L'].width = 25
left, right, top, bottom = [Side(style='thin', color='000000')] * 4
title = NamedStyle(name="title")
title.font = Font(name=u'宋体', size=11, bold=True)
title.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
title.border = Border(left=left, right=right, top=top, bottom=bottom)
content = NamedStyle(name="content")
content.font = Font(name=u'宋体', size=11)
content.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
content.border = Border(left=left, right=right, top=top, bottom=bottom)
content_long = NamedStyle(name="content_long")
content_long.font = Font(name=u'宋体', size=11)
content_long.border = Border(left=left, right=right, top=top, bottom=bottom)
content_long.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
for i in range(self.ws_new.max_row):
self.ws_new.row_dimensions[i + 1].height = 40
for x in self.ws_new['A:C']:
for y in x:
y.style = content
for x in self.ws_new['E:F']:
for y in x:
y.style = content
for x in self.ws_new['H:K']:
for y in x:
y.style = content
for x in self.ws_new[1]:
x.style = title
for x in self.ws_new['D'][1:]:
x.style = content_long
for x in self.ws_new['L'][1:]:
x.style = content_long
for x in self.ws_new['G'][1:]:
x.style = content_long
def pg01_namedstyle():
''' NamedStyles set (mutable & used when need to apply formatting to different cells at once) '''
# columnEven = [2, 4, 6, 8]
# columnOdd = [3, 5, 7, 9]
#
headerrows = NamedStyle(name='headerrows')
headerrows.font = Font(bold=True, sz=12)
headerrows.alignment = center
#
rooms = NamedStyle(name='rooms')
rooms.font = Font(b=True, sz=12)
rooms.alignment = Alignment(horizontal='left', vertical='center')
#
subtitles = NamedStyle(name="subtitles")
subtitles.font = Font(i=True, size=9)
subtitles.alignment = left
#
rightAlign = NamedStyle(name='rightAlign')
rightAlign.font = Font(b=True, i=True, sz=10)
rightAlign.alignment = right
wb.save('Plymouth_Daily_Rounds.xlsx')
#
# Header Rows
sheet['A2'].style = rightAlign
sheet['A3'].style = 'rightAlign'
sheet['B3'].style = headerrows
sheet['C3'].style = 'headerrows'
sheet['D3'].style = 'headerrows'
sheet['E3'].style = 'headerrows'
# Room Divisions
sheet['A4'].style = rooms
sheet['A20'].style = 'rooms'
sheet['A28'].style = 'rooms'
sheet['A32'].style = 'rooms'
# Subtitles
sheet['A5'].style = subtitles
sheet['A8'].style = 'subtitles'
sheet['A21'].style = 'subtitles'
sheet['A33'].style = 'subtitles'
sheet['A40'].style = 'subtitles'
sheet['A44'].style = 'subtitles'
# Cell-specific adjustments
# A1
a1 = sheet['A1']
a1.font = Font(size=12, b=True, i=True, color='FF0000')
a1.alignment = center
a1.value = 'Note: When doing rounds be aware for unusual smells, sounds, sights, or anything not normal.'
sheet['A47'].alignment = leftTop
sheet['A47'].font = Font(b=True)
wb.save('Plymouth_Daily_Rounds.xlsx')
def wrapper(ws, number_of_row):
# 设置标题样式
ws.merge_cells('a1:f1')
color1 = Color(rgb=title_color)
font = Font(name="Microsoft YaHei UI",
size=34,
b=True,
color=color1)
a1 = ws['a1']
alignment = Alignment(horizontal='center', vertical='center')
a1.font = font
a1.alignment = alignment
# 设置表头样式
color2 = Color(rgb=header_color)
style_for_row2 = NamedStyle(name='header')
style_for_row2.font = Font(name='Calibri', size=16, color='FFFFFF')
style_for_row2.alignment = Alignment(horizontal='center',
vertical='center')
style_for_row2.fill = PatternFill('solid', fgColor=color2)
for each_cell in ws[2]:
each_cell.style = style_for_row2
# 设置表格样式
for i in range(1, number_of_row + 3):
ws.row_dimensions[i].height = 49.5
for i in range(1, 7):
ws.column_dimensions[chr(64 + i)].width = 15
color3 = Color(rgb=body_color)
style_for_body = NamedStyle(name='body')
style_for_body.font = Font(name='Calibri')
style_for_body.alignment = Alignment(horizontal='center',
vertical='center')
style_for_body.fill = PatternFill("solid", fgColor=color3)
style_for_body.border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'),
bottom=Side(border_style='thin',
color='FF000000'),
top=Side(border_style='thin',
color='FF000000'))
for i in range(3, number_of_row + 3):
for j in range(1, 7):
ws.cell(row=i, column=j).style = style_for_body
return function(ws, number_of_row)
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 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 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 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 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 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 get_style_from_dict(style_dict, style_name):
"""
Make NamedStyle instance from dictionary
:param style_dict: dictionary with style properties.
Example: {'fill': {'fill_type'='solid',
'start_color'='FFCCFFCC'},
'alignment': {'horizontal': 'center',
'vertical': 'center',
'wrapText': True,
'shrink_to_fit': True},
'border_side': {'border_style': 'thin',
'color': 'FF000000'},
'font': {'name': 'Arial',
'size': 14,
'bold': True,
'color': 'FF000000'}
}
:param style_name: name of created style
:return: openpyxl.styles.NamedStyle instance
"""
style = NamedStyle(name=style_name)
if not style_dict:
return style
for key, value in style_dict.items():
if key == "font":
style.font = Font(**value)
elif key == "fill":
style.fill = PatternFill(**value)
elif key == "alignment":
style.alignment = Alignment(**value)
elif key == "border_side":
side = Side(**value)
style.border = Border(left=side, right=side, top=side, bottom=side)
return style
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 __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
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 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 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 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 _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 format_xlsx_worksheet(workbook, worksheet, header_values, number_of_rows):
# Header 1
worksheet.merge_cells('A1:D1')
worksheet['A1'].value = "SHARPpy SRH"
worksheet.merge_cells('E1:G1')
worksheet['E1'].value = "SHARPpy CAPE"
worksheet.merge_cells('H1:I1')
worksheet['H1'].value = "SHARPpy LCL"
worksheet.merge_cells('J1:N1')
worksheet['J1'].value = "SHARPpy SHEAR"
header1 = NamedStyle(name="header1")
header1.font = Font(name="Calibri", size=14, bold=True)
header1.alignment = Alignment(horizontal="center")
for cell in worksheet["1:1"]:
cell.style = header1
# Header 2
header2 = NamedStyle(name="header2")
header2.font = Font(name="Calibri", size=14, bold=True)
header2.border = Border(bottom=Side(border_style="thick"))
header2.alignment = Alignment(horizontal="center")
c = 1
for header_name in header_values:
worksheet.cell(row=2, column=c).value = header_name
c += 1
for cell in worksheet["2:2"]:
cell.style = header2
# Data
data_style = NamedStyle(name="data_style")
data_style.font = Font(name="Calibri", size=14, bold=False)
data_style.alignment = Alignment(horizontal="center")
for row in worksheet.iter_rows(3,number_of_rows):
for cell in row:
cell.style = data_style
return workbook
def __init__(self):
__style = NamedStyle(name='simple')
__style.font = Font(bold=True, size=11, color=colors.BLACK)
__style.fill = PatternFill(patternType='solid', fgColor='FABF8F')
bd = Side(style='thin', color=colors.BLACK)
self.__border = Border(left=bd, top=bd, right=bd, bottom=bd)
__style.border = self.__border
self.__alignment = Alignment(horizontal='left', vertical='center')
__style.alignment = self.__alignment
self.__header_style = __style
def makeStyle (self, name, copyfromcell, sheet=None):
ws = self._getTable (sheet)
newstyle = NamedStyle (name=name)
newstylecell = ws [copyfromcell]
newstyle.font = copy (newstylecell.font)
newstyle.fill = copy (newstylecell.fill)
newstyle.border = copy (newstylecell.border)
newstyle.alignment = copy (newstylecell.alignment)
newstyle.number_format = newstylecell.number_format
newstyle.protection = copy (newstylecell.protection)
return newstyle
def save_excel_file(words_data):
"""
Saves the words_data to a styled excel sheet
:param words_data: [[word, synonyms, sentences], ....]
:return: None
"""
workbook = Workbook()
sheet = workbook.active
sheet.cell(row=1, column=1).value = 'word'
for i in range(SYNONYMS_NUM):
column_index = i + 2
sheet.cell(row=1, column=column_index).value = f'synonym {i+1}'
sheet.column_dimensions[get_column_letter(
column_index)].width = FONT_SIZE + 5
for i in range(SENTENCES_NUM):
column_index = i + 2 + SYNONYMS_NUM
sheet.cell(row=1, column=column_index).value = f'sentence {i+1}'
sheet.column_dimensions[get_column_letter(
column_index)].width = FONT_SIZE * 7
row_index = 2 # starts at 1 + header size (1)
for word, synonyms, sentences in words_data:
sheet.cell(row=row_index, column=1).value = word
for i, synonym in enumerate(synonyms):
column_index = i + 2
sheet.cell(row=row_index, column=column_index).value = synonym
for i, sentence in enumerate(sentences):
column_index = i + 2 + SYNONYMS_NUM
sheet.cell(row=row_index, column=column_index).value = sentence
row_index += 1
font = Font(size=FONT_SIZE)
sheet.column_dimensions['A'].width = FONT_SIZE * 2.5
sheet.row_dimensions[1].height = FONT_SIZE * 2
sheet.freeze_panes = 'B2'
# apply the font to all the cells in the seet
for row in sheet.iter_rows():
for cell in row:
cell.font = font
# style template for the header row
header = NamedStyle(name="header")
header.font = Font(size=FONT_SIZE, bold=True)
header.border = Border(bottom=Side(border_style="double"))
header.alignment = Alignment(horizontal="center", vertical="center")
for cell in sheet[1]:
cell.style = header
workbook.save(filename=OUTPUT_FILENAME)
print(f'The data was saved successfully to {OUTPUT_FILENAME}')
def patologistology_buh_data(ws1, data):
"""
res - результат выборки SQL
порядок возврата:
napr, date_confirm, time_confirm, create_date_napr, create_time_napr,
doc_fio, coast, discount, how_many, ((coast + (coast/100 * discount)) * how_many)::NUMERIC(10,2) AS sum_money,
ist_f, time_confirmation, num_card, ind_family, ind_name,
patronymic, birthday, date_born, to_char(EXTRACT(YEAR from age(time_confirmation, date_born)), '999') as ind_age
:return:
"""
style_border_res = NamedStyle(name="style_border_res")
bd = Side(style='thin', color="000000")
style_border_res.border = Border(left=bd, top=bd, right=bd, bottom=bd)
style_border_res.font = Font(bold=False, size=11)
style_border_res.alignment = Alignment(wrap_text=True,
horizontal='center',
vertical='center')
r = 4
for res in data:
r += 1
ws1.cell(row=r, column=1).value = res["hospital"]
ws1.cell(row=r, column=2).value = res["fio_patient"]
ws1.cell(row=r, column=3).value = res["born_patient"]
ws1.cell(row=r, column=4).value = res["polis"]
ws1.cell(row=r, column=5).value = res["snils"]
ws1.cell(row=r, column=6).value = res["visit_date"]
try:
mcb10 = json.loads(res["mcb10_code"])
result_mcb10 = mcb10.get("code", "")
except:
result_mcb10 = ""
ws1.cell(row=r, column=7).value = result_mcb10
ws1.cell(row=r, column=8).value = res["fin_source"]
ws1.cell(row=r, column=9).value = res["price_category"]
ws1.cell(row=r, column=10).value = res["purpose"]
try:
service = json.loads(res["service_code"])
service_code = service.get('code', '')
service_title = service.get('title', '')
except:
service_code = ""
service_title = ""
ws1.cell(row=r, column=11).value = f"{service_code} - {service_title}"
ws1.cell(row=r, column=12).value = res["direction"]
rows = ws1[f'A{r}:L{r}']
for row in rows:
for cell in row:
cell.style = style_border_res
return ws1
def adds_title_format_new(ws,
lonTableHeader,
font_color="FFFFFF",
fill_color="afbcd7",
rows=10):
headOpe = NamedStyle(name="headOpe")
headOpe.alignment = Alignment(horizontal='center')
headOpe.fill = PatternFill("solid", fgColor=fill_color)
headOpe.font = Font(color=font_color, size=12, bold=True)
for row in ws.iter_rows('A' + str(rows) + ':' +
get_column_letter(lonTableHeader) + str(rows)):
for cell in row:
cell.style = headOpe
return ws
def highlight_style():
normal = NamedStyle("highlight")
normal.font = Font(bold=True, size=20)
# style = CellStyle()
normal.border = Border(Side(style='thin', color="000000"),
Side(style='thin', color="000000"),
Side(style='thin', color="000000"),
Side(style='thin', color="000000"))
normal.alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
return normal
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)
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 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 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)
