1#######################################################################
2#
3# Example of how to use Python and the XlsxWriter module to write
4# simple array formulas.
5#
6# Copyright 2013-2021, John McNamara, jmcnamara@cpan.org
7#
8import xlsxwriter
9
10# Create a new workbook and add a worksheet
11workbook = xlsxwriter.Workbook('array_formula.xlsx')
12worksheet = workbook.add_worksheet()
13
14# Write some test data.
15worksheet.write('B1', 500)
16worksheet.write('B2', 10)
17worksheet.write('B5', 1)
18worksheet.write('B6', 2)
19worksheet.write('B7', 3)
20worksheet.write('C1', 300)
21worksheet.write('C2', 15)
22worksheet.write('C5', 20234)
23worksheet.write('C6', 21003)
24worksheet.write('C7', 10000)
25
26
27# Write an array formula that returns a single value
28worksheet.write_formula('A1', '{=SUM(B1:C1*B2:C2)}')
29
30# Same as above but more verbose.
31worksheet.write_array_formula('A2:A2', '{=SUM(B1:C1*B2:C2)}')
32
33# Write an array formula that returns a range of values
34worksheet.write_array_formula('A5:A7', '{=TREND(C5:C7,B5:B7)}')
35
36workbook.close()
37