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-2026, 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.
     */
    worksheet_write_dynamic_formula(worksheet1, CELL("F2"),
                                    "=_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.
     */
    worksheet_write_dynamic_formula(worksheet2, CELL("F2"),
                                    "=_xlfn.UNIQUE(B2:B17)",
                                    NULL);

    /* A more complex example combining SORT and UNIQUE. */
    worksheet_write_dynamic_formula(worksheet2, CELL("H2"),
                                    "=_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.
     */
    worksheet_write_dynamic_formula(worksheet3, CELL("F2"),
                                    "=_xlfn._xlws.SORT(B2:B17)",
                                    NULL);

    /* A more complex example combining SORT and FILTER. */
    worksheet_write_dynamic_formula(worksheet3, CELL("H2"),
                                    "=_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.
     */
    worksheet_write_dynamic_formula(worksheet4, CELL("D2"),
                                    "=_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.
     */
    worksheet_write_dynamic_formula(worksheet5, CELL("F1"),
                                    "=_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.
     */
    worksheet_write_dynamic_formula(worksheet6, CELL("D2"),
                                    "=_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.
     */
    worksheet_write_dynamic_formula(worksheet7, CELL("A1"),
                                    "=_xlfn.RANDARRAY(5,3,1,100, TRUE)",
                                    NULL);

    /*
     * Example of using the SEQUENCE() function.
     */
    worksheet_write_dynamic_formula(worksheet8, CELL("A1"),
                                    "=_xlfn.SEQUENCE(4,5)",
                                    NULL);


    /*
     * Example of using the Spill range operator.
     */
    worksheet_write_dynamic_formula(worksheet9, CELL("H2"),
                                    "=_xlfn.ANCHORARRAY(F2)",
                                    NULL);

    worksheet_write_dynamic_formula(worksheet9, CELL("J2"),
                                    "=COUNTA(_xlfn.ANCHORARRAY(F2))",
                                    NULL);

    /* Write the data the function will work on. */
    worksheet_write_dynamic_formula(worksheet9, CELL("F2"),
                                    "=_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.
     */
    worksheet_write_dynamic_array_formula(worksheet10, RANGE("B1:B3"),
                                          "=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);
    }
}