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 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);
    format_set_bold(bold);
 
    /* 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);
    }
 
    /* 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);
}

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.

• worksheet_write_string()
• worksheet_write_number()
• worksheet_write_formula()
• worksheet_write_datetime()
• worksheet_write_blank()

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