Editing Azure Mobile Service data using Excel

EditMyMobileServiceDataWithExcel.cs

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Windows.Forms;
using Microsoft.Office.Core;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelAddIn1 {
	public partial class ThisAddIn {
		// get that stuff from your mobile service's dashboard
		private static readonly MobileServices.Sdk.MobileServiceClient Client = new MobileServices.Sdk.MobileServiceClient(
			"https://MYSERVICE.azure-mobile.net/",
			"APPLICATION_KEY"
			);

		// keep score of sheets that are being used as data editors
		readonly HashSet<Excel.Worksheet> activatedSheets = new HashSet<Excel.Worksheet>();

		// UI elements - Excel require that the addin keeps reference to them
		CommandBarButton deleteButton, getDataButton, newButton;
		CommandBar mainMenu;

		private void ThisAddIn_Startup(object sender, System.EventArgs e) {

			// add the menu bar
			mainMenu = Application.CommandBars.Add(
				"MobileServiceAdmin", MsoBarPosition.msoBarTop, false, true);

			mainMenu.Name = "Mobile Services";

			getDataButton = (CommandBarButton)mainMenu.Controls.Add(
				MsoControlType.msoControlButton);
			getDataButton.Caption = "Load table data";
			getDataButton.FaceId = 1126;
			getDataButton.Click += delegate { LoadTable(); };

			deleteButton = (CommandBarButton)mainMenu.Controls.Add(
				MsoControlType.msoControlButton);
			deleteButton.Caption = "Delete record";
			deleteButton.FaceId = 4100;
			deleteButton.Click += delegate { DeleteRecord(); };

			newButton = (CommandBarButton)mainMenu.Controls.Add(
				MsoControlType.msoControlButton);
			newButton.Caption = "New record";
			newButton.FaceId = 137;
			newButton.Click += delegate { InsertRecord(); };

			mainMenu.Visible = true;

			// apply edits when data on the sheet changes
			Application.SheetChange += Application_SheetChange;
		}

		private void DeleteRecord() {
			var cell = Application.ActiveCell;
			var sheet = (Excel.Worksheet)Application.ActiveSheet;
			var id = GrabId(cell, sheet);
			if (id == null) {
				MessageBox.Show("cannot delete a record without Id (on column A)");
				return;
			}

			var message = string.Format("About to delete record with id#" + id + "\r\nClick OK to proceed");

			var result = MessageBox.Show(message, "Deleting ...", MessageBoxButtons.OKCancel);
			if (result == DialogResult.Cancel) {
				return;
			}

			var table = Client.GetTable(sheet.Name);
			table.Delete(id, (Action<Exception>)(err => {
				if (err != null) {
					MessageBox.Show("Could not delete\r\n" + err.ToString());
					return;
				}
				cell.EntireRow.Delete();
				MessageBox.Show("deleted");
			}));
		}

		private void InsertRecord() {
			var cell = Application.ActiveCell;
			var sheet = (Excel.Worksheet)Application.ActiveSheet;
			var id = GrabId(cell, sheet);
			if (id != null) {
				MessageBox.Show("cannot insert a record with Id (on column A)");
				return;
			}
			var fields = GetFields()
				.Skip(1)             // not saving ID
				.ToList();

			var currentCell = GetIdCell(cell, sheet);
			var newItem = new JObject();
			foreach (var field in fields) {
				var name = field.Item1;
				var type = field.Item2;
				currentCell = currentCell.Offset[0, 1];
				var value = currentCell.Value2;
				if (string.IsNullOrEmpty(type)) {
					DateTime val;
					if (DateTime.TryParse(currentCell.Text.ToString(), CultureInfo.CurrentUICulture, DateTimeStyles.AssumeLocal, out val)) {
						value = val;
					}
				}
				else if (type == JTokenType.Date.ToString()) {
					value = DateTime.FromOADate((double)value);
				}
				newItem[name] = value;
			}

			var message = string.Format("About to insert record: {0}\r\nClick OK to proceed", newItem.ToString(Formatting.Indented));

			var result = MessageBox.Show(message, "Inserting ...", MessageBoxButtons.OKCancel);
			if (result == DialogResult.Cancel) {
				return;
			}

			var table = Client.GetTable(sheet.Name);
			table.Insert(newItem, (res, err) => {
				if (err != null) {
					MessageBox.Show("Could not insert\r\n" + err.ToString());
					return;
				}
				var newObject = JObject.Parse(res);
				GetIdCell(cell, sheet).Value2 = newObject["id"].ToString();
				MessageBox.Show(res);
			});

		}

		private bool bypass;

		void Application_SheetChange(object sender, Excel.Range changed) {
			// this s true while we are undo-ing to keep the original value
			if (bypass)
				return;

			// not supporting multi-cell change
			if (changed.Cells.Count > 1) {
				return;
			}

			// not supporting if (first column) cell change
			if (changed.Cells.Count > 1) {
				return;
			}

			var sheet = (Excel.Worksheet)sender;

			// if it is not an editing sheet, go away
			if (activatedSheets.Contains(sheet) == false) {
				return;
			}

			dynamic old = null;
			dynamic value = changed.Value2;
			// store original value
			try {
				bypass = true;
				Application.Undo();
				old = changed.Value2;
				changed.Value2 = value;
			}
			catch (Exception) {
				MessageBox.Show("error = could not retrieve original value");
				if (old != null)
					changed.Value2 = old;
				return;
			}
			finally {
				bypass = false;
			}

			var initialStatus = Application.StatusBar;
			try {
				// we grab the record Id from the first column, and
				// the field's name and type from the first row
				var id = GrabId(changed, sheet);
				if (id == null) {
					return;
				}
				Application.StatusBar = "updating item with id#" + id;
				var field = GetFieldNameAndType(changed);
				string type = field.type;
				string fieldName = field.name;
				// we need special handling for dates as excel store them
				// in that wacky format
				if (type == JTokenType.Date.ToString()) {
					value = DateTime.FromOADate((double)value);
				}

				Application.StatusBar = string.Format("saving {0} of type {1} to column [{2}]", value, type, fieldName);

				// enough with Excel, lets do some Azure Mobile Services!
				var table = Client.GetTable(sheet.Name);
				var updates = new JObject();
				updates["id"] = id;
				updates[fieldName] = value;

				// Update sends over only the fields that have changed
				table.Update(updates, err => {
					Application.StatusBar = initialStatus;
					if (err != null) {
						MessageBox.Show("err:\r\n" + err);
						changed.Value2 = old;
						return;
					}
					MessageBox.Show("updated");
				});
			}
			catch (Exception ex) {
				MessageBox.Show("err:\r\n" + ex);
			}
			finally {
				Application.StatusBar = initialStatus;
			}
		}

		void LoadTable() {
			var initialStatus = Application.StatusBar;

			var sheet = (Excel.Worksheet)Application.ActiveSheet;
			activatedSheets.Remove(sheet);

			var tableName = sheet.Name;
			Application.StatusBar = "loading from [" + tableName + "]";
			var table = Client.GetTable(tableName);
			sheet.UsedRange.Clear();

			// cheating - this will only bring the first page of data
			// an enhanced version will come up soon
			table.GetAll((arr, err) => {
				Application.StatusBar = initialStatus;
				if (err != null) {
					MessageBox.Show("err:\r\n" + err);
					return;
				}
				if (arr.Count == 0) {
					MessageBox.Show("empty");
					return;
				}

				Application.StatusBar = "setting headers";
				var current = sheet.Range["A1"];
				// store the field names and types on the first line
				foreach (JProperty prop in arr[0]) {
					current.Value2 = prop.Name;
					var type = prop.Value.Type.ToString();
					current.NoteText(Text: type);
					if (type == JTokenType.Date.ToString()) {
						current.EntireColumn.NumberFormat = "dd/mm/yyyy hh:mm:ss";
					}
					current = current.Offset[0, 1];
				}

				for (var i = 0; i < arr.Count; ++i) {
					Application.StatusBar = "writing row#" + (i + 1);
					var row = i + 2;
					var j = 0;
					foreach (JProperty prop in arr[i]) {
						var col = (char)('A' + j++);
						var cellName = col + row.ToString();
						var cell = sheet.Cells.Range[cellName, cellName];
						var value =
							// unless we set dates as strings, their numeric
							// value will show up int the field
							// or else I'll figure out how to properly set
							// the format of the cell
							prop.Value.Type == JTokenType.Date
								? prop.Value.Value<DateTime>().ToString()
								: ((JValue)prop.Value).Value;
						cell.Value2 = value;
					}
				}
				// ready to play
				activatedSheets.Add(sheet);
				Application.StatusBar = initialStatus;
			});
		}

		List<Tuple<string, string>> GetFields() {
			var fields = new List<Tuple<string, string>>();
			var sheet = (Excel.Worksheet)Application.ActiveSheet;
			var fieldsRow = sheet.Range["A1"];
			while (string.IsNullOrWhiteSpace(fieldsRow.Text.ToString()) == false) {
				var name = fieldsRow.Value2;
				var type = fieldsRow.NoteText();
				fields.Add(new Tuple<string, string>(name, type));
				fieldsRow = fieldsRow.Offset[0, 1];
			}
			return fields;
		}

		private static dynamic GetFieldNameAndType(Excel.Range changed) {
			var fieldNameCell = changed.Offset[1 - changed.Row, 0];
			var fieldName = fieldNameCell.Value2.ToString();
			var type = fieldNameCell.NoteText();
			return new { fieldName, type };
		}

		private static dynamic GrabId(Excel.Range changed, Excel.Worksheet sheet) {
			var idCell = GetIdCell(changed, sheet);
			var id = idCell.Value2;
			if (id == null || string.IsNullOrWhiteSpace(id.ToString())) {
				return null;
			}
			return id;
		}

		private static Excel.Range GetIdCell(Excel.Range changed, Excel.Worksheet sheet) {
			var idCellName = "A" + changed.Row.ToString();
			var idCell = sheet.Cells.Range[idCellName, idCellName];
			return idCell;
		}

		private void ThisAddIn_Shutdown(object sender, EventArgs e) {
		}

		#region VSTO generated code

		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InternalStartup() {
			this.Startup += new System.EventHandler(ThisAddIn_Startup);
			this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
		}

		#endregion
	}
}