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 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(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 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='ＭＳ ゴシック'.decode("utf8"), size=36, bold=True)
FontS2 = Font(name='ＭＳ ゴシック'.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='ＭＳ ゴシック'.decode("utf8"), size=10.5, bold=False)
FontS2 = Font(name='ＭＳ ゴシック'.decode("utf8"), size=10.5, bold=True)
FontS3 = Font(name='ＭＳ ゴシック'.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='ＭＳ ゴシック'.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='ＭＳ ゴシック'.decode("utf8"), size=10.5, bold=False)
FontS2 = Font(name='ＭＳ ゴシック'.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='ＭＳ ゴシック'.decode("utf8"), size=10.5, bold=False)
FontS2 = Font(name='ＭＳ ゴシック'.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)
