Exemplo n.º 1
0
        /// <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);
        }