Dec 16, 2009

Excel Import using c# code (dynamically select first sheet name)

I'm sure all of you who have been coding are familiar with this stuff (of importing data from excel). But how many of you have specified the requirement that your excel sheet must have first sheet named as 'Sheet1' and that it will give error if you rename it? I'm sure most of you! Here I'm putting down the code snippet using which you can import data from the first sheet of an excel file. You can rename it to say 'book1' or anything else, what the code snippet will do is that it'll fetch the first sheet's name using OLEDB and will pass on to you, so that you can use it in your code. Now the snippet:


string strOledbConnection;

strOledbConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFilePath + "; Jet OLEDB:Engine Type=5;" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strOledbConnection);
conn.Open();
DataTable dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string strsheetName = "Sheet1$";
if (dbSchema != null && dbSchema.Rows.Count > 0)
{
strsheetName = Convert.ToString(dbSchema.Rows[0]["TABLE_NAME"]);
}
conn.Close();
OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + strsheetName + "] ", strOledbConnection);
DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet);

Now you see, your old code would be 'SELECT * FROM [Sheet1$]' something, instead this will be dynamic. Make sure you put in your sheet name in the variable 'strExcelFilePath'

1 comment:

  1. But in this case Convert.ToString(dbSchema.Rows[0]["TABLE_NAME"]) return first sheet name after sorting all sheet name with in excel file.how to get first sheet name??

    ReplyDelete