def parse_font(self, font_node):
"""Read individual font"""
font = Font()
fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS)
if fontSizeEl is not None:
font.size = fontSizeEl.get('val')
fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS)
if fontNameEl is not None:
font.name = fontNameEl.get('val')
bold = font_node.find('{%s}b' % SHEET_MAIN_NS)
if bold is not None:
font.bold = bool(bold.get('val', True))
italic = font_node.find('{%s}i' % SHEET_MAIN_NS)
if italic is not None:
font.italic = bool(italic.get('val', True))
underline = font_node.find('{%s}u' % SHEET_MAIN_NS)
if underline is not None:
font.underline = underline.get('val', 'single')
strikethrough = font_node.find('{%s}strike' % SHEET_MAIN_NS)
if strikethrough is not None:
font.strikethrough = True
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
font.color.index = self._get_relevant_color(color)
return font
def parse_fonts(root, color_index):
"""Read in the fonts"""
font_list = []
fonts = root.find('{%s}fonts' % SHEET_MAIN_NS)
if fonts is not None:
font_nodes = fonts.findall('{%s}font' % SHEET_MAIN_NS)
for font_node in font_nodes:
font = Font()
fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS)
if fontSizeEl is not None:
font.size = fontSizeEl.get('val')
fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS)
if fontNameEl is not None:
font.name = fontNameEl.get('val')
font.bold = True if len(font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False
font.italic = True if len(font_node.findall('{%s}i' % SHEET_MAIN_NS)) else False
if len(font_node.findall('{%s}u' % SHEET_MAIN_NS)):
underline = font_node.find('{%s}u' % SHEET_MAIN_NS).get('val')
font.underline = underline if underline else 'single'
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
if color.get('indexed') is not None and 0 <= int(color.get('indexed')) < len(color_index):
font.color.index = color_index[int(color.get('indexed'))]
elif color.get('theme') is not None:
if color.get('tint') is not None:
font.color.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint'))
else:
font.color.index = 'theme:%s:' % color.get('theme') # prefix color with theme
elif color.get('rgb'):
font.color.index = color.get('rgb')
font_list.append(font)
return font_list
def parse_font(self, font_node):
"""Read individual font"""
font = Font()
fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS)
if fontSizeEl is not None:
font.size = fontSizeEl.get('val')
fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS)
if fontNameEl is not None:
font.name = fontNameEl.get('val')
bold = font_node.find('{%s}b' % SHEET_MAIN_NS)
if bold is not None:
font.bold = bool(bold.get('val', True))
italic = font_node.find('{%s}i' % SHEET_MAIN_NS)
if italic is not None:
font.italic = bool(italic.get('val', True))
underline = font_node.find('{%s}u' % SHEET_MAIN_NS)
if underline is not None:
font.underline = underline.get('val', 'single')
strikethrough = font_node.find('{%s}strike' % SHEET_MAIN_NS)
if strikethrough is not None:
font.strikethrough = True
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
font.color.index = self._get_relevant_color(color)
return font
def test_conditional_formatting_setDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
cf.add(
'C1:C10',
FormulaRule(formula=['ISBLANK(C1)'],
font=font,
border=borders,
fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 2
assert self.workbook.style_properties['dxf_list'][0] == {
'font': font,
'border': borders,
'fill': fill
}
assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def styled_sheet():
from openpyxl import Workbook
from openpyxl.styles import Font, Style, Fill, Color, colors
wb = Workbook()
ws = wb.active
ws.title = 'Test 1'
red_fill = Fill()
red_fill.fill_type = 'solid'
red_fill.start_color = Color(Color.RED),
red_fill.end_color = Color(Color.RED)
empty_fill = Fill()
styles = []
# pregenerate relevant styles
for row in range(ROWS):
_row = []
for col in range(COLUMNS):
cell = ws.cell(row=row + 1, column=col + 1)
cell.value = 1
font = Font()
fill = Fill()
if formatData[row][col] & BOLD:
font.bold = True
if formatData[row][col] & ITALIC:
font.italic = True
if formatData[row][col] & UNDERLINE:
font.underline = 'single'
if formatData[row][col] & RED_BG:
fill = red_fill
style = Style()
style.font = font
style.fill = fill
ws._styles[cell.address] = style
def test_conditional_formatting_addDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
dxfId = cf.addDxfStyle(self.workbook, font, borders, fill)
assert dxfId == 0
dxfId = cf.addDxfStyle(self.workbook, None, None, fill)
assert dxfId == 1
assert len(self.workbook.style_properties['dxf_list']) == 2
def test_conditional_formatting_setDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
cf.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 2
assert self.workbook.style_properties['dxf_list'][0] == {'font': font, 'border': borders, 'fill': fill}
assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def exportTable(matrix, fname, format, title=None):
"""Save data to a file
Inputs
matrix: array with data to save
fname: name of file to save
format: name of format to save
csv | ods | xls | xlsx
title: column title array, optional
"""
sheetTitle = unicode(QApplication.translate("pychemqt", "Table"))
if fname.split(".")[-1] != format:
fname+=".%s" % format
# Format title
if title:
header = []
for ttl in title:
line = unicode(ttl).split(os.linesep)
if line[-1] != "[-]":
line[-1] = "["+line[-1]+"]"
header.append(" ".join(line))
c_newline=maketrans(os.linesep, " ")
if format == "csv":
import csv
with open(fname, "w") as archivo:
writer = csv.writer(archivo, delimiter='\t', quotechar='"', quoting=csv.QUOTE_NONE)
# Add Data
if title:
writer.writerow([ttl.translate(c_newline) for ttl in header])
c_float=maketrans(".", ",")
for row in matrix:
writer.writerow([str(data).translate(c_float) for data in row])
elif format == "ods":
import ezodf
spreadsheet = ezodf.newdoc("ods", fname)
sheets = spreadsheet.sheets
sheet=ezodf.Table(sheetTitle)
sheets+=sheet
sheet.reset(size=(len(matrix)+1, len(matrix[0])))
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i"%(spreadsheetColumn(i), 1)].set_value(ttl)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i"%(spreadsheetColumn(i), j+2)].set_value(data)
spreadsheet.save()
elif format == "xls":
import xlwt
spreadsheet = xlwt.Workbook()
sheet = spreadsheet.add_sheet(sheetTitle)
font = xlwt.Font()
font.bold = True
style = xlwt.XFStyle()
style.font = font
# Add Data
if title:
for i, ttl in enumerate(header):
sheet.write(0, i, ttl, style)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet.write(j+1, i, data)
spreadsheet.save(fname)
elif format == "xlsx":
import openpyxl
from openpyxl.styles import Style, Font
spreadsheet = openpyxl.Workbook()
sheet = spreadsheet.active
sheet.title = sheetTitle
font1 = Font()
font1.size = 9
font1.bold = True
font2 = Font()
font2.size = 9
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i"%(spreadsheetColumn(i), 1)] = ttl
sheet["%s%i"%(spreadsheetColumn(i), 1)].style.font= font1
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i"%(spreadsheetColumn(i), j+2)] = data
sheet["%s%i"%(spreadsheetColumn(i), j+2)].style.font = font2
spreadsheet.save(filename=fname)
else:
raise ValueError(QApplication.translate(
"pychemqt", "Unsopported format") + " " + format)
def exportTable(matrix, fname, ext, title=None):
"""Save data to a file
Inputs
matrix: array with data to save
fname: name of file to save
ext: name of format to save
csv | ods | xls | xlsx
title: column title array, optional
"""
sheetTitle = QApplication.translate("pychemqt", "Table")
if fname.split(".")[-1] != ext:
fname += ".%s" % ext
# Format title
header = []
if title:
for ttl in title:
line = str(ttl).split(os.linesep)
if line[-1] != "[-]":
line[-1] = "[" + line[-1] + "]"
header.append(" ".join(line))
if ext == "csv":
import csv
with open(fname, 'w') as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=header)
writer.writeheader()
for row in matrix:
kw = {}
for ttl, value in zip(header, row):
kw[ttl] = value
writer.writerow(kw)
elif ext == "ods":
import ezodf
spreadsheet = ezodf.newdoc("ods", fname)
sheets = spreadsheet.sheets
sheet = ezodf.Table(sheetTitle)
sheets += sheet
sheet.reset(size=(len(matrix) + 1, len(matrix[0])))
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i" % (spreadsheetColumn(i), j + 2)].set_value(data)
spreadsheet.save()
elif ext == "xls":
import xlwt
spreadsheet = xlwt.Workbook()
sheet = spreadsheet.add_sheet(sheetTitle)
font = xlwt.Font()
font.bold = True
style = xlwt.XFStyle()
style.font = font
# Add Data
if title:
for i, ttl in enumerate(header):
sheet.write(0, i, ttl, style)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet.write(j + 1, i, data)
spreadsheet.save(fname)
elif ext == "xlsx":
import openpyxl
from openpyxl.styles import Font
spreadsheet = openpyxl.Workbook()
sheet = spreadsheet.active
sheet.title = sheetTitle
font1 = Font()
font1.size = 9
font1.bold = True
font2 = Font()
font2.size = 9
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl
sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i" % (spreadsheetColumn(i), j + 2)] = data
sheet["%s%i" %
(spreadsheetColumn(i), j + 2)].style.font = font2
spreadsheet.save(filename=fname)
else:
raise ValueError(
QApplication.translate("pychemqt", "Unsopported format") + " " +
ext)
def do_comparison_report(ct: pd.DataFrame) -> Workbook:
statuses = Status.objects.all().values_list('test_status', flat=True)
wb = Workbook()
ws = wb.active
year, work_week, weekday = intel_calendar.ww_date()
rdate = f'Validations comparison report: {year} ww{work_week}.{weekday}'
ws['A2'] = rdate
ws['A2'].font = Font(bold=True, size=14)
ws.merge_cells('A2:G2')
c_row = 3
# Starting table creation
table_row_start, table_columns = c_row + 1, 1
table_row_end = c_row + 1
# DataFrame to cells
rows = dataframe_to_rows(ct, index=True, header=True)
for r_id, row in enumerate(rows, 1):
table_columns = len(row)
c_row += 1
table_row_end = c_row
for c_id, value in enumerate(row, 1):
ws.cell(row=c_row, column=c_id, value=value)
if r_id == table_row_start:
ws.cell(row=c_row,
column=c_id).alignment = Alignment(wrap_text=True)
font = Font()
# coloring statuses
if value in statuses:
if value == 'Passed':
font = Font(color='2E7D32')
elif value == 'Failed':
font = Font(color='B71C1C')
elif value == 'Error':
font = Font(color='D84315')
elif value == 'Blocked':
font = Font(color='616161')
elif value == 'Skipped':
font = Font(color='00838F')
elif value == 'Canceled':
font = Font(color='3E2723')
font.bold = True
ws.cell(row=c_row,
column=c_id).alignment = Alignment(horizontal='center')
ws.cell(row=c_row, column=c_id).font = font
# Insert first column header name
ws.cell(row=table_row_start, column=1, value='Item name')
# First column
max_first_column_len = max(map(len, list(ct.index)))
# Format first and last row in "table"
for c_id in range(1, table_columns + 1):
ws.cell(row=table_row_start, column=c_id).font = Font(color=WHITE)
ws.column_dimensions[to_letter(c_id)].width = max_first_column_len
table = Table(
ref=
f'{to_letter(1)}{table_row_start}:{to_letter(table_columns)}{table_row_end - 1}',
displayName='Results',
tableStyleInfo=MEDIUM_STYLE)
ws.add_table(table)
# Remove empty row where DataFrame index name should be located
ws.delete_rows(table_row_start + 1, 1)
return wb
def exportTable(matrix, fname, ext, title=None):
"""Save data to a file
Inputs
matrix: array with data to save
fname: name of file to save
ext: name of format to save
csv | ods | xls | xlsx
title: column title array, optional
"""
sheetTitle = QApplication.translate("pychemqt", "Table")
if fname.split(".")[-1] != ext:
fname += ".%s" % ext
# Format title
header = []
if title:
for ttl in title:
line = str(ttl).split(os.linesep)
if line[-1] != "[-]":
line[-1] = "["+line[-1]+"]"
header.append(" ".join(line))
if ext == "csv":
import csv
with open(fname, 'w') as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=header)
writer.writeheader()
for row in matrix:
kw = {}
for ttl, value in zip(header, row):
kw[ttl] = value
writer.writerow(kw)
elif ext == "ods":
import ezodf
spreadsheet = ezodf.newdoc("ods", fname)
sheets = spreadsheet.sheets
sheet = ezodf.Table(sheetTitle)
sheets += sheet
sheet.reset(size=(len(matrix)+1, len(matrix[0])))
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i" % (spreadsheetColumn(i), j+2)].set_value(data)
spreadsheet.save()
elif ext == "xls":
import xlwt
spreadsheet = xlwt.Workbook()
sheet = spreadsheet.add_sheet(sheetTitle)
font = xlwt.Font()
font.bold = True
style = xlwt.XFStyle()
style.font = font
# Add Data
if title:
for i, ttl in enumerate(header):
sheet.write(0, i, ttl, style)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet.write(j+1, i, data)
spreadsheet.save(fname)
elif ext == "xlsx":
import openpyxl
from openpyxl.styles import Font
spreadsheet = openpyxl.Workbook()
sheet = spreadsheet.active
sheet.title = sheetTitle
font1 = Font()
font1.size = 9
font1.bold = True
font2 = Font()
font2.size = 9
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl
sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i" % (spreadsheetColumn(i), j+2)] = data
sheet["%s%i" % (spreadsheetColumn(i), j+2)].style.font = font2
spreadsheet.save(filename=fname)
else:
raise ValueError(QApplication.translate(
"pychemqt", "Unsopported format") + " " + ext)
def test_write_dxf(self):
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "FFFFFFFF"
whiteFont.bold = True
whiteFont.italic = True
whiteFont.underline = 'single'
whiteFont.strikethrough = True
blueBorder = Borders()
blueBorder.left.border_style = 'medium'
blueBorder.left.color = Color(Color.BLUE)
blueBorder.right.border_style = 'medium'
blueBorder.right.color = Color(Color.BLUE)
blueBorder.top.border_style = 'medium'
blueBorder.top.color = Color(Color.BLUE)
blueBorder.bottom.border_style = 'medium'
blueBorder.bottom.color = Color(Color.BLUE)
cf = ConditionalFormatting()
cf.add('A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 1
assert 'font' in self.workbook.style_properties['dxf_list'][0]
assert 'border' in self.workbook.style_properties['dxf_list'][0]
assert 'fill' in self.workbook.style_properties['dxf_list'][0]
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
<b val="1" />
<i val="1" />
<u val="single" />
<strike />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
<border>
<left style="medium">
<color rgb="FF0000FF"></color>
</left>
<right style="medium">
<color rgb="FF0000FF"></color>
</right>
<top style="medium">
<color rgb="FF0000FF"></color>
</top>
<bottom style="medium">
<color rgb="FF0000FF"></color>
</bottom>
</border>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
def exportTable(matrix, fname, format, title=None):
"""Save data to a file
Inputs
matrix: array with data to save
fname: name of file to save
format: name of format to save
csv | ods | xls | xlsx
title: column title array, optional
"""
sheetTitle = unicode(QApplication.translate("pychemqt", "Table"))
if fname.split(".")[-1] != format:
fname += ".%s" % format
# Format title
if title:
header = []
for ttl in title:
line = unicode(ttl).split(os.linesep)
if line[-1] != "[-]":
line[-1] = "[" + line[-1] + "]"
header.append(" ".join(line))
c_newline = maketrans(os.linesep, " ")
if format == "csv":
import csv
with open(fname, "w") as archivo:
writer = csv.writer(archivo,
delimiter='\t',
quotechar='"',
quoting=csv.QUOTE_NONE)
# Add Data
if title:
writer.writerow([ttl.translate(c_newline) for ttl in header])
c_float = maketrans(".", ",")
for row in matrix:
writer.writerow([str(data).translate(c_float) for data in row])
elif format == "ods":
import ezodf
spreadsheet = ezodf.newdoc("ods", fname)
sheets = spreadsheet.sheets
sheet = ezodf.Table(sheetTitle)
sheets += sheet
sheet.reset(size=(len(matrix) + 1, len(matrix[0])))
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i" % (spreadsheetColumn(i), j + 2)].set_value(data)
spreadsheet.save()
elif format == "xls":
import xlwt
spreadsheet = xlwt.Workbook()
sheet = spreadsheet.add_sheet(sheetTitle)
font = xlwt.Font()
font.bold = True
style = xlwt.XFStyle()
style.font = font
# Add Data
if title:
for i, ttl in enumerate(header):
sheet.write(0, i, ttl, style)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet.write(j + 1, i, data)
spreadsheet.save(fname)
elif format == "xlsx":
import openpyxl
from openpyxl.styles import Style, Font
spreadsheet = openpyxl.Workbook()
sheet = spreadsheet.active
sheet.title = sheetTitle
font1 = Font()
font1.size = 9
font1.bold = True
font2 = Font()
font2.size = 9
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl
sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i" % (spreadsheetColumn(i), j + 2)] = data
sheet["%s%i" %
(spreadsheetColumn(i), j + 2)].style.font = font2
spreadsheet.save(filename=fname)
else:
raise ValueError(
QApplication.translate("pychemqt", "Unsopported format") + " " +
format)
def __init__(self,
filename,
sheet_name= None,
font_name = "Calibri",
font_size = 10,
header_font_size = 14,
header_bold = True,
froze_first_row=True,
add_fliter=True,
header_height=25,
add_border=True,
alignment_center=True,
specific_alignment_left = [],
auto_fit_column_width=True,
wrap_width=[],
fill_color_header='95B3DF',
hide_columns=[],
column_width= {},
output_filename=None,
home_page_bottom= None,
whiten_nontable_area= False,
zoom_level = 85,
alignment_center_horizon = [],
alignment_center_vertical = []
):
# load excel file and focus on one sheet
if column_width is None: ## if sheet name is not defined, the app is default to apply format function to all sheets
column_width = {}
wb = load_workbook(filename)
if sheet_name is None:
sheet_name = wb.sheetnames
elif type(sheet_name) == str:
sheet_name = [sheet_name]
# defind table, header, body
for sheet_name_item in sheet_name:
try:
ws = wb[sheet_name_item]
table = ws[ws.min_row:ws.max_row]
header = ws[ws.min_row]
if table == header: ## if there is only header in that sheet
table = (table,)
body = ws[ws.min_row + 1:ws.max_row]
#font and font size
font = Font(name=font_name,
size=font_size,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
for tr in table:
for td in tr:
td.font = font
font = Font(name=font_name,
size=header_font_size,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
#font for header
for td in header:
td.font = font
if header_bold:
for td in header:
font = copy(td.font)
font.bold = True
td.font = font
## frozen first row
if froze_first_row:
freeze_position = ws['A2']
ws.freeze_panes = freeze_position
## add filter on header
if add_fliter:
ws.auto_filter.ref = ws.dimensions
## set header height
header_obj = ws.row_dimensions[1]
header_obj.height = header_height
## add_border
if add_border:
border = Border(left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'),
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='FF000000'))
for tr in table:
for td in tr:
td.border = border
## alignment_center
if alignment_center:
alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
for tr in table:
for td in tr:
td.alignment = alignment
## for those who have left top alignment
for col in specific_alignment_left:
column = ws[col]
for td in column:
alignment = copy(td.alignment)
alignment.horizontal = 'left'
alignment.vertical = 'top'
td.alignment = alignment
## for those who have horizontal center alignment
for col in alignment_center_horizon:
column = ws[col]
for td in column:
alignment = copy(td.alignment)
alignment.horizontal = 'center'
td.alignment = alignment
## for those who have vertical center alignment
for col in alignment_center_vertical:
column = ws[col]
for td in column:
alignment = copy(td.alignment)
alignment.vertical = 'center'
td.alignment = alignment
###header do not change
if alignment_center:
for cell in header:
alignment = copy(cell.alignment)
alignment.horizontal = 'center'
alignment.vertical = 'center'
cell.alignment = alignment
## auto fit column width
if auto_fit_column_width:
column_widths = []
for i, cell in enumerate(header):
try:
column_widths[i] = len(cell.value)
except IndexError:
column_widths.append(len(cell.value))
for i, width in enumerate(column_widths):
ws.column_dimensions[get_column_letter(i + 1)].width = width * 1.3 + 5
##choose column width (specific)
for col in column_width:
ws.column_dimensions[col].width = column_width[col]
## wrap
for col in wrap_width:
column = ws[col]
for td in column:
alignment = copy(td.alignment)
#alignment.horizontal = 'left'
#alignment.vertical = 'top'
alignment.wrap_text = True
td.alignment = alignment
#ws.column_dimensions[col].width = 55
## header do not change
alignment.horizontal ='center'
alignment.vertical = 'center'
ws[col + "1"].alignment=alignment
##fill header color
if fill_color_header is not None:
fill = PatternFill(start_color=fill_color_header, end_color=fill_color_header, fill_type="solid")
for cell in header:
cell.fill = fill
##hide columns
for col in hide_columns:
ws.column_dimensions[col].hidden = True
if home_page_bottom is not None:
offset = 4
No_row = ws.max_row + offset
Home_Bottom = ws[get_column_letter(1) + str(No_row - 1)]
Home_Bottom.value = f'=HYPERLINK("#{home_page_bottom}!A1","Home Page")'
Home_Bottom.font = Font(size=15, bold=True, color='95B3DF')
##whiten_nontable_area
if whiten_nontable_area:
### define area to whiten
whiten_col = ws.max_column + 2
whiten_row = ws.max_row + 2
whiten_area1 = ws[get_column_letter(whiten_col) + str(1):"AJ200"]
whiten_area2 = ws['A' + str(whiten_row): get_column_letter(whiten_col - 1) + str(200)]
whiten_area_col = ws[get_column_letter(whiten_col - 1) + str(1):get_column_letter(whiten_col - 1) + str(200)]
whiten_area_row = ws['A' + str(whiten_row - 1): get_column_letter(whiten_col - 1) + str(whiten_row - 1)]
color = 'FFFFFFFF'
font = Font(bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=color)
fill = PatternFill(start_color=color, end_color=color, fill_type="solid")
for tr in whiten_area1:
for td in tr:
td.font = font
td.border = None
td.fill = fill
for tr in whiten_area2:
for td in tr:
td.font = font
td.border = None
td.fill = fill
for tr in whiten_area_col:
for td in tr:
td.font = font
# td.border = None
td.fill = fill
for tr in whiten_area_row:
for td in tr:
td.font = font
# td.border = None
td.fill = fill
## adjust zoom level
for ws in wb.worksheets:
ws.sheet_view.zoomScale = zoom_level
except:
pass
if output_filename is None:
wb.save(filename=filename)
else:
wb.save(filename=output_filename)
#formatter('test.xlsx', sheet_name='raw_data', wrap_width=['J'],column_width={'J':50},header_height=36,output_filename='dsad.xlsx',home_page_bottom='Summary',whiten_nontable_area=True )
#formatter(filename="test.xlsx", hide_columns=["C","D","F","D","S","U","T","W"])
#excel_file_name = 'test.xlsx'
#formatter('test.xlsx',sheet_name = ['Sheet1','Sheet2','Sheet3'],output_filename='dsad.xlsx')
#formatter('test.xlsx',output_filename='dsad.xlsx')
#formatter('test.xlsx',sheet_name = 'Sheet1',output_filename='dsad1.xlsx')
def test_write_dxf(self):
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "FFFFFFFF"
whiteFont.bold = True
whiteFont.italic = True
whiteFont.underline = 'single'
whiteFont.strikethrough = True
blueBorder = Borders()
blueBorder.left.border_style = 'medium'
blueBorder.left.color = Color(Color.BLUE)
blueBorder.right.border_style = 'medium'
blueBorder.right.color = Color(Color.BLUE)
blueBorder.top.border_style = 'medium'
blueBorder.top.color = Color(Color.BLUE)
blueBorder.bottom.border_style = 'medium'
blueBorder.bottom.color = Color(Color.BLUE)
cf = ConditionalFormatting()
cf.add(
'A1:A2',
FormulaRule(formula="[A1=1]",
font=whiteFont,
border=blueBorder,
fill=redFill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 1
assert 'font' in self.workbook.style_properties['dxf_list'][0]
assert 'border' in self.workbook.style_properties['dxf_list'][0]
assert 'fill' in self.workbook.style_properties['dxf_list'][0]
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
<b val="1" />
<i val="1" />
<u val="single" />
<strike />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
<border>
<left style="medium">
<color rgb="FF0000FF"></color>
</left>
<right style="medium">
<color rgb="FF0000FF"></color>
</right>
<top style="medium">
<color rgb="FF0000FF"></color>
</top>
<bottom style="medium">
<color rgb="FF0000FF"></color>
</bottom>
</border>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
