Using Excel with SSDS
Each year at the middle to end of November my wife starts talking about holiday cards. Its the same thing each year. Need to go get the girls dresses (BTW I have 4 young daughters). Need to make an appointment with the photographer...And then comes my favorite question."Hey can you update the Holiday card spreadsheet and print labels?" That in and of itself is a task.Who's moved? Who needs to be added? and so on...but my favorite part is figuring out where I put the spreadsheet.Ok, I know being a Microsoft nerd and all I should have SharePoint setup or at a minimum a file share somewhere to keep such important documents, and to be honest I do, a file share that is...but I use that for all our media (I am a Media Center junkie.more on that in a later post). For some reason I have a mental block against loading this spreadsheet up on the server. As I said, I am a big nerd, so there are computers all over the house. Which one has a copy of the spreadsheet and <GASP> which one has the current copy?
I think you can see where I am going here..
One of the early demo apps I wrote was an Excel Add-In to store excel files up in SSDS. Since Jason and Jeff have been showing off some code goodies, I figured I would so the same so I could be part of the cool kids club.
Lets start by taking a quick walk through of the add-in.
The add-in lives in the data tab of Excel and adds two buttons.
Upon clicking either button, a panel is shown which lets you load or retrieve data into, or out of, SSDS.
With this add-in I can use my extremely powerful friend Excel to transfer data into and out of SSDS.Pretty cool right?
So how is it done?
There are plenty of articles out on the World Wide InterWeb :) showing how to create add-ins, so I am really not going to touch upon that here, but if interested you can check out the Visual Studio Tools for Office Developer Portal
Lets first look at the retrieval.
The user is required to enter in their Authority, User Name and Password. Once we have that, we validate the user and populate the Container dropdown with a list of all the containers in the authority.
1: public static bool ValidateAuthority(String AuthorityName, String UserName, String Password)
2: { 3: if ((AuthorityName.Trim().Length > 0) && (UserName.Trim().Length > 0) && (Password.Trim().Length > 0))
4: { 5: string authURI = Uri.EscapeUriString(string.Format("{0}?",AuthorityName)); 6:
7: try
8: { 9:
10: WebRequest request = CreateRequest(authURI, HttpMethods.GET, String.Empty, XmlContentType, UserName,Password);
11: using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
12: { 13: string data = ReadResponse(response);
14: if (response.StatusCode != HttpStatusCode.OK)
15: { 16: string errorText = ReadResponse(response);
17: MessageBox.Show(string.Format("Unexpected status code returned: {0} \n\n {1}", response.StatusCode, errorText), "Error Validating Authority", MessageBoxButtons.OK, MessageBoxIcon.Error); 18:
19: return false;
20: }
21: else
22: { 23: return true;
24: }
25: }
26: }
27: catch (WebException ex)
28: { 29: HttpWebResponse response = ex.Response as HttpWebResponse;
30: if (response != null)
31: { 32: string errorText = ReadResponse(response);
33: MessageBox.Show(string.Format("Unexpected status code returned: {0} \n\n {1}", response.StatusCode, errorText), "Error Validating Authority", MessageBoxButtons.OK, MessageBoxIcon.Error); 34: }
35: else
36: { 37: MessageBox.Show("Unable to validate Authority. Check to see that it is valid", "Error Validating Authority", MessageBoxButtons.OK, MessageBoxIcon.Error); 38: }
39: return false;
40: }
41:
42: }
43: else
44: { 45: MessageBox.Show("Invalid Authority, Username or Password", "Error Validating Authority", MessageBoxButtons.OK, MessageBoxIcon.Error); 46: return false;
47: }
48: }
1:
2: public static String[] FetchContainersForAuthority(String AuthorityName, String UserName, String Password)
3: { 4: List<string> lContainers = new List<string>();
5:
6: try
7: { 8: string queryURI = Uri.EscapeUriString(string.Format(@"{0}?q=''", AuthorityName)); 9: WebRequest request = CreateRequest(queryURI, HttpMethods.GET, String.Empty, XmlContentType, UserName, Password);
10: using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
11: { 12: string data = ReadResponse(response);
13:
14: XmlDocument containerESet = new XmlDocument();
15: containerESet.LoadXml(data);
16:
17: XmlNodeList elemList = containerESet.GetElementsByTagName("s:Id"); 18:
19: foreach (XmlNode xNodeContainer in elemList)
20: { 21: lContainers.Add(xNodeContainer.InnerText);
22: }
23: }
24: }
25: catch (UriFormatException)
26: { 27: MessageBox.Show(string.Format("Bad URI format: {0}", AuthorityName), "Invalid Options", MessageBoxButtons.OK, MessageBoxIcon.Error); 28: }
29: catch (WebException ex)
30: { 31: HttpWebResponse response = ex.Response as HttpWebResponse;
32: if (response != null)
33: { 34: if (response.StatusCode == HttpStatusCode.NotFound)
35: { 36: //return false;
37: }
38: else
39: { 40: string errorText = ReadResponse(response);
41: MessageBox.Show(string.Format("Unexpected status code returned: {0} \n\n {1}", response.StatusCode, errorText), "Error Validating Container", MessageBoxButtons.OK, MessageBoxIcon.Error); 42: }
43: }
44: else
45: { 46: MessageBox.Show("An error has occured.", "Error Validating Container", MessageBoxButtons.OK, MessageBoxIcon.Error); 47: }
48:
49: }
50:
51: return lContainers.ToArray();
52:
53: }
The user then is able to select a container and click retrieve (BTW I am not happy with this implemetation's use of a DataTable and will be re-factoring it in the future)
1: public static DataTable GetEntitysInContainer(String AuthorityName, String SheetContainer, String UserName, String Password)
2: { 3: DataTable dtEntity = null;
4: string queryUri = Uri.EscapeUriString(string.Format(@"{0}{1}?q=''", AuthorityName, SheetContainer)); 5:
6: try
7: { 8: WebRequest request = CreateRequest(queryUri, HttpMethods.GET, String.Empty, XmlContentType, UserName, Password);
9: using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
10: { 11: string data = ReadResponse(response);
12:
13: if (response.StatusCode != HttpStatusCode.OK)
14: { 15: MessageBox.Show(String.Format("Unexpected status code returned: {0}", response.StatusCode), 16: "Operation Failed");
17: }
18: else
19: { 20: dtEntity = new DataTable();
21: XmlDocument entitySet = new XmlDocument();
22: entitySet.LoadXml(data);
23:
24: dtEntity.Columns.Add("Id"); 25: foreach (XmlNode entityNode in entitySet.FirstChild.ChildNodes)
26: { 27: // Check for custom Kind usage
28: if (entityNode.Name != Constants.DefaultItemKind && !dtEntity.Columns.Contains("Kind")) 29: { 30: dtEntity.Columns.Add("Kind"); 31: }
32:
33: foreach (XmlNode props in entityNode.ChildNodes)
34: { 35: if (!props.Name.StartsWith("s:")) 36: { 37: if (!dtEntity.Columns.Contains(props.Name))
38: { 39: dtEntity.Columns.Add(props.Name);
40: }
41: }
42: }
43: }
44:
45:
46: foreach (XmlNode entityNode in entitySet.FirstChild.ChildNodes)
47: { 48: DataRow drEntity = dtEntity.NewRow();
49:
50: // Check for custom kind value
51: if (entityNode.Name != Constants.DefaultItemKind)
52: { 53: drEntity["Kind"] = entityNode.Name;
54: }
55:
56: foreach (XmlNode props in entityNode.ChildNodes)
57: { 58: string propName = props.Name;
59: if (propName == "s:Id")
60: { 61: propName = "Id";
62: }
63:
64: if (!propName.StartsWith("s:")) 65: { 66: if (props.Attributes != null && props.Attributes["xsi:type"] != null)
67: { 68: if (props.Attributes["xsi:type"].InnerText == "x:dateTime")
69: { 70: drEntity[propName] = Convert.ToDateTime(props.InnerText).ToShortDateString();
71: }
72: else
73: { 74: drEntity[propName] = props.InnerText;
75: }
76: }
77: else
78: { 79: drEntity[propName] = props.InnerText;
80: }
81: }
82: }
83:
84: dtEntity.Rows.Add(drEntity);
85: }
86: }
87:
88: response.Close();
89: }
90: }
91: catch (WebException ex)
92: { 93: HttpWebResponse response = ex.Response as HttpWebResponse;
94: MessageBox.Show(String.Format("Unexpected status code returned: {0}", response.StatusCode), 95: "Operation Failed");
96: }
97:
98: return dtEntity;
99: }
End result is this, and for clarity I also show one of the entities directly from SSDS
1: - <ExcelRow>
2: <s:Id>Row0002</s:Id>
3: <s:Version>1</s:Version>
4: <FName xsi:type="x:string">Abbie</FName>
5: <LName xsi:type="x:string">Dougherty</LName>
6: <Sex xsi:type="x:string">Female</Sex>
7: <JobTitle xsi:type="x:string">Financial Analyst</JobTitle>
8: <Department xsi:type="x:string">Accounting</Department>
9: <Location xsi:type="x:string">New York</Location>
10: <PhoneNo xsi:type="x:string">212-600-3880</PhoneNo>
11: </ExcelRow>
That's pretty cool, but it doesn't show off SSDS's data type's other than string. Let look at the upload and make sure we call out some other, more interesting data type :)
For the upload, the first thing we do is check to see if the container exists, and if not, we create it.
1: public static bool CheckContainerExists(string AuthorityName, string ContainerName, string UserName, string Password)
2: { 3:
4: string queryURI = Uri.EscapeUriString(string.Format(@"{0}{1}?q=''", AuthorityName, ContainerName)); 5:
6: try
7: { 8:
9: WebRequest request = CreateRequest(queryURI, HttpMethods.GET, String.Empty, XmlContentType, UserName, Password);
10: using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
11: { 12: string data = ReadResponse(response);
13: if (response.StatusCode != HttpStatusCode.OK)
14: { 15: return false;
16: }
17: else
18: { 19: return true;
20: }
21: }
22: }
23: catch (WebException ex)
24: { 25: HttpWebResponse response = ex.Response as HttpWebResponse;
26: if (response != null)
27: { 28: if (response.StatusCode == HttpStatusCode.NotFound)
29: { 30: return false;
31: }
32: else
33: { 34: string errorText = ReadResponse(response);
35: MessageBox.Show(string.Format("Unexpected status code returned: {0} \n\n {1}", response.StatusCode, errorText), "Error Validating Container", MessageBoxButtons.OK, MessageBoxIcon.Error); 36: }
37: }
38: else
39: { 40: MessageBox.Show("An error has occured.", "Error Validating Container", MessageBoxButtons.OK, MessageBoxIcon.Error); 41: }
42:
43: return false;
44: }
45: }
1: public static bool CreateContainer(string AuthorityName, string ContainerName, string UserName, string Password)
2: { 3: string containerPayload = CreateSheetContainerPayload(ContainerName);
4:
5: try
6: { 7: string createUri = Uri.EscapeUriString(AuthorityName);
8:
9: HttpWebRequest request = CreateRequest(createUri, HttpMethods.POST, containerPayload, XmlContentType, UserName, Password) as HttpWebRequest;
10:
11: using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
12: { 13: string responseBody = ReadResponse(response);
14: if (response.StatusCode != HttpStatusCode.Created)
15: { 16: response.Close();
17: MessageBox.Show(string.Format("An error has occured. /n/n{0}", responseBody), "Error Creating Container", MessageBoxButtons.OK, MessageBoxIcon.Error); 18: return false;
19: }
20:
21: response.Close();
22: }
23:
24: return true;
25: }
26: catch (WebException ex)
27: { 28: HttpWebResponse resp = ex.Response as HttpWebResponse;
29: string errorMsg = ReadResponse(resp);
30:
31: return false;
32: }
33: }
CreateContainer calls a method CreateSheetContainerPayload which returns the payload for the Container Create..I should also mention at this point that the Add-In allows the user to specify a container name and if not specified, it will default to the sheet name.
1: public static string CreateSheetContainerPayload(string SheetName)
2: { 3: const string ContainerTemplate =
4: @"<s:Container xmlns:s='http://schemas.microsoft.com/sitka/2008/03/'>
5: <s:Id>{0}</s:Id> 6: </s:Container>";
7:
8: return String.Format(ContainerTemplate, SheetName);
9: }
Now we loop through each row and build our entities.We choose the FlexProperty datatype depending on the data type in the cell..
1: if (numberFormat != "General")
2: { 3:
4: //we need to do this check here becuase in excel if you once had a date in the column the fomat might still be
5: //a date format even though there is not a date in the cell...
6: if (CheckForInt(cellValue))
7: { 8: entityPayload.AppendLine(string.Format(@"<{0} xsi:type='x:dateTime'>{1}</{2}>", ColumnHeaders[y - 1], ConvertSerialtoDateTime(Convert.ToInt32(cellValue)).ToString(), ColumnHeaders[y - 1])); 9: }
10: else
11: { 12: entityPayload.AppendLine(string.Format(@"<{0} xsi:type='x:string'>{1}</{2}>", ColumnHeaders[y - 1], cellValue, ColumnHeaders[y - 1])); 13: }
14:
15: }
16: else
17: { 18: if ((cellValue.ToLower() == "true") || (cellValue.ToLower() == "false"))
19: { 20: entityPayload.AppendLine(string.Format(@"<{0} xsi:type='x:boolean'>{1}</{2}>", ColumnHeaders[y - 1], cellValue, ColumnHeaders[y - 1])); 21: }
22: else if (CheckForInt(cellValue))
23: { 24: entityPayload.AppendLine(string.Format(@"<{0} xsi:type='x:decimal'>{1}</{2}>", ColumnHeaders[y - 1], cellValue, ColumnHeaders[y - 1])); 25: }
26: else if (cellValue.StartsWith(@"file:"))
27: { 28: //read image and convert
29: string filePath = cellValue.Substring(5);
30: string binFileContents = ReadImage(filePath);
31: string MimeType;
32:
33: using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
34: { 35: using (Image photo = Image.FromStream(fs, true, false))
36: { 37: MimeType = GetMimeType(photo);
38: }
39: }
40:
41: entityPayload.AppendLine(string.Format(@"<{0} xsi:type='x:base64Binary'>{1}</{2}>", ColumnHeaders[y - 1], binFileContents, ColumnHeaders[y - 1])); 42: entityPayload.AppendLine(string.Format(@"<{0}MimeType xsi:type='x:string'>{1}</{2}MimeType>", ColumnHeaders[y - 1], MimeType, ColumnHeaders[y - 1])); 43: }
44: else
45: { 46: entityPayload.AppendLine(string.Format(@"<{0} xsi:type='x:string'>{1}</{2}>", ColumnHeaders[y - 1], cellValue, ColumnHeaders[y - 1])); 47: }
I wanted to show off the binary datatype, so I added the file: semantics to pull an image from the file system, pull the MimeType and add it as an additional FlexProperty.
All that is left is to do the POST
1: string createUri = String.Format("{0}{1}", AuthorityName, ContainerName); 2:
3: WebRequest request = CreateRequest(createUri, HttpMethods.POST, EntityPayload, XmlContentType, UserName, Password);
4:
5: using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
6: { 7: string responseBody = ReadResponse(response);
8: if (response.StatusCode != HttpStatusCode.Created)
9: { 10: response.Close();
11: return false;
12: }
13:
14: response.Close();
15: }
16:
17: return true;
A couple points I want to make here.
I am not saying people should store all their Excel documents in SSDS, but what I am looking to convey is that if you want an easy way to pull some data from SSDS, modify it, and put it back, Excel is a great tool for that.
Additionally all the Office Solution Developers out there, SSDS is a great way to offer offsite backup features from within your applications. This is also a great example of a rich local client application consuming data in the cloud.
For my next trick, I am going to walk down the hall and get with the Sync Framework team who has written some really cool Sync stuff using SSDS..but that is going to have to wait for another post.
-Dave
