/// <summary>
/// Updates a SpreadsheetDocument with new tabular data.
/// </summary>
/// <param name="ssdoc">
/// The excel document to update.</param>
/// <param name="json">
/// String in JSON format representing the tabular data for updating the Chart's cached data points.
/// The JSON object must contain a "fields" attribute as an array containing the field/column names.
/// The JSON object must contain a "rows" attribute as an array of arrays representing the rows and their values, with values matching the same order and cardinality of the field names.
/// This is the same data as the underlying Excel spreadsheet contents.</param>
/// <param name="sheetName">
/// The name of the Excel worksheet where the chart data originates from.
/// Used for updating the chart's cell references.</param>
/// <returns>
/// Returns the updated SpreadsheetDocument</returns>
public static SpreadsheetDocument Update(this SpreadsheetDocument ssdoc, string json, string sheetName)
{
if ((json == null) || (json == String.Empty))
{
json = "{\"fields\": [ \"No Results\" ], \"rows\": [[ \"No Results\" ]]}";
}
//Splunk JSON data is a series of objects consisting of multiple key(column)/value(row) pairs in the result attribute.
dynamic input = JsonConvert.DeserializeObject <dynamic>(json);
if (input["rows"].Count == 0)
{
json = "{\"fields\": [ \"No Results\" ], \"rows\": [[ \"No Results\" ]]}";
input = JsonConvert.DeserializeObject <dynamic>(json);
}
ss.Sheet sheet = ssdoc.WorkbookPart.Workbook.Descendants <ss.Sheet>().Where(s => s.Name.ToString() == sheetName).FirstOrDefault();
if (sheet == null)
{
sheet = ssdoc.WorkbookPart.Workbook.Descendants <ss.Sheet>().FirstOrDefault();
}
WorksheetPart worksheet = (WorksheetPart)ssdoc.WorkbookPart.GetPartById(sheet.Id);
ss.SheetData data = worksheet.Worksheet.GetFirstChild <ss.SheetData>();
//Remove all the rows after our column headers row. We'll replace them with new rows as the table is populated from the Splunk search results.
ss.Row firstRow = data.Elements <ss.Row>().First();
while (firstRow.NextSibling <ss.Row>() != null)
{
firstRow.NextSibling <ss.Row>().Remove();
}
ss.Row newHeader = new ss.Row();
newHeader.DyDescent = 0.25;
newHeader.RowIndex = 1;
var columnNames = input["fields"];
char startingHeaderColumn = 'A';
foreach (var column in columnNames)
{
string cellRef = startingHeaderColumn.ToString() + 1;
ss.Cell newCell = new ss.Cell();
ss.CellValue cv = new ss.CellValue(column.ToString());
newCell.CellReference = cellRef;
newCell.DataType = ss.CellValues.String;
newCell.Append(cv);
newHeader.Append(newCell);
startingHeaderColumn++;
}
data.InsertAfter(newHeader, data.Elements <ss.Row>().Last());
data.RemoveChild(firstRow);
for (int i = 0; i < input["rows"].Count; i++)
{
char startingColumn = 'A';
int startingColumnVal = 1;
int endingColumnVal = 0;
//Set the Excel row index (Excel index starts at 1, not zero and row 1 has headers so add 2 to the for index)
uint rowIndex = Convert.ToUInt32(i) + 2;
ss.Row newRow = new ss.Row();
newRow.DyDescent = 0.25;
newRow.RowIndex = rowIndex;
Debug.WriteLine(String.Format("Writing Excel Row {0}", i + 1));
var row = input["rows"][i];
foreach (var cell in row)
{
string cellRef = startingColumn.ToString() + rowIndex;
ss.Cell newCell = new ss.Cell();
ss.CellValue cv = new ss.CellValue(cell.ToString());
newCell.CellReference = cellRef;
if (startingColumn == 'A')
{
newCell.DataType = ss.CellValues.String;
}
newCell.Append(cv);
newRow.Append(newCell);
startingColumn++;
endingColumnVal++;
}
int numberOfCellsInRow = newRow.Descendants <ss.Cell>().Count();
ListValue <StringValue> spans = new ListValue <StringValue>();
spans.Items.Add(string.Format("{0}:{1}", startingColumnVal, endingColumnVal));
newRow.Spans = spans;
data.InsertAfter(newRow, data.Elements <ss.Row>().Last());
}
// Update Table Reference
Debug.WriteLine("Updating Table");
var table = worksheet.TableDefinitionParts.First().Table;
table.Reference = string.Format("A1:{0}{1}", GetExcelColumnName(input["fields"].Count), input["rows"].Count + 1);
table.TableColumns.RemoveAllChildren();
for (int i = 0; i < columnNames.Count; i++)
{
var newColumn = new ss.TableColumn();
newColumn.Id = new UInt32Value((uint)i + 1);
newColumn.Name = new StringValue(columnNames[i].ToString());
table.TableColumns.Append(newColumn);
}
return(ssdoc);
}