Reading excel data

Read Data From Excel File Using C# ASP.NET Without OLEDB

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

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

There are different ways using which we can read the excel sheet data in our program. Here I am going to discuss how to read the data from excel sheets without using OLEDB.

Read the data from excel Without using OLEDB

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

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

Name your project
Enter a 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 the above code in the visual studio below UI will render in the browser

simple excel upload UI
Simple Excel Upload UI

There are different libraries available in the market to achieve this goal but here in this program, We are using the Spire library to read the data from the excel sheet. For adding, the reference follows the below steps.

Right Click on reference -> Click on Add reference -> Click on Browse(select the dll from the downloaded place)

Add reference
Add reference

After adding the reference. Now Import the dll reference of Spire Library.

using Spire.Xls;

        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 is 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 data table 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" || ext.Trim()== ".xlsx")
                {
                    Workbook workbook = new Workbook();
                    workbook.LoadFromFile(Path.Combine(path, fileName));
                    Worksheet sheet = workbook.Worksheets[0];
                    dt = sheet.ExportDataTable();
                    grdData.DataSource = dt;
                    grdData.DataBind();
                }

Validate the extension of the excel file, based on the extension.
We simply create an object of Workbook class from the Spire library. All the class-related details are already given on the Spire official website click here. write a query to read the data from an excel sheet and store it in the data table and display that data into the grid view.
The 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" || ext.Trim()== ".xlsx")
                {
                    Workbook workbook = new Workbook();
                    workbook.LoadFromFile(Path.Combine(path, fileName));
                    Worksheet sheet = workbook.Worksheets[0];
                    dt = sheet.ExportDataTable();
                   
                }
           
          
            }
            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