Link to home
Create AccountLog in
Avatar of raheelasadkhan
raheelasadkhanFlag 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

Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Have you tried using:
 

cellObject = range.get_Formula(null);

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HarryNS
HarryNS

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of raheelasadkhan

ASKER

Thanks. Casting was required from Object to Range with the get_Range method.
I thought the question was how to read formulas, not write them? ;)