public static async Task <bool> TryCreateExcelChartFromTableAsync()
{
string createdFileId = await UserSnippets.UploadExcelFileAsync("excelTestResource.xlsx");
WorkbookChart excelWorkbookChart = await UserSnippets.CreateExcelChartFromTableAsync(createdFileId);
return(excelWorkbookChart != null);
}
public static async Task <WorkbookChart> CreateExcelChartFromTableAsync(string fileId)
{
WorkbookChart workbookChart = null;
if (fileId == null)
{
Debug.WriteLine("Please upload the excelTestResource.xlsx file by running the Upload XL File snippet.");
return(workbookChart);
}
else
{
try
{
GraphServiceClient graphClient = AuthenticationHelper.GetAuthenticatedClient();
// Get the table range.
WorkbookRange tableRange = await graphClient.Me.Drive.Items[fileId]
.Workbook
.Tables["CreateChartFromTable"] // Set in excelTestResource.xlsx
.Range()
.Request()
.GetAsync();
// Create a chart based on the table range.
workbookChart = await graphClient.Me.Drive.Items[fileId]
.Workbook
.Worksheets["CreateChartFromTable"] // Set in excelTestResource.xlsx
.Charts
.Add("ColumnStacked", "Auto", tableRange.Address)
.Request()
.PostAsync();
Debug.WriteLine("Created the Excel chart: " + workbookChart.Name);
}
catch (Microsoft.Graph.ServiceException e)
{
Debug.WriteLine("We failed to create the Excel chart: " + e.Error.Message);
}
return(workbookChart);
}
}
// 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);
}
public static async Task ReplyWithChart(IDialogContext context, string workbookId, string worksheetId, WorkbookChart chart)
{
try
{
// Create and save user nonce
var userNonce = (Guid.NewGuid()).ToString();
context.ConversationData.SetValue <ChartAttachment>(
userNonce,
new ChartAttachment()
{
WorkbookId = workbookId,
WorksheetId = worksheetId,
ChartId = chart.Id
});
await context.FlushAsync(context.CancellationToken);
// Reply with chart URL attached
var reply = context.MakeMessage();
reply.Recipient.Id = (reply.Recipient.Id != null) ? reply.Recipient.Id : (string)(HttpContext.Current.Items["UserId"]);
var image = new Microsoft.Bot.Connector.Attachment()
{
ContentType = "image/png",
ContentUrl = $"{RequestHelper.RequestUri.Scheme}://{RequestHelper.RequestUri.Authority}/api/{reply.ChannelId}/{reply.Conversation.Id}/{reply.Recipient.Id}/{userNonce}/image"
};
reply.Attachments.Add(image);
await context.PostAsync(reply);
}
catch (Exception ex)
{
await context.PostAsync($"Sorry, something went wrong getting the **{chart.Name}** chart ({ex.Message})");
}
}
