libxlsxwriter
dynamic_arrays.c
<< array_formula.c utf8.c >>

Example of writing Excel 365 dynamic array formulas to a worksheet.

/*
* An example of how to use libxlsxwriter to write functions that create
* dynamic arrays. These functions are new to Excel 365. The examples mirror
* the examples in the Excel documentation on these functions.
*
* Copyright 2014-2021, John McNamara, jmcnamara@cpan.org
*
*/
#include "xlsxwriter.h"
void write_worksheet_data(lxw_worksheet *worksheet, lxw_format *header);
int main() {
lxw_workbook *workbook = workbook_new("dynamic_arrays.xlsx");
lxw_worksheet *worksheet1 = workbook_add_worksheet(workbook, "Filter");
lxw_worksheet *worksheet2 = workbook_add_worksheet(workbook, "Unique");
lxw_worksheet *worksheet3 = workbook_add_worksheet(workbook, "Sort");
lxw_worksheet *worksheet4 = workbook_add_worksheet(workbook, "Sortby");
lxw_worksheet *worksheet5 = workbook_add_worksheet(workbook, "Xlookup");
lxw_worksheet *worksheet6 = workbook_add_worksheet(workbook, "Xmatch");
lxw_worksheet *worksheet7 = workbook_add_worksheet(workbook, "Randarray");
lxw_worksheet *worksheet8 = workbook_add_worksheet(workbook, "Sequence");
lxw_worksheet *worksheet9 = workbook_add_worksheet(workbook, "Spill ranges");
lxw_worksheet *worksheet10 = workbook_add_worksheet(workbook, "Older functions");
lxw_format *header1 = workbook_add_format(workbook);
format_set_bg_color(header1, 0x74AC4C);
format_set_font_color(header1, 0xFFFFFF);
lxw_format *header2 = workbook_add_format(workbook);
format_set_bg_color(header2, 0x528FD3);
format_set_font_color(header2, 0xFFFFFF);
/*
* Example of using the FILTER() function.
*/
"=_xlfn._xlws.FILTER(A1:D17,C1:C17=K2)",
NULL);
/* Write the data the function will work on. */
worksheet_write_string(worksheet1, CELL("K1"), "Product", header2);
worksheet_write_string(worksheet1, CELL("K2"), "Apple", NULL );
worksheet_write_string(worksheet1, CELL("F1"), "Region", header2);
worksheet_write_string(worksheet1, CELL("G1"), "Sales Rep", header2);
worksheet_write_string(worksheet1, CELL("H1"), "Product", header2);
worksheet_write_string(worksheet1, CELL("I1"), "Units", header2);
write_worksheet_data(worksheet1, header1);
worksheet_set_column_pixels(worksheet1, COLS("E:E"), 20, NULL);
worksheet_set_column_pixels(worksheet1, COLS("J:J"), 20, NULL);
/*
* Example of using the UNIQUE() function.
*/
"=_xlfn.UNIQUE(B2:B17)",
NULL);
/* A more complex example combining SORT and UNIQUE. */
"=_xlfn._xlws.SORT(_xlfn.UNIQUE(B2:B17))",
NULL);
/* Write the data the function will work on. */
worksheet_write_string(worksheet2, CELL("F1"), "Sales Rep", header2);
worksheet_write_string(worksheet2, CELL("H1"), "Sales Rep", header2);
write_worksheet_data(worksheet2, header1);
worksheet_set_column_pixels(worksheet2, COLS("E:E"), 20, NULL);
worksheet_set_column_pixels(worksheet2, COLS("G:G"), 20, NULL);
/*
* Example of using the SORT() function.
*/
"=_xlfn._xlws.SORT(B2:B17)",
NULL);
/* A more complex example combining SORT and FILTER. */
"=_xlfn._xlws.SORT(_xlfn._xlws.FILTER(C2:D17,D2:D17>5000,\"\"),2,1)",
NULL);
/* Write the data the function will work on. */
worksheet_write_string(worksheet3, CELL("F1"), "Sales Rep", header2);
worksheet_write_string(worksheet3, CELL("H1"), "Product", header2);
worksheet_write_string(worksheet3, CELL("I1"), "Units", header2);
write_worksheet_data(worksheet3, header1);
worksheet_set_column_pixels(worksheet3, COLS("E:E"), 20, NULL);
worksheet_set_column_pixels(worksheet3, COLS("G:G"), 20, NULL);
/*
* Example of using the SORTBY() function.
*/
"=_xlfn.SORTBY(A2:B9,B2:B9)",
NULL);
/* Write the data the function will work on. */
worksheet_write_string(worksheet4, CELL("A1"), "Name", header1);
worksheet_write_string(worksheet4, CELL("B1"), "Age", header1);
worksheet_write_string(worksheet4, CELL("A2"), "Tom", NULL);
worksheet_write_string(worksheet4, CELL("A3"), "Fred", NULL);
worksheet_write_string(worksheet4, CELL("A4"), "Amy", NULL);
worksheet_write_string(worksheet4, CELL("A5"), "Sal", NULL);
worksheet_write_string(worksheet4, CELL("A6"), "Fritz", NULL);
worksheet_write_string(worksheet4, CELL("A7"), "Srivan", NULL);
worksheet_write_string(worksheet4, CELL("A8"), "Xi", NULL);
worksheet_write_string(worksheet4, CELL("A9"), "Hector", NULL);
worksheet_write_number(worksheet4, CELL("B2"), 52, NULL);
worksheet_write_number(worksheet4, CELL("B3"), 65, NULL);
worksheet_write_number(worksheet4, CELL("B4"), 22, NULL);
worksheet_write_number(worksheet4, CELL("B5"), 73, NULL);
worksheet_write_number(worksheet4, CELL("B6"), 19, NULL);
worksheet_write_number(worksheet4, CELL("B7"), 39, NULL);
worksheet_write_number(worksheet4, CELL("B8"), 19, NULL);
worksheet_write_number(worksheet4, CELL("B9"), 66, NULL);
worksheet_write_string(worksheet4, CELL("D1"), "Name", header2);
worksheet_write_string(worksheet4, CELL("E1"), "Age", header2);
worksheet_set_column_pixels(worksheet4, COLS("C:C"), 20, NULL);
/*
* Example of using the XLOOKUP() function.
*/
"=_xlfn.XLOOKUP(E1,A2:A9,C2:C9)",
NULL);
/* Write the data the function will work on. */
worksheet_write_string(worksheet5, CELL("A1"), "Country", header1);
worksheet_write_string(worksheet5, CELL("B1"), "Abr", header1);
worksheet_write_string(worksheet5, CELL("C1"), "Prefix", header1);
worksheet_write_string(worksheet5, CELL("A2"), "China", NULL);
worksheet_write_string(worksheet5, CELL("A3"), "India", NULL);
worksheet_write_string(worksheet5, CELL("A4"), "United States", NULL);
worksheet_write_string(worksheet5, CELL("A5"), "Indonesia", NULL);
worksheet_write_string(worksheet5, CELL("A6"), "Brazil", NULL);
worksheet_write_string(worksheet5, CELL("A7"), "Pakistan", NULL);
worksheet_write_string(worksheet5, CELL("A8"), "Nigeria", NULL);
worksheet_write_string(worksheet5, CELL("A9"), "Bangladesh", NULL);
worksheet_write_string(worksheet5, CELL("B2"), "CN", NULL);
worksheet_write_string(worksheet5, CELL("B3"), "IN", NULL);
worksheet_write_string(worksheet5, CELL("B4"), "US", NULL);
worksheet_write_string(worksheet5, CELL("B5"), "ID", NULL);
worksheet_write_string(worksheet5, CELL("B6"), "BR", NULL);
worksheet_write_string(worksheet5, CELL("B7"), "PK", NULL);
worksheet_write_string(worksheet5, CELL("B8"), "NG", NULL);
worksheet_write_string(worksheet5, CELL("B9"), "BD", NULL);
worksheet_write_number(worksheet5, CELL("C2"), 86, NULL);
worksheet_write_number(worksheet5, CELL("C3"), 91, NULL);
worksheet_write_number(worksheet5, CELL("C4"), 1, NULL);
worksheet_write_number(worksheet5, CELL("C5"), 62, NULL);
worksheet_write_number(worksheet5, CELL("C6"), 55, NULL);
worksheet_write_number(worksheet5, CELL("C7"), 92, NULL);
worksheet_write_number(worksheet5, CELL("C8"), 234, NULL);
worksheet_write_number(worksheet5, CELL("C9"), 880, NULL);
worksheet_write_string(worksheet5, CELL("E1"), "Brazil", header2);
worksheet_set_column_pixels(worksheet5, COLS("A:A"), 100, NULL);
worksheet_set_column_pixels(worksheet5, COLS("D:D"), 20, NULL);
/*
* Example of using the XMATCH() function.
*/
"=_xlfn.XMATCH(C2,A2:A6)",
NULL);
/* Write the data the function will work on. */
worksheet_write_string(worksheet6, CELL("A1"), "Product", header1);
worksheet_write_string(worksheet6, CELL("A2"), "Apple", NULL);
worksheet_write_string(worksheet6, CELL("A3"), "Grape", NULL);
worksheet_write_string(worksheet6, CELL("A4"), "Pear", NULL);
worksheet_write_string(worksheet6, CELL("A5"), "Banana", NULL);
worksheet_write_string(worksheet6, CELL("A6"), "Cherry", NULL);
worksheet_write_string(worksheet6, CELL("C1"), "Product", header2);
worksheet_write_string(worksheet6, CELL("D1"), "Position", header2);
worksheet_write_string(worksheet6, CELL("C2"), "Grape", NULL);
worksheet_set_column_pixels(worksheet6, COLS("B:B"), 20, NULL);
/*
* Example of using the RANDARRAY() function.
*/
"=_xlfn.RANDARRAY(5,3,1,100, TRUE)",
NULL);
/*
* Example of using the SEQUENCE() function.
*/
"=_xlfn.SEQUENCE(4,5)",
NULL);
/*
* Example of using the Spill range operator.
*/
"=_xlfn.ANCHORARRAY(F2)",
NULL);
"=COUNTA(_xlfn.ANCHORARRAY(F2))",
NULL);
/* Write the data the function will work on. */
"=_xlfn.UNIQUE(B2:B17)",
NULL);
worksheet_write_string(worksheet9, CELL("F1"), "Unique", header2);
worksheet_write_string(worksheet9, CELL("H1"), "Spill", header2);
worksheet_write_string(worksheet9, CELL("J1"), "Spill", header2);
write_worksheet_data(worksheet9, header1);
worksheet_set_column_pixels(worksheet9, COLS("E:E"), 20, NULL);
worksheet_set_column_pixels(worksheet9, COLS("G:G"), 20, NULL);
worksheet_set_column_pixels(worksheet9, COLS("I:I"), 20, NULL);
/*
* Example of using dynamic ranges with older Excel functions.
*/
"=LEN(A1:A3)",
NULL);
/* Write the data the function will work on. */
worksheet_write_string(worksheet10, CELL("A1"), "Foo", NULL);
worksheet_write_string(worksheet10, CELL("A2"), "Food", NULL);
worksheet_write_string(worksheet10, CELL("A3"), "Frood", NULL);
return workbook_close(workbook);
}
/* A simple function and data structure to populate some of the worksheets. */
struct worksheet_data {
char col1[10];
char col2[10];
char col3[10];
int col4;
};
void write_worksheet_data(lxw_worksheet *worksheet, lxw_format *header) {
struct worksheet_data data[160] = {
{"East", "Tom", "Apple", 6380},
{"West", "Fred", "Grape", 5619},
{"North", "Amy", "Pear", 4565},
{"South", "Sal", "Banana", 5323},
{"East", "Fritz", "Apple", 4394},
{"West", "Sravan", "Grape", 7195},
{"North", "Xi", "Pear", 5231},
{"South", "Hector", "Banana", 2427},
{"East", "Tom", "Banana", 4213},
{"West", "Fred", "Pear", 3239},
{"North", "Amy", "Grape", 6520},
{"South", "Sal", "Apple", 1310},
{"East", "Fritz", "Banana", 6274},
{"West", "Sravan", "Pear", 4894},
{"North", "Xi", "Grape", 7580},
{"South", "Hector", "Apple", 9814},
};
worksheet_write_string(worksheet, CELL("A1"), "Region", header);
worksheet_write_string(worksheet, CELL("B1"), "Sales Rep", header);
worksheet_write_string(worksheet, CELL("C1"), "Product", header);
worksheet_write_string(worksheet, CELL("D1"), "Units", header);
for (int row = 0; row < 16; row++) {
worksheet_write_string(worksheet, row + 1, 0, data[row].col1, NULL);
worksheet_write_string(worksheet, row + 1, 1, data[row].col2, NULL);
worksheet_write_string(worksheet, row + 1, 2, data[row].col3, NULL);
worksheet_write_number(worksheet, row + 1, 3, data[row].col4, NULL);
}
}
workbook_close
lxw_error workbook_close(lxw_workbook *workbook)
Close the Workbook object and write the XLSX file.
workbook_new
lxw_workbook * workbook_new(const char *filename)
Create a new workbook object.
worksheet_write_dynamic_formula
lxw_error worksheet_write_dynamic_formula(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format)
Write an Excel 365 dynamic array formula to a worksheet cell.
worksheet_set_column_pixels
lxw_error worksheet_set_column_pixels(lxw_worksheet *worksheet, lxw_col_t first_col, lxw_col_t last_col, uint32_t pixels, lxw_format *format)
Set the properties for one or more columns of cells, with the width in pixels.
lxw_worksheet
Struct to represent an Excel worksheet.
Definition: worksheet.h:2107
lxw_format
Struct to represent the formatting properties of an Excel format.
Definition: format.h:358
RANGE
#define RANGE(range)
Convert an Excel A1:B2 range into a (first_row, first_col, last_row, last_col) sequence.
Definition: utility.h:82
COLS
#define COLS(cols)
Convert an Excel A:B column range into a (col1, col2) pair.
Definition: utility.h:63
format_set_bg_color
void format_set_bg_color(lxw_format *format, lxw_color_t color)
Set the pattern background color for a cell.
worksheet_write_dynamic_array_formula
lxw_error worksheet_write_dynamic_array_formula(lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col, const char *formula, lxw_format *format)
Write an Excel 365 dynamic array formula to a worksheet range.
lxw_workbook
Struct to represent an Excel workbook.
Definition: workbook.h:280
worksheet_write_string
lxw_error worksheet_write_string(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *string, lxw_format *format)
Write a string to a worksheet cell.
worksheet_write_number
lxw_error worksheet_write_number(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, double number, lxw_format *format)
Write a number to a worksheet cell.
format_set_font_color
void format_set_font_color(lxw_format *format, lxw_color_t color)
Set the color of the font used in the cell.
CELL
#define CELL(cell)
Convert an Excel A1 cell string into a (row, col) pair.
Definition: utility.h:45
workbook_add_worksheet
lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)
Add a new worksheet to a workbook.
workbook_add_format
lxw_format * workbook_add_format(lxw_workbook *workbook)
Create a new Format object to formats cells in worksheets.