def add_signature_image(sheet): xfile = openpyxl.load_workbook(filepath) sheet = xfile.get_sheet_by_name('Time Card') img = Image('signature_scan.gif') img.drawing.width = 100 img.drawing.height = 50 img.anchor(sheet.cell('E35')) sheet.add_image(img)
def find_images(archive, path): """ Given the path to a drawing file extract anchors with images """ src = archive.read(path) tree = fromstring(src) drawing = SpreadsheetDrawing.from_tree(tree) rels_path = get_rels_path(path) deps = [] if rels_path in archive.namelist(): deps = get_dependents(archive, rels_path) images = [] for rel in drawing._blip_rels: dep = deps[rel.embed] if dep.Type == IMAGE_NS: image = Image(BytesIO(archive.read(dep.target))) image.anchor = rel.anchor images.append(image) return images
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "difference_values" not in report['reporting_period'].keys() or \ len(report['reporting_period']['difference_values']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################################# has_difference_values_data_flag = True if 'difference_values' not in report['reporting_period'].keys() or len( report['reporting_period']['difference_values']) == 0: has_difference_values_data_flag = False current_row_number = 6 if has_difference_values_data_flag: reporting_period_data = report['reporting_period'] category = report['meter']['energy_category_name'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 报告期' current_row_number += 1 ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border if not isinstance(category, list): ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = report['meter'][ 'energy_category_name'] + " (" + report['meter'][ 'unit_of_measure'] + ")" current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '总表消耗' ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['master_meter_consumption_in_category'], 2) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '分表消耗' ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['submeters_consumption_in_category'], 2) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '差值' ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['difference_in_category'], 2) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '差值百分比' ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = str( round(reporting_period_data['percentage_difference'] * 100, 2)) + '%' current_row_number += 2 time = reporting_period_data['timestamps'] parameters_names_len = len(report['parameters']['names']) parameters_data = report['parameters'] parameters_parameters_datas_len = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue parameters_parameters_datas_len += 1 has_time_data_flag = False if time is not None and len(time) > 0: has_time_data_flag = True if has_time_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' current_row_number += 1 chart_start_number = current_row_number current_row_number = current_row_number + 1 + len( category) * 6 + parameters_parameters_datas_len * 6 table_start_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = '日期时间' ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)] = report['meter'][ 'energy_category_name'] + " (" + report['meter'][ 'unit_of_measure'] + ")" current_row_number += 1 for i in range(0, len(time)): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = time[i] ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)] = round( reporting_period_data['difference_values'][i], 2) current_row_number += 1 table_end_number = current_row_number - 1 line = LineChart() line.title = '报告期差值 - ' + report['meter'][ 'energy_category_name'] + " (" + report['meter'][ 'unit_of_measure'] + ")" labels = Reference(ws, min_col=2, min_row=table_start_number + 1, max_row=table_end_number) line_data = Reference(ws, min_col=3, min_row=table_start_number, max_row=table_end_number) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True # 数量显示 ws.add_chart(line, "B" + str(chart_start_number)) ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = '总计' ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)] = round( reporting_period_data[ 'master_meter_consumption_in_category'], 2) else: pass ########################################## has_parameters_names_and_timestamps_and_values_data = True # 12 is the starting line number of the last line chart in the report period category = report['meter']['energy_category_name'] ca_len = len(category) time_len = len(report['reporting_period']['timestamps']) current_sheet_parameters_row_number = 14 + ca_len * 6 if 'parameters' not in report.keys() or \ report['parameters'] is None or \ 'names' not in report['parameters'].keys() or \ report['parameters']['names'] is None or \ len(report['parameters']['names']) == 0 or \ 'timestamps' not in report['parameters'].keys() or \ report['parameters']['timestamps'] is None or \ len(report['parameters']['timestamps']) == 0 or \ 'values' not in report['parameters'].keys() or \ report['parameters']['values'] is None or \ len(report['parameters']['values']) == 0 or \ timestamps_data_all_equal_0(report['parameters']['timestamps']): has_parameters_names_and_timestamps_and_values_data = False if has_parameters_names_and_timestamps_and_values_data: ############################### # new worksheet ############################### parameters_data = report['parameters'] parameters_names_len = len(parameters_data['names']) parameters_ws = wb.create_sheet('相关参数') parameters_timestamps_data_max_len = \ get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) # Row height parameters_ws.row_dimensions[1].height = 102 for i in range(2, 7 + 1): parameters_ws.row_dimensions[i].height = 42 for i in range(8, parameters_timestamps_data_max_len + 10): parameters_ws.row_dimensions[i].height = 60 # Col width parameters_ws.column_dimensions['A'].width = 1.5 parameters_ws.column_dimensions['B'].width = 25.0 for i in range(3, 12 + parameters_names_len * 3): parameters_ws.column_dimensions[format_cell.get_column_letter( i)].width = 15.0 # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") parameters_ws.add_image(img, 'B1') # Title parameters_ws.row_dimensions[3].height = 60 parameters_ws['B3'].font = name_font parameters_ws['B3'].alignment = b_r_alignment parameters_ws['B3'] = 'Name:' parameters_ws['C3'].border = b_border parameters_ws['C3'].alignment = b_c_alignment parameters_ws['C3'].font = name_font parameters_ws['C3'] = name parameters_ws['D3'].font = name_font parameters_ws['D3'].alignment = b_r_alignment parameters_ws['D3'] = 'Period:' parameters_ws['E3'].border = b_border parameters_ws['E3'].alignment = b_c_alignment parameters_ws['E3'].font = name_font parameters_ws['E3'] = period_type parameters_ws['F3'].font = name_font parameters_ws['F3'].alignment = b_r_alignment parameters_ws['F3'] = 'Date:' parameters_ws['G3'].border = b_border parameters_ws['G3'].alignment = b_c_alignment parameters_ws['G3'].font = name_font parameters_ws[ 'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local parameters_ws.merge_cells("G3:H3") parameters_ws_current_row_number = 6 parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' 相关参数' parameters_ws_current_row_number += 1 parameters_table_start_row_number = parameters_ws_current_row_number parameters_ws.row_dimensions[ parameters_ws_current_row_number].height = 80 parameters_ws_current_row_number += 1 table_current_col_number = 2 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] table_current_row_number = parameters_ws_current_row_number for j, value in enumerate(list(parameters_data['timestamps'][i])): col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = value col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = round( parameters_data['values'][i][j], 2) table_current_row_number += 1 table_current_col_number = table_current_col_number + 3 ######################################################## # parameters chart and parameters table ######################################################## ws['B' + str(current_sheet_parameters_row_number)].font = title_font ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数' current_sheet_parameters_row_number += 1 chart_start_row_number = current_sheet_parameters_row_number col_index = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue line = LineChart() data_col = 3 + col_index * 3 labels_col = 2 + col_index * 3 col_index += 1 line.title = '相关参数 - ' + \ parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line_data = Reference( parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = False line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def export_excel(_path, _df=None, _wb=None, _sheet_name='sheet1', _letter_fmt=None, _append=False, _frz='B2', _auto_flt=True, _auto_sz=False, _header_height=None, _col_width_=[20, 20], _header_fmt=None, _header_rot=0, _zoom=100, _heatmap=0, _is_index=True, _index_name='Index', _header_txtcol='000000', _header_fillcol='d9f2f8', _img=None): import openpyxl as px from openpyxl.utils import get_column_letter time_start = time.perf_counter() if _path == None and _wb == None: print('[Error] Both of _path and _bw are None.') return print('[Exporting Excel file ...] Sheet : "{0}"'.format(_sheet_name)) #------------------------------------------- # 初期設定 #------------------------------------------- # Workbook作成 if _wb == None: if _append: # 既存ファイルにシート追加 try: wb = px.load_workbook(_path) except: _append = False # ファイルが存在しないときは新規作成 if not _append: # 新規ファイル wb = px.Workbook() else: wb = _wb _append = True # Worksheet作成 ws = wb.create_sheet(title=_sheet_name) #------------------------------------------- # DataFrameをWorksheetに書き込み #------------------------------------------- if _df is not None: #----- 作業用にDataFrameをコピー ----- df = _df.copy() # Timestampを文字列に変換(そのままだとエラーになるので) list_timestamp_col = list() # Timestampのセルが存在する列を探して文字列に変換する for col_name, col in df.iteritems(): for item in col: tp = type(item) if tp is pd._libs.tslibs.timestamps.Timestamp: list_timestamp_col.append(col_name) break for col in list_timestamp_col: df[col] = df[col].astype(str) df[col] = df[col].replace('NaT', '') #----- Excelファイル用フォーマットの作成 ----- base_font = '游ゴシック' from openpyxl.styles.fonts import Font from openpyxl.styles import PatternFill font_header_row = Font(name=base_font, b=True, sz=10, color=_header_txtcol) font_header_col = Font(name=base_font, b=True, sz=10, color=_header_txtcol) font_cell = Font(name=base_font, sz=10) align_header_row = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True, textRotation=_header_rot) align_header_col = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True) fill_header_row = PatternFill(patternType='solid', fgColor=_header_fillcol) fill_header_col = PatternFill(patternType='solid', fgColor=_header_fillcol) #----- データ出力 ----- # DataFrameをWorksheetにExport l = df.columns.tolist() if _is_index: l.insert(0, _index_name) # 行のindexを先頭列に追加 ws.append(l) count = 0 for i, row in df.iterrows(): # 一行ずつwsに追加していく l = row.values.tolist() if _is_index: l.insert(0, row.name) # 行のindexを先頭列に追加 ws.append(l) count += 1 print('\r - データコピー {0}/{1}'.format(count, len(df)), end="") print('') #----- Worksheetの書式設定 ----- # ヘッダー行(既定値) for cell in list(ws.rows)[0]: cell.font = font_header_row cell.alignment = align_header_row cell.fill = fill_header_row # ヘッダー行(個別) if _header_fmt != None: list_cell = list(ws.rows)[0] for head, fmt in _header_fmt.items(): try: index = list(df.columns).index(head) if _is_index: index += 1 cell = list_cell[index] except: continue # rotation try: rotation = fmt['rotation'] cell.alignment = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True, textRotation=rotation) except: pass # 文字色 try: text_color = fmt['txtcol'] cell.font = Font(name=base_font, b=True, sz=10, color=text_color) except: pass # 背景色 try: fill_color = fmt['fillcol'] cell.fill = PatternFill(patternType='solid', fgColor=fill_color) except: pass # 列ごとの書式設定用のリスト作成 list_dtxt_pat = list() list_dfill_pat = list() if _header_fmt != None: for head, fmt in _header_fmt.items(): try: index = list(df.columns).index(head) if _is_index: index += 1 except: continue # 文字色 try: text_color = fmt['dtxtcol'] list_dtxt_pat.append( [index, Font(name=base_font, sz=10, color=text_color)]) except: pass # 背景色 try: dfill_color = fmt['dfillcol'] list_dfill_pat.append([ index, PatternFill(patternType='solid', fgColor=dfill_color) ]) except: pass # データ行書式設定 count = 0 for row in ws.iter_rows(min_row=2): # 書式設定 for cell in row: cell.font = font_cell # 列ごとの書式設定で上書き for list_pat in list_dtxt_pat: # 個別設定がある列を順に処理する idx = list_pat[0] row[idx].font = list_pat[1] for list_pat in list_dfill_pat: # 個別設定がある列を順に処理する idx = list_pat[0] row[idx].fill = list_pat[1] # Index列がある場合はIndex用設定 if _is_index: row[0].font = font_header_col # 先頭列のみ太字 row[0].alignment = align_header_col # 先頭列のみセンタリング row[0].fill = fill_header_col # 先頭列の塗りつぶし count += 1 print('\r - 書式設定 {0}/{1}'.format(count, len(df)), end="") print('') #----- セルの文字書式 ----- if type(_letter_fmt) is dict: # _header_fmtがあれば不要だが互換性のために残してある for col in ws.iter_cols(): col_name = col[0].value if col_name in _letter_fmt: num_format = _letter_fmt[col_name] for cell in col: cell.number_format = num_format elif type(_letter_fmt) is str: for col in ws.iter_cols(): for cell in col: cell.number_format = _letter_fmt # 列ごとの個別設定で上書き if _header_fmt != None: list_col = list(_header_fmt.keys()) for col in ws.iter_cols(): col_name = col[0].value if col_name in list_col: # 列書式一覧の辞書にこの列が存在する try: fmt = _header_fmt[col_name] num_format = fmt['dtxtformat'] for cell in col: cell.number_format = num_format except: pass # Worksheetの列幅調整 if _auto_sz: # 自動調整 for col in ws.columns: max_length = 0 column = col[0].column column = get_column_letter(column) # 数字をアルファベットに変換 cols = col if _header_rot != 90 else col[1:] for cell in cols: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) adjusted_width = (max_length + 2) * 1.1 ws.column_dimensions[column].width = adjusted_width else: for col in ws.columns: column = col[0].column # 列番号を取得 col_letter = get_column_letter(column) # 列番号を列記号に変換 width = _col_width_[0] if column == 1 else _col_width_[1] # 列幅 ws.column_dimensions[col_letter].width = width # 列ごとの個別調整 if _header_fmt != None: list_col = list(ws.columns) for head, fmt in _header_fmt.items(): try: width = fmt['width'] index = list(df.columns).index(head) if _is_index: index += 1 col = list_col[index] column = col[0].column # 列番号を取得 col_letter = get_column_letter(column) # 列番号を列記号に変換 ws.column_dimensions[col_letter].width = width except: pass # Worksheetの行の高さ調整 if _header_height != None: ws.row_dimensions[1].height = _header_height # ヒートマップ from openpyxl.formatting.rule import ColorScale, FormatObject from openpyxl.styles import Color if _heatmap == 1: # 赤 → 白 → 青 first = FormatObject(type='min') last = FormatObject(type='max') # colors match the format objects: colors = [Color('F8696B'), Color('5A8AC6')] # a three color scale would extend the sequences mid = FormatObject(type='percentile', val=50) colors.insert(1, Color('FCFCFF')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors) # create a rule with the color scale from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) # 対象範囲を示す文字列を作成 rg = 'A2:' + get_column_letter(ws.max_column) + str(ws.max_row) ws.conditional_formatting.add(rg, rule) elif _heatmap == 2: # 白 → 橙 → 赤 first = FormatObject(type='min') last = FormatObject(type='max') # colors match the format objects: colors = [Color('FFFFFF'), Color('F8696B')] # a three color scale would extend the sequences mid = FormatObject(type='percentile', val=50) colors.insert(1, Color('FFEB84')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors) # create a rule with the color scale from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) # 対象範囲を示す文字列を作成 rg = 'A2:' + get_column_letter(ws.max_column) + str(ws.max_row) ws.conditional_formatting.add(rg, rule) elif _heatmap == 3: # 赤 → 橙 → 白 first = FormatObject(type='min') last = FormatObject(type='max') # colors match the format objects: colors = [Color('F8696B'), Color('FFFFFF')] # a three color scale would extend the sequences mid = FormatObject(type='percentile', val=25) colors.insert(1, Color('FFEB84')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors) # create a rule with the color scale from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) # 対象範囲を示す文字列を作成 rg = 'A2:' + get_column_letter(ws.max_column) + str(ws.max_row) ws.conditional_formatting.add(rg, rule) # 枠固定 if _frz != None: ws.freeze_panes = _frz # オートフィルタ if _auto_flt: ws.auto_filter.ref = 'A1:' + get_column_letter(ws.max_column) + '1' # 表示倍率 ws.sheet_view.zoomScale = _zoom #------------------------------------------- # Worksheetに画像を挿入 #------------------------------------------- if _img != None: from openpyxl.drawing.image import Image for img in _img: fpath = img[0] # 挿入する画像ファイル anchor = img[1] # 挿入位置 px_img = Image(fpath) px_img.anchor = anchor ws.add_image(px_img) #------------------------------------------- # Excelファイルに書き込み #------------------------------------------- # 最後に不要なシートを削除 if 'Sheet' in wb.sheetnames: wb.remove(wb['Sheet']) print(' - ファイル書き込み...', end='') wb.save(_path) # 画像ファイル削除 if _img != None: for img in _img: is_delete = False # ファイルを削除するか否か if len(img) > 2: is_delete = img[2] if is_delete: # ファイル削除 os.remove(fpath) print( '\n ---> Finished. (処理時間:{0:.3f}[sec])'.format(time.perf_counter() - time_start)) return wb
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() # todo ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "values" not in report['reporting_period'].keys() or len(report['reporting_period']['values']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ############################### has_cost_data_flag = True if "values" not in report['reporting_period'].keys() or len( report['reporting_period']['values']) == 0: has_cost_data_flag = False if has_cost_data_flag: ws['B6'].font = title_font ws['B6'] = name + '报告期成本' reporting_period_data = report['reporting_period'] category = report['offline_meter']['energy_category_name'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B7'].fill = table_fill ws['B7'].border = f_border ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '成本' ws['B8'].border = f_border ws['B9'].font = title_font ws['B9'].alignment = c_c_alignment ws['B9'] = '环比' ws['B9'].border = f_border col = 'B' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '7'].fill = table_fill ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = report['offline_meter']['energy_category_name'] + \ " (" + report['offline_meter']['unit_of_measure'] + ")" ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = round(reporting_period_data['total_in_category'], 2) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate'] is not None else "-" ws[col + '9'].border = f_border # TCE TCO2E end_col = col # TCE tce_col = chr(ord(end_col) + 1) ws[tce_col + '7'].fill = table_fill ws[tce_col + '7'].font = name_font ws[tce_col + '7'].alignment = c_c_alignment ws[tce_col + '7'] = "吨标准煤 (TCE)" ws[tce_col + '7'].border = f_border ws[tce_col + '8'].font = name_font ws[tce_col + '8'].alignment = c_c_alignment ws[tce_col + '8'] = round( reporting_period_data['total_in_kgce'] / 1000, 2) ws[tce_col + '8'].border = f_border ws[tce_col + '9'].font = name_font ws[tce_col + '9'].alignment = c_c_alignment ws[tce_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate'] is not None else "-" ws[tce_col + '9'].border = f_border # TCO2E tco2e_col = chr(ord(end_col) + 2) ws[tco2e_col + '7'].fill = table_fill ws[tco2e_col + '7'].font = name_font ws[tco2e_col + '7'].alignment = c_c_alignment ws[tco2e_col + '7'] = "吨二氧化碳排放 (TCO2E)" ws[tco2e_col + '7'].border = f_border ws[tco2e_col + '8'].font = name_font ws[tco2e_col + '8'].alignment = c_c_alignment ws[tco2e_col + '8'] = round( reporting_period_data['total_in_kgco2e'] / 1000, 2) ws[tco2e_col + '8'].border = f_border ws[tco2e_col + '9'].font = name_font ws[tco2e_col + '9'].alignment = c_c_alignment ws[tco2e_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate'] is not None else "-" ws[tco2e_col + '9'].border = f_border else: for i in range(6, 9 + 1): ws.rows_dimensions[i].height = 0.1 ###################################### has_cost_datail_flag = True reporting_period_data = report['reporting_period'] category = report['offline_meter']['energy_category_name'] ca_len = len(category) times = reporting_period_data['timestamps'] if "values" not in reporting_period_data.keys() or len( reporting_period_data['values']) == 0: has_cost_datail_flag = False if has_cost_datail_flag: ws['B11'].font = title_font ws['B11'] = name + '详细数据' ws.row_dimensions[18].height = 60 ws['B18'].fill = table_fill ws['B18'].font = title_font ws['B18'].border = f_border ws['B18'].alignment = c_c_alignment ws['B18'] = '日期时间' time = times has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = 18 + len(time) if has_data: end_data_row_number = 19 for i in range(0, len(time)): col = 'B' end_data_row_number = 19 + i row = str(end_data_row_number) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border ws['B' + str(end_data_row_number + 1)].font = title_font ws['B' + str(end_data_row_number + 1)].alignment = c_c_alignment ws['B' + str(end_data_row_number + 1)] = '总计' ws['B' + str(end_data_row_number + 1)].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '18'].fill = table_fill ws[col + '18'].font = title_font ws[col + '18'].alignment = c_c_alignment ws[col + '18'] = report['offline_meter']['energy_category_name'] + \ " (" + report['offline_meter']['unit_of_measure'] + ")" ws[col + '18'].border = f_border time = times time_len = len(time) for j in range(0, time_len): row = str(19 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['values'][j], 2) ws[col + row].border = f_border ws[col + str(end_data_row_number + 1)].font = title_font ws[col + str(end_data_row_number + 1)].alignment = c_c_alignment ws[col + str(end_data_row_number + 1)] = round( reporting_period_data['total_in_category'], 2) ws[col + str(end_data_row_number + 1)].border = f_border line = LineChart() line.title = '报告期成本 - ' + report['offline_meter']['energy_category_name'] + \ " (" + report['offline_meter']['unit_of_measure'] + ")" line_data = Reference(ws, min_col=3, min_row=18, max_row=max_row) line.series.append(Series(line_data, title_from_data=True)) labels = Reference(ws, min_col=2, min_row=19, max_row=max_row) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.height = 8.25 line.width = 24 ws.add_chart(line, "B12") else: for i in range(11, 43 + 1): ws.row_dimensions[i].height = 0.0 filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 118 for i in range(2, 37 + 1): ws.row_dimensions[i].height = 30 for i in range(38, 69 + 1): ws.row_dimensions[i].height = 30 # Col width ws.column_dimensions['A'].width = 1.5 for i in range(ord('B'), ord('I')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") ws.add_image(img, 'B1') # Title ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################################# # First: 能耗分析 # 6: title # 7: table title # 8~10 table_data # Total: 5 rows # if has not energy data: set low height for rows ################################################# reporting_period_data = report['reporting_period'] has_energy_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) ==0: has_energy_data_flag = False if has_energy_data_flag: ws['B6'].font = title_font ws['B6'] = name + ' 能耗分析' category = reporting_period_data['names'] ca_len = len(category) ws['B7'].fill = table_fill ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '能耗' ws['B8'].border = f_border ws['B9'].font = title_font ws['B9'].alignment = c_c_alignment ws['B9'] = '单位面积能耗' ws['B9'].border = f_border ws['B10'].font = title_font ws['B10'].alignment = c_c_alignment ws['B10'] = '环比' ws['B10'].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) row = '7' cell = col + row ws[col + '7'].fill = table_fill ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = round(reporting_period_data['subtotals'][i], 0) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = round( reporting_period_data['subtotals_per_unit_area'][i], 2) ws[col + '9'].border = f_border ws[col + '10'].font = name_font ws[col + '10'].alignment = c_c_alignment ws[col + '10'] = str( round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" ws[col + '10'].border = f_border # TCE TCO2E end_col = col # TCE tce_col = chr(ord(end_col) + 1) ws[tce_col + '7'].fill = table_fill ws[tce_col + '7'].font = name_font ws[tce_col + '7'].alignment = c_c_alignment ws[tce_col + '7'] = "TCE" ws[tce_col + '7'].border = f_border ws[tce_col + '8'].font = name_font ws[tce_col + '8'].alignment = c_c_alignment ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'], 0) ws[tce_col + '8'].border = f_border ws[tce_col + '9'].font = name_font ws[tce_col + '9'].alignment = c_c_alignment ws[tce_col + '9'] = round( reporting_period_data['total_in_kgce_per_unit_area'], 2) ws[tce_col + '9'].border = f_border ws[tce_col + '10'].font = name_font ws[tce_col + '10'].alignment = c_c_alignment ws[tce_col + '10'] = str( round(reporting_period_data['increment_rate_in_kgce'] * 100, 2)) + "%" ws[tce_col + '10'].border = f_border # TCO2E tco2e_col = chr(ord(end_col) + 2) ws[tco2e_col + '7'].fill = table_fill ws[tco2e_col + '7'].font = name_font ws[tco2e_col + '7'].alignment = c_c_alignment ws[tco2e_col + '7'] = "TCO2E" ws[tco2e_col + '7'].border = f_border ws[tco2e_col + '8'].font = name_font ws[tco2e_col + '8'].alignment = c_c_alignment ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'], 0) ws[tco2e_col + '8'].border = f_border ws[tco2e_col + '9'].font = name_font ws[tco2e_col + '9'].alignment = c_c_alignment ws[tco2e_col + '9'] = round( reporting_period_data['total_in_kgco2e_per_unit_area'], 2) ws[tco2e_col + '9'].border = f_border ws[tco2e_col + '10'].font = name_font ws[tco2e_col + '10'].alignment = c_c_alignment ws[tco2e_col + '10'] = str( round(reporting_period_data['increment_rate_in_kgco2e'] * 100, 2)) + "%" ws[tco2e_col + '10'].border = f_border else: for i in range(6, 10 + 1): ws.row_dimensions[i].height = 0.1 ################################################# # Second: 分时电耗 # 12: title # 13: table title # 14~17 table_data # Total: 6 rows ################################################ has_ele_peak_flag = True if "toppeaks" not in reporting_period_data.keys() or \ reporting_period_data['toppeaks'] is None or \ len(reporting_period_data['toppeaks']) == 0: has_ele_peak_flag = False if has_ele_peak_flag: ws['B12'].font = title_font ws['B12'] = name + ' 分时电耗' ws['B13'].fill = table_fill ws['B13'].font = name_font ws['B13'].alignment = c_c_alignment ws['B13'].border = f_border ws['C13'].fill = table_fill ws['C13'].font = name_font ws['C13'].alignment = c_c_alignment ws['C13'].border = f_border ws['C13'] = '分时电耗' ws['B14'].font = title_font ws['B14'].alignment = c_c_alignment ws['B14'] = '尖' ws['B14'].border = f_border ws['C14'].font = title_font ws['C14'].alignment = c_c_alignment ws['C14'].border = f_border ws['C14'] = round(reporting_period_data['toppeaks'][0], 0) ws['B15'].font = title_font ws['B15'].alignment = c_c_alignment ws['B15'] = '峰' ws['B15'].border = f_border ws['C15'].font = title_font ws['C15'].alignment = c_c_alignment ws['C15'].border = f_border ws['C15'] = round(reporting_period_data['onpeaks'][0], 0) ws['B16'].font = title_font ws['B16'].alignment = c_c_alignment ws['B16'] = '平' ws['B16'].border = f_border ws['C16'].font = title_font ws['C16'].alignment = c_c_alignment ws['C16'].border = f_border ws['C16'] = round(reporting_period_data['midpeaks'][0], 0) ws['B17'].font = title_font ws['B17'].alignment = c_c_alignment ws['B17'] = '谷' ws['B17'].border = f_border ws['C17'].font = title_font ws['C17'].alignment = c_c_alignment ws['C17'].border = f_border ws['C17'] = round(reporting_period_data['offpeaks'][0], 0) pie = PieChart() labels = Reference(ws, min_col=2, min_row=14, max_row=17) pie_data = Reference(ws, min_col=3, min_row=14, max_row=17) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 5.25 # cm 1.05*5 1.05cm = 30 pt pie.width = 9 # pie.title = "Pies sold by category" s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = True # 标签显示 s1.dLbls.showVal = True # 数量显示 s1.dLbls.showPercent = True # 百分比显示 # s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100 ws.add_chart(pie, "D13") else: for i in range(12, 18 + 1): ws.row_dimensions[i].height = 0.1 # end_row 10 # start_row 12 ################################################ # Third: 子空间能耗 # 19: title # 20: table title # 21~24 table_data # Total: 6 rows ################################################ has_child_flag = True # Judge if the space has child space, if not, delete it. if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \ len(report['child_space']["energy_category_names"]) == 0: has_child_flag = False if has_child_flag: child = report['child_space'] child_spaces = child['child_space_names_array'][0] child_subtotals = child['subtotals_array'][0] ws['B19'].font = title_font ws['B19'] = name + ' 子空间能耗' ws['B20'].fill = table_fill ws['B20'].border = f_border ws['C20'].fill = table_fill ws['C20'].font = title_font ws['C20'].alignment = c_c_alignment ws['C20'].border = f_border ws['C20'] = child['energy_category_names'][0] ca_len = len(child['energy_category_names']) space_len = len(child['child_space_names_array'][0]) for i in range(0, space_len): row = str(i + 21) ws['B' + row].font = name_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = child['child_space_names_array'][0][i] ws['B' + row].border = f_border for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + row].font = name_font ws[col + row].alignment = c_c_alignment ws[col + row] = child['subtotals_array'][0][i] ws[col + row].border = f_border # pie # 25~30: pie pie = PieChart() labels = Reference(ws, min_col=2, min_row=21, max_row=23) pie_data = Reference(ws, min_col=3 + j, min_row=21, max_row=23) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 5.25 # cm 1.05*5 1.05cm = 30 pt pie.width = 8 # pie.title = "Pies sold by category" s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = True # 标签显示 s1.dLbls.showVal = True # 数量显示 s1.dLbls.showPercent = True # 百分比显示 # s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100 chart_col = chr(ord('B') + 2 * j) chart_cell = chart_col + '26' ws.add_chart(pie, chart_cell) else: for i in range(19, 36 + 1): ws.row_dimensions[i].height = 0.1 ################################################ # Fourth: 能耗详情 # 37: title # 38: table title # 39~69: table_data ################################################ reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] has_detail_data_flag = True if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: ws['B37'].font = title_font ws['B37'] = name + ' 能耗详情' ws['B38'].fill = table_fill ws['B38'].border = f_border ws['B38'].alignment = c_c_alignment ws['B38'] = '时间' time = times[0] has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = 38 + len(time) print("max_row", max_row) if has_data: for i in range(0, len(time)): col = 'B' row = str(39 + i) # col = chr(ord('B') + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border for i in range(0, ca_len): # 38 title col = chr(ord('C') + i) ws[col + '38'].fill = table_fill ws[col + '38'].font = title_font ws[col + '38'].alignment = c_c_alignment ws[col + '38'] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '38'].border = f_border # 39 data time = times[i] time_len = len(time) for j in range(0, time_len): row = str(39 + j) # col = chr(ord('B') + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( reporting_period_data['values'][i][j], 0) ws[col + row].border = f_border # bar # 39~: bar bar = BarChart() labels = Reference(ws, min_col=2, min_row=39, max_row=max_row + 1) bar_data = Reference(ws, min_col=3 + i, min_row=38, max_row=max_row + 1) # openpyxl bug bar.add_data(bar_data, titles_from_data=True) bar.set_categories(labels) bar.height = 5.25 # cm 1.05*5 1.05cm = 30 pt bar.width = 18 # pie.title = "Pies sold by category" bar.dLbls = DataLabelList() bar.dLbls.showCatName = True # label show bar.dLbls.showVal = True # val show bar.dLbls.showPercent = True # percent show # s1 = CharacterProperties(sz=1800) # font size *100 chart_col = chr(ord('B') + 2 * i) chart_cell = chart_col + str(max_row + 2) ws.add_chart(bar, chart_cell) else: for i in range(37, 69 + 1): ws.row_dimensions[i].height = 0.1 filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def read_drawings(ws, drawings_path, archive, valid_files): """ Given a worksheet and the XML of its drawings file, links drawings to cells """ drawings_codename = os.path.split(drawings_path)[-1] rels_file = PACKAGE_DRAWINGS_RELS + '/' + drawings_codename + '.rels' if rels_file not in valid_files: return None rels_source = archive.read(rels_file) rels_root = fromstring(rels_source) root = fromstring(archive.read(drawings_path)) for node in root: col, row = 0, 0 posX = posY = cX = cY = None name = u'' cell_from = node.find('{%s}from' % SHEET_DRAWING_NS) if cell_from is not None: col = cell_from.find('{%s}col' % SHEET_DRAWING_NS) if col is not None: col = int(col.text) row = cell_from.find('{%s}row' % SHEET_DRAWING_NS) if row is not None: row = int(row.text) cell = ws['%s%s' % (get_column_letter(col + 1), row + 1)] pos = node.find('{%s}pos' % SHEET_DRAWING_NS) if pos is not None: posX, posY = _get_position(pos) ext = node.find('{%s}ext' % SHEET_DRAWING_NS) if ext is not None: cX, cY = _get_dimension(ext) pic = node.find('{%s}pic' % SHEET_DRAWING_NS) if pic is not None: sp_pr = pic.find('{%s}spPr' % SHEET_DRAWING_NS) if sp_pr is not None: xfrm = sp_pr.find('{%s}xfrm' % DRAWING_NS) if xfrm is not None: ext = xfrm.find('{%s}ext' % DRAWING_NS) if ext is not None: cX, cY = _get_dimension(ext) nv_pic_pr = pic.find('{%s}nvPicPr' % SHEET_DRAWING_NS) if nv_pic_pr is not None: nv_pic_pr = nv_pic_pr.find('{%s}cNvPr' % SHEET_DRAWING_NS) if nv_pic_pr is not None: name = nv_pic_pr.attrib.get('name', '') blip_fill = pic.find('{%s}blipFill' % SHEET_DRAWING_NS) if blip_fill is not None: blip = blip_fill.find('{%s}blip' % DRAWING_NS) if blip is not None: rid = blip.attrib.get('{%s}embed' % REL_NS) if rid is not None: image_file = read_image_file(rels_root, rid, valid_files) if image_file: img = Image(BytesIO(archive.read(image_file))) if posY is not None and posX is not None: img.drawing.top = posY img.drawing.left = posX else: img.anchor(cell, anchortype='oneCell') if cX is not None and cY is not None: img.drawing.width = cX img.drawing.height = cY img.drawing.name = name ws.add_image(img)
''' Need to develop flexible means of accomodating different instrument_id ''' ''' e.g. AD Hub DA ATC vs AD Hub RT ATC vs AD Hub ATC - Monthly Strip ''' valuation_date = date(2017, 6, 28) forecast_start = date(2017, 7,1) forecast_end = date(2021, 12, 31) df_hedges = get_hedges('Lightstone', 'HoldCo', forecast_start, forecast_end) df_all_prices = get_all_commodity_prices(valuation_date) excel_template = 'c:/users/agood/projects/kean_proj/templates/hedge detail.xlsx' wb = opx.load_workbook(filename = excel_template) ws = wb.active image = Image('c:/users/agood/projects/kean_proj/graphics/Lightstone_Logo.jpg') ws.add_image(image, 'A1') ws.row_dimensions[1].height = 54 ''' clear main body of worksheet ''' for row in ws.iter_rows(row_offset=4): for cell in row: cell.value = None loop_rows = 1 remaining_column = 8 + (13 - forecast_start.month) + 1 data_format = NamedStyle(name='data', number_format='#,##0_);(#,##0)') df_hedges_relevant = df_hedges[df_hedges['trade_date']<forecast_start] df_hedges_relevant = df_hedges_relevant.reset_index(drop=True) for i, hedge in df_hedges_relevant.iterrows(): ws.cell(row=i+5, column=1, value=hedge.loc['tid_kindle'])
def main(): for f_path in f_pathes: print(f_path) f_name, ext = os.path.splitext( os.path.basename(f_path)) #パスから filename と 拡張子を抽出 save_path = os.path.join(temp_dir, f_name + '_T' + ext) # 保存先のファイルパス save_sort_path = os.path.join(temp_dir, f_name + '_sort' + ext) # 表を画像として保存するパスを設定 img_path = os.path.join(temp_dir, f_name + '.png') # Excel ファイルの保存先のパス save_excel_path = os.path.join(dist_dir, f_name + '_sort' + '.xlsx') df = pd.read_csv(f_path, header=None, names=index_name) # csv ファイルを読み出し df.index = index_name # 列名を追加 df.T # 配列を転置する df.to_csv(save_path) # 転置したデータを一度保存 df = pd.read_csv(save_path, header=None) # 保存したデータを再度読み出し # 1列目の値をもとに行の順番をソート df_row_sort = df.sort_values(0, axis=0, ascending=True, na_position='first') # 1行目の値をもとに列の順番をソート df_col_sort = df_row_sort.sort_values(0, axis=1, ascending=True, na_position='first') df_col_sort.to_csv(save_sort_path, header=None, index=False) # header と index を除いて保存 df = pd.read_csv(save_sort_path, index_col=0) # 保存したデータを再々度読み出し # --------------------- # # 4つの変数を計算 # # --------------------- # tp = _Calc_TP(df) fp = _Calc_FP(df, tp) fn = _Calc_FN(df, tp) tn = _Calc_TN(df, tp=tp, fn=fn, fp=fp) # ------------------------------------- # # クラスごとの評価指標の計算 # # ------------------------------------- # tpr = _Calc_TPR(tp, fn) fnr = _Calc_FNR(tpr) tnr = _Calc_TNR(tn, fp) fpr = _Calc_FPR(tnr) tpa = _Calc_TPA(tp, fp) tna = _Calc_TNR(tn, fp) Evaluation_tuple = { "TP": tp, "TN": tn, "FP": fp, "FN": fn, "真陽性率(TPR)": tpr, "偽陰性率(FNR)": fnr, "真陰性率(TNR)": tnr, "偽陽性率(FPR)": fpr, "真陽性精度(TPA)": tpa, "真陰性精度(TNA)": tna, } res = pd.DataFrame(Evaluation_tuple) # -------------------------------- # # 評価指標の平均値の計算 # # -------------------------------- # acc = _Calc_Acc(df, tp) Evaluation_tuple = { "Acc": [acc], } res2 = pd.DataFrame(Evaluation_tuple) # xslx ファイルに書き出し with pd.ExcelWriter(save_excel_path) as writer: df.to_excel(writer, sheet_name="Confusion_matrix") res.to_excel(writer, sheet_name="Evaluation_index", index=False) res2.to_excel(writer, sheet_name="Average_index", index=False) #混同行列の表を作成 _Drowing_Table(df, save_path=img_path) # 混同行列の表を Excel ファイルに保存 img = Image(img_path) # 画像を読み出し try: wb = openpyxl.load_workbook(save_excel_path) ws = wb.create_sheet(title="Image") ws.add_image(img, 'A1') wb.save(save_excel_path) except Exception as e: print('Error: ', e) finally: wb.close()
def insertImage(self,image,image_size,anchor=None): image=Image(image) image.width,image.height=image_size self.sheet.add_image(image,anchor)
col = 1 row = 2 # for data in data_list: # data_list에서 문장들을 하나씩 가지고 오기 img_list = data.find( 'img') # img태그(속성과 속성값이 없거나 필요없거나 하면 생략, img로도 충분히 가려낼수 있으니)값 img_src = img_list['src'] a_list = data.find('a') title = a_list['title'] title = re.sub('[^0-9a-zA-Zㄱ-힗]', '', title) link = "https://comic.naver.com" + a_list['href'] strong = data.find('strong').text #urlretrieve(img_src, './image/'+title+'.gif') img_file = Image('./image/' + title + '.gif') # pprint(img_file) #cell = sheet1.cell(row=row, column=1) img_file.anchor = 'A' + str(row) #pprint('A' + str(col)) sheet1.add_image(img_file) sheet1.cell(row=row, column=2).value = title sheet1.cell(row=row, column=3).value = strong sheet1.cell(row=row, column=4).value = link # col = col + 1 row = row + 1 wb.save("./webtoon.xlsx") #print(title, strong, link) ''' data2 = data1.findAll('dd')
def run(): connection = None cursor = None try: connection = psycopg2.connect(user='******', password='******', host='127.0.0.1', port='5432', database='ksgmet') cursor = connection.cursor() workbook = Workbook() sheet = None for region in REGIONS: if sheet: sheet = workbook.create_sheet() else: sheet = workbook.active sheet.title = region.name sheet.page_setup.fitToWidth = 1 x, y = 1, 1 sheet.merge_cells(start_row=x, start_column=y, end_row=x, end_column=y + len(PREDICTIONS)) desc_cell = sheet.cell(row=x, column=y, value=region.description) desc_cell.alignment = Alignment(wrap_text=True, vertical='top') row = sheet.row_dimensions[x] row.height = IMG_ROW_HEIGHT sheet.merge_cells(range_string='J1:L1') if region.map_file: img = Image('D:\\workspace\\MGR\\maps\\regions\\' + region.map_file) img.anchor = 'J1' img.width = IMG_WIDTH img.height = IMG_HEIGHT sheet.add_image(img) x += 1 for field in FIELDS: sheet.cell(row=x, column=y, value='Pole ' + field) sheet.merge_cells(start_row=x, start_column=y, end_row=x, end_column=y + 5) field_row = sheet.row_dimensions[x] field_row.font = BOLD_FONT field_row.fill = PatternFill('solid', fgColor='FFFF00') x += 1 y = 1 for date_range in DATE_RANGES: # Header header = '{} - {}'.format(field, date_range.description) header_cell = sheet.cell(row=x, column=y, value=header) sheet.merge_cells(start_row=x, start_column=y, end_row=x, end_column=y + len(PREDICTIONS)) header_cell.font = BOLD_FONT header_cell.alignment = Alignment(horizontal='center') # Bold the column col = sheet.column_dimensions[get_column_letter(y)] col.width = 40 x += 1 cell = sheet.cell(row=x, column=y, value='Odległość prognozy') cell.font = BOLD_FONT x += 1 cell = sheet.cell(row=x, column=y, value='Ilość analizowanych prognoz') cell.font = BOLD_FONT x += 1 cell = sheet.cell(row=x, column=y, value='Średnia różnica') cell.font = BOLD_FONT x += 1 cell = sheet.cell(row=x, column=y, value='Średnia różnica wartości bezwzględnych') cell.font = BOLD_FONT x += 1 cell = sheet.cell(row=x, column=y, value='Odchylenie standardowe') cell.font = BOLD_FONT # Go to data coll x -= 4 y += 1 for prediction in PREDICTIONS: cell = sheet.cell(row=x, column=y, value=prediction.description()) cell.font = BOLD_FONT x += 1 data = execute_query(cursor, region, date_range, field, prediction) for val in data: sheet.cell(row=x, column=y, value=val) x += 1 # Back up to data coll start and go to the next one y += 1 x -= 5 if region == ALL_DATA: img_row = x + 5 img_col = y - len(PREDICTIONS) - 1 sheet.merge_cells(start_row=img_row, end_row=img_row, start_column=img_col, end_column=img_col + 2) row = sheet.row_dimensions[img_row] row.height = IMG_ROW_HEIGHT img = Image('D:\\workspace\\MGR\\maps\\first_analysis\\' + date_range.map_dir_part() + '\\' + field + '_avg_ad.png') img.anchor = sheet.cell(row=img_row, column=img_col).coordinate img.width = IMG_WIDTH img.height = IMG_HEIGHT sheet.add_image(img) img_col = img_col + 3 sheet.merge_cells(start_row=img_row, end_row=img_row, start_column=img_col, end_column=img_col + 5) img = Image('D:\\workspace\\MGR\\maps\\first_analysis\\' + date_range.map_dir_part() + '\\' + field + '_abs_avg_ad.png') img.anchor = sheet.cell(row=img_row, column=img_col).coordinate img.width = IMG_WIDTH img.height = IMG_HEIGHT sheet.add_image(img) img_col = img_col - 3 img_row = img_row + 1 row = sheet.row_dimensions[img_row] row.height = IMG_ROW_HEIGHT sheet.merge_cells(start_row=img_row, end_row=img_row, start_column=img_col, end_column=img_col + 2) img = Image('D:\\workspace\\MGR\\maps\\first_analysis\\' + date_range.map_dir_part() + '\\' + field + '_std_dev_ad.png') img.anchor = sheet.cell(row=img_row, column=img_col).coordinate img.width = IMG_WIDTH img.height = IMG_HEIGHT sheet.add_image(img) # Go back to header x -= 1 # Space between date ranges col = sheet.column_dimensions[get_column_letter(y)] col.fill = PatternFill('solid', fgColor='E0E0E0') y += 1 # New field, 8 = 5 data rows, 2 img row and one space x += 9 y = 1 workbook.save('D:\\workspace\\MGR\\first_analysis\\data.xlsx') finally: if cursor: cursor.close() if connection: connection.close()
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000') ) b_border = Border( bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################## reporting_period_data = report['reporting_period'] has_cost_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_cost_data_flag = False if has_cost_data_flag: ws['B5'].font = title_font ws['B5'] = name + ' 报告期收入' category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B6'].fill = table_fill ws['B6'].border = f_border ws['B7'].font = title_font ws['B7'].alignment = c_c_alignment ws['B7'] = '报告期收入' ws['B7'].border = f_border ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '环比' ws['B8'].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '6'].fill = table_fill ws[col + '6'].font = name_font ws[col + '6'].alignment = c_c_alignment ws[col + '6'] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '6'].border = f_border ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = round(reporting_period_data['subtotals'][i], 2) ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \ if reporting_period_data['increment_rates'][i] is not None else "-" ws[col + '8'].border = f_border col = chr(ord(col) + 1) ws[col + '6'].fill = table_fill ws[col + '6'].font = name_font ws[col + '6'].alignment = c_c_alignment ws[col + '6'] = "总计 (" + reporting_period_data['total_unit'] + ")" ws[col + '6'].border = f_border ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = round(reporting_period_data['total'], 2) ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['total_increment_rate'] is not None else "-" ws[col + '8'].border = f_border else: for i in range(6, 8 + 1): ws.row_dimensions[i].height = 0.1 ################################## current_row_number = 10 has_subtotals_data_flag = True if "subtotals" not in reporting_period_data.keys() or \ reporting_period_data['subtotals'] is None or \ len(reporting_period_data['subtotals']) == 0: has_subtotals_data_flag = False if has_subtotals_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 收入占比' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '收入' ws['D' + str(current_row_number)].fill = table_fill ws['D' + str(current_row_number)].font = name_font ws['D' + str(current_row_number)].alignment = c_c_alignment ws['D' + str(current_row_number)].border = f_border ws['D' + str(current_row_number)] = '收入占比' current_row_number += 1 ca_len = len(reporting_period_data['names']) wssum = Decimal(0.0) for i in range(0, ca_len): wssum = round(reporting_period_data['subtotals'][i], 2) + wssum for i in range(0, ca_len): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = reporting_period_data['names'][i] ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round(reporting_period_data['subtotals'][i], 2) ws['D' + str(current_row_number)].font = title_font ws['D' + str(current_row_number)].alignment = c_c_alignment ws['D' + str(current_row_number)].border = f_border ws['D' + str(current_row_number)] = '{:.2%}'.format(round( reporting_period_data['subtotals'][i], 2) / wssum) if wssum is not None and wssum != Decimal(0.0) \ else " " current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = name + ' 收入占比' labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True table_cell = 'F' + str(table_start_row_number - 1) ws.add_chart(pie, table_cell) if ca_len < 4: current_row_number = current_row_number - ca_len + 4 else: for i in range(13, 22 + 1): ws.row_dimensions[i].height = 0.1 ############################################# current_row_number = 14 reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] has_detail_data_flag = True ca_len = len(report['reporting_period']['names']) table_row = (current_row_number + 1) + ca_len * 6 if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' ws.row_dimensions[table_row].height = 60 ws['B' + str(table_row)].fill = table_fill ws['B' + str(table_row)].font = title_font ws['B' + str(table_row)].border = f_border ws['B' + str(table_row)].alignment = c_c_alignment ws['B' + str(table_row)] = '日期时间' time = times[0] has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = table_row + len(time) if has_data: for i in range(0, len(time)): col = 'B' row = str(table_row + 1 + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][ i] + ")" ws[col + str(table_row)].border = f_border # 39 data time = times[i] time_len = len(time) for j in range(0, time_len): row = str(table_row + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['values'][i][j], 2) ws[col + row].border = f_border line = LineChart() line.title = \ '报告期收入 - ' + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=table_row + 1, max_row=max_row) line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(current_row_number + 1 + 6 * i) ws.add_chart(line, chart_cell) row = str(max_row + 1) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = '小计' ws['B' + row].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) row = str(max_row + 1) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['subtotals'][i], 2) ws[col + row].border = f_border col = chr(ord(col) + 1) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row)] = '总计 (' + report['reporting_period']['total_unit'] + ')' ws[col + str(table_row)].border = f_border total_sum = 0 for j in range(0, len(time)): row = str(table_row + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment every_day_sum = reporting_period_values_every_day_sum(reporting_period_data, j, ca_len) total_sum += every_day_sum ws[col + row] = round(every_day_sum, 2) ws[col + row].border = f_border row = str(table_row + 1 + len(time)) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(total_sum, 2) ws[col + row].border = f_border else: for i in range(37, 69 + 1): ws.row_dimensions[i].height = 0.1 ##################################### has_associated_equipment_flag = True time_len = len(times[0]) current_row_number = time_len + table_row + 3 if "associated_equipment" not in report.keys() or \ "energy_category_names" not in report['associated_equipment'].keys() or \ len(report['associated_equipment']["energy_category_names"]) == 0 \ or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \ or report['associated_equipment']['associated_equipment_names_array'] is None \ or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \ or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0: has_associated_equipment_flag = False if has_associated_equipment_flag: associated_equipment = report['associated_equipment'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 相关设备数据' current_row_number += 1 ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '相关设备' ca_len = len(associated_equipment['energy_category_names']) for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" col_subtotal = chr(ord('C') + ca_len) ws[col_subtotal + str(current_row_number)].fill = table_fill ws[col_subtotal + str(current_row_number)].font = name_font ws[col_subtotal + str(current_row_number)].alignment = c_c_alignment ws[col_subtotal + str(current_row_number)].border = f_border ws[col_subtotal + str(current_row_number)] = '总计 (' + report['reporting_period']['total_unit'] + ')' associated_equipment_len = len(associated_equipment['associated_equipment_names_array'][0]) for i in range(0, associated_equipment_len): current_row_number += 1 row = str(current_row_number) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = associated_equipment['associated_equipment_names_array'][0][i] ws['B' + row].border = f_border subtotal = Decimal(0.0) for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(associated_equipment['subtotals_array'][j][i], 2) ws[col + row].border = f_border subtotal += associated_equipment['subtotals_array'][j][i] ws[col_subtotal + row].font = title_font ws[col_subtotal + row].alignment = c_c_alignment ws[col_subtotal + row] = round(subtotal, 2) ws[col_subtotal + row].border = f_border print(subtotal) filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
elif child.tag == 'password': ecpassword = child.text elif child.tag == 'username': ecusername = child.text wb = openpyxl.load_workbook('Finesse.xlsx') sheet = wb.get_sheet_by_name('Cluster Setup') sheet['B34'] = host sheet['B35'] = backupHost sheet['B36'] = databaseName sheet['B37'] = port sheet['B38'] = ecusername sheet['B39'] = ecpassword sheet['B40'] = uri ws = wb.active img = Image('DDlogo.png') ws.add_image(img, 'A1') wb.save('Finesse.xlsx') # GET layout response = GET(layout, username, password) #xmlcontent='layout.xml' #fileopen=open(xmlcontent,'wb') #fileopen.write(response.content) #fileopen.close() #Variable initialization for Finesse 11.5 & less uri = '' layoutxml = '' #tree= ET.parse('layout.xml')
picture_path3 = "D:\\2022\\基坑监测\\电子签名png格式\\黄地发电子签.png" path = "D:\\2022\\基坑监测\\科创大街\\科创大街数据库-报表\\17监测日报2022-4-18.xlsx" excel = win32com.client.DispatchEx('Excel.Application') # 这个是必备的, #使用win32建新excel也需要他 excel.Visible = True # 是否可视化 wb = excel.Workbooks.Open(path, UpdateLinks=False, ReadOnly=False) ws = wb.Worksheets['封面'] for shp in ws.Shapes: if (shp.Type == constants.msoPicture): shp.Delete() #z只删除图片的shape # ####worksheet.insert_image(row=1, col=5, filename='E:\\临时图片\\' + v, options={'x_scale': 0.1, 'y_scale': 0.1}) image = Image(picture_path1) image1 = Image(picture_path2) image2 = Image(picture_path3) data = pandas.read_excel(path, '封面') book1 = load_workbook(path) sheet1 = book1.get_sheet_by_name('封面') image.width = 140 image.height = 120 image1.width = 140 image.height = 120 image2.width = 100 image2.height = 70 sheet1.add_image(image, 'B8') sheet1.add_image(image1, 'B9') sheet1.add_image(image2, 'B10')
def output_to_excel(): from openpyxl import Workbook from openpyxl.styles import PatternFill, Font, Border, numbers, Side, Alignment from openpyxl.drawing.image import Image # 定义样式 font_col = Font(name='dengxian', size=20, bold=True, color='FFFFFFFF') font_panzheng = Font(name='dengxian', size=19, bold=True, color='FFF08600') font_prob = Font(name='dengxian', size=19, bold=False, color='FF007AC6') fill_col = PatternFill(fill_type='solid', start_color='FF0070C6', end_color='FF0070C6') fill_row = PatternFill(fill_type='solid', start_color='FFE4F3FC', end_color='FFE4F3FC') fill_prob = PatternFill(fill_type='solid', start_color='FFF7F7F7', end_color='FFF7F7F7') format_percent = numbers.BUILTIN_FORMATS[9] border_corner1 = Border(left=Side(border_style='thick', color='FF999999'), right=Side(border_style='thin', color='FFFFFFFF'), top=Side(border_style='thick', color='FF999999'), bottom=Side(border_style='thin', color='FFFFFFFF')) border_corner2 = Border(left=Side(border_style='thin', color='FFFFFFFF'), right=Side(border_style='thick', color='FF999999'), top=Side(border_style='thick', color='FF999999'), bottom=Side(border_style='thin', color='FFFFFFFF')) border_corner3 = Border(left=Side(border_style='thin', color='FFFFFFFF'), right=Side(border_style='thick', color='FF999999'), top=Side(border_style='thin', color='FFFFFFFF'), bottom=Side(border_style='thick', color='FF999999')) border_corner4 = Border(left=Side(border_style='thick', color='FF999999'), right=Side(border_style='thin', color='FFFFFFFF'), top=Side(border_style='thin', color='FFFFFFFF'), bottom=Side(border_style='thick', color='FF999999')) border_up = Border(left=Side(border_style='thin', color='FFFFFFFF'), right=Side(border_style='thin', color='FFFFFFFF'), top=Side(border_style='thick', color='FF999999'), bottom=Side(border_style='thin', color='FFFFFFFF')) border_left = Border(left=Side(border_style='thick', color='FF999999'), right=Side(border_style='thin', color='FFFFFFFF'), top=Side(border_style='thin', color='FFFFFFFF'), bottom=Side(border_style='thin', color='FFFFFFFF')) border_right = Border(left=Side(border_style='thin', color='FFFFFFFF'), right=Side(border_style='thick', color='FF999999'), top=Side(border_style='thin', color='FFFFFFFF'), bottom=Side(border_style='thin', color='FFFFFFFF')) border_down = Border(left=Side(border_style='thin', color='FFFFFFFF'), right=Side(border_style='thin', color='FFFFFFFF'), top=Side(border_style='thin', color='FFFFFFFF'), bottom=Side(border_style='thick', color='FF999999')) border_inside = Border(left=Side(border_style='thin', color='FFFFFFFF'), right=Side(border_style='thin', color='FFFFFFFF'), top=Side(border_style='thin', color='FFFFFFFF'), bottom=Side(border_style='thin', color='FFFFFFFF')) alignment = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) wb = Workbook() ws = wb.active prob_up_5, prob_mid_5, prob_down_5 = cal_prob(0) prob_up_20, prob_mid_20, prob_down_20 = cal_prob(1) ws.append(['一周', '概率', '一月', '概率']) ws.append(['', prob_up_5, '', prob_up_20]) ws.append(['盘整', prob_mid_5, '盘整', prob_mid_20]) ws.append(['', prob_down_5, '', prob_down_20]) up, up2 = Image('up.png'), Image('up.png') down, down2 = Image('down.png'), Image('down.png') ws.add_image(up, 'A2') ws.add_image(up2, 'C2') ws.add_image(down, 'A4') ws.add_image(down2, 'C4') #curve=Image('.\curve.png') #ws.add_image(curve, 'A6') for c in ['A', 'B', 'C', 'D']: ws.column_dimensions[c].width = 20 for r in range(1, 5): ws.row_dimensions[r].height = 35 for row in range(1, 5): for col in range(4): ws[row][col].border = border_inside ws[1][0].border, ws[1][3].border, ws[4][3].border, ws[4][ 0].border = border_corner1, border_corner2, border_corner3, border_corner4 ws[1][1].border, ws[1][2].border = border_up, border_up ws[4][1].border, ws[4][2].border = border_down, border_down ws[2][0].border, ws[3][0].border = border_left, border_left ws[2][3].border, ws[3][3].border = border_right, border_right for row in range(1, 5): for col in [0, 2]: ws[row][col].fill = fill_row ws[row][col].font = font_col ws[row][col].alignment = alignment for col in [1, 3]: ws[row][col].fill = fill_prob ws[row][col].font = font_prob ws[row][col].number_format = format_percent ws[row][col].alignment = alignment for col in range(4): ws[1][col].fill = fill_col ws[1][col].font = font_col ws[3][0].font, ws[3][2].font = font_panzheng, font_panzheng wb.save(INDEX_SYMBOL + "_Probability.xlsx") print("[Processing]: Output to EXCEL file succeed.")
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################## current_row_number = 6 reporting_period_data = report['reporting_period'] has_names_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_names_data_flag = False if has_names_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 报告期消耗' current_row_number += 1 category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " " + reporting_period_data['energy_category_names'][i] + \ " (" + reporting_period_data['units'][i] + ")" col = chr(ord(col) + 1) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '消耗' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) col = chr(ord(col) + 1) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '环比' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = str( round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \ if reporting_period_data['increment_rates'][i] is not None else '-' col = chr(ord(col) + 1) current_row_number += 2 category_dict = group_by_category( reporting_period_data['energy_category_names']) for category_dict_name, category_dict_values in category_dict.items(): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = \ name + ' ' + category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \ ') 分项消耗占比' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '消耗' current_row_number += 1 for i in category_dict_values: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 3) current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = \ name + ' ' + category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \ ') 分项消耗占比' labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True ws.add_chart(pie, 'D' + str(table_start_row_number)) if len(category_dict_values) < 4: current_row_number = current_row_number - len( category_dict_values) + 4 current_row_number += 1 ##################################### has_values_data = True has_timestamps_data = True if 'values' not in reporting_period_data.keys() or \ reporting_period_data['values'] is None or \ len(reporting_period_data['values']) == 0: has_values_data = False if 'timestamps' not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0 or \ len(reporting_period_data['timestamps'][0]) == 0: has_timestamps_data = False if has_values_data and has_timestamps_data: ca_len = len(reporting_period_data['names']) time = reporting_period_data['timestamps'][0] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' current_row_number += 1 chart_start_row_number = current_row_number current_row_number += ca_len * 6 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '日期时间' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" col = chr(ord(col) + 1) current_row_number += 1 for i in range(0, len(time)): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = time[i] col = 'C' for j in range(0, ca_len): ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \ if reporting_period_data['values'][j][i] is not None else 0.00 col = chr(ord(col) + 1) current_row_number += 1 table_end_row_number = current_row_number - 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '小计' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) col = chr(ord(col) + 1) current_row_number += 2 format_time_width_number = 1.0 min_len_number = 1.0 min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0 if period_type == 'hourly': format_time_width_number = 4.0 min_len_number = 2 min_width_number = 12.0 elif period_type == 'daily': format_time_width_number = 2.5 min_len_number = 4 min_width_number = 14.0 elif period_type == 'monthly': format_time_width_number = 2.1 min_len_number = 4 min_width_number = 12.4 elif period_type == 'yearly': format_time_width_number = 1.5 min_len_number = 5 min_width_number = 11.5 for i in range(0, ca_len): line = LineChart() line.title = '报告期消耗 - ' + \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = format_time_width_number * len(time) if len( time) > min_len_number else min_width_number if line.width > 24: line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) ##################################### has_associated_equipment_flag = True if "associated_equipment" not in report.keys() or \ "energy_item_names" not in report['associated_equipment'].keys() or \ len(report['associated_equipment']["energy_item_names"]) == 0 \ or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \ or report['associated_equipment']['associated_equipment_names_array'] is None \ or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \ or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0: has_associated_equipment_flag = False if has_associated_equipment_flag: associated_equipment = report['associated_equipment'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 相关设备数据' current_row_number += 1 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '相关设备' ca_len = len(associated_equipment['energy_item_names']) for i in range(0, ca_len): row = chr(ord('C') + i) ws[row + str(current_row_number)].fill = table_fill ws[row + str(current_row_number)].font = name_font ws[row + str(current_row_number)].alignment = c_c_alignment ws[row + str(current_row_number)].border = f_border ws[row + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" associated_equipment_len = len( associated_equipment['associated_equipment_names_array'][0]) for i in range(0, associated_equipment_len): current_row_number += 1 row = str(current_row_number) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = associated_equipment[ 'associated_equipment_names_array'][0][i] ws['B' + row].border = f_border for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + row].font = name_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( associated_equipment['subtotals_array'][j][i], 2) ws[col + row].border = f_border filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
from openpyxl import Workbook from openpyxl.drawing.image import Image wb = Workbook() ws = wb.active ws['A1'] = 'You should see three logos below' # create an image # img1 = Image('Tulips.jpg') img2 = Image("1.png") # add to worksheet and anchor next to cells ws.add_image(img2, "A3") ws.add_image(img2, "C5") #注意不能将相同图片对象添加到不同单元格中,程序只会在一个位置生成多个图片。 #本例中的多个图片对象,则可以分配给多个不同单元格对象 wb.save(u'f:\\示例.xlsx')
'A': ' (1)', 'B': ' (2)', 'C': ' (3)', 'D': ' (4)', 'E': ' (5)', 'F': ' (6)', 'G': ' (7)', 'H': ' (8)', 'I': ' (9)', 'J': ' (10)' } for sheet_name in sheet_names1: ws = wb[sheet_name] img = Image(imgfile_path + sheet_name + '.JPG') img.width = 1378 #35 cm (100dpi環境下) img.height = 775 #19.69 cm ws.add_image(img, 'A1') for s_name, f_name in sheet_names2.items(): ws = wb[s_name] img = Image(imgfile_path + f_name + '.png') img.width = 1378 #35 cm (100dpi環境下) img.height = 775 #19.69 cm ws.add_image(img, 'A1') wb.save(xlfile_path)
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000') ) b_border = Border( bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################## reporting_period_data = report['reporting_period'] has_cost_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_cost_data_flag = False if has_cost_data_flag: ws['B5'].font = title_font ws['B5'] = name + ' 报告期收入' category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B6'].fill = table_fill ws['B6'].border = f_border ws['B7'].font = title_font ws['B7'].alignment = c_c_alignment ws['B7'] = '报告期收入' ws['B7'].border = f_border ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '环比' ws['B8'].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '6'].fill = table_fill ws[col + '6'].font = name_font ws[col + '6'].alignment = c_c_alignment ws[col + '6'] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '6'].border = f_border ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = round(reporting_period_data['subtotals'][i], 2) ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \ if reporting_period_data['increment_rates'][i] is not None else "-" ws[col + '8'].border = f_border col = chr(ord(col) + 1) ws[col + '6'].fill = table_fill ws[col + '6'].font = name_font ws[col + '6'].alignment = c_c_alignment ws[col + '6'] = "总计 (" + reporting_period_data['total_unit'] + ")" ws[col + '6'].border = f_border ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = round(reporting_period_data['total'], 2) ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['total_increment_rate'] is not None else "-" ws[col + '8'].border = f_border else: for i in range(6, 8 + 1): ws.row_dimensions[i].height = 0.1 ################################## current_row_number = 10 has_subtotals_data_flag = True if "subtotals" not in reporting_period_data.keys() or \ reporting_period_data['subtotals'] is None or \ len(reporting_period_data['subtotals']) == 0: has_subtotals_data_flag = False if has_subtotals_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 收入占比' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '收入' ws['D' + str(current_row_number)].fill = table_fill ws['D' + str(current_row_number)].font = name_font ws['D' + str(current_row_number)].alignment = c_c_alignment ws['D' + str(current_row_number)].border = f_border ws['D' + str(current_row_number)] = '收入占比' current_row_number += 1 ca_len = len(reporting_period_data['names']) income_sum = Decimal(0.0) for i in range(0, ca_len): income_sum = round(reporting_period_data['subtotals'][i], 2) + income_sum for i in range(0, ca_len): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = reporting_period_data['names'][i] ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round(reporting_period_data['subtotals'][i], 2) ws['D' + str(current_row_number)].font = title_font ws['D' + str(current_row_number)].alignment = c_c_alignment ws['D' + str(current_row_number)].border = f_border ws['D' + str(current_row_number)] = '{:.2%}'.format(round( reporting_period_data['subtotals'][i], 2) / income_sum) \ if income_sum is not None and income_sum != Decimal(0.0) \ else '' current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = name + ' 收入占比' labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True table_cell = 'F' + str(table_start_row_number - 1) ws.add_chart(pie, table_cell) if ca_len < 4: current_row_number = current_row_number - ca_len + 4 else: for i in range(13, 22 + 1): ws.row_dimensions[i].height = 0.1 ############################################# current_row_number = 14 reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] has_detail_data_flag = True ca_len = len(report['reporting_period']['names']) real_timestamps_len = timestamps_data_not_equal_0(report['parameters']['timestamps']) table_row = (current_row_number + 1) + ca_len * 6 + real_timestamps_len * 7 if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' ws.row_dimensions[table_row].height = 60 ws['B' + str(table_row)].fill = table_fill ws['B' + str(table_row)].font = title_font ws['B' + str(table_row)].border = f_border ws['B' + str(table_row)].alignment = c_c_alignment ws['B' + str(table_row)] = '日期时间' time = times[0] has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = table_row + len(time) if has_data: for i in range(0, len(time)): col = 'B' row = str(table_row + 1 + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][ i] + ")" ws[col + str(table_row)].border = f_border # 39 data time = times[i] time_len = len(time) for j in range(0, time_len): row = str(table_row + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['values'][i][j], 2) ws[col + row].border = f_border line = LineChart() line.title = \ '报告期收入 - ' + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=table_row + 1, max_row=max_row) line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(current_row_number + 1 + 6 * i) ws.add_chart(line, chart_cell) row = str(max_row + 1) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = '小计' ws['B' + row].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) row = str(max_row + 1) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['subtotals'][i], 2) ws[col + row].border = f_border col = chr(ord(col) + 1) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row)] = '总计 (' + report['reporting_period']['total_unit'] + ')' ws[col + str(table_row)].border = f_border total_sum = 0 for j in range(0, len(time)): row = str(table_row + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment every_day_sum = reporting_period_values_every_day_sum(reporting_period_data, j, ca_len) total_sum += every_day_sum ws[col + row] = round(every_day_sum, 2) ws[col + row].border = f_border row = str(table_row + 1 + len(time)) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(total_sum, 2) ws[col + row].border = f_border else: for i in range(37, 69 + 1): ws.row_dimensions[i].height = 0.1 ########################################## current_sheet_parameters_row_number = current_row_number + ca_len * 6 + 1 has_parameters_names_and_timestamps_and_values_data = True if 'parameters' not in report.keys() or \ report['parameters'] is None or \ 'names' not in report['parameters'].keys() or \ report['parameters']['names'] is None or \ len(report['parameters']['names']) == 0 or \ 'timestamps' not in report['parameters'].keys() or \ report['parameters']['timestamps'] is None or \ len(report['parameters']['timestamps']) == 0 or \ 'values' not in report['parameters'].keys() or \ report['parameters']['values'] is None or \ len(report['parameters']['values']) == 0 or \ timestamps_data_all_equal_0(report['parameters']['timestamps']): has_parameters_names_and_timestamps_and_values_data = False if has_parameters_names_and_timestamps_and_values_data: ############################### # new worksheet ############################### parameters_data = report['parameters'] parameters_names_len = len(parameters_data['names']) parameters_ws = wb.create_sheet('相关参数') parameters_timestamps_data_max_len = \ get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) # Row height parameters_ws.row_dimensions[1].height = 102 for i in range(2, 7 + 1): parameters_ws.row_dimensions[i].height = 42 for i in range(8, parameters_timestamps_data_max_len + 10): parameters_ws.row_dimensions[i].height = 60 # Col width parameters_ws.column_dimensions['A'].width = 1.5 parameters_ws.column_dimensions['B'].width = 25.0 for i in range(3, 12+parameters_names_len*3): parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0 # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") parameters_ws.add_image(img, 'B1') # Title parameters_ws.row_dimensions[3].height = 60 parameters_ws['B3'].font = name_font parameters_ws['B3'].alignment = b_r_alignment parameters_ws['B3'] = 'Name:' parameters_ws['C3'].border = b_border parameters_ws['C3'].alignment = b_c_alignment parameters_ws['C3'].font = name_font parameters_ws['C3'] = name parameters_ws['D3'].font = name_font parameters_ws['D3'].alignment = b_r_alignment parameters_ws['D3'] = 'Period:' parameters_ws['E3'].border = b_border parameters_ws['E3'].alignment = b_c_alignment parameters_ws['E3'].font = name_font parameters_ws['E3'] = period_type parameters_ws['F3'].font = name_font parameters_ws['F3'].alignment = b_r_alignment parameters_ws['F3'] = 'Date:' parameters_ws['G3'].border = b_border parameters_ws['G3'].alignment = b_c_alignment parameters_ws['G3'].font = name_font parameters_ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local parameters_ws.merge_cells("G3:H3") parameters_ws_current_row_number = 6 parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' 相关参数' parameters_ws_current_row_number += 1 parameters_table_start_row_number = parameters_ws_current_row_number parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80 parameters_ws_current_row_number += 1 table_current_col_number = 2 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(parameters_ws_current_row_number-1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number-1)].border = f_border col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(parameters_ws_current_row_number-1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number-1)].border = f_border parameters_ws[col + str(parameters_ws_current_row_number-1)].font = name_font parameters_ws[col + str(parameters_ws_current_row_number-1)].alignment = c_c_alignment parameters_ws[col + str(parameters_ws_current_row_number-1)] = parameters_data['names'][i] table_current_row_number = parameters_ws_current_row_number for j, value in enumerate(list(parameters_data['timestamps'][i])): col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = value col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = round(parameters_data['values'][i][j], 2) table_current_row_number += 1 table_current_col_number = table_current_col_number + 3 ######################################################## # parameters chart and parameters table ######################################################## ws['B' + str(current_sheet_parameters_row_number)].font = title_font ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数' current_sheet_parameters_row_number += 1 chart_start_row_number = current_sheet_parameters_row_number col_index = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue line = LineChart() data_col = 3+col_index*3 labels_col = 2+col_index*3 col_index += 1 line.title = '相关参数 - ' + \ parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number)) line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number)) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = False line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) current_sheet_parameters_row_number = chart_start_row_number current_sheet_parameters_row_number += 1 ########################################## filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active ws.title = "ShopFloorCost" # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Arial', size=15, bold=True) title_font = Font(name='Arial', size=15, bold=True) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") ws.add_image(img, 'A1') # Title ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'] = name ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'] = period_type ws['B4'].alignment = b_r_alignment ws['B4'] = 'Reporting Start Datetime:' ws['C4'].border = b_border ws['C4'].alignment = b_c_alignment ws['C4'] = reporting_start_datetime_local ws['D4'].alignment = b_r_alignment ws['D4'] = 'Reporting End Datetime:' ws['E4'].border = b_border ws['E4'].alignment = b_c_alignment ws['E4'] = reporting_end_datetime_local if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename #################################################################################################################### reporting_period_data = report['reporting_period'] has_energy_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_energy_data_flag = False if has_energy_data_flag: ws['B6'].font = title_font ws['B6'] = name + ' ' + 'Reporting Period Costs' category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B7'].fill = table_fill ws['B7'].border = f_border ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = 'Cost' ws['B8'].border = f_border ws['B9'].font = title_font ws['B9'].alignment = c_c_alignment ws['B9'] = 'Per Unit Area' ws['B9'].border = f_border ws['B10'].font = title_font ws['B10'].alignment = c_c_alignment ws['B10'] = 'Increment Rate' ws['B10'].border = f_border col = 'B' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '7'].fill = table_fill ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = round( reporting_period_data['subtotals_per_unit_area'][i], 2) ws[col + '9'].border = f_border ws[col + '10'].font = name_font ws[col + '10'].alignment = c_c_alignment ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \ if reporting_period_data['increment_rates'][i] is not None else "-" ws[col + '10'].border = f_border end_col = chr(ord(col) + 1) ws[end_col + '7'].fill = table_fill ws[end_col + '7'].font = name_font ws[end_col + '7'].alignment = c_c_alignment ws[end_col + '7'] = "Total (" + reporting_period_data['total_unit'] + ")" ws[end_col + '7'].border = f_border ws[end_col + '8'].font = name_font ws[end_col + '8'].alignment = c_c_alignment ws[end_col + '8'] = round(reporting_period_data['total'], 2) ws[end_col + '8'].border = f_border ws[end_col + '9'].font = name_font ws[end_col + '9'].alignment = c_c_alignment ws[end_col + '9'] = round(reporting_period_data['total_per_unit_area'], 2) ws[end_col + '9'].border = f_border ws[end_col + '10'].font = name_font ws[end_col + '10'].alignment = c_c_alignment ws[end_col + '10'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['total_increment_rate'] is not None else "-" ws[end_col + '10'].border = f_border else: for i in range(6, 10 + 1): ws.row_dimensions[i].height = 0.1 #################################################################################################################### has_ele_peak_flag = True if "toppeaks" not in reporting_period_data.keys() or \ reporting_period_data['toppeaks'] is None or \ len(reporting_period_data['toppeaks']) == 0: has_ele_peak_flag = False if has_ele_peak_flag: ws['B12'].font = title_font ws['B12'] = name + 'Electricity Cost by Time-Of-Use' ws['B13'].fill = table_fill ws['B13'].font = name_font ws['B13'].alignment = c_c_alignment ws['B13'].border = f_border ws['C13'].fill = table_fill ws['C13'].font = name_font ws['C13'].alignment = c_c_alignment ws['C13'].border = f_border ws['C13'] = 'Electricity Cost by Time-Of-Use' ws['D13'].fill = table_fill ws['D13'].font = name_font ws['D13'].alignment = c_c_alignment ws['D13'].border = f_border ws['D13'] = 'Electricity Cost Proportion by Time-Of-Use' wssum = round(reporting_period_data['toppeaks'][0], 2) + round( reporting_period_data['onpeaks'][0], 2) + round( reporting_period_data['midpeaks'][0], 2) + round( reporting_period_data['offpeaks'][0], 2) ws['B14'].font = title_font ws['B14'].alignment = c_c_alignment ws['B14'] = 'TopPeak' ws['B14'].border = f_border ws['C14'].font = title_font ws['C14'].alignment = c_c_alignment ws['C14'].border = f_border ws['C14'] = round(reporting_period_data['toppeaks'][0], 2) ws['D14'].font = title_font ws['D14'].alignment = c_c_alignment ws['D14'].border = f_border ws['D14'] = '{:.2%}'.format(round(reporting_period_data['toppeaks'][0], 2) / wssum) \ if wssum is not None and wssum != 0 else "0.00%" ws['B15'].font = title_font ws['B15'].alignment = c_c_alignment ws['B15'] = 'OnPeak' ws['B15'].border = f_border ws['C15'].font = title_font ws['C15'].alignment = c_c_alignment ws['C15'].border = f_border ws['C15'] = round(reporting_period_data['onpeaks'][0], 2) ws['D15'].font = title_font ws['D15'].alignment = c_c_alignment ws['D15'].border = f_border ws['D15'] = '{:.2%}'.format(round(reporting_period_data['onpeaks'][0], 2) / wssum) \ if wssum is not None and wssum != 0 else "0.00%" ws['B16'].font = title_font ws['B16'].alignment = c_c_alignment ws['B16'] = 'MidPeak' ws['B16'].border = f_border ws['C16'].font = title_font ws['C16'].alignment = c_c_alignment ws['C16'].border = f_border ws['C16'] = round(reporting_period_data['midpeaks'][0], 2) ws['D16'].font = title_font ws['D16'].alignment = c_c_alignment ws['D16'].border = f_border ws['D16'] = '{:.2%}'.format(round(reporting_period_data['midpeaks'][0], 2) / wssum) \ if wssum is not None and wssum != 0 else "0.00%" ws['B17'].font = title_font ws['B17'].alignment = c_c_alignment ws['B17'] = 'OffPeak' ws['B17'].border = f_border ws['C17'].font = title_font ws['C17'].alignment = c_c_alignment ws['C17'].border = f_border ws['C17'] = round(reporting_period_data['offpeaks'][0], 2) ws['D17'].font = title_font ws['D17'].alignment = c_c_alignment ws['D17'].border = f_border ws['D17'] = '{:.2%}'.format(round(reporting_period_data['offpeaks'][0], 2) / wssum) \ if wssum is not None and wssum != 0 else "0.00%" pie = PieChart() pie.title = name + 'Electricity Cost by Time-Of-Use' labels = Reference(ws, min_col=2, min_row=14, max_row=17) pie_data = Reference(ws, min_col=3, min_row=13, max_row=17) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True ws.add_chart(pie, "E13") else: for i in range(12, 18 + 1): ws.row_dimensions[i].height = 0.1 #################################################################################################################### current_row_number = 19 has_subtotals_data_flag = True if "subtotals" not in reporting_period_data.keys() or \ reporting_period_data['subtotals'] is None or \ len(reporting_period_data['subtotals']) == 0: has_subtotals_data_flag = False if has_subtotals_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' ' + 'Costs Proportion' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = 'Cost' ws['D' + str(current_row_number)].fill = table_fill ws['D' + str(current_row_number)].font = name_font ws['D' + str(current_row_number)].alignment = c_c_alignment ws['D' + str(current_row_number)].border = f_border ws['D' + str(current_row_number)] = 'Costs Proportion' current_row_number += 1 ca_len = len(reporting_period_data['names']) wssum = 0 for i in range(0, ca_len): wssum = round(reporting_period_data['subtotals'][i], 2) + wssum for i in range(0, ca_len): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = reporting_period_data['names'][i] ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) ws['D' + str(current_row_number)].font = title_font ws['D' + str(current_row_number)].alignment = c_c_alignment ws['D' + str(current_row_number)].border = f_border ws['D' + str(current_row_number)] = '{:.2%}'.format( round(reporting_period_data['subtotals'][i], 2) / wssum) if wssum is not None and wssum != 0 else "0.00%" current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = name + ' ' + 'Costs Proportion' labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True table_cell = 'E' + str(table_start_row_number) ws.add_chart(pie, table_cell) if ca_len < 4: current_row_number = current_row_number - ca_len + 4 else: for i in range(21, 29 + 1): current_row_number = 30 ws.row_dimensions[i].height = 0.1 #################################################################################################################### current_row_number += 1 has_detail_data_flag = True table_start_draw_flag = current_row_number + 1 if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] ca_len = len(report['reporting_period']['names']) parameters_names_len = len(report['parameters']['names']) parameters_parameters_datas_len = 0 for i in range(0, parameters_names_len): if len(report['parameters']['timestamps'][i]) == 0: continue parameters_parameters_datas_len += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' ' + 'Detailed Data' detail_data_table_start_row_number = current_row_number + ( ca_len + parameters_parameters_datas_len) * 6 + 3 current_row_number = detail_data_table_start_row_number time = times[0] has_data = False if len(time) > 0: has_data = True if has_data: ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = 'Datetime' col = 'B' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" ws[col + str(current_row_number)].border = f_border end_col = chr(ord(col) + 1) ws[end_col + str(current_row_number)].fill = table_fill ws[end_col + str(current_row_number)].font = title_font ws[end_col + str(current_row_number)].alignment = c_c_alignment ws[end_col + str(current_row_number )] = "Total (" + reporting_period_data['total_unit'] + ")" ws[end_col + str(current_row_number)].border = f_border current_row_number += 1 for i in range(0, len(time)): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = time[i] ws['B' + str(current_row_number)].border = f_border col = 'B' periodic_sum = Decimal(0.0) for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment value = round(reporting_period_data['values'][j][i], 2) periodic_sum += value ws[col + str(current_row_number)] = value ws[col + str(current_row_number)].border = f_border end_col = chr(ord(col) + 1) ws[end_col + str(current_row_number)].font = title_font ws[end_col + str(current_row_number)].alignment = c_c_alignment ws[end_col + str(current_row_number)] = round(periodic_sum, 2) ws[end_col + str(current_row_number)].border = f_border current_row_number += 1 table_end_row_number = current_row_number - 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = 'Subtotal' ws['B' + str(current_row_number)].border = f_border col = 'B' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) ws[col + str(current_row_number)].border = f_border # line line = LineChart() line.title = 'Reporting Period Costs - ' + \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=detail_data_table_start_row_number + 1, max_row=table_end_row_number) line_data = Reference( ws, min_col=3 + i, min_row=detail_data_table_start_row_number, max_row=table_end_row_number) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(table_start_draw_flag + 6 * i) ws.add_chart(line, chart_cell) end_col = chr(ord(col) + 1) ws[end_col + str(current_row_number)].font = title_font ws[end_col + str(current_row_number)].alignment = c_c_alignment ws[end_col + str(current_row_number)] = round( reporting_period_data['total'], 2) ws[end_col + str(current_row_number)].border = f_border current_row_number += 1 else: for i in range(30, 69 + 1): current_row_number = 70 ws.row_dimensions[i].height = 0.1 #################################################################################################################### has_parameters_names_and_timestamps_and_values_data = True ca_len = len(report['reporting_period']['names']) current_sheet_parameters_row_number = table_start_draw_flag + ca_len * 6 if 'parameters' not in report.keys() or \ report['parameters'] is None or \ 'names' not in report['parameters'].keys() or \ report['parameters']['names'] is None or \ len(report['parameters']['names']) == 0 or \ 'timestamps' not in report['parameters'].keys() or \ report['parameters']['timestamps'] is None or \ len(report['parameters']['timestamps']) == 0 or \ 'values' not in report['parameters'].keys() or \ report['parameters']['values'] is None or \ len(report['parameters']['values']) == 0 or \ timestamps_data_all_equal_0(report['parameters']['timestamps']): has_parameters_names_and_timestamps_and_values_data = False if has_parameters_names_and_timestamps_and_values_data: ################################################################################################################ # new worksheet ################################################################################################################ parameters_data = report['parameters'] parameters_names_len = len(parameters_data['names']) file_name = (re.sub(r'[^A-Z]', '', ws.title)) + '_' parameters_ws = wb.create_sheet(file_name + 'Parameters') parameters_timestamps_data_max_len = \ get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) # Row height parameters_ws.row_dimensions[1].height = 102 for i in range(2, 7 + 1): parameters_ws.row_dimensions[i].height = 42 for i in range(8, parameters_timestamps_data_max_len + 10): parameters_ws.row_dimensions[i].height = 60 # Col width parameters_ws.column_dimensions['A'].width = 1.5 parameters_ws.column_dimensions['B'].width = 25.0 for i in range(3, 12 + parameters_names_len * 3): parameters_ws.column_dimensions[format_cell.get_column_letter( i)].width = 15.0 # Img img = Image("excelexporters/myems.png") parameters_ws.add_image(img, 'A1') # Title parameters_ws['B3'].alignment = b_r_alignment parameters_ws['B3'] = 'Name:' parameters_ws['C3'].border = b_border parameters_ws['C3'].alignment = b_c_alignment parameters_ws['C3'] = name parameters_ws['D3'].alignment = b_r_alignment parameters_ws['D3'] = 'Period:' parameters_ws['E3'].border = b_border parameters_ws['E3'].alignment = b_c_alignment parameters_ws['E3'] = period_type parameters_ws['B4'].alignment = b_r_alignment parameters_ws['B4'] = 'Reporting Start Datetime:' parameters_ws['C4'].border = b_border parameters_ws['C4'].alignment = b_c_alignment parameters_ws['C4'] = reporting_start_datetime_local parameters_ws['D4'].alignment = b_r_alignment parameters_ws['D4'] = 'Reporting End Datetime:' parameters_ws['E4'].border = b_border parameters_ws['E4'].alignment = b_c_alignment parameters_ws['E4'] = reporting_end_datetime_local parameters_ws_current_row_number = 6 parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font parameters_ws[ 'B' + str(parameters_ws_current_row_number)] = name + ' ' + 'Parameters' parameters_ws_current_row_number += 1 parameters_table_start_row_number = parameters_ws_current_row_number parameters_ws.row_dimensions[ parameters_ws_current_row_number].height = 80 parameters_ws_current_row_number += 1 table_current_col_number = 2 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] table_current_row_number = parameters_ws_current_row_number for j, value in enumerate(list(parameters_data['timestamps'][i])): col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = value col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = round( parameters_data['values'][i][j], 2) table_current_row_number += 1 table_current_col_number = table_current_col_number + 3 ################################################################################################################ # parameters chart and parameters table ################################################################################################################ ws['B' + str(current_sheet_parameters_row_number)].font = title_font ws['B' + str( current_sheet_parameters_row_number)] = name + ' ' + 'Parameters' current_sheet_parameters_row_number += 1 chart_start_row_number = current_sheet_parameters_row_number col_index = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue line = LineChart() data_col = 3 + col_index * 3 labels_col = 2 + col_index * 3 col_index += 1 line.title = 'Parameters - ' + \ parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line_data = Reference( parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = False line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) current_sheet_parameters_row_number = chart_start_row_number current_sheet_parameters_row_number += 1 filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename #################################################################################################################### current_row_number = 6 reporting_period_data = report['reporting_period'] has_names_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_names_data_flag = False if has_names_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 报告期产出' current_row_number += 1 category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" col = chr(ord(col) + 1) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '产出' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) col = chr(ord(col) + 1) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '环比' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = str( round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \ if reporting_period_data['increment_rates'][i] is not None else '-' col = chr(ord(col) + 1) current_row_number += 2 #################################################################################################################### has_values_data = True has_timestamps_data = True if 'values' not in reporting_period_data.keys() or \ reporting_period_data['values'] is None or \ len(reporting_period_data['values']) == 0: has_values_data = False if 'timestamps' not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0 or \ len(reporting_period_data['timestamps'][0]) == 0: has_timestamps_data = False if has_values_data and has_timestamps_data: ca_len = len(reporting_period_data['names']) time = reporting_period_data['timestamps'][0] real_timestamps_len = timestamps_data_not_equal_0( report['parameters']['timestamps']) ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' current_row_number += 1 chart_start_row_number = current_row_number current_row_number += ca_len * 6 + real_timestamps_len * 7 + 1 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '日期时间' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" col = chr(ord(col) + 1) current_row_number += 1 for i in range(0, len(time)): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = time[i] col = 'C' for j in range(0, ca_len): ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \ if reporting_period_data['values'][j][i] is not None else 0.00 col = chr(ord(col) + 1) current_row_number += 1 table_end_row_number = current_row_number - 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '小计' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) col = chr(ord(col) + 1) current_row_number += 2 format_time_width_number = 1.0 min_len_number = 1.0 min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0 if period_type == 'hourly': format_time_width_number = 4.0 min_len_number = 2 min_width_number = 12.0 elif period_type == 'daily': format_time_width_number = 2.5 min_len_number = 4 min_width_number = 14.0 elif period_type == 'monthly': format_time_width_number = 2.1 min_len_number = 4 min_width_number = 12.4 elif period_type == 'yearly': format_time_width_number = 1.5 min_len_number = 5 min_width_number = 11.5 for i in range(0, ca_len): line = LineChart() line.title = '报告期产出 - ' + \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = format_time_width_number * len(time) if len( time) > min_len_number else min_width_number if line.width > 24: line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) ##################################### has_associated_equipment_flag = True current_row_number += 2 if "associated_equipment" not in report.keys() or \ "energy_category_names" not in report['associated_equipment'].keys() or \ len(report['associated_equipment']["energy_category_names"]) == 0 \ or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \ or report['associated_equipment']['associated_equipment_names_array'] is None \ or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \ or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0: has_associated_equipment_flag = False if has_associated_equipment_flag: associated_equipment = report['associated_equipment'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 相关设备数据' current_row_number += 1 ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '相关设备' ca_len = len(associated_equipment['energy_category_names']) for i in range(0, ca_len): row = chr(ord('C') + i) ws[row + str(current_row_number)].fill = table_fill ws[row + str(current_row_number)].font = name_font ws[row + str(current_row_number)].alignment = c_c_alignment ws[row + str(current_row_number)].border = f_border ws[row + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" associated_equipment_len = len( associated_equipment['associated_equipment_names_array'][0]) for i in range(0, associated_equipment_len): current_row_number += 1 row = str(current_row_number) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = associated_equipment[ 'associated_equipment_names_array'][0][i] ws['B' + row].border = f_border for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( associated_equipment['subtotals_array'][j][i], 2) ws[col + row].border = f_border #################################################################################################################### current_sheet_parameters_row_number = chart_start_row_number + 1 has_parameters_names_and_timestamps_and_values_data = True if 'parameters' not in report.keys() or \ report['parameters'] is None or \ 'names' not in report['parameters'].keys() or \ report['parameters']['names'] is None or \ len(report['parameters']['names']) == 0 or \ 'timestamps' not in report['parameters'].keys() or \ report['parameters']['timestamps'] is None or \ len(report['parameters']['timestamps']) == 0 or \ 'values' not in report['parameters'].keys() or \ report['parameters']['values'] is None or \ len(report['parameters']['values']) == 0 or \ timestamps_data_all_equal_0(report['parameters']['timestamps']): has_parameters_names_and_timestamps_and_values_data = False if has_parameters_names_and_timestamps_and_values_data: ############################### # new worksheet ############################### parameters_data = report['parameters'] parameters_names_len = len(parameters_data['names']) parameters_ws = wb.create_sheet('相关参数') parameters_timestamps_data_max_len = \ get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) # Row height parameters_ws.row_dimensions[1].height = 102 for i in range(2, 7 + 1): parameters_ws.row_dimensions[i].height = 42 for i in range(8, parameters_timestamps_data_max_len + 10): parameters_ws.row_dimensions[i].height = 60 # Col width parameters_ws.column_dimensions['A'].width = 1.5 parameters_ws.column_dimensions['B'].width = 25.0 for i in range(3, 12 + parameters_names_len * 3): parameters_ws.column_dimensions[format_cell.get_column_letter( i)].width = 15.0 # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") parameters_ws.add_image(img, 'B1') # Title parameters_ws.row_dimensions[3].height = 60 parameters_ws['B3'].font = name_font parameters_ws['B3'].alignment = b_r_alignment parameters_ws['B3'] = 'Name:' parameters_ws['C3'].border = b_border parameters_ws['C3'].alignment = b_c_alignment parameters_ws['C3'].font = name_font parameters_ws['C3'] = name parameters_ws['D3'].font = name_font parameters_ws['D3'].alignment = b_r_alignment parameters_ws['D3'] = 'Period:' parameters_ws['E3'].border = b_border parameters_ws['E3'].alignment = b_c_alignment parameters_ws['E3'].font = name_font parameters_ws['E3'] = period_type parameters_ws['F3'].font = name_font parameters_ws['F3'].alignment = b_r_alignment parameters_ws['F3'] = 'Date:' parameters_ws['G3'].border = b_border parameters_ws['G3'].alignment = b_c_alignment parameters_ws['G3'].font = name_font parameters_ws[ 'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local parameters_ws.merge_cells("G3:H3") parameters_ws_current_row_number = 6 parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' 相关参数' parameters_ws_current_row_number += 1 parameters_table_start_row_number = parameters_ws_current_row_number parameters_ws.row_dimensions[ parameters_ws_current_row_number].height = 80 parameters_ws_current_row_number += 1 table_current_col_number = 'B' for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue parameters_ws[table_current_col_number + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[table_current_col_number + str(parameters_ws_current_row_number - 1)].border = f_border col = decimal_to_column( column_to_decimal(table_current_col_number) + 1) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] table_current_row_number = parameters_ws_current_row_number for j, value in enumerate(list(parameters_data['timestamps'][i])): col = table_current_col_number parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = value col = decimal_to_column(column_to_decimal(col) + 1) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = round( parameters_data['values'][i][j], 2) table_current_row_number += 1 table_current_col_number = decimal_to_column( column_to_decimal(table_current_col_number) + 3) ######################################################## # parameters chart and parameters table ######################################################## ws['B' + str(current_sheet_parameters_row_number)].font = title_font ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数' current_sheet_parameters_row_number += 1 chart_start_row_number = current_sheet_parameters_row_number col_index = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue line = LineChart() data_col = 3 + col_index * 3 labels_col = 2 + col_index * 3 col_index += 1 line.title = '相关参数 - ' + \ parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line_data = Reference( parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = False line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) current_sheet_parameters_row_number = chart_start_row_number current_sheet_parameters_row_number += 1 ########################################## filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
sheet.cell(row=8, column=k3).value = days[num] sheet.cell(row=8, column=k3).alignment = align sheet.cell(row=8, column=k3).font = Font(u'微软雅黑', size=11) # 设置列宽12 c_char = get_column_letter(k3) sheet.column_dimensions[get_column_letter(k3)].width = 12 num += 1 # 设置行高30 for k4 in range(8, 14): sheet.row_dimensions[k4].height = 30 # 最后添加图片并设置文本属性 # 合并单元格 sheet.merge_cells('I1:P20') # 添加图片 img = Image('PR2.jpg') sheet.add_image(img, 'I1') # 添加年份及月份 sheet.cell(row=3, column=1).value = '2019年' sheet.cell(row=4, column=1).value = str(i) + '月' # 设置年份及月份文本属性 sheet.cell(row=3, column=1).font = Font(u'微软雅黑', size=16, bold=True, color='FF7887') sheet.cell(row=4, column=1).font = Font(u'微软雅黑', size=16, bold=True, color='FF7887')
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 121 for i in range(2, 37 + 1): ws.row_dimensions[i].height = 30 for i in range(38, 90 + 1): ws.row_dimensions[i].height = 30 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 20.0 for i in range(ord('C'), ord('I')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) # data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) # c_r_alignment = Alignment(vertical='bottom', # horizontal='center', # text_rotation=0, # wrap_text=False, # shrink_to_fit=False, # indent=0) # Img img = Image("excelexporters/myems.png") # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################################# # First: 统计分析 # 6: title # 7: table title # 8~ca_len table_data ################################################# reporting_period_data = report['reporting_period'] has_energy_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_energy_data_flag = False filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename if has_energy_data_flag: ws['B6'].font = title_font ws['B6'] = name + ' 统计分析' category = reporting_period_data['names'] # table_title ws['B7'].fill = table_fill ws['B7'].font = title_font ws['B7'].alignment = c_c_alignment ws['B7'] = '报告期' ws['B7'].border = f_border ws['C7'].font = title_font ws['C7'].alignment = c_c_alignment ws['C7'] = '算术平均数' ws['C7'].border = f_border ws['D7'].font = title_font ws['D7'].alignment = c_c_alignment ws['D7'] = '中位数' ws['D7'].border = f_border ws['E7'].font = title_font ws['E7'].alignment = c_c_alignment ws['E7'] = '最小值' ws['E7'].border = f_border ws['F7'].font = title_font ws['F7'].alignment = c_c_alignment ws['F7'] = '最大值' ws['F7'].border = f_border ws['G7'].font = title_font ws['G7'].alignment = c_c_alignment ws['G7'] = '样本标准差' ws['G7'].border = f_border ws['H7'].font = title_font ws['H7'].alignment = c_c_alignment ws['H7'] = '样本方差' ws['H7'].border = f_border # table_data for i, value in enumerate(category): row = i * 2 + 8 ws['B' + str(row)].font = name_font ws['B' + str(row)].alignment = c_c_alignment ws['B' + str(row)] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + " )" ws['B' + str(row)].border = f_border ws['B' + str(row + 1)].font = name_font ws['B' + str(row + 1)].alignment = c_c_alignment ws['B' + str(row + 1)] = "环比" ws['B' + str(row + 1)].border = f_border ws['C' + str(row)].font = name_font ws['C' + str(row)].alignment = c_c_alignment ws['C' + str(row)] = round(reporting_period_data['means'][i], 2) \ if reporting_period_data['means'][i] is not None else '' ws['C' + str(row)].border = f_border ws['C' + str(row)].number_format = '0.00' ws['C' + str(row + 1)].font = name_font ws['C' + str(row + 1)].alignment = c_c_alignment ws['C' + str(row + 1)] = str(round(reporting_period_data['means_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['means_increment_rate'][i] is not None else '0.00%' ws['C' + str(row + 1)].border = f_border ws['D' + str(row)].font = name_font ws['D' + str(row)].alignment = c_c_alignment ws['D' + str(row)] = round(reporting_period_data['medians'][i], 2) \ if reporting_period_data['medians'][i] is not None else '' ws['D' + str(row)].border = f_border ws['D' + str(row)].number_format = '0.00' ws['D' + str(row + 1)].font = name_font ws['D' + str(row + 1)].alignment = c_c_alignment ws['D' + str(row + 1)] = str(round(reporting_period_data['medians_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['medians_increment_rate'][i] is not None else '0.00%' ws['D' + str(row + 1)].border = f_border ws['E' + str(row)].font = name_font ws['E' + str(row)].alignment = c_c_alignment ws['E' + str(row)] = round(reporting_period_data['minimums'][i], 2) \ if reporting_period_data['minimums'][i] is not None else '' ws['E' + str(row)].border = f_border ws['E' + str(row)].number_format = '0.00' ws['E' + str(row + 1)].font = name_font ws['E' + str(row + 1)].alignment = c_c_alignment ws['E' + str(row + 1)] = str(round(reporting_period_data['minimums_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['minimums_increment_rate'][i] is not None else '0.00%' ws['E' + str(row + 1)].border = f_border ws['F' + str(row)].font = name_font ws['F' + str(row)].alignment = c_c_alignment ws['F' + str(row)] = round(reporting_period_data['maximums'][i], 2) \ if reporting_period_data['maximums'][i] is not None else '' ws['F' + str(row)].border = f_border ws['F' + str(row)].number_format = '0.00' ws['F' + str(row + 1)].font = name_font ws['F' + str(row + 1)].alignment = c_c_alignment ws['F' + str(row + 1)] = str(round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['maximums_increment_rate'][i] is not None else '0.00%' ws['F' + str(row + 1)].border = f_border ws['G' + str(row)].font = name_font ws['G' + str(row)].alignment = c_c_alignment ws['G' + str(row)] = round(reporting_period_data['stdevs'][i], 2) \ if reporting_period_data['stdevs'][i] is not None else '' ws['G' + str(row)].border = f_border ws['G' + str(row)].number_format = '0.00' ws['G' + str(row + 1)].font = name_font ws['G' + str(row + 1)].alignment = c_c_alignment ws['G' + str(row + 1)] = str(round(reporting_period_data['stdevs_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['stdevs_increment_rate'][i] is not None else '0.00%' ws['G' + str(row + 1)].border = f_border ws['H' + str(row)].font = name_font ws['H' + str(row)].alignment = c_c_alignment ws['H' + str(row)] = round(reporting_period_data['variances'][i], 2) \ if reporting_period_data['variances'][i] is not None else '' ws['H' + str(row)].border = f_border ws['H' + str(row)].number_format = '0.00' ws['H' + str(row + 1)].font = name_font ws['H' + str(row + 1)].alignment = c_c_alignment ws['H' + str(row + 1)] = str(round(reporting_period_data['variances_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['variances_increment_rate'][i] is not None else '0.00%' ws['H' + str(row + 1)].border = f_border ################################################# # Second: 报告期消耗 # 9 + ca_len * 2: title # 10 + ca_len * 2: table title # row_title + 2 ~ row_title + 2 + ca_len : table_data ################################################# if has_energy_data_flag: names = reporting_period_data['names'] ca_len = len(names) row_title = 9 + ca_len * 2 ws['B' + str(row_title)].font = title_font ws['B' + str(row_title)] = name + ' 单位面积值' ws['D' + str(row_title)].font = title_font ws['D' + str(row_title)] = str(report['store']['area']) + 'M²' category = reporting_period_data['names'] # table_title ws['B' + str(row_title + 1)].fill = table_fill ws['B' + str(row_title + 1)].font = title_font ws['B' + str(row_title + 1)].alignment = c_c_alignment ws['B' + str(row_title + 1)] = '报告期' ws['B' + str(row_title + 1)].border = f_border ws['C' + str(row_title + 1)].font = title_font ws['C' + str(row_title + 1)].alignment = c_c_alignment ws['C' + str(row_title + 1)] = '算术平均数' ws['C' + str(row_title + 1)].border = f_border ws['D' + str(row_title + 1)].font = title_font ws['D' + str(row_title + 1)].alignment = c_c_alignment ws['D' + str(row_title + 1)] = '中位数' ws['D' + str(row_title + 1)].border = f_border ws['E' + str(row_title + 1)].font = title_font ws['E' + str(row_title + 1)].alignment = c_c_alignment ws['E' + str(row_title + 1)] = '最小值' ws['E' + str(row_title + 1)].border = f_border ws['F' + str(row_title + 1)].font = title_font ws['F' + str(row_title + 1)].alignment = c_c_alignment ws['F' + str(row_title + 1)] = '最大值' ws['F' + str(row_title + 1)].border = f_border ws['G' + str(row_title + 1)].font = title_font ws['G' + str(row_title + 1)].alignment = c_c_alignment ws['G' + str(row_title + 1)] = '样本标准差' ws['G' + str(row_title + 1)].border = f_border ws['H' + str(row_title + 1)].font = title_font ws['H' + str(row_title + 1)].alignment = c_c_alignment ws['H' + str(row_title + 1)] = '样本方差' ws['H' + str(row_title + 1)].border = f_border # table_data for i, value in enumerate(category): row_data = row_title + 2 + i ws['B' + str(row_data)].font = name_font ws['B' + str(row_data)].alignment = c_c_alignment ws['B' + str(row_data)] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + "/M²)" ws['B' + str(row_data)].border = f_border ws['C' + str(row_data)].font = name_font ws['C' + str(row_data)].alignment = c_c_alignment if reporting_period_data['means_per_unit_area'][i] \ or reporting_period_data['means_per_unit_area'][i] == 0: ws['C' + str(row_data)] = round( reporting_period_data['means_per_unit_area'][i], 2) ws['C' + str(row_data)].border = f_border ws['C' + str(row_data)].number_format = '0.00' ws['D' + str(row_data)].font = name_font ws['D' + str(row_data)].alignment = c_c_alignment if reporting_period_data['medians_per_unit_area'][i] \ or reporting_period_data['medians_per_unit_area'][i] == 0: ws['D' + str(row_data)] = round( reporting_period_data['medians_per_unit_area'][i], 2) ws['D' + str(row_data)].border = f_border ws['D' + str(row_data)].number_format = '0.00' ws['E' + str(row_data)].font = name_font ws['E' + str(row_data)].alignment = c_c_alignment if reporting_period_data['minimums_per_unit_area'][i] \ or reporting_period_data['minimums_per_unit_area'][i] == 0: ws['E' + str(row_data)] = round( reporting_period_data['minimums_per_unit_area'][i], 2) ws['E' + str(row_data)].border = f_border ws['E' + str(row_data)].number_format = '0.00' ws['F' + str(row_data)].font = name_font ws['F' + str(row_data)].alignment = c_c_alignment if reporting_period_data['maximums_per_unit_area'][i] \ or reporting_period_data['maximums_per_unit_area'][i] == 0: ws['F' + str(row_data)] = round( reporting_period_data['maximums_per_unit_area'][i], 2) ws['F' + str(row_data)].border = f_border ws['F' + str(row_data)].number_format = '0.00' ws['G' + str(row_data)].font = name_font ws['G' + str(row_data)].alignment = c_c_alignment if (reporting_period_data['stdevs_per_unit_area'][i]) \ or reporting_period_data['stdevs_per_unit_area'][i] == 0: ws['G' + str(row_data)] = round( reporting_period_data['stdevs_per_unit_area'][i], 2) ws['G' + str(row_data)].border = f_border ws['G' + str(row_data)].number_format = '0.00' ws['H' + str(row_data)].font = name_font ws['H' + str(row_data)].alignment = c_c_alignment if reporting_period_data['variances_per_unit_area'][i] \ or reporting_period_data['variances_per_unit_area'][i] == 0: ws['H' + str(row_data)] = round( reporting_period_data['variances_per_unit_area'][i], 2) ws['H' + str(row_data)].border = f_border ws['H' + str(row_data)].number_format = '0.00' ######################################################## # Third: 详细数据 # row_sat+row_title~ row_sat+row_title+time_len: line # row_sat+1+row_title: table title # i + row_sat + 2 + 10 * ca_len~: table_data ######################################################## has_timestamps_flag = True if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_timestamps_flag = False if has_timestamps_flag: timestamps = reporting_period_data['timestamps'][0] values = reporting_period_data['values'] names = reporting_period_data['names'] ca_len = len(names) time_len = len(timestamps) # title row_title = 10 * ca_len # row_st == row_statistical analysis table row_sat = 12 + 3 * ca_len ws['B' + str(row_sat + row_title)].font = title_font ws['B' + str(row_sat + row_title)] = name + ' 详细数据' # table_title ws['B' + str(row_sat + 1 + row_title)].fill = table_fill ws['B' + str(row_sat + 1 + row_title)].font = name_font ws['B' + str(row_sat + 1 + row_title)].alignment = c_c_alignment ws['B' + str(row_sat + 1 + row_title)] = "时间" ws['B' + str(row_sat + 1 + row_title)].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(row_sat + 1 + row_title)].font = name_font ws[col + str(row_sat + 1 + row_title)].alignment = c_c_alignment ws[col + str(row_sat + 1 + row_title)] = names[ i] + " - (" + reporting_period_data['units'][i] + ")" ws[col + str(row_sat + 1 + row_title)].border = f_border # table_date for i in range(0, time_len): rows = i + row_sat + 2 + 10 * ca_len ws['B' + str(rows)].font = name_font ws['B' + str(rows)].alignment = c_c_alignment ws['B' + str(rows)] = timestamps[i] ws['B' + str(rows)].border = f_border for index in range(0, ca_len): col = chr(ord('C') + index) ws[col + str(rows)].font = name_font ws[col + str(rows)].alignment = c_c_alignment ws[col + str(rows)] = round(values[index][i], 2) ws[col + str(rows)].number_format = '0.00' ws[col + str(rows)].border = f_border # 小计 row_subtotals = row_sat + 2 + time_len + 10 * ca_len ws['B' + str(row_subtotals)].font = name_font ws['B' + str(row_subtotals)].alignment = c_c_alignment ws['B' + str(row_subtotals)] = "小计" ws['B' + str(row_subtotals)].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(row_subtotals)].font = name_font ws[col + str(row_subtotals)].alignment = c_c_alignment ws[col + str(row_subtotals)] = round( reporting_period_data['subtotals'][i], 2) ws[col + str(row_subtotals)].border = f_border ws[col + str(row_subtotals)].number_format = '0.00' # LineChart for i in range(0, ca_len): lc = LineChart() lc.title = "报告期消耗" + " - " + names[ i] + "(" + reporting_period_data['units'][i] + ")" lc.style = 10 lc.height = 8.40 # cm 1.05*8 1.05cm = 30 pt lc.width = 31 lc.x_axis.majorTickMark = 'in' lc.y_axis.majorTickMark = 'in' times = Reference(ws, min_col=2, min_row=row_sat + 2 + row_title, max_row=row_sat + 2 + row_title + time_len) lc_data = Reference(ws, min_col=3 + i, min_row=row_sat + 1 + row_title, max_row=row_sat + 1 + row_title + time_len) lc.add_data(lc_data, titles_from_data=True) lc.set_categories(times) ser = lc.series[0] ser.marker.symbol = "diamond" ser.marker.size = 5 ws.add_chart(lc, 'B' + str(row_sat + 10 * i)) filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") # img = Image("myems.png") img.width = img.width * 1.06 img.height = img.height * 1.06 ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local[: 10] + "__" + reporting_end_datetime_local[: 10] ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################################# # First: 报告期成本分析 # 6: title # 7: table title # 8~10 table_data # Total: 5 rows # if has not energy data: set low height for rows ################################################# reporting_period_data = report['reporting_period'] has_energy_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_energy_data_flag = False if has_energy_data_flag: ws['B6'].font = title_font ws['B6'] = name + ' 报告期成本分析' category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B7'].fill = table_fill ws['B7'].border = f_border ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '成本' ws['B8'].border = f_border ws['B9'].font = title_font ws['B9'].alignment = c_c_alignment ws['B9'] = '环比' ws['B9'].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) row = '7' cell = col + row ws[col + '7'].fill = table_fill ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \ if reporting_period_data['increment_rates'][i] is not None else "-" ws[col + '9'].border = f_border col = chr(ord(col) + 1) ws[col + '7'].fill = table_fill ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = "总计 (" + reporting_period_data['total_unit'] + ")" ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = round(reporting_period_data['total'], 2) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['total_increment_rate'] is not None else "-" ws[col + '9'].border = f_border else: for i in range(6, 8 + 1): ws.row_dimensions[i].height = 0.1 ################################################# # Second: 分时用电成本 # 12: title # 13: table title # 14~17 table_data # Total: 6 rows ################################################ has_ele_peak_flag = True if "toppeaks" not in reporting_period_data.keys() or \ reporting_period_data['toppeaks'] is None or \ len(reporting_period_data['toppeaks']) == 0: has_ele_peak_flag = False if has_ele_peak_flag: ws['B12'].font = title_font ws['B12'] = name + ' 分时用电成本' ws.row_dimensions[13].height = 60 ws['B13'].fill = table_fill ws['B13'].font = name_font ws['B13'].alignment = c_c_alignment ws['B13'].border = f_border ws['C13'].fill = table_fill ws['C13'].font = name_font ws['C13'].alignment = c_c_alignment ws['C13'].border = f_border ws['C13'] = '分时用电成本' ws['B14'].font = title_font ws['B14'].alignment = c_c_alignment ws['B14'] = '尖' ws['B14'].border = f_border ws['C14'].font = title_font ws['C14'].alignment = c_c_alignment ws['C14'].border = f_border ws['C14'] = round(reporting_period_data['toppeaks'][0], 2) ws['B15'].font = title_font ws['B15'].alignment = c_c_alignment ws['B15'] = '峰' ws['B15'].border = f_border ws['C15'].font = title_font ws['C15'].alignment = c_c_alignment ws['C15'].border = f_border ws['C15'] = round(reporting_period_data['onpeaks'][0], 2) ws['B16'].font = title_font ws['B16'].alignment = c_c_alignment ws['B16'] = '平' ws['B16'].border = f_border ws['C16'].font = title_font ws['C16'].alignment = c_c_alignment ws['C16'].border = f_border ws['C16'] = round(reporting_period_data['midpeaks'][0], 2) ws['B17'].font = title_font ws['B17'].alignment = c_c_alignment ws['B17'] = '谷' ws['B17'].border = f_border ws['C17'].font = title_font ws['C17'].alignment = c_c_alignment ws['C17'].border = f_border ws['C17'] = round(reporting_period_data['offpeaks'][0], 2) pie = PieChart() pie.title = name + ' 分时用电成本' labels = Reference(ws, min_col=2, min_row=14, max_row=17) pie_data = Reference(ws, min_col=3, min_row=13, max_row=17) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 7.25 # cm 1.05*5 1.05cm = 30 pt pie.width = 9 # pie.title = "Pies sold by category" s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False # 标签显示 s1.dLbls.showVal = True # 数量显示 s1.dLbls.showPercent = True # 百分比显示 # s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100 ws.add_chart(pie, "D13") else: for i in range(12, 18 + 1): ws.row_dimensions[i].height = 0.1 # end_row 10 # start_row 12 ################################################ # Second: 成本占比 ################################################ current_row_number = 19 has_subtotals_data_flag = True if 'subtotals' not in reporting_period_data.keys() or \ reporting_period_data['subtotals'] is None: has_subtotals_data_flag = False if has_subtotals_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 成本占比' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '成本占比' current_row_number += 1 category = reporting_period_data['names'] ca_len = len(category) for i in range(0, ca_len): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = reporting_period_data['names'][i] + \ ' (' + reporting_period_data['units'][i] + ')' ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 3) ws['C' + str(current_row_number)].border = f_border current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = name + ' 成本占比' labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True ws.add_chart(pie, 'D' + str(table_start_row_number)) if ca_len < 4: current_row_number = current_row_number - ca_len + 4 current_row_number += 1 ################################################ # Fourth: 成本详情 # current_row_number: title # current_row_number+1 ~ current_row_number+1+ca_len*6-1: line # current_row_number+1+ca_len*6: table title # current_row_number+1+ca_len*6~: table_data ################################################ reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] has_detail_data_flag = True ca_len = len(report['reporting_period']['names']) real_timestamps_len = timestamps_data_not_equal_0( report['parameters']['timestamps']) table_row = current_row_number + 2 + ca_len * 6 + real_timestamps_len * 7 chart_start_row_number = current_row_number + 1 if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' ws.row_dimensions[table_row].height = 60 ws['B' + str(table_row)].fill = table_fill ws['B' + str(table_row)].font = title_font ws['B' + str(table_row)].border = f_border ws['B' + str(table_row)].alignment = c_c_alignment ws['B' + str(table_row)] = '日期时间' time = times[0] has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = table_row + len(time) print("max_row", max_row) if has_data: for i in range(0, len(time)): col = 'B' row = str(table_row + 1 + i) # col = chr(ord('B') + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border for i in range(0, ca_len): # 38 title col = chr(ord('C') + i) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row)] = reporting_period_data['names'][i] + \ " (" + reporting_period_data['units'][i] + ")" ws[col + str(table_row)].border = f_border # 39 data time = times[i] time_len = len(time) for j in range(0, time_len): row = str(table_row + 1 + j) # col = chr(ord('B') + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( reporting_period_data['values'][i][j], 2) ws[col + row].border = f_border current_row_number = table_row + 1 + len(times[0]) ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '小计' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) # line # 39~: line line = LineChart() line.title = '报告期消耗 - ' + ws.cell(column=3 + i, row=table_row).value labels = Reference(ws, min_col=2, min_row=table_row + 1, max_row=max_row) line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row) # openpyxl bug line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 # cm 1.05*5 1.05cm = 30 pt line.width = 24 # pie.title = "Pies sold by category" line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' # line.dLbls.showCatName = True # label show line.dLbls.showVal = True # val show line.dLbls.showPercent = True # percent show # s1 = CharacterProperties(sz=1800) # font size *100 chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number + 6 * i) ws.add_chart(line, chart_cell) current_sheet_parameters_row_number = chart_start_row_number + ca_len * 6 ########################################## has_parameters_names_and_timestamps_and_values_data = True if 'parameters' not in report.keys() or \ report['parameters'] is None or \ 'names' not in report['parameters'].keys() or \ report['parameters']['names'] is None or \ len(report['parameters']['names']) == 0 or \ 'timestamps' not in report['parameters'].keys() or \ report['parameters']['timestamps'] is None or \ len(report['parameters']['timestamps']) == 0 or \ 'values' not in report['parameters'].keys() or \ report['parameters']['values'] is None or \ len(report['parameters']['values']) == 0 or \ timestamps_data_all_equal_0(report['parameters']['timestamps']): has_parameters_names_and_timestamps_and_values_data = False if has_parameters_names_and_timestamps_and_values_data: ############################### # new worksheet ############################### parameters_data = report['parameters'] parameters_names_len = len(parameters_data['names']) parameters_ws = wb.create_sheet('相关参数') parameters_timestamps_data_max_len = \ get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) # Row height parameters_ws.row_dimensions[1].height = 102 for i in range(2, 7 + 1): parameters_ws.row_dimensions[i].height = 42 for i in range(8, parameters_timestamps_data_max_len + 10): parameters_ws.row_dimensions[i].height = 60 # Col width parameters_ws.column_dimensions['A'].width = 1.5 parameters_ws.column_dimensions['B'].width = 25.0 for i in range(3, 12 + parameters_names_len * 3): parameters_ws.column_dimensions[format_cell.get_column_letter( i)].width = 15.0 # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") parameters_ws.add_image(img, 'B1') # Title parameters_ws.row_dimensions[3].height = 60 parameters_ws['B3'].font = name_font parameters_ws['B3'].alignment = b_r_alignment parameters_ws['B3'] = 'Name:' parameters_ws['C3'].border = b_border parameters_ws['C3'].alignment = b_c_alignment parameters_ws['C3'].font = name_font parameters_ws['C3'] = name parameters_ws['D3'].font = name_font parameters_ws['D3'].alignment = b_r_alignment parameters_ws['D3'] = 'Period:' parameters_ws['E3'].border = b_border parameters_ws['E3'].alignment = b_c_alignment parameters_ws['E3'].font = name_font parameters_ws['E3'] = period_type parameters_ws['F3'].font = name_font parameters_ws['F3'].alignment = b_r_alignment parameters_ws['F3'] = 'Date:' parameters_ws['G3'].border = b_border parameters_ws['G3'].alignment = b_c_alignment parameters_ws['G3'].font = name_font parameters_ws[ 'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local parameters_ws.merge_cells("G3:H3") parameters_ws_current_row_number = 6 parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' 相关参数' parameters_ws_current_row_number += 1 parameters_table_start_row_number = parameters_ws_current_row_number parameters_ws.row_dimensions[ parameters_ws_current_row_number].height = 80 parameters_ws_current_row_number += 1 table_current_col_number = 'B' for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue parameters_ws[table_current_col_number + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[table_current_col_number + str(parameters_ws_current_row_number - 1)].border = f_border col = chr(ord(table_current_col_number) + 1) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] table_current_row_number = parameters_ws_current_row_number for j, value in enumerate(list(parameters_data['timestamps'][i])): col = table_current_col_number parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = value col = chr(ord(col) + 1) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = round( parameters_data['values'][i][j], 2) table_current_row_number += 1 table_current_col_number = chr(ord(table_current_col_number) + 3) ######################################################## # parameters chart and parameters table ######################################################## ws['B' + str(current_sheet_parameters_row_number)].font = title_font ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数' current_sheet_parameters_row_number += 1 chart_start_row_number = current_sheet_parameters_row_number col_index = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue line = LineChart() data_col = 3 + col_index * 3 labels_col = 2 + col_index * 3 col_index += 1 line.title = '相关参数 - ' + \ parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line_data = Reference( parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = False line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) current_sheet_parameters_row_number = chart_start_row_number current_sheet_parameters_row_number += 1 ########################################## filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
from openpyxl.worksheet.table import Table, TableStyleInfo from openpyxl.drawing.image import Image wb = load_workbook(".\\Exercise Files\\Pie.xlsx") ws = wb.active # we will create a table object with display name of Table1 tab = Table(displayName='Table1', ref='A1:B5') # now the table knows what its called and where to find the data and it needs to know how to format itself. # from here we will use table style information style = TableStyleInfo(name='TableStyleMedium9', showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) # set the tablestyleinfo property to style we just created. tab.tableStyleInfo = style # add the table to worksheet ws.add_table(tab) img = Image(".\\Exercise Files\\mountain.jpg") img.height = img.height * .25 img.width = img.width * .25 ws.add_image(img, 'C1') # image property allows us to access us height and width wb.save(".\\Exercise Files\\table.xlsx")
#%% sheet['A1'] = 'CODE' sheet['B1'] = 'QUANTITY' sheet['C1'] = 'RATE' sheet['D1'] = 'PICTURE' sheet['E1'] = 'DESCRIPTION' descriptions = 'Generates random lorem ipsum text Lorem Ipsum Generator Lorem Ipsum Generator provides a GTK+ graphical user interface, a command-line interface' #%% i = sheet.max_row for file in glob.glob("*.jpeg"): i = i + 1 splits = file.split(' ') sheet['A{}'.format(i)] = splits[0] sheet['B{}'.format(i)] = splits[1] sheet['C{}'.format(i)] = splits[2].split('.')[0] img = Image(os.getcwd() + '\\' + file) img.height, img.width = 150, 150 sheet.add_image(img, 'D{}'.format(i)) sheet.row_dimensions[i].height = 130 sheet.column_dimensions['B'].width = 20 sheet.column_dimensions['D'].width = 30 sheet['E{}'.format(i)] = descriptions sheet.column_dimensions['E'].width = 50 #%% for row_ in sheet: for cell_ in row_: cell_.alignment = a #for col in sheet.columns: # max_length = 0 # column = col[0].column # Get the column name # for cell in col:
from openpyxl import Workbook from openpyxl.drawing.image import Image wb = Workbook() ws = wb.active img = Image("img.png") ws.add_image(img, "C3") # C3 위치에 img.png 파일 이미지 삽임 wb.save("sample_image.xlsx")
def do_process_workbook(): print_if_debug("creating temp directory") if not args.keep_barcodes_in_home: tempdir = tempfile.mkdtemp() else: temp_dir_in_cwd = os.path.join(program_launch_cwd, 'barcode images') os.mkdir(temp_dir_in_cwd) tempdir = temp_dir_in_cwd print_if_debug("temp directory created as: " + tempdir) progress_bar.configure(mode='indeterminate', maximum=100) progress_bar.start() progress_numbers.configure(text="opening workbook") wb = openpyxl.load_workbook(old_workbook_path) ws = wb.worksheets[0] progress_numbers.configure(text="testing workbook save") wb.save(new_workbook_path) count = 0 save_counter = 0 progress_bar.configure(maximum=ws.max_row, value=count) progress_numbers.configure(text=str(count) + "/" + str(ws.max_row)) border_size = int(border_spinbox.get()) for _ in ws.iter_rows(): # iterate over all rows in current worksheet if not process_workbook_keep_alive: break try: count += 1 progress_bar.configure(maximum=ws.max_row, value=count, mode='determinate') progress_numbers.configure(text=str(count) + "/" + str(ws.max_row)) progress_bar.configure(value=count) # get code from column "B", on current row, add a zero to the end to make seven digits print_if_debug("getting cell contents on line number " + str(count)) upc_barcode_number = ws["B" + str(count)].value + "0" print_if_debug("cell contents are: " + upc_barcode_number) # select barcode type, specify barcode, and select image writer to save as png ean = barcode.get('ean8', upc_barcode_number, writer=ImageWriter()) # select output image size via dpi. internally, pybarcode renders as svg, then renders that as a png file. # dpi is the conversion from svg image size in mm, to what the image writer thinks is inches. ean.default_writer_options['dpi'] = int(dpi_spinbox.get()) # module height is the barcode bar height in mm ean.default_writer_options['module_height'] = float(height_spinbox.get()) # text distance is the distance between the bottom of the barcode, and the top of the text in mm ean.default_writer_options['text_distance'] = 1 # font size is the text size in pt ean.default_writer_options['font_size'] = int(font_size_spinbox.get()) # quiet zone is the distance from the ends of the barcode to the ends of the image in mm ean.default_writer_options['quiet_zone'] = 2 # save barcode image with generated filename print_if_debug("generating barcode image") with tempfile.NamedTemporaryFile(dir=tempdir, suffix='.png', delete=False) as initial_temp_file_path: filename = ean.save(initial_temp_file_path.name[0:-4]) print_if_debug("success, barcode image path is: " + filename) print_if_debug("opening " + str(filename) + " to add border") barcode_image = pil_Image.open(str(filename)) # open image as pil object print_if_debug("success") print_if_debug("adding barcode and saving") img_save = pil_ImageOps.expand(barcode_image, border=border_size, fill='white') # add border around image width, height = img_save.size # get image size of barcode with border # resize cell to size of image ws.column_dimensions['A'].width = int(math.ceil(float(width) * .15)) ws.row_dimensions[count].height = int(math.ceil(float(height) * .75)) # write out image to file with tempfile.NamedTemporaryFile(dir=tempdir, suffix='.png', delete=False) as final_barcode_path: img_save.save(final_barcode_path.name) print_if_debug("success, final barcode path is: " + final_barcode_path.name) # open image with as openpyxl image object print_if_debug("opening " + final_barcode_path.name + " to insert into output spreadsheet") img = OpenPyXlImage(final_barcode_path.name) print_if_debug("success") # attach image to cell print_if_debug("adding image to cell") img.anchor(ws.cell('A' + str(count)), anchortype='oneCell') # add image to cell ws.add_image(img) save_counter += 1 print_if_debug("success") # This save in the loop frees references to the barcode images, # so that python's garbage collector can clear them except Exception as barcode_error: print_if_debug(barcode_error) if save_counter >= file_limit - 50: print_if_debug("saving intermediate workbook to free file handles") progress_bar.configure(mode='indeterminate', maximum=100) progress_bar.start() progress_numbers.configure(text=str(count) + "/" + str(ws.max_row) + " saving") wb.save(new_workbook_path) print_if_debug("success") save_counter = 1 progress_numbers.configure(text=str(count) + "/" + str(ws.max_row)) progress_bar.configure(value=0) print_if_debug("saving workbook to file") progress_bar.configure(mode='indeterminate', maximum=100) progress_bar.start() progress_numbers.configure(text="saving") wb.save(new_workbook_path) print_if_debug("success") if not args.keep_barcode_files: print_if_debug("removing temp folder " + tempdir) shutil.rmtree(tempdir) print_if_debug("success") progress_bar.stop() progress_bar.configure(maximum=100, value=0, mode='determinate') progress_numbers.configure(text="")
def add_image(self, ws_title, img_path, anchor): """Add an image to worksheet, specifying the anchor cell (for the top-left corner of the image).""" ws = self.wb[ws_title] ws.add_image(Image(img_path), anchor) print('\tAdded {img} to {sheet} at cell {cell}'.format( img=os.path.basename(img_path), sheet=ws_title, cell=anchor))
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # for i in range(2, 37 + 1): # ws.row_dimensions[i].height = 30 # # for i in range(38, 69 + 1): # ws.row_dimensions[i].height = 30 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################## reporting_period_data = report['reporting_period'] has_cost_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_cost_data_flag = False if has_cost_data_flag: ws['B6'].font = title_font ws['B6'] = name + ' 报告期成本' category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B7'].fill = table_fill ws['B7'].border = f_border ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '成本' ws['B8'].border = f_border ws['B9'].font = title_font ws['B9'].alignment = c_c_alignment ws['B9'] = '单位面积值' ws['B9'].border = f_border ws['B10'].font = title_font ws['B10'].alignment = c_c_alignment ws['B10'] = '环比' ws['B10'].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '7'].fill = table_fill ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = round( reporting_period_data['subtotals_per_unit_area'][i], 2) ws[col + '9'].border = f_border ws[col + '10'].font = name_font ws[col + '10'].alignment = c_c_alignment ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \ if reporting_period_data['increment_rates'][i] is not None else "-" ws[col + '10'].border = f_border col = chr(ord(col) + 1) ws[col + '7'].fill = table_fill ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = "总计 (" + reporting_period_data['total_unit'] + ")" ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = round(reporting_period_data['total'], 2) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = round(reporting_period_data['total_per_unit_area'], 2) ws[col + '9'].border = f_border ws[col + '10'].font = name_font ws[col + '10'].alignment = c_c_alignment ws[col + '10'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['total_increment_rate'] is not None else "-" ws[col + '10'].border = f_border else: for i in range(6, 10 + 1): ws.row_dimensions[i].height = 0.1 ############################## has_ele_peak_flag = True if "toppeaks" not in reporting_period_data.keys() or \ reporting_period_data['toppeaks'] is None or \ len(reporting_period_data['toppeaks']) == 0: has_ele_peak_flag = False if has_ele_peak_flag: ws['B12'].font = title_font ws['B12'] = name + '分时用电成本' ws.row_dimensions[13].height = 60 ws['B13'].fill = table_fill ws['B13'].font = name_font ws['B13'].alignment = c_c_alignment ws['B13'].border = f_border ws['C13'].fill = table_fill ws['C13'].font = name_font ws['C13'].alignment = c_c_alignment ws['C13'].border = f_border ws['C13'] = '分时用电成本' ws['B14'].font = title_font ws['B14'].alignment = c_c_alignment ws['B14'] = '尖' ws['B14'].border = f_border ws['C14'].font = title_font ws['C14'].alignment = c_c_alignment ws['C14'].border = f_border ws['C14'] = round(reporting_period_data['toppeaks'][0], 2) ws['B15'].font = title_font ws['B15'].alignment = c_c_alignment ws['B15'] = '峰' ws['B15'].border = f_border ws['C15'].font = title_font ws['C15'].alignment = c_c_alignment ws['C15'].border = f_border ws['C15'] = round(reporting_period_data['onpeaks'][0], 2) ws['B16'].font = title_font ws['B16'].alignment = c_c_alignment ws['B16'] = '平' ws['B16'].border = f_border ws['C16'].font = title_font ws['C16'].alignment = c_c_alignment ws['C16'].border = f_border ws['C16'] = round(reporting_period_data['midpeaks'][0], 2) ws['B17'].font = title_font ws['B17'].alignment = c_c_alignment ws['B17'] = '谷' ws['B17'].border = f_border ws['C17'].font = title_font ws['C17'].alignment = c_c_alignment ws['C17'].border = f_border ws['C17'] = round(reporting_period_data['offpeaks'][0], 2) pie = PieChart() pie.title = name + '分时用电成本' labels = Reference(ws, min_col=2, min_row=14, max_row=17) pie_data = Reference(ws, min_col=3, min_row=13, max_row=17) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 7.25 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True ws.add_chart(pie, "D13") else: for i in range(12, 18 + 1): ws.row_dimensions[i].height = 0.1 ################################## current_row_number = 19 has_child_flag = True if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \ len(report['child_space']["energy_category_names"]) == 0: has_child_flag = False if has_child_flag: child = report['child_space'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 子空间数据' current_row_number += 1 ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border ca_len = len(child['energy_category_names']) col = '' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = child['energy_category_names'][ i] + ' (' + child['units'][i] + ')' col = chr(ord(col) + 1) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number )] = '总计 (' + report['reporting_period']['total_unit'] + ')' space_len = len(child['child_space_names_array'][0]) for i in range(0, space_len): current_row_number += 1 row = str(current_row_number) ws['B' + row].font = name_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = child['child_space_names_array'][0][i] ws['B' + row].border = f_border col = '' every_day_sum = 0 for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + row].font = name_font ws[col + row].alignment = c_c_alignment every_day_sum += child['subtotals_array'][j][i] ws[col + row] = round(child['subtotals_array'][j][i], 2) ws[col + row].border = f_border col = chr(ord(col) + 1) ws[col + row].font = name_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(every_day_sum, 2) ws[col + row].border = f_border current_row_number += 1 # Pie for i in range(0, ca_len): pie = PieChart() labels = Reference(ws, min_col=2, min_row=current_row_number - space_len, max_row=current_row_number - 1) pie_data = Reference(ws, min_col=3 + i, min_row=current_row_number - space_len - 1, max_row=current_row_number - 1) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 8 col = chr(ord('C') + i) pie.title = ws[col + '20'].value s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True chart_cell = '' if i % 2 == 0: chart_cell = 'B' + str(current_row_number) else: chart_cell = 'E' + str(current_row_number) current_row_number += 5 ws.add_chart(pie, chart_cell) # chart_col = chr(ord('B') + 2 * i) # chart_cell = chart_col + str(current_row_number) # ws.add_chart(pie, chart_cell) if ca_len % 2 == 1: current_row_number += 5 # else: # for i in range(19, 36 + 1): # current_row_number = 36 # ws.row_dimensions[i].height = 0.1 current_row_number += 1 ############################################# reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] has_detail_data_flag = True ca_len = len(report['reporting_period']['names']) table_row = (current_row_number + 1) + ca_len * 6 if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' ws.row_dimensions[table_row].height = 60 ws['B' + str(table_row)].fill = table_fill ws['B' + str(table_row)].font = title_font ws['B' + str(table_row)].border = f_border ws['B' + str(table_row)].alignment = c_c_alignment ws['B' + str(table_row)] = '日期时间' time = times[0] has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = table_row + len(time) if has_data: for i in range(0, len(time)): col = 'B' row = str(table_row + 1 + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row)] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + str(table_row)].border = f_border # 39 data time = times[i] time_len = len(time) for j in range(0, time_len): row = str(table_row + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( reporting_period_data['values'][i][j], 2) ws[col + row].border = f_border line = LineChart() line.title = \ '报告期成本 - ' + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=table_row + 1, max_row=max_row) line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(current_row_number + 1 + 6 * i) ws.add_chart(line, chart_cell) row = str(max_row + 1) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = '小计' ws['B' + row].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) row = str(max_row + 1) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['subtotals'][i], 2) ws[col + row].border = f_border col = chr(ord(col) + 1) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row )] = '总计 (' + report['reporting_period']['total_unit'] + ')' ws[col + str(table_row)].border = f_border total_sum = 0 for j in range(0, len(time)): row = str(table_row + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment every_day_sum = reporting_period_values_every_day_sum( reporting_period_data, j, ca_len) total_sum += every_day_sum ws[col + row] = round(every_day_sum, 2) ws[col + row].border = f_border row = str(table_row + 1 + len(time)) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(total_sum, 2) ws[col + row].border = f_border else: for i in range(37, 69 + 1): ws.row_dimensions[i].height = 0.1 filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
#参数cols : 指定移动的列数,如:+1向右移动1列,-1向左移动1列 #参数translate=False: 表示现有单元将被覆盖。公式和参考资料将不会更新 help(Worksheet.move_range) ws.move_range('G10:I13', rows=2, cols=2, translate=True) #插入图片 ##### #使用:openpyxl.drawing.image.Image(self,img) 来初始化图片 #使用:Worksheet.add_image(self, img, anchor=None) 来插入图片 #参数img: 为经过Image类初始化后的图片对象 #参数anchar: 为cell单元格定位 from openpyxl.drawing.image import Image help(Image) help(Worksheet.add_image) img = Image(r'openpyxl_cases\002592.png') ws.add_image(img, 'A11') #插入表单Table ##### from openpyxl import Workbook from openpyxl.worksheet.table import ( Table, #插入表单使用 TableStyleInfo #表单样式信息类 ) ws_table = wb.create_sheet(title='Table') #创建一个工作表 data = [['Apples', 10000, 5000, 8000, 6000], ['Pears', 2000, 3000, 4000, 5000], ['Bananas', 6000, 6000, 6500, 6000], ['Oranges', 500, 300, 200, 700]] #####
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "difference_values" not in report['reporting_period'].keys() or \ len(report['reporting_period']['difference_values']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################################# has_difference_values_data_flag = True if 'difference_values' not in report['reporting_period'].keys() or len( report['reporting_period']['difference_values']) == 0: has_difference_values_data_flag = False current_row_number = 6 if has_difference_values_data_flag: reporting_period_data = report['reporting_period'] category = report['meter']['energy_category_name'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 报告期' current_row_number += 1 ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border if not isinstance(category, list): ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = report['meter'][ 'energy_category_name'] + " (" + report['meter'][ 'unit_of_measure'] + ")" current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '总表消耗' ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['master_meter_consumption_in_category'], 2) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '分表消耗' ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['submeters_consumption_in_category'], 2) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '差值' ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['difference_in_category'], 2) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '差值百分比' ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = str( round(reporting_period_data['percentage_difference'] * 100, 2)) + '%' current_row_number += 2 time = reporting_period_data['timestamps'] has_time_data_flag = False if time is not None and len(time) > 0: has_time_data_flag = True if has_time_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' current_row_number += 1 chart_start_number = current_row_number current_row_number = current_row_number + 6 table_start_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = '日期时间' ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)] = report['meter'][ 'energy_category_name'] + " (" + report['meter'][ 'unit_of_measure'] + ")" current_row_number += 1 for i in range(0, len(time)): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = time[i] ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)] = round( reporting_period_data['difference_values'][i], 2) current_row_number += 1 table_end_number = current_row_number - 1 line = LineChart() line.title = '报告期差值 - ' + report['meter'][ 'energy_category_name'] + " (" + report['meter'][ 'unit_of_measure'] + ")" labels = Reference(ws, min_col=2, min_row=table_start_number + 1, max_row=table_end_number) line_data = Reference(ws, min_col=3, min_row=table_start_number, max_row=table_end_number) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True # 数量显示 ws.add_chart(line, "B" + str(chart_start_number)) ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = '总计' ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)] = round( reporting_period_data[ 'master_meter_consumption_in_category'], 2) else: pass filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def writeMeltCurves(sheet,r,c,imageFile): '''Adds a melt curve .jpeg file at the specified cell.''' image = Image(imageFile) image.anchor(sheet.cell(row=r,column=c),anchortype='oneCell') sheet.add_image(image)
from openpyxl import Workbook from openpyxl.drawing.image import Image Work_book = Workbook() work_sheet = Work_book.active img = Image("small-image.jpg") work_sheet.add_image(img, "C11") # Work_book.save("images.xlsx") print(img.width, img.height) large_img = Image("small-image.jpg") large_img.width = 600 large_img.height = 400 # print(img.width, img.height) work_sheet.add_image(large_img, "L11") Work_book.save("images.xlsx")