|
private DeleteColumn ( int col, int cols ) : |
||
| col | int | |
| cols | int | |
| return | ||
public void InsertDeleteTest()
{
var addr = new ExcelAddressBase("A1:B3");
Assert.AreEqual(addr.AddRow(2, 4).Address, "A1:B7");
Assert.AreEqual(addr.AddColumn(2, 4).Address, "A1:F3");
Assert.AreEqual(addr.DeleteColumn(2, 1).Address, "A1:A3");
Assert.AreEqual(addr.DeleteRow(2, 2).Address, "A1:B1");
Assert.AreEqual(addr.DeleteRow(1, 3), null);
Assert.AreEqual(addr.DeleteColumn(1, 2), null);
}
/// <summary>
/// Shifts all comments based on their address and the location of inserted rows and columns.
/// </summary>
/// <param name="fromRow">The start row.</param>
/// <param name="fromCol">The start column.</param>
/// <param name="rows">The number of rows to insert.</param>
/// <param name="columns">The number of columns to insert.</param>
internal void Delete(int fromRow, int fromCol, int rows, int columns)
{
List <ExcelComment> deletedComments = new List <ExcelComment>();
ExcelAddressBase address = null;
foreach (ExcelComment comment in _list)
{
address = new ExcelAddressBase(comment.Address);
if (fromCol > 0 && address._fromCol >= fromCol)
{
address = address.DeleteColumn(fromCol, columns);
}
if (fromRow > 0 && address._fromRow >= fromRow)
{
address = address.DeleteRow(fromRow, rows);
}
if (address == null || address.Address == "#REF!")
{
deletedComments.Add(comment);
}
else
{
comment.Reference = address.Address;
}
}
foreach (var comment in deletedComments)
{
Remove(comment);
}
}
/// <summary>
/// Shifts all comments based on their address and the location of inserted rows and columns.
/// </summary>
/// <param name="fromRow">The start row.</param>
/// <param name="fromCol">The start column.</param>
/// <param name="rows">The number of rows to insert.</param>
/// <param name="columns">The number of columns to insert.</param>
/// <param name="toRow">If the delete is in a range, this is the end row</param>
/// <param name="toCol">If the delete is in a range, this the end column</param>
internal void Delete(int fromRow, int fromCol, int rows, int columns, int toRow = ExcelPackage.MaxRows, int toCol = ExcelPackage.MaxColumns)
{
List <ExcelComment> deletedComments = new List <ExcelComment>();
ExcelAddressBase address = null;
foreach (ExcelComment comment in _list.Where(x => x != null))
{
address = new ExcelAddressBase(comment.Address);
if (columns > 0 && address._fromCol >= fromCol &&
address._fromRow >= fromRow && address._toRow <= toRow)
{
address = address.DeleteColumn(fromCol, columns);
}
if (rows > 0 && address._fromRow >= fromRow &&
address._fromCol >= fromCol && address._toCol <= toCol)
{
address = address.DeleteRow(fromRow, rows);
}
if (address == null || address.Address == "#REF!")
{
deletedComments.Add(comment);
}
else
{
comment.Reference = address.Address;
}
}
foreach (var comment in deletedComments)
{
comment.TopNode.ParentNode.RemoveChild(comment.TopNode); //Remove VML
comment._commentHelper.TopNode.ParentNode.RemoveChild(comment._commentHelper.TopNode); //Remove Comment
}
}
/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="Formula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <param name="setFixed">Fixed address</param>
/// <returns></returns>
internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, bool setFixed = false)
{
//return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement);
var d = new Dictionary <string, object>();
try
{
var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
var tokens = sct.Tokenize(Formula);
String f = "";
foreach (var t in tokens)
{
if (t.TokenType == TokenType.ExcelAddress)
{
var a = new ExcelAddressBase(t.Value);
if (!string.IsNullOrEmpty(a._ws) || !string.IsNullOrEmpty(a._wb))
{
// This address is in a different worksheet or workbook, thus no update is required
f += a.Address;
continue;
}
if (rowIncrement > 0)
{
a = a.AddRow(afterRow, rowIncrement, setFixed);
}
else if (rowIncrement < 0)
{
a = a.DeleteRow(afterRow, -rowIncrement, setFixed);
}
if (colIncrement > 0)
{
a = a.AddColumn(afterColumn, colIncrement, setFixed);
}
else if (colIncrement < 0)
{
a = a.DeleteColumn(afterColumn, -colIncrement, setFixed);
}
if (a == null || !a.IsValidRowCol())
{
f += "#REF!";
}
else
{
f += a.Address;
}
}
else
{
f += t.Value;
}
}
return(f);
}
catch //Invalid formula, skip updateing addresses
{
return(Formula);
}
}
/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="Formula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <returns></returns>
internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn)
{
//return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement);
var d = new Dictionary <string, object>();
try
{
var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
var tokens = sct.Tokenize(Formula);
String f = "";
foreach (var t in tokens)
{
if (t.TokenType == TokenType.ExcelAddress)
{
ExcelAddressBase a = new ExcelAddressBase(t.Value);
if (rowIncrement > 0)
{
a = a.AddRow(afterRow, rowIncrement);
}
else if (rowIncrement < 0)
{
a = a.DeleteRow(afterRow, -rowIncrement);
}
if (colIncrement > 0)
{
a = a.AddColumn(afterColumn, colIncrement);
}
else if (colIncrement > 0)
{
a = a.DeleteColumn(afterColumn, -colIncrement);
}
if (a == null)
{
f += "#REF!";
}
else
{
f += a.Address;
}
}
else
{
f += t.Value;
}
}
return(f);
}
catch //Invalid formula, skip updateing addresses
{
return(Formula);
}
}
/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="Formula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <returns></returns>
internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn)
{
//return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement);
var d=new Dictionary<string, object>();
try
{
var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
var tokens = sct.Tokenize(Formula);
String f = "";
foreach (var t in tokens)
{
if (t.TokenType == TokenType.ExcelAddress)
{
ExcelAddressBase a = new ExcelAddressBase(t.Value);
if (rowIncrement > 0)
{
a = a.AddRow(afterRow, rowIncrement);
}
else if (rowIncrement < 0)
{
a = a.DeleteRow(afterRow, -rowIncrement);
}
if (colIncrement > 0)
{
a = a.AddColumn(afterColumn, colIncrement);
}
else if (colIncrement > 0)
{
a = a.DeleteColumn(afterColumn, -colIncrement);
}
if (a == null)
{
f += "#REF!";
}
else
{
f += a.Address;
}
}
else
{
f += t.Value;
}
}
return f;
}
catch //Invalid formula, skip updateing addresses
{
return Formula;
}
}
/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="formula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <param name="currentSheet">The sheet that contains the formula currently being processed.</param>
/// <param name="modifiedSheet">The sheet where cells are being inserted or deleted.</param>
/// <param name="setFixed">Fixed address</param>
/// <returns>The updated version of the <paramref name="formula"/>.</returns>
internal static string UpdateFormulaReferences(string formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false)
{
var d = new Dictionary <string, object>();
try
{
var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
var tokens = sct.Tokenize(formula);
String f = "";
foreach (var t in tokens)
{
if (t.TokenType == TokenType.ExcelAddress)
{
var a = new ExcelAddressBase(t.Value);
var referencesModifiedWorksheet = (string.IsNullOrEmpty(a._ws) && currentSheet.Equals(modifiedSheet, StringComparison.CurrentCultureIgnoreCase)) || modifiedSheet.Equals(a._ws, StringComparison.CurrentCultureIgnoreCase);
if (!setFixed && (!string.IsNullOrEmpty(a._wb) || !referencesModifiedWorksheet))
{
// This address is in a different worksheet or workbook; no update is required.
f += a.Address;
continue;
}
// Persist fully-qualified worksheet references.
if (!string.IsNullOrEmpty(a._ws))
{
f += $"'{a._ws}'!";
}
if (rowIncrement > 0)
{
a = a.AddRow(afterRow, rowIncrement, setFixed);
}
else if (rowIncrement < 0)
{
a = a.DeleteRow(afterRow, -rowIncrement, setFixed);
}
if (colIncrement > 0)
{
a = a.AddColumn(afterColumn, colIncrement, setFixed);
}
else if (colIncrement < 0)
{
a = a.DeleteColumn(afterColumn, -colIncrement, setFixed);
}
if (a == null || !a.IsValidRowCol())
{
f += "#REF!";
}
else
{
// If the address was not shifted, then a.Address will still have the sheet name.
var address = a.Address.Split('!');
if (address.Length > 1)
{
f += address[1];
}
else
{
f += a.Address;
}
}
}
else
{
f += t.Value;
}
}
return(f);
}
catch //Invalid formula, skip updating addresses
{
return(formula);
}
}
/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="Formula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <param name="setFixed">Fixed address</param>
/// <returns></returns>
internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, bool setFixed=false)
{
//return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement);
var d=new Dictionary<string, object>();
try
{
var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
var tokens = sct.Tokenize(Formula);
String f = "";
foreach (var t in tokens)
{
if (t.TokenType == TokenType.ExcelAddress)
{
var a = new ExcelAddressBase(t.Value);
if (!string.IsNullOrEmpty(a._ws) || !string.IsNullOrEmpty(a._wb))
{
// This address is in a different worksheet or workbook, thus no update is required
f += a.Address;
continue;
}
if (rowIncrement > 0)
{
a = a.AddRow(afterRow, rowIncrement, setFixed);
}
else if (rowIncrement < 0)
{
a = a.DeleteRow(afterRow, -rowIncrement, setFixed);
}
if (colIncrement > 0)
{
a = a.AddColumn(afterColumn, colIncrement, setFixed);
}
else if (colIncrement < 0)
{
a = a.DeleteColumn(afterColumn, -colIncrement, setFixed);
}
if (a == null || !a.IsValidRowCol())
{
f += "#REF!";
}
else
{
f += a.Address;
}
}
else
{
f += t.Value;
}
}
return f;
}
catch //Invalid formula, skip updateing addresses
{
return Formula;
}
}
/// <summary>
/// Shifts all comments based on their address and the location of inserted rows and columns.
/// </summary>
/// <param name="fromRow">The start row.</param>
/// <param name="fromCol">The start column.</param>
/// <param name="rows">The number of rows to insert.</param>
/// <param name="columns">The number of columns to insert.</param>
internal void Delete(int fromRow, int fromCol, int rows, int columns)
{
List<ExcelComment> deletedComments = new List<ExcelComment>();
ExcelAddressBase address = null;
foreach (ExcelComment comment in _list)
{
address = new ExcelAddressBase(comment.Address);
if (fromCol>0 && address._fromCol >= fromCol)
{
address = address.DeleteColumn(fromCol, columns);
}
if(fromRow > 0 && address._fromRow >= fromRow)
{
address = address.DeleteRow(fromRow, rows);
}
if(address.Address=="#REF!")
{
deletedComments.Add(comment);
}
else
{
comment.Reference = address.Address;
}
}
foreach(var comment in deletedComments)
{
Remove(comment);
}
}
/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="formula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <param name="currentSheet">The sheet that contains the formula currently being processed.</param>
/// <param name="modifiedSheet">The sheet where cells are being inserted or deleted.</param>
/// <param name="setFixed">Fixed address</param>
/// <returns>The updated version of the <paramref name="formula"/>.</returns>
internal static string UpdateFormulaReferences(string formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false)
{
try
{
var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
var tokens = sct.Tokenize(formula);
var f = "";
foreach (var t in tokens)
{
if (t.TokenTypeIsSet(TokenType.ExcelAddress))
{
var address = new ExcelAddressBase(t.Value);
if ((!string.IsNullOrEmpty(address._wb) || !IsReferencesModifiedWorksheet(currentSheet, modifiedSheet, address)) && !setFixed)
{
f += address.Address;
continue;
}
if (!string.IsNullOrEmpty(address._ws)) //The address has worksheet.
{
if (t.Value.IndexOf("'!") >= 0)
{
f += $"'{address._ws}'!";
}
else
{
f += $"{address._ws}!";
}
}
if (!address.IsFullColumn)
{
if (rowIncrement > 0)
{
address = address.AddRow(afterRow, rowIncrement, setFixed);
}
else if (rowIncrement < 0)
{
if (address._fromRowFixed == false && (address._fromRow >= afterRow && address._toRow < afterRow - rowIncrement))
{
address = null;
}
else
{
address = address.DeleteRow(afterRow, -rowIncrement, setFixed);
}
}
}
if (address != null && !address.IsFullRow)
{
if (colIncrement > 0)
{
address = address.AddColumn(afterColumn, colIncrement, setFixed);
}
else if (colIncrement < 0)
{
if (address._fromColFixed == false && (address._fromCol >= afterColumn && address._toCol < afterColumn - colIncrement))
{
address = null;
}
else
{
address = address.DeleteColumn(afterColumn, -colIncrement, setFixed);
}
}
}
if (address == null || !address.IsValidRowCol())
{
f += "#REF!";
}
else
{
var ix = address.Address.LastIndexOf('!');
if (ix > 0)
{
f += address.Address.Substring(ix + 1);
}
else
{
f += address.Address;
}
}
}
else
{
f += t.Value;
}
}
return(f);
}
catch //Invalid formula, return formula
{
return(formula);
}
}
/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="formula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <param name="currentSheet">The sheet that contains the formula currently being processed.</param>
/// <param name="modifiedSheet">The sheet where cells are being inserted or deleted.</param>
/// <param name="setFixed">Fixed address</param>
/// <returns>The updated version of the <paramref name="formula"/>.</returns>
internal static string UpdateFormulaReferences(string formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false)
{
var d = new Dictionary<string, object>();
try
{
var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
var tokens = sct.Tokenize(formula);
String f = "";
foreach (var t in tokens)
{
if (t.TokenType == TokenType.ExcelAddress)
{
var a = new ExcelAddressBase(t.Value);
var referencesModifiedWorksheet = (string.IsNullOrEmpty(a._ws) && currentSheet.Equals(modifiedSheet, StringComparison.CurrentCultureIgnoreCase)) || modifiedSheet.Equals(a._ws, StringComparison.CurrentCultureIgnoreCase);
if (!setFixed && (!string.IsNullOrEmpty(a._wb) || !referencesModifiedWorksheet))
{
// This address is in a different worksheet or workbook; no update is required.
f += a.Address;
continue;
}
// Persist fully-qualified worksheet references.
if (!string.IsNullOrEmpty(a._ws))
{
f += $"'{a._ws}'!";
}
if (rowIncrement > 0)
{
a = a.AddRow(afterRow, rowIncrement, setFixed);
}
else if (rowIncrement < 0)
{
a = a.DeleteRow(afterRow, -rowIncrement, setFixed);
}
if (colIncrement > 0)
{
a = a.AddColumn(afterColumn, colIncrement, setFixed);
}
else if (colIncrement < 0)
{
a = a.DeleteColumn(afterColumn, -colIncrement, setFixed);
}
if (a == null || !a.IsValidRowCol())
{
f += "#REF!";
}
else
{
// If the address was not shifted, then a.Address will still have the sheet name.
var address = a.Address.Split('!');
if (address.Length > 1)
f += address[1];
else
f += a.Address;
}
}
else
{
f += t.Value;
}
}
return f;
}
catch //Invalid formula, skip updating addresses
{
return formula;
}
}