private void Remove()
{
var selection = GetRange();
if (selection == null)
{
return;
}
if (MessageBox.Show(null, Resources.There_is_no_undo, Resources.Title, MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
{
return;
}
var errorCells = new List <string>();
var calculation = xl.Calculation;
xl.Calculation = XlCalculation.xlCalculationManual;
try
{
foreach (Range cell in selection)
{
var substitutionFunction = new Func <ParserResult, string>(r =>
{
try
{
var offset = r.Value;
offset = offset.Replace("COLUMN()", cell.Column.ToString());
offset = offset.Replace("ROW()", cell.Row.ToString());
var result = cell.Worksheet.Evaluate(offset) as Range;
if (result != null)
{
if (result.Parent.Name == cell.Parent.Name)
{
return(result.get_Address(External: false, RowAbsolute: false, ColumnAbsolute: false));
}
return(string.Format("'{0}'!{1}", result.Parent.Name, result.get_Address(External: false, RowAbsolute: false, ColumnAbsolute: false)));
}
}
catch
{
errorCells.Add(cell.Address);
}
return(r.Value);
});
var parser = new FunctionParser("OFFSET", cell.Formula, substitutionFunction);
if (parser.Parse() == ParserState.Success)
{
cell.Formula = parser.Output;
}
}
if (errorCells.Count > 0)
{
MessageBox.Show(null, string.Format(Resources.The_following_cells_were_skipped, String.Join(", ", errorCells)), Resources.Title, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
finally
{
xl.Calculation = calculation;
}
}
private void Remove()
{
var selection = GetRange();
if (selection == null)
return;
if (MessageBox.Show(null, Resources.There_is_no_undo, Resources.Title, MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
return;
var errorCells = new List<string>();
var calculation = xl.Calculation;
xl.Calculation = XlCalculation.xlCalculationManual;
try
{
foreach (Range cell in selection)
{
var substitutionFunction = new Func<ParserResult, string>(r =>
{
try
{
var offset = r.Value;
offset = offset.Replace("COLUMN()", cell.Column.ToString());
offset = offset.Replace("ROW()", cell.Row.ToString());
var result = cell.Worksheet.Evaluate(offset) as Range;
if (result != null)
{
if (result.Parent.Name == cell.Parent.Name)
return result.get_Address(External: false, RowAbsolute: false, ColumnAbsolute: false);
return string.Format("'{0}'!{1}", result.Parent.Name, result.get_Address(External: false, RowAbsolute: false, ColumnAbsolute: false));
}
}
catch
{
errorCells.Add(cell.Address);
}
return r.Value;
});
var parser = new FunctionParser("OFFSET", cell.Formula, substitutionFunction);
if (parser.Parse() == ParserState.Success)
cell.Formula = parser.Output;
}
if (errorCells.Count > 0)
MessageBox.Show(null, string.Format(Resources.The_following_cells_were_skipped, String.Join(", ", errorCells)), Resources.Title, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
xl.Calculation = calculation;
}
}
