public static async Task SetColumnValue(IDialogContext context, string workbookId, string tableName, string name, int rowIndex, object value) { // Get the table var table = await ServicesHelper.ExcelService.GetTableAsync( workbookId, tableName, ExcelHelper.GetSessionIdForRead(context)); if ((bool)(table.ShowHeaders)) { // Get the table header var header = await ServicesHelper.ExcelService.GetTableHeaderRowRangeAsync( workbookId, tableName, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); // Find the column var lowerName = name.ToLower(); var columnIndex = header.Text[0].IndexOf(h => h.ToString().ToLower() == lowerName); if (columnIndex >= 0) { var dataBodyRange = await ServicesHelper.ExcelService.GetTableDataBodyRangeAsync(workbookId, tableName, ExcelHelper.GetSessionIdForRead(context), "$select=columnIndex, rowIndex, rowCount, address"); var rowAddress = ExcelHelper.GetRangeAddress( (int)(dataBodyRange.ColumnIndex) + columnIndex, (int)(dataBodyRange.RowIndex) + rowIndex, 1, 1 ); var range = await ServicesHelper.ExcelService.UpdateRangeAsync( workbookId, ExcelHelper.GetWorksheetName(dataBodyRange.Address), rowAddress, new object[] { new object[] { value } }, await ExcelHelper.GetSessionIdForUpdateAsync(context) ); await ServicesHelper.LogExcelServiceResponse(context); 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 ReplyWithValue(IDialogContext context, string workbookId, NamedItem namedItem) { try { switch (namedItem.Type) { case "Range": var range = await ServicesHelper.ExcelService.NamedItemRangeAsync( workbookId, namedItem.Name, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); 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})"); } }
// Lookup a name assuming that it is named item, return null if it doesn't exist public static async Task <NamedItem> GetNamedItem(IDialogContext context, string workbookId, string name) { NamedItem[] namedItems = null; try { namedItems = await ServicesHelper.ExcelService.ListNamedItemsAsync( workbookId, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); } catch { } return(namedItems?.FirstOrDefault(n => n.Name.ToLower() == name.ToLower())); }
// Lookup a name assuming that it is named item, return null if it doesn't exist public static async Task <Table> GetTable(IDialogContext context, string workbookId, string name) { Table table = null; try { table = await ServicesHelper.ExcelService.GetTableAsync( workbookId, name, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); } catch { } return(table); }
// Lookup a name assuming that it is named item, return null if it doesn't exist public static async Task <Chart> GetChart(IDialogContext context, string workbookId, string worksheetId, string name) { Chart chart = null; try { chart = await ServicesHelper.ExcelService.GetChartAsync( workbookId, worksheetId, name, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); } catch { } return(chart); }
public async static Task <string[]> GetWorksheetNamesAsync(IDialogContext context, string workbookId) { try { var worksheets = await ServicesHelper.ExcelService.ListWorksheetsAsync( workbookId, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); return(worksheets.Select <Worksheet, string>(w => w.Name).ToArray()); } catch (Exception) { return(new string[] { }); } }
public static async Task ReplyWithTable(IDialogContext context, string workbookId, Table table) { try { var range = await ServicesHelper.ExcelService.GetTableRangeAsync( workbookId, table.Id, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); var reply = $"**{table.Name}**\n\n{NamedItemsWorker.GetRangeReply(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 SetCellValue(IDialogContext context, string workbookId, string worksheetId, string cellAddress, object value) { try { var range = await ServicesHelper.ExcelService.UpdateRangeAsync( workbookId, worksheetId, cellAddress, new object[] { new object[] { value } }, await ExcelHelper.GetSessionIdForUpdateAsync(context)); await ServicesHelper.LogExcelServiceResponse(context); 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 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 item = await ServicesHelper.OneDriveService.GetItemMetadataAsync("", filename); await ServicesHelper.LogOneDriveServiceResponse(context); 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 worksheets = await ServicesHelper.ExcelService.ListWorksheetsAsync( item.Id, ExcelHelper.GetSessionIdForRead(context), "$top=1"); await ServicesHelper.LogExcelServiceResponse(context); 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.Get <string>("WorkbookId"); var worksheetId = context.UserData.Get <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 worksheets = await ServicesHelper.ExcelService.ListWorksheetsAsync( workbookId, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); 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.Get <string>("WorkbookId"); var worksheetId = context.UserData.Get <string>("WorksheetId"); try { var charts = await ServicesHelper.ExcelService.ListChartsAsync( workbookId, worksheetId, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); if (charts.Length > 0) { var reply = new StringBuilder(); if (charts.Length == 1) { reply.Append($"There is **1** chart on **{worksheetId}**:\n"); } else { reply.Append($"There are **{charts.Length}** 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 DoListTables(IDialogContext context) { var workbookId = context.UserData.Get <string>("WorkbookId"); try { var tables = await ServicesHelper.ExcelService.ListTablesAsync( workbookId, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); if (tables.Length > 0) { var reply = new StringBuilder(); if (tables.Length == 1) { reply.Append($"There is **1** table in the workbook:\n"); } else { reply.Append($"There are **{tables.Length}** tables in the workbook:\n"); } foreach (var table in tables) { reply.Append($"* **{table.Name}**\n"); } await context.PostAsync(reply.ToString()); } else { await context.PostAsync($"There are no tables in the workbook"); } } catch (Exception ex) { await context.PostAsync($"Sorry, something went wrong getting the tables ({ex.Message})"); } }
public static async Task DoListWorksheetsAsync(IDialogContext context) { var workbookId = context.UserData.Get <string>("WorkbookId"); var worksheetId = context.UserData.Get <string>("WorksheetId"); try { var worksheets = await ServicesHelper.ExcelService.ListWorksheetsAsync( workbookId, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); var reply = new StringBuilder(); if (worksheets.Length == 1) { reply.Append($"There is **1** worksheet in the workbook:\n"); } else { reply.Append($"There are **{worksheets.Length}** 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})"); } }
public static async Task ReplyWithTableRow(IDialogContext context, string workbookId, Table table, object[] row) { if ((bool)(table.ShowHeaders)) { // Get the table header var header = await ServicesHelper.ExcelService.GetTableHeaderRowRangeAsync( workbookId, table.Id, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); var reply = new StringBuilder(); var separator = ""; for (var i = 0; i < row.Length; i++) { if ((row[i] != null) && (((string)row[i]) != string.Empty)) { reply.Append($"{separator}* {header.Text[0][i]}: **{row[i]}**"); separator = "\n"; } } await context.PostAsync(reply.ToString()); } else { var reply = new StringBuilder(); var separator = ""; for (var i = 0; i < row.Length; i++) { reply.Append($"{separator}* **{row[i]}**"); separator = "\n"; } await context.PostAsync(reply.ToString()); } }
public static async Task ReplyWithValue(IDialogContext context, string workbookId, string worksheetId, string cellAddress) { try { var range = await ServicesHelper.ExcelService.GetRangeAsync( workbookId, worksheetId, cellAddress, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); 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 DoLookupTableRow(IDialogContext context, string value) { var workbookId = context.UserData.Get <string>("WorkbookId"); string tableName = string.Empty; context.UserData.TryGetValue <string>("TableName", out tableName); try { if ((tableName != null) && (tableName != string.Empty)) { Table table = null; try { table = await ServicesHelper.ExcelService.GetTableAsync( workbookId, tableName, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); } catch { } if (table != null) { if ((value != null) && (value != string.Empty)) { var range = await ServicesHelper.ExcelService.GetTableDataBodyRangeAsync( workbookId, tableName, ExcelHelper.GetSessionIdForRead(context)); 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 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 range = await ServicesHelper.ExcelService.NamedItemRangeAsync( workbookId, namedItem.Name, ExcelHelper.GetSessionIdForRead(context)); await ServicesHelper.LogExcelServiceResponse(context); if ((range.RowCount == 1) && (range.ColumnCount == 1)) { // Named item points to a single cell try { range = await ServicesHelper.ExcelService.UpdateRangeAsync( workbookId, ExcelHelper.GetWorksheetName(range.Address), ExcelHelper.GetCellAddress(range.Address), new object[] { new object[] { value } }, await ExcelHelper.GetSessionIdForUpdateAsync(context)); await ServicesHelper.LogExcelServiceResponse(context); 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})"); } }