Tutorial 2: Adding formatting to the XLSX File

Next: Tutorial 3: Writing different types of data to the XLSX File.

In the previous section we created a simple spreadsheet using C and the libxlsxwriter library.

This converted the required data into an Excel file but it looked a little bare. In order to make the information clearer we would like to add some simple formatting, like this:

The differences here are that we have added Item and Cost column headers in a bold font, we have formatted the currency in the second column and we have made the Total string bold.

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;
};
 
struct expense expenses[] = {
    {"Rent", 1000},
    {"Gas",   100},
    {"Food",  300},
    {"Gym",    50},
};
 
 
int main() {
 
    /* Create a workbook and add a worksheet. */
    lxw_workbook  *workbook  = workbook_new("tutorial02.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");
 
    /* 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_number(worksheet, row, col + 1, 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 + 1, "=SUM(B2:B5)", 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 two Format objects that we can use to format cells in the spreadsheet.

Format objects represent all of the formatting properties that can be applied to a cell in Excel such as fonts, number formatting, colors and borders. This is explained in more detail in The Format object and Working with Formats.

For now we will avoid getting into the details and just use a limited amount of the format functionality to add some simple formatting:

    /* 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");

We can then pass these formats as a parameter to the worksheet_write*() functions to format the data in the cell:

    worksheet_write_string (worksheet, row + 1, col,     "Total",       bold);
    worksheet_write_formula(worksheet, row + 1, col + 1, "=SUM(B2:B5)", money);

In the next section we will look at handling more data types.

Next: Tutorial 3: Writing different types of data to the XLSX File.