def add_worksheet(self, name=None):
"""
Add a new worksheet to the Excel workbook.
Args:
name: The worksheet name. Defaults to 'Sheet1', etc.
Returns:
Reference to a worksheet object.
"""
sheet_index = len(self.worksheets_objs)
name = self._check_sheetname(name)
# Initialisation data to pass to the worksheet.
init_data = {
"name": name,
"index": sheet_index,
"str_table": self.str_table,
"worksheet_meta": self.worksheet_meta,
"optimization": self.optimization,
"tmpdir": self.tmpdir,
"date_1904": self.date_1904,
}
worksheet = Worksheet()
worksheet._initialize(init_data)
self.worksheets_objs.append(worksheet)
self.sheetnames.append(name)
return worksheet
def add_worksheet(self, name=None):
"""
Add a new worksheet to the Excel workbook.
Args:
name: The worksheet name. Defaults to 'Sheet1', etc.
Returns:
Reference to a worksheet object.
"""
sheet_index = len(self.worksheets_objs)
name = self._check_sheetname(name)
# Encode any string options passed by the user.
name = encode_utf8(name)
# Initialisation data to pass to the worksheet.
init_data = {
'name': name,
'index': sheet_index,
'str_table': self.str_table,
'worksheet_meta': self.worksheet_meta,
'optimization': self.optimization,
'tmpdir': self.tmpdir,
'date_1904': self.date_1904,
}
worksheet = Worksheet()
worksheet._initialize(init_data)
self.worksheets_objs.append(worksheet)
self.sheetnames.append(name)
return worksheet
def _write_excel_rows(rows: list, workbook: Workbook,
worksheet: Worksheet) -> int:
excel_longest_word = ''
for i, row in enumerate(rows, 1):
row_format = None
if i == 1:
row_format = workbook.add_format({
'bold': True,
'bg_color': '#cccccc',
})
elif i % 2 == 0:
row_format = workbook.add_format({
'bg_color': '#f1f1f1',
})
range_cells = 'A%s:I10' % i
row_longest_word = find_longest_word(row)
if len(row_longest_word) > len(excel_longest_word):
excel_longest_word = row_longest_word
worksheet.write_row(range_cells, row, row_format)
self.update_state(state='PROGRESS',
meta={
'current': i,
'total': self.total_progress,
'status': 'In progress...',
})
return len(excel_longest_word)
def adjust_column_and_row_sizes(worksheet: Worksheet, rows: List[Tuple[str,
...]],
num_columns: int) -> None:
"""
Rows height:
- Title row is thicker than data row
- blank separator row is thinner than data row
Columns width:
- Each column width is wider enough all the text in cells it contains
:param worksheet: worksheet we operate on
:param rows: list of tuples where each tuple represents a row in worksheet
:param num_columns: number of columns in worksheet
:return:
"""
num_rows = len(rows)
# adjust row sizes
column_max_sizes = [0] * num_columns
worksheet.set_row(0, 30)
for i in range(num_rows):
if rows[i][0] != " ":
worksheet.set_row(i + 1, 20)
else:
worksheet.set_row(i + 1, 10)
for j in range(num_columns):
column_max_sizes[j] = max(column_max_sizes[j], len(rows[i][j]) + 5)
# adjust column sizes
for j in range(num_columns):
worksheet.set_column(j, j, column_max_sizes[j])
worksheet.set_column(num_columns, num_columns, 20)
def set_column_autowidth(worksheet: Worksheet, column: int):
"""
Set the width automatically on a column in the `Worksheet`.
!!! Make sure you run this function AFTER having all cells filled in
the worksheet!
"""
maxwidth = get_column_width(worksheet=worksheet, column=column)
if maxwidth is None:
return
worksheet.set_column(first_col=column, last_col=column, width=maxwidth)
def size_cells(
ws: xlsxWorksheet,
col_widths: List[Tuple[int, int, int]],
row_heights: List[Tuple[int, int]],
) -> None:
for row, height in [(0, BORDER_CELLS_SPAN_CHAR_UNITS)] + row_heights:
ws.set_row_pixels(row, height=height)
for col_i, col_f, width in [(0, 0, BORDER_CELLS_SPAN_CHAR_UNITS)
] + col_widths:
ws.set_column_pixels(col_i, col_f, width=width)
def adjust_rows_height(cls,
df: pd.DataFrame,
wb: Workbook,
worksheet: Worksheet):
bold_fmt = wb.add_format({'bold': True})
worksheet.set_row(0, cell_format=bold_fmt)
for idx, row in df.iterrows():
height = max([1 + sum([1 for ch in str(c or '') if ch == '\n'])
for c in row])
height = min(height, cls.MAX_ROW_HEIGHT)
worksheet.set_row(idx + 1, height * worksheet.default_row_height)
def _set_columns_width(workbook: Workbook, worksheet: Worksheet, columns,
fields: List[str]):
cell_format_text_wrap = workbook.add_format()
cell_format_text_wrap.set_text_wrap()
for idx, field in enumerate(fields, start=0):
if columns[field][MAX_WIDTH_KEY] > MAX_WIDTH:
worksheet.set_column(idx, idx, MAX_WIDTH,
cell_format_text_wrap)
else:
worksheet.set_column(idx, idx, columns[field][MAX_WIDTH_KEY])
def write_conditional_blank_color_if_dependency_fails(
worksheet: Worksheet,
row_index: int,
column_index: int,
question: Dict[str, Any],
full_question_path: Tuple[str, ...],
question_to_cell: Dict[Tuple[str, ...], str],
bool_options: Dict[str, str],
black_format: Format,
) -> None:
index = -1
criterias = []
q = question
while "dependsOn" in q:
compare, value = q["dependsOn"]["compare"], q["dependsOn"]["value"]
new_question_path = full_question_path[:index]
dependent_cell_name = question_to_cell[(
new_question_path[0],
*list(map(lambda x: x[0], list(new_question_path[1:-1]))),
new_question_path[-1][1],
)]
comparison_value = (f'"{bool_options["Yes"]}"' if value is True else
'"{boolOptions["No"]}"' if value is False else
f"{value}" if isinstance(value, int)
or isinstance(value, float) else f'"{value}"')
opposite_compare = ("<>" if compare == "=" else "=" if compare == "<>"
else ">" if compare == "<=" else "<=" if compare ==
">" else ">=" if compare == "<" else "<")
criterias.append('AND(${0}{1}{2},${0}<>"")'.format(
dependent_cell_name, opposite_compare, comparison_value))
index = index - 1
q = q["dependsOn"]
if len(criterias) > 1:
final_criteria = "=OR("
for i, criteria in enumerate(criterias):
if i == 0:
final_criteria = final_criteria + criteria
else:
final_criteria = final_criteria + "," + criteria
final_criteria = final_criteria + ")"
else:
final_criteria = "=" + criterias[0]
worksheet.conditional_format(
row_index,
column_index,
row_index,
column_index,
{
"type": "formula",
"criteria": final_criteria,
"format": black_format
},
)
def MergeColumnCells(work_book: Workbook, work_sheet: Worksheet, row: int,
column_start: int, column_end: int, text: str) -> None:
# Output :- A1, B2, etc
column_row_lambda = lambda x: NumToExcelAlpha(x) + str(row + 1)
# Sample : 'A1:D1'
merge_range_string = ':'.join(
map(column_row_lambda, [column_start, column_end]))
work_sheet.merge_range(merge_range_string, text,
GetDefaultCellFormat(work_book))
def make_sheet_banded(worksheet: Worksheet, df: pd.DataFrame):
col_vals = df.columns.values
xl_cols_dict = [{'header': col} for col in df.columns.values]
last_col_letter = excel_columns()[len(col_vals) - 1]
xl_last_data_row = df.shape[0] + 1 # plus 1 is because data starts at row 2
table_style = {
'banded_rows': True,
'header_row': True,
'columns': xl_cols_dict,
'style': 'Table Style Light 16'
}
worksheet.add_table(f'A1:{last_col_letter}{xl_last_data_row}', table_style)
def adjust_columns_width(cls,
df: pd.DataFrame,
wb: Workbook,
worksheet: Worksheet):
# wrap_format = wb.add_format({'shrink': True})
for idx, col in enumerate(df):
series = df[col]
max_len = max((
series.astype(str).map(len).max(), # len of largest item
len(str(series.name)) # len of column name/header
)) + 1 # adding a little extra space
max_len = min(max_len, cls.MAX_COL_WIDTH)
worksheet.set_column(idx, idx, max_len)
def _add_sheet(self, name, is_chartsheet):
# Utility for shared code in add_worksheet() and add_chartsheet().
sheet_index = len(self.worksheets_objs)
name = self._check_sheetname(name, is_chartsheet)
# Initialisation data to pass to the worksheet.
init_data = {
'name': name,
'index': sheet_index,
'str_table': self.str_table,
'worksheet_meta': self.worksheet_meta,
'optimization': self.optimization,
'tmpdir': self.tmpdir,
'date_1904': self.date_1904,
'strings_to_numbers': self.strings_to_numbers,
'strings_to_formulas': self.strings_to_formulas,
'strings_to_urls': self.strings_to_urls,
'default_date_format': self.default_date_format,
'default_url_format': self.default_url_format,
'excel2003_style': self.excel2003_style,
}
if is_chartsheet:
worksheet = Chartsheet()
else:
worksheet = Worksheet()
worksheet._initialize(init_data)
self.worksheets_objs.append(worksheet)
self.sheetnames.append(name)
return worksheet
def DetailsWriter(work_book: Workbook, work_sheet: Worksheet,
excel_sheet_number: int, row: int,
details_list: list) -> int:
''' Function to write details under respective headers in the excel table
Returns a list of count of characters in every cells for the respective row
'''
# Use the required empty value
empty_cell_value = 'NIL'
format_dict = {
'align': 'center',
'valign': 'vcenter',
'fg_color': '#add8e6',
'border': 1
}
cell_format = work_book.add_format(format_dict)
column_width = []
for column, value in enumerate(details_list):
temp_cell_format = None
# Check :- is None / empty
if not value:
value, temp_cell_format = empty_cell_value, cell_format
# If value is a list
elif isinstance(value, list):
# If the type of list elements is non-object type
if len(value) == 0 or not isinstance(value[0], dict):
value = str(value)
# Type of list elements is object type
# TODO :: Nested List need to be handled i.e, [[1,2],[3,4]] , flattening the list might be applicable
else:
# Set the new Sheet name to the value
value = ExcelFileWriter(work_book, excel_sheet_number + 1,
value)
work_sheet.write(row, column, value)
column_width.append(len(str(value)))
return column_width
def MergeRowCells(work_book: Workbook, work_sheet: Worksheet, column: int,
row_start: int, row_end: int, text: str) -> None:
if not text:
return
cell_format = GetDefaultCellFormat(work_book)
# If the cell is in the last row of headers, no need for merging cells
if row_start + 1 == row_end:
work_sheet.write(row_start, column, text, cell_format)
return
# Sample : 'A1:D1'
merge_range_string = NumToExcelAlpha(column) + str(
row_start + 1) + ':' + NumToExcelAlpha(column) + str(row_end)
work_sheet.merge_range(merge_range_string, text, cell_format)
def write_form_description_as_cell_comment(
worksheet: Worksheet,
form_names: List[str],
question_index_to_row_index: Dict[int, int],
form_name_to_description: Dict[str, str],
) -> None:
# add form description as comment to column and make every other form
# appear in different color
last_form_name = ""
i = 1
for form_name in form_names:
if form_name != last_form_name:
last_form_name = form_name
if form_name in form_name_to_description:
worksheet.write_comment(
question_index_to_row_index[i + 1],
0,
form_name_to_description[form_name],
)
i = i + 1
def __init__(self, worksheet: Worksheet, headers=None, header_format=None):
if headers is None:
raise Exception("Invalid headers. List expected!")
if worksheet is None:
raise Exception("Invalid worksheet")
self._headers = headers
self._header_length = [len(header) for header in headers]
self._worksheet = worksheet
for i, header in enumerate(headers):
if header_format is not None:
worksheet.write(0, i, header, header_format)
else:
worksheet.write(0, i, header)
self._next_row = 1
def write(self, frame:Worksheet):
"""
show re on target cell::frame in xlsx
update the content of value a and format based on the re type
:param frame:
:return:
"""
# -normal default
# -crs Cell-specific Reference Signal (CRS)
# -mbsfnrs MBSFN reference signal
# -pbch PBCH
# -uepdsch_dmrs UE-specific Reference Signal (DM-RS) associated with PDSCH
# -edpdcch_dmrs DeModulation Reference Signal (DM-RS) associated with EPDCCH
# -pcs Positioning Reference Signal (PRS)
# -csi_rs SI Reference Signal (CSI-RS)
#only normal cp is supported in current release 2016.1.8
# in future,offset shall derived from an accumulated way or a lookup table
frame.write(self.k+self.n__s*7, self.l+self.n__s*7, self.a, self.reFormat)
def write_data(self, sheet: Worksheet, year_list):
sheet.write_column(1, 0, asset_sheet_content_zh)
col = 1
for year in year_list:
sheet.write(0, col, str(year) + self.date)
sheet.write_column(1, col, self.get_sheet(year).data_list)
col += 1
def write_row_col(sheet:Worksheet, row:int, col:int, dataL:list, cellFormat, writeRow:bool):
# if cellFormat is a list, assign cellFormat to the corresponding data,
# else assign the same cellFormat to all data
broadcast = (not isinstance(cellFormat, list))
if not broadcast:
if len(dataL) != len(cellFormat):
raise ValueError("The length of data and formats should be the same")
for i in range(len(dataL)):
if writeRow:
# row not changed, col increases with i
tmpRow = row
tmpCol = col+i
else:
# row increases with i, col not changed
tmpRow = row+i
tmpCol = col
# write as number in default, otherwise as string
try:
sheet.write_number(tmpRow, tmpCol, float(dataL[i]), cellFormat if broadcast else cellFormat[i])
except (TypeError, ValueError):
sheet.write_string(tmpRow, tmpCol, dataL[i], cellFormat if broadcast else cellFormat[i])
def add_worksheet(self, name=None):
"""
Add a new worksheet to the Excel workbook.
Args:
name: The worksheet name. Defaults to 'Sheet1', etc.
Returns:
Reference to a worksheet object.
"""
sheet_index = len(self.worksheets_objs)
name = self._check_sheetname(name)
# Initialisation data to pass to the worksheet.
init_data = {
'name': name,
'index': sheet_index,
'str_table': self.str_table,
'worksheet_meta': self.worksheet_meta,
'optimization': self.optimization,
'tmpdir': self.tmpdir,
'date_1904': self.date_1904,
'strings_to_numbers': self.strings_to_numbers,
'strings_to_formulas': self.strings_to_formulas,
'strings_to_urls': self.strings_to_urls,
'default_date_format': self.default_date_format,
'default_url_format': self.default_url_format,
}
worksheet = Worksheet()
worksheet._initialize(init_data)
self.worksheets_objs.append(worksheet)
self.sheetnames.append(name)
return worksheet
def add_worksheet(self, name=None):
"""
Add a new worksheet to the Excel workbook.
Args:
name: The worksheet name. Defaults to 'Sheet1', etc.
Returns:
Reference to a worksheet object.
"""
sheet_index = len(self.worksheets_objs)
name = self._check_sheetname(name)
# Initialisation data to pass to the worksheet.
init_data = {
'name': name,
'index': sheet_index,
'str_table': self.str_table,
'worksheet_meta': self.worksheet_meta,
'optimization': self.optimization,
'tmpdir': self.tmpdir,
'date_1904': self.date_1904,
'strings_to_numbers': self.strings_to_numbers,
'strings_to_formulas': self.strings_to_formulas,
'strings_to_urls': self.strings_to_urls,
'default_date_format': self.default_date_format,
'default_url_format': self.default_url_format,
}
worksheet = Worksheet()
worksheet._initialize(init_data)
self.worksheets_objs.append(worksheet)
self.sheetnames.append(name)
return worksheet
def write_data(self, sheet: Worksheet, merge_format):
"""
write the indicator data to the indicator sheet
:param sheet: indicator sheet which is a xlsxwriter.Worksheet instance
"""
col = 1
for year in self.year_list:
year_data = self.year2data[year]
sheet.write_column(6, col, year_data.data_list)
col += 1
avg_col = 1 + len(self.year_list) # “行业平均”数据所在列
ratio_col = 1 + avg_col # “比率”数据所在列
sub_score_col = 1 + ratio_col # “分项能力”得分所在列
sheet.write_column(6, avg_col, self.avg_data)
sheet.write_column(6, ratio_col, self.ratio)
sheet.merge_range("%s:%s" % (xl_rowcol_to_cell(6, sub_score_col), xl_rowcol_to_cell(11, sub_score_col)),
self.score, merge_format)
def write_column_titles(
worksheet: Worksheet,
num_columns: int,
title_format: Format,
translations: Dict[str, str],
) -> None:
worksheet.write("A1", translations.get("Form", "Form"), title_format)
for i in range(1, num_columns - 1):
worksheet.write_blank(f"{xl_col_to_name(i)}1", "", title_format)
worksheet.write(
f"{xl_col_to_name(num_columns - 1)}1",
translations.get("Question", "Question"),
title_format,
)
worksheet.write(
f"{xl_col_to_name(num_columns)}1",
translations.get("Answer", "Answer"),
title_format,
)
async def _store_hotel(self,
sheet: Worksheet,
row: int,
parsed_columns: List[HotelField],
hotel: HotelContentRow) -> None:
parsed_header: HotelField
# 由 Columns 尋訪，並找出資料欄位
for col, header in enumerate(parsed_columns):
if header is HotelField.Url:
url = hotel[header]
url = self._normializa_url(url)
link_style = {"color": "blue", "underline": 1, "font_size": 12}
link_format = self._workbook.add_format(link_style)
sheet.write_url(row, col, url, link_format)
elif header is HotelField.Rooms:
number_style = {"font_size": 12}
number_format = self._workbook.add_format(number_style)
sheet.write_number(row, col, int(hotel[header]), number_format)
else:
# 如果資料為 None 改成空字串
field_data = hotel[header] if hotel[header] else ""
sheet.write_string(row, col, field_data)
def _write_end(self, worksheet: Worksheet):
worksheet.write('A35', 'Konditionen:', self.fonts.tt)
worksheet.write('B35', '', self.fonts.leer)
worksheet.write('C35', 'netto', self.fonts.ttr)
worksheet.write('A36', 'zahlbar bis:', self.fonts.tt)
worksheet.write('B36', '', self.fonts.leer)
worksheet.write('C36',
(datetime.date.today() +
datetime.timedelta(365 / 12)).strftime('%d.%m.%Y'),
self.fonts.ttr_rot)
worksheet.write('A38', 'Herzlichen Dank für den geschätzten Auftrag',
self.fonts.d)
worksheet.write('A40', 'Mit freundlichen Grüssen', self.fonts.d)
worksheet.write('A41', 'Name des Betriebs', self.fonts.pr)
def _write_bill_header(self, worksheet: Worksheet):
worksheet.write('A13', 'Rechnung', self.fonts.rechnung)
worksheet.write('B13', '', self.fonts.dd)
worksheet.write('C13', '', self.fonts.dd)
worksheet.write('D13', '', self.fonts.dd)
def _write_postal_information(self, worksheet: Worksheet, bill: Bill):
worksheet.write('A7', 'Kunde:', self.fonts.bg)
worksheet.write('C7', bill.customer_name, self.fonts.d)
worksheet.write('C8', bill.customer_address, self.fonts.d)
worksheet.write('C9', bill.customer_zip_place, self.fonts.d)
def _write_bill_meta_info(self, worksheet: Worksheet, bill: Bill):
worksheet.write('A16', 'Datum:', self.fonts.bg)
t = time.strftime("%d.%m.%Y")
worksheet.write('C16', t, self.fonts.d)
worksheet.write('A18', 'Rechnungperiode:', self.fonts.d)
worksheet.write('C18', bill.period_str, self.fonts.pr)
worksheet.write('A19', 'Rechnungsnummer:', self.fonts.d)
worksheet.write('C19', bill.get_bill_nbr_string(), self.fonts.pr)
def _write_table(self, worksheet: Worksheet, bill: Bill):
worksheet.write('A21', 'Anzahl', self.fonts.tt)
worksheet.write('B21', 'Beschreibung', self.fonts.tt)
worksheet.write('C21', 'Preis', self.fonts.tt)
worksheet.write('D21', 'Betrag', self.fonts.tt)
worksheet.write('A22', '', self.fonts.leer)
worksheet.write('B22', 'Eierlieferungen', self.fonts.tt),
worksheet.write('C22', '', self.fonts.leer)
worksheet.write('D22', '', self.fonts.leer)
worksheet.write('A23', bill.nbr_of_eggs, self.fonts.anzahl)
worksheet.write('B23', 'Stück', self.fonts.tt)
worksheet.write('C23', Bill.price_per_egg, self.fonts.ttr)
worksheet.write('D23', '=A23*C23', self.fonts.ttr)
for x in range(23, 29):
for y in range(0, 4):
worksheet.write(x, y, '', self.fonts.leer)
worksheet.write('C30', 'Total', self.fonts.bgu)
worksheet.write('D30', '=SUM(D23:D29)', self.fonts.total)
def add_cell_validation(
worksheet: Worksheet,
row_index: int,
column_index: int,
full_question_path: Tuple[str, ...],
question: Dict[str, Any],
question_to_cell: Dict[Tuple[str, ...], str],
bool_options: Dict[str, str],
date_format: Format,
black_format: Format,
) -> None:
type = question["questionType"]
has_options = "options" in question
if type == "BOOL":
worksheet.data_validation(
row_index,
column_index,
row_index,
column_index,
{
"validate": "list",
"source": list(bool_options.values())
},
)
elif has_options:
options = question["options"]
if "multiOptions" in question and question["multiOptions"]:
s = set(options)
multi_options = sum(
map(lambda r: sorted(list(combinations(s, r))),
range(1,
len(s) + 1)),
[],
)
options = [
";".join(multi_option) for multi_option in multi_options
]
worksheet.data_validation(
row_index,
column_index,
row_index,
column_index,
{
"validate": "list",
"source": options
},
)
elif type == "INTEGER":
worksheet.data_validation(
row_index,
column_index,
row_index,
column_index,
{
"validate": "integer",
"criteria": ">",
"value": -(2**32)
},
)
elif type == "FLOAT":
worksheet.data_validation(
row_index,
column_index,
row_index,
column_index,
{
"validate": "decimal",
"criteria": ">",
"value": -(2**32)
},
)
elif type == "DATE":
worksheet.write_datetime(
row_index,
column_index,
datetime.strptime("01/01/1970", "%d/%m/%Y"),
date_format,
)
if "questionDescription" in question:
worksheet.write_comment(row_index, column_index,
question["questionDescription"])
if "dependsOn" in question:
write_conditional_blank_color_if_dependency_fails(
worksheet,
row_index,
column_index,
question,
full_question_path,
question_to_cell,
bool_options,
black_format,
)
def write_xlsx(self, sheet: Worksheet, father):
graph1 = bar_and_plot(
category=self.year_list,
bar_param=[[
father.profit_data.get_sheet(year).data["selling_expenses"]
for year in self.year_list
], "销售费用"],
plot_param=[[
self.get_indicator(year).data["sales_expense_rate"]
for year in self.year_list
], "销售费用率"])
sheet.insert_image(0, 0, "", {"image_data": graph1})
graph2 = bar_and_plot(
category=self.year_list,
bar_param=[[
father.profit_data.get_sheet(year).data["management_fees"]
for year in self.year_list
], "管理费用"],
plot_param=[[
self.get_indicator(year).data["management_fee_rate"]
for year in self.year_list
], "管理费用率"])
sheet.insert_image(20, 0, "", {"image_data": graph2})
graph3 = bar_and_plot(
category=self.year_list,
bar_param=[[
(father.profit_data.get_sheet(year).data["management_fees"] +
father.profit_data.get_sheet(year).data["selling_expenses"])
for year in self.year_list
], "销售费用+管理费用"],
plot_param=[[
self.get_indicator(year).data["sales_management_fee_rate"]
for year in self.year_list
], "销售管理费用率"])
sheet.insert_image(40, 0, "", {"image_data": graph3})
graph4 = bar_and_plot(
category=self.year_list,
bar_param=[[
(father.profit_data.get_sheet(year).data["management_fees"] +
father.profit_data.get_sheet(year).data["selling_expenses"] +
father.profit_data.get_sheet(year).data["financial_expenses"])
for year in self.year_list
], "期间费用"],
plot_param=[[
self.get_indicator(year).data["period_expense_rate"]
for year in self.year_list
], "期间费用率"])
sheet.insert_image(60, 0, "", {"image_data": graph4})
graph5 = bar_and_plot(
category=self.year_list,
bar_param=[[
father.balance_data.get_sheet(year).data["total_assets"]
for year in self.year_list
], "资产总计"],
plot_param=[[
self.get_indicator(year).data["asset_turnover_days"]
for year in self.year_list
], "资产周转天数"],
use_percent=False)
sheet.insert_image(80, 0, "", {"image_data": graph5})
graph6 = bar_and_plot(
category=self.year_list,
bar_param=[[
father.balance_data.get_sheet(year).data["accounts_receivable"]
for year in self.year_list
], "应收账款"],
plot_param=[[
self.get_indicator(
year).data["accounts_receivable_turnover_days"]
for year in self.year_list
], "应收账款周转天数"],
use_percent=False)
sheet.insert_image(100, 0, "", {"image_data": graph6})
graph7 = bar_and_plot(
category=self.year_list,
bar_param=[[
father.balance_data.get_sheet(year).data["stock"]
for year in self.year_list
], "存货"],
plot_param=[[
self.get_indicator(year).data["inventory_turnover_days"]
for year in self.year_list
], "存货周转天数"],
use_percent=False)
sheet.insert_image(120, 0, "", {"image_data": graph7})
graph8 = img_draw(category=self.year_list,
plot_params=[[[
self.get_indicator(year).data["business_cycle"]
for year in self.year_list
], "营业周期", 1]],
use_percent=False)
sheet.insert_image(140, 0, "", {"image_data": graph8})
graph9 = img_draw(
category=self.year_list,
plot_params=[[[
self.get_indicator(year).data["repayment_rate_of_sales"]
for year in self.year_list
], "销售回款率", 1]])
sheet.insert_image(160, 0, "", {"image_data": graph9})
def write_xlsx(self, sheet: Worksheet, father):
graph1 = bar_and_plot(
category=self.year_list,
bar_param=[
[father.cash_data.get_sheet(year).data["net_cash_op"] for year in self.year_list],
"经营现金流"
],
plot_param=[
[self.get_indicator(year).data["operating_cash_growth_rate"] for year in self.year_list],
"经营现金增长率"
]
)
sheet.insert_image(0, 0, "", {"image_data": graph1})
graph2 = img_draw(
category=self.year_list,
plot_params=[
[
[father.dev_data.get_indicator(year).data["sales_growth_rate"] for year in self.year_list],
"销售增长率",
1
],
[
[self.get_indicator(year).data["operating_cash_growth_rate"] for year in self.year_list],
"经营现金增长率",
1
]
]
)
sheet.insert_image(20, 0, "", {"image_data": graph2})
graph3 = bar_and_plot(
category=self.year_list,
bar_param=[
[father.cash_data.get_sheet(year).data["final_cash"] for year in self.year_list],
"期末现金流"
],
plot_param=[
[self.get_indicator(year).data["cash_flow_growth_rate"] for year in self.year_list],
"现金流量增长率"
]
)
sheet.insert_image(40, 0, "", {"image_data": graph3})
graph4 = bar_and_plot(
category=self.year_list,
bar_param=[
[self.get_indicator(year).data["free_cash_ratio"] for year in self.year_list],
"自由现金流"
],
plot_param=[
[self.get_indicator(year).data["sales_and_operating_cash_ratio"] for year in self.year_list],
"销售自由现金比"
]
)
sheet.insert_image(60, 0, "", {"image_data": graph4})
graph5 = img_draw(
category=self.year_list,
plot_params=[
[
[self.get_indicator(year).data["sales_and_operating_cash_ratio"] for year in self.year_list],
"销售经营现金比",
1
]
]
)
sheet.insert_image(80, 0, "", {"image_data": graph5})
graph6 = img_draw(
category=self.year_list,
plot_params=[
[
[self.get_indicator(year).data["sales_cash_ratio"] for year in self.year_list],
"销售现金比",
1
]
]
)
sheet.insert_image(100, 0, "", {"image_data": graph6})