Ticket T933994

Visible to All Users

How to parse a cell formula and extract cell references used in this formula

Tridip

created 4 years ago

I have application which read data from excel file including formula. my routine extract all cell address from formula.
few sample formula which my application can still parse using RegEx follows

a) (W2/R2)-1
b) W23-U23

But Now a formula bit long which my RegEx could not parse. the formula looks like (U2+W2)/2*1000/W25
This is my code which parse formula and iterate in cell address

C#
string regex = @"\$?[A-Z]{1,3}\$?[0-9]{1,7}";
string strDefaultCalculations="(U2+W2)/2*1000/W25";

List<string> lstAddress = Regex.Matches(strDefaultCalculations, regex).Cast<Match>().Select(m => m.Value).ToList();

foreach (var data in lstAddress)
{

}

but above routine can parse the formula like (W2/R2)-1 AND W23-U23

So i thought i am using DevExpress Spread sheet control. so just curious to know that Spread sheet control has any function where i will pass my complex formula and that function will return all cell address as a list of string ?

if Spread sheet control does not have this functionality then please tell me what component exist in devexpress library which can do the job what i am looking for. i am using DXperience Subscription.

it is very urgent please guide me how to achieve my goal. if any 3rd party free library does this job then mention the name too.

thanks

Answers approved by DevExpress Support

DevExpress Support Team

created 4 years ago

Hi,

You can use the Formula Engine for this purpose. First, use the FormulaEngine.Parse method to get a ParsedExpression instance. Then, use the ParsedExpression.GetRanges method to get a collection of referenced cell ranges.

Should you have further questions, please let me know.

Show previous comments (9)

Tridip 4 years ago

Why do not you post the sample code
(U2+W2)/2*1000/W25 this is my formula from where i need to extract cell address

what dll i need to import? . i am using DXperiece subscription. so DXperiece subscription has the formula engine ?

please see my code and translate that code using DX formula engine with sample code. thanks

Tridip 4 years ago

i am using DX version 18.2.4

DevExpress Support Team 4 years ago

Hi Tridip,

I am a bit confused. The help topics I mentioned clearly describe how to use the formula engine and contain all required code samples. Have you reviewed these topics? Did you encounter any issues when implementing this solution? If yes, please describe them in greater detail.

Tridip 4 years ago

which dll reference i need to add to work with Formula Engine ?

do i need to drop spread sheet control onto form to work with formula engine ?
without spread sheet control can i work with Formula Engine ?

tell me what dll and what namespace i need to work with ?

thanks

DevExpress Support Team 4 years ago

Hi,

You can access the FormulaEngine object using the following properties:

- SpreadsheetControl.Document.FormulaEngine.
- Workbook.FormulaEngine.

Thus, you can create either SpreadsheetControl or Workbook to use FormulaEngine. The use of this object does not require referencing any additional DLLs.

Tridip 4 years ago

i guess it is not getting clear that what i am trying to do.

I want to just parse few formula's cell address. for this purpose i am not going to load any excel into spread sheet. show me few classes where i can insert my formula and get cell address from that formula in List<String> format.

Tridip 4 years ago

I am not being able to create work book then how could i use formula engine. here is code

C#
using DevExpress.Spreadsheet;

Workbook workbook = spreadsheetControl1.CreateNewDocument();

DevExpress Support Team 4 years ago

Hi Tridip,

It is impossible to parse formulas without loading a document. If you use SpreadsheetControl, it is sufficient to load your file (SpreadsheetControl.LoadDocument) and then use SpreadsheetControl.Document.FormulaEngine as I previously described. In short, you need to iterate through the existing cells, check if a cell contains a formula (as I recall from your previous tickets, you already have the code to iterate through cells and get formulas), and parse it to get referenced ranges.

If you face any issues when implementing this solution, feel free to post your current results here. I will examine them and will help you proceed.

Tridip 4 years ago

Here i load the excel file into spread sheet control and few cell has formula. now see the code.

C#
DevExpress.Spreadsheet.IWorkbook workbook = null;
            DevExpress.Spreadsheet.Worksheet sheet = null;

            //sample formula =D2-D19
            workbook = spreadsheetControl1.Document;
            sheet = workbook.Worksheets[0];

            if (sheet[11, 39].Formula != "")
            {
                DevExpress.Spreadsheet.Formulas.ParsedExpression pe = spreadsheetControl1.Document.FormulaEngine.Parse(sheet[11, 39].Formula);
                List<Range> lstRanges = pe.GetRanges();
                if (lstRanges.Count > 0)
                {

                }
            }

My formula was =D2-D19 and now tell me how could i extract D2 and D19 from lstRanges ?

this formula is very simple but formula could be very complicated too. so in any case when formula will be big and complicated then can extract cell address from there?

one sample formula =((D2-D19)*2)/C1 i just need to extract D2,D19,C1 from here.

please tell me how could i get cell address from formula. thanks

DevExpress Support Team 4 years ago

Hi Tridip,

As I recall, we already discussed this question in your other ticket: Winform Spreadsheet control: How to get cell address. The same approach is applicable here as well.

Tridip 4 years ago

Yes solved. here is working code

C#
using DevExpress.Spreadsheet;
using System.IO;

DevExpress.Spreadsheet.IWorkbook workbook = null;
            DevExpress.Spreadsheet.Worksheet sheet = null;

            //sample formula =D2-D19
            workbook = spreadsheetControl1.Document;
            sheet = workbook.Worksheets[0];

            if (sheet[11, 39].Formula != "")
            {
                DevExpress.Spreadsheet.Formulas.ParsedExpression pe = spreadsheetControl1.Document.FormulaEngine.Parse(sheet[11, 39].Formula);
                List<Range> lstRanges = pe.GetRanges();
                if (lstRanges.Count > 0)
                {
                    foreach(Range r in lstRanges)
                    {
                      string xx=  r.GetReferenceA1();
                    }
                }

            }

thanks

DevExpress Support Team 4 years ago

Thank you for informing us that you resolved the issue.

Created: September 23, 2020 5:07 PM

Modified: November 10, 2020 10:33 AM

Platform: WinForms Office File API

Product: XtraSpreadsheet Spreadsheet Document API

Framework / Specific: .NET Framework

Build: 18.2.4

Operating System: Windows 10

IDE: Microsoft Visual Studio 2013

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

How to parse a cell formula and extract cell references used in this formula

Answers approved by DevExpress Support

Recently viewed tickets