private static void GenerateTemplate(WorkbookPart workbookPart, string sheetId, List <ColumnBlockToInsert> columnsBlockToInsert)
{
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheetId);
var lastColumnName = "A";
foreach (var columnBlockToInsert in columnsBlockToInsert)
{
var startColumnIndex = CellHelper.GetColumnIndex(columnBlockToInsert.FirstColumnName);
for (int i = 0; i < columnBlockToInsert.ColumnsWidths.Length; i++)
{
AddColumns(worksheetPart.Worksheet, startColumnIndex + i, columnBlockToInsert.ColumnsWidths[i]);
}
foreach (var blockToInsert in columnBlockToInsert.RowBlocksToInsert)
{
var fromIndex = 0;
foreach (var cellToInsert in blockToInsert.CellsToInsert)
{
var column1Name = CellHelper.ColumnIndexToColumnLetter(startColumnIndex + fromIndex);
var row1Index = blockToInsert.RowId;
var cell1Reference = new CellReference($"{column1Name}{row1Index}");
var column2Name = CellHelper.ColumnIndexToColumnLetter(startColumnIndex + fromIndex + cellToInsert.RowSize - 1);
var row2Index = blockToInsert.RowId;
var cell2Reference = new CellReference($"{column2Name}{row2Index}");
lastColumnName = column2Name;
if (cellToInsert.RowSize > 1)
{
MergeCellHelper.MergeTwoCells(worksheetPart.Worksheet, cell1Reference.Reference, cell2Reference.Reference);
}
else
{
CellHelper.CreateSpreadsheetCellIfNotExist(worksheetPart.Worksheet, cell1Reference.Reference);
}
if (!string.IsNullOrEmpty(cellToInsert.FieldName))
{
var valueToInsert = new ValueToInsert
{
FieldName = null,
IsFormula = false,
Type = typeof(string),
Value = cellToInsert.FieldName,
};
SetCellValues(worksheetPart.Worksheet, column1Name, blockToInsert.RowId, valueToInsert);
}
CellHelper.CopyCellStyle(worksheetPart.Worksheet,
new CellReference(cellToInsert.StyleCellReference).ColumnName,
new CellReference(cellToInsert.StyleCellReference).RowIndex,
column1Name, row1Index);
fromIndex++;
}
}
}
MergeCellHelper.MergeTwoCells(worksheetPart.Worksheet, "B3", $"{lastColumnName}3");
}
public static void CopyAndInsertRows(int fromRowIndex, int toRowIndex, int rowsCount, int wellIndex,
WorkbookPart workbookPart, string sheetId, List <TableToInsert> dataTables)
{
var processedTablesRows = dataTables.ToDictionary(x => x.TableName, y => 0);
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheetId);
var mergeCells = MergeCellHelper.GetMergeCells(workbookPart, sheetId).FirstOrDefault()
.ChildElements.Select(x => x as MergeCell);
Row oldRow = null;
Row generatedRow = null;
var mergeCellsForCopy = new List <MergeCell>();
for (int i = 0; i < rowsCount; i++)
{
var oldRowIndex = fromRowIndex + i;
oldRow = RowHelper.GetRow(worksheetPart.Worksheet, (uint)oldRowIndex);
generatedRow = CreateRow(worksheetPart.Worksheet, oldRow, (uint)(toRowIndex + i), dataTables, 0, new List <LocationWithValue>(), processedTablesRows);
foreach (var cell in generatedRow.Descendants <Cell>())
{
if (cell == null)
{
continue;
}
var cellValue = CellHelper.GetCellValue(cell, workbookPart);
if (String.IsNullOrWhiteSpace(cellValue) || cellValue.Length <= 4)
{
continue;
}
if (!cellValue.StartsWith("{{Well1"))
{
continue;
}
cellValue = cellValue.Replace("{{Well1", $"{{{{Well{wellIndex}");
SetCellValues(cell, new ValueToInsert(null, typeof(string), cellValue));
}
InsertRowHelper.InsertRow((uint)(toRowIndex + i), worksheetPart, generatedRow);
var rowMergeCellsForCopy = mergeCells.Where(x => new MergeCellReference(x.Reference).CellFrom.RowIndex == oldRowIndex);
mergeCellsForCopy.AddRange(rowMergeCellsForCopy);
}
foreach (var mergeCellForCopy in mergeCellsForCopy)
{
var newMergeCell = MergeCellReferenceMoveByRows(new MergeCellReference(mergeCellForCopy.Reference), toRowIndex - fromRowIndex);
MergeCellHelper.MergeTwoCells(worksheetPart.Worksheet, newMergeCell.CellFrom.Reference, newMergeCell.CellTo.Reference);
}
}
