public void WriteArray(Addressable <DefinedName, ExcelAddress.NamedRange> address, string[] value)
{
var startCell = address.Address;
var currentCell = startCell;
var sheet = this;
foreach (var x in value)
{
sheet.WriteValue(currentCell.StartCell, x);
currentCell = (ExcelAddress.NamedRange)currentCell.MoveLeft();
}
}
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;
}
}
}
}
public void WritePagedArray(Addressable <DefinedName, ExcelAddress.NamedRange> address, ExcelPagedArray value)
{
var startCell = address.Address;
var startRowIndex = startCell.RowIndex;
var currentCell = startCell;
var sheet = this;
sheet.CopyRow(startRowIndex, value.PageSize);
var rowsWrittenToThisSheet = 0;
var sheetNumber = 0;
for (var i = 0; i < value.Length; i++)
{
foreach (var x in value[i])
{
sheet.WriteValue(currentCell.StartCell, x);
currentCell = (ExcelAddress.NamedRange)currentCell.MoveLeft();
}
rowsWrittenToThisSheet++;
if (rowsWrittenToThisSheet < value.PageSize)
{
startCell = (ExcelAddress.NamedRange)startCell.MoveDown();
currentCell = startCell;
}
else
{
if ((i + 1) < value.Length)
{
sheetNumber++;
sheet = sheet.Clone(sheet.Name + "_" + sheetNumber);
sheet.CopyRow(address.Address.RowIndex, (int)Math.Min(value.PageSize, value.Length - i - 1));
startCell = address.Address;
currentCell = startCell;
rowsWrittenToThisSheet = 0;
}
}
}
}
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>());
}
}
}
public Addressable <DefinedName, ExcelAddress.NamedRange> TryGetDefinedName(string name, Addressable <DefinedName, ExcelAddress.NamedRange> defaultValue)
{
if (string.IsNullOrEmpty(name))
{
return(defaultValue);
}
Addressable <DefinedName, ExcelAddress.NamedRange> retValue;
if (this.DefinedNames.TryGetValue(name, out retValue))
{
return(retValue);
}
else
{
return(defaultValue);
}
}