Reading Excel in c#

Reading excel is the piece of cake but for me its took some trail and errors and here is the post about it. The module flow as follows,
1. Load excel file obviously
2. Use Microsoft.ACE.OLEDB to convert excel data into DataTable format
3. DataTable will make the life easier [traversal throw the data]
the code as below,

        public DataTable readExcel(String sheetName, String fileName)
        {
            string dirLocation = Directory.GetCurrentDirectory();
            String filePath = dirLocation + "\\" + fileName;
            string connString = string.Empty;
            if (filePath.EndsWith(".xlsx"))
            {
                //2007 Format
                connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"", filePath);
            }
            using (OleDbConnection con = new OleDbConnection(connString))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    //Read Sheet
                    cmd.Connection = con;
                    con.Open();
                    DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    con.Close();

                    //Read the Header Row and value
                    cmd.CommandText = "SELECT * From [" + sheetName + "$]";
                    using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                    {
                        System.Data.DataTable data = new DataTable();
                        da.SelectCommand = cmd;
                        da.Fill(data);
                        return data;
                    }
                }
            }
        }

As this method return Data Table so we can traversal the data table to get the data by row/column as below,

Excel Data like,

Super Hero Feature
Iron Man High tech suite
Superman Flying
Thor Hammer
Spiderman Web

Get Row Data:

 foreach (DataRow rowData in data.Rows)
    {
    	console.WriteLine("Row --> "+rowData[0]+", "+rowData[1]);
    }

this will return Iron Man, High tech suite
Get Column Data and Header:

 foreach (DataRow rowData in data.Rows)
    {
    	console.WriteLine("Row: "+rowData);
    	foreach (DataColumn column in data.Columns)
        {
        	string columnValue = rowData[column.ColumnName].ToString();
        	Console.WriteLine(column.ColumnName + ", " + columnValue);
        }
    }

this will return Super Hero, Iron Man

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s