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})");
}
}