使用Python Xlsxwriter创建Excel电子表格（第2部分：公式，链接与命名区域）
使用Python Xlsxwriter创建Excel电子表格（第2部分：公式，链接与命名区域）
标签：Python与Excel,Xlsxwriter
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所示。
使用Python对Excel工作簿定义名称
在Excel工作簿中创建“名称”是一种常见做法。这些名称基本上只是变量，可以通过调用给定的名称来访问它们，而不是使用单元格/单元格区域地址。要在Excel中创建和修改名称，单击功能区“公式”选项卡中的“名称管理器”，如下图2所示。
在Python中，我们可以使用Workbook对象中的define_name()方法。这个方法需要两个参数：第一个参数是要使用的名称，第二个参数是指向单元格地址的字符串，或者只是一个值。例如：
wb.define_name('income_increase',"=输入!
wb.define_name('investment_return',"=输入!
wb.define_name('inflation',"=输入!
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个值。
- ws（工作表）作为参数，因此很容易写入任何工作表
- name_range作为参数，因此可以将不同的名称传递到函数中进行计算
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',"=输入!
wb.define_name('investment_return',"=输入!
wb.define_name('inflation',"=输入!
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所示。
