technology, computer, code-1283624.jpg

Read Data From Excel File Using C# ASP.NET

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

In this post, we learn how to read an excel file. We will make a web application using Asp.net C#.

There is different ways using which we can read the excel sheet data in our program. Here I am going to discuss 2 ways to read the data from excel sheet.

  1. Read the Data From excel using OLEDB
  2. Read the Data From excel Without OLEDB(using some third party Library)

Read the data from excel using OLEDB

  1. Open Visual Studio. Select New Project, then select Asp.Net Web Application.
select a project
Create a asp.net project

2. Name it as you want. My application’s name is ReadDataApp.

Name your project
Enter name for your project

3. Select Empty Project

empty pproject
Select an empty project#avacloudadda

4. I will create a simple UI that contains file upload and a Submit button, of course, a label for displaying messages.

 <form id="form1" runat="server">
        <div>
            <asp:Label ID="litMsgDanger" runat="server" Text="Label"></asp:Label></br>
            <asp:FileUpload ID="flUpload" runat="server" />
            <asp:Button ID="btnSubmit" runat="server" Text="Upload" OnClick="btnSubmit_Click" /><br /><br/>
            <asp:GridView ID="grdData" runat="server"></asp:GridView>
        </div>
    </form>

After executing above code in visual studio below UI will render in the browser

simple excel upload UI
Simple Excel Upload UI
string ConStr = "";
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            if (flUpload.HasFile)
            {
                uploadDetails();
            }
            else
            {
                
                litMsgDanger.Text = "Please uplaod the excel file!!";
            }
        }

The above piece of code contains, a simple validation for file upload control which verify that any file present or not. If not present then else block of the code will be executed. Also, create a function named uploadDetails() .

            DataTable dt = new DataTable();
            string ext = Path.GetExtension(flUpload.FileName).ToLower();
            string fileName = flUpload.FileName;
            //getting the path of the file   
            string path = Server.MapPath("~/file");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            else
            {
                DirectoryInfo di = new DirectoryInfo(path);
                foreach (FileInfo item in di.GetFiles())
                {
                    item.Delete();
                }
            }
            flUpload.SaveAs(Path.Combine(path, fileName));       

Created a datatable for storing the data extracted from excel and display in grid view.
Getting the extension for the uploaded file
Mapping the current server Directory.
Validating the directory(File) exits or not. If not then It will create a folder.
If a directory exists then, It will delete all the files inside the folder.
Finally, Save the file on the provided path.

   if (ext.Trim() == ".xls")
            {
                //connection string for that file which extantion is .xls  
                ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.Combine(path, fileName) + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\" providerName=\"System.Data.OleDb\"";

            }
            else if (ext.Trim() == ".xlsx")
            {
                //connection string for that file which extantion is .xlsx  
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(path, fileName) + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            else
            {
                litMsgDanger.Text = "Invalid file format";            

            }

            //making query  
            string query = "SELECT * FROM [Sheet1$]";
            //Providing connection  
            OleDbConnection conn = new OleDbConnection(ConStr);
            //checking that connection state is closed or not if closed the   
            //open the connection  
            try
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                conn.Open();
                //create command object  
                OleDbCommand cmd = new OleDbCommand(query, conn);
                // create a data adapter and get the data into dataadapter  
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(dt);
                grdData.DataSource = dt;
                grdData.DataBind();

            }
            catch (Exception ex)
            {               
                litMsgDanger.Text = ex.Message;               
            }
            finally
            {
                 conn.Close();
            }

Validate the extension of the excel file, based on the extension, Program execute the conditions and create the OLEDB connection string.
We simply write a query to read the data from excel sheet and stored in the datatable and display that data into the grid view.
Complete code is given below.

protected void uploadDetails()
        {
            DataTable dt = new DataTable();
            string ext = Path.GetExtension(flUpload.FileName).ToLower();
            string fileName = flUpload.FileName;
            //getting the path of the file   
            string path = Server.MapPath("~/file");

            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            else
            {
                DirectoryInfo di = new DirectoryInfo(path);
                foreach (FileInfo item in di.GetFiles())
                {
                    item.Delete();
                }
            }
            flUpload.SaveAs(Path.Combine(path, fileName));

           
            if (ext.Trim() == ".xls")
            {
                //connection string for that file which extantion is .xls  
                ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.Combine(path, fileName) + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\" providerName=\"System.Data.OleDb\"";

            }
            else if (ext.Trim() == ".xlsx")
            {
                //connection string for that file which extantion is .xlsx  
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(path, fileName) + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            else
            {
                litMsgDanger.Text = "Invalid file format";            

            }

            //making query  
            string query = "SELECT * FROM [Sheet1$]";
            //Providing connection  
            OleDbConnection conn = new OleDbConnection(ConStr);
            //checking that connection state is closed or not if closed the   
            //open the connection  
            try
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                conn.Open();
                //create command object  
                OleDbCommand cmd = new OleDbCommand(query, conn);
                // create a data adapter and get the data into dataadapter  
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(dt);
                grdData.DataSource = dt;
                grdData.DataBind();

            }
            catch (Exception ex)
            {               
                litMsgDanger.Text = ex.Message;               
            }
            finally
            {
                 conn.Close();
            }
        }
Gridview output
Gridview output

I hope you like the post. If you have any doubt. Please comment or post in our community forum.

vivek sahu

More to explorer

JBOD (just a bunch of disks)

JBOD stands for “just a bunch of disks” is a collection of hard disks with no collective properties, all disks being independently

Leave a Comment