internal ExcelComment(XmlNamespaceManager ns, XmlNode commentTopNode, ExcelRangeBase cell) : base(null, cell, cell.Worksheet.VmlDrawingsComments.NameSpaceManager) { //_commentHelper = new XmlHelper(ns, commentTopNode); _commentHelper = XmlHelperFactory.Create(ns, commentTopNode); var textElem = commentTopNode.SelectSingleNode("d:text", ns); if (textElem == null) { textElem = commentTopNode.OwnerDocument.CreateElement("text", ExcelPackage.schemaMain); commentTopNode.AppendChild(textElem); } if (!cell.Worksheet._vmlDrawings.ContainsKey(ExcelAddress.GetCellID(cell.Worksheet.SheetID, cell.Start.Row, cell.Start.Column))) { cell.Worksheet._vmlDrawings.Add(cell); } TopNode = cell.Worksheet.VmlDrawingsComments[ExcelCellBase.GetCellID(cell.Worksheet.SheetID, cell.Start.Row, cell.Start.Column)].TopNode; RichText = new ExcelRichTextCollection(ns, textElem); var tNode = textElem.SelectSingleNode("d:t", ns); if (tNode != null) { _text = tNode.InnerText; } }
/// <summary> /// Gets the formula of a named range relative to the specified <paramref name="relativeRow"/> and <paramref name="relativeColumn"/>. /// </summary> /// <param name="relativeRow">The row from which the named range is referenced.</param> /// <param name="relativeColumn">The column from which the named range is referenced.</param> /// <returns>The updated formula relative to the specified <paramref name="relativeRow"/> and <paramref name="relativeColumn"/>.</returns> public IEnumerable <Token> GetRelativeNameFormula(int relativeRow, int relativeColumn) { var tokens = this.Workbook.FormulaParser.Lexer.Tokenize(this.NameFormula); foreach (var token in tokens) { if (token.TokenType == TokenType.ExcelAddress) { var address = new ExcelAddress(token.Value); // Do not update external references. if (!string.IsNullOrEmpty(address?.Workbook)) { continue; } int fromRow = address._fromRow; int fromColumn = address._fromCol; int toRow = address._toRow; int toColumn = address._toCol; if (!address._isFullColumn) { fromRow = this.GetRelativeLocation(address._fromRowFixed, address._fromRow, relativeRow, ExcelPackage.MaxRows); toRow = this.GetRelativeLocation(address._toRowFixed, address._toRow, relativeRow, ExcelPackage.MaxRows); } if (!address._isFullRow) { fromColumn = this.GetRelativeLocation(address._fromColFixed, address._fromCol, relativeColumn, ExcelPackage.MaxColumns); toColumn = this.GetRelativeLocation(address._toColFixed, address._toCol, relativeColumn, ExcelPackage.MaxColumns); } var updatedAddress = ExcelCellBase.GetAddress(fromRow, fromColumn, toRow, toColumn, address._fromRowFixed, address._fromColFixed, address._toRowFixed, address._toColFixed); token.Value = ExcelCellBase.GetFullAddress(address.WorkSheet, updatedAddress); } } return(tokens); }
/// <summary> /// Returns the letter corresponding to the supplied 1-based column index. /// </summary> /// <param name="column">Index of the column (1-based)</param> /// <returns>The corresponding letter, like A for 1.</returns> public static string GetColumnLetter(int column) { if (column > ExcelPackage.MaxColumns || column < 1) { throw new InvalidOperationException("Invalid 1-based column index: " + column + ". Valid range is 1 to " + ExcelPackage.MaxColumns); } return(ExcelCellBase.GetColumnLetter(column)); }
private static string BuildNewAddress(ExcelNamedRange namedRange, string newAddress) { if (namedRange.FullAddress.Contains("!")) { var worksheet = namedRange.FullAddress.Split('!')[0]; worksheet = worksheet.Trim('\''); newAddress = ExcelCellBase.GetFullAddress(worksheet, newAddress); } return(newAddress); }
/// <summary> /// Indexer for the comments collection /// </summary> /// <param name="cell">The cell</param> /// <returns>The comment</returns> public ExcelComment this[ExcelCellAddress cell] { get { ulong cellID = ExcelCellBase.GetCellID(Worksheet.SheetID, cell.Row, cell.Column); if (_comments.IndexOf(cellID) >= 0) { return(_comments[cellID] as ExcelComment); } else { return(null); } } }
private void InsertRows(int rowFrom, int rows, ExcelNamedRange namedRange) { if (rows > 0) { if (rowFrom <= namedRange.Start.Row) { var newAddress = ExcelCellBase.GetAddress(namedRange.Start.Row + rows, namedRange.Start.Column, namedRange.End.Row + rows, namedRange.End.Column); namedRange.Address = BuildNewAddress(namedRange, newAddress); } else if (rowFrom <= namedRange.End.Row && namedRange.End.Row + rows <= ExcelPackage.MaxRows) { var newAddress = ExcelCellBase.GetAddress(namedRange.Start.Row, namedRange.Start.Column, namedRange.End.Row + rows, namedRange.End.Column); namedRange.Address = BuildNewAddress(namedRange, newAddress); } } }
private void InsertColumns(int colFrom, int cols, ExcelNamedRange namedRange) { if (colFrom > 0) { if (colFrom <= namedRange.Start.Column) { var newAddress = ExcelCellBase.GetAddress(namedRange.Start.Row, namedRange.Start.Column + cols, namedRange.End.Row, namedRange.End.Column + cols); namedRange.Address = BuildNewAddress(namedRange, newAddress); } else if (colFrom <= namedRange.End.Column && namedRange.End.Column + cols < ExcelPackage.MaxColumns) { var newAddress = ExcelCellBase.GetAddress(namedRange.Start.Row, namedRange.Start.Column, namedRange.End.Row, namedRange.End.Column + cols); namedRange.Address = BuildNewAddress(namedRange, newAddress); } } }
/// <summary> /// Returns a new range which represents the column header. /// </summary> /// <param name="worksheet">The worksheet.</param> /// <param name="row">The current row.</param> /// <param name="column">The column header definition.</param> /// <returns> /// Returns a new <see cref="T:OfficeOpenXml.ExcelRange" /> which contains de column header representation. /// </returns> /// <exception cref="T:System.ArgumentNullException">The value specified is <c>null</c>.</exception> public static ExcelRange GetRangeFromModel(this ExcelWorksheet worksheet, int row, ColumnHeaderModel column) { SentinelHelper.ArgumentNull(worksheet); SentinelHelper.ArgumentNull(column); var fields = column.Owner.Parent.Fields; var fromField = fields[column.From]; var toField = fields[column.To]; var from = fields.IndexOf(fromField) + 1; var to = fields.IndexOf(toField) + 1; var range = ExcelCellBase.GetAddress(row, from, row, to); var cell = worksheet.Cells[range]; return(cell); }
//private void InsertRows(int rowFrom, int rows, ExcelNamedRange namedRange) //{ // if (rows > 0) // { // if (rowFrom <= namedRange.Start.Row) // { // var newAddress = ExcelCellBase.GetAddress(namedRange.Start.Row + rows, namedRange.Start.Column, namedRange.End.Row + rows, namedRange.End.Column); // namedRange.Address = BuildNewAddress(namedRange, newAddress); // } // else if (rowFrom <= namedRange.End.Row && namedRange.End.Row+rows <= ExcelPackage.MaxRows) // { // var newAddress = ExcelCellBase.GetAddress(namedRange.Start.Row, namedRange.Start.Column, namedRange.End.Row + rows, namedRange.End.Column); // namedRange.Address = BuildNewAddress(namedRange, newAddress); // } // } //} private void InsertRows(int rowFrom, int rows, ExcelNamedRange namedRange) { if (rows > 0) { if (namedRange.Addresses?.Count > 0) //Contains multiple ranges { foreach (var rng in namedRange.Addresses) { if (rowFrom <= rng.Start.Row) { var newAddress = ExcelCellBase.GetAddress(rng.Start.Row + rows, rng.Start.Column, rng.End.Row + rows, rng.End.Column); rng.Address = BuildNewAddress(namedRange, newAddress); } else if (rowFrom <= rng.End.Row && rng.End.Row + rows <= ExcelPackage.MaxRows) { var newAddress = ExcelCellBase.GetAddress(rng.Start.Row, rng.Start.Column, rng.End.Row + rows, rng.End.Column); rng.Address = BuildNewAddress(namedRange, newAddress); } } namedRange.Address = namedRange.FullAddress; } else { if (rowFrom <= namedRange.Start.Row) { var newAddress = ExcelCellBase.GetAddress(namedRange.Start.Row + rows, namedRange.Start.Column, namedRange.End.Row + rows, namedRange.End.Column); namedRange.Address = BuildNewAddress(namedRange, newAddress); } else if (rowFrom <= namedRange.End.Row && namedRange.End.Row + rows <= ExcelPackage.MaxRows) { var newAddress = ExcelCellBase.GetAddress(namedRange.Start.Row, namedRange.Start.Column, namedRange.End.Row + rows, namedRange.End.Column); namedRange.Address = BuildNewAddress(namedRange, newAddress); } } } }
/// <summary> /// Freeze the columns/rows to left and above the cell /// </summary> /// <param name="Row"></param> /// <param name="Column"></param> public void FreezePanes(int Row, int Column) { //TODO:fix this method to handle splits as well. if (Row == 1 && Column == 1) { UnFreezePanes(); } string sqRef = SelectedRange, activeCell = ActiveCell; XmlElement paneNode = TopNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement; if (paneNode == null) { CreateNode(_paneNodePath); paneNode = TopNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement; } paneNode.RemoveAll(); //Clear all attributes if (Column > 1) { paneNode.SetAttribute("xSplit", (Column - 1).ToString()); } if (Row > 1) { paneNode.SetAttribute("ySplit", (Row - 1).ToString()); } paneNode.SetAttribute("topLeftCell", ExcelCellBase.GetAddress(Row, Column)); paneNode.SetAttribute("state", "frozen"); RemoveSelection(); if (Row > 1 && Column == 1) { paneNode.SetAttribute("activePane", "bottomLeft"); XmlElement sel = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain); sel.SetAttribute("pane", "bottomLeft"); if (activeCell != "") { sel.SetAttribute("activeCell", activeCell); } if (sqRef != "") { sel.SetAttribute("sqref", sqRef); } sel.SetAttribute("sqref", sqRef); TopNode.InsertAfter(sel, paneNode); } else if (Column > 1 && Row == 1) { paneNode.SetAttribute("activePane", "topRight"); XmlElement sel = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain); sel.SetAttribute("pane", "topRight"); if (activeCell != "") { sel.SetAttribute("activeCell", activeCell); } if (sqRef != "") { sel.SetAttribute("sqref", sqRef); } TopNode.InsertAfter(sel, paneNode); } else { paneNode.SetAttribute("activePane", "bottomRight"); XmlElement sel1 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain); sel1.SetAttribute("pane", "topRight"); string cell = ExcelCellBase.GetAddress(1, Column); sel1.SetAttribute("activeCell", cell); sel1.SetAttribute("sqref", cell); paneNode.ParentNode.InsertAfter(sel1, paneNode); XmlElement sel2 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain); cell = ExcelCellBase.GetAddress(Row, 1); sel2.SetAttribute("pane", "bottomLeft"); sel2.SetAttribute("activeCell", cell); sel2.SetAttribute("sqref", cell); sel1.ParentNode.InsertAfter(sel2, sel1); XmlElement sel3 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain); sel3.SetAttribute("pane", "bottomRight"); if (activeCell != "") { sel3.SetAttribute("activeCell", activeCell); } if (sqRef != "") { sel3.SetAttribute("sqref", sqRef); } sel2.ParentNode.InsertAfter(sel3, sel2); } Panes = LoadPanes(); }