troubleshooting Question

How to read Excel Formulas from C#

Avatar of raheelasadkhan
raheelasadkhanFlag for Pakistan asked on
.NET ProgrammingMicrosoft ExcelC#
4 Comments1 Solution4924 ViewsLast Modified:
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);
}
ASKER CERTIFIED SOLUTION
HarryNS

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros