How to read data of an Excel file using C#?

OK,

One of the more difficult concepts to grasp about Excel VSTO programming is that you don’t refer to cells like an array, Worksheet[0][0] won’t give you cell A1, it will error out on you. Even when you type into A1 when Excel is open, you are actually entering data into Range A1. Therefore you refer to cells as Named Ranges. Here’s an example:

Excel.Worksheet sheet = workbook.Sheets["Sheet1"] as Excel.Worksheet; 
Excel.Range range = sheet.get_Range("A1", Missing.Value)

You can now literally type:

range.Text // this will give you the text the user sees
range.Value2 // this will give you the actual value stored by Excel (without rounding)

If you want to do something like this:

Excel.Range range = sheet.get_Range("A1:A5", Missing.Value)

if (range1 != null)
     foreach (Excel.Range r in range1)
     {
         string user = r.Text
         string value = r.Value2

     }

There might be a better way, but this has worked for me.

The reason you need to use Value2 and not Value is because the Value property is a parametrized and C# doesn’t support them yet.

As for the cleanup code, i will post that when i get to work tomorrow, i don’t have the code with me, but it’s very boilerplate. You just close and release the objects in the reverse order you created them. You can’t use a Using() block because the Excel.Application or Excel.Workbook doesn’t implement IDisposable, and if you don’t clean-up, you will be left with a hanging Excel objects in memory.

Note:

If you don’t set the Visibility property Excel doesn’t display, which can be disconcerting to your users, but if you want to just rip the data out, that is probably good enough
You could OleDb, that will work too.

I hope that gets you started, let me know if you need further clarification. I’ll post a complete

here is a complete sample:

using System;
using System.IO;
using System.Reflection;
using NUnit.Framework;
using ExcelTools = Ms.Office;
using Excel = Microsoft.Office.Interop.Excel;

namespace Tests
{
    [TestFixture]
    public class ExcelSingle
    {
        [Test]
        public void ProcessWorkbook()
        {
            string file = @"C:\Users\Chris\Desktop\TestSheet.xls";
            Console.WriteLine(file);

            Excel.Application excel = null;
            Excel.Workbook wkb = null;

            try
            {
                excel = new Excel.Application();

                wkb = ExcelTools.OfficeUtil.OpenBook(excel, file);

                Excel.Worksheet sheet = wkb.Sheets["Data"] as Excel.Worksheet;

                Excel.Range range = null;

                if (sheet != null)
                    range = sheet.get_Range("A1", Missing.Value);

                string A1 = String.Empty;

                if( range != null )
                    A1 = range.Text.ToString();

                Console.WriteLine("A1 value: {0}", A1);

            }
            catch(Exception ex)
            {
                //if you need to handle stuff
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (wkb != null)
                    ExcelTools.OfficeUtil.ReleaseRCM(wkb);

                if (excel != null)
                    ExcelTools.OfficeUtil.ReleaseRCM(excel);
            }
        }
    }
}

I’ll post the functions from ExcelTools tomorrow, I don’t have that code with me either.

Edit:
As promised, here are the Functions from ExcelTools you might need.

public static Excel.Workbook OpenBook(Excel.Application excelInstance, string fileName, bool readOnly, bool editable,
        bool updateLinks) {
        Excel.Workbook book = excelInstance.Workbooks.Open(
            fileName, updateLinks, readOnly,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing);
        return book;
    }

public static void ReleaseRCM(object o) {
        try {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
        } catch {
        } finally {
            o = null;
        }
    }

To be frank, this stuff is much easier if you use VB.NET. It’s in C# because I didn’t write it. VB.NET does option parameters well, C# does not, hence the Type.Missing. Once you typed Type.Missing twice in a row, you run screaming from the room!

As for you question, you can try to following:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.find(VS.80).aspx

I will post an example when I get back from my meeting… cheers

Edit: Here is an example

range = sheet.Cells.Find("Value to Find",
                                                 Type.Missing,
                                                 Type.Missing,
                                                 Type.Missing,
                                                 Type.Missing,
                                                 Excel.XlSearchDirection.xlNext,
                                                 Type.Missing,
                                                 Type.Missing, Type.Missing);

range.Text; //give you the value found

Here is another example inspired by this site:

 range = sheet.Cells.Find("Value to find", Type.Missing, Type.Missing,Excel.XlLookAt.xlWhole,Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlNext,false, false, Type.Missing);

It helps to understand the parameters.

P.S. I’m one of those weird people who enjoys learning COM automation. All this code steamed from a tool I wrote for work which required me to process over 1000+ spreadsheets from the lab each Monday.

More Related Contents:

Leave a Comment Cancel reply