def fitauto(sheet):
"""
Cette fonction permet d'ajuster la taille des colonnes en fonction de la taille de cellules max afin d'avoir un contenu
homogène.
----------
sheet : WorkBook
Feuille d'un fichier excel
"""
from openpyxl.utils.cell import _get_column_letter
column_widths = []
for row in sheet.iter_rows():
for i, cell in enumerate(
row): #Pour chaque cellule de la feuille par ligne
try:
size = len(str(cell.value))
column_widths[i] = max(
column_widths[i], size
) #Compare la valeur de la largeur de colonne à celle de ces cellules
# print(column_widths)
#print(str(cell.value) + " && " + str(column_widths[i]) + " index: " + str(i))
except IndexError:
column_widths.append(len(str(cell.value)))
print(column_widths)
if column_widths.__len__() == 9:
column_widths[
8] = 100 #Changement de la largeur de colonne qui sera fixe pour la colonne tour de parole
for i, column_width in enumerate(column_widths):
# print(column_width)
sheet.column_dimensions[_get_column_letter(
i + 1
)].width = column_width + 1 #On fixe la taille max selon le tableau column_widths
def get_sheet_headings(self, sheet_name):
worksheet = self.workbook[self.sheet_names_map[sheet_name]]
sheet_configuration = self.sheet_configuration[self.sheet_names_map[sheet_name]]
configuration_line = 1 if sheet_configuration else 0
if not sheet_configuration:
sheet_configuration = self.base_configuration
if not self.use_configuration:
sheet_configuration = {}
skip_rows = sheet_configuration.get("skipRows", 0)
if sheet_configuration.get("ignore") or (
sheet_configuration.get("hashcomments") and sheet_name.startswith("#")
):
# returning empty headers is a proxy for no data in the sheet.
return []
if self.vertical_orientation:
return [
cell.value
for cell in worksheet[_get_column_letter(skip_rows + 1)][
configuration_line:
]
]
try:
return [
cell.value for cell in worksheet[skip_rows + configuration_line + 1]
]
except IndexError:
# If the heading line is after data in the spreadsheet. i.e when skipRows
return []
def get_sheet_lines(self, sheet_name):
sheet_configuration = self.sheet_configuration[self.sheet_names_map[sheet_name]]
configuration_line = 1 if sheet_configuration else 0
if not sheet_configuration:
sheet_configuration = self.base_configuration
if not self.use_configuration:
sheet_configuration = {}
skip_rows = sheet_configuration.get("skipRows", 0)
header_rows = sheet_configuration.get("headerRows", 1)
worksheet = self.workbook[self.sheet_names_map[sheet_name]]
if self.vertical_orientation:
header_row = worksheet[_get_column_letter(skip_rows + 1)]
remaining_rows = worksheet.iter_cols(min_col=skip_rows + header_rows + 1)
if configuration_line:
header_row = header_row[1:]
remaining_rows = worksheet.iter_cols(min_col=skip_rows + header_rows + 1, min_row=2)
else:
header_row = worksheet[skip_rows + configuration_line + 1]
remaining_rows = worksheet.iter_rows(min_row=skip_rows + configuration_line + header_rows + 1)
coli_to_header = {}
for i, header in enumerate(header_row):
if header.value is None:
continue
if sheet_configuration.get("hashcomments") and str(header.value).startswith('#'):
continue
coli_to_header[i] = header.value
for row in remaining_rows:
yield OrderedDict((coli_to_header[i], x.value) for i, x in enumerate(row) if i in coli_to_header)
def get_sheet_lines(self, sheet_name):
sheet_configuration = self.sheet_configuration[
self.sheet_names_map[sheet_name]]
configuration_line = 1 if sheet_configuration else 0
if not sheet_configuration:
sheet_configuration = self.base_configuration
if not self.use_configuration:
sheet_configuration = {}
skip_rows = sheet_configuration.get("skipRows", 0)
header_rows = sheet_configuration.get("headerRows", 1)
worksheet = self.workbook[self.sheet_names_map[sheet_name]]
if self.vertical_orientation:
header_row = worksheet[_get_column_letter(skip_rows + 1)]
remaining_rows = worksheet.iter_cols(min_col=skip_rows +
header_rows + 1)
if configuration_line:
header_row = header_row[1:]
remaining_rows = worksheet.iter_cols(min_col=skip_rows +
header_rows + 1,
min_row=2)
else:
header_row = worksheet[skip_rows + configuration_line + 1]
remaining_rows = worksheet.iter_rows(
min_row=skip_rows + configuration_line + header_rows + 1)
coli_to_header = {}
for i, header in enumerate(header_row):
coli_to_header[i] = header.value
for row in remaining_rows:
output_row = OrderedDict()
for i, x in enumerate(row):
header = coli_to_header[i]
value = x.value
if not header:
# None means that the cell will be ignored
value = None
elif (sheet_configuration.get("hashcomments")
and isinstance(header, str) and header.startswith("#")):
# None means that the cell will be ignored
value = None
output_row[header] = value
yield output_row
def get_sheet_headings(self, sheet_name):
worksheet = self.workbook[self.sheet_names_map[sheet_name]]
sheet_configuration = self.sheet_configuration[self.sheet_names_map[sheet_name]]
configuration_line = 1 if sheet_configuration else 0
if not sheet_configuration:
sheet_configuration = self.base_configuration
if not self.use_configuration:
sheet_configuration = {}
skip_rows = sheet_configuration.get("skipRows", 0)
if (sheet_configuration.get("ignore") or
(sheet_configuration.get("hashcomments") and sheet_name.startswith('#'))):
# returning empty headers is a proxy for no data in the sheet.
return []
if self.vertical_orientation:
return [cell.value for cell in worksheet[_get_column_letter(skip_rows + 1)][configuration_line:]]
try:
return [cell.value for cell in worksheet[skip_rows + configuration_line + 1]]
except IndexError:
# If the heading line is after data in the spreadsheet. i.e when skipRows
return []
def do_unflatten(self):
main_sheet_by_ocid = OrderedDict()
sheets = list(self.get_sub_sheets_lines())
for i, sheet in enumerate(sheets):
sheet_name, lines = sheet
try:
actual_headings = self.get_sheet_headings(sheet_name)
# If sheet is empty or too many lines have been skipped
if not actual_headings:
continue
found = OrderedDict()
last_col = len(actual_headings)
# We want to ignore data in earlier columns, so we look
# through the data backwards
for i, actual_heading in enumerate(reversed(actual_headings)):
if actual_heading is None:
continue
if actual_heading in found:
found[actual_heading].append((last_col - i) - 1)
else:
found[actual_heading] = [i]
for actual_heading in reversed(found):
if len(found[actual_heading]) > 1:
keeping = found[actual_heading][0] # noqa
ignoring = found[actual_heading][1:]
ignoring.reverse()
if len(ignoring) >= 3:
warn(
(
'Duplicate heading "{}" found, ignoring '
"the data in columns {} and {}."
).format(
actual_heading,
", ".join(
[
_get_column_letter(x + 1)
for x in ignoring[:-1]
]
),
_get_column_letter(ignoring[-1] + 1),
),
DataErrorWarning,
)
elif len(found[actual_heading]) == 3:
warn(
(
'Duplicate heading "{}" found, ignoring '
"the data in columns {} and {}."
).format(
actual_heading,
_get_column_letter(ignoring[0] + 1),
_get_column_letter(ignoring[1] + 1),
),
DataErrorWarning,
)
else:
warn(
(
'Duplicate heading "{}" found, ignoring '
"the data in column {}."
).format(
actual_heading, _get_column_letter(ignoring[0] + 1),
),
DataErrorWarning,
)
except NotImplementedError:
# The ListInput type used in the tests doesn't support getting headings.
actual_headings = None
for j, line in enumerate(lines):
if all(x is None or x == "" for x in line.values()):
# if all(x == '' for x in line.values()):
continue
root_id_or_none = line.get(self.root_id) if self.root_id else None
cells = OrderedDict()
for k, header in enumerate(line):
heading = actual_headings[k] if actual_headings else header
if self.vertical_orientation:
# This is misleading as it specifies the row number as the distance vertically
# and the horizontal 'letter' as a number.
# https://github.com/OpenDataServices/flatten-tool/issues/153
cells[header] = Cell(
line[header], (sheet_name, str(k + 1), j + 2, heading)
)
else:
cells[header] = Cell(
line[header],
(sheet_name, _get_column_letter(k + 1), j + 2, heading),
)
unflattened = unflatten_main_with_parser(
self.parser, cells, self.timezone, self.xml, self.id_name
)
if root_id_or_none not in main_sheet_by_ocid:
main_sheet_by_ocid[root_id_or_none] = TemporaryDict(
self.id_name, xml=self.xml
)
def inthere(unflattened, id_name):
if self.xml:
return unflattened[id_name]["text()"].cell_value
else:
return unflattened[id_name].cell_value
if (
self.id_name in unflattened
and inthere(unflattened, self.id_name)
in main_sheet_by_ocid[root_id_or_none]
):
if self.xml:
unflattened_id = unflattened.get(self.id_name)[
"text()"
].cell_value
else:
unflattened_id = unflattened.get(self.id_name).cell_value
merge(
main_sheet_by_ocid[root_id_or_none][unflattened_id],
unflattened,
{
"sheet_name": sheet_name,
"root_id": self.root_id,
"root_id_or_none": root_id_or_none,
"id_name": self.id_name,
self.id_name: unflattened_id,
},
)
else:
main_sheet_by_ocid[root_id_or_none].append(unflattened)
temporarydicts_to_lists(main_sheet_by_ocid)
return sum(main_sheet_by_ocid.values(), [])
def report_from_template(dft, df_cy16, filename):
# Load the Template
wb = load_workbook(settings.BASE_DIR + "/media/reports/KPI_Template.xlsx")
# select the data sheet
ws_data = wb.get_sheet_by_name("Data")
wb.remove_sheet(ws_data)
ws_data = wb.create_sheet('Data', 0)
for r in dataframe_to_rows(dft, index=True, header=True):
ws_data.append(r)
# ws_chart = wb.create_sheet('Chart')
# img = openpyxl.drawing.image.Image('7.jpg')
# img.anchor(ws_chart.cell('C4'))
# ws_chart.add_image(img)
# select the sheet
ws = wb.get_sheet_by_name("CY16")
# Get the last row
end_row = ws.max_row
#####################################################################################################
#
# Integrate Code Above
#
#####################################################################################################
index_col_cells, col_rows = get_cell_refs_for_index_and_cols()
#####################################################################################################
# Styles
# We need to make sure to manually edit the template file that we are using in order to ensure that
# our formatting during the script works appropriately
# Insert Column Headers
for i, col in enumerate(list(df_cy16.columns)):
ws[str(_get_column_letter(i + 2) + "1")].value = col
# Index of DataFrame
zipped_ref_and_index_values = zip(index_col_cells, list(df_cy16.index))
for input_ref_value in zipped_ref_and_index_values:
ws[input_ref_value[0]].value = input_ref_value[1]
ws[input_ref_value[0]].alignment = Alignment(horizontal='right')
# All Columns in DataFrame
for i, col_name in enumerate(list(df_cy16.columns)):
col_cell_refs = [
str(_get_column_letter(i + 2) + row_ref) for row_ref in col_rows
]
zipped_ref_and_col_values = zip(col_cell_refs,
list(df_cy16.ix[:, i].values))
for input_ref_value in zipped_ref_and_col_values:
ws[input_ref_value[0]].value = input_ref_value[1]
ws[input_ref_value[0]].alignment = Alignment(horizontal='right')
# Insert Remaining Table Headers specific to this Table
table_headers = [
'Program Summary', 'ADM Optimization', 'ADM Stock Outs',
'ADM Scan Rate on Refill Transactions'
]
table_headers_refs = ['A2', 'A8', 'A21', 'A32']
zipped_ref_and_table_headers = zip(table_headers_refs, table_headers)
for input_ref_value in zipped_ref_and_table_headers:
ws[input_ref_value[0]].value = input_ref_value[1]
ws[input_ref_value[0]].alignment = Alignment(horizontal='right')
wb.save(filename)
def do_unflatten(self):
main_sheet_by_ocid = OrderedDict()
sheets = list(self.get_sub_sheets_lines())
for i, sheet in enumerate(sheets):
sheet_name, lines = sheet
try:
actual_headings = self.get_sheet_headings(sheet_name)
# If sheet is empty or too many lines have been skipped
if not actual_headings:
continue
found = OrderedDict()
last_col = len(actual_headings)
# We want to ignore data in earlier columns, so we look
# through the data backwards
for i, actual_heading in enumerate(reversed(actual_headings)):
if actual_heading is None:
continue
if actual_heading in found:
found[actual_heading].append((last_col-i)-1)
else:
found[actual_heading] = [i]
for actual_heading in reversed(found):
if len(found[actual_heading]) > 1:
keeping = found[actual_heading][0]
ignoring = found[actual_heading][1:]
ignoring.reverse()
if len(ignoring) >= 3:
warn(
(
'Duplicate heading "{}" found, ignoring '
'the data in columns {} and {}.'
).format(
actual_heading,
', '.join(
[_get_column_letter(x+1) for x in ignoring[:-1]]
),
_get_column_letter(ignoring[-1] + 1),
),
DataErrorWarning
)
elif len(found[actual_heading]) == 3:
warn(
(
'Duplicate heading "{}" found, ignoring '
'the data in columns {} and {}.'
).format(
actual_heading,
_get_column_letter(ignoring[0] + 1),
_get_column_letter(ignoring[1] + 1),
),
DataErrorWarning
)
else:
warn(
(
'Duplicate heading "{}" found, ignoring '
'the data in column {}.'
).format(
actual_heading,
_get_column_letter(ignoring[0]+1),
),
DataErrorWarning
)
except NotImplementedError:
# The ListInput type used in the tests doesn't support getting headings.
actual_headings = None
for j, line in enumerate(lines):
if all(x is None or x == '' for x in line.values()):
#if all(x == '' for x in line.values()):
continue
root_id_or_none = line.get(self.root_id) if self.root_id else None
cells = OrderedDict()
for k, header in enumerate(line):
heading = actual_headings[k] if actual_headings else header
if self.vertical_orientation:
# This is misleading as it specifies the row number as the distance vertically
# and the horizontal 'letter' as a number.
# https://github.com/OpenDataServices/flatten-tool/issues/153
cells[header] = Cell(line[header], (sheet_name, str(k+1), j+2, heading))
else:
cells[header] = Cell(line[header], (sheet_name, _get_column_letter(k+1), j+2, heading))
unflattened = unflatten_main_with_parser(self.parser, cells, self.timezone, self.xml, self.id_name)
if root_id_or_none not in main_sheet_by_ocid:
main_sheet_by_ocid[root_id_or_none] = TemporaryDict(self.id_name, xml=self.xml)
def inthere(unflattened, id_name):
if self.xml:
return unflattened[id_name]['text()'].cell_value
else:
return unflattened[id_name].cell_value
if self.id_name in unflattened and inthere(unflattened, self.id_name) in main_sheet_by_ocid[root_id_or_none]:
if self.xml:
unflattened_id = unflattened.get(self.id_name)['text()'].cell_value
else:
unflattened_id = unflattened.get(self.id_name).cell_value
merge(
main_sheet_by_ocid[root_id_or_none][unflattened_id],
unflattened,
{
'sheet_name': sheet_name,
'root_id': self.root_id,
'root_id_or_none': root_id_or_none,
'id_name': self.id_name,
self.id_name: unflattened_id
}
)
else:
main_sheet_by_ocid[root_id_or_none].append(unflattened)
temporarydicts_to_lists(main_sheet_by_ocid)
return sum(main_sheet_by_ocid.values(), [])
style = "date"
elif "€" in row[key]:
style = "currency"
else:
try:
num = int(row[key])
style = "numeric"
except ValueError:
style = "text"
# write cell;
cell.style = style
cell.value = row[key]
# apply width values;
for c, cell_width in enumerate(column_widths, start_column):
column = _get_column_letter(c)
sheet.column_dimensions[column].width = cell_width
# create excel file and remove csv.
try:
book.save("generatedata.com.xlsx")
except OSError as error:
print("ERROR saving excel file.", error)
sys.exit(1)
else:
send2trash("generatedata.com.csv")
sys.exit(0)
elif os.path.exists("generatedata.com.xlsx"):
# load excel file;
book = openpyxl.load_workbook(filename="generatedata.com.xlsx")