Monday, July 12, 2010

Reading Excel Column into a List - C#

Note: I'm sure there is a way to retrieve the column and sheet name from an Excel sheet...I just don't know how so for this program they must be specified as a parameter. The method to retrieve the name of an excel sheet can be seen here.

//Given a column name in an Excel sheet, it returns each cell of the column in an List of strings
//xlsDir = Directory of the excel sheet
//col = name of the column in sheet
//sheetName = the name of the excel sheet
//@return: Each element of the List is a cell in the column
public List excelColtoArr(string xlsDir, string col, string sheetName)
{
            
    string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; data source=" + xlsDir + "; Extended Properties='Excel 8.0;HDR=YES;'";
    OleDbConnection con = new OleDbConnection(connectionString);
    // Select using a Worksheet name
    string selectString = "SELECT * FROM [" + sheetName + "$]";

            
    OleDbCommand cmd = new OleDbCommand(selectString, con);

     List arr = new List();
     try
     {
         con.Open();
         OleDbDataReader theData = cmd.ExecuteReader();
         while (theData.Read())
         {
            arr.Add(replaceIllegalChars(theData[col].ToString().Trim()));
         }


     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.Message);
     }
     finally
     {
         con.Dispose();
     }

     return arr;
}

0 comments:

Post a Comment