As you all know.. my site went down last saturday... and so came keith digging his code archive, backups and cd's for atleast a strain of his lost and futile website... which he wasn't able to find.

But amidst the search is his encounter again with his old(yeah this is pretty old!) Excel Data Helper. I think the class was written about 2 1/2 years ago.

This Excel Data Helper is a class that contains a collection of methods that you can use to consume data inside a Microsoft Excel File.

[code language="C#"]

/* Excel Data Helper 0.11b is a set of functions that can help you in consuming
 * data in a excel spreadsheet.
 *
 * You can obtain the latest version of this library from http://www.keithrull.com
 *
 * Copyright (C) 2006  Keith Rull [ keith.rull[at]gmail.com ]
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.

 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the
 * Free Software Foundation, Inc.,
 * 51 Franklin Street, Fifth Floor,
 * Boston, MA  02110-1301, USA.
* */

using System;
using System.Data;
using System.Data.OleDb;

namespace KeithRull.Utilities
{
    /// <summary>
    /// ExcelDataHelper is a set of functions that can help you in consuming data in a excel spreadsheet
    /// </summary>
    public sealed class ExcelDataHelper
    {
        private ExcelDataHelper() { }

        static string oledbProviderString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

        /// <summary>
        /// a method that retrieves the worksheet names from the specified excel worksheet
        /// </summary>
        /// <param name="excelFilePath">excel document path</param>
        /// <returns>an array of worksheet names</returns>
        public static string[] WorkSheetNames(string excelFilePath)
        {
            //an array that would hold the extracted worksheet names
            string[] workSheetNames;
            //create a connection to the excel worksheet
            using (OleDbConnection oledbConnection = new OleDbConnection(string.Format(oledbProviderString, excelFilePath)))
            {
                //open the connection
                oledbConnection.Open();
                // Get all of the Table names from the Excel workbook
                DataTable dataTable = oledbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //specify the dimension of the array
                workSheetNames = new string[dataTable.Rows.Count];
                //Add the Table name to the string array.
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    //append to the array the worksheet names
                    workSheetNames[ i ] = (string)dataTable.Rows[ i ]["TABLE_NAME"];
                }
                //close the connection
                oledbConnection.Close();
            }
            //return the array
            return workSheetNames;
        }

        /// <summary>
        /// Get columns in the specified excel document
        /// </summary>
        /// <param name="excelFilePath">excel document path</param>
        /// <returns>an array of colum names</returns>
        public static string[] ColumnNamesArray(string excelFilePath)
        {
            //Get the columnames from the Excel spreadsheet
            DataColumnCollection columCollection = ColumnNamesCollection(excelFilePath);

            //an array that would hold the column names
            string[] columNames = new string[columCollection.Count];
            //iterate thru all the columns in our DataColumnCollection
            foreach (DataColumn column in columCollection)
            {
                //specify the index of the string in our array and the value of the column name
                columNames[column.Ordinal] = column.ColumnName;
            }

            //return the column names
            return columNames;
        }

        /// <summary>
        /// Get columns in the specified excel document
        /// </summary>
        /// <param name="excelFilePath">excel document path</param>
        /// <returns>a DataColumCollection colum names</returns>
        public static DataColumnCollection ColumnNamesCollection(string excelFilePath)
        {  
            return ColumnNamesCollection(excelFilePath,"Sheet1$");
        }

        /// <summary>
        /// Get columns in the specified excel document and spreadsheet
        /// </summary>
        /// <param name="excelFilePath">excel document path</param>
        /// <param name="sheetName">sheetname to read</param>
        /// <returns>a DataColumCollection colum names</returns>
        public static DataColumnCollection ColumnNamesCollection(string excelFilePath, string sheetName)
        {
            DataColumnCollection columNames;
            //create a new connection to the specified excel path
            using (OleDbConnection oledbConnection = new OleDbConnection(string.Format(oledbProviderString, excelFilePath)))
            {
                string sqlStatement = "Select top 1 * from [" + sheetName + "]";
                //open the connection
                oledbConnection.Open();

                //Create an OleDbDataAdapter for our connection
                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlStatement, oledbConnection);

                //Create a DataTable for our data
                DataTable table = new DataTable();

                adapter.Fill(table);

                columNames = table.Columns;

                //close the connection
                oledbConnection.Close();
            }
            //return the column names
            return columNames;
        }

        /// <summary>
        /// Converts the Excel Spreadsheet to Dataset
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <returns>a dataset containing all the worksheets inside the spreadsheet</returns>
        public static DataSet ExcelToDataSet(string excelFilePath)
        {
            //get an array of the worksheetnames in our spreadsheet
            string[] workSheets = ExcelDataHelper.WorkSheetNames(excelFilePath);
            //create a new dataset
            DataSet dataSet = new DataSet();
            //iterate thru all the worksheetnames is our array
            foreach (string workSheet in workSheets)
            {
                //create a new DataTable specifying the DataTable name
                DataTable dataTable = new DataTable(workSheet);
                //Get the contents of our worksheet and put it into a DataTable
                dataTable = ExcelToDataTable(excelFilePath, workSheet);
                //Add our DataTable to our DataSet
                dataSet.Tables.Add(dataTable);
            }
            // return our DataSet
            return dataSet;
        }

        /// <summary>
        /// Converts the Excel Spreadsheet into a DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns>This returns the first sheet into a datatable</returns>
        public static DataTable ExcelToDataTable(string filePath)
        {
            //return our DataTable
            return ExcelToDataTable(filePath, 0);
        }

        /// <summary>
        /// Converts the Excel Spreadsheet into a DataTable
        /// </summary>
        /// <param name="filePath">excel file path</param>
        /// <param name="index">the index of the worksheet to return</param>
        /// <returns>a datatable containing the values form the specied index in the spreadsheet</returns>
        public static DataTable ExcelToDataTable(string filePath, int index)
        {
            //return our DataTable
            return ExcelToDataSet(filePath).Tables[index];
        }

        /// <summary>
        /// Converts the Excel spreadsheet into a DataTable
        /// </summary>
        /// <param name="filePath">excel file path</param>
        /// <param name="workSheet">worksheet name</param>
        /// <returns>a datatable containing the values form the specified worksheetname in the spreadsheet</returns>
        public static DataTable ExcelToDataTable(string filePath, string workSheet)
        {
            //Create a new table named after the worksheet
            DataTable dataTable = new DataTable(workSheet);
            //Create a new connection to out Excel spreadsheet
            using (OleDbConnection oledbConnection = new OleDbConnection(string.Format(oledbProviderString, filePath)))
            {
                //the SQL stament to use in fetching the records from the excel spreadsheet
                string sqlStatement = @"SELECT * FROM [" + workSheet + "]";
                //open our connection to the spreadsheet
                oledbConnection.Open();
                try
                {
                    //create an OledbAdapter that we will use to to access the data.
                    OleDbDataAdapter adapter = new OleDbDataAdapter(sqlStatement, oledbConnection);
                    //fill the datable with the values
                    adapter.Fill(dataTable);
                }
                catch { }
                //close the oledb connection
                oledbConnection.Close();
            }
            //return our DataTable
            return dataTable;
        }
    }
}

[/code]

Here's the VB.NET version of the code. The VB.NET version doesnt have any code comments included in it since this class was part of a collection of utilities that i started writing in C#.  I converted the code using this tool from developerfusion so there might be errors on the VB.NET version since i havent tried it yet. I'll fully update it as soon as i get to find the time after i have restored my website.

[code language="VB.NET"]

Imports System
Imports System.Data
Imports System.Data.OleDb
Namespace KeithRull.Utilities

 Public NotInheritable Class ExcelDataHelper

   Private Sub New()
   End Sub
   Shared oledbProviderString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

   Public Shared Function WorkSheetNames(ByVal excelFilePath As String) As String()
     Dim workSheetNames As String()
     ' Using
     Dim oledbConnection As OleDbConnection = New OleDbConnection(String.Format(oledbProviderString, excelFilePath))
     Try
       oledbConnection.Open
       Dim dataTable As DataTable = oledbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
       workSheetNames = New String(dataTable.Rows.Count) {}
       Dim i As Integer = 0
       While i < dataTable.Rows.Count
         workSheetNames( i ) = CType(dataTable.Rows( i )("TABLE_NAME"), String)
         System.Math.Min(System.Threading.Interlocked.Increment(i),i-1)
       End While
       oledbConnection.Close
     Finally
       CType(oledbConnection, IDisposable).Dispose()
     End Try
     Return workSheetNames
   End Function

   Public Shared Function ColumnNamesArray(ByVal excelFilePath As String) As String()
     Dim columCollection As DataColumnCollection = ColumnNamesCollection(excelFilePath)
     Dim columNames(columCollection.Count) As String
     For Each column As DataColumn In columCollection
       columNames(column.Ordinal) = column.ColumnName
     Next
     Return columNames
   End Function

   Public Shared Function ColumnNamesCollection(ByVal excelFilePath As String) As DataColumnCollection
     Return ColumnNamesCollection(excelFilePath, "Sheet1$")
   End Function

   Public Shared Function ColumnNamesCollection(ByVal excelFilePath As String, ByVal sheetName As String) As DataColumnCollection
     Dim columNames As DataColumnCollection
     ' Using
     Dim oledbConnection As OleDbConnection = New OleDbConnection(String.Format(oledbProviderString, excelFilePath))
     Try
       Dim sqlStatement As String = "Select top 1 * from [" + sheetName + "]"
       oledbConnection.Open
       Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(sqlStatement, oledbConnection)
       Dim table As DataTable = New DataTable
       adapter.Fill(table)
       columNames = table.Columns
       oledbConnection.Close
     Finally
       CType(oledbConnection, IDisposable).Dispose()
     End Try
     Return columNames
   End Function

   Public Shared Function ExcelToDataSet(ByVal excelFilePath As String) As DataSet
     Dim workSheets As String() = ExcelDataHelper.WorkSheetNames(excelFilePath)
     Dim dataSet As DataSet = New DataSet
     For Each workSheet As String In workSheets
       Dim dataTable As DataTable = New DataTable(workSheet)
       dataTable = ExcelToDataTable(excelFilePath, workSheet)
       dataSet.Tables.Add(dataTable)
     Next
     Return dataSet
   End Function

   Public Shared Function ExcelToDataTable(ByVal filePath As String) As DataTable
     Return ExcelToDataTable(filePath, 0)
   End Function

   Public Shared Function ExcelToDataTable(ByVal filePath As String, ByVal index As Integer) As DataTable
     Return ExcelToDataSet(filePath).Tables(index)
   End Function

   Public Shared Function ExcelToDataTable(ByVal filePath As String, ByVal workSheet As String) As DataTable
     Dim dataTable As DataTable = New DataTable(workSheet)
     ' Using
     Dim oledbConnection As OleDbConnection = New OleDbConnection(String.Format(oledbProviderString, filePath))
     Try
       Dim sqlStatement As String = "SELECT * FROM [" + workSheet + "]"
       oledbConnection.Open
       Try
         Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(sqlStatement, oledbConnection)
         adapter.Fill(dataTable)
       Catch
       End Try
       oledbConnection.Close
     Finally
       CType(oledbConnection, IDisposable).Dispose()
     End Try
     Return dataTable
   End Function
 End Class
End Namespace

[/code]

Also, Attach to this post is the sourcecode.

Oh well time to go back to the archive digging reverie... Have fun guys!