private void BuildArrayStructure(string propertyName, JArray jArray) { if (!doc.HasDefinedName(propertyName)) { return; } var ranges = doc.GetDefinedNameText(propertyName).Split(','); foreach (var rng in ranges) { var rowRange = new RowRange(rng); if (!ArrayStructure.Any(p => p.PropertyName.Equals(propertyName) && p.RowRange.SheetName.Equals(rowRange.SheetName) && p.RowRange.RowStart == rowRange.RowStart)) { ArrayStructure.Add(new ArrayTemplate { PropertyName = propertyName, RowRange = rowRange }); } } // find nested arrays // look through all rows to find arrays foreach (var row in jArray) { // look throug all props foreach (var item in (JObject)row) { if (item.Value.Type == JTokenType.Array) { var subProperty = propertyName + "." + item.Key; BuildArrayStructure(subProperty, (JArray)item.Value); } } } }
// print single row on single sheet private void PrintRow(ArrayTemplate template, string propertyName, JObject data) { if (!doc.SelectWorksheet(template.SheetName)) { return; } if (data == null) { return; } var nextRow = NextRow .Where(s => s.SheetName.Equals(template.SheetName, StringComparison.OrdinalIgnoreCase) && s.Group == template.Group) .Select(i => i.NextRow).FirstOrDefault(); doc.InsertRow(nextRow, template.RowRange.RowCount); doc.CopyRow(template.RowRange.RowStart, template.RowRange.RowEnd, nextRow); IncrementNextRow(template, nextRow); // Print all properties in current row foreach (var prop in data) { var subProperty = propertyName + "." + prop.Key; // print prop int offset = nextRow - template.RowRange.RowStart; var ranges = doc.GetDefinedNameText(subProperty).Split(','); foreach (var rng in ranges) { if (rng.StartsWith(template.SheetName)) { // print only if property inside inserted row RowRange rowRange = new RowRange(rng); if (rowRange.RowStart <= template.RowRange.RowStart && template.RowRange.RowEnd <= rowRange.RowEnd) { var refer = rng.Substring(rng.IndexOf('!') + 1).Replace("$", ""); int row, col; if (SLDocument.WhatIsRowColumnIndex(refer, out row, out col)) { SetCellValue(row, col, prop.Value, offset); } } } } } // Replace values in curly braces, e.g. {Name} int rowStart = nextRow; int rowEnd = nextRow + template.RowRange.RowCount - 1; var cells = doc.GetCells().Where(c => c.Key.RowIndex >= rowStart && c.Key.RowIndex <= rowEnd); foreach (var cell in cells) { var cellValue = doc.GetCellValueAsString(cell.Key.RowIndex, cell.Key.ColumnIndex); cellValue = cellValue.Replace(propertyName + ".", ""); var matches = regexReplace.Matches(cellValue); if (matches.Count > 0) { ReplaceValueInBrackets(cell.Key.RowIndex, cell.Key.ColumnIndex, cellValue, data, matches); } } }