raheelasadkhan
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?
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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? ;)
Open in new window