Naveen's Weblog

Bridge to future

Get All formula from excel cell

Posted by codingsense on March 1, 2009


Hi Friends,

In this topic we shall see how to get the formula from an excel cell. This program lists only the field in which user has entered a formula in excel sheet.
Here let us explore a good feature of exed known as “UsedRange”. Used Range gives only the cells those have been used, this helps us to avoid searcing in whole excel sheet. We can search only the cells in the UsedRange and fetch if the user has given any formula to cells or not, if he has given a formula lets fetch the formula and display the row, col and formula.

Download Project : 9KB

To access the excel file we shall use Late binding method using System.Reflection which helps to invoke any version of excel instance that is installed on my machine.

//Get Instance of excel on the machine
                objClassType = Type.GetTypeFromProgID("Excel.Application");
//Create new instance of Excel
                objExcelApp = Activator.CreateInstance(objClassType);

To get the UsedRange we can run a macro and get the RowCount and ColCount of the UsedRange and loop within the boundary.

//Get the used Rows and Cols from excel                
object UsedRange = objSheet.GetType().InvokeMember("UsedRange",BindingFlags.GetProperty,null,objSheet,null);
object Rows = UsedRange.GetType().InvokeMember("Rows", BindingFlags.GetProperty, null, UsedRange, null);
object objRowCount = Rows.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, Rows, null);
object Cols = UsedRange.GetType().InvokeMember("Columns", BindingFlags.GetProperty, null, UsedRange, null);
object objColCount = Cols.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, Cols, null);

Next we will loop throught each cells within the UsedRange boundary and check if the Cells[i,j].Formula is starting with “=”. If yes then its a formula, Add the row, col and formula into the list and proceed to next cell.

//Get cells object                
object Cells = objSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty,
null, objSheet, null);
for (int RowIndex = 1; RowIndex <= RowCount; RowIndex++)
{
for (int ColIndex = 1; ColIndex <= ColCount; ColIndex++)
{
object objCellText = Cells.GetType().InvokeMember("Item", BindingFlags.GetProperty, 
null, Cells, new object[] { RowIndex, ColIndex });
string CellText = Convert.ToString(objCellText.GetType().InvokeMember("Formula", 
BindingFlags.GetProperty,null, objCellText, null));
if (CellText.StartsWith("="))
{
FormulaCell cell = new FormulaCell(RowIndex,ColIndex,CellText);
ListOfFormulae.Add(cell);
}
}

After searching each cell within the used range we get the collection of FormulaCells. After finishing our task, the most important is to disposed the Unmanaged object. For disposing let us use Marshal class.

//Invoke the close method of WorkBooks
objBooks.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, objBooks, null);
//Release the COM Excel application using Marshal class                
Marshal.ReleaseComObject(objExcelApp);

Now lets display it.

if (ListOfFormulae != null)
{
for (int Index = 0; Index < ListOfFormulae.Count; Index++)
{
Console.WriteLine("Row = " + ListOfFormulae[Index].Row +
", Col = " + ListOfFormulae[Index].Col +
" has the formula " + ListOfFormulae[Index].Value);
}
}

Hope you have understood the following,

  • How Late binding is done in C#.
  • How to use a macro through C#.
  • How Properties of another application can be accessed using a GetType().InvokeMember() and BindingFlags.GetProperty.
  • How Functions of another application are invoked using a GetType().InvokeMember() and BindingFlags.InvokeMethod.
  • The important one is how to dispose the unmanaged object.

If you have any doubts, please comment.

Happy learing 🙂

Advertisements

3 Responses to “Get All formula from excel cell”

  1. TC said

    Sounds useful, but how do I run it on my spreadsheet?

  2. Sherin said

    Hi
    Thanks a lot
    Much useful for getting fileterd row count

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: