def dataframe_to_excel(df, index):
filename = './temp/excel_input_' + str(index) + '.xlsx'
if isinstance(df, pd.DataFrame):
with open(filename, "a+", encoding='utf8') as f:
writer = ew(filename, engine='xlsxwriter')
df.to_excel(writer, 'sheet1')
writer.save()
def xlwriter(rows, p, r, n, title):
# convert the interest rate to monthly for output.
# pass p,r,n to i_amortize to retrieve the monthly
# payment. calculate the total loan after interest.
mpr = (r/100)/12
mp = i_amortize(p, r, n)
amount = mp*n
name = title+".xlsx"
if op.isfile(name):
ErrorBack = (1, f"{title}.xlsx already exists")
return ErrorBack
# stuff the rows from the passed dictionary into a
# data frame, and create the writer.
df1 = xl(rows)
writer = ew(name, engine='xlsxwriter')
# attempt to output the data frame to a spreadsheet
try:
df1.to_excel(writer, startrow=6, index=False)
ErrorBack = (0, 'success!')
except ValueError:
# more error handling will go here as testing
# progresses. for now it returns a 1 value for
# a ValueError
ErrorBack = (2, "some other funky thing went wrong")
return ErrorBack
# open a workbook and worksheet
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# number formats for currency, percentage, and whole numbers
format_per = workbook.add_format({'num_format': '%##.##'})
format_mpr = workbook.add_format({'num_format': '%.######'})
format_num = workbook.add_format({'num_format': '#,###'})
format_cur = workbook.add_format({'num_format': '$#,##0.00'})
# set the format for all columns to currency
worksheet.set_column('A:E', 18, format_cur)
# write the summary info at the top
worksheet.write(0, 0, 'Loan Amount')
worksheet.write(0, 1, p, format_cur)
worksheet.write(1, 0, 'Interest Rate')
worksheet.write(1, 1, r, format_per)
worksheet.write(2, 0, 'Number of Payments')
worksheet.write(2, 1, n, format_num)
worksheet.write(3, 0, 'Monthly Interset Rate')
worksheet.write(3, 1, mpr, format_mpr)
worksheet.write(4, 0, 'Total Loan Amount')
worksheet.write(4, 1, amount, format_cur)
# save the spreadsheet
writer.save()
return ErrorBack
def generar_DF_Excel(df, nombre_archivo):
nombre_final = nombre_archivo + '.xlsx'
writer = ew(nombre_final)
df.to_excel(writer, 'Orders2')
writer.save()
print('Ok')