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