libxlsxwriter
Loading...
Searching...
No Matches
Tutorial 3: Writing different types of data to the XLSX File

In the previous section we created a simple spreadsheet with formatting using C and the libxlsxwriter module.

This time let's extend the data we want to write to include some dates:

Item Date Cost
Rent 2013-01-13 1000
Gas 2013-01-14 100
Food 2013-01-16 300
Gym 2013-01-20 50

The corresponding spreadsheet will look like this:

The differences here are that we have added a Date column with formatting and made that column a little wider to accommodate the dates.

To do this we can extend our program as follows:

#include "xlsxwriter.h"
/* Some data we want to write to the worksheet. */
struct expense {
char item[32];
int cost;
lxw_datetime datetime;
};
Struct to represent a date and time in Excel.
Definition: common.h:159
struct expense expenses[] = {
{"Rent", 1000, { .year = 2013, .month = 1, .day = 13 } },
{"Gas", 100, { .year = 2013, .month = 1, .day = 14 } },
{"Food", 300, { .year = 2013, .month = 1, .day = 16 } },
{"Gym", 50, { .year = 2013, .month = 1, .day = 20 } },
};
int main() {
/* Create a workbook and add a worksheet. */
lxw_workbook *workbook = workbook_new("tutorial03.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
int row = 0;
int col = 0;
int i;
/* Add a bold format to use to highlight cells. */
lxw_format *bold = workbook_add_format(workbook);
/* Add a number format for cells with money. */
lxw_format *money = workbook_add_format(workbook);
format_set_num_format(money, "$#,##0");
/* Add an Excel date format. */
lxw_format *date_format = workbook_add_format(workbook);
format_set_num_format(date_format, "mmmm d yyyy");
/* Adjust the column width. */
worksheet_set_column(worksheet, 0, 0, 15, NULL);
/* Write some data header. */
worksheet_write_string(worksheet, row, col, "Item", bold);
worksheet_write_string(worksheet, row, col + 1, "Cost", bold);
/* Iterate over the data and write it out element by element. */
for (i = 0; i < 4; i++) {
/* Write from the first cell below the headers. */
row = i + 1;
worksheet_write_string (worksheet, row, col, expenses[i].item, NULL);
worksheet_write_datetime(worksheet, row, col + 1, &expenses[i].datetime, date_format);
worksheet_write_number (worksheet, row, col + 2, expenses[i].cost, money);
}
void format_set_bold(lxw_format *format)
Turn on bold for the format font.
void format_set_num_format(lxw_format *format, const char *num_format)
Set the number format for a cell.
Struct to represent the formatting properties of an Excel format.
Definition: format.h:359
Struct to represent an Excel workbook.
Definition: workbook.h:293
Struct to represent an Excel worksheet.
Definition: worksheet.h:2115
lxw_workbook * workbook_new(const char *filename)
Create a new workbook object.
lxw_format * workbook_add_format(lxw_workbook *workbook)
Create a new Format object to formats cells in worksheets.
lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)
Add a new worksheet to a workbook.
lxw_error worksheet_set_column(lxw_worksheet *worksheet, lxw_col_t first_col, lxw_col_t last_col, double width, lxw_format *format)
Set the properties for one or more columns of cells.
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.
lxw_error worksheet_write_datetime(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_datetime *datetime, lxw_format *format)
Write a date or time to a worksheet cell.
/* Write a total using a formula. */
worksheet_write_string (worksheet, row + 1, col, "Total", bold);
worksheet_write_formula(worksheet, row + 1, col + 2, "=SUM(C2:C5)", money);
/* Save the workbook and free any allocated memory. */
return workbook_close(workbook);
}
lxw_error workbook_close(lxw_workbook *workbook)
Close the Workbook object and write the XLSX file.
lxw_error worksheet_write_formula(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format)
Write a formula to a worksheet cell.

The main difference between this and the previous program is that we have added a new Format object for dates and we have additional handling for the date information.

We have also extended the data that we are going to write to include a lxw_datetime struct to hold the date information. We will see how that is used shortly.

struct expense {
char item[32];
int cost;
lxw_datetime datetime;
};
struct expense expenses[] = {
{"Rent", 1000, { .year = 2013, .month = 1, .day = 13 } },
{"Gas", 100, { .year = 2013, .month = 1, .day = 14 } },
{"Food", 300, { .year = 2013, .month = 1, .day = 16 } },
{"Gym", 50, { .year = 2013, .month = 1, .day = 20 } },
};

Excel treats different types of input data, such as strings and numbers, differently although it generally does it transparently to the user. Libxlsxwriter tries to emulate this in the worksheet_write*() functions by mapping C data types to types that Excel supports.

In this version of our program we have used some of these functions for different types of data:

worksheet_write_string (worksheet, row, col, expenses[i].item, NULL);
worksheet_write_datetime(worksheet, row, col + 1, &expenses[i].datetime, date_format);
worksheet_write_number (worksheet, row, col + 2, expenses[i].cost, money);

The handling of dates is also new to our program.

Dates and times in Excel are floating point numbers that have a number format applied to display them in the correct format. Libxlsxwriter provides the worksheet_write_datetime() and the lxw_datetime struct help to convert dates and times into Excel date and time numbers.

The worksheet_write_datetime() function converts the lxw_datetime struct to a number that represents an Excel date but we also need to add the number format to ensure that Excel displays it as as date:

format_set_num_format(date_format, "mmmm d yyyy");

Date handling is explained in more detail in Working with Dates and Times.

That completes the tutorial section.

In the next sections we will look at the API in more detail starting with workbook.

Next: The Workbook object