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