def index_init():
file = load_workbook('app/templates/079254C-0000-ML-100_0.xlsx')
sheet_list = file.sheetnames
new_file = Workbook()
new_ws = new_file.active
print('')
#print(main_folder)
print('')
index_rows = []
#f = open('index_assiut.html', 'w')
for sheet in sheet_list:
sheet = file[sheet]
sec1 = sheet['B13'].value
sec2 = sheet['B15'].value
sec3 = sheet['B17'].value
if sheet['B17'].value is not None:
para2 = sec3.split(' ')[0]
else:
para2 = sec2.split(' ')[0]
for row in sheet.iter_rows(min_row=2):
cell = row[2]
if cell.value is not None and cell.value[0:7] == '079254C':
'''
cell.hyperlink = Hyperlink('link',
target='5.1.1/' + cell.value + "_" + row[3].value,
#location='5.1.1/' + cell.value + "_" + row[3].value,
display='something')
'''
cell_start_value = str(cell.value)
#cell.value = '=HYPERLINK("5.1.1\\' + cell_start_value + "_" + row[3].value +'"'+',"' + cell.value + '")'
#cell.value = "=HYPERLINK('5.1.1/" + cell.value + "_" + row[3].value + "','" + cell.value + "')"
cell.hyperlink = Hyperlink(
'link',
target='.\\FILE\\' + para2 + '\\' + str(cell_start_value) +
"_" + str(row[3].value),
#location='5.1.1/' + cell.value + "_" + row[3].value,
display='something')
print(cell.value)
link1 = row[3]
link1.hyperlink = Hyperlink(
'link',
target='.\\FILE\\' + para2 + '\\' + cell_start_value +
"_" + str(row[3].value),
#location='5.1.1/' + cell.value + "_" + row[3].value,
display='something')
index_rows.append((cell_start_value, cell.value))
try:
new_ws.append(
(sec1, sec2, sec3, row[1].value, cell_start_value,
row[3].value, row[4].value))
except:
new_ws.append((sec1, sec2, sec3, row[1].value,
cell_start_value, row[3].value))
file.save('tavole.xlsx')
new_file.save('report_cliente.xlsx')
'''
def hyperlink(self, val):
"""Set value and display for hyperlinks in a cell.
Automatically sets the `value` of the cell with link text,
but you can modify it afterwards by setting the `value`
property, and the hyperlink will remain."""
if not isinstance(val, Hyperlink):
val = Hyperlink(ref="", target=val)
val.ref = self.coordinate
self._hyperlink = val
if self._value is None:
self.value = val.target or val.location
def dataSelect():
global opsumSheets
global wbOpsum
global nameOpsum
global nameData
global wsOpsumSamlet
global wsGennemsnit
global wsTemplate
global sheetCount
global opsummeringData
global samletCount
global samletData
global directory
if (nameOpsum != "Intet dokument valgt"):
#loading chosen Workbook
root.filename = filedialog.askopenfilename(
initialdir=directory,
title="Select file",
filetypes=(("Excel files", "*.xlsx"), ("All files", "*.*")))
nameDataList = root.filename.split("/")
nameData = nameDataList[len(nameDataList) - 1]
wbData = load_workbook(root.filename, data_only=True)
wsData = wbData.active
#Choosing first free sheet
wsOutput = wbOpsum["Template (" + str(sheetCount + 1) + ")"]
#choosing 'samlet sheet'
wsSamlet = wbOpsum['Samlet']
startCelle = 'C' + str(samletCount)
slutCelle = 'J' + str(samletCount)
samletDataCells = wsSamlet[startCelle:slutCelle] #Belbin numbers
#Inputting data to wsOutput
outputDataCells = wsData["B102":"I102"] #Belbin numbers
for row in outputDataCells:
k = 0
for cell in row:
wsOutput[opsummeringData[k]].value = cell.value
wsSamlet[(str(samletData[k]) +
str(samletCount))].value = cell.value
k += 1
wsOutput.title = wsData["B3"].value #updating opsum sheet title
wsOutput["B2"].value = wsData["B3"].value #Data Name
nameCell = 'B' + str(samletCount)
wsSamlet[nameCell].hyperlink = Hyperlink(nameCell,
"'" + wsOutput.title + "'!A1",
None, wsData["B3"].value)
wsSamlet[nameCell].value = wsData["B3"].value
samletCount += 1 #updating samletCount
sheetCount += 1 #Updating sheetCount
wbOpsum.save(directory + nameOpsum)
#Updates Label
textData = "Dokument sidst valgt: " + nameData
vData.set(textData)
else:
messagebox.showinfo("Fejl", "Du skal vælge et samle-dokument først :P")
def hyperlink(self, val):
"""Set value and display for hyperlinks in a cell.
Automatically sets the `value` of the cell with link text,
but you can modify it afterwards by setting the `value`
property, and the hyperlink will remain."""
self._hyperlink = Hyperlink(ref=self.coordinate, target=val)
if self._value is None:
self.value = val
def extract_links(value: str) -> Tuple[str, List[Hyperlink]]:
"""Преобразуем ссылки с HYPERLINK если они имеются"""
hyperlink_list = []
count = 0
for word in value.split('\n'):
if is_link(word.strip()):
hyperlink = Hyperlink(ref=f"Файл {count+1}", location=word)
hyperlink_list.append(hyperlink)
count += 1
return value, hyperlink_list
def parse_hyperlinks(self, element):
link = Hyperlink.from_tree(element)
if link.id:
rel = self.ws._rels[link.id]
link.target = rel.Target
if ":" in link.ref:
# range of cells
for row in self.ws[link.ref]:
for cell in row:
cell.hyperlink = link
else:
self.ws[link.ref].hyperlink = link
def parse_hyperlinks(self, element):
link = Hyperlink.from_tree(element)
if link.id:
rel = self.ws._rels[link.id]
link.target = rel.Target
if ":" in link.ref:
# range of cells
for row in self.ws[link.ref]:
for cell in row:
cell.hyperlink = link
else:
self.ws[link.ref].hyperlink = link
def test_internal_hyperlink(worksheet):
from .. worksheet import write_hyperlinks
from openpyxl.worksheet.hyperlink import Hyperlink
ws = worksheet
cell = ws['A1']
cell.hyperlink = Hyperlink(ref="", location="'STP nn000TL-10, PKG 2.52'!A1")
ws._hyperlinks.append(cell.hyperlink)
hyper = write_hyperlinks(ws)
xml = tostring(hyper.to_tree())
expected = """
<hyperlinks>
<hyperlink location="'STP nn000TL-10, PKG 2.52'!A1" ref="A1"/>
</hyperlinks>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def create_index_entry(row, index_entry):
"""Create a row on the index sheet."""
subject_name, subject_type, function_name, data_series, sheet_name = index_entry
index_ws.cell(row=row, column=1).value = subject_name
index_ws.cell(row=row, column=2).value = subject_type
index_ws.cell(row=row, column=3).value = function_name
index_ws.cell(row=row, column=4).value = data_series
hyperlink_cell = index_ws.cell(row=row, column=5)
hyperlink_label = f"Click to jump to sheet '{sheet_name}'"
hyperlink_location = f"'{sheet_name}'!B2" # don't use # here before sheet name, does not work
# Hyperlink class: target keyword seems to be used for external links
# hyperlink_cell.value = f'=HYPERLINK("{hyperlink_location}", "{hyperlink_label}")'
# didn't manage to get it working with HYPERLINK function
hyperlink_cell.value = hyperlink_label
hyperlink_cell.hyperlink = Hyperlink(
ref=f"E{row}",
location=hyperlink_location,
tooltip=f"Click to jump to sheet '{sheet_name}' with data")
hyperlink_cell.style = "Hyperlink"
def download_plot_analyses_to_xlsx(request, analyses):
"""Download plot data for given analyses as XLSX file.
Parameters
----------
request
HTTPRequest
analyses
Sequence of Analysis instances
Returns
-------
HTTPResponse
"""
# TODO: We need a mechanism for embedding references to papers into output.
# Pack analysis results into a single text file.
excel_file_buffer = io.BytesIO()
excel_writer = pd.ExcelWriter(excel_file_buffer)
bold_font = Font(bold=True)
#
# Create sheet with meta data
#
meta_df = _analyses_meta_data_dataframe(analyses, request)
meta_df.to_excel(excel_writer,
sheet_name='INFORMATION',
index=False,
freeze_panes=(1, 0))
# Analyze subject names and store a distinct name
# which can be used in sheet names if subject names are not unique
subject_names_in_sheet_names = [a.subject.name for a in analyses]
for sn in set(subject_names_in_sheet_names): # iterate over distinct names
# replace name with a unique one using a counter
indices = [i for i, a in enumerate(analyses) if a.subject.name == sn]
if len(indices) > 1: # only rename if not unique
for k, idx in enumerate(indices):
subject_names_in_sheet_names[idx] += f" ({k + 1})"
def comment_on_average(y, std_err_y_masked):
"""Calculate a comment.
Parameters:
y: float
std_err_y_masked: boolean
"""
if np.isnan(y):
return 'average could not be computed'
elif std_err_y_masked:
return 'no error could be computed because the average contains only a single data point'
return ''
index_entries = [
] # tuples with (subject name, subject type, function name, data series, hyperlink to sheet)
for analysis_idx, analysis in enumerate(analyses):
result = pickle.loads(analysis.result)
column1 = '{} ({})'.format(result['xlabel'], result['xunit'])
column2 = '{} ({})'.format(result['ylabel'], result['yunit'])
column3 = 'standard error of {} ({})'.format(result['ylabel'],
result['yunit'])
column4 = 'comment'
for series_idx, series in enumerate(result['series']):
df_columns_dict = {column1: series['x'], column2: series['y']}
try:
std_err_y_mask = series['std_err_y'].mask
except (AttributeError, KeyError) as exc:
std_err_y_mask = np.zeros(series['y'].shape, dtype=bool)
try:
df_columns_dict[column3] = series['std_err_y']
df_columns_dict[column4] = [
comment_on_average(y, masked)
for y, masked in zip(series['y'], std_err_y_mask)
]
except KeyError:
pass
df = pd.DataFrame(df_columns_dict)
#
# Save data for index entry
#
sheet_name = f"analysis-{analysis_idx}-series-{series_idx}"
subject_type = analysis.subject.get_content_type(
).name # human-readable name
if subject_type == 'topography':
subject_type = 'measurement' # this is how topographies are denoted in the UI
index_entries.append(
(analysis.subject.name, subject_type, analysis.function.name,
series['name'], sheet_name))
#
# Write data sheet to excel file
#
df.to_excel(excel_writer,
sheet_name=sheet_name,
freeze_panes=(6, 1),
startcol=0,
startrow=5)
sheet = excel_writer.sheets[sheet_name]
sheet["A1"] = "Analysis"
sheet["A1"].font = bold_font
sheet["B1"] = analysis.function.name
sheet["A2"] = "Subject"
sheet["A2"].font = bold_font
sheet["B2"] = analysis.subject.name
sheet["A3"] = "Subject Type"
sheet["A3"].font = bold_font
sheet["B3"] = subject_type
sheet["A4"] = "Data Series"
sheet["A4"].font = bold_font
sheet["B4"] = series['name']
sheet.column_dimensions['A'].width = 20
sheet.column_dimensions['B'].width = 20
sheet.column_dimensions['C'].width = 20
sheet.column_dimensions['D'].width = 25
# Link "Back to Index"
sheet["D1"].hyperlink = Hyperlink(
ref=f"D1",
location="'INDEX'!A1",
tooltip=f"Click to jump back to INDEX")
sheet["D1"].value = "Click to jump back to INDEX"
sheet["D1"].style = "Hyperlink"
excel_writer.close()
filename = '{}.xlsx'.format(analysis.function.name).replace(' ', '_')
#
# Create INDEX sheet with links in Openpyxl
#
wb = openpyxl.load_workbook(excel_file_buffer)
excel_file_buffer.close()
index_ws = wb.create_sheet("INDEX", 0)
index_headers = [
"Subject Name", "Subject Type", "Function Name", "Data Series", "Link"
]
for col_idx, col_header in enumerate(index_headers):
header_cell = index_ws.cell(row=1, column=col_idx + 1)
header_cell.value = col_header
header_cell.font = bold_font
def create_index_entry(row, index_entry):
"""Create a row on the index sheet."""
subject_name, subject_type, function_name, data_series, sheet_name = index_entry
index_ws.cell(row=row, column=1).value = subject_name
index_ws.cell(row=row, column=2).value = subject_type
index_ws.cell(row=row, column=3).value = function_name
index_ws.cell(row=row, column=4).value = data_series
hyperlink_cell = index_ws.cell(row=row, column=5)
hyperlink_label = f"Click to jump to sheet '{sheet_name}'"
hyperlink_location = f"'{sheet_name}'!B2" # don't use # here before sheet name, does not work
# Hyperlink class: target keyword seems to be used for external links
# hyperlink_cell.value = f'=HYPERLINK("{hyperlink_location}", "{hyperlink_label}")'
# didn't manage to get it working with HYPERLINK function
hyperlink_cell.value = hyperlink_label
hyperlink_cell.hyperlink = Hyperlink(
ref=f"E{row}",
location=hyperlink_location,
tooltip=f"Click to jump to sheet '{sheet_name}' with data")
hyperlink_cell.style = "Hyperlink"
for entry_idx, index_entry in enumerate(index_entries):
create_index_entry(entry_idx + 2, index_entry)
# create_index_entry(len(index_entries) + 2, ("META DATA", '', '', '', 'META DATA'))
# increase column width on index page
for col, colwidth in [("A", 30), ("B", 15), ("C", 25), ("D", 30),
("E", 45)]:
index_ws.column_dimensions[col].width = colwidth
#
# Save to named temporary file and prepare response
#
with tempfile.NamedTemporaryFile() as tmp:
wb.save(tmp.name)
tmp.seek(0)
response = HttpResponse(
tmp.read(),
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response['Content-Disposition'] = 'attachment; filename="{}"'.format(
filename)
return response
