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); } }