Many a times we are in the need of reading the MS Excel file and populate them into the GridView or validating the data of the excel sheet. In this article we are going to see how to populate the MS Excel data into the GridView and loop through them.
To demonstrate how to read the MS Excel data and populate into GridView or loop through the records, I have created a sample MS Excel file and here is how it looks like.
In this excel we have two sheets, Sheet1 and Sheet2. I have taken few columns and data as displayed above.
Now, the next step is to have a connection string to connect to the MS Excel file and here is the appSettings into my web.config file.
<appSettings>
<!-- for .xls -->
<add key="Excel2003OleDBConnection" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 8.0;HDR=YES;IMEX=1""/>
<!-- for .xlsx -->
<add key="Excel2007OleDBConnection" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1""/>
</appSettings>
Here, the first appSettings is for the file that is .xls and the second appSetting is for .xslx (latest version of MS Excel). If you notice this carefully, we have a placeholder for the
Data Source ie {0} where we will fill the name of the file at the time of using this connection string.
Next, I have kept a GridView where I am going to populate the data retrieved from the Excel sheet.
<div>
<asp:GridView ID="GridView1" runat="server" EnableViewState="false" />
</div>
Next is to write the code that connect to the MS Excel file and bring the data and here it goes.
Namespace to be used
using System.Data;
using System.Configuration;
using System.Data.OleDb;
Below is the actual code that does the magic.
protected void Page_Load(object sender, EventArgs e)
{
string file = Server.MapPath("~/App_Data/SampleData.xls");
string connStr = string.Format(ConfigurationManager.AppSettings["Excel2003OleDBConnection"], file);
DataTable table = new DataTable();
using (OleDbConnection conn = new OleDbConnection(connStr))
{
string sheet = @"SELECT * FROM [Sheet2$]"; // to avoid error write the sheet name in square bracket
using (OleDbCommand cmd = new OleDbCommand(sheet, conn))
{
conn.Open();
using (OleDbDataAdapter ad = new OleDbDataAdapter(cmd))
{
ad.Fill(table);
}
conn.Close();
};
}
GridView1.DataSource = table;
GridView1.DataBind();
// to loop through the rows use foreach loop
foreach (DataRow row in table.Rows)
{
string firstName = row["FirstName"].ToString();
}
}
In the Page_Load event of the page, we have got the MS Excel file name to connect to into the
file variable. Then we have formatted the connection string using
string.Format to ensure that the file name is placed at the placeholder of the connection string.
Remember, that if you are using wrong appSettings, you may get error. For example, if you are trying to use second appSettings to read the .xlsx file for which the driver is not installed on your system, you will get following error.
System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not
registered on the local machine
Till MS Excel 2010 version, you should be using the first appSettings with .xls extension of the MS Excel file or you need the latest provider installed on your system.
As we are using OledDb in our connection string, so we will need to use Oledb .NET provider. Use the same type of code that you use to retrieve the data from a RDBMS like Sql Server. Instead of database table name in the SQL statement, write the MS Excel file Sheet name. Note that I have written the sheet name in square bracke [] as you may get error if you failed to do so. You should also suffix the sheet name with $ to avoid errors listed below.
If you fail to suffix $ in the Sheet name
System.Data.OleDb.OleDbException: The Microsoft Jet database engine could not
find the object 'Sheet2'. Make sure the object exists and that you spell its
name and the path name correctly.
If you fail to keep the sheet name in square bracket "[]"
System.Data.OleDb.OleDbException: Syntax error in FROM clause.
Calling the Fill method of the SqlDataAdapter object will give you records from the respective sheet into the DataTable that you can use to populate the GridView or loop through and check for any errors in the records.
Hope this article was useful, thanks for reading and do let me know your feedback.