def test_lots_cols(write_cols, ColumnDimension, DummyWorksheet):
from openpyxl.styles import Font
ws = DummyWorksheet
from openpyxl.cell import get_column_letter
for i in range(1, 15):
label = get_column_letter(i)
cd = ColumnDimension(worksheet=ws)
cd.font = Font(name=label)
dict(cd) # create style_id in order for test
ws.column_dimensions[label] = cd
cols = write_cols(ws)
xml = tostring(cols)
expected = """<cols>
<col max="1" min="1" style="1"></col>
<col max="2" min="2" style="2"></col>
<col max="3" min="3" style="3"></col>
<col max="4" min="4" style="4"></col>
<col max="5" min="5" style="5"></col>
<col max="6" min="6" style="6"></col>
<col max="7" min="7" style="7"></col>
<col max="8" min="8" style="8"></col>
<col max="9" min="9" style="9"></col>
<col max="10" min="10" style="10"></col>
<col max="11" min="11" style="11"></col>
<col max="12" min="12" style="12"></col>
<col max="13" min="13" style="13"></col>
<col max="14" min="14" style="14"></col>
</cols>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_col_style(write_cols, ColumnDimension, DummyWorksheet):
ws = DummyWorksheet
cd = ColumnDimension()
ws.column_dimensions['A'] = cd
cd._style = 1
cols = write_cols(ws)
xml = tostring(cols)
expected = """<cols><col max="1" min="1" style="1"></col></cols>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_col_style(write_cols, ColumnDimension, DummyWorksheet):
ws = DummyWorksheet
cd = ColumnDimension(worksheet=ws)
ws.column_dimensions['A'] = cd
cd._style = 1
cols = write_cols(ws)
xml = tostring(cols)
expected = """<cols><col max="1" min="1" style="1"></col></cols>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_col_style(write_cols, ColumnDimension, DummyWorksheet):
from openpyxl.styles import Font
ws = DummyWorksheet
cd = ColumnDimension(worksheet=ws)
ws.column_dimensions['A'] = cd
cd.font = Font(color="FF0000")
cols = write_cols(ws)
xml = tostring(cols)
expected = """<cols><col max="1" min="1" style="1"></col></cols>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def fit_width(ws):
dim_holder = DimensionHolder(worksheet=ws)
dim_holder[get_column_letter(1)] = ColumnDimension(ws,
min=1,
max=1,
width=40)
for col in range(ws.min_column + 1, ws.max_column + 1):
dim_holder[get_column_letter(col)] = ColumnDimension(ws,
min=col,
max=col,
collapsed=True,
bestFit=True)
ws.column_dimensions = dim_holder
def test_lots_cols(out, write_cols, ColumnDimension, DummyWorksheet):
ws = DummyWorksheet
from openpyxl.cell import get_column_letter
for i in range(1, 15):
label = get_column_letter(i)
ws._styles[label] = i
ws.column_dimensions[label] = ColumnDimension()
with xmlfile(out) as xf:
write_cols(xf, ws)
xml = out.getvalue()
expected = """<cols>
<col max="1" min="1" style="1"></col>
<col max="2" min="2" style="2"></col>
<col max="3" min="3" style="3"></col>
<col max="4" min="4" style="4"></col>
<col max="5" min="5" style="5"></col>
<col max="6" min="6" style="6"></col>
<col max="7" min="7" style="7"></col>
<col max="8" min="8" style="8"></col>
<col max="9" min="9" style="9"></col>
<col max="10" min="10" style="10"></col>
<col max="11" min="11" style="11"></col>
<col max="12" min="12" style="12"></col>
<col max="13" min="13" style="13"></col>
<col max="14" min="14" style="14"></col>
</cols>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def xl_set_col_width(sheet, column, cm):
""" change column width """
letter = column_to_letter(column)
if column not in sheet.column_dimensions.keys():
sheet.column_dimensions[letter] = \
ColumnDimension(worksheet=sheet)
sheet.column_dimensions[letter].width = xl_col_width(cm)
def test_write_lots_cols(out, doc, write_cols, ColumnDimension):
worksheet = DummyWorksheet()
from openpyxl.cell import get_column_letter
for i in range(1, 15):
label = get_column_letter(i)
worksheet._styles[label] = i
worksheet.column_dimensions[label] = ColumnDimension()
write_cols(doc, worksheet)
doc.endDocument()
xml = out.getvalue()
expected = """<cols>
<col max="1" min="1" style="1"></col>
<col max="2" min="2" style="2"></col>
<col max="3" min="3" style="3"></col>
<col max="4" min="4" style="4"></col>
<col max="5" min="5" style="5"></col>
<col max="6" min="6" style="6"></col>
<col max="7" min="7" style="7"></col>
<col max="8" min="8" style="8"></col>
<col max="9" min="9" style="9"></col>
<col max="10" min="10" style="10"></col>
<col max="11" min="11" style="11"></col>
<col max="12" min="12" style="12"></col>
<col max="13" min="13" style="13"></col>
<col max="14" min="14" style="14"></col>
</cols>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def set_column_width(map_ws):
col_width = 2
dim_holder = DimensionHolder(worksheet=map_ws)
for col in range(map_ws.min_column, map_ws.max_column + 1):
dim_holder[get_column_letter(col)] = ColumnDimension(map_ws, min=col, max=col, width=col_width)
map_ws.column_dimensions = dim_holder
def parse_column_dimensions(self, col):
attrs = dict(col.attrib)
column = get_column_letter(int(attrs['min']))
attrs['index'] = column
if 'style' in attrs:
attrs['style'] = self.styles[int(attrs['style'])]
dim = ColumnDimension(self.ws, **attrs)
self.ws.column_dimensions[column] = dim
def test_col_widths(write_cols, ColumnDimension, DummyWorksheet):
ws = DummyWorksheet
ws.column_dimensions['A'] = ColumnDimension(worksheet=ws, width=4)
cols = write_cols(ws)
xml = tostring(cols)
expected = """<cols><col width="4" min="1" max="1" customWidth="1"></col></cols>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_col_widths(out, doc, write_cols, ColumnDimension):
worksheet = DummyWorksheet()
worksheet.column_dimensions['A'] = ColumnDimension(width=4)
write_cols(doc, worksheet)
doc.endDocument()
xml = out.getvalue()
expected = """<cols><col width="4" min="1" max="1" customWidth="1"></col></cols>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_outline_cols(out, write_cols, ColumnDimension, DummyWorksheet):
worksheet = DummyWorksheet
worksheet.column_dimensions['A'] = ColumnDimension(outline_level=1)
with xmlfile(out) as xf:
write_cols(xf, worksheet)
xml = out.getvalue()
expected = """<cols><col max="1" min="1" outlineLevel="1"/></cols>"""
diff = compare_xml(expected, xml)
assert diff is None, diff
def test_outline_cols(write_cols, ColumnDimension, DummyWorksheet):
worksheet = DummyWorksheet
worksheet.column_dimensions['A'] = ColumnDimension(worksheet=worksheet,
outline_level=1)
cols = write_cols(worksheet)
xml = tostring(cols)
expected = """<cols><col max="1" min="1" outlineLevel="1"/></cols>"""
diff = compare_xml(expected, xml)
assert diff is None, diff
def test_col_widths(out, write_cols, ColumnDimension, DummyWorksheet):
ws = DummyWorksheet
ws.column_dimensions['A'] = ColumnDimension(width=4)
with xmlfile(out) as xf:
write_cols(xf, ws)
xml = out.getvalue()
expected = """<cols><col width="4" min="1" max="1" customWidth="1"></col></cols>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_outline_format(out, write_format, ColumnDimension, DummyWorksheet):
worksheet = DummyWorksheet
worksheet.column_dimensions['A'] = ColumnDimension(outline_level=1)
with xmlfile(out) as xf:
write_format(xf, worksheet)
xml = out.getvalue()
expected = """<sheetFormatPr defaultRowHeight="15" baseColWidth="10" outlineLevelCol="1" />"""
diff = compare_xml(expected, xml)
assert diff is None, diff
def test_outline_cols(out, doc, write_cols, ColumnDimension):
worksheet = DummyWorksheet()
worksheet.column_dimensions['A'] = ColumnDimension(outline_level=1)
write_cols(doc, worksheet)
doc.endDocument()
xml = out.getvalue()
expected = """<cols><col max="1" min="1" outlineLevel="1"/></cols>"""
diff = compare_xml(expected, xml)
assert diff is None, diff
def set_column_width(self, column_width=10):
"""
Adjust all columns to the same specified width.
"""
dim_holder = DimensionHolder(worksheet=xl.ws)
for col in range(xl.ws.min_column, xl.ws.max_column + 1):
dim_holder[get_column_letter(col)] = ColumnDimension(
xl.ws, min=col, max=col, width=column_width)
xl.ws.column_dimensions = dim_holder
def test_outline_format(out, doc, write_sheet_format, ColumnDimension):
worksheet = DummyWorksheet()
worksheet.column_dimensions['A'] = ColumnDimension(outline_level=1)
write_sheet_format(doc, worksheet)
doc.endDocument()
xml = out.getvalue()
expected = """<sheetFormatPr defaultRowHeight="15" baseColWidth="10" outlineLevelCol="1" />"""
diff = compare_xml(expected, xml)
assert diff is None, diff
def test_outline_format(write_format, ColumnDimension, DummyWorksheet):
worksheet = DummyWorksheet
worksheet.column_dimensions['A'] = ColumnDimension(worksheet=worksheet,
outline_level=1)
fmt = write_format(worksheet)
xml = tostring(fmt)
expected = """<sheetFormatPr defaultRowHeight="15" baseColWidth="10" outlineLevelCol="1" />"""
diff = compare_xml(expected, xml)
assert diff is None, diff
def test_write_cols_style(out, doc, write_cols, ColumnDimension):
worksheet = DummyWorksheet()
worksheet.column_dimensions['A'] = ColumnDimension()
worksheet._styles['A'] = 1
write_cols(doc, worksheet)
doc.endDocument()
xml = out.getvalue()
expected = """<cols><col max="1" min="1" style="1"></col></cols>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_col_style(out, write_cols, ColumnDimension, DummyWorksheet):
ws = DummyWorksheet
ws.column_dimensions['A'] = ColumnDimension()
ws._styles['A'] = 1
with xmlfile(out) as xf:
write_cols(xf, ws)
xml = out.getvalue()
expected = """<cols><col max="1" min="1" style="1"></col></cols>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def resize_columns(ws):
dim_holder = DimensionHolder(worksheet=ws)
dims = {}
for row in ws.rows:
for cell in row:
if cell.value:
dims[cell.column_letter] = max(
(dims.get(cell.column_letter, 0), len(str(cell.value))))
for col in range(ws.min_column, ws.max_column + 1):
dim_holder[get_column_letter(col)] = ColumnDimension(
ws,
min=col,
max=col,
width=(dims[get_column_letter(col)] + 1) * 1.2)
ws.column_dimensions = dim_holder
def generateExcelt(self): # TODO rushed, needs better implementation
"""generates an excel file of the failed tests"""
steps = ["setup", "call", "teardown"]
report = self.json
ws = self.ws
dh = self.dh
ws.cell(1, 1, "Test Case Path").font = Font(b=True)
ws.cell(1, 2, "Test Case Name").font = Font(b=True)
ws.cell(1, 3, "Test Case Status").font = Font(b=True)
ws.cell(1, 4, "Messages").font = Font(b=True)
y = {"path": 1, "name": 2, "status": 3, "Messages": 4}
x = 2
for test in report['tests']:
if test['outcome'] == "failed":
ws.cell(x, y["path"], test['nodeid'])
ws.cell(x, y["name"], test['nodeid'].split("::")[-1])
ws.cell(x, y["status"],
test['outcome'].upper()).font = Font(b=True,
color="FF0000")
messages = ""
for step in steps:
if "log" in test[step]:
for log in test[step]["log"]:
print("log type:::" + str(type(log)))
if log["levelname"] == "MESSAGE":
messages += log["msg"] + " |\n "
ws.cell(x, y["Messages"], messages)
x += 1
# setting the width of the columns
for col in range(ws.min_column, ws.max_column + 1):
dh[get_column_letter(col)] = ColumnDimension(ws,
min=col,
max=col,
width=40)
ws.column_dimensions = dh
# saving the report
try:
if not (self.path[-1] == '\\'):
self.path += '\\'
self.wb.save(self.path + 'report.xlsx')
except:
print("Excel path is invalid")
def _writerow(rowvalues, header=False):
row = []
_ws = _xlinfo["ws"]
for i, v in enumerate(rowvalues):
cell = WriteOnlyCell(_ws, value=v)
if header:
cell.fill = hdrCellFill
cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
colLetter = chr(ord('A') + i)
_ws.column_dimensions[colLetter] = ColumnDimension(_ws, customWidth=True)
_ws.column_dimensions[colLetter].width = headerWidths.get(v, 20)
else:
cell.alignment = Alignment(horizontal="right" if isinstance(v, _NUM_TYPES)
else "center" if isinstance(v, bool)
else "left",
vertical="top",
wrap_text=isinstance(v, str))
row.append(cell)
_ws.append(row)
def _save_report_file_as_excel(report_file_name, dt_of_fetching_data, header,
animals_list, now):
wb = Workbook()
ws = wb.active
row = 1
col = 1
# date
ws.cell(column=1,
row=row,
value='Data pobrania danych: %s' % dt_of_fetching_data)
# header
row += 1
for header_elem in header:
ws.cell(column=col, row=row, value=header_elem)
col += 1
# values
row += 1
cnt = 1
for animal in animals_list:
col = 1
values = [
cnt, animal.herd_id, animal.animal_id, animal.process_status,
animal.passport_id, animal.passport_status,
animal.age_in_months(now), animal.species, animal.sex,
animal.breed, animal.typ_uzyt, animal.status, animal.birth_date,
animal.get_animal_events_states_text()
]
for value in values:
ws.cell(column=col, row=row, value=value)
col += 1
row += 1
cnt += 1
for col in range(1, 10):
col_letter = get_column_letter(col)
ws.column_dimensions[col_letter] = ColumnDimension(ws,
customWidth=True)
ws.column_dimensions[col_letter].width = 24
wb.save(report_file_name)
def bind_col_dimensions(self):
for col, cd in self.parser.column_dimensions.items():
if 'style' in cd:
key = int(cd['style'])
cd['style'] = self.ws.parent._cell_styles[key]
self.ws.column_dimensions[col] = ColumnDimension(self.ws, **cd)
def append_df_to_excel(filename,
df,
sheet_name='Sheet1',
startrow=None,
truncate_sheet=False,
set_width=False,
**to_excel_kwargs):
# Excel file doesn't exist - saving and exiting
if not os.path.isfile(filename):
if 'header' in to_excel_kwargs:
to_excel_kwargs.pop('header')
df.to_excel(filename,
sheet_name=sheet_name,
startrow=startrow if startrow is not None else 0,
**to_excel_kwargs)
return
writer = pd.ExcelWriter(filename, engine='openpyxl', mode='a')
# try to open an existing workbook
writer.book = load_workbook(filename)
# get the last row in the existing Excel sheet
# if it was not specified explicitly
if startrow is None and sheet_name in writer.book.sheetnames:
startrow = writer.book[sheet_name].max_row
# truncate sheet
if truncate_sheet and sheet_name in writer.book.sheetnames:
# index of [sheet_name] sheet
idx = writer.book.sheetnames.index(sheet_name)
# remove [sheet_name]
writer.book.remove(writer.book.worksheets[idx])
# create an empty sheet [sheet_name] using old index
writer.book.create_sheet(sheet_name, idx)
# copy existing sheets
writer.sheets = {ws.title: ws for ws in writer.book.worksheets}
if set_width:
size_dict = {
1: 15,
3: 15,
4: 30,
5: 15,
6: 15,
7: 40,
8: 17,
9: 17,
10: 30,
12: 30
}
ws = writer.sheets["Sheet1"]
dim_holder = DimensionHolder(worksheet=ws)
for i, j in size_dict.items():
dim_holder[get_column_letter(i)] = ColumnDimension(ws,
min=i,
max=i,
width=j)
ws.column_dimensions = dim_holder
if startrow is None:
startrow = 0
# write out the new sheet
df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)
# save the workbook
writer.save()
def update_excel_file(excel_file_1,istenilen_adet):
excel_file_1 = excel_file_1
#df_first_shift = pd.read_excel(excel_file_1, sheet_name='Sheet')
#workbook = Workbook() #overvrites
workbook = load_workbook ( excel_file_1 ) #JUST SAVES
ws = workbook.active
sayi_katsayi = 0
while sayi_katsayi < istenilen_adet:
sabit_katsayi = ( 4 * sayi_katsayi)
ws[f"B{1+sabit_katsayi}"] = str('Adı')
ws[f"B{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"C{1+sabit_katsayi}"] = str('Ürün kodu')
ws[f"C{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"D{1+sabit_katsayi}"] = str('Fiyat($)')
ws[f"D{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"E{1+sabit_katsayi}"] = str('Deri Rengi')
ws[f"E{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"F{1+sabit_katsayi}"] = str('Deri Rengi Opsiyonları')
ws[f"F{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"G{1+sabit_katsayi}"] = str('Deri Türü')
ws[f"G{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"H{1+sabit_katsayi}"] = str('Deri Oranı')
ws[f"H{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"I{1+sabit_katsayi}"] = str('Tüy Rengi')
ws[f"I{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"J{1+sabit_katsayi}"] = str('Tüy Rengi Opsiyonları')
ws[f"J{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"K{1+sabit_katsayi}"] = str('Sezon')
ws[f"K{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"L{1+sabit_katsayi}"] = str('Bakım')
ws[f"L{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"M{1+sabit_katsayi}"] = str('Fermuar Türü')
ws[f"M{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"N{1+sabit_katsayi}"] = str('Fermuar Materyeli')
ws[f"N{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"O{1+sabit_katsayi}"] = str('Düğmeleri')
ws[f"O{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"P{1+sabit_katsayi}"] = str('Çift Taraflı')
ws[f"P{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"Q{1+sabit_katsayi}"] = str('Koleksiyon Adı')
ws[f"Q{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"R{1+sabit_katsayi}"] = str('Cep Türü')
ws[f"R{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"S{1+sabit_katsayi}"] = str('İsim Etiketi')
ws[f"S{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"T{1+sabit_katsayi}"] = str('Ağırlık')
ws[f"T{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"U{1+sabit_katsayi}"] = str('Açıklama')
ws[f"U{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"V{1+sabit_katsayi}"] = str('Materyaller')
ws[f"V{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"W{1+sabit_katsayi}"] = str('Ürün Ölçüleri(cm)')
ws[f"W{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"X{1+sabit_katsayi}"] = str('İç Kısım')
ws[f"X{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"Y{1+sabit_katsayi}"] = str('Dış Kısım')
ws[f"Y{1+sabit_katsayi}"].font = Font(bold=True)
ws[f"Z{1+sabit_katsayi}"] = str('Jeket Türü')
ws[f"Z{1+sabit_katsayi}"].font = Font(bold=True)
# ingilizce kisim
ws[f"B{3 + sabit_katsayi}"] = str ( 'Name' )
ws[f"B{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"C{3 + sabit_katsayi}"] = str ( 'Product Code' )
ws[f"C{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"D{3 + sabit_katsayi}"] = str ( 'Price($)' )
ws[f"D{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"E{3 + sabit_katsayi}"] = str ( 'Colour of The Leather' )
ws[f"E{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"F{3 + sabit_katsayi}"] = str ( 'Colour Options Of Leather' )
ws[f"F{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"G{3 + sabit_katsayi}"] = str ( 'Type Of Leather' )
ws[f"G{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"H{3 + sabit_katsayi}"] = str ( 'Leather Percentage' )
ws[f"H{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"I{3 + sabit_katsayi}"] = str ( 'Feather Color' )
ws[f"I{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"J{3 + sabit_katsayi}"] = str ( 'Feather Color Options' )
ws[f"J{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"K{3 + sabit_katsayi}"] = str ( 'Seeason' )
ws[f"K{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"L{3 + sabit_katsayi}"] = str ( 'Care' )
ws[f"L{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"M{3 + sabit_katsayi}"] = str ( 'Zipper Type' )
ws[f"M{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"N{3 + sabit_katsayi}"] = str ( 'Zipper Material' )
ws[f"N{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"O{3 + sabit_katsayi}"] = str ( 'Buttons' )
ws[f"O{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"P{3 + sabit_katsayi}"] = str ( 'Reversable' )
ws[f"P{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"Q{3 + sabit_katsayi}"] = str ( 'Collection Name' )
ws[f"Q{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"R{3 + sabit_katsayi}"] = str ( 'Pocket Type' )
ws[f"R{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"S{3 + sabit_katsayi}"] = str ( 'Name Tag' )
ws[f"S{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"T{3 + sabit_katsayi}"] = str ( 'Weight' )
ws[f"T{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"U{3 + sabit_katsayi}"] = str ( 'Description' )
ws[f"U{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"V{3 + sabit_katsayi}"] = str ( 'Materials' )
ws[f"V{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"W{3 + sabit_katsayi}"] = str ( 'Product Meeasurements' )
ws[f"W{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"X{3 + sabit_katsayi}"] = str ( 'Interior' )
ws[f"X{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"Y{3 + sabit_katsayi}"] = str ( 'Exterior' )
ws[f"Y{3 + sabit_katsayi}"].font = Font ( bold=True )
ws[f"Z{3 + sabit_katsayi}"] = str ( 'Type of Jacket' )
ws[f"Z{3 + sabit_katsayi}"].font = Font ( bold=True )
sayi_katsayi += 1
##Adjustment for excel files
dim_holder = DimensionHolder(worksheet=ws)
for col in range(ws.min_column, ws.max_column + 1):
dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=25)
ws.column_dimensions = dim_holder
#
workbook.save(filename = excel_file_1)#hatali workbbook nerden cikti
def __format_sheet(self, ws, command):
self.SVC_log.debug("Starting formating " + command +
" information into Excel file")
# Fix the width
self.SVC_log.debug("Starting fixing width for " + command + " sheet")
dim_holder = DimensionHolder(worksheet=ws)
for the_col in range(ws.min_column, ws.max_column + 1):
dim_holder[get_column_letter(the_col)] = ColumnDimension(
ws, min=the_col, max=the_col, width=25)
ws.column_dimensions = dim_holder
self.SVC_log.debug("Completed fixing width for " + command + " sheet")
# Set the header
self.SVC_log.debug("Starting setting header for " + command + " sheet")
HEADER_FONT = Font(name='Calibri',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='000000')
for the_col in range(ws.min_column, ws.max_column + 1):
ws.cell(row=1, column=the_col).font = HEADER_FONT
ws.cell(row=1,
column=the_col).fill = PatternFill(start_color='0066CC',
end_color='0066CC',
fill_type='darkGrid')
self.SVC_log.debug("Completed setting header for " + command +
" sheet")
# Center all
self.SVC_log.debug("Starting centering cells for " + command +
" sheet")
for row_cells in ws.iter_rows():
for cell in row_cells:
cell.alignment = Alignment(horizontal='center',
vertical='center')
self.SVC_log.debug("Completed centering cells for " + command +
" sheet")
# Create alternate colors
self.SVC_log.debug("Starting coloring alternate rows for " + command +
" sheet")
for the_row in range(2, ws.max_row + 1, 2):
for the_col in range(1, ws.max_column + 1):
ws.cell(row=the_row, column=the_col).fill = PatternFill(
start_color='66cc00',
end_color='66cc00',
fill_type='darkGrid')
for the_row in range(3, ws.max_row + 1, 2):
for the_col in range(1, ws.max_column + 1):
ws.cell(row=the_row, column=the_col).fill = PatternFill(
start_color='b3ff66',
end_color='b3ff66',
fill_type='lightGrid')
self.SVC_log.debug("Completed coloring alternate rows for " + command +
" sheet")
self.SVC_log.debug("Completed formating " + command +
" information into Excel file")
def saveLoadableExcel(dts, excelFile):
from arelle import ModelDocument, XmlUtil
from openpyxl import Workbook, cell
from openpyxl.styles import Font, PatternFill, Border, Alignment, Color, fills, Side
from openpyxl.worksheet.dimensions import ColumnDimension
workbook = Workbook(encoding="utf-8")
# remove pre-existing worksheets
while len(workbook.worksheets) > 0:
workbook.remove_sheet(workbook.worksheets[0])
conceptsWs = workbook.create_sheet(title="Concepts")
dtsWs = workbook.create_sheet(title="DTS")
# identify type of taxonomy
conceptsWsHeaders = None
cellFontArgs = None
for doc in dts.urlDocs.values():
if doc.type == ModelDocument.Type.SCHEMA and doc.inDTS:
for i in range(len(headersStyles)):
if re.match(headersStyles[i][0], doc.targetNamespace):
cellFontArgs = headersStyles[i][
1] # use as arguments to Font()
conceptsWsHeaders = headersStyles[i][2]
break
if conceptsWsHeaders is None:
dts.info(
"error:saveLoadableExcel",
_("Referenced taxonomy style not identified, assuming general pattern."
),
modelObject=dts)
cellFontArgs = headersStyles[-1][1] # use as arguments to Font()
conceptsWsHeaders = headersStyles[-1][2]
hdrCellFont = Font(**cellFontArgs)
hdrCellFill = PatternFill(
patternType=fills.FILL_SOLID,
fgColor=Color("00FFBF5F")) # Excel's light orange fill color = 00FF990
cellFont = Font(**cellFontArgs)
def writeCell(ws,
row,
col,
value,
fontBold=False,
borders=True,
indent=0,
hAlign=None,
vAlign=None,
hdr=False):
cell = ws.cell(row=row, column=col)
cell.value = value
if hdr:
cell.font = hdrCellFont
cell.fill = hdrCellFill
if not hAlign: hAlign = "center"
if not vAlign: vAlign = "center"
else:
cell.font = cellFont
if not hAlign: hAlign = "left"
if not vAlign: vAlign = "top"
if borders:
cell.border = Border(top=Side(border_style="thin"),
left=Side(border_style="thin"),
right=Side(border_style="thin"),
bottom=Side(border_style="thin"))
cell.alignment = Alignment(horizontal=hAlign,
vertical=vAlign,
wrap_text=True,
indent=indent)
# sheet 1 col widths
for i, hdr in enumerate(conceptsWsHeaders):
colLetter = cell.get_column_letter(i + 1)
conceptsWs.column_dimensions[colLetter] = ColumnDimension(
conceptsWs, customWidth=True)
conceptsWs.column_dimensions[colLetter].width = headerWidths.get(
hdr[1], 40)
# sheet 2 headers
for i, hdr in enumerate(dtsWsHeaders):
colLetter = cell.get_column_letter(i + 1)
dtsWs.column_dimensions[colLetter] = ColumnDimension(conceptsWs,
customWidth=True)
dtsWs.column_dimensions[colLetter].width = hdr[1]
writeCell(dtsWs, 1, i + 1, hdr[0], hdr=True)
# referenced taxonomies
conceptsRow = 1
dtsRow = 3
# identify extension schema
extensionSchemaDoc = None
if dts.modelDocument.type == ModelDocument.Type.SCHEMA:
extensionSchemaDoc = dts.modelDocument
elif dts.modelDocument.type == ModelDocument.Type.INSTANCE:
for doc, docReference in dts.modelDocument.referencesDocument.items():
if docReference.referenceType == "href":
extensionSchemaDoc = doc
break
if extensionSchemaDoc is None:
dts.info("error:saveLoadableExcel",
_("Unable to identify extension taxonomy."),
modelObject=dts)
return
for doc, docReference in extensionSchemaDoc.referencesDocument.items():
if docReference.referenceType == "import" and doc.targetNamespace != XbrlConst.xbrli:
writeCell(dtsWs, dtsRow, 1, "import")
writeCell(dtsWs, dtsRow, 2, "schema")
writeCell(
dtsWs, dtsRow, 3,
XmlUtil.xmlnsprefix(doc.xmlRootElement, doc.targetNamespace))
writeCell(dtsWs, dtsRow, 4, doc.uri)
writeCell(dtsWs, dtsRow, 5, doc.targetNamespace)
dtsRow += 1
dtsRow += 1
doc = extensionSchemaDoc
writeCell(dtsWs, dtsRow, 1, "extension")
writeCell(dtsWs, dtsRow, 2, "schema")
writeCell(dtsWs, dtsRow, 3,
XmlUtil.xmlnsprefix(doc.xmlRootElement, doc.targetNamespace))
writeCell(dtsWs, dtsRow, 4, os.path.basename(doc.uri))
writeCell(dtsWs, dtsRow, 5, doc.targetNamespace)
dtsRow += 1
for doc, docReference in extensionSchemaDoc.referencesDocument.items():
if docReference.referenceType == "href" and doc.type == ModelDocument.Type.LINKBASE:
linkbaseType = ""
role = docReference.referringModelObject.get(
"{http://www.w3.org/1999/xlink}role") or ""
if role.startswith("http://www.xbrl.org/2003/role/"
) and role.endswith("LinkbaseRef"):
linkbaseType = os.path.basename(role)[0:-11]
writeCell(dtsWs, dtsRow, 1, "extension")
writeCell(dtsWs, dtsRow, 2, "linkbase")
writeCell(dtsWs, dtsRow, 3, linkbaseType)
writeCell(dtsWs, dtsRow, 4, os.path.basename(doc.uri))
writeCell(dtsWs, dtsRow, 5, "")
dtsRow += 1
dtsRow += 1
# extended link roles defined in this document
for roleURI, roleTypes in sorted(
dts.roleTypes.items(),
# sort on definition if any else URI
key=lambda item: (item[1][0].definition or item[0])):
for roleType in roleTypes:
if roleType.modelDocument == extensionSchemaDoc:
writeCell(dtsWs, dtsRow, 1, "extension")
writeCell(dtsWs, dtsRow, 2, "role")
writeCell(dtsWs, dtsRow, 3, "")
writeCell(dtsWs, dtsRow, 4, roleType.definition)
writeCell(dtsWs, dtsRow, 5, roleURI)
dtsRow += 1
# tree walk recursive function
def treeWalk(row, depth, concept, preferredLabel, arcrole, preRelSet,
visited):
if concept is not None:
# calc parents
calcRelSet = dts.relationshipSet(XbrlConst.summationItem,
preRelSet.linkrole)
calcRel = None
for modelRel in calcRelSet.toModelObject(concept):
calcRel = modelRel
break
for i, hdr in enumerate(conceptsWsHeaders):
colType = hdr[1]
value = ""
if colType == "name":
value = str(concept.name)
elif colType == "prefix" and concept.qname is not None:
value = concept.qname.prefix
elif colType == "type" and concept.type is not None:
value = str(concept.type.qname)
elif colType == "substitutionGroup":
value = str(concept.substitutionGroupQname)
elif colType == "abstract":
value = "true" if concept.isAbstract else "false"
elif colType == "nillable":
if concept.isNillable:
value = "true"
elif colType == "periodType":
value = concept.periodType
elif colType == "balance":
value = concept.balance
elif colType == "label":
role = hdr[2]
lang = hdr[3]
if role == XbrlConst.standardLabel:
if "indented" in hdr:
roleUri = preferredLabel
elif "overridePreferred" in hdr:
if preferredLabel and preferredLabel != XbrlConst.standardLabel:
roleUri = role
else:
roleUri = "**no value**" # skip putting a value in this column
else:
roleUri = role
else:
roleUri = role
if roleUri != "**no value**":
value = concept.label(
roleUri,
linkroleHint=preRelSet.linkrole,
lang=lang,
fallbackToQname=(role == XbrlConst.standardLabel))
elif colType == "preferredLabel" and preferredLabel:
if preferredLabel.startswith(
"http://www.xbrl.org/2003/role/"):
value = os.path.basename(preferredLabel)
else:
value = preferredLabel
elif colType == "calculationParent" and calcRel is not None:
calcParent = calcRel.fromModelObject
if calcParent is not None:
value = str(calcParent.qname)
elif colType == "calculationWeight" and calcRel is not None:
value = calcRel.weight
elif colType == "depth":
value = depth
if "indented" in hdr:
indent = min(depth, MAXINDENT)
else:
indent = 0
writeCell(conceptsWs, row, i + 1, value, indent=indent)
row += 1
if concept not in visited:
visited.add(concept)
for modelRel in preRelSet.fromModelObject(concept):
if modelRel.toModelObject is not None:
row = treeWalk(row, depth + 1, modelRel.toModelObject,
modelRel.preferredLabel, arcrole,
preRelSet, visited)
visited.remove(concept)
return row
# use presentation relationships for conceptsWs
arcrole = XbrlConst.parentChild
# sort URIs by definition
linkroleUris = []
relationshipSet = dts.relationshipSet(arcrole)
if relationshipSet:
for linkroleUri in relationshipSet.linkRoleUris:
modelRoleTypes = dts.roleTypes.get(linkroleUri)
if modelRoleTypes:
roledefinition = (modelRoleTypes[0].genLabel(strip=True)
or modelRoleTypes[0].definition
or linkroleUri)
else:
roledefinition = linkroleUri
linkroleUris.append((roledefinition, linkroleUri))
linkroleUris.sort()
# for each URI in definition order
for roledefinition, linkroleUri in linkroleUris:
# write linkrole
writeCell(
conceptsWs,
conceptsRow,
1, (roledefinition or linkroleUri),
borders=False) # ELR has no boarders, just font specified
conceptsRow += 1
# write header row
for i, hdr in enumerate(conceptsWsHeaders):
writeCell(conceptsWs, conceptsRow, i + 1, hdr[0], hdr=True)
conceptsRow += 1
# elr relationships for tree walk
linkRelationshipSet = dts.relationshipSet(arcrole, linkroleUri)
for rootConcept in linkRelationshipSet.rootConcepts:
conceptsRow = treeWalk(conceptsRow, 0, rootConcept, None,
arcrole, linkRelationshipSet, set())
conceptsRow += 1 # double space rows between tables
else:
# write header row
for i, hdr in enumerate(conceptsWsHeaders):
writeCell(conceptsWs, conceptsRow, i, hdr[0], hdr=True)
conceptsRow += 1
# get lang
lang = None
for i, hdr in enumerate(conceptsWsHeaders):
colType = hdr[1]
if colType == "label":
lang = hdr[3]
if colType == "label":
role = hdr[2]
lang = hdr[3]
lbls = defaultdict(list)
for concept in set(dts.qnameConcepts.values(
)): # may be twice if unqualified, with and without namespace
lbls[concept.label(role, lang=lang)].append(concept.objectId())
srtLbls = sorted(lbls.keys())
excludedNamespaces = XbrlConst.ixbrlAll.union(
(XbrlConst.xbrli, XbrlConst.link, XbrlConst.xlink, XbrlConst.xl,
XbrlConst.xbrldt, XbrlConst.xhtml))
for label in srtLbls:
for objectId in lbls[label]:
concept = dts.modelObject(objectId)
if concept.modelDocument.targetNamespace not in excludedNamespaces:
for i, hdr in enumerate(conceptsWsHeaders):
colType = hdr[1]
value = ""
if colType == "name":
value = str(concept.qname.localName)
elif colType == "prefix":
value = concept.qname.prefix
elif colType == "type":
value = str(concept.type.qname)
elif colType == "substitutionGroup":
value = str(concept.substitutionGroupQname)
elif colType == "abstract":
value = "true" if concept.isAbstract else "false"
elif colType == "periodType":
value = concept.periodType
elif colType == "balance":
value = concept.balance
elif colType == "label":
role = hdr[2]
lang = hdr[3]
value = concept.label(role, lang=lang)
elif colType == "depth":
value = 0
if "indented" in hdr:
indent = min(0, MAXINDENT)
else:
indent = 0
writeCell(conceptsWs,
conceptsRow,
i,
value,
indent=indent)
conceptsRow += 1
try:
workbook.save(excelFile)
dts.info("info:saveLoadableExcel",
_("Saved Excel file: %(excelFile)s"),
excelFile=os.path.basename(excelFile),
modelXbrl=dts)
except Exception as ex:
dts.error("exception:saveLoadableExcel",
_("File saving exception: %(error)s"),
error=ex,
modelXbrl=dts)