//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 ListexcelColtoArr(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; }
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.
Labels:
C#,
C# Sample Code
0 comments:
Post a Comment