Parsing an excel file and reading a cell

Question

I got an excel file. I have uploaded the screenshot. I need to write a .NET application (console application) to parse the excel file. You can see a cell titled "Function Name". My .NET app should find that particular cell and read the contents in that column such as Template, InstanceFromTemplate, Task and so on. If it reads Task, it should call CreateTask function like Task(); If it reads InstanceFromTemplate, it should call InstanceFromTemplate function like TaskInstanceFromTemplate();

I have written code for those functions. I only need help on how to parse the excel sheet, go to that particular column, read the cells containing function names under that column and call that appropriate function. Any help would be appreciated.

Algorithm is like

1. open excel file
2. search for a header cell titled Function Name
3. Read the first cell under the header cell
4. If the first cell reads , call function (code for GetAllTemplate function is already there) Only need to call it afte finding it
5. If the second cell , call function.
6. Last cell under the header cell Function Name is reached. So, exit

Answer 1

I strongly suggest using a library like Excel Data Reader and work with the data in managed code. The CodePlex site has a good example of doing what you need.

Answer 2

There are a handful of tutorials here that should get you started:

http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

Answer 3

Using Excel interop you can do it this way. I supposed that the function names are pointing to functions that are defined in your excel workbook, otherwise you have to change that part to use reflection (but in this case you'd need a receiver for the methods, and I don't see one in the question).

        object hmissing = System.Reflection.Missing.Value; 

        Xls.Application App = new Xls.ApplicationClass();
        App.Visible = true;
        Xls.Workbook wb = App.Workbooks.Open(@"c:\tmp\cartel1.xls", hmissing, hmissing, hmissing, hmissing, hmissing, hmissing,
            hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing);
        Xls.Worksheet ws = (Xls.Worksheet)wb.ActiveSheet;
        Xls.Range rng = ws.UsedRange;
        Xls.Range hdr = rng.Find("Function Name", hmissing, hmissing, hmissing, hmissing, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, hmissing, hmissing, hmissing);
        string hdrAdd = hdr.get_Address(hmissing, hmissing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, hmissing, hmissing);
        string[] pcs = hdrAdd.Split('$');
        string col = pcs[1];
        int row;
        int.TryParse(pcs[2], out row);
        string methName;
        row++;
        while ((methName = App.get_Range(col + row.ToString(), hmissing).get_Value(hmissing) as String) != null)
        {
            App.Run(methName.Split('.')[1], hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing,
                hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing,
                hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing, hmissing,
                hmissing, hmissing, hmissing);
            row++;
        }

Answer 4

Based on the Description on what you are trying to do you should really use LINQ to Ecxel plugin I think it simple way to solve your problem

http://code.google.com/p/linqtoexcel/

Answer 5

Extra notes for future reference. Just wanted to point out that, working with interops are messy. Your best bet would be to use libraries.