I’ve had a lot of requests for data export functionality from my habit tracking app Daily Goals. People tentatively suggest comma separated value (CSV) format, but I know that deep down they want the only data format that matters to non-software developers - Excel.
Data in Daily Goals is persisted using an SQLite database (via Core Data) with three main tables - habits, events and notes. The key advantage of an Excel file over a CSV is that I can create multiple sheets, which in turn allows me to present the same data in multiple ways within the one document. For example, I export worksheets containing all the habits, events and notes separately for people who want to analyse their habits from an overall perspective. I also export joined forms of this data - one sheet per habit with all the habit particulars/events/notes for that habit in the one place. Trying to do this with CSV would require a zip file with one CSV per sheet. Seems pointless when you know that everyone is just going to import it into Excel anyway!
Export a core data sqlite database as an Excel .xlsx file. The Excel features I needed were quite basic:
My quick search revealed three main options for writing Excel files on iOS.
I decided to go with libxlsxwriter after using its counterpart python library xlsxwriter. Don’t be put off by the library being written in C - it’s super easy to use in your Objective-C project and the API is very clean.
We’ll build libxlsxwriter as a static library project.
Create a new Xcode project. When prompted for the project template, choose iOS -> Library & Framework -> Cocoa Touch Static Library. Name the project
libxlsxwriter-ios (or whatever you like).
xlsxwriter.m files that Xcode has created for you.
Download the latest libxlsxwriter source. Libxlsxwriter requires zlib 1.2.8 but iOS only ships with 1.2.5. Turns out a lot can change in point releases on a ubiquitous open source library, so we need to downgrade the zlib related files in libxlsxwriter. Delete the files in the
include/xlsxwriter/third_party/minizip. Feel free to delete everything else except for the
.c files in
.h files in
include and the
Download the zlib-1.2.5 source. Locate the
contrib/minizip folder. Copy
zip.h folder to the
include/xlsxwriter/third_party/minizip folder. Copy
zip.c to the
third_party/minizip folder. We have successfully downgraded the minizip component of zlib that libxlsxwriter depends on.
Copy the libxlsxwriter source as modified above into your project folder.
src folder to your project. Add a group called
include, and inside it add the contents of the
include folder. Ensure that these files are added as a folder reference in Xcode (blue folder icon). This will ensure later on that the headers are copied to the appropriate subfolder.
The project structure should look like this:
The libxlsxwriter files are all looking for headers in an
xlsxwriter subfolder. To make sure they find them, add the following to your build settings: User Header Search Paths:
$(SRCROOT) will evaluate to the folder containing your
.xcodeproj file. Ensure the header search path is set to recursive. Since the
include folder contains a subfolder
xlsxwriter, adding this search path means that
#include statements of the form
#include "xlsxwriter/workbook.h" will succeed.
If you try to build the project now, you’ll get complaints about
fopen64 and some crypto files. Adding
USE_FILE32API=1 NOCRYPT=1 NOUNCRYPT=1 to your build settings preprocessor macros will make these complaints go away. Essentially we are telling minizip to use 32 bit file access function names and not to bother including any crypto.
Set the copy files phase under Build Phases to copy
xlsxwriter.h and the
xlsxwriter folder (blue folder reference inside the
include folder) to the Products Directory with an
include subpath. This ensures that when your static library project is built as part of a main project that it copies its headers to a location accessible to that project. (You will need to add this folder as a header search path in your master project).
Hit the build button. Everything should now work. You’ll get warnings from some of the C files. If these annoy you, you can add a
-w to the compiler flags setting for these files under Build Phases -> Compile Sources.
Now that we have a project building a static library, we can easily integrate it with another project. I recommend the subproject approach. I may write an article about it at some point, but in the mean time please see Easy Xcode Static Library Subprojects and Submodules. Note that since libxlsxwriter needs zlib, you’ll need to add zlib-1.2.5.dylib to your linker settings.
You can find the docs for libxlsxwriter here. You’ll probably also want to write some kind of Excel exporter class to handle the translation between
NSManagedObject instances and your spreadsheet.
Finally, if you’re on a mac and don’t have a decent version of Excel installed, I found the free web version to be quite good for testing basic files. You can use it at office.com.