January 16, 2014

Generate Excel Spreadsheets in Native Mobile Apps

By

One of the benefits of developing native apps is having access to the full processing power of the platform. This means you can do interesting things like embed an entire spreadsheet processing engine inside your app.

The FlexCel component is a powerful library that allows Excel files to be manipulated from Xamarin.iOS, Xamarin.Mac and Xamarin.Android applications. With FlexCel, you get the power to read and write Excel files from C# code, as well as export them to pdf, images or html. There’s even a high level reporting engine, and a utility application called ApiMate that will generate code from an existing spreadsheet. In this post we’ll look at how to get started with this very impressive component.

Numbers

For this scenario, we’ll capture location data, which we’ll use to generate a spreadsheet. We’ll create an Excel file at the following path, which can then be previewed or sent as an email attachment for example:

path = Path.Combine (Environment.GetFolderPath (
  Environment.SpecialFolder.Personal), "locations.xlsx");

FlexCel uses the XlsFile class to model an Excel file. For example, the following code instantiates an XlsFile object with a single sheet:

var xls = new XlsFile (1, true);

XlsFile contains methods to manipulate the spreadsheet. The most basic operation is to add a value to a cell, which we can do with the SetCellValue method. Let’s use SetCellValue to add a title cell along with some column headers for the latitude, longitude and altitude data that we’re going to capture:

xls.SetCellValue (1, 1, "Location data from Xamarin.iOS");
xls.SetCellValue (2, 1, "Latitude");
xls.SetCellValue (2, 2, "Longitude");
xls.SetCellValue (2, 3, "Altitude");

Similarly, we can use SetCellValue to write the actual location data:

locations.ForEach (l => {
  xls.SetCellValue (++row, 1, l.Coordinate.Latitude);
  xls.SetCellValue (row, 2, l.Coordinate.Longitude);
  xls.SetCellValue (row, 3, l.Altitude);
});

The location data is captured using the Core Location framework. A discussion of how to use Core Location is beyond the scope of this post. For the Core Location source used in this example, see the LocationHelper class in the solution that accompanies this post.

In addition to data, we can control other spreadsheet features such as cell layout and formatting.

For example, we can merge the title cell across 3 columns:

xls.MergeCells(1, 1, 1, 3);

We can also format cells using the TFlxFormat class. For example, we can create a TFlxFormat instance to manipulate a cell’s font, fill style and alignment as follows:

TFlxFormat cellFormat = xls.GetCellVisibleFormatDef (1, 1);
cellFormat.Font.Size20 = 240;
cellFormat.Font.Color = TExcelColor.FromTheme (TThemeColor.Accent1);
cellFormat.Font.Style = TFlxFontStyles.Bold;
cellFormat.FillPattern.Pattern = TFlxPatternStyle.Solid;
cellFormat.FillPattern.FgColor = TExcelColor.FromTheme(TThemeColor.Accent3, 0.6);
cellFormat.VAlignment = TVFlxAlignment.center;
cellFormat.HAlignment = THFlxAlignment.center;

Then we simply call SetCellFormat to apply the format to a cell:

xls.SetCellFormat (1, 1, xls.AddFormat (cellFormat));

To save the Excel file, we call the XlsFile’s Save method with the path specified earlier:

xls.Save (path);

Once we have the file we can do typical things such as preview it or send it as an email attachment:

FlexCel2

You can get the source code for this post from my github repo, along with a trial of FlexCel in the Xamarin Component Store.

Discuss this blog post in the Xamarin Forums

TwitterFacebookGoogle+LinkedInEmail