Bridging the Gap Between Xamarin and Excel

Pave the way from Excel to Xamarin

As many already know, Excel is a fairly common spreadsheet utility offered by Microsoft to organize and manipulate data. If you have previously worked with Excel you may find that maintaining hundreds of spreadsheets can be a cumbersome task. Warehousing, manufacturing, and querying data into an application to visualize it in a meaningful way has never been easier.

This article will guide you on how easy it is to import worksheets into the Infragistics Excel Library to then convert the data into an object that the XamCategoryChart can analyze and automatically display a visualization for using a Xamarin Forms project.

Xamarin.Forms + Infragistics Excel Engine + Infragistics Ultimate UI for Xamarin

The Infragistics Excel Engine seamlessly integrates with Xamarin.Forms to enable you to use excel data in your application. Our data visualization tools provide an efficient way to take your spreadsheet data from computer screen and have it jump to your fingertips.

The XamCategoryChart component, in the latest Infragistics Xamarin tool suite, provides the next big innovation in mobile app development. Data visualization with high performance meets a simplistic, yet rich, API in a cross-platform environment.

Code Less, Offer More

Importing worksheet data First your portable class library project needs to import your Excel Data. You add the file as an embedded resource and create a stream for the file to begin its conversion.

public MainPage() { InitializeComponent(); ExcelData _data; var assembly = typeof(MainPage).GetTypeInfo().Assembly; Stream stream = assembly.

 GetManifestResourceStream("XFCategoryChart.tst1.xls"); _data = GetExcelData(stream); /// (Continued below) } 

Create a Workbook using the Stream. While you have an open stream from the Excel file, the Excel Engine will let you create a Workbook object. It's best to use a an Excel file where the first record is the field headers to store as properties for the XamDataChart.

private ExcelData GetExcelData(Stream streamName) { Workbook workbook1; using (streamName) { workbook1 = Workbook.Load(streamName); } int columnsNumber = 0; string column; List gridColumns = new List(); var worksheet = workbook1.Worksheets[0]; while (worksheet.Rows[0].GetCellValue(columnsNumber) != null) { columnsNumber++; } for (var columnIndex = 0; columnIndex < columnsNumber; 
 columnIndex++) { column = worksheet.Rows[0].GetCellText(columnIndex); gridColumns.Add(column); } /// (Continued below) } 

Create a model to represent your data entities The XamCategoryChart requires the data to be in the form of an object that implements IEnumerable. A Dictionary, from the Systems.Collections.Generic class, can used to store each record. You will need iterate through the Excel file and add new rows

/// (Continued from above) var worksheetRowsCount = worksheet.Rows.Count(); var items = new ExcelData(); for (var i = 1; i < worksheetRowsCount; i++) { var newRow = new Dictionary<string, object="">(); var row = worksheet.Rows[i]; for (var columnIndex = 0; columnIndex < columnsNumber; columnIndex++) { var cellValue = row.GetCellText(columnIndex); double doubleValue; if (double.TryParse(cellValue, out doubleValue)) { newRow[gridColumns[columnIndex]] = doubleValue; } else { newRow[gridColumns[columnIndex]] = cellValue; } } items.Add(newRow); } return items; } public class ExcelData : ObservableCollection<Dictionary<string, object="">> { public void NotifyReset() { OnCollectionChanged(new System.Collections.Specialized.

 NotifyCollectionChangedEventArgs(System.Collections.

 Specialized.NotifyCollectionChangedAction.Reset)); } } 

Add the XamCategoryChart and bind to ExcelData

public MainPage() { InitializeComponent(); … this.BindingContext = _data; } 

Full Code Sample

The following code snippet demonstrates how to acquire the data and make it available to the content page using a Cross-Platform Xamarin Forms project.

public partial class MainPage : ContentPage { public MainPage() { InitializeComponent(); ExcelData _data; var assembly = typeof(MainPage).GetTypeInfo().Assembly; Stream stream = assembly.

 GetManifestResourceStream("XFCategoryChart.tst1.xls"); string text = ""; using (var reader = new System.IO.StreamReader(stream)) { text = reader.ReadToEnd(); _data = GetExcelData(stream); } this.BindingContext = _data; } private ExcelData GetExcelData(Stream streamName) { Workbook workbook1; using (streamName) { workbook1 = Workbook.Load(streamName); } int columnsNumber = 0; string column; List gridColumns = new List(); var worksheet = workbook1.Worksheets[0]; while (worksheet.Rows[0].GetCellValue(columnsNumber) != null) { columnsNumber++; } for (var columnIndex = 0; columnIndex < columnsNumber; 
 columnIndex++) { column = worksheet.Rows[0].GetCellText(columnIndex); gridColumns.Add(column); } var worksheetRowsCount = worksheet.Rows.Count(); var items = new ExcelData(); for (var i = 1; i < worksheetRowsCount; i++) { var newRow = new Dictionary<string, object="">(); var row = worksheet.Rows[i]; for (var columnIndex = 0; columnIndex < columnsNumber; 

 columnIndex++) { var cellValue = row.GetCellText(columnIndex); double doubleValue; if (double.TryParse(cellValue, out doubleValue)) { newRow[gridColumns[columnIndex]] = doubleValue; } else { newRow[gridColumns[columnIndex]] = cellValue; } } items.Add(newRow); } return items; } } public class ExcelData : ObservableCollection<Dictionary<string, object="">> { public void NotifyReset() { OnCollectionChanged(new System.Collections.Specialized.
 NotifyCollectionChangedEventArgs(System.Collections.Specialized.
 NotifyCollectionChangedAction.Reset)); } } 

Now with continuous delivery of Ultimate UI for Xamarin with our private NuGet feed, you get more capabilities and controls faster than ever. Download a trial today to get started, and then check out the Write Fast and Run Fast lessons that will have you creating cross-platform Xamarin.Forms mobile apps in no time.