XLSXWriter 使用动态单元格引用跨列应用公式 [英] XLSXWriter Apply Formula Across Column With Dynamic Cell Reference

问题描述

以下是 XSLX Writer 文档中的示例:

Here's an example from the XSLX Writer documentation:

worksheet.write_formula('A1', '=10*B1 + C1')

看起来很简单.但是如果我想将这个公式应用于A1:A30"呢?此外，如果在单元格 A3 中，公式应动态更新为:

Looks simple enough. But what if I want to apply this formula for 'A1:A30'? Also, what if in cell A3, the formula should dynamically update to:

'=10*B3 + C3'

?

我一直无法通过查看文档来做到这一点.我看到 write_array_formula 的一个有前途的选项，但我不想要数组公式.

I've been unable to do this from looking at the docs. I see a promising option for write_array_formula, but I don't want an array formula.

我想出了一个涉及遍历每一行和每一列的解决方案，但我希望有一个更简单的解决方案.

I've come up with a solution involving looping through each row and column, but I'm hoping there's a simpler one.

推荐答案

我想出了一个涉及遍历每一行和每一列的解决方案，但我希望有一个更简单的解决方案.

I've come up with a solution involving looping through each row and column, but I'm hoping there's a simpler one.

这是唯一的方法.像这样:

That is the only way to do it. Something like this:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

for row_num in range(1, 21):
    worksheet.write_formula(row_num - 1, 0,
                            '=10*$B%d + $C%d' % (row_num, row_num))

    # Write some data for the formula.
    worksheet.write(row_num - 1, 1, row_num)
    worksheet.write(row_num - 1, 2, row_num)

workbook.close()

这篇关于XLSXWriter 使用动态单元格引用跨列应用公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！