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 IDictionary <string, Addressable <DefinedName, ExcelAddress.NamedRange> > GetDefinedNames()
{
var names = new Dictionary <string, Addressable <DefinedName, ExcelAddress.NamedRange> >();
var dns = this.workbookPart.Workbook.DefinedNames;
if (dns != null)
{
foreach (var openXmlElement in dns)
{
var dn = (DefinedName)openXmlElement;
if (!dn.Name.Value.Contains("Print_Area"))
{
names.Add(dn.Name.Value, Addressable <DefinedName, ExcelAddress.NamedRange> .Lift(dn));
}
}
}
return(names);
}
public void InsertCopyOfRow(uint rowIndex, uint targetRowIndex, bool rebuildIndex)
{
var sheetData = this.WorksheetPart.Worksheet.GetFirstChild <SheetData>();
var rows = sheetData.Elements <Row>();
var lastRow = rows.Last().RowIndex;
var currRow = rows.FirstOrDefault(r => r.RowIndex == rowIndex);
if (currRow != null)
{
var newRow = (Row)currRow.CloneNode(true);
if (targetRowIndex >= lastRow)
{
this.AppendRow(targetRowIndex, newRow, rebuildIndex);
}
else
{
this.InsertRow(targetRowIndex, newRow, rebuildIndex);
}
var mergeCells = this.WorksheetPart.Worksheet.Elements <MergeCells>().FirstOrDefault();
if (mergeCells != null)
{
var rowMergedCells = 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))
.Select(r =>
{
var cell = (MergeCell)r.Item.CloneNode(true);
cell.Reference = r.Address.MoveToRow(targetRowIndex).ReferenceString;
return(cell);
});
mergeCells.Append(rowMergedCells.Cast <OpenXmlElement>());
}
}
}