I am trying to create a flask route that sends data to a function, that function creates an openpyxl excel file and returns the excel file to the route, and the route then returns the downloadable file to a React frontend. I'm not sure if this is the exact problem, but I am getting errors that the file is not found in my instance/files folder. Instead, the file is saving to my project's root directory. The same path is working for other routes, so I'm not sure what I'm doing wrong here/why it is saving elsewhere. I'm assuming this is why I can't return the excel file to the frontend, but it could be other issues with my function/route. Please help!
This is my openpyxl function:
def generate_prev_sim_csv(data):
get_dict = data
claims = data['claims']
setup_dict = data['setups']
summary_metric_headers = data['setupSummaryMetricHeaders']
filename = "Simulation_Summary.xlsx"
wb = Workbook()
sheet = wb.active
# styles
heading_font = Font(size=11, bold=True)
heading = NamedStyle(name='Heading')
wb.add_named_style(heading)
heading.font = heading_font
percent_value = NamedStyle(name='Percentage')
wb.add_named_style(percent_value)
percent_value.number_format = '0.00%'
# Claim Header
headers = ['Claim']
start_claim_header_row = 1
start_claim_header_col = 2
for i, header in enumerate(headers):
current_row = start_claim_header_row
column_letter = get_column_letter(start_claim_header_col)
cell_ref = f"{column_letter}{current_row}"
sheet[cell_ref] = header
sheet[cell_ref].style = heading
# Setup Header
setup_title = "Setup "
start_setup_header_row = 1
start_setup_header_col = 3
for header_index, header in enumerate(setup_dict):
current_row = start_setup_header_row
column_letter = get_column_letter(start_setup_header_col)
cell_ref = f"{column_letter}{current_row}"
sheet[cell_ref] = setup_title str(header_index)
sheet[cell_ref].style = heading
for col_index, col_data in enumerate(setup_dict):
current_col = start_setup_header_col 1
column_letter = get_column_letter(current_col)
cell_ref = f"{column_letter}{current_row}"
sheet[cell_ref] = setup_title str(col_index 1)
sheet[cell_ref].style = heading
# Side by Side Claim and Claim States Table
starting_col_index = 2
starting_row_index = 2
for index, claim in enumerate(claims):
current_row = starting_row_index index
column_letter = get_column_letter(starting_col_index)
cell_ref = f"{column_letter}{current_row}"
sheet[cell_ref] = claim
sheet[cell_ref].style = heading
for i, setup in enumerate(setup_dict):
setup_claims_on = setup[3]
current_col = starting_col_index i 1
column_letter = get_column_letter(current_col)
cell_ref = f"{column_letter}{current_row}"
if claim in setup_claims_on:
sheet[cell_ref] = setup[2][claim]['Summary_Metrics']['Reach']
sheet[cell_ref].style = percent_value
elif setup[0][claim] == "Offered":
sheet[cell_ref] = "Already Offered"
elif setup[0][claim] == "Considered":
sheet[cell_ref] = "Considered"
elif setup[0][claim] == "Excluded":
sheet[cell_ref] = "Excluded"
else:
sheet[cell_ref] = ""
# Summary Metrics Header
start_metric_header_row = 16
start_metric_header_col = 2
for i, header in enumerate(summary_metric_headers):
current_row = start_metric_header_row
column_letter = get_column_letter(start_metric_header_col)
cell_ref = f"{column_letter}{current_row}"
sheet[cell_ref] = "Summary Metrics"
sheet[cell_ref].style = heading
# Summary Metrics Table
start_col_index = 2
start_row_index = 17
for i, header in enumerate(summary_metric_headers):
current_row = start_row_index i
column_letter = get_column_letter(start_col_index)
cell_ref = f"{column_letter}{current_row}"
sheet[cell_ref] = header
sheet[cell_ref].style = heading
for id, setup in enumerate(setup_dict):
current_col = starting_col_index id 1
column_letter = get_column_letter(current_col)
cell_ref = f"{column_letter}{current_row}"
if header == "Subgroup":
sheet[cell_ref] = setup[5]
elif header == "Number of Respondents":
sheet[cell_ref] = setup[4]
elif header == "Average Liked":
sheet[cell_ref] = round(setup[1]["Average_Number_of_Items_Liked"], 2)
elif header == "Average Reach":
sheet[cell_ref] = setup[1]["Reach"]
sheet[cell_ref].style = percent_value
elif header == "Average Favorite":
sheet[cell_ref] = setup[1]["Favorite_Percentage"]
sheet[cell_ref].style = percent_value
else:
sheet[cell_ref] = ""
wb.save(filename=filename)
return filename
This is my route. I'm not sure what to do with the return from the function?:
@bp.route("/api/export_prev_sim_to_csv", methods=["GET", "POST"])
def export_simulations_to_csv():
data = request.get_json() or {}
print(data)
if not os.path.exists(current_app.instance_path):
os.mkdir(current_app.instance_path)
if not os.path.exists(os.path.join(current_app.instance_path, "files")):
os.mkdir(os.path.join(current_app.instance_path, "files"))
cs_fn = os.path.join(
current_app.instance_path, "files", "Simulation_Summary.xlsx"
)
openpyxl_file = generate_prev_sim_csv(data)
return send_file(
cs_fn,
mimetype=(
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
),
as_attachment=True,
cache_timeout=0,
)
CodePudding user response：
You are saving the file in root directory in generate_prev_sim_csv function
filename = "Simulation_Summary.xlsx"
[...]
wb.save(filename=filename)
Wb.save creates a file if it doesn't exist so you don't need to create file in your route
Just change the filename to this in your openpyxl function
filename = 'instance/files/Simulation_Summary.xlsx'