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'].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 \ "timestamps" not in report['reporting_period'].keys() or len(report['reporting_period']['timestamps']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ############################### has_names_data_flag = True if "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: has_names_data_flag = False current_row_number = 6 if has_names_data_flag: reporting_period_data = report['reporting_period'] category = reporting_period_data['names'] ca_len = len(category) 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 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] + "/H)" 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)] = '平均负荷' for i in range(0, ca_len): col = chr(ord('C') + i) 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['averages'][i], 2) \ if reporting_period_data['averages'][i] is not None else 'N/A' 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)] = '单位面积值' for i in range(0, ca_len): col = chr(ord('C') + i) 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['averages_per_unit_area'][i], 2) \ if reporting_period_data['averages_per_unit_area'][i] is not None else 'N/A' 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)] = '环比' for i in range(0, ca_len): col = chr(ord('C') + i) 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['averages_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['averages_increment_rate'][i] is not None else "-" current_row_number += 2 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 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] + "/H)" 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)] = '最大负荷' for i in range(0, ca_len): col = chr(ord('C') + i) 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['maximums'][i], 2) \ if reporting_period_data['maximums'][i] is not None else 'N/A' 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)] = '单位面积值' for i in range(0, ca_len): col = chr(ord('C') + i) 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['maximums_per_unit_area'][i], 2) \ if reporting_period_data['maximums_per_unit_area'][i] is not None else 'N/A' 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)] = '环比' for i in range(0, ca_len): col = chr(ord('C') + i) 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['maximums_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['maximums_increment_rate'][i] is not None else "-" current_row_number += 2 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 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] 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)] = '负荷系数' for i in range(0, ca_len): col = chr(ord('C') + i) 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['factors'][i], 2) \ if reporting_period_data['factors'][i] is not None else '-' 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)] = '环比' for i in range(0, ca_len): col = chr(ord('C') + i) 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['factors_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['factors_increment_rate'][i] is not None else "-" current_row_number += 2 has_sub_averages_data_flag = True has_sub_maximums_data_flag = True if "sub_averages" not in report['reporting_period'].keys() or len(report['reporting_period']['sub_averages']) == 0: has_sub_averages_data_flag = False if "sub_averages" not in report['reporting_period'].keys() or len(report['reporting_period']['sub_averages']) == 0: has_sub_maximums_data_flag = False if has_sub_averages_data_flag or has_sub_maximums_data_flag: reporting_period_data = report['reporting_period'] category = reporting_period_data['names'] ca_len = len(category) times = reporting_period_data['timestamps'] time = times[0] 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 if has_sub_averages_data_flag: current_row_number = (current_row_number + ca_len * 6) if has_sub_maximums_data_flag: current_row_number = (current_row_number + ca_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)].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): if has_sub_averages_data_flag: 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] + "/H)" col = chr(ord(col) + 1) if has_sub_maximums_data_flag: 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] + "/H)" 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): if has_sub_averages_data_flag: 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['sub_averages'][j][i], 2) \ if reporting_period_data['sub_averages'][j][i] is not None else 0.00 col = chr(ord(col) + 1) if has_sub_maximums_data_flag: 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['sub_maximums'][j][i], 2) \ if reporting_period_data['sub_maximums'][j][i] is not None else 0.00 col = chr(ord(col) + 1) current_row_number += 1 table_end_number = current_row_number - 1 current_chart_col_number = 3 current_chart_row_number = chart_start_number for i in range(0, ca_len): labels = Reference(ws, min_col=2, min_row=table_start_number + 1, max_row=table_end_number) if has_sub_averages_data_flag: line = LineChart() line.title = '报告期 平均负荷 - ' + ws.cell(column=current_chart_col_number, row=table_start_number).value datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number, max_row=table_end_number) line.add_data(datas, 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(current_chart_row_number)) current_chart_row_number += 6 current_chart_col_number += 1 if has_sub_maximums_data_flag: line = LineChart() line.title = '报告期 最大负荷 - ' + ws.cell(column=current_chart_col_number, row=table_start_number).value datas = Reference(ws, min_col=current_chart_col_number, min_row=table_start_number, max_row=table_end_number) line.add_data(datas, 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(current_chart_row_number)) current_chart_row_number += 6 current_chart_col_number += 1 filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def startCheck(self): wb = None filename = self.fileEntry.get() try: wb = openpyxl.load_workbook(filename) messagebox.showinfo('XXX様スタイルガイドツール', os.path.basename(filename) + 'を開きます。') except OSError: messagebox.showinfo('XXX様スタイルガイドツール', '正しいファイルパースを選択してください。') return sheet = None try: sheet = wb['checkSheet'] except KeyError: messagebox.showinfo('XXX様スタイルガイドツール', '英数チェックファイルを使ってください。') return maxRow = sheet.max_row titleName = 'Title.xlsx.sdlxliff' tableName = 'table' freeName = 'Free.xlsx.sdlxliff' imageName = '.xml' header = sheet.cell(row=1, column=15) header.value = 'XXX様' header.fill = PatternFill('solid', fgColor='BFBFBF') thin_border = Border(top=Side(border_style='thin', color='00000000'), right=Side(border_style='thin', color='00000000'), left=Side(border_style='thin', color='00000000'), bottom=Side(border_style='thin', color='00000000')) header.border = thin_border sheet.column_dimensions['O'].width = 30 for i in range(2, maxRow + 1): sheet.cell(row=i, column=15).value = None sheet.cell(row=i, column=15).alignment = Alignment(wrapText=True) sheet.cell(row=i, column=15).border = thin_border def errors(errVal, word, case): if case == 'upper': errTxt = ' は大文字ではない' elif case == 'lower': errTxt = ' は小文字ではない' elif case == 'dot': errTxt = ' のユニコードが違う' elif case == 'singular': errTxt = ' は複数形ではない' elif case == 'mismatch': errTxt = ' の訳文が合っていない' elif case == 'delete': errTxt = ' は翻訳対象以外' if not errVal: errVal = word + errTxt else: errVal = err.value + '\n' + word + errTxt return errVal def isWord(word): nonCapWords = ["with", "per", "for", "the", "but", "from"] if not word.isalpha(): return False elif len(word) < 3: return False elif word in nonCapWords: return False else: return True def checkCount(string, src, tar): if src.count(string) == tar.count(string): return True else: return False def tokenize(string): punc = '!@#$%^&*()_-+={}[]:;"\'|<>,.?/~`' for marker in punc: text = string.replace(marker, " ") split = text.split() return split for i in range(2, maxRow + 1): t = sheet.cell(row=i, column=1) source = sheet.cell(row=i, column=4) target = sheet.cell(row=i, column=6) err = sheet.cell(row=i, column=15) src = str(source.value) tar = str(target.value) if src and tar: #check 二硫化モリブデン if '二硫化モリブデン' in src and self.lang.get() == 1: if src.count('二硫化モリブデン') != tar.count(r'MoS<sub>2</sub>'): err.value = errors(err.value, 'MoS<sub>2</sub>', 'mismatch') #check dot unicode if self.lang.get() == 1: if '·' in str(tar): case = 'dot' err.value = errors(err.value, '·', case) elif self.lang.get() == 2: if '・' in tar: case = 'dot' err.value = errors(err.value, '・', case) #check arrows if re.compile('←\d').search(src): if re.compile('←\d').search(src).group(0) not in tar: err.value = errors( err.value, re.compile('←\d').search(src).group(0), 'mismatch') #check 全角 space if ' ' in src: if self.lang.get() == 1 and ' ' not in tar: pos = src.find(' ') if src[pos] != 0: starNum = re.compile(r"*\d: ") if not starNum.search(src): err.value = errors(err.value, '全角スペース', 'mismatch') if self.lang.get() == 2 and ' ' not in tar: err.value = errors(err.value, '全角スペース', 'mismatch') #check brackets if '[[' in src: if src.count('[[') != tar.count('[['): err.value = errors(err.value, '[[', 'mismatch') if ']]' in src: if not checkCount(']]', src, tar): err.value = errors(err.value, ']]', 'mismatch') #check tab character if r'\t' in src: if not checkCount(r'\t', src, tar): err.value = errors(err.value, r'\t', 'mismatch') #check hyphens if '----' in src: if not checkCount('----', src, tar): err.value = errors(err.value, '----', 'mismatch') #check slashes if '//' in src: if not checkCount('//', src, tar): err.value = errors(err.value, '//', 'mismatch') #check bullets if '●' in src: if not checkCount('●', src, tar): err.value = errors(err.value, '●', 'mismatch') #check maru suuji maruSuuji = '①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳' for num in maruSuuji: if num in src: if not checkCount(num, src, tar): err.value = errors(err.value, num, 'mismatch') #check *1:, etc. if re.compile('*\d:').search(src): if re.compile('*\d:').search(src).group(0) not in tar: err.value = errors( err.value, re.compile('*\d:').match(src).group(0), 'mismatch') #check zero backslash if 'バックラッシュ0' in src or 'バックラッシュゼロ' in src or r'バックラッシュ<rubi>0@ゼロ</rubi>' in src: if self.lang.get() == 1: if 'zero backslash' not in tar: err.value = errors(err.value, 'zero backslash', 'mismatch') elif self.lang.get() == 2: if '零背隙' not in tar: err.value = errors(err.value, '零背隙', 'mismatch') #check dimensional table if '寸法・価格表' in src or '価格表' in src: if self.lang.get() == 1: if 'dimensional table' not in tar: err.value = errors(err.value, 'dimensional table', 'mismatch') elif self.lang.get() == 2: if '尺寸表' not in tar: err.value = errors(err.value, '尺寸表', 'mismatch') #check 価格 elif '価格' in src: if '【データ上で消去】' not in tar: err.value = '価格は翻訳対象以外' else: err.value = '' #check 単価: if '単価' in src: if '【データ上で消去】' not in tar: err.value = '単価は翻訳対象以外' else: err.value = '' #check 円 if '円' in src: if '【データ上で消去】' not in tar: err.value = '円は翻訳対象以外' else: err.value = '' #check 納期 if '納期' in src: if '【データ上で消去】' not in tar: err.value = '納期は翻訳対象以外' #check 出荷 if '出荷' in src and '【データ上で消去】' not in tar: err.value = errors(err.value, '出荷', 'delete')
import pandas as pd from pandas import DataFrame from openpyxl.drawing.image import Image #activate a spreadsheet gravcalc = Workbook() sheet_one = gravcalc.active sheet_one.title = 'Workings' #create an extra sheet for the formulae sheet_two = gravcalc.create_sheet('Formulae', 1) #format the spreadsheet bold_font = Font(bold=True, color=colors.BLUE, size=10) font_alignment = Alignment(horizontal='center', vertical='center') font_border = Border(bottom=Side(border_style='thin')) sheet_one.freeze_panes = "B1" #add titles to the spreadsheet sheet_one["A1"] = "GRAVITY STATION" sheet_one["A1"].font = bold_font sheet_one["A3"] = "FIRST BASE STATION" sheet_one["A3"].font = bold_font sheet_one["A4"] = "LAST BASE STATION" sheet_one["A4"].font = bold_font sheet_one["B1"] = "TIME(hr)" sheet_one["B1"].font = bold_font sheet_one["B2"] = "(hr)" sheet_one["B2"].font = bold_font sheet_one["C1"] = "TIME(min)"
colum = [ 'Объем ДД', 'Значение приоритета корректировки', 'Доля ГЭС', 'Доля ТЭС', 'Номер пакета ДД' ] dd_1.drop(colum, axis=1, inplace=True) dd_1.drop(dd_1[dd_1['Код ГТПП Покупателя'] != 'PINTCHIN PINTCHN1'].index, axis=0, inplace=True) vc_pc['TARGET_DATE'] = vc_pc['TARGET_DATE'].astype(str) sec_f['MONTH'] = sec_f['MONTH'].astype(str) # Экспортируем Excel dd_1.to_excel(path_2, sheet_name='ДД ИНТЕРРАО в НЦЗ', index=False) vc_pc.to_excel(path_3, sheet_name='VC_PC', index=False) sec_f.to_excel(path_4, sheet_name='Факт ИНТЕРРАО в НЦЗ', index=False) # Создаем шаблоны стилей border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000'), top=Side(border_style='thin', color='FF000000'), bottom=Side(border_style='thin', color='FF000000')) align_head = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=True, shrink_to_fit=True, indent=0) align_cell = Alignment(horizontal='right', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0)
from openpyxl.styles import PatternFill from openpyxl.styles import Font from openpyxl.styles import Border, Side from openpyxl.drawing.image import Image import subprocess wb = load_workbook("simpleWeatherSimply.xlsx") ws = wb.active # calculate total number of rows and # columns in source excel file mr = ws.max_row mc = ws.max_column thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) blueFill = PatternFill(start_color='FF0000FF', end_color='FF0000FF', fill_type='solid') cyanFill = PatternFill(start_color='FF40E0D0', end_color='FF40E0D0', fill_type='solid') greenFill = PatternFill(start_color='FF00FF00', end_color='FF00FF00', fill_type='solid') yellowFill = PatternFill(start_color='FFFFFF00', end_color='FFFFFF00',
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active ws.title = "CombinedEquipmentEfficiency" # 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('R')): 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_efficiency" not in report.keys() or \ "names" not in report['reporting_period_efficiency'].keys() or len( report['reporting_period_efficiency']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename #################################################################################################################### current_row_number = 6 reporting_period_data = report['reporting_period_efficiency'] 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 + ' ' + 'Reporting Period Cumulative Efficiency' current_row_number += 1 category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[current_row_number].height = 80 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) 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['numerator_names'][i] + " (" + \ reporting_period_data['numerator_units'][i] + ")" col = chr(ord(col) + 1) 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['denominator_names'][i] + " (" + \ reporting_period_data['denominator_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)] = 'Cumulative Efficiency' 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['cumulations'][i], 2) col = chr(ord(col) + 1) 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['numerator_cumulations'][i], 2) col = chr(ord(col) + 1) 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['denominator_cumulations'][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)] = 'Increment Rate' 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) 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_num'][i] * 100, 2)) + '%' \ if reporting_period_data['increment_rates_num'][i] is not None else '-' col = chr(ord(col) + 1) 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_den'][i] * 100, 2)) + '%' \ if reporting_period_data['increment_rates_den'][i] is not None else '-' col = chr(ord(col) + 1) current_row_number += 2 #################################################################################################################### has_parameters_names_and_timestamps_and_values_data = True current_sheet_parameters_row_number = current_row_number 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 ##################################### 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']) de_len = len(reporting_period_data['denominator_names']) nu_len = len(reporting_period_data['numerator_names']) time = reporting_period_data['timestamps'][0] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number )] = name + ' ' + 'Reporting Period Cumulative Efficiency' current_row_number += 1 chart_start_row_number = current_row_number current_row_number += ca_len * 6 + de_len * 6 + nu_len * 6 + 1 if has_parameters_names_and_timestamps_and_values_data: current_sheet_parameters_row_number = current_row_number real_timestamps_len = timestamps_data_not_equal_0( report['parameters']['timestamps']) current_row_number += 6 * real_timestamps_len + 2 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' ' + 'Detailed Data' current_row_number += 1 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 85 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)] = 'Datetime' 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) 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['numerator_names'][i] + " (" + \ reporting_period_data['numerator_units'][i] + ")" col = chr(ord(col) + 1) 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['denominator_names'][i] + " (" + \ reporting_period_data['denominator_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) 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['numerator_values'][j][i], 2) \ if reporting_period_data['numerator_values'][j][i] is not None else 0.00 col = chr(ord(col) + 1) 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['denominator_values'][j][i], 2) \ if reporting_period_data['denominator_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)] = 'Subtotal' 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['cumulations'][i], 2) col = chr(ord(col) + 1) 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['numerator_cumulations'][i], 2) col = chr(ord(col) + 1) 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['denominator_cumulations'][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 i_num = 0 for i in range(0, ca_len): line = LineChart() line.title = 'Reporting Period Cumulative Efficiency - ' + \ 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_num, 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) i_num += 1 line = LineChart() line.title = 'Reporting Period Cumulative Efficiency - ' + \ reporting_period_data['names'][i] + '-' + reporting_period_data['numerator_names'][i] + " (" + \ reporting_period_data['numerator_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_num, 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) i_num += 1 line = LineChart() line.title = 'Reporting Period Cumulative Efficiency - ' + \ reporting_period_data['names'][i] + '-' +reporting_period_data['denominator_names'][i] + " (" + \ reporting_period_data['denominator_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_num, 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) i_num += 1 ################################################################################################################ 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 + ' ' + 'Associated Equipment Data' 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)] = 'Associated Equipment' 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 #################################################################################################################### 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
class A205XLSXNode: white_space_multiplier = 4 cell_border = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'), top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000')) unused_style = NamedStyle(name="Unused", fill=PatternFill(start_color='808B96', end_color='808B96', fill_type='solid')) tile_style = NamedStyle(name="Title", fill=PatternFill(start_color='00529B', end_color='00529B', fill_type='solid'), font=Font(color="FFFFFF", bold=True, sz=14)) heading_style = NamedStyle(name="Heading", fill=PatternFill(start_color='01AED8', end_color='01AED8', fill_type='solid'), font=Font(bold=True), border=cell_border) schema_style = NamedStyle(name="Schema", fill=PatternFill(start_color='D9D9D9', end_color='D9D9D9', fill_type='solid'), font=Font(bold=True), border=cell_border) grid_var_style = NamedStyle(name="Grid Variables", fill=PatternFill(start_color='D9D9D9', end_color='D9D9D9', fill_type='solid'), font=Font(bold=True, color='0070C0'), border=cell_border) value_style = NamedStyle(name="Value", fill=PatternFill(start_color='FFFFFF', end_color='FFFFFF', fill_type='solid'), border=cell_border) def __init__(self, name, parent=None, tree=None, value=None, sheet_ref=None, option=None): self.children = [] # List of children A205XLSXNodes self.name = name # Name of this node self.value = value # Value (if any) of this node self.sheet_ref = sheet_ref # Reference to another sheet (if applicable) self.parent = parent # Parent A205XLSXNode of this node self.grid_set = None # Ordered arrays of repeated grid variable values (used only for grid_variable nodes) if parent: # Inherit much information from parent self.lineage = self.parent.lineage + [ name ] # List of parent node names (as strings) self.options = self.parent.options + [option] self.tree = self.parent.tree self.sheet = self.parent.child_sheet self.sheet_type = self.parent.child_sheet_type self.beg = self.parent.next_child_beg if self.sheet_type == SheetType.FLAT: self.next_child_beg = self.beg + 1 else: self.next_child_beg = self.beg else: # Root node self.lineage = [] self.options = [] self.tree = tree self.sheet = self.tree.schema_type self.sheet_type = SheetType.FLAT self.child_sheet_type = self.sheet_type self.beg = 3 self.next_child_beg = 3 if self.sheet not in self.tree.sheets: self.tree.sheets.append(self.sheet) # These will be changed by any children self.end = self.beg self.increment_ancestors() self.child_sheet = self.sheet self.child_sheet_type = self.sheet_type # Initial detection of new sheets if self.sheet_ref: # Indicator of pointer to another sheet if "performance_map" in self.sheet_ref: self.child_sheet_type = SheetType.PERFORMANCE_MAP elif "_representation" in self.sheet_ref: self.child_sheet_type = SheetType.FLAT else: self.child_sheet_type = SheetType.ARRAY if self.child_sheet_type == SheetType.FLAT: self.next_child_beg = 3 else: self.next_child_beg = 1 self.child_sheet = self.sheet_ref if parent: self.parent.add_child(self) def add_child(self, node): ''' Add a child node to this node. ''' self.children.append(node) def get_num_ancestors(self): ''' Count ancestors back to root. ''' if self.parent: return len(self.parent.lineage) else: return 0 def get_ancestor(self, generation): if generation == 0: return self else: return self.parent.get_ancestor(generation - 1) def increment_ancestors(self): ''' Increment the begining and endings appropriately for ancestors ''' if self.parent: if self.sheet == self.parent.sheet: # If parent is in the same sheet increment both self.parent.end = self.end self.parent.increment_ancestors() self.parent.next_child_beg = self.end + 1 def add_grid_set(self, grid_set): ''' Add a cartesian product grid set to this node. Only used for "grid_variables" nodes. ''' if self.name != 'grid_variables': raise Exception( f"Cannot add a grid set to '{self.name}'. Grid sets can only be added to 'grid_variables' nodes." ) self.grid_set = grid_set def get_schema_node(self): ''' Search for schema content for this node ''' return self.tree.schema.get_schema_node(self.lineage, self.options) def is_required(self): ''' Check schema if this is a required node ''' if self.parent: parent_schema_node = self.parent.get_schema_node() if 'required' in parent_schema_node: return self.name in parent_schema_node['required'] else: return False else: return True def fill_sheet(self, worksheet): for column in [x for x in string.ascii_uppercase ] + ["A" + x for x in string.ascii_uppercase]: worksheet.column_dimensions[column].fill = self.unused_style.fill worksheet[f"{column}1"].style = self.tile_style def write_header(self, worksheet): ''' Write the header data for a new sheet ''' self.fill_sheet(worksheet) if self.parent: if self.sheet == self.parent.sheet: # A simple array (not a nested object) worksheet.cell(row=1, column=1).value = '.'.join(self.lineage) return else: worksheet.cell(row=1, column=1).value = '.'.join(self.parent.lineage) else: worksheet.cell( row=1, column=1 ).value = f"{self.tree.schema_type}: {self.tree.schema.get_rs_title(self.tree.schema_type)}" if self.sheet_type == SheetType.FLAT: xlsx_headers = [ 'Data Group', 'Data Element', 'Value', 'Units', 'Required' ] for column, header in enumerate(xlsx_headers, start=1): worksheet.cell(row=2, column=column).value = header worksheet.cell(row=2, column=column).style = self.heading_style worksheet.column_dimensions['B'].width = 50 worksheet.column_dimensions['C'].width = 31 def write_node(self): ''' Write tree content to XLSX ''' wb = self.tree.workbook sheet = self.sheet if sheet not in wb: wb.create_sheet(sheet) self.write_header(wb[sheet]) schema_node = self.get_schema_node() if self.name is not None: if self.sheet_type == SheetType.FLAT: if len(self.children) > 0: level_index = 1 wb[sheet].cell(row=self.beg, column=level_index).value = '.'.join( self.lineage) else: level_index = 2 buffer = ' ' * ( self.get_num_ancestors() - 1 ) * self.white_space_multiplier # TODO: get_num_ancestors_to_root for embedded RS sheets? wb[sheet].cell( row=self.beg, column=level_index).value = buffer + self.name wb[sheet].cell(row=self.beg, column=1).style = self.schema_style wb[sheet].cell(row=self.beg, column=2).style = self.schema_style wb[sheet].cell(row=self.beg, column=4).style = self.schema_style wb[sheet].cell(row=self.beg, column=5).style = self.schema_style wb[sheet].cell(row=self.beg, column=3).style = self.value_style if schema_node: # Add description if 'description' in schema_node: comment = openpyxl.comments.Comment( schema_node['description'], "ASHRAE 205") wb[sheet].cell(row=self.beg, column=level_index).comment = comment # Enum validation if 'enum' in schema_node: enumerants = f'"{",".join(schema_node["enum"])}"' if len( enumerants ) < 256: # Apparent limitation of written lists (TODO: https://stackoverflow.com/a/33532984/1344457) dv = openpyxl.worksheet.datavalidation.DataValidation( type='list', formula1=enumerants, allow_blank=True) wb[sheet].add_data_validation(dv) dv.add(wb[sheet].cell(row=self.beg, column=3)) # Boolean validation if 'type' in schema_node: if schema_node['type'] == 'boolean': dv = openpyxl.worksheet.datavalidation.DataValidation( type='list', formula1='"TRUE,FALSE"', allow_blank=True) wb[sheet].add_data_validation(dv) dv.add(wb[sheet].cell(row=self.beg, column=3)) # Add units if 'units' in schema_node: wb[sheet].cell(row=self.beg, column=4).value = schema_node['units'] # Add required # TODO: Make conditional formatting (e.g. red name if not entered) if self.is_required(): wb[sheet].cell(row=self.beg, column=5).value = u'\u2713' # Checkmark wb[sheet].cell( row=self.beg, column=5).alignment = Alignment(horizontal='center') else: # Not found in schema comment = openpyxl.comments.Comment( "Not found in schema.", "ASHRAE 205") wb[sheet].cell(row=self.beg, column=level_index).comment = comment wb[sheet].cell(row=self.beg, column=level_index).font = Font( color='FF0001', bold=True) if self.sheet_ref: wb[sheet].cell(row=self.beg, column=3).value = '$' + self.sheet_ref # Hyperlink to referenced sheets wb[sheet].cell( row=self.beg, column=3).hyperlink = f"#{self.sheet_ref}!A1" if (self.child_sheet_type == SheetType.ARRAY and len(self.children) == 0): # Make sheet for holding array values array_sheet = unique_name_with_index( self.child_sheet, self.tree.sheets) wb.create_sheet(array_sheet) self.write_header(wb[array_sheet]) wb[array_sheet].cell(row=2, column=1).value = self.name wb[array_sheet].cell( row=2, column=1).style = self.schema_style wb[array_sheet].cell( row=3, column=1).style = self.schema_style if schema_node: if 'units' in schema_node: wb[array_sheet].cell( row=3, column=1).value = schema_node['units'] row = 4 if self.value: for value in self.value: wb[array_sheet].cell(row=row, column=1).value = value wb[array_sheet].cell( row=row, column=1).style = self.value_style row += 1 else: array_length = 5 if schema_node: if 'maxItems' in schema_node: array_length = schema_node['maxItems'] for i in range(array_length): wb[array_sheet].cell( row=row, column=1).style = self.value_style row += 1 if self.value is not None and self.sheet_ref is None: wb[sheet].cell(row=self.beg, column=3).value = self.value # TODO: Something better here...a lot of repetition... elif self.sheet_type == SheetType.PERFORMANCE_MAP: if len(self.children) > 0: level_index = 2 else: level_index = 3 wb[sheet].cell(row=level_index, column=self.beg).value = self.name if '_variables' in self.parent.name: if self.parent.name == 'grid_variables': wb[sheet].cell( row=2, column=self.beg).style = self.grid_var_style wb[sheet].cell( row=3, column=self.beg).style = self.grid_var_style wb[sheet].cell( row=4, column=self.beg).style = self.grid_var_style wb[sheet].cell(row=level_index, column=self.beg).alignment = Alignment( text_rotation=45) if self.parent.grid_set: row = 5 for value in self.parent.grid_set[self.name]: wb[sheet].cell( row=row, column=self.beg).style = self.value_style wb[sheet].cell(row=row, column=self.beg).value = value row += 1 else: # 2^n rows for spacing row = 5 for i in range(2**(len(self.parent.children))): wb[sheet].cell( row=row, column=self.beg).style = self.value_style row += 1 else: wb[sheet].cell( row=2, column=self.beg).style = self.schema_style wb[sheet].cell( row=3, column=self.beg).style = self.schema_style wb[sheet].cell( row=4, column=self.beg).style = self.schema_style wb[sheet].cell(row=level_index, column=self.beg).alignment = Alignment( text_rotation=45) if self.value is not None: row = 5 for value in self.value: wb[sheet].cell( row=row, column=self.beg).style = self.value_style wb[sheet].cell(row=row, column=self.beg).value = value row += 1 else: # 2^n rows for spacing row = 5 for i in range(2**(len( self.parent.parent.children[0].children))): wb[sheet].cell( row=row, column=self.beg).style = self.value_style row += 1 if schema_node: # Add units if 'units' in schema_node: wb[sheet].cell( row=4, column=self.beg).value = schema_node['units'] # Add required # TODO: Make conditional formatting (e.g. red name if not entered) # Add description if 'description' in schema_node: comment = openpyxl.comments.Comment( schema_node['description'], "ASHRAE 205") wb[sheet].cell(row=level_index, column=self.beg).comment = comment else: # Not found in schema comment = openpyxl.comments.Comment( "Not found in schema.", "ASHRAE 205") wb[sheet].cell(row=level_index, column=self.beg).comment = comment wb[sheet].cell(row=level_index, column=self.beg).font = Font(color='FF0001', bold=True) # TODO: Something better here...a lot of repetition... elif self.sheet_type == SheetType.ARRAY: if len(self.children) > 0: raise Exception( "Were not handling nested items in an array yet!") wb[sheet].cell(row=2, column=self.beg).value = self.name wb[sheet].cell(row=2, column=self.beg).style = self.schema_style wb[sheet].cell(row=3, column=self.beg).style = self.schema_style row = 4 if self.value is not None: for value in self.value: wb[sheet].cell(row=row, column=self.beg).value = value wb[sheet].cell( row=row, column=self.beg).style = self.value_style if schema_node: # Enum validation if 'enum' in schema_node: enumerants = f'"{",".join(schema_node["enum"])}"' if len( enumerants ) < 256: # Apparent limitation of written lists (TODO: https://stackoverflow.com/a/33532984/1344457) dv = openpyxl.worksheet.datavalidation.DataValidation( type='list', formula1=enumerants, allow_blank=True) wb[sheet].add_data_validation(dv) dv.add(wb[sheet].cell(row=row, column=self.beg)) row += 1 else: array_length = 5 parent_schema_node = self.parent.get_schema_node() if parent_schema_node: if 'maxItems' in parent_schema_node: array_length = parent_schema_node['maxItems'] for i in range(array_length): wb[sheet].cell( row=row, column=self.beg).style = self.value_style if schema_node: # Enum validation if 'enum' in schema_node: enumerants = f'"{",".join(schema_node["enum"])}"' if len( enumerants ) < 256: # Apparent limitation of written lists (TODO: https://stackoverflow.com/a/33532984/1344457) dv = openpyxl.worksheet.datavalidation.DataValidation( type='list', formula1=enumerants, allow_blank=True) wb[sheet].add_data_validation(dv) dv.add(wb[sheet].cell(row=row, column=self.beg)) row += 1 if schema_node: # Add units if 'units' in schema_node: wb[sheet].cell( row=3, column=self.beg).value = schema_node['units'] # Add required # TODO: Make conditional formatting (e.g. red name if not entered) # Add description if 'description' in schema_node: comment = openpyxl.comments.Comment( schema_node['description'], "ASHRAE 205") wb[sheet].cell(row=2, column=self.beg).comment = comment else: # Not found in schema comment = openpyxl.comments.Comment( "Not found in schema.", "ASHRAE 205") wb[sheet].cell(row=2, column=self.beg).comment = comment wb[sheet].cell(row=2, column=self.beg).font = Font(color='FF0001', bold=True) for child in self.children: child.write_node() def read_node(self): ''' Translate XLSX content into nodes of a tree. ''' ws = self.tree.workbook[self.child_sheet] end_node = False while not end_node: if self.child_sheet_type == SheetType.PERFORMANCE_MAP: # Everything from the perspective of parent node data_group = ws.cell(row=2, column=self.next_child_beg).value data_element = ws.cell(row=3, column=self.next_child_beg).value if data_group and data_group != self.name: if data_group == 'grid_variables': new_node = A205XLSXNode(data_group, parent=self) new_node.add_grid_set({}) new_node.read_node() elif data_group == 'lookup_variables': if self.name == 'grid_variables': # We hit the end of grid variables and need to conclude that node # process grid set grid_set = {} for child in self.children: grid_set[child.name] = child.value self.add_grid_set(grid_set) # check grid set? grid_vars = process_grid_set(grid_set) # reset grid variable values for child in self.children: child.value = grid_vars[child.name] end_node = True else: new_node = A205XLSXNode(data_group, parent=self) new_node.read_node() elif data_element: if self.name not in ['grid_variables', 'lookup_variables']: raise Exception( f"Invalid data group: '{self.name}'. Data groups in {self.parent.name} should be 'grid_variables' or 'lookup_variables'" ) row = 5 end_of_column = False value = [] while not end_of_column: item = ws.cell(row=row, column=self.next_child_beg).value if item is not None: value.append(item) row += 1 else: end_of_column = True new_node = A205XLSXNode(data_element, parent=self, value=value) else: # End of sheet end_node = True elif self.child_sheet_type == SheetType.ARRAY: # Everything from the perspective of parent node data_element = ws.cell(row=2, column=self.next_child_beg).value if data_element: row = 4 end_of_column = False value = [] while not end_of_column: item = ws.cell(row=row, column=self.next_child_beg).value if item is not None: value.append(item) row += 1 else: end_of_column = True new_node = A205XLSXNode(data_element, parent=self, value=value) else: # End of sheet end_node = True else: # Flat Sheets data_group = ws.cell(row=self.next_child_beg, column=1).value data_element = ws.cell(row=self.next_child_beg, column=2).value cell_value = ws.cell(row=self.next_child_beg, column=3).value value = cell_value sheet_ref = None if type(cell_value) == str: if cell_value[0] == '$': value = None sheet_ref = cell_value[1:] if data_group: lineage = data_group.split(".") if len(lineage) <= self.get_num_ancestors( ) + 1 and self.parent is not None: # if lineage the same or shorter, this is not going to be a child node end_node = True else: new_node = A205XLSXNode(lineage[-1], parent=self, value=value, sheet_ref=sheet_ref) new_node.read_node() elif data_element: if sheet_ref: # Get array values from another sheet row = 4 end_of_column = False value = [] while not end_of_column: item = self.tree.workbook[sheet_ref].cell( row=row, column=1).value if item is not None: value.append(item) row += 1 else: end_of_column = True # Determine hierarchy level using number of spaces level = (len(data_element) - len(data_element.lstrip(' ')) ) / self.white_space_multiplier data_element = data_element.strip(' ') generations = self.get_num_ancestors() - level if generations > 0: end_node = True A205XLSXNode(data_element, parent=self.get_ancestor(generations), value=value, sheet_ref=sheet_ref) else: # End of sheet end_node = True def collect_content(self, content): ''' Collect content from the tree and return it as a Python Dict. Used primarily to load contents from an XLSX sheet. ''' if len(self.children) > 0: if self.child_sheet_type == SheetType.ARRAY: # Add list elements content[self.name] = [] length = len(self.children[0].value) for i in range(length): item = {} for child in self.children: if len(child.children) > 0: raise Exception( "Array sheets can only be one level deep!") item[child.name] = child.value[i] content[self.name].append(item) else: if self.name: content[self.name] = {} for child in self.children: child.collect_content(content[self.name]) else: for child in self.children: child.collect_content(content) else: content[self.name] = self.value
def get(self, request, *args, **kwargs): query = TiempoMuertonDet.objects.all() wb = Workbook() ws = wb.active ws.tittle = 'Tiempos Muertos' #Establer el nombre del archivo nombre_archivo = "Reporte Tiempos Muertosw.xlsx" ws['B1'].alignment = Alignment(horizontal='left', vertical='center') ws['B1'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B1'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B1'].font = Font(name='calibri', size=12, bold=True) ws['B1'] = 'Company' ws.merge_cells('B1:F1') ws['B2'].alignment = Alignment(horizontal='left', vertical='center') ws['B2'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B2'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B2'].font = Font(name='calibri', size=12, bold=True) ws['B2'] = 'Department' ws.merge_cells('B2:F2') ws['B3'].alignment = Alignment(horizontal='left', vertical='center') ws['B3'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B3'].font = Font(name='calibri', size=12, bold=True) ws['B3'] = 'Reporte de Tiempos Muertos' ws.merge_cells('B3:F3') ws.row_dimensions[1].height = 20 ws.row_dimensions[2].height = 20 ws.row_dimensions[3].height = 20 ws.column_dimensions['B'].width = 20 ws.column_dimensions['C'].width = 20 ws.column_dimensions['D'].width = 20 ws.column_dimensions['E'].width = 20 ws['B6'].alignment = Alignment(horizontal='center', vertical='center') ws['B6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['B6'].font = Font(name='calibri', size=11, bold=True) ws['B6'] = 'Fecha' ws['C6'].alignment = Alignment(horizontal='center', vertical='center') ws['C6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['C6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['C6'].font = Font(name='calibri', size=11, bold=True) ws['C6'] = 'Planta' controlador = 7 for q in query: ws.cell(row=controlador, column=7).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=7).border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=7).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=7).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=7).value = str(q.causa) ws.cell(row=controlador, column=7).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=7).border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=7).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=7).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=7).value = q.cantidad ws.cell(row=controlador, column=8).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=8).border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=8).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=8).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=8).value = q.obs id_enc = q.tiempo_muerto_id query2 = TiempoMuertoEnc.objects.filter(id=id_enc) for x in query2: ws.cell(row=controlador, column=2).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=2).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=2).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=2).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=2).value = x.fecha_produccion ws.cell(row=controlador, column=3).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=3).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=3).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=3).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=3).value = str(x.planta) ws.cell(row=controlador, column=4).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=4).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=4).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=4).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=4).value = str(x.linea) ws.cell(row=controlador, column=5).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=5).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=5).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=5).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=5).value = str(x.supervisor) ws.cell(row=controlador, column=5).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=5).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=5).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=5).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=5).value = str(x.turno) controlador += 1 response = HttpResponse(content_type='application/ms-excel') contenido = "attachment; filename = {0}".format(nombre_archivo) response["Content-Disposition"] = contenido wb.save(response) return response
from openpyxl import load_workbook from openpyxl.styles import Border, Side wb = load_workbook('26.sales_turnover.xlsx') ws = wb.active black_thin = Side(color='000000', border_style='thin') border = Border(left=black_thin, right=black_thin, top=black_thin, bottom=black_thin) for row in ws.iter_rows(min_row=2, min_col=2): for cell in row: cell.border = border wb.save('26.sales_turnover_changed.xlsx')
def get_styles(): font_main = Font(name='Times New Roman', size=12, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') font_bold = Font(name='Times New Roman', size=12, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') font_bold_s = Font(name='Times New Roman', size=10, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') font_calibri = Font(name='Calibri', size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') font_arial = Font(name='Arial Cyr', size=12, bold=False, italic=True, vertAlign=None, underline='none', strike=False, color='FF000000') fill = PatternFill(fill_type='solid', start_color='c1c1c1', end_color='c2c2c2') border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000'), top=Side(border_style='thin', color='FF000000'), bottom=Side(border_style='thin', color='FF000000'), diagonal=Side(border_style='thin', color='FF000000'), diagonal_direction=0, outline=Side(border_style='thin', color='FF000000'), vertical=Side(border_style='thin', color='FF000000'), horizontal=Side(border_style='thin', color='FF000000')) align_center = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) align_center2 = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) align_left = Alignment(horizontal='left', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) align_right = Alignment(horizontal='right', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) header_font = Font(name='Times New Roman', size=9, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') return (font_main, font_bold, font_bold_s, font_calibri, font_arial, fill, border, align_center, align_center2, align_left, align_right, header_font)
def exam_scores(exam_id): # Читаем данные из базы данных exam = Exam.objects.select_related('course').get(id=exam_id) group = exam.course.group exam_points = exam.exammarks_set.all().order_by('student__FIO') semester = exam.course.discipline_detail.semester.name if int(semester) % 2 == 1: additional = int(semester) // 2 else: additional = (int(semester) - 1) // 2 edu_period = EduPeriod.objects.get(begin_year__year=group.begin_year.year + additional) # Открываем шаблон app_dir = os.path.dirname(os.path.abspath(__file__)) template = os.path.join(app_dir, 'exam_scores.xlsx') workbook = load_workbook(template) ws = workbook.active # Семестр ws['A5'] = ws['L5'] = 'Семестр: {}, {} – {} уч.г.'.format( semester, edu_period.begin_year.year, edu_period.end_year.year, ) # Форма контроля ws['A6'] = ws['L6'] = 'Форма контроля: {}, курс: {}, группа: {}'.format( exam.get_controlType_display(), group.year, group.Name, ) # Дисциплина ws['A7'] = ws[ 'L7'] = 'Дисциплина: ' + exam.course.discipline_detail.discipline.Name # ФИО преподавателя ws['A8'] = ws[ 'L8'] = 'Фамилия, имя, отчество преподавателя: ' + exam.course.lecturer.FIO # Дата ws['A9'] = ws[ 'L9'] = 'Дата проведения зачета/экзамена: {:%d.%m.%Y}'.format( exam.examDate) # Таблица с баллами summary = { ExamMarks.MARKS[0][0]: 0, ExamMarks.MARKS[1][0]: 0, ExamMarks.MARKS[2][0]: 0, ExamMarks.MARKS[3][0]: 0, ExamMarks.MARKS[4][0]: 0, ExamMarks.MARKS[5][0]: 0, ExamMarks.MARKS[6][0]: 0, ExamMarks.MARKS[7][0]: 0, ExamMarks.MARKS[8][0]: 0, ExamMarks.MARKS[9][0]: 0 } k = 0 for points in exam_points: k += 1 ws.insert_rows(11 + k) ws.row_dimensions[11 + k].height = 30 row = str(11 + k) ws['A' + row] = ws['L' + row] = str(k) ws['B' + row] = ws['M' + row] = points.student.FIO ws['C' + row] = ws['N' + row] = points.student.student_id ws['D' + row] = ws['O' + row] = points.inPoints + points.additional_points ws['E' + row] = ws['P' + row] = points.examPoints ws['F' + row] = ws['Q' + row] = points.total_points ws['G' + row] = ws['R' + row] = points.get_mark_display() ws['H' + row] = ws['S' + row] = points.mark_symbol summary[points.mark] += 1 # Стиль для ячеек таблицы solid_line = Side(style='thin', color='000000') cell_style = NamedStyle(name='cell_style') cell_style.alignment.horizontal = 'center' cell_style.alignment.vertical = 'center' cell_style.alignment.wrapText = Bool(True) cell_style.border = Border(left=solid_line, right=solid_line, top=solid_line, bottom=solid_line) cell_style.font = Font(name='Arial', size=9) cell_style.number_format = '#.0' # Применяем стили к таблице for i in range(12, k + 12): for j in range(1, 10): ws.cell(row=i, column=j).style = ws.cell(row=i, column=j + 11).style = cell_style ws['C' + str(k + 14)] = ws['N' + str(k + 14)] = summary[6] ws['C' + str(k + 15)] = ws['N' + str(k + 15)] = summary[7] ws['C' + str(k + 16)] = ws['N' + str(k + 16)] = summary[8] ws['C' + str(k + 17)] = ws['N' + str(k + 17)] = summary[5] ws['C' + str(k + 18)] = ws['N' + str(k + 18)] = summary[4] ws['C' + str(k + 19)] = ws['N' + str(k + 19)] = summary[3] ws['C' + str(k + 20)] = ws['N' + str(k + 20)] = summary[2] ws['C' + str(k + 21)] = ws['N' + str(k + 21)] = summary[0] # Суммы баллов и буквенные эквиваленты оценки for i in range(8): row = str(14 + k + i) ws.merge_cells('E' + row + ':F' + row) ws.merge_cells('P' + row + ':Q' + row) ws.merge_cells('G' + row + ':I' + row) ws.merge_cells('R' + row + ':T' + row) ws['E' + row].style = ws['F' + row].style = ws['G' + row].style = \ ws['H' + row].style = ws['I' + row].style = ws['P' + row].style = \ ws['Q' + row].style = ws['R' + row].style = ws['S' + row].style = \ ws['T' + row].style = cell_style # Подпись директора row = str(24 + k) ws.merge_cells('A' + row + ':I' + row) ws.merge_cells('L' + row + ':T' + row) ws.print_area = 'A1:T' + str(row) ws.page_setup = PrintPageSetup(worksheet=ws) ws.page_setup.paperSize = '9' ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE ws.page_setup.fitToHeight = True ws.page_setup.fitToWidth = True ws.page_setup.fitToPage = True ws.sheet_properties.pageSetUpPr.fitToPage = True return workbook
def write_dest(xlsx_name, schema_name): border = Border( left=Side(border_style=borders.BORDER_THIN, color='FF000000'), right=Side(border_style=borders.BORDER_THIN, color='FF000000'), top=Side(border_style=borders.BORDER_THIN, color='FF000000'), bottom=Side(border_style=borders.BORDER_THIN, color='FF000000') ) alignment = Alignment(horizontal='justify', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=True, indent=0) fill = PatternFill(fill_type=None, start_color='FFFFFFFF') # 基本的样式 basic_style = Style(font=Font(name='Microsoft YaHei') , border=border, alignment=alignment , fill=fill) title_style = basic_style.copy( font=Font(name='Microsoft YaHei', b=True, size=20, color='00215757'), alignment=Alignment(horizontal='center', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=True, indent=0), fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00B2CBED')) header_style = basic_style.copy( font=Font(name='Microsoft YaHei', b=True, size=15, color='00215757'), fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F')) common_style = basic_style.copy() link_style = basic_style.copy(font=Font( name='Microsoft YaHei', color=colors.BLUE, underline='single')) table_data = load_schema(schema_name) wb = Workbook() wb.active.title = "首页列表" for table in table_data: ws = wb.create_sheet(title=table[0]) ws.merge_cells('E3:H3') # 合并单元格 ws['E3'].style = title_style ws['F2'].style = Style(border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['G2'].style = Style(border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['H2'].style = Style(border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['I3'].style = Style(border=Border( left=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['E3'] = table[0] ws['E4'].style = header_style ws['E4'] = '列名' ws['F4'].style = header_style ws['F4'] = '类型' ws['G4'].style = header_style ws['G4'] = '空值约束' ws['H4'].style = header_style ws['H4'] = '备注' ws.column_dimensions['E'].width = 20 ws.column_dimensions['F'].width = 20 ws.column_dimensions['G'].width = 16 ws.column_dimensions['H'].width = 45 for idx, each_column in enumerate(table[2:]): ws['E{}'.format(idx + 5)].style = common_style ws['E{}'.format(idx + 5)] = each_column[0] ws['F{}'.format(idx + 5)].style = common_style ws['F{}'.format(idx + 5)] = each_column[1] ws['G{}'.format(idx + 5)].style = common_style ws['G{}'.format(idx + 5)] = each_column[2] ws['H{}'.format(idx + 5)].style = common_style ws['H{}'.format(idx + 5)] = each_column[3].strip().split('\'')[1] ws = wb['首页列表'] ws.merge_cells('D3:F3') ws['D3'].style = title_style ws['E2'].style = Style(border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['F2'].style = Style(border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['G3'].style = Style(border=Border( left=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['D3'] = '贷快发数据库系统表' ws['D4'].style = header_style ws['D4'] = '编号' ws['E4'].style = header_style ws['E4'] = '表名' ws['F4'].style = header_style ws['F4'] = '详情链接' ws.column_dimensions['D'].width = 15 ws.column_dimensions['E'].width = 25 ws.column_dimensions['F'].width = 35 for inx, val in enumerate(table_data): ws['D{}'.format(inx + 5)].style = common_style ws['D{}'.format(inx + 5)] = inx + 1 ws['E{}'.format(inx + 5)].style = common_style ws['E{}'.format(inx + 5)] = val[1] linkcell = ws['F{}'.format(inx + 5)] linkcell.style = link_style linkcell.value = val[0] linkcell.hyperlink = '#{0}!{1}'.format(val[0], 'E3') wb.save(filename=xlsx_name)
elif code == "TruSeq": CONC_INPUT_UDF = "Conc. Input (nM) TruSeq DNA" VOLUME_PHIX_UDF = "Volume PhiX (uL) TruSeq DNA" VOLUME_FINAL_UDF = "Volume final (uL) TruSeq DNA" START_COL = 2 START_ROW = 2 warning = [] lims = Lims(config.BASEURI, config.USERNAME, config.PASSWORD) process = Process(lims, id=sys.argv[1]) wb = Workbook() ws = wb.active side_style = Side(border_style="thin") thick_side_style = Side(border_style="thick") def sort_key(elem): input, output = elem container, well = output.location row, col = well.split(":") return (container.id, int(col), row) formatting = [("Sample name", 16, None), ("Well", 8, None), ("Sample conc. [nM]", 10, '0.00'), ("Conc. Input [nM]", 10, '0.0'), ("Volum final [uL]", 10, '0'), ("Input [uL]", 10, '0.00'), ("RSB [uL]", 10, '0.00'), ("PhiX [uL]", 10, '0.00'), ("Well strip", 9, '0')]
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['shopfloor']['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 + 9 * 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 = 9 * 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 + 9 * 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 + 9 * 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 + 9 * 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.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 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) 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'] = round(reporting_period_data['subtotals_per_unit_area'][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 + '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'] = round(reporting_period_data['total_per_unit_area'], 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, 9 + 1): ws.row_dimensions[i].height = 0.1 ################################## current_row_number = 11 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']) total = Decimal(0.0) for i in range(0, ca_len): total = reporting_period_data['subtotals'][i] + total 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(reporting_period_data['subtotals'][i] / total) \ if total > 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 = '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 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 current_end_row_number = current_row_number 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: current_end_row_number += ca_len * 6 + 1 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) current_end_row_number += (len(time) + 3) 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 = Decimal(0.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 current_row_number = current_end_row_number ################################## 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 \ or 'child_space_names_array' not in report['child_space'].keys() \ or report['child_space']['energy_category_names'] is None \ or len(report['child_space']['child_space_names_array']) == 0 \ or len(report['child_space']['child_space_names_array'][0]) == 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 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(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 = 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)] = 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 = 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)] = '总计 (' + 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 = title_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 = Decimal(0.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 table_end_row_number = current_row_number current_row_number += 1 chart_start_row_number = current_row_number # Pie for i in range(0, ca_len): pie = PieChart() 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 + i, 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 = 8 pie.title = ws.cell(column=3 + i, row=table_start_row_number).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(chart_start_row_number) else: chart_cell = 'E' + str(chart_start_row_number) chart_start_row_number += 5 ws.add_chart(pie, chart_cell) current_row_number = chart_start_row_number if ca_len % 2 == 1: current_row_number += 5 current_row_number += 1 ############################################# filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
from openpyxl.writer.excel import save_virtual_workbook from openpyxl.styles import Alignment, Border, Side from django.utils.translation import ugettext_lazy as _ from accounts.models import User from distriblists.models import DistributionList from distriblists.forms import DistributionListForm header_alignment = Alignment( horizontal='center', textRotation=45, ) role_alignment = Alignment(horizontal='center') cell_border = Border(top=Side(style='thin'), bottom=Side(style='thin'), left=Side(style='thin'), right=Side(style='thin'), vertical=Side(style='thin')) def import_review_members(filepath, category): """Import review members from an excel file.""" wb = openpyxl.load_workbook(filepath) ws = wb.active # Extracts the user list from the header row emails, user_ids = _extract_users(ws) max_col = len(user_ids) + 1 # Don't use ws.max_column, it's not reliable
def data2pandas2save(self, festival, product_dict, result_data): year_df = pd.Series(self.result_data.values()) index_df = pd.Series(list(self.result_data.keys())) df1 = pd.DataFrame( index_df, index=[i for i in range(0, len(self.product_dict.keys()))]) df2 = pd.DataFrame( list(year_df[0]), index=[i for i in range(0, len(self.product_dict.keys()))]) df3 = pd.merge(df1, df2, left_index=True, right_index=True, how='outer') df3.columns.values[0] = '農產品' for index, row in df3.iterrows(): product_name = [k for k in list(self.product_dict.keys())][index] df3.loc[index, '農產品'] = product_name Row_list = [] for index, rows in df3.iterrows(): data_list = [] for y in range(self.roc_before5years, self.roc_year + 1): data_list.append(rows[str(y)]) l = [y for x in data_list for y in x] Row_list.append(l) columns = [] for y in range(self.roc_before5years, self.roc_year + 1): if y == self.roc_year: for i in range(0, 9): temp_list = [] if i == 0: temp = '節前四周' elif i == 1: temp = '節前三周' elif i == 2: temp = '節前二周' elif i == 3: temp = '節前一周' elif i == 4: temp = '節後一周' elif i == 5: temp = '節後二周' elif i == 6: temp = '節後三周' elif i == 7: temp = '節後四周' elif i == 8: temp = '最近一日' temp_list.append(str(y)) temp_list.append(temp) columns.append(tuple(temp_list)) else: for i in range(0, 8): temp_list = [] if i == 0: temp = '節前四周' elif i == 1: temp = '節前三周' elif i == 2: temp = '節前二周' elif i == 3: temp = '節前一周' elif i == 4: temp = '節後一周' elif i == 5: temp = '節後二周' elif i == 6: temp = '節後三周' elif i == 7: temp = '節後四周' temp_list.append(str(y)) temp_list.append(temp) columns.append(tuple(temp_list)) df4 = pd.DataFrame(Row_list, columns=pd.MultiIndex.from_tuples(columns)) for index, row in df4.iterrows(): product_name = [k for k in list(self.product_dict.keys())][index] df4.loc[index, ('農產品', '產品名稱')] = product_name for index, row in df4.iterrows(): df4.loc[index, ( '節後四周與去年同期比較', '漲跌率(%)' )] = df4.loc[index, (str(self.roc_year), '節後四周')] / df4.loc[index, ( str(self.roc_year - 1), '節後四周')] * 100 - 100 df4.loc[index, ('節後四周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, ( str(self.roc_year), '節後四周')] - df4.loc[index, (str(self.roc_year - 1), '節後四周')] df4.loc[index, ('前五年簡單平均', '節後四周平均(元/公斤)')] = self.trimmean( df4.loc[index, [(str(self.roc_year - 1), '節後四周'), (str(self.roc_year - 2), '節後四周'), (str(self.roc_year - 3), '節後四周'), (str(self.roc_year - 4), '節後四周'), (str(self.roc_year - 5), '節後四周')]]) # df4.loc[index,('前五年簡單平均','節後四周平均(元/公斤)')] = df4.loc[index,[(str(self.roc_year-1), '節後四周'),(str(self.roc_year-2), '節後四周'),(str(self.roc_year-3), '節後四周'),(str(self.roc_year-4), '節後四周'),(str(self.roc_year-5), '節後四周')]].mean(axis=0) df4.loc[index, ( '節後三周與去年同期比較', '漲跌率(%)' )] = df4.loc[index, (str(self.roc_year), '節後三周')] / df4.loc[index, ( str(self.roc_year - 1), '節後三周')] * 100 - 100 df4.loc[index, ('節後三周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, ( str(self.roc_year), '節後三周')] - df4.loc[index, (str(self.roc_year - 1), '節後三周')] df4.loc[index, ('前五年簡單平均', '節後三周平均(元/公斤)')] = self.trimmean( df4.loc[index, [(str(self.roc_year - 1), '節後三周'), (str(self.roc_year - 2), '節後三周'), (str(self.roc_year - 3), '節後三周'), (str(self.roc_year - 4), '節後三周'), (str(self.roc_year - 5), '節後三周')]]) df4.loc[index, ( '節後二周與去年同期比較', '漲跌率(%)' )] = df4.loc[index, (str(self.roc_year), '節後二周')] / df4.loc[index, ( str(self.roc_year - 1), '節後二周')] * 100 - 100 df4.loc[index, ('節後二周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, ( str(self.roc_year), '節後二周')] - df4.loc[index, (str(self.roc_year - 1), '節後二周')] df4.loc[index, ('前五年簡單平均', '節後二周平均(元/公斤)')] = self.trimmean( df4.loc[index, [(str(self.roc_year - 1), '節後二周'), (str(self.roc_year - 2), '節後二周'), (str(self.roc_year - 3), '節後二周'), (str(self.roc_year - 4), '節後二周'), (str(self.roc_year - 5), '節後二周')]]) df4.loc[index, ( '節後一周與去年同期比較', '漲跌率(%)' )] = df4.loc[index, (str(self.roc_year), '節後一周')] / df4.loc[index, ( str(self.roc_year - 1), '節後一周')] * 100 - 100 df4.loc[index, ('節後一周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, ( str(self.roc_year), '節後一周')] - df4.loc[index, (str(self.roc_year - 1), '節後一周')] df4.loc[index, ('前五年簡單平均', '節後一周平均(元/公斤)')] = self.trimmean( df4.loc[index, [(str(self.roc_year - 1), '節後一周'), (str(self.roc_year - 2), '節後一周'), (str(self.roc_year - 3), '節後一周'), (str(self.roc_year - 4), '節後一周'), (str(self.roc_year - 5), '節後一周')]]) df4.loc[index, ( '節前一周與去年同期比較', '漲跌率(%)' )] = df4.loc[index, (str(self.roc_year), '節前一周')] / df4.loc[index, ( str(self.roc_year - 1), '節前一周')] * 100 - 100 df4.loc[index, ('節前一周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, ( str(self.roc_year), '節前一周')] - df4.loc[index, (str(self.roc_year - 1), '節前一周')] df4.loc[index, ('前五年簡單平均', '節前一周平均(元/公斤)')] = self.trimmean( df4.loc[index, [(str(self.roc_year - 1), '節前一周'), (str(self.roc_year - 2), '節前一周'), (str(self.roc_year - 3), '節前一周'), (str(self.roc_year - 4), '節前一周'), (str(self.roc_year - 5), '節前一周')]]) df4.loc[index, ( '節前二周與去年同期比較', '漲跌率(%)' )] = df4.loc[index, (str(self.roc_year), '節前二周')] / df4.loc[index, ( str(self.roc_year - 1), '節前二周')] * 100 - 100 df4.loc[index, ('節前二周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, ( str(self.roc_year), '節前二周')] - df4.loc[index, (str(self.roc_year - 1), '節前二周')] df4.loc[index, ('前五年簡單平均', '節前二周平均(元/公斤)')] = self.trimmean( df4.loc[index, [(str(self.roc_year - 1), '節前二周'), (str(self.roc_year - 2), '節前二周'), (str(self.roc_year - 3), '節前二周'), (str(self.roc_year - 4), '節前二周'), (str(self.roc_year - 5), '節前二周')]]) df4.loc[index, ( '節前三周與去年同期比較', '漲跌率(%)' )] = df4.loc[index, (str(self.roc_year), '節前三周')] / df4.loc[index, ( str(self.roc_year - 1), '節前三周')] * 100 - 100 df4.loc[index, ('節前三周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, ( str(self.roc_year), '節前三周')] - df4.loc[index, (str(self.roc_year - 1), '節前三周')] df4.loc[index, ('前五年簡單平均', '節前三周平均(元/公斤)')] = self.trimmean( df4.loc[index, [(str(self.roc_year - 1), '節前三周'), (str(self.roc_year - 2), '節前三周'), (str(self.roc_year - 3), '節前三周'), (str(self.roc_year - 4), '節前三周'), (str(self.roc_year - 5), '節前三周')]]) df4.loc[index, ( '節前四周與去年同期比較', '漲跌率(%)' )] = df4.loc[index, (str(self.roc_year), '節前四周')] / df4.loc[index, ( str(self.roc_year - 1), '節前四周')] * 100 - 100 df4.loc[index, ('節前四周與去年同期比較', '差幅(元/公斤)')] = df4.loc[index, ( str(self.roc_year), '節前四周')] - df4.loc[index, (str(self.roc_year - 1), '節前四周')] df4.loc[index, ('前五年簡單平均', '節前四周平均(元/公斤)')] = self.trimmean( df4.loc[index, [(str(self.roc_year - 1), '節前四周'), (str(self.roc_year - 2), '節前四周'), (str(self.roc_year - 3), '節前四周'), (str(self.roc_year - 4), '節前四周'), (str(self.roc_year - 5), '節前四周')]]) df4.index = df4.index + 1 columns_list = [] columns_list.append(('農產品', '產品名稱')) columns_list.append(('{}'.format(self.roc_year), '最近一日')) for y in range(self.roc_year, self.roc_year - 5 - 1, -1): columns_list.append(('{}'.format(y), '節後四周')) columns_list.append(('節後四周與去年同期比較', '漲跌率(%)')) columns_list.append(('節後四周與去年同期比較', '差幅(元/公斤)')) columns_list.append(('前五年簡單平均', '節後四周平均(元/公斤)')) for y in range(self.roc_year, self.roc_year - 5 - 1, -1): columns_list.append(('{}'.format(y), '節後三周')) columns_list.append(('節後三周與去年同期比較', '漲跌率(%)')) columns_list.append(('節後三周與去年同期比較', '差幅(元/公斤)')) columns_list.append(('前五年簡單平均', '節後三周平均(元/公斤)')) for y in range(self.roc_year, self.roc_year - 5 - 1, -1): columns_list.append(('{}'.format(y), '節後二周')) columns_list.append(('節後二周與去年同期比較', '漲跌率(%)')) columns_list.append(('節後二周與去年同期比較', '差幅(元/公斤)')) columns_list.append(('前五年簡單平均', '節後二周平均(元/公斤)')) for y in range(self.roc_year, self.roc_year - 5 - 1, -1): columns_list.append(('{}'.format(y), '節後一周')) columns_list.append(('節後一周與去年同期比較', '漲跌率(%)')) columns_list.append(('節後一周與去年同期比較', '差幅(元/公斤)')) columns_list.append(('前五年簡單平均', '節後一周平均(元/公斤)')) for y in range(self.roc_year, self.roc_year - 5 - 1, -1): columns_list.append(('{}'.format(y), '節前一周')) columns_list.append(('節前一周與去年同期比較', '漲跌率(%)')) columns_list.append(('節前一周與去年同期比較', '差幅(元/公斤)')) columns_list.append(('前五年簡單平均', '節前一周平均(元/公斤)')) for y in range(self.roc_year, self.roc_year - 5 - 1, -1): columns_list.append(('{}'.format(y), '節前二周')) columns_list.append(('節前二周與去年同期比較', '漲跌率(%)')) columns_list.append(('節前二周與去年同期比較', '差幅(元/公斤)')) columns_list.append(('前五年簡單平均', '節前二周平均(元/公斤)')) for y in range(self.roc_year, self.roc_year - 5 - 1, -1): columns_list.append(('{}'.format(y), '節前三周')) columns_list.append(('節前三周與去年同期比較', '漲跌率(%)')) columns_list.append(('節前三周與去年同期比較', '差幅(元/公斤)')) columns_list.append(('前五年簡單平均', '節前三周平均(元/公斤)')) for y in range(self.roc_year, self.roc_year - 5 - 1, -1): columns_list.append(('{}'.format(y), '節前四周')) columns_list.append(('節前四周與去年同期比較', '漲跌率(%)')) columns_list.append(('節前四周與去年同期比較', '差幅(元/公斤)')) columns_list.append(('前五年簡單平均', '節前四周平均(元/公斤)')) df4 = df4[columns_list] df5 = df4.copy() if festival == 1: self.roc_date_range = self.ROC_Chinese_New_Year_dict if festival == 2: self.roc_date_range = self.ROC_Dragon_Boat_Festival_dict if festival == 3: self.roc_date_range = self.ROC_Mid_Autumn_Festival_dict #組合新欄位名稱 yes_date = self.roc_date_range[str(self.roc_year)][-1].split('~')[0] if len(yes_date) > 5: yes_date = yes_date.split('/')[1] + '/' + yes_date.split('/')[2] columns_name = [] columns_name.append('農產品') columns_name.append('最近一日\n{}/{}\n(元/公斤)'.format( self.today.year - 1911, yes_date)) for y in range(self.roc_year, self.roc_year - 6, -1): date = self.roc_date_range[str(y)][7] columns_name.append('{0}年節後四周\n{1}\n(元/公斤)'.format(y, date)) columns_name.append('{}年節後四周較{}年同期\n漲跌率\n(%)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('{}年節後四周較{}年同期\n差幅\n(元/公斤)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('近5年簡單平均\n({}-{}年)\n節後四周\n(元/公斤)'.format( self.roc_year - 5, self.roc_year - 1)) for y in range(self.roc_year, self.roc_year - 6, -1): date = self.roc_date_range[str(y)][6] columns_name.append('{0}年節後三周\n{1}\n(元/公斤)'.format(y, date)) columns_name.append('{}年節後三周較{}年同期\n漲跌率\n(%)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('{}年節後三周較{}年同期\n差幅\n(元/公斤)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('近5年簡單平均\n({}-{}年)\n節後三周\n(元/公斤)'.format( self.roc_year - 5, self.roc_year - 1)) for y in range(self.roc_year, self.roc_year - 6, -1): date = self.roc_date_range[str(y)][5] columns_name.append('{0}年節後二周\n{1}\n(元/公斤)'.format(y, date)) columns_name.append('{}年節後二周較{}年同期\n漲跌率\n(%)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('{}年節後二周較{}年同期\n差幅\n(元/公斤)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('近5年簡單平均\n({}-{}年)\n節後二周\n(元/公斤)'.format( self.roc_year - 5, self.roc_year - 1)) for y in range(self.roc_year, self.roc_year - 6, -1): date = self.roc_date_range[str(y)][4] columns_name.append('{0}年節後一周\n{1}\n(元/公斤)'.format(y, date)) columns_name.append('{}年節後一周較{}年同期\n漲跌率\n(%)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('{}年節後一周較{}年同期\n差幅\n(元/公斤)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('近5年簡單平均\n({}-{}年)\n節後一周\n(元/公斤)'.format( self.roc_year - 5, self.roc_year - 1)) for y in range(self.roc_year, self.roc_year - 6, -1): date = self.roc_date_range[str(y)][3] columns_name.append('{0}年節前一周\n{1}\n(元/公斤)'.format(y, date)) columns_name.append('{}年節前一周較{}年同期\n漲跌率\n(%)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('{}年節前一周較{}年同期\n差幅\n(元/公斤)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('近5年簡單平均\n({}-{}年)\n節前一周\n(元/公斤)'.format( self.roc_year - 5, self.roc_year - 1)) for y in range(self.roc_year, self.roc_year - 6, -1): date = self.roc_date_range[str(y)][2] columns_name.append('{0}年節前二周\n{1}\n(元/公斤)'.format(y, date)) columns_name.append('{}年節前二周較{}年同期\n漲跌率\n(%)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('{}年節前二周較{}年同期\n差幅\n(元/公斤)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('近5年簡單平均\n({}-{}年)\n節前二周\n(元/公斤)'.format( self.roc_year - 5, self.roc_year - 1)) for y in range(self.roc_year, self.roc_year - 6, -1): date = self.roc_date_range[str(y)][1] columns_name.append('{0}年節前三周\n{1}\n(元/公斤)'.format(y, date)) columns_name.append('{}年節前三周較{}年同期\n漲跌率\n(%)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('{}年節前三周較{}年同期\n差幅\n(元/公斤)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('近5年簡單平均\n({}-{}年)\n節前三周\n(元/公斤)'.format( self.roc_year - 5, self.roc_year - 1)) for y in range(self.roc_year, self.roc_year - 6, -1): date = self.roc_date_range[str(y)][0] columns_name.append('{0}年節前四周\n{1}\n(元/公斤)'.format(y, date)) columns_name.append('{}年節前四周較{}年同期\n漲跌率\n(%)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('{}年節前四三周較{}年同期\n差幅\n(元/公斤)'.format( self.roc_year, self.roc_year - 1)) columns_name.append('近5年簡單平均\n({}-{}年)\n節前四周\n(元/公斤)'.format( self.roc_year - 5, self.roc_year - 1)) df5.columns = columns_name if self.festival == 1: festival_title = '春節' if self.festival == 2: festival_title = '端午節' if self.festival == 3: festival_title = '中秋節' file_name = '{}_{}節前價格表.xlsx'.format(self.roc_year, festival_title) writer = pd.ExcelWriter(file_name) wb = openpyxl.Workbook() df6 = df5.copy() df6.to_excel(writer) ws = wb.create_sheet(index=0, title="價格表") #pandas to openpyxl for r in dataframe_to_rows(df6, index=False, header=False): ws.append(r) #設定字形大小及格式 title_font = Font(size=22) content_font = Font(size=16) alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) #新增框線 border = Border(top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000'), left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000')) for _row in ws.iter_rows(): for _cell in _row: _cell.border = border _cell.font = content_font _cell.number_format = '#,##0.0' #小數一位 #在第一行前插入一行 ws.insert_rows(1) # 合併儲存格 ws.merge_cells('A1:BV1') #儲存格內容及格式 ws['A1'] = '{}節前農產品價格變動情形表'.format(festival_title) ws['A1'].alignment = Alignment(horizontal='center', vertical='center') ws['A1'].font = title_font #插入兩行製作各欄位標題 ws.insert_rows(2) ws.insert_rows(2) for i in range(1, len(df6.columns) + 1): if i in [ 9, 10, 18, 19, 27, 28, 36, 37, 45, 46, 54, 55, 63, 64, 72, 73 ]: ws.cell(row=2, column=i).value = df6.columns[i - 1].split('\n')[0] ws.cell(row=3, column=i).value = df6.columns[i - 1].split( '\n')[1] + '\n' + df6.columns[i - 1].split('\n')[2] else: ws.cell(row=2, column=i).value = df6.columns[i - 1] ws.cell(row=2, column=i).font = content_font ws.cell(row=3, column=i).font = content_font # 合併儲存格 for i in range(65, 91): if i in (73, 74, 82, 83): continue ws.merge_cells('{0}2:{0}3'.format(chr(i))) for i in range(65, 91): if i in (65, 66, 74, 75, 83, 84): continue ws.merge_cells('A{0}2:A{0}3'.format(chr(i))) for i in range(65, 91): if i in (66, 67, 75, 76, 84, 85): continue ws.merge_cells('B{0}2:B{0}3'.format(chr(i))) ws.merge_cells('I2:J2') ws.merge_cells('R2:S2') ws.merge_cells('AA2:AB2') ws.merge_cells('AJ2:AK2') ws.merge_cells('AS2:AT2') ws.merge_cells('BB2:BC2') ws.merge_cells('BK2:BL2') ws.merge_cells('BT2:BU2') # 設定儲存格格式 for i in range(1, len(df6.columns) + 1): ws.cell(row=2, column=i).alignment = alignment #置中及換行 ws.cell(row=3, column=i).alignment = alignment ws.cell(row=2, column=i).border = border #框線 ws.cell(row=3, column=i).border = border #欄位寬度 ws.column_dimensions['A'].width = 33 for c in range(2, len(df6.columns) + 1): ws.column_dimensions[get_column_letter(c)].width = 22 for _col in [ 'I', 'J', 'R', 'S', 'AA', 'AB', 'AJ', 'AK', 'AS', 'AT', 'BB', 'BC', 'BK', 'BL', 'BT', 'BU' ]: ws.column_dimensions[_col].width = 18.5 #設定第2列高度 ws.row_dimensions[2].height = 42 #隱藏欄位 # hidden_columns=['B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO'] # for col in hidden_columns: # ws.column_dimensions[col].hidden= True #凍結窗格 ws.freeze_panes = ws['B4'] # Close the Pandas Excel writer and output the Excel file. wb.save(file_name) return file_name
def create_excel(context_list, title_list, sheet_list): # 1、创建excel文件 f = openpyxl.Workbook() for i in range(int(sheet_list[0])): f.create_sheet(title=sheet_list[i + 1], index=i) # 2、创建sheet格式 # sheet: sheet = f.worksheets[2] for i in range(0, 5): # 行合并 sheet.merge_cells(start_row=16 * i + 3, start_column=2, end_row=16 * i + 18, end_column=2) sheet.merge_cells(start_row=16 * i + 3, start_column=3, end_row=16 * i + 6, end_column=3) sheet.merge_cells(start_row=16 * i + 7, start_column=3, end_row=16 * i + 10, end_column=3) sheet.merge_cells(start_row=16 * i + 11, start_column=3, end_row=16 * i + 14, end_column=3) sheet.merge_cells(start_row=16 * i + 15, start_column=3, end_row=16 * i + 18, end_column=3) sheet.merge_cells(start_row=16 * i + 3, start_column=4, end_row=16 * i + 6, end_column=4) sheet.merge_cells(start_row=16 * i + 7, start_column=4, end_row=16 * i + 10, end_column=4) sheet.merge_cells(start_row=16 * i + 11, start_column=4, end_row=16 * i + 14, end_column=4) sheet.merge_cells(start_row=16 * i + 15, start_column=4, end_row=16 * i + 18, end_column=4) sheet.merge_cells(start_row=83, start_column=2, end_row=83, end_column=4) sheet.merge_cells(start_row=84, start_column=2, end_row=84, end_column=4) sheet.merge_cells(start_row=85, start_column=2, end_row=85, end_column=4) # 设置单元格字体 # font = Font(name=u'宋体', size=14, color=BLUE, bold=True) # sheet.cell(row=1, column=i+1).font = font align = Alignment(horizontal='center', vertical='center') fill = PatternFill(start_color='CC99CC', end_color='CC99CC', fill_type='solid') sheet.freeze_panes = 'F3' border = Border(left=Side(style='medium', color='FF000000'), right=Side(style='medium', color='FF000000'), top=Side(style='medium', color='FF000000'), bottom=Side(style='medium', color='FF000000'), diagonal=Side(style='medium', color='FF000000'), diagonal_direction=0, outline=Side(style='medium', color='FF000000'), vertical=Side(style='medium', color='FF000000'), horizontal=Side(style='medium', color='FF000000')) for i in range(2, 86): for j in range(2, 37): sheet.cell(row=i, column=j).border = border # 3、填写内容 for i in range(len(context_list)): sheet.cell(row=2, column=i + 2).alignment = align sheet.cell(row=2, column=i + 2, value=10).fill = fill sheet.cell(row=2, column=i + 2).value = context_list[i] B_list = ['1Q', '2Q', '3Q', '4Q', '全年'] # for i in range(len(B_list)): # sheet.cell(row=i+3, column=2).alignment = align # sheet.cell(row=i+3, column=2).value = B_list[i] sheet.cell(row=3, column=2).alignment = align sheet.cell(row=3, column=2).value = B_list[0] sheet.cell(row=19, column=2).alignment = align sheet.cell(row=19, column=2).value = B_list[1] sheet.cell(row=35, column=2).alignment = align sheet.cell(row=35, column=2).value = B_list[2] sheet.cell(row=51, column=2).alignment = align sheet.cell(row=51, column=2).value = B_list[3] sheet.cell(row=67, column=2).alignment = align sheet.cell(row=67, column=2).value = B_list[4] C_list = ['签约1个月', '签约半年', '交付一周', '入住期'] for j in range(len(C_list)): for l in range(0, 4): sheet.cell(row=j + (4 * l + 3), column=3).alignment = align sheet.cell(row=j + (4 * l + 3), column=3).value = C_list[j] D_list = [ "2018.12-2019.2签约", "2018.7-2018.9签约", "2018.12.5-2019.3.15交付", "\\", "2019.3-2019.5签约", "2018.10-2018.12签约", "2019.3.16-2019.6.15交付", "\\", "2019.6-2019.8签约", "2019.1-2019.3签约", "2019.6.16-2019.9.15交付", "\\", "2019.9-2019.11签约", "2019.4-2019.6签约", "2019.9.16-2019.12.10交付", "\\", "2018.12-2019.11签约", "2018.7-2019.6签约", "2018.12.5-2019.12.10交付", "\\" ] for i in range(len(D_list)): sheet.cell(row=i + 3, column=4).alignment = align sheet.cell(row=i + 3, column=4).value = D_list[i] # 4、保存文件 curPath = os.getcwd() tempPath = 'file_target' targetPath = curPath + os.path.sep + tempPath if not os.path.exists(targetPath): os.makedirs(targetPath) for i in range(len(title_list)): f.save(targetPath + "/" + title_list[i] + ".xlsx")
def create_aww_performance_excel_file(excel_data, data_type, month, state, district, block): export_info = excel_data[1][1] excel_data = [line[3:] for line in excel_data[0][1]] thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) warp_text_alignment = Alignment(wrap_text=True) bold_font = Font(bold=True) blue_fill = PatternFill("solid", fgColor="B3C5E5") grey_fill = PatternFill("solid", fgColor="BFBFBF") workbook = Workbook() worksheet = workbook.active worksheet.title = "AWW Performance Report" worksheet.sheet_view.showGridLines = False # sheet title worksheet.merge_cells('B2:J2') title_cell = worksheet['B2'] title_cell.fill = PatternFill("solid", fgColor="4472C4") title_cell.value = "AWW Performance Report for the month of {}".format( month) title_cell.font = Font(size=18, color="FFFFFF") title_cell.alignment = Alignment(horizontal="center") # sheet header for cell in {"B3", "C3", "D3", "E3", "F3", "G3", "H3", "J3"}: worksheet[cell].fill = blue_fill worksheet[cell].font = bold_font worksheet[cell].alignment = warp_text_alignment worksheet.merge_cells('B3:C3') worksheet['B3'].value = "State: {}".format(state) worksheet['D3'].value = "District: {}".format(district) worksheet.merge_cells('E3:F3') worksheet['E3'].value = "Block: {}".format(block) worksheet.merge_cells('H3:I3') worksheet['H3'].value = "Date when downloaded:" worksheet['H3'].alignment = Alignment(horizontal="right") utc_now = datetime.now(pytz.utc) now_in_india = utc_now.astimezone(india_timezone) worksheet['J3'].value = custom_strftime('{S} %b %Y', now_in_india) worksheet['J3'].alignment = Alignment(horizontal="right") # table header table_header_position_row = 5 table_header = { 'B': "S.No", 'C': "Supervisor", 'D': "AWC", 'E': "AWW Name", 'F': "AWW Contact Number", 'G': "Home Visits Conducted", 'H': "Number of Days AWC was Open", 'I': "Weighing Efficiency", 'J': "Eligible for Incentive", } for column, value in table_header.items(): cell = "{}{}".format(column, table_header_position_row) worksheet[cell].fill = grey_fill worksheet[cell].border = thin_border worksheet[cell].font = bold_font worksheet[cell].alignment = warp_text_alignment worksheet[cell].value = value # table contents row_position = table_header_position_row + 1 for enum, row in enumerate(excel_data[1:], start=1): columns = ["B", "C", "D", "E", "F", "G", "H", "I", "J"] for column_index in range(len(columns)): column = columns[column_index] cell = "{}{}".format(column, row_position) worksheet[cell].border = thin_border if column_index == 0: worksheet[cell].value = enum else: worksheet[cell].value = row[column_index - 1] row_position += 1 # sheet dimensions title_row = worksheet.row_dimensions[2] title_row.height = 23 worksheet.row_dimensions[table_header_position_row].height = 46 widths = { 'A': 4, 'B': 7, 'C': max(15, len(state) * 4 // 3), 'D': 13 + (len(district) * 4 // 3), 'E': 12, 'F': max(13, len(block) * 4 // 3), 'G': 15, 'H': 11, 'I': 14, 'J': 14, } for column in ["C", "E", "G"]: if widths[column] > 25: worksheet.row_dimensions[3].height = max( 16 * ((widths[column] // 25) + 1), worksheet.row_dimensions[3].height) widths[column] = 25 columns = ["C", "D", "E", "F", "G", "H", "I", "J"] # column widths based on table contents for column_index in range(len(columns)): widths[columns[column_index]] = max( widths[columns[column_index]], max( len(row[column_index].decode('utf-8') if isinstance( row[column_index], bytes) else six. text_type(row[column_index])) for row in excel_data[1:]) * 4 // 3 if len(excel_data) >= 2 else 0) for column, width in widths.items(): worksheet.column_dimensions[column].width = width # export info worksheet2 = workbook.create_sheet("Export Info") worksheet2.column_dimensions['A'].width = 14 worksheet2['A1'].value = export_info[0][0] worksheet2['B1'].value = export_info[0][1] worksheet2['A2'].value = export_info[1][0] worksheet2['B2'].value = export_info[1][1] worksheet2['A3'].value = export_info[2][0] worksheet2['B3'].value = export_info[2][1] worksheet2['A4'].value = export_info[3][0] worksheet2['B4'].value = export_info[3][1] worksheet2['A4'].value = export_info[4][0] worksheet2['B4'].value = export_info[4][1] # saving file file_hash = uuid.uuid4().hex export_file = BytesIO() icds_file = IcdsFile(blob_id=file_hash, data_type=data_type) workbook.save(export_file) export_file.seek(0) icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24) icds_file.save() return file_hash
# 번호, 영어, 수학 a1 = ws["A1"] b1 = ws["B1"] c1 = ws["C1"] ws.column_dimensions["A"].width = 5 # A열의 너비를 5로 설정 ws.row_dimensions[1].height = 50 # 1열의 높이를 50으로 설정 # 스타일 적용 a1.font = Font(color="FF0000", italic=True, bold=True) # 글자 색은 빨강, 기울임 + 두껍게 적용 b1.font = Font(color="CC33FF", name="Arial", strike=True) # 폰트 지정 + 취소선 c1.font = Font(color="0000FF", size=20, underline="single") # 글자 크기 지정 + 밑줄 thin_border = Border(left=Side(style="thin"), right=Side(style="thin"), top=Side(style="thin"), bottom=Side(style="thin")) # 테두리 적용 a1.border = thin_border b1.border = thin_border c1.border = thin_border # 90점 넘는 셀은 초록색 for row in ws.rows: for cell in row: cell.alignment = Alignment( horizontal="center", vertical="center" ) # 각 cell에 대해 중앙 정렬 (center, left, right, top, bottom) if cell.column == 1: # A: 번호 열은 제외 continue
warning = [] lims = Lims(config.BASEURI, config.USERNAME, config.PASSWORD) process = Process(lims, id=sys.argv[1]) use_sample_conc = False use_output_conc = False if len(sys.argv) > 3: use_sample_conc = "use_sample_conc" in sys.argv[3].split(",") use_output_conc = "use_output_conc" in sys.argv[3].split(",") wb = Workbook() ws = wb.active side_style = Side(border_style="thin") border_style = Border(top=side_style, left=side_style, right=side_style, bottom=side_style) def sort_key(elem): input, output = elem container, well = output.location row, col = well.split(":") return (container.id, int(col), row) def get_or_default(udfname): try:
cols = list(set(df[colpick].values)) alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' font = Font(name='Calibri', size=12, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') fill = PatternFill(fill_type=None, start_color='FFFFFFFF', end_color='FF000000') border = Border(left=Side(border_style=None, color='FF000000'), right=Side(border_style=None, color='FF000000'), top=Side(border_style=None, color='FF000000'), bottom=Side(border_style=None, color='FF000000'), diagonal=Side(border_style=None, color='FF000000'), diagonal_direction=0, outline=Side(border_style=None, color='FF000000'), vertical=Side(border_style=None, color='FF000000'), horizontal=Side(border_style=None, color='FF000000')) alignment = Alignment(horizontal='general', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0)
def make_excel(list, sheetname, office): import openpyxl, datetime from openpyxl.styles import Alignment, Border, Side now = datetime.datetime.now() wb = openpyxl.Workbook() sheet = wb.active sheet.title = sheetname sh = wb[sheetname] sh.column_dimensions['A'].width = 60 sh.column_dimensions['B'].width = 40 sh.column_dimensions['C'].width = 30 sh.column_dimensions['I'].width = 15 sh.column_dimensions['F'].width = 15 sh.column_dimensions['K'].width = 15 columns = [ "物件url", "物件名", "住所", "家賃", "管理費", "月々支払い", "敷金", "礼金", "仲介手数料", "面積", "1m2の値段", "初期費用", "距離" ] sh.append(columns) for cells in sh['1']: cells.alignment = Alignment(horizontal='center') cells.border = Border(outline=True, bottom=Side(style="thick", color="FF000000")) for i in list: sh.append(i) for cells in sh['A']: currow = cells.row if cells.row != 1: initialcost = sh.cell(row=currow, column=6).value * 2 + sh.cell( row=currow, column=5).value + sh.cell( row=currow, column=6).value + sh.cell(row=currow, column=7).value sh.cell(row=currow, column=12).value = initialcost #get the distance from website import urllib.parse from bs4 import BeautifulSoup from selenium import webdriver import time driver = webdriver.PhantomJS() for loc in sh['C']: if loc.value != "住所": locinfo = urllib.parse.quote_plus(loc.value) base = f"http://r1web.realwork.jp/index_ex.html?{urllib.parse.quote_plus(f'{office}',encoding='utf-8')}&{locinfo}" driver.get(base) time.sleep(2) data = driver.page_source.encode('utf-8') soup = BeautifulSoup(data, "lxml") div = soup.find_all("td", {'id': 'meter_r'}) div = str(div) div = div.replace( '[<td id="meter_r" style="width: 50mm;border-style: solid;border-color: #E0E0E0">', '') div = div.replace('</td>]', '') print(div) currow = loc.row sh.cell(row=currow, column=13).value = div wb.save(f"media/{sheetname}_{now}.xlsx")
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] 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 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 filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def get (self, request, *args, **kwargs): print(request.GET.get('campo')) fecha_sel = request.GET.get('campo') #fecha_sel_parse = parse(fecha_sel) print('fecha ',fecha_sel_parse.date()) query = TiempoMuertDet.objects.all() query2 = TiempoMuertoEnc.objects.filter(id=tiempo_muerto) wb = Workbook() ws = wb.active ws.tittle='Tiempo Muerto' #Establer el nombre del archivo #nombre_archivo = "Reporte Tiempo Muerto" + fecha_sel_parse.date())+ ".xlsx" #ws['B1'].alignment= Alignment(horizontal='left', vertical='center') ws['B1'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B1'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B1'].font = Font(name='calibri', size=12, bold=True) ws['B1']='Mar Bran S.A. de C.V.' ws.merge_cells('B1:F1') ws['B2'].alignment= Alignment(horizontal='left', vertical='center') ws['B2'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B2'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B2'].font = Font(name='calibri', size=12, bold=True) ws['B2']='Innovación, Mejora Continua y Six Sigma' ws.merge_cells('B2:F2') ws['B3'].alignment= Alignment(horizontal='left', vertical='center') ws['B3'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B3'].font = Font(name='calibri', size=12, bold=True) ws['B3']='Reporte de Tiempos Muertos' ws.merge_cells('B3:F3') ws.row_dimensions[1].height=20 ws.row_dimensions[2].height=20 ws.row_dimensions[3].height=20 ws.column_dimensions['B'].width=20 ws.column_dimensions['C'].width=20 ws.column_dimensions['D'].width=20 ws.column_dimensions['E'].width=20 ws['B6'].alignment= Alignment(horizontal='center', vertical='center') ws['B6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['B6'].font = Font(name='calibri', size=11, bold=True) ws['B6']='Fecha de producciòn' ws['C6'].alignment= Alignment(horizontal='center', vertical='center') ws['C6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['C6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['C6'].font = Font(name='calibri', size=11, bold=True) ws['C6']='Turno' controlador = 7 for q in query: ws.cell(row=controlador,column=2).alignment= Alignment(horizontal='center', vertical='center') ws.cell(row=controlador,column=2).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador,column=2).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador,column=2).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador,column=2).value=q.fecha_produccion ws.cell(row=controlador,column=3).alignment= Alignment(horizontal='center', vertical='center') ws.cell(row=controlador,column=3).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador,column=3).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador,column=3).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador,column=3).value=q.tipo_cambio #contador+=1 controlador +=1 #Definir el tipo de resupuesta a dar response = HttpResponse(content_type='application/ms-excel') contenido = "attachment; filename = {0}".format(nombre_archivo) response["Content-Disposition"] = contenido wb.save(response) return response
def normal_datas_to_excel(normal_data, platform_datas, start, end): wb = Workbook() ws = wb.active ws.title = "平台活动报表" ws.append(["平台活动报告"] * 4) ws.append(['']) ws.merge_cells("A1:D2") for row in range(1, ws.max_row + 1): for col in range(1, ws.max_column + 1): ws.cell(row=row, column=col).fill = PatternFill(patternType="solid", fgColor="56237f") ws.cell(row=row, column=col).font = Font(name="黑体", size=16, color=colors.WHITE) ws.cell(row=row, column=col).alignment = Alignment(horizontal="center", vertical="center") ws.append([''] * 4) ws.append([''] * 4) ws.append(['日期', start + ' - ' + end]) ws.merge_cells("B5:D5") ws.append(['活动名', normal_data['activityName']]) ws.merge_cells("B6:D6") for row in range(5, 7): for col in range(2, 5): ws.cell(row=row, column=col).alignment = Alignment(horizontal="center", vertical="center") ws.append(['已使用', normal_data['used'], '未使用', normal_data['remaining']]) ws.append(['参与人数', normal_data['usedPeopleNum'], '补贴金额', '']) # ws.merge_cells("B8:D8") # for col in range(2, 5): # ws.cell(row=ws.max_row, column=col).alignment = Alignment( # horizontal="center", vertical="center") platform_datas_to_excel(ws, platform_datas) left, right, top, bottom = [Side(style='thin', color='000000')] * 4 for row in range(5, 9): for col in range(1, 5): ws.cell(row=row, column=col).font = Font(name='DengXian') ws.cell(row=row, column=col).border = Border(left=left, right=right, top=top, bottom=bottom) for row in range(15, ws.max_row + 1): for col in range(1, ws.max_column + 1): ws.cell(row=15, column=col).fill = PatternFill("solid", fgColor="deebf7") ws.cell(row=row, column=col).font = Font(name='DengXian') ws.cell(row=row, column=col).border = Border(left=left, right=right, top=top, bottom=bottom) wb.save('/home/fanjindong/notebook/报表/{}-{}-{}_{}.xlsx'.format( normal_data['appCode'], normal_data['activityId'], start, end))
def _insert_compare_string_results(string_results, ws, row_height): """ Insert string result into a worksheet Parameters ---------- string_results: dict result to insert ws: Excel worksheet instance row_height: float Height of the row """ # construct thick border thin = Side(border_style="thin", color="000000") border = Border(top=thin, left=thin, right=thin, bottom=thin) row_heights = {} # loop and output result for result in string_results: column = result['column'] if not 'result_df' in result.keys(): ws.append([column, result['error_msg']]) for col in ['A', 'B']: ws['%s%d' % (col, ws.max_row)].style = 'Bad' ws.append(['']) continue result_df = result['result_df'][0][['feature', 'value', 'graph']] value_counts_df = result['result_df'][1] head_row = _insert_df(result_df, ws) # if there is value counts result if len(value_counts_df) > 0: value_counts_df = value_counts_df.rename( columns={ 'value': 'top 10 values', 'count_x': 'count_1', 'count_y': 'count_2' }) databar_head = _insert_df(value_counts_df, ws, header=True, head_style='60 % - Accent5') for row_idx in range(databar_head, databar_head + value_counts_df.shape[0] + 1): row_heights[row_idx] = 25 # add conditional formatting: data bar first = FormatObject(type='num', val=0) second = FormatObject(type='num', val=np.max([ value_counts_df['count_1'].max(), value_counts_df['count_2'].max() ])) data_bar1 = DataBar(cfvo=[first, second], color=TABLE1_DARK.replace('#', ''), showValue=True, minLength=None, maxLength=None) data_bar2 = DataBar(cfvo=[first, second], color=TABLE2_DARK.replace('#', ''), showValue=True, minLength=None, maxLength=None) # assign the data bar to a rule rule1 = Rule(type='dataBar', dataBar=data_bar1) ws.conditional_formatting.add( 'B%d:B%d' % (databar_head + 1, databar_head + len(value_counts_df)), rule1) rule2 = Rule(type='dataBar', dataBar=data_bar2) ws.conditional_formatting.add( 'C%d:C%d' % (databar_head + 1, databar_head + len(value_counts_df)), rule2) # draw the thick outline border _style_range(ws, 'A%d:C%d' % (head_row, databar_head + len(value_counts_df)), border=border) else: _style_range(ws, 'A%d:C%d' % (head_row, head_row + result_df.shape[0] - 1), border=border) # add gap ws.append(['']) _adjust_ws(ws, row_height=row_height, row_heights=row_heights, adjust_type='str')
def create_left(self, path_to_sample_data, palette): sheet = self.workbook.active # read groups_schedules to get indices df = pd.read_csv((cwd / path_to_sample_data).resolve(), index_col=0) # more options can be specified also # with pd.option_context('display.max_rows', None, 'display.max_columns', None): # print(df) days = [df.columns[i] for i in range(0, len(df.columns), 3)] time_slots = df.index # vertical offset offset = 3 # height of a slot in rows time_slot_height = self.time_slot_height # number of time_slots slots_number = len(time_slots) # one row for name of day and rows for time_slots day_height = 1 + slots_number * time_slot_height week = len(days) # for each day for day_index in range(week): # starting on this row day_row = offset + day_index * day_height # set name of day sheet[f'A{day_row}'] = days[day_index] # add border side_style = Side(style='thin') self.add_border(min_row=day_row, min_column=1, max_row=day_row, max_column=1) # paint day cell sheet.cell(day_row, 1).fill = PatternFill("solid", fgColor=palette['day']) # for each time slot for time_slot_index in range(slots_number): time_slot_row = day_row + 1 + time_slot_index * 3 sheet.merge_cells(f'A{time_slot_row}:A{time_slot_row + 2}') sheet.row_dimensions[time_slot_row].height = 40 sheet.row_dimensions[time_slot_row + 1].height = 40 sheet.row_dimensions[time_slot_row + 2].height = 20 sheet[f'A{time_slot_row}'] = time_slots[time_slot_index] sheet[f'A{time_slot_row}'].alignment = Alignment( vertical="top") sheet[f'A{time_slot_row}'].border = Border(left=side_style, top=side_style, right=side_style, bottom=side_style)
""" get lotno """ data = [] # get file data with open(file, encoding='unicode_escape') as f: csvReader = reader((line.replace('\0', '') for line in f)) for row in csvReader: data.append(row) # get lotno lotno = data[5][1] return lotno alignment = Alignment(horizontal='center', vertical='center') thin = Side(border_style='thin', color=BLACK) border = Border(top=thin, left=thin, right=thin, bottom=thin) rowOffset = 5 project = 'Unknown' totalLotCount = 0 nowTime = 'Unknown' HWBIN_TO_SWBIN = { 'F28': { 3: { 'SWBin': (1, 2), 'isPassBin': True }, 1: { 'SWBin': (37, 38, 61, 62, 64, 65, 66, 90, 91, 92, 94),
import urllib.request import time from bs4 import BeautifulSoup from selenium import webdriver from openpyxl import Workbook from openpyxl import load_workbook from openpyxl.styles import PatternFill, Alignment, Font, Border, Side, NamedStyle, colors import re numregex = re.compile('\d+') ### font = Font(color=colors.BLACK) font2 = Font(color='578fcc') font3 = Font(color="ff0000") fill = PatternFill("solid", fgColor='FF839ce3') ali = Alignment(horizontal='center', vertical='center', shrinkToFit=True) thin = Side(border_style="thin", color="000000") border = Border(top=thin, left=thin, right=thin, bottom=thin) ### def style_range(ws, cell_range, border=Border(), fill=None, font=None, alignment=None): top = Border(top=border.top) left = Border(left=border.left) right = Border(right=border.right) bottom = Border(bottom=border.bottom) rows = ws[cell_range]