Regex and Excel Cells

Question

There is a .NET opensource library called NPOI that allows you to manipulate Excel files. Unfortunately, their ShiftRows function does not adjust any cell references in formulas.

Therefore, I need to create a regex pattern to update them. Take for example a cell containing the following formula:

=(B7/C9) * (A10-B4)

I would like to bump any row references by 1 thus becoming

=(B8/C10) * (A11-B5)

Basically, I just need a pattern that will extract the numbers out into a "MatchCollection". I can do the rest.

Can anyone help?

Thanks.

Answer 1

Take a look at my answer to another question: Which regular expression is able to select excel column names in a formula in C#?

In that answer I match formulas and include code to increment the cell number (and column naming). Also, do a search on Excel column naming here and you'll find other ways to get the names generated or incremented. I probably could shorten the IncrementColumn MatchEvaluator's code using one of those methods but that's what I came up with at the time.

Answer 2

You should be very careful using regular expressions for this purpose. Excel formulas can become very complex, especially with user-defined functions or when functions apply to ranges. NPOI contains a Formula evaluation library and I think you should have a look at this as well - especially if the spreadsheets are out of your control.