Office Interop Excel

 Collapse

using Microsoft.Office.Interop.Excel;

I've created a console application and just kept all the code within the main method. I've done this to make it a bit easier to follow. Next, we need to setup the objects that we'll be working with.

 Collapse

ApplicationClass app = new ApplicationClass(); // the Excel application.

    // the reference to the workbook,

    // which is the xls document to read from.

    Workbook book = null;
    // the reference to the worksheet,

    // we'll assume the first sheet in the book.

    Worksheet sheet = null;
    Range range = null;
    // the range object is used to hold the data

    // we'll be reading from and to find the range of data.

The following options will help speed up the Excel application. They can also be set to true which will help us see what's going on with the document while debugging.

 Collapse

app.Visible = false;
    app.ScreenUpdating = false;
    app.DisplayAlerts = false;

Now that the application setup is out of the way, we can open an XLS document and get the first worksheet in the workbook. Excel seems to prefer a full path to the document. As such, I get the current executing directory and move up two directories to the XLS document.

 Collapse

string execPath = 
         Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);

    book = app.Workbooks.Open(execPath + @"\..\..\Book1.xls", 
           Missing.Value, Missing.Value, Missing.Value, 
           Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
           Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
           Missing.Value, Missing.Value, Missing.Value);
    sheet = (Worksheet)book.Worksheets[1];

Finding the range of data

Next, we need to get an initial range to work with. We'll start with A1, you can start with the row your data starts on, to exclude the header information.

 Collapse

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

Now that we have a range to work with, we can use the get_End method of the Range object and theXlDirection enumeration to specify which direction to find the end. We'll go to the right first and down second. The get_End stops at the first empty cell. And works on the first row or column in the range. So based on our initial range selection of A1, it will look for the first empty cell in row 1 moving to the right from column A.

 Collapse

range = range.get_End(XlDirection.xlToRight);

In this example, it will find cell F1 is empty, and return E1 as the end range. We'll use this range, meaning cell E1, to find the end of the data moving down.

 Collapse

range = range.get_End(XlDirection.xlDown);

This will get us to cell E20. Using this method, we get the bottom right cell with data. Now we can obtain the full range of data using the starting cell, A1, and the ending cell, E20. In order to get the address of the cell from theRange, we use the get_Address method of the Range object. The XlReferenceStyle specifies the format of the address returned. We want xlA1 because the get_Range method expects that format. The following returns a string containing E20:

 Collapse

string downAddress = range.get_Address(
        false, false, XlReferenceStyle.xlA1, 
        Type.Missing, Type.Missing);

We'll use the get_Range method to get a range from A1 to E20.

 Collapse

range = sheet.get_Range("A1", downAddress);

We now have a reference to the data.

Reading the data

Range objects will return their data in a two dimensional array of objects with the Value2 property. Dimension one represents the rows, while dimension two represents the columns. This is much faster than reading the data cell by cell.

 Collapse

object[,] values = (object[,])range.Value2;

    Console.WriteLine("Row Count: " + values.GetLength(0).ToString());
    Console.WriteLine("Col Count: " + values.GetLength(1).ToString());

With the values object array, all we need to do is loop through to get the data. We'll start by writing out the column numbers.

 Collapse

Console.Write("");
    for (int j = 1; j <= values.GetLength(1); j++) {
        Console.Write("{0}", j);
    }

What we really want is the data, so we'll need to loop through the rows, then the columns to access each value invalues.

 Collapse

Console.WriteLine();
    for (int i = 1; i <= values.GetLength(0); i++) {
        Console.Write("{0}", i);
        for (int j = 1; j <= values.GetLength(1); j++) {
            Console.Write("{0}", values[i, j]);
        }
        Console.WriteLine();
    }

Clean up

In order for the GC to collect the objects, which can have a large memory footprint, we want to set the references tonull, close the workbook, and quit the Excel application.

 Collapse

range = null;
    sheet = null;
    if (book != null)
        book.Close(false, Missing.Value, Missing.Value);
    book = null;
    if (app != null)
        app.Quit();
    app = null;

How to select column from Excel sheet

OleDbConnection connection = null;
        String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @"c:\Book.xls" + ";" + "Extended Properties=Excel 8.0;";
        DataTable dtCH = new DataTable();
        connection = new OleDbConnection(sConnectionString);
        OleDbCommand command = new OleDbCommand("Select column1,column2 FROM [Sheet1$]", connection);
        connection.Open();
        dtCH = new DataTable("Table");
        OleDbDataReader db_reader = command.ExecuteReader();

        dtCH.Load(db_reader);
        DataTable table = dtCH;

        connection.Close();

Reading a Excel Sheet using OLEDB Connection Object

/// 

        /// This function is used to read Excel Sheet using OLEDB connection
        /// 
        /// file name of the excel file
        public void ReadExcel(string filename)
        {
            try
            {
                //Create a OLEDB connection for Excel file
                string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                            "Data Source=" + filename + ";" +
                                            "Extended Properties=Excel 8.0;";
                OleDbConnection objConn = new OleDbConnection(connectionString);
                objConn.Open();
                // Creating a command object to read the values from Excel file
                OleDbCommand ObjCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
                // Creating a Read object
                OleDbDataReader objReader = ObjCommand.ExecuteReader();
                // Looping through the values and displaying
                while (objReader.Read())
                {
                    MessageBox.Show(objReader[0].ToString() + " " + objReader[1].ToString());
                }
                //Disposing the objects
                objReader.Dispose();
                ObjCommand.Dispose();
                objConn.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Reading Numbers as Strings from .csv file

When Excel opens a csv file it processes it automatically, and displays numbers using a general format.

If you want to bring these fields in with leading zeros, you could change the file to a txt file (by changing the suffix), and then open it with Excel. This will bring up the text file wizard, which has options to treat the field as text. This will retain leading zeroes.