public static MergeCellReference MergeCellReferenceMoveByRows(MergeCellReference mergeCell, int rowsCount)
{
var newFromRowIndex = mergeCell.CellFrom.RowIndex + rowsCount;
var newToRowIndex = mergeCell.CellTo.RowIndex + rowsCount;
var newFromCellReference = $"{mergeCell.CellFrom.ColumnName}{newFromRowIndex}";
var newToCellReference = $"{mergeCell.CellTo.ColumnName}{newToRowIndex}";
return(new MergeCellReference($"{newFromCellReference}:{newToCellReference}"));
}
public static bool IsMergeCellNotExists(MergeCells mergeCells, MergeCell mergeCell)
{
foreach (MergeCell item in mergeCells)
{
var mergeCellReference = new MergeCellReference(item.Reference);
var cellReference = new CellReference(mergeCell.Reference);
if (mergeCellReference.CellFrom.RowIndex == cellReference.RowIndex &&
mergeCellReference.CellFrom.ColumnIndex == cellReference.ColumnIndex)
{
return(false);
}
}
return(true);
}
private void FillTables(WorkbookPart workbookPart, string sheetId, List <System.Data.DataTable> dataTables)
{
var processedTablesRows = dataTables.ToDictionary(x => x.TableName, y => 0);
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheetId);
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
Helper.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._Field.Contains(":1")))
{
rowsForProcess = dataTables
.Where(x => fields.Any(y => y._Field.Split('.')[0] == x.TableName))
.Max(x => x.Rows.Count - processedTablesRows[x.TableName]);
}
else
{
rowsForProcess = 1;
for (int i = 0; i < fields.Count; i++)
{
fields[i] = new Models.Field(fields[i].Row, fields[i].Column, fields[i]._Field.Replace(":1", ""));
tableNamesForAddOneRow.Add(fields[i]._Field.Split('.')[0]);
}
}
for (int i = 0; i < rowsForProcess; i++)
{
var generatedRow = CreateRow(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)
{
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)
{
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();
}
}
}
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();
}
}
}
