/// <summary>
/// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="valueType">Type of the value</param>
/// <param name="value">The actual value</param>
/// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param>
/// <param name="save">Save the worksheet?</param>
/// <returns>True if succesful</returns>
private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Row row;
DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null;
DocumentFormat.OpenXml.Spreadsheet.Cell cell;
DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null;
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
string cellAddress = ExcelProc.ColumnNameFromIndex(columnIndex) + rowIndex;
// Check if the row exists, create if necessary
if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First();
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowIndex
};
//sheetData.Append(row);
for (uint counter = rowIndex - 1; counter > 0; counter--)
{
previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
if (previousRow != null)
{
break;
}
}
sheetData.InsertAfter(row, previousRow);
}
// Check if the cell exists, create if necessary
if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
{
cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First();
}
else
{
// Find the previous existing cell in the row
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == ExcelProc.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
if (previousCell != null)
{
break;
}
}
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellAddress
};
row.InsertAfter(cell, previousCell);
}
// Check if the column collection exists
columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
if (columns == null)
{
columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
}
// Check if the column exists
if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
// Find the previous existing column in the columns
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(
new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
// Add the value
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value);
if (styleIndex != null)
{
cell.StyleIndex = styleIndex;
}
if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date)
{
cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType);
}
if (save)
{
worksheet.Save();
}
return(true);
}
/// <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);
}
