private static void UpdateMergedCellReferences(WorksheetPart worksheetPart, uint rowIndex, bool isDeletedRow)
{
if (!worksheetPart.Worksheet.Elements <MergeCells>().Any())
{
return;
}
MergeCells mergeCells = worksheetPart.Worksheet.Elements <MergeCells>().FirstOrDefault();
if (mergeCells == null)
{
return;
}
// Grab all the merged cells that have a merge cell row index reference equal to or greater than the row index passed in
List <MergeCell> mergeCellsList = mergeCells.Elements <MergeCell>()
.Where(r => r.Reference.HasValue &&
(GetRowIndex(r.Reference.Value.Split(':').ElementAt(0)) >= rowIndex ||
GetRowIndex(r.Reference.Value.Split(':').ElementAt(1)) >= rowIndex)).ToList();
// Need to remove all merged cells that have a matching rowIndex when the row is deleted
if (isDeletedRow)
{
List <MergeCell> mergeCellsToDelete = mergeCellsList.Where(r => GetRowIndex(r.Reference.Value.Split(':').ElementAt(0)) == rowIndex ||
GetRowIndex(r.Reference.Value.Split(':').ElementAt(1)) == rowIndex).ToList();
// Delete all the matching merged cells
foreach (MergeCell cellToDelete in mergeCellsToDelete)
{
cellToDelete.Remove();
}
// Update the list to contain all merged cells greater than the deleted row index
mergeCellsList = mergeCells.Elements <MergeCell>()
.Where(r => r.Reference.HasValue &&
(GetRowIndex(r.Reference.Value.Split(':').ElementAt(0)) > rowIndex ||
GetRowIndex(r.Reference.Value.Split(':').ElementAt(1)) > rowIndex)).ToList();
}
// Either increment or decrement the row index on the merged cell reference
foreach (MergeCell mergeCell in mergeCellsList)
{
string[] cellReference = mergeCell.Reference.Value.Split(':');
if (GetRowIndex(cellReference.ElementAt(0)) >= rowIndex)
{
string columnName = GetColumnName(cellReference.ElementAt(0));
cellReference[0] = isDeletedRow ? columnName + (GetRowIndex(cellReference.ElementAt(0)) - 1).ToString() : IncrementCellReference(cellReference.ElementAt(0), CellReferencePartEnum.Row);
}
if (GetRowIndex(cellReference.ElementAt(1)) >= rowIndex)
{
string columnName = GetColumnName(cellReference.ElementAt(1));
cellReference[1] = isDeletedRow ? columnName + (GetRowIndex(cellReference.ElementAt(1)) - 1).ToString() : IncrementCellReference(cellReference.ElementAt(1), CellReferencePartEnum.Row);
}
mergeCell.Reference = new StringValue(cellReference[0] + ":" + cellReference[1]);
}
}
private void UpdateMergedCellReferences(uint rowIndex, bool isDeletedRow)
{
if (this.WorksheetPart.Worksheet.Elements <MergeCells>().Any())
{
MergeCells mergeCells = this.WorksheetPart.Worksheet.Elements <MergeCells>().FirstOrDefault();
if (mergeCells != null)
{
var mergeCellsList =
mergeCells.Elements <MergeCell>()
.Select(r => Addressable <MergeCell, ExcelAddress.CellRange> .Lift(r))
.Where(r => r.Address.Match(c => c.RowIndex >= rowIndex,
c => c.StartCell.RowIndex >= rowIndex || c.EndCell.RowIndex >= rowIndex)
);
if (isDeletedRow)
{
var mergeCellsToDelete =
mergeCellsList.Where(r =>
r.Address.Match(c => c.RowIndex == rowIndex,
c => c.StartCell.RowIndex == rowIndex || c.EndCell.RowIndex == rowIndex));
foreach (var cellToDelete in mergeCellsToDelete)
{
cellToDelete.Item.Remove();
}
// Update the list to contain all merged cells greater than the deleted row index
mergeCellsList =
mergeCells
.Elements <MergeCell>()
.Select(r => Addressable <MergeCell, ExcelAddress.CellRange> .Lift(r))
.Where(r =>
r.Address.Match(c => c.RowIndex > rowIndex,
c => c.StartCell.RowIndex > rowIndex || c.EndCell.RowIndex > rowIndex))
.ToList();
}
// Either increment or decrement the row index on the merged cell reference
foreach (var mergeCell in mergeCellsList.ToArray())
{
var addr = isDeletedRow ? mergeCell.Address.MoveUp() : mergeCell.Address.MoveDown();
mergeCell.Item.Reference = addr.ReferenceString;
}
}
}
}
private void DeleteRows(SpreadsheetDocument RecultDoc, WorksheetWriter RecultWriter, uint rowIndex, uint count)
{
RecultWriter.DeleteRows(rowIndex, count);
if (RecultWriter.Worksheet.Worksheet.Elements <MergeCells>().Count() > 0)
{
MergeCells mergeCells = RecultWriter.Worksheet.Worksheet.Elements <MergeCells>().First();
//Удаление обединенных ячеек попавших в облать удаления
for (int i = mergeCells.Elements <MergeCell>().Count() - 1; i >= 0; i--)
{
string Range = mergeCells.Elements <MergeCell>().ElementAt(i).Reference.Value;
UInt32 startRow = SpreadsheetReader.RowFromReference(FirstRefFromRange(Range));
UInt32 endRow = SpreadsheetReader.RowFromReference(LastRefFromRange(Range));
if (startRow >= rowIndex && endRow <= (rowIndex + count - 1))
{
mergeCells.Elements <MergeCell>().ElementAt(i).Remove();
}
}
//Обновления ссылок всех ниже стоящих обединенных ячеек
foreach (MergeCell mCell in mergeCells.Elements <MergeCell>())
{
string Range = mCell.Reference.Value;
string StartRef = FirstRefFromRange(Range);
string EndRef = LastRefFromRange(Range);
UInt32 startRow = SpreadsheetReader.RowFromReference(StartRef);
UInt32 endRow = SpreadsheetReader.RowFromReference(EndRef);
if (startRow > (rowIndex + count - 1))
{
string newRangeFirst = SpreadsheetReader.ColumnFromReference(StartRef) + (SpreadsheetReader.RowFromReference(StartRef) - count);
string newRangeEnd = SpreadsheetReader.ColumnFromReference(EndRef) + (SpreadsheetReader.RowFromReference(EndRef) - count);
mCell.Reference.Value = newRangeFirst + ":" + newRangeEnd;
}
}
}
}