C# Read Excel using late binding

Question

Hi I have not used late binding before but it would seem to be the solution, if only I could find a concise example!

Or may be it's not the solution but I'm sure you guys will know!

I need to fill a dropdown combbox list from a column in excel reading down to the first blank cell. the solution needs to work with excel 2003 and above some PCs never have had 2003 install only office 2010 other have been upgraded from 2003 and some are still on 2003!

I need a solution that works on all of the above.

So I'm looking into late binding is this the correct way to go? would Linq help!?

Its a clasic windows Form app using .Net 4.

I thought I would write a method that takes the file name and path and returns a list which I would then assign to the combobox.

But being new I'm not getting pass go!

Any help/examples PLEASE

score 6 · Accepted Answer · edited Jun 20 '20 at 09:12

It sounds like you're looking at using COM interop/automation with the Excel application installed on client machines.

If your sole requirement is to extract data from an Excel file, you'll be better off using a library that can simply read data out of the file itself, rather than launching the Excel process in the background.

It is faster, cleaner, and more testable.

I've used NPOI for .xls files (and there are certainly others), and there are LOTS of options for .xlsx files. This SO question is about creating a file, but any of the suggested libraries can of course read files as well.

The only time I'd use COM automation is to interact with a running instance of Excel.

Edit (in response to comments)

Here is a sample of getting the values of column B as strings:

using System;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var stream = new FileStream(@"c:\my_workbook.xls", FileMode.Open);
            var workbook = new HSSFWorkbook(stream);
            stream.Close();

            var sheet = workbook.GetSheet("My Sheet Name");
            var row_enumerator = sheet.GetRowEnumerator();

            while (row_enumerator.MoveNext())
            {
                var row = (Row)row_enumerator.Current;
                var cell = row.GetCell(1); // in Excel, indexes are 1-based; in NPOI the indexes are 0-based
                Console.WriteLine(cell.StringCellValue);
            }

            Console.ReadKey();
        }
    }
}

Hi Jay Thanks for your advice. I have downloaded NPOI and have a couple of questions if I may! The only example seem to be not in English, could you show me what I need to do to read a column? Also do I need to register the DLL on the target PCs or just ship it in the same Directory as the exe? Many thanks for your help — RTFS, Dec 17 '10 at 22:29
getting there slowly! try { HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(excelFile, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } Sheet sheet = hssfworkbook.GetSheet("LCC"); } catch (Exception ex) { MessageBox.Show(ex.Message); } — RTFS, Dec 17 '10 at 23:01
Thank you very much for the code Jay. I'm trying to go down the column only, thus far the only way I can see to do this is in a loop which breaks with an exception when it hits an empty cell! There appears to be no sheet.GetRowEnumerator equivalent for the columns!? — RTFS, Dec 18 '10 at 11:31
Now I see it! sorry for the above, it does exactly what I want THANK YOU JAY. — RTFS, Dec 18 '10 at 11:40

C# Read Excel using late binding

1 Answers1

Edit (in response to comments)