Skip to content

Instantly share code, notes, and snippets.

@kenegozi
Created September 7, 2012 07:06
Show Gist options
  • Save kenegozi/3664011 to your computer and use it in GitHub Desktop.
Save kenegozi/3664011 to your computer and use it in GitHub Desktop.
Editing Azure Mobile Service data using Excel
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
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment