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 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