|
Xlsxwriter是一个功能强大的库，可以重新创建Excel的大多数功能，包括公式、链接和命名区域。在本系列的第1部分：使用Python Xlsxwriter创建Excel电子表格，我们对其进行了初步介绍。现在开始，利用Python的功能提升我们的Excel技能。 为了演示，我们将使用xlsxwriter创建一个现金流预测模型。在金融学中，“现金流预测”是指在一定年限内进行的一组计算。在我们的演示中，我们预测（即计算）一个人10年的收入和支出，并考虑工资增长和通货膨胀。同时假设此人将所有剩余收入储蓄起来，并将其用于投资，投资也将以假设的百分比增长。然后，我们可以计算出这个人在x年内有多少钱。 设置我们的“Excel”工作簿和工作表 import xlsxwriter from xlsxwriter.utility import xl_rowcol_to_cell wb = xlsxwriter.Workbook(r'D:\hf_excel.xlsx') ws_1 = wb.add_worksheet('输入') ws_2 = wb.add_worksheet('计算') variables = { '年数': 10, '收入': 50000, '支出': 30000, '收入增长率': 0.05, '投资回报率': 0.06, '通胀率': 0.03, } row = 1 col = 1 我们将几个输入参数存储到一个名为variables的字典中，这些是我们用来预测现金流的假设。我们还初始化了两个计数器row和col，稍后我们将使用它们在单元格中循环遍历。当然，我们可以使用枚举而不是设置计数器，但这样做可以更自由地更改单元格坐标且更容易。 让我们首先将字典内容写入“输入”工作表或ws_1。 for i in variables.keys(): ws_1.write(row,col,i) ws_1.write(row,col+1,variables[i]) row += 1 结果如下图1所示。
图1 使用Python对Excel工作簿定义名称 在Excel工作簿中创建“名称”是一种常见做法。这些名称基本上只是变量，可以通过调用给定的名称来访问它们，而不是使用单元格/单元格区域地址。要在Excel中创建和修改名称，单击功能区“公式”选项卡中的“名称管理器”，如下图2所示。 图2 在Python中，我们可以使用Workbook对象中的define_name()方法。这个方法需要两个参数：第一个参数是要使用的名称，第二个参数是指向单元格地址的字符串，或者只是一个值。例如： wb.define_name('income_increase','=输入!$C$5') wb.define_name('investment_return','=输入!$C$6') wb.define_name('inflation','=输入!$C$7') wb.define_name('test','=100') 一旦有了这些名称，就可以直接调用它们，而不必担心值是否位于单元格“G10”或任何地址中。 现金流预测模型 最好在做法是在“计算”工作表上再次显示输入假设。它确实是重复的，但它有助于使事情更加透明。因此，我们将把“输入”工作表上的所有内容都转到“计算”工作表上，“计算”工作表上的值将链接到“输入”工作表。 row = 1 for i in variables.keys(): ws_2.write(row, col, i) ws_2.write(row, col+1, f'=输入!{xl_rowcol_to_cell(row,col+1)}') row += 1 将行计数器重置回1后，可以使用类似的循环将字典内容写入ws_2，即“计算”工作表。 使用f-字符串帮助将变量传递到字符串中，因此f”=输入！{xl_rowcol_to_cell(1,2)}”计算结果为“=输入！C2”。 接着，我们快速设置计算列名和行号（即年份）。我们可以使用sheet.write_row和sheet.write_column方法将可编辑项写入Excel工作表。 calc_cols = ['年份','收入','支出','可投资收入','累积财富'] ws_2.write_row(9,1,calc_cols) ws_2.write_column(10,1,range(variables['年数'])) 然后，我们定义了一个辅助函数annual_increase()来避免重复代码。函数将首先写入初始值（第0年），然后向前到剩余的9个值。
def annual_increase(ws, start_row,start_col, n, initial_value, name_range): ws.write(start_row, start_col, initial_value) for i in range(n-1): ws.write(start_row+1+i, start_col,f'={xl_rowcol_to_cell(start_row+i,start_col)}*(1+{name_range})') 对于链接到外部Excel文件，方法相同，只需将完整路径添加到要链接到的文件，就可以了。 使用Python编写Excel公式 我们可以使用辅助函数annual_increase为前两个计算项目“收入”和“支出”创建公式“数组”。 annual_increase(ws_2, 10, 2,variables['年数'], variables['收入'],'income_increase') annual_increase(ws_2, 10, 3, variables['年数'],variables['支出'], 'inflation') 要编写“可投资收入”计算公式，只需计算收入和支出之间的差额。 for i in range(variables['年数']): ws_2.write(10+i, 4, f'={xl_rowcol_to_cell(10+i,2)}-{xl_rowcol_to_cell(10+i, 3)}') 最后，写出“累积财富”列的公式。最后，不要忘记wb.close()工作簿，否则什么保存不了。 ws_2.write(10, 5,f'={xl_rowcol_to_cell(10, 4)}') for i in range(variables['年数']-1): ws_2.write(11+i, 5, f'={xl_rowcol_to_cell(10+i,5)}*(1+investment_return)+{xl_rowcol_to_cell(11+i,4)}',) wb.close() 放在一起 全部代码如下： import xlsxwriter from xlsxwriter.utility import xl_rowcol_to_cell wb =xlsxwriter.Workbook(r'D:\hf_excel.xlsx') ws_1 = wb.add_worksheet('输入') ws_2 = wb.add_worksheet('计算') variables = { '年数': 10, '收入': 50000, '支出': 30000, '收入增长率': 0.05, '投资回报率': 0.06, '通胀率': 0.03, } row = 1 col = 1 for i in variables.keys(): ws_1.write(row,col,i) ws_1.write(row,col+1,variables[i]) row += 1 wb.define_name('income_increase','=输入!$C$5') wb.define_name('investment_return','=输入!$C$6') wb.define_name('inflation','=输入!$C$7') wb.define_name('test','=100') row = 1 for i in variables.keys(): ws_2.write(row, col, i) ws_2.write(row, col+1, f'=输入!{xl_rowcol_to_cell(row,col+1)}') row += 1 calc_cols = ['年份','收入','支出','可投资收入','累积财富'] ws_2.write_row(9,1,calc_cols) ws_2.write_column(10,1,range(variables['年数'])) def annual_increase(ws, start_row,start_col, n, initial_value, name_range): ws.write(start_row, start_col, initial_value) for i in range(n-1): ws.write(start_row+1+i, start_col,f'={xl_rowcol_to_cell(start_row+i,start_col)}*(1+{name_range})') annual_increase(ws_2, 10, 2,variables['年数'], variables['收入'],'income_increase') annual_increase(ws_2, 10, 3, variables['年数'],variables['支出'], 'inflation') for i in range(variables['年数']): ws_2.write(10+i, 4, f'={xl_rowcol_to_cell(10+i,2)}-{xl_rowcol_to_cell(10+i, 3)}') ws_2.write(10, 5,f'={xl_rowcol_to_cell(10, 4)}') for i in range(variables['年数']-1): ws_2.write(11+i, 5, f'={xl_rowcol_to_cell(10+i,5)}*(1+investment_return)+{xl_rowcol_to_cell(11+i,4)}',) wb.close() 结果如下图3所示。 图3 注：本文学习整理自pythoninoffice.com。 |
|
|
来自： hercules028 > 《Python and AI》
