(Using Apose.Cells 8.3.2.1)
The problem we're having is that the formulae just aren't saving.
This is the test code that isn't working :
var workbooks = new List
{
new Workbook(@"C:\Temp\Book1.xls"),
new Workbook(@"C:\Temp\Book2.xls")
};
var regex = new Regex(@"!([A-Za-z0-9_]+)");
foreach (var workbook in workbooks)
{
// Named Ranges -> "FIX_" + name
foreach (var name in workbook.Worksheets.Names)
{
name.Text = "FIX_" + name.Text;
}
// Update Formula References to renamed Named Ranges
foreach (var worksheet in workbook.Worksheets)
{
foreach (Cell cell in worksheet.Cells)
{
if (!cell.IsFormula || cell.IsInTable || (cell.IsInArray && !cell.IsArrayHeader)) continue;
if (!regex.IsMatch(cell.Formula)) continue;
var newformula = regex.Replace(cell.Formula, "!FIX_$1");
cell.Formula = newformula;
}
}
}
foreach (var workbook in workbooks)
{
workbook.Save(workbook.FileName, SaveFormat.Excel97To2003);
}
"Book1.xls" and "Book2.xls" are new excel 97-2003 spreadsheets with only one cell in each. A1 in Book1.xls contains the value "1337" and the workbook has a single named range referencing A1 called "MyNamedRange". A1 in Book2.xls contains the formula "='C:\Temp\Book1.xls'!MyNamedRange".
When I run the above code, the named range in Book1 is updated, but the formula in Book2 remains unchanged, even though I know that the line "cell.Formula = newformula" has actually been run on that formula.
This works for all formulae containing named ranges within the workbook, but external references don't appear to save.