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);
}
}
private static void FillTables(WorkbookPart workbookPart, string sheetId, List <TableToInsert> dataTables)
{
var processedTablesRows = dataTables.ToDictionary(x => x.TableName, y => 0);
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheetId);
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
ExcelHelper.AddEmptyRows(sheetData);
var rows = sheetData.Elements <Row>().ToList();
foreach (var row in rows)
{
if (!IsRowContainsCellsForFill(row, workbookPart, dataTables.Select(x => x.TableName).ToArray()))
{
continue;
}
var fields = GetRowFieldsForFill(row, workbookPart, dataTables.Select(x => x.TableName).ToArray());
var generatedRowIndex = row.RowIndex;
if (fields.Any())
{
var tableNamesForAddOneRow = new List <string>();
// Кол-во строк которое надо обработать
int rowsForProcess = 0;
// Если нет полей, которые надо оставить без изменений
if (!fields.Any(x => x.ValueName.Contains(":1")))
{
// Кол-во строк из задействованных таблиц, которое осталось обработать
var maxTableRowsForAdd = 0;
foreach (var dataTable in dataTables.Where(x => fields.Any(y => y.ValueName.Split('.')[0] == x.TableName)))
{
// Если таблица пустая, то указываем, единицу для пустой строки
var rowsCount = dataTable.Rows.Count > 0 ? dataTable.Rows.Count : 1;
// Оставшиеся количество строк для таблицы, которое осталось обработать
var remainingTableRowsForAdd = rowsCount - processedTablesRows[dataTable.TableName];
if (maxTableRowsForAdd == 0 &&
processedTablesRows[dataTable.TableName] > 0 &&
remainingTableRowsForAdd <= 0)
{
// Добавление пустой строки
maxTableRowsForAdd = 1;
}
else
{
maxTableRowsForAdd = Math.Max(maxTableRowsForAdd, remainingTableRowsForAdd);
}
}
rowsForProcess = maxTableRowsForAdd;
}
else
{
// Т.к. есть поля которые надо оставить без изменений,
// то кол-во строк для обработки равно единице
rowsForProcess = 1;
for (int i = 0; i < fields.Count; i++)
{
fields[i] = new LocationWithValue(fields[i].RowIndex, fields[i].ColumnIndex, fields[i].ValueName.Replace(":1", ""));
// Имена таблиц (списков) для которых надо указать что еще одна строка обработана
tableNamesForAddOneRow.Add(fields[i].ValueName.Split('.')[0]);
}
}
// Обработка строк
//var merges = new List<MergeCell>();
for (int i = 0; i < rowsForProcess; i++)
{
var generatedRow = CreateRow(worksheetPart.Worksheet, row, generatedRowIndex, dataTables, i, fields, processedTablesRows);
if (i == 0)
{
row.InsertBeforeSelf(generatedRow);
}
else
{
InsertRowHelper.InsertRow(generatedRowIndex, worksheetPart, generatedRow);
}
if (row.RowIndex != generatedRowIndex)
{
if (worksheetPart.Worksheet.Elements <MergeCells>().Count() > 0)
{
// if (!merges.Any())
// {
var worksheet = worksheetPart.Worksheet;
var mergeCells = worksheet.Elements <MergeCells>().First();
var rowMergeCellsList = new List <MergeCell>();
var mergeCellChildElements = mergeCells.ChildElements;
foreach (MergeCell item in mergeCellChildElements)
{
var mergeCellReference = new MergeCellReference(item.Reference);
if (mergeCellReference.CellFrom.RowIndex == row.RowIndex)
{
// merges.Add(item);
var cellFrom = mergeCellReference.CellFrom;
var cellTo = mergeCellReference.CellTo;
cellFrom.RowIndex = (int)generatedRowIndex.Value;
cellTo.RowIndex = (int)generatedRowIndex.Value;
var mergeCell = new MergeCell {
Reference = $"{cellFrom.Reference}:{cellTo.Reference}"
};
mergeCells.Append(mergeCell);
}
}
// }
// else
// {
// var worksheet = worksheetPart.Worksheet;
// var mergeCells = worksheet.Elements<MergeCells>().First();
// foreach (var merge in merges)
// {
// var mergeCellReference = new MergeCellReference(merge.Reference);
//
// var cellFrom = mergeCellReference.CellFrom;
// var cellTo = mergeCellReference.CellTo;
// cellFrom.RowIndex = (int)generatedRowIndex.Value;
// cellTo.RowIndex = (int)generatedRowIndex.Value;
// var mergeCell = new MergeCell { Reference = $"{cellFrom.Reference}:{cellTo.Reference}" };
// mergeCells.Append(mergeCell);
// }
// }
}
}
generatedRowIndex++;
}
foreach (var tableNameForAddOneRow in tableNamesForAddOneRow.Distinct())
{
processedTablesRows[tableNameForAddOneRow]++;
}
row.Remove();
}
}
}