| << 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"
int main() {
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. */
write_worksheet_data(worksheet1, header1);
/*
* 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. */
write_worksheet_data(worksheet2, header1);
/*
* 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. */
write_worksheet_data(worksheet3, header1);
/*
* Example of using the SORTBY() function.
*/
"=_xlfn.SORTBY(A2:B9,B2:B9)",
NULL);
/* Write the data the function will work on. */
/*
* Example of using the XLOOKUP() function.
*/
"=_xlfn.XLOOKUP(E1,A2:A9,C2:C9)",
NULL);
/* Write the data the function will work on. */
/*
* Example of using the XMATCH() function.
*/
"=_xlfn.XMATCH(C2,A2:A6)",
NULL);
/* Write the data the function will work on. */
/*
* 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);
write_worksheet_data(worksheet9, header1);
/*
* Example of using dynamic ranges with older Excel functions.
*/
"=LEN(A1:A3)",
NULL);
/* Write the data the function will work on. */
}
/* 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;
};
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},
};
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);
}
}
void format_set_font_color(lxw_format *format, lxw_color_t color)
Set the color of the font used in the cell.
void format_set_bg_color(lxw_format *format, lxw_color_t color)
Set the pattern background color for a cell.
#define COLS(cols)
Convert an Excel A:B column range into a (col1, col2) pair.
Definition: utility.h:64
#define RANGE(range)
Convert an Excel A1:B2 range into a (first_row, first_col, last_row, last_col) sequence.
Definition: utility.h:83
#define CELL(cell)
Convert an Excel A1 cell string into a (row, col) pair.
Definition: utility.h:46
lxw_format * workbook_add_format(lxw_workbook *workbook)
Create a new Format object to formats cells in worksheets.
lxw_error workbook_close(lxw_workbook *workbook)
Close the Workbook object and write the XLSX file.
lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)
Add a new worksheet to a workbook.
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_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_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.
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.
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.
