public static async Task ReplyWithValue(IDialogContext context, string workbookId, string worksheetId, string cellAddress)
{
try
{
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var rangeRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Worksheets[worksheetId].Range(cellAddress).Request(headers);
var range = await rangeRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, rangeRequest);
if ((string)(range.ValueTypes[0][0]) != "Empty")
{
await context.PostAsync($"**{cellAddress}** is **{range.Text[0][0]}**");
}
else
{
await context.PostAsync($"**{cellAddress}** is empty");
}
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong getting the value of **{cellAddress}** ({ex.Message})");
}
}
public static async Task SetCellValue(IDialogContext context, string workbookId, string worksheetId, string cellAddress, object value)
{
try
{
var newValue = new WorkbookRange()
{
Values = JToken.Parse($"[[\"{value}\"]]")
};
var headers = ServicesHelper.GetWorkbookSessionHeader(
await ExcelHelper.GetSessionIdForUpdateAsync(context));
var updateRangeRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Worksheets[worksheetId]
.Range(cellAddress).Request(headers);
var range = await updateRangeRequest.PatchAsync(newValue);
await ServicesHelper.LogGraphServiceRequest(context, updateRangeRequest, newValue);
await context.PostAsync($"**{cellAddress}** is now **{range.Text[0][0]}**");
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong setting the value of **{cellAddress}** to **{value.ToString()}** ({ex.Message})");
}
}
public static async Task ReplyWithValue(IDialogContext context, string workbookId, WorkbookNamedItem namedItem)
{
try
{
switch (namedItem.Type)
{
case "Range":
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var namedItemRangeRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Names[namedItem.Name].Range().Request(headers);
var range = await namedItemRangeRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, namedItemRangeRequest);
if ((range.RowCount == 1) && (range.ColumnCount == 1))
{
// Named item points to a single cell
if ((string)(range.ValueTypes[0][0]) != "Empty")
{
await context.PostAsync($"**{namedItem.Name}** is **{range.Text[0][0]}**");
}
else
{
await context.PostAsync($"**{namedItem.Name}** is empty");
}
}
else
{
// Named item points to a range with multiple cells
var reply = $"**{namedItem.Name}** has these values:\n\n{GetRangeReply(range)}";
await context.PostAsync(reply);
}
break;
case "String":
case "Boolean":
case "Integer":
case "Double":
await context.PostAsync($"**{namedItem.Name}** is **{namedItem.Value}**");
break;
default:
await context.PostAsync($"Sorry, I am not able to determine the value of **{namedItem.Name}** ({namedItem.Type}, {namedItem.Value})");
break;
}
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong getting the value of **{namedItem.Name}** ({ex.Message})");
}
}
public static async Task DoOpenWorkbookAsync(IDialogContext context, string workbookName)
{
try
{
// Add extension to filename, if needed
var filename = workbookName.ToLower();
if (!(filename.EndsWith(".xlsx")))
{
filename = $"{filename}.xlsx";
}
// Get meta data for the workbook
var itemRequest = ServicesHelper.GraphClient.Me.Drive.Root.ItemWithPath(filename).Request();
var item = await itemRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, itemRequest);
context.UserData.SetValue("WorkbookId", item.Id);
context.ConversationData.SetValue("WorkbookName", item.Name);
context.ConversationData.SetValue("WorkbookWebUrl", item.WebUrl);
context.UserData.RemoveValue("Type");
context.UserData.RemoveValue("Name");
context.UserData.RemoveValue("CellAddress");
context.UserData.RemoveValue("TableName");
context.UserData.RemoveValue("RowIndex");
// Get the first worksheet in the workbook
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var worksheetsRequest = ServicesHelper.GraphClient.Me.Drive.Items[item.Id]
.Workbook.Worksheets.Request(headers).Top(1);
var worksheets = await worksheetsRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, worksheetsRequest);
context.UserData.SetValue("WorksheetId", worksheets[0].Name);
// Respond
await context.PostAsync($"We are ready to work with **{worksheets[0].Name}** in {ExcelHelper.GetWorkbookLinkMarkdown(context)}");
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong when I tried to open the **{workbookName}** workbook on your OneDrive for Business ({ex.Message})");
}
}
public static async Task DoSelectWorksheetAsync(IDialogContext context, string worksheetName)
{
try
{
var workbookId = context.UserData.GetValue <string>("WorkbookId");
var worksheetId = context.UserData.GetValue <string>("WorksheetId");
// Check if we are already working with the new worksheet
if (worksheetName.ToLower() == worksheetId.ToLower())
{
await context.PostAsync($"We are already working with the **{worksheetId}** worksheet");
return;
}
// Check if the new worksheet exist
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var worksheetsRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Worksheets.Request(headers);
var worksheets = await worksheetsRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, worksheetsRequest);
var lowerWorksheetName = worksheetName.ToLower();
var worksheet = worksheets.FirstOrDefault(w => w.Name.ToLower() == lowerWorksheetName);
if (worksheet == null)
{
await context.PostAsync($@"**{worksheetName}** is not a worksheet in the workbook. Type ""select worksheet"" to select the worksheet from a list");
return;
}
// Save the worksheet id
context.UserData.SetValue <string>("WorksheetId", worksheet.Name);
// Respond
await context.PostAsync($"We are ready to work with the **{worksheet.Name}** worksheet");
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong selecting the {worksheetName} worksheet ({ex.Message})");
}
}
public static async Task DoListCharts(IDialogContext context)
{
var workbookId = context.UserData.GetValue <string>("WorkbookId");
var worksheetId = context.UserData.GetValue <string>("WorksheetId");
try
{
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var chartsRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Worksheets[worksheetId].Charts.Request(headers);
var charts = await chartsRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, chartsRequest);
if (charts.Count > 0)
{
var reply = new StringBuilder();
if (charts.Count == 1)
{
reply.Append($"There is **1** chart on **{worksheetId}**:\n");
}
else
{
reply.Append($"There are **{charts.Count}** on **{worksheetId}**:\n");
}
foreach (var chart in charts)
{
reply.Append($"* **{chart.Name}**\n");
}
await context.PostAsync(reply.ToString());
}
else
{
await context.PostAsync($"There are no charts on {worksheetId}");
}
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong getting the charts ({ex.Message})");
}
}
public static async Task DoListNamedItems(IDialogContext context)
{
var workbookId = context.UserData.GetValue <string>("WorkbookId");
try
{
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var namedItemsRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Names.Request(headers);
var namedItems = await namedItemsRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, namedItemsRequest);
if (namedItems.Count > 0)
{
var reply = new StringBuilder();
if (namedItems.Count == 1)
{
reply.Append($"There is **1** named item in the workbook:\n");
}
else
{
reply.Append($"There are **{namedItems.Count}** named items in the workbook:\n");
}
foreach (var namedItem in namedItems)
{
reply.Append($"* **{namedItem.Name}**\n");
}
await context.PostAsync(reply.ToString());
}
else
{
await context.PostAsync($"There are no named items in the workbook");
}
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong getting the named items ({ex.Message})");
}
}
public static async Task ReplyWithTableRow(IDialogContext context, string workbookId, WorkbookTable table, JToken row)
{
// Convert JToken
var rowVals = JsonConvert.DeserializeObject <object[]>(row.ToString());
if ((bool)(table.ShowHeaders))
{
// Get the table header
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var headerRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[table.Id].HeaderRowRange().Request(headers);
var header = await headerRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, headerRequest);
var reply = new StringBuilder();
var separator = "";
for (var i = 0; i < rowVals.Length; i++)
{
if ((rowVals[i] != null) && (((string)rowVals[i]) != string.Empty))
{
reply.Append($"{separator}* {header.Text[0][i]}: **{rowVals[i]}**");
separator = "\n";
}
}
await context.PostAsync(reply.ToString());
}
else
{
var reply = new StringBuilder();
var separator = "";
for (var i = 0; i < rowVals.Length; i++)
{
reply.Append($"{separator}* **{rowVals[i]}**");
separator = "\n";
}
await context.PostAsync(reply.ToString());
}
}
public static async Task DoListWorksheetsAsync(IDialogContext context)
{
var workbookId = context.UserData.GetValue <string>("WorkbookId");
var worksheetId = context.UserData.GetValue <string>("WorksheetId");
try
{
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var worksheetsRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Worksheets.Request(headers);
var worksheets = await worksheetsRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, worksheetsRequest);
var reply = new StringBuilder();
if (worksheets.Count == 1)
{
reply.Append($"There is **1** worksheet in the workbook:\n");
}
else
{
reply.Append($"There are **{worksheets.Count}** worksheets in the workbook:\n");
}
var active = "";
foreach (var worksheet in worksheets)
{
active = (worksheet.Name.ToLower() == worksheetId.ToLower()) ? " (active)" : "";
reply.Append($"* **{worksheet.Name}**{active}\n");
}
await context.PostAsync(reply.ToString());
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong getting the worksheets ({ex.Message})");
}
}
// Lookup a name assuming that it is named item, return null if it doesn't exist
public static async Task <Microsoft.Graph.WorkbookNamedItem> GetNamedItem(IDialogContext context, string workbookId, string name)
{
try
{
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var namedItemsRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Names.Request(headers);
var namedItems = await namedItemsRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, namedItemsRequest);
return(namedItems?.FirstOrDefault(n => n.Name.ToLower() == name.ToLower()));
}
catch
{
return(null);
}
}
// Lookup a name assuming that it is named item, return null if it doesn't exist
public static async Task <WorkbookChart> GetChart(IDialogContext context, string workbookId, string worksheetId, string name)
{
WorkbookChart chart = null;
try
{
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var chartRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Worksheets[worksheetId].Charts[name].Request(headers);
chart = await chartRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, chartRequest);
}
catch
{
}
return(chart);
}
// Lookup a name assuming that it is named item, return null if it doesn't exist
public static async Task <WorkbookTable> GetTable(IDialogContext context, string workbookId, string name)
{
WorkbookTable table = null;
try
{
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var tableRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[name].Request(headers);
table = await tableRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, tableRequest);
}
catch
{
}
return(table);
}
public async static Task <string[]> GetWorksheetNamesAsync(IDialogContext context, string workbookId)
{
try
{
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var worksheetsRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Worksheets.Request(headers);
var worksheets = await worksheetsRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, worksheetsRequest);
return(worksheets.Select <WorkbookWorksheet, string>(w => w.Name).ToArray());
}
catch (Exception)
{
return(new string[] { });
}
}
public static async Task ReplyWithTable(IDialogContext context, string workbookId, WorkbookTable table)
{
try
{
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
var rangeRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[table.Id].Range().Request(headers);
var range = await rangeRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, rangeRequest);
var reply = $"**{table.Name}**\n\n{NamedItemsWorker.GetRangeReplyAsTable(range)}";
await context.PostAsync(reply);
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong getting the **{table.Name}** table ({ex.Message})");
}
}
public static async Task SetColumnValue(IDialogContext context, string workbookId, string tableName, string name, int rowIndex, object value)
{
var headers = ServicesHelper.GetWorkbookSessionHeader(
await ExcelHelper.GetSessionIdForUpdateAsync(context));
// Get the table
var tableRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[tableName].Request(headers);
var table = await tableRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, tableRequest);
if ((bool)(table.ShowHeaders))
{
// Get the table header
var headerRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[table.Id].HeaderRowRange().Request(headers);
var header = await headerRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, headerRequest);
// Find the column
var lowerName = name.ToLower();
var columnIndex = header.Text[0].IndexOf(h => h.ToString().ToLower() == lowerName);
if (columnIndex >= 0)
{
var rangeRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[table.Id].DataBodyRange().Request(headers);
var dataBodyRange = await rangeRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, rangeRequest);
var rowAddress = ExcelHelper.GetRangeAddress(
(int)(dataBodyRange.ColumnIndex) + columnIndex,
(int)(dataBodyRange.RowIndex) + rowIndex,
1, 1);
var newValue = new WorkbookRange()
{
Values = JToken.Parse($"[[\"{value}\"]]")
};
var updateRangeRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Worksheets[ExcelHelper.GetWorksheetName(dataBodyRange.Address)]
.Range(rowAddress).Request(headers);
var range = await updateRangeRequest.PatchAsync(newValue);
await ServicesHelper.LogGraphServiceRequest(context, updateRangeRequest, newValue);
await context.PostAsync($"**{header.Text[0][columnIndex]}** is now **{range.Text[0][0]}**");
}
else
{
await context.PostAsync($"**{name}** is not a column in **{table.Name}**");
}
}
else
{
await context.PostAsync($"I cannot set values in **{table.Name}** because it does not have any headers");
}
}
public static async Task SetNamedItemValue(IDialogContext context, string workbookId, string name, object value)
{
try
{
var namedItem = await GetNamedItem(context, workbookId, name);
if (namedItem != null)
{
switch (namedItem.Type)
{
case "Range":
var headers = ServicesHelper.GetWorkbookSessionHeader(
await ExcelHelper.GetSessionIdForUpdateAsync(context));
var namedItemRangeRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Names[namedItem.Name].Range().Request(headers);
var range = await namedItemRangeRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, namedItemRangeRequest);
if ((range.RowCount == 1) && (range.ColumnCount == 1))
{
// Named item points to a single cell
try
{
var newValue = new WorkbookRange()
{
Values = JToken.Parse($"[[\"{value}\"]]")
};
var updateRangeRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Worksheets[ExcelHelper.GetWorksheetName(range.Address)]
.Cell(range.RowIndex.Value, range.ColumnIndex.Value).Request(headers);
range = await updateRangeRequest.PatchAsync(newValue);
await ServicesHelper.LogGraphServiceRequest(context, updateRangeRequest, newValue);
await context.PostAsync($"**{namedItem.Name}** is now **{range.Text[0][0]}**");
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong setting the value of **{namedItem.Name}** to **{value}** ({ex.Message})");
}
}
else
{
await context.PostAsync($"Sorry, I can't set the value of **{namedItem.Name}** since it is a range of cells");
}
break;
case "String":
case "Boolean":
case "Integer":
case "Double":
await context.PostAsync($"Sorry, I am not able to set the value of **{namedItem.Name}** since it is a constant");
break;
default:
await context.PostAsync($"Sorry, I am not able to set the value of **{namedItem.Name}** ({namedItem.Type}, {namedItem.Value})");
break;
}
}
else
{
await context.PostAsync($"**{name}** is not a named item in the workbook");
}
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong setting the value of **{name}** ({ex.Message})");
}
}
public static async Task DoLookupTableRow(IDialogContext context, string value)
{
var workbookId = context.UserData.GetValue <string>("WorkbookId");
string tableName = string.Empty;
context.UserData.TryGetValue <string>("TableName", out tableName);
try
{
if ((tableName != null) && (tableName != string.Empty))
{
WorkbookTable table = null;
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(context));
try
{
var tablesRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[tableName].Request(headers);
table = await tablesRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, tablesRequest);
}
catch
{
}
if (table != null)
{
if ((value != null) && (value != string.Empty))
{
var rangeRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[tableName].DataBodyRange().Request(headers);
var range = await rangeRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, rangeRequest);
if ((range != null) && (range.RowCount > 0))
{
var lowerValue = value.ToLower();
var rowIndex = -1;
var columnIndex = 0;
while ((rowIndex < 0) && (columnIndex < range.ColumnCount))
{
// Look for a full match in the first column of the table
rowIndex = range.Text.IndexOf(r => (((string)(r[columnIndex])).ToLower() == lowerValue));
if (rowIndex >= 0)
{
break;
}
else
{
// Look for a partial match in the first column of the table
rowIndex = range.Text.IndexOf(r => (((string)(r[columnIndex])).ToLower().Contains(lowerValue)));
if (rowIndex >= 0)
{
break;
}
}
++columnIndex;
}
if (rowIndex >= 0)
{
context.UserData.SetValue <int>("RowIndex", rowIndex);
await ReplyWithTableRow(context, workbookId, table, range.Text[rowIndex]);
}
else
{
await context.PostAsync($"**{value}** is not in **{table.Name}**");
}
}
else
{
await context.PostAsync($"**{table.Name}** doesn't have any rows");
}
}
else
{
await context.PostAsync($"Need a value to look up a row in **{table.Name}**");
}
}
else
{
await context.PostAsync($"**{tableName}** is not a table in the workbook");
}
}
else
{
await context.PostAsync($"Need the name of a table to look up a row");
}
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong looking up the table row ({ex.Message})");
}
}
public async Task <HttpResponseMessage> Image(string channelId, string conversationId, string userId, string userNonce)
{
// Save the request url
RequestHelper.RequestUri = Request.RequestUri;
ChartAttachment chartAttachment = null;
try
{
var conversationData = await BotStateHelper.GetConversationDataAsync(channelId, conversationId);
chartAttachment = conversationData.GetProperty <ChartAttachment>(userNonce);
if (chartAttachment == null)
{
throw new ArgumentException("User nounce not found");
}
// Get access token
BotAuth.Models.AuthResult authResult = null;
try
{
var userData = await BotStateHelper.GetUserDataAsync(channelId, userId);
authResult = userData.GetProperty <BotAuth.Models.AuthResult>($"MSALAuthProvider{BotAuth.ContextConstants.AuthResultKey}");
}
catch
{
}
if (authResult != null)
{
ServicesHelper.AccessToken = authResult.AccessToken;
var headers = ServicesHelper.GetWorkbookSessionHeader(
ExcelHelper.GetSessionIdForRead(conversationData, chartAttachment.WorkbookId));
// Get the chart image
#region Graph client bug workaround
// Workaround for following issue:
// https://github.com/microsoftgraph/msgraph-sdk-dotnet/issues/107
// Proper call should be:
// var imageAsString = await ServicesHelper.GraphClient.Me.Drive.Items[chartAttachment.WorkbookId]
// .Workbook.Worksheets[chartAttachment.WorksheetId]
// .Charts[chartAttachment.ChartId].Image(0, 0, "fit").Request(headers).GetAsync();
// Get the request URL just to the chart because the image
// request builder is broken
string chartRequestUrl = ServicesHelper.GraphClient.Me.Drive.Items[chartAttachment.WorkbookId]
.Workbook.Worksheets[chartAttachment.WorksheetId]
.Charts[chartAttachment.ChartId].Request().RequestUrl;
// Append the proper image request segment
string chartImageRequestUrl = $"{chartRequestUrl}/image(width=0,height=0,fittingMode='fit')";
// Create an HTTP request message
var imageRequest = new HttpRequestMessage(HttpMethod.Get, chartImageRequestUrl);
// Add session header
imageRequest.Headers.Add(headers[0].Name, headers[0].Value);
// Add auth
await ServicesHelper.GraphClient.AuthenticationProvider.AuthenticateRequestAsync(imageRequest);
// Send request
var imageResponse = await ServicesHelper.GraphClient.HttpProvider.SendAsync(imageRequest);
if (!imageResponse.IsSuccessStatusCode)
{
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
// Parse the response for the base 64 image string
var imageObject = JObject.Parse(await imageResponse.Content.ReadAsStringAsync());
var imageAsString = imageObject.GetValue("value").ToString();
#endregion
// Convert the image from a string to an image
byte[] byteBuffer = Convert.FromBase64String(imageAsString);
var memoryStream = new MemoryStream(byteBuffer);
memoryStream.Position = 0;
// Send the image back in the response
var response = Request.CreateResponse(HttpStatusCode.OK);
response.Headers.AcceptRanges.Add("bytes");
response.Content = new StreamContent(memoryStream);
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("render");
response.Content.Headers.ContentDisposition.FileName = "chart.png";
response.Content.Headers.ContentType = new MediaTypeHeaderValue("image/png");
response.Content.Headers.ContentLength = memoryStream.Length;
response.Headers.CacheControl = new CacheControlHeaderValue()
{
NoCache = true, NoStore = true
};
return(response);
}
else
{
return(Request.CreateResponse(HttpStatusCode.Forbidden));
}
}
catch
{
// The user nonce was not found in user state
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
}
public static async Task DoAddTableRow(IDialogContext context, object[] rows)
{
var workbookId = context.UserData.GetValue <string>("WorkbookId");
string tableName = string.Empty;
context.UserData.TryGetValue <string>("TableName", out tableName);
try
{
if ((tableName != null) && (tableName != string.Empty))
{
WorkbookTable table = null;
var headers = ServicesHelper.GetWorkbookSessionHeader(
await ExcelHelper.GetSessionIdForUpdateAsync(context));
try
{
var tablesRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[tableName].Request(headers);
table = await tablesRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, tablesRequest);
}
catch
{
}
if (table != null)
{
// Get number of columns in table
var headerRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[table.Id].HeaderRowRange().Request(headers);
var tableHeaderRange = await headerRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, headerRequest);
// Ensure that the row to be added has the right number of values. Add additional values, if needed
var checkedRows = new List <object>();
foreach (object[] uncheckedRow in rows)
{
if (uncheckedRow.Length < tableHeaderRange.ColumnCount)
{
var checkedRow = uncheckedRow.ToList();
while (checkedRow.Count < tableHeaderRange.ColumnCount)
{
checkedRow.Add(null);
}
checkedRows.Add(checkedRow.ToArray());
}
else
{
checkedRows.Add(uncheckedRow);
}
}
// Add row
var newVals = JToken.Parse(JsonConvert.SerializeObject(checkedRows));
var addRowRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[table.Id].Rows.Add(values: newVals).Request(headers);
var row = await addRowRequest.PostAsync();
await ServicesHelper.LogGraphServiceRequest(context, addRowRequest, newVals);
await context.PostAsync($"Added a new row to **{table.Name}**");
var rangeRequest = ServicesHelper.GraphClient.Me.Drive.Items[workbookId]
.Workbook.Tables[table.Id].DataBodyRange().Request(headers);
var range = await rangeRequest.GetAsync();
await ServicesHelper.LogGraphServiceRequest(context, rangeRequest);
await ReplyWithTableRow(context, workbookId, table, range.Text[row.Index ?? 0]);
}
else
{
await context.PostAsync($"**{tableName}** is not a table in the workbook");
}
}
else
{
await context.PostAsync($"Need the name of a table to add a row");
}
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong adding the table row ({ex.Message})");
}
}
