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"
struct expense {
char item[32];
int cost;
};
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() {
int row = 0;
int col = 0;
int i;
for (i = 0; i < 4; i++) {
row = i + 1;
}
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.
}
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;
};
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:
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:
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