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); } }