//if (template.IsHeader)
//{
// 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);
// // fill values
//}
//// Print all properties in current row
//foreach (var item in data)
//{
// var subProperty = propertyName + "." + item.Key;
// if (item.Value.Type == JTokenType.Array)
// {
// //PrintArraysWithStructure(subProperty, (JArray)item.Value);
// }
// else
// {
// // fill values
// //// 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, item.Value, offset);
// // }
// // }
// // }
// //}
// }
//} // foreach (var item in data)
//if (!template.IsHeader)
//{
// 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);
// // fill values
//}
//// 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);
// var matches = regexReplace.Matches(cellValue);
// if (matches.Count > 0)
// {
// ReplaceValueInBrackets(cell.Key.RowIndex, cell.Key.ColumnIndex, cellValue, data, matches);
// }
//}
private void IncrementNextRow(ArrayTemplate template, int fromRow)
{
NextRow
.Where(n => n.SheetName.Equals(template.SheetName) && n.NextRow >= fromRow)
.ToList()
.ForEach(n =>
{
n.NextRow += template.RowRange.RowCount;
});
ArrayStructure
.Where(s => s.RowRange.SheetName.Equals(template.SheetName) && s.RowRange.RowStart >= fromRow)
.ToList()
.ForEach(e =>
{
e.RowRange.RowStart += template.RowRange.RowCount;
e.RowRange.RowEnd += template.RowRange.RowCount;
});
}
// Separate groups mean separate areas to print arrays
private void DivideToGroupsArrayStructure()
{
int group = 0;
foreach (var sheet in doc.GetSheetNames())
{
var list = ArrayStructure
.Where(s => s.RowRange.SheetName.Equals(sheet, StringComparison.OrdinalIgnoreCase))
.OrderBy(o => o.RowRange.RowStart)
.ToList();
if (list.Count == 0)
{
continue;
}
ArrayTemplate previous = null;
foreach (var rng in list)
{
if (previous != null)
{
if (previous.RowRange.RowEnd + 1 != rng.RowRange.RowStart)
{
group++;
}
if (rng.PropertyName.StartsWith(previous.PropertyName + ".") && previous.Group == group)
{
previous.IsHeader = true;
}
}
rng.Group = group;
previous = rng;
}
group++;
}
ArrayStructure = ArrayStructure.OrderBy(o => o.Group).ThenBy(o => o.RowRange.RowStart).ToList();
}
// 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);
}
}
}
private void PrintArraysWithStructure(string propertyName, JArray jArray, int group, ArrayTemplate templateFrom = null, ArrayTemplate templateTo = null)
{
var templatesAr = ArrayStructure
.Where(a => a.Group.Equals(group) &&
a.PropertyName.Equals(propertyName));
if (templateFrom != null)
{
templatesAr = templatesAr.Where(t => t.RowRange.RowStart > templateFrom.RowRange.RowEnd);
}
if (templateTo != null)
{
templatesAr = templatesAr.Where(t => t.RowRange.RowEnd < templateTo.RowRange.RowStart);
}
var templates = templatesAr.ToArray();
if (templates.Length == 0)
{
foreach (var row in jArray)
{
var data = row as JObject;
if (data == null)
{
continue;
}
// print subarrays
foreach (var prop in data)
{
var subProperty = propertyName + "." + prop.Key;
if (prop.Value.Type == JTokenType.Array)
{
PrintArraysWithStructure(subProperty, (JArray)prop.Value, group, templateFrom, templateTo);
}
}
}
}
for (int t = 0; t < templates.Length; t++)
{
var current = templates[t];
var next = t < templates.Length - 1 ? templates[t + 1] : null; // null if last
var header = current.IsHeader ? current : null;
var footer = current.IsHeader ? null : current;
if (next != null && !next.IsHeader)
{
footer = next;
t++;
}
foreach (var row in jArray)
{
var data = row as JObject;
if (data == null)
{
continue;
}
// print header
if (header != null)
{
PrintRow(header, propertyName, data);
}
// print subarrays
foreach (var prop in data)
{
var subProperty = propertyName + "." + prop.Key;
if (prop.Value.Type == JTokenType.Array)
{
PrintArraysWithStructure(subProperty, (JArray)prop.Value, group, header, footer);
}
}
// print footer
if (footer != null)
{
PrintRow(footer, propertyName, data);
}
}
}
}