| public static GetAddress ( int Row, bool AbsoluteRow, int Column, bool AbsoluteCol ) : string | ||
| Row | int | The number of the row |
| AbsoluteRow | bool | Absolute row |
| Column | int | The number of the column in the worksheet |
| AbsoluteCol | bool | Absolute column |
| Résultat | string | |
/// <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);
}
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();
}
