def group(self, start, end=None, outline_level=1, hidden=False): """allow grouping a range of consecutive rows or columns together :param start: first row or column to be grouped (mandatory) :param end: last row or column to be grouped (optional, default to start) :param outline_level: outline level :param hidden: should the group be hidden on workbook open or not """ if end is None: end = start if isinstance(self.default_factory(), ColumnDimension): new_dim = self[start] new_dim.outline_level = outline_level new_dim.hidden = hidden work_sequence = get_column_interval(start, end)[1:] for column_letter in work_sequence: if column_letter in self: del self[column_letter] new_dim.min, new_dim.max = map(column_index_from_string, (start, end)) elif isinstance(self.default_factory(), RowDimension): for el in range(start, end + 1): new_dim = self.worksheet.row_dimensions[el] new_dim.outline_level = outline_level new_dim.hidden = hidden
def load_workbook_range(range_string, ws): """ Select worksheet range and return as pandas dataframe """ col_start, col_end = re.findall("[A-Z]+", range_string) data_rows = [] for row in ws[range_string]: data_rows.append([cell.value for cell in row]) return pd.DataFrame(data_rows, columns=get_column_interval(col_start, col_end))
def load_workbook_range(self, range_string, ws): col_start, col_end = re.findall("[A-Z]+", range_string) data_rows = [] for row in ws[range_string]: data_rows.append([cell.value for cell in row]) return pd.DataFrame(data_rows, columns=get_column_interval(col_start, col_end))
def _filling_page3(self): xml_obj = xr.XMLReader(self.path_xml[3]) product_names = xml_obj.get_list_of_dicts() ls_product_names = [] idx_name_product = 1 align = Alignment(horizontal="left", vertical="center", wrapText = True) for array in product_names: ls_product_names.append(array['attrib']['fullname']) self._ws_page3.cell(row = 8 + idx_name_product, column = 1, value = idx_name_product).alignment = self.al self._ws_page3.cell(row = 8 + idx_name_product, column = 2, value = ls_product_names[-1]).alignment = align idx_name_product += 1 dict_price = self._data_page3_to_dict(ls_product_names) dt = self.report_date.split('T') date_arr = dt[0].split('-') self._ws_page3["B1"] = "Отчет сгенерирован Функциональной подсистемой «Управление сельского хозяйства» акимата города Астаны" self._ws_page3["G1"] = "Дата выдачи: " + ".".join(str(el) for el in self.sec_since_epoch) self._ws_page3["B3"] = "Ежедневный отчет о мониторинге цен на социально-значимые продовольственные товары" self._ws_page3["B4"] = "Отчетный период за " + format_date(date(int(date_arr[0]), int(date_arr[1]), int(date_arr[2])), format = 'long', locale = 'ru_RU') frs_date = self.first_sunday.split('T') rep_date = self.report_date.split('T') self._ws_page3["A7"] = "№" self._ws_page3["B7"] = "Наименование социально-значимых продовольственных товаров" self._ws_page3["C7"] = "Цена на " + '.'.join(frs_date[0].split('-')[::-1]) self._ws_page3["D7"] = "Цена на " + '.'.join(rep_date[0].split('-')[::-1]) self._ws_page3["E7"] = "Отклонение в % в динамике с " + '.'.join(frs_date[0].split('-')[::-1]) self._ws_page3["F7"] = "Пороговые значения розничных цен на соц. значимые продовольственные товары на " + self._get_start_quarter(date_arr) + " (утверждены приказом МНЭ)" self._ws_page3["G7"] = "Отклонение цены в сравнении с пороговой ценой в %" idx = 1 for col_letter in utils.get_column_interval(1, 7): self._ws_page3[col_letter + '8'] = idx idx += 1 for row_number in range(len(ls_product_names) + 2): self._ws_page3[col_letter + str(7 + row_number)].border = self.border self._ws_page3[col_letter + str(7 + row_number)].font = self.font if col_letter == 'B' and 9 <= 7 + row_number: self._ws_page3[col_letter + str(7 + row_number)].alignment = align else: self._ws_page3[col_letter + str(7 + row_number)].alignment = self.al self._filling_value_page3(dict_price, ls_product_names)
def dimension_format(worksheet, dimension='columns'): """ 行列格式化 :param worksheet: :param dimension: :return: """ if dimension == 'columns': for column_index in get_column_interval(worksheet.min_column, worksheet.max_column): worksheet.column_dimensions[column_index].auto_size = True worksheet.column_dimensions[column_index].best_fit = True elif dimension == 'rows': for row_index in range(worksheet.min_row, worksheet.max_row): pass
def _create_table(self, dict_markets): self._ws_page1.cell(row = 9, column = 1, value = 1) self._ws_page1.cell(row = 9, column = 2, value = 2) product_names = self._reading_file(self.path_xml[2]) ls_product_names = [] idx_name_product = 1 for array in product_names: ls_product_names.append(array['attrib']['fullname']) self._ws_page1.cell(row = 9 + idx_name_product, column = 1, value = idx_name_product) self._ws_page1.cell(row = 9 + idx_name_product, column = 2, value = ls_product_names[-1]) idx_name_product += 1 self._ws_page1.cell(row = self._START_ROW_TABLE, column = self._START_COL_TABLE, value = "№") self._ws_page1.cell(row = self._START_ROW_TABLE, column = self._START_COL_TABLE + 1, value = "Наименование социально-значимых продовольственных товаров") for index_market, name_market in enumerate(dict_markets): self._ws_page1.merge_cells(start_row = 7, start_column = index_market * 3 + 3, end_row = 7, end_column = index_market * 3 + 5) #name market self._ws_page1.cell(row = 7, column = index_market * 3 + 3, value = name_market).border = self.border #name min self._ws_page1.cell(row = 8, column = index_market * 3 + 3, value = 'min') self._ws_page1.cell(row = 9, column = index_market * 3 + 3, value = index_market * 3 + 3) #name max self._ws_page1.cell(row = 8, column = index_market * 3 + 4, value = 'max') self._ws_page1.cell(row = 9, column = index_market * 3 + 4, value = index_market * 3 + 4) #name avr_sum self._ws_page1.cell(row = 8, column = index_market * 3 + 5, value = 'Ср.цена') self._ws_page1.cell(row = 9, column = index_market * 3 + 5, value = index_market * 3 + 5) self._filling_value_page1(dict_markets[name_market], index_market * 3 + 3, ls_product_names) for col_letter in utils.get_column_interval(1, len(dict_markets) * 3 + 2): for row_number in range(len(ls_product_names) + 3): self._ws_page1[col_letter + str(7 + row_number)].font = self.font self._ws_page1[col_letter + str(7 + row_number)].border = self.border if col_letter == 'B' and 10 <= 7 + row_number: self._ws_page1[col_letter + str(7 + row_number)].alignment = self.al_left else: self._ws_page1[col_letter + str(7 + row_number)].alignment = self.al_center
def group(self, start, end=None, outline_level=1, hidden=False): """allow grouping a range of consecutive columns together :param start: first column to be grouped (mandatory) :param end: last column to be grouped (optional, default to start) :param outline_level: outline level :param hidden: should the group be hidden on workbook open or not """ if end is None: end = start new_dim = self[start] new_dim.outline_level = outline_level new_dim.hidden = hidden work_sequence = get_column_interval(start, end)[1:] for column_letter in work_sequence: if column_letter in self: del self[column_letter] new_dim.min, new_dim.max = map(column_index_from_string, (start, end))
def make_cover_sheet(spec_json, ws): FontS1 = Font(name='MS ゴシック'.decode("utf8"), size=36, bold=True) FontS2 = Font(name='MS ゴシック'.decode("utf8"), size=16, bold=True) AlignS1 = Alignment(horizontal='center', vertical='center') ws.title = "表紙".decode("utf8") ws.sheet_view.showGridLines = False for col_letter in get_column_interval(1, 10000): ws.column_dimensions[col_letter].width = 2.7 start_index = 16 ws.merge_cells(start_row=start_index, start_column=2, end_row=start_index + 2, end_column=31) cell = ws.cell(column=2, row=start_index) cell.value = spec_json["chapterInfo"] cell.font = FontS1 cell.alignment = AlignS1 start_index += 4 ws.merge_cells(start_row=start_index, start_column=2, end_row=start_index + 2, end_column=31) cell = ws.cell(column=2, row=start_index) cell.value = spec_json["titleInfo"] cell.font = FontS1 cell.alignment = AlignS1 start_index += 4 ws.merge_cells(start_row=start_index, start_column=2, end_row=start_index + 2, end_column=31) cell = ws.cell(column=2, row=start_index) cell.value = spec_json["verInfo"] cell.font = FontS2 cell.alignment = AlignS1
def _child_line(self, sheet, group, across): """ StructureChef._child_line() -> None Draws lines from parent to children. """ # where the parent cell ends and the connection to children begins center = group.extra['center'] tip = across.number() + self.BOX_ACROSS # stem from parent to children side = Side(border_style='thick') upper = sheet.cell(row=center, column=tip) upper.border = Border(top=side) under = sorted(g.extra['center'] for g in group.groups) # stems from children up to parent for row in under: c = sheet.cell(row=row, column=tip + 1) border = c.border.copy() border.top = side c.border = border # branch on which children hang if len(under) > 1: top = min(under) end = max(under) for row in range(top, end): c = sheet.cell(row=row, column=tip + 1) border = c.border.copy() border.left = side c.border = border # col sizing for c in get_column_interval(tip, tip + 1): sheet.column_dimensions[c].width = self.SEP_ACROSS
def make_cat_sheet(spec_json, wb): FontS1 = Font(name='MS ゴシック'.decode("utf8"), size=10.5, bold=False) FontS2 = Font(name='MS ゴシック'.decode("utf8"), size=10.5, bold=True) FontS3 = Font(name='MS ゴシック'.decode("utf8"), size=10.5, bold=False, color=BLUE) Borderthin = Side(border_style="thin", color="000000") ws = wb.create_sheet("目次".decode("utf8"), 1) ws.sheet_view.showGridLines = False for col_letter in get_column_interval(1, 10000): ws.column_dimensions[col_letter].width = 2.7 start_index = 7 ws.merge_cells(start_row=start_index, start_column=2, end_row=start_index, end_column=5) ws.merge_cells(start_row=start_index, start_column=6, end_row=start_index, end_column=11) ws.merge_cells(start_row=start_index, start_column=12, end_row=start_index, end_column=17) ws.merge_cells(start_row=start_index, start_column=18, end_row=start_index, end_column=29) for iborder in range(2, 30): cell = ws.cell(column=iborder, row=start_index) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) cell = ws.cell(column=2, row=start_index) cell.value = "項番".decode("utf8") cell.font = FontS2 cell = ws.cell(column=6, row=start_index) cell.value = "大項目".decode("utf8") cell.font = FontS2 cell = ws.cell(column=12, row=start_index) cell.value = "中項目".decode("utf8") cell.font = FontS2 cell = ws.cell(column=18, row=start_index) cell.value = "小項目".decode("utf8") cell.font = FontS2 start_index += 1 ws.merge_cells(start_row=start_index, start_column=2, end_row=start_index, end_column=5) ws.merge_cells(start_row=start_index, start_column=6, end_row=start_index, end_column=29) for iborder in range(2, 30): cell = ws.cell(column=iborder, row=start_index) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) cell = ws.cell(column=2, row=start_index) cell.value = "2.3." cell.font = FontS1 cell = ws.cell(column=6, row=start_index) cell.value = "概要".decode("utf8") cell.font = FontS3 cell.hyperlink = "#2.3.概要!B2".decode("utf8") start_index += 1 ws.merge_cells(start_row=start_index, start_column=2, end_row=start_index, end_column=5) ws.merge_cells(start_row=start_index, start_column=6, end_row=start_index, end_column=29) for iborder in range(2, 30): cell = ws.cell(column=iborder, row=start_index) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) cell = ws.cell(column=2, row=start_index) cell.value = "2.3.0." cell.font = FontS1 cell = ws.cell(column=6, row=start_index) cell.value = "用語定義".decode("utf8") cell.font = FontS3 cell.hyperlink = "#2.3.0.用語定義!B2".decode("utf8") start_index += 1 ws.merge_cells(start_row=start_index, start_column=2, end_row=start_index, end_column=5) ws.merge_cells(start_row=start_index, start_column=6, end_row=start_index, end_column=29) for iborder in range(2, 30): cell = ws.cell(column=iborder, row=start_index) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) cell = ws.cell(column=2, row=start_index) cell.value = "2.3.1." cell.font = FontS1 cell = ws.cell(column=6, row=start_index) cell.value = "案内使用言語".decode("utf8") cell.font = FontS3 cell.hyperlink = "#2.3.1.案内使用言語!B2".decode("utf8") for iborder in range(2, 12): cell = ws.cell(column=iborder, row=start_index) cell.border = Border(left=Borderthin, right=Borderthin, top=Borderthin) start_index += 1 ws.merge_cells(start_row=start_index, start_column=2, end_row=start_index, end_column=5) ws.merge_cells(start_row=start_index, start_column=6, end_row=start_index, end_column=11) ws.merge_cells(start_row=start_index, start_column=12, end_row=start_index, end_column=29) for iborder in range(2, 30): cell = ws.cell(column=iborder, row=start_index) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) cell = ws.cell(column=2, row=start_index) cell.value = "2.3.1.1." cell.font = FontS1 cell = ws.cell(column=12, row=start_index) cell.value = "案内音声言語".decode("utf8") cell.font = FontS3 cell.hyperlink = "#2.3.1.1.案内音声言語!B2".decode("utf8") for iborder in range(2, 12): cell = ws.cell(column=iborder, row=start_index) cell.border = Border(left=Borderthin, right=Borderthin, bottom=Borderthin)
def _filling_page2(self, data_price): dt = self.date.split('T') date_arr = dt[0].split('-') sec_since_epoch = list(time.localtime())[:3][::-1] self._ws_page2[ "B1"] = "Отчет сгенерирован Функциональной подсистемой «Управление сельского хозяйства» акимата города Астаны" self._ws_page2["G1"] = "Дата выдачи: " + ".".join( str(el) for el in self.sec_since_epoch) self._ws_page2[ "B3"] = "Еженедельный отчет о мониторинге цен на социально-значимые продовольственные товары" self._ws_page2["B4"] = "отчетный период за " + format_date( date(int(date_arr[0]), int(date_arr[1]), int(date_arr[2])), format='long', locale='ru_RU') dt_beg = list( time.gmtime( time.mktime((int(date_arr[0]), int(date_arr[1]), int(date_arr[2]), 0, 0, 0, 0, 0, 0)) - 6 * 24 * 60 * 60))[:3] dt_beg = '.'.join(str(el) for el in dt_beg[::-1]) dt_end = '.'.join(str(el) for el in date_arr[::-1]) self._ws_page2["A7"] = "№" self._ws_page2[ "B7"] = "Наименование социально-значимых продовольственных товаров" self._ws_page2["C7"] = "Цена за " + dt_beg self._ws_page2["D7"] = "Цена за " + dt_end self._ws_page2["E7"] = "сравнение в %, " + dt_beg + " с " + dt_end product_names = ls_price_last_week(self.path_xml[2]) ls_product_names = [] idx_name_product = 1 for array in product_names: ls_product_names.append(array['attrib']['fullname']) self._ws_page2.cell(row=8 + idx_name_product, column=1, value=idx_name_product) self._ws_page2.cell(row=8 + idx_name_product, column=2, value=ls_product_names[-1]) idx_name_product += 1 idx = 1 al_left = Alignment(horizontal="left", vertical="center", wrapText=True) for col_letter in utils.get_column_interval(1, 5): self._ws_page2[col_letter + '8'] = idx idx += 1 for row_number in range(len(ls_product_names) + 2): self._ws_page2[col_letter + str(7 + row_number)].border = self.border self._ws_page2[col_letter + str(7 + row_number)].font = self.font if col_letter == 'B' and 9 <= 7 + row_number: self._ws_page2[col_letter + str(7 + row_number)].alignment = self.al_left else: self._ws_page2[col_letter + str(7 + row_number)].alignment = self.al_center self._filling_value_page2(data_price, ls_product_names)
def set_col_map(self): field_list = list(self.get_export_order()) column_list = get_column_interval(1, len(field_list)) col_map = dict(zip(field_list, column_list)) self.resource_map[self.sheet_title]['col_map'] = col_map
def _filling_page1(self, dict_markets): dt = self.report_date.split('T') dt = dt[0].split('-') self._ws_page1["B4"] = "отчетный период за " + format_date( date(int(dt[0]), int(dt[1]), int(dt[2])), format='long', locale='ru_RU') self._ws_page1[ "B1"] = "Отчет сгенерирован Функциональной подсистемой «Управление сельского хозяйства» акимата города Астаны" self._ws_page1[ "B3"] = "Отчет о мониторинге цен на социально-значимые продовольственные товары в разрезе торговых объектов" self._ws_page1["G1"] = "Дата выдачи: " + ".".join( str(el) for el in self.sec_since_epoch) self._ws_page1["A7"] = "№" product_names = self._reading_file(self.path_xml[2]) self._ws_page1.cell(row=9, column=1, value=1) self._ws_page1.cell(row=9, column=2, value=2) ls_product_names = [] idx_name_product = 1 align = Alignment(horizontal="left", vertical="center", wrapText=True) for array in product_names: ls_product_names.append(array['attrib']['fullname']) self._ws_page1.cell(row=9 + idx_name_product, column=1, value=idx_name_product) self._ws_page1.cell(row=9 + idx_name_product, column=2, value=ls_product_names[-1]) idx_name_product += 1 for i_col in range(len(dict_markets)): self._ws_page1.merge_cells(start_row=7, start_column=i_col * 3 + 3, end_row=7, end_column=i_col * 3 + 5) i_col = 0 for name_market in dict_markets: #name market self._ws_page1.cell(row=7, column=i_col * 3 + 3, value=name_market).border = self.border #name min self._ws_page1.cell(row=8, column=i_col * 3 + 3, value='min') self._ws_page1.cell(row=9, column=i_col * 3 + 3, value=i_col * 3 + 3) #name max self._ws_page1.cell(row=8, column=i_col * 3 + 4, value='max') self._ws_page1.cell(row=9, column=i_col * 3 + 4, value=i_col * 3 + 4) #name avr_sum self._ws_page1.cell(row=8, column=i_col * 3 + 5, value='Ср.цена') self._ws_page1.cell(row=9, column=i_col * 3 + 5, value=i_col * 3 + 5) self._filling_value_page1(dict_markets[name_market], i_col * 3 + 3, ls_product_names) i_col += 1 for col_letter in utils.get_column_interval(1, len(dict_markets) * 3 + 2): for row_number in range(len(ls_product_names) + 3): self._ws_page1[col_letter + str(7 + row_number)].font = self.font self._ws_page1[col_letter + str(7 + row_number)].border = self.border if col_letter == 'B' and 10 <= 7 + row_number: self._ws_page1[col_letter + str(7 + row_number)].alignment = align else: self._ws_page1[col_letter + str(7 + row_number)].alignment = self.al
def getMaxColRange(ws, startCol, fil): return get_column_interval(startCol, getMaxCol(ws, startCol, fil))
def make_history_sheet(spec_json, wb): FontS1 = Font(name='MS ゴシック'.decode("utf8"), size=10.5, bold=False) Borderthin = Side(border_style="thin", color="000000") if not "history" in spec_json.keys(): return ws = wb.create_sheet("更新履歴".decode("utf8")) ws.sheet_view.showGridLines = False for col_letter in get_column_interval(1, 10000): ws.column_dimensions[col_letter].width = 2.7 s_row = 2 ws.merge_cells(start_row=s_row, start_column=2, end_row=s_row, end_column=3) ws.merge_cells(start_row=s_row, start_column=4, end_row=s_row, end_column=10) ws.merge_cells(start_row=s_row, start_column=11, end_row=s_row, end_column=23) ws.merge_cells(start_row=s_row, start_column=24, end_row=s_row, end_column=36) ws.merge_cells(start_row=s_row, start_column=37, end_row=s_row, end_column=41) for iBorder in range(2, 42): cell = ws.cell(column=iBorder, row=s_row) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) cell = ws.cell(column=2, row=s_row) cell.value = "No." cell.font = FontS1 cell = ws.cell(column=4, row=s_row) cell.value = "項番".decode("utf8") cell.font = FontS1 cell = ws.cell(column=11, row=s_row) cell.value = "更新前内容".decode("utf8") cell.font = FontS1 cell = ws.cell(column=24, row=s_row) cell.value = "更新後内容".decode("utf8") cell.font = FontS1 cell = ws.cell(column=37, row=s_row) cell.value = "更新日".decode("utf8") cell.font = FontS1 s_row += 1 for i_index, history_data in enumerate(spec_json["history"], 1): ws.merge_cells(start_row=s_row, start_column=2, end_row=s_row, end_column=3) ws.merge_cells(start_row=s_row, start_column=4, end_row=s_row, end_column=10) ws.merge_cells(start_row=s_row, start_column=11, end_row=s_row, end_column=23) ws.merge_cells(start_row=s_row, start_column=24, end_row=s_row, end_column=36) ws.merge_cells(start_row=s_row, start_column=37, end_row=s_row, end_column=41) for iBorder in range(2, 42): cell = ws.cell(column=iBorder, row=s_row) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) cell = ws.cell(column=2, row=s_row) cell.value = "%d" % i_index cell.font = FontS1 cell = ws.cell(column=4, row=s_row) cell.value = history_data["chapterInfo"] cell.font = FontS1 cell = ws.cell(column=11, row=s_row) old_str = "" for o_k, o_v in history_data["old_content"].iteritems(): old_str += o_v[0]["dataValue"][0] + "\n" cell.value = old_str cell.font = FontS1 cell = ws.cell(column=24, row=s_row) new_str = "" for n_k, n_v in history_data["new_content"].iteritems(): new_str += n_v[0]["dataValue"][0] + "\n" cell.value = new_str cell.font = FontS1 cell = ws.cell(column=37, row=s_row) cell.value = history_data["modify_date"] cell.font = FontS1 s_row += 1
def _create_table(self, data_price, dt): dt_beg = list( time.gmtime( time.mktime((int(dt[0]), int(dt[1]), int(dt[2]), 0, 0, 0, 0, 0, 0)) - 6 * 24 * 60 * 60))[:3] dt_beg = '.'.join(str(el) for el in dt_beg[::-1]) dt_end = '.'.join(str(el) for el in dt[::-1]) self._ws_page2.cell(row=self._START_ROW_TABLE, column=self._START_COL_TABLE, value="№") self._ws_page2.cell( row=self._START_ROW_TABLE, column=self._START_COL_TABLE + 1, value="Наименование социально-значимых продовольственных товаров") self._ws_page2.cell(row=self._START_ROW_TABLE, column=self._START_COL_TABLE + 2, value="Цена за " + dt_beg) self._ws_page2.cell(row=self._START_ROW_TABLE, column=self._START_COL_TABLE + 3, value="Цена за " + dt_end) self._ws_page2.cell(row=self._START_ROW_TABLE, column=self._START_COL_TABLE + 4, value="сравнение в %, " + dt_beg + " с " + dt_end) product_names = self._reading_file(self.path_xml[2]) ls_product_names = [] idx_name_product = 1 for array in product_names: ls_product_names.append(array['attrib']['fullname']) self._ws_page2.cell(row=8 + idx_name_product, column=1, value=idx_name_product) self._ws_page2.cell(row=8 + idx_name_product, column=2, value=ls_product_names[-1]) idx_name_product += 1 idx = 1 for col_letter in utils.get_column_interval(1, 5): self._ws_page2[col_letter + '8'] = idx idx += 1 for row_number in range(len(ls_product_names) + 2): self._ws_page2[col_letter + str(7 + row_number)].border = self.border self._ws_page2[col_letter + str(7 + row_number)].font = self.font if col_letter == 'B' and 9 <= 7 + row_number: self._ws_page2[col_letter + str(7 + row_number)].alignment = self.al_left else: self._ws_page2[col_letter + str(7 + row_number)].alignment = self.al_center self._filling_table(data_price, ls_product_names)
def make_func_sheet(spec_json, wb): FontS1 = Font(name='MS ゴシック'.decode("utf8"), size=10.5, bold=False) FontS2 = Font(name='MS ゴシック'.decode("utf8"), size=10.5, bold=True) Borderthin = Side(border_style="thin", color="000000") for fun_info in spec_json["chapterInfoList"]: title_info = fun_info["chapterInfo"] + fun_info["titleInfo"] ws = wb.create_sheet(title_info) ws.sheet_view.showGridLines = False for col_letter in get_column_interval(1, 10000): ws.column_dimensions[col_letter].width = 2.7 row_index = 2 col_index = 2 ws.merge_cells(start_row=row_index, start_column=col_index, end_row=row_index, end_column=col_index + 3) cell = ws.cell(column=col_index, row=row_index) cell.value = fun_info["chapterInfo"] cell.font = FontS2 for iborer in range(col_index, col_index + 4): cell = ws.cell(column=iborer, row=row_index) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) cell = ws.cell(column=col_index + 4, row=row_index) cell.value = fun_info["titleInfo"] cell.font = FontS2 row_index += 2 col_index += 1 ws.merge_cells(start_row=row_index, start_column=col_index, end_row=row_index, end_column=col_index + 1) cell = ws.cell(column=col_index, row=row_index) cell.value = "概要".decode("utf8") cell.font = FontS1 for iborer in range(col_index, col_index + 2): cell = ws.cell(column=iborer, row=row_index) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) for iRow in range(0, len(fun_info["summaryInfo"])): cell = ws.cell(column=col_index + 2, row=row_index) cell.value = fun_info["summaryInfo"][iRow]["dataValue"][0] cell.font = FontS1 row_index += 1 row_index += 1 ws.merge_cells(start_row=row_index, start_column=col_index, end_row=row_index, end_column=col_index + 1) cell = ws.cell(column=col_index, row=row_index) cell.value = "前提".decode("utf8") cell.font = FontS1 for iborer in range(col_index, col_index + 2): cell = ws.cell(column=iborer, row=row_index) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) for iRow in range(0, len(fun_info["preCondition"])): cell = ws.cell(column=col_index + 2, row=row_index) cell.value = fun_info["preCondition"][iRow]["dataValue"][0] cell.font = FontS1 row_index += 1 row_index += 1 ws.merge_cells(start_row=row_index, start_column=col_index, end_row=row_index, end_column=col_index + 1) cell = ws.cell(column=col_index, row=row_index) cell.value = "呼出".decode("utf8") cell.font = FontS1 for iborer in range(col_index, col_index + 2): cell = ws.cell(column=iborer, row=row_index) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) for iRow in range(0, len(fun_info["callInfo"])): cell = ws.cell(column=col_index + 2, row=row_index) cell.value = fun_info["callInfo"][iRow]["dataValue"][0] cell.font = FontS1 row_index += 1 row_index += 1 make_func_list(ws, fun_info["appRange"], row_index, col_index)
def make_def_sheet(spec_json, wb): FontS1 = Font(name='MS ゴシック'.decode("utf8"), size=10.5, bold=False) FontS2 = Font(name='MS ゴシック'.decode("utf8"), size=10.5, bold=True) Borderthin = Side(border_style="thin", color="000000") AlignS1 = Alignment(horizontal='left', vertical='top', wrap_text=True) ws = wb.create_sheet(spec_json["chapterInfo"][1:-1] + ".0." + "用語定義".decode("utf8")) ws.sheet_view.showGridLines = False for col_letter in get_column_interval(1, 10000): ws.column_dimensions[col_letter].width = 2.7 start_index = 2 ws.merge_cells(start_row=start_index, start_column=2, end_row=start_index, end_column=5) cell = ws.cell(column=2, row=start_index) cell.value = spec_json["chapterInfo"][1:-1] + ".0." cell.font = FontS2 for iborer in range(2, 6): cell = ws.cell(column=iborer, row=start_index) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin, bottom=Borderthin) cell = ws.cell(column=6, row=start_index) cell.value = "用語定義".decode("utf8") cell.font = FontS2 start_index += 2 for def_info in spec_json["nameDefList"]: nameDefList = def_info["nameDef"] nameInfo = def_info["nameInfo"] ws.merge_cells(start_row=start_index, start_column=4, end_row=start_index + len(nameDefList) - 1, end_column=8) for iRow in range(0, len(nameDefList)): ws.merge_cells(start_row=start_index + iRow, start_column=9, end_row=start_index + iRow, end_column=29) if iRow == 0: cell = ws.cell(column=4, row=start_index + iRow) cell.value = nameInfo["dataValue"][0] cell.alignment = AlignS1 cell = ws.cell(column=9, row=start_index + iRow) cell.value = nameDefList[iRow]["dataValue"][0] cell.alignment = AlignS1 else: cell = ws.cell(column=9, row=start_index + iRow) cell.value = nameDefList[iRow]["dataValue"][0] cell.alignment = AlignS1 for iborer in range(4, 30): iRow = 0 cell = ws.cell(column=iborer, row=start_index + iRow) cell.border = Border(top=Borderthin, left=Borderthin, right=Borderthin) cell.font = FontS1 iRow += 1 while (iRow < len(nameDefList)): cell = ws.cell(column=iborer, row=start_index + iRow) cell.border = Border(left=Borderthin, right=Borderthin) cell.font = FontS1 iRow += 1 cell = ws.cell(column=iborer, row=start_index + iRow - 1) cell.font = FontS1 if iRow > 1: cell.border = Border(bottom=Borderthin, left=Borderthin, right=Borderthin) else: cell.border = Border(top=Borderthin, bottom=Borderthin, left=Borderthin, right=Borderthin) start_index += len(nameDefList)