Avatar of raheelasadkhan
raheelasadkhan
Flag for Pakistan asked on

How to read Excel Formulas from C#

I'm using Visual C# Express Edition and Office 2007 Professional. I can read the value contents of individual cells but need a way to read the formula string instead.

Example of Excel File:
A1 = "=10"
A2 = "=20"
A3 = "=SUM(A1:A2)"

When reading, the cell returns a value of 30 and rightfully so. I want to extract the actual formula string of "=SUM(A1:A2)" instead. Any ideas?
private void ReadFromExcel()
{
    Microsoft.Office.Interop.Excel.Application excelApplication = null;
    Microsoft.Office.Interop.Excel.Workbook workbook = null;
    Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
 
    excelApplication = new Microsoft.Office.Interop.Excel.Application();
    excelApplication.Visible = false;
 
    workbook = excelApplication.Workbooks.Open(@"C:\program.xls", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
    worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[2];
 
    for (int r = 1; r <= 45; r++)
    {
        this.richTextBoxProcessLog.AppendText(System.Environment.NewLine);
 
        for (int c = 1; c <= 7; c++)
        {
            this.richTextBoxProcessLog.AppendText
                (
                "   "
                + this.GetCellValue(worksheet, r, c)
                );
        }
    }
 
    excelApplication.Quit();
    excelApplication = null;
}
 
private string GetCellValue(Microsoft.Office.Interop.Excel.Worksheet worksheet, int rowIndex, int columnIndex)
{
    string cellValue = "";
    object cellObject = null;
    Microsoft.Office.Interop.Excel.Range range = null;
 
    try
    {
        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowIndex, columnIndex];
 
        cellObject = range.get_Value(null);
        cellValue = (cellObject == null ? "" : cellObject.ToString().Trim());
    }
    catch (System.Exception exception)
    {
        this.AppendLog(exception);
    }
 
    return (cellValue);
}

Open in new window

.NET ProgrammingMicrosoft ExcelC#

Avatar of undefined
Last Comment
Rory Archibald

8/22/2022 - Mon
Rory Archibald

Have you tried using:
 

cellObject = range.get_Formula(null);

Open in new window

ASKER CERTIFIED SOLUTION
HarryNS

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
raheelasadkhan

ASKER
Thanks. Casting was required from Object to Range with the get_Range method.
Rory Archibald

I thought the question was how to read formulas, not write them? ;)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes