Excel reading using ExcelDataReader dll


Hi
We will get the scenario to read the Excel file and display somewhere or save in some database
We can do this task using so many approaches
1. Using OLEDB driver
It is one of the traditional and famous approaches. But here is one problem. If you will upload huge data i.e more than 255 character in one column. If you will upload it then it will truncate 255 char. But you can fix this issue while changing the registry. But if it is web based application then this approach will be not suitable.
2. Using ExcelDataReader DLL
It is a Lightweight library used for reading Microsoft Excel files in .NET. It is open source dll. Here is not limitation on char size. It is also very simple to use in the project.

Step1: Download the dll using this link
http://exceldatareader.codeplex.com/

Step2: write the code in code behind file like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ICSharpCode.SharpZipLib;
using ICSharpCode;
using Excel;
using System.IO;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void BtnSubmit_Click(object sender, EventArgs e)
    {
        dataUpload();
    }
    protected void dataUpload()
    {

        if (FileUpload1.HasFile)
        {
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

            string path = FileUpload1.PostedFile.FileName;

            FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);

            //for excel 2003
            // IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            // for Excel 2007
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();
            excelReader.IsFirstRowAsColumnNames = true;
            GridView1.DataSource = result;
            GridView1.DataBind();

        }
        else
        {
            LblError.Text = "Unable to upload the selected file. Please check the selected file path or confirm that the file is not blank!";
        }


    }
}

.

You can also download the code from here

Advertisements
Posted in C#. 10 Comments »

10 Responses to “Excel reading using ExcelDataReader dll”

  1. regi Says:

    Hi, I’m trying to execute this and having a FileNotFoundException because the FileUpload1.PostedFile.FileName just gets the file name of the excel file and not the path and so the FileStream was not able to open it. Did you not get the same? If you didn’t

    • Chandra Dev Says:

      No. I didnot get that issue. While uploading the exel file, file is not finding so it is throwing that issue. Are you using fileupload control Ajax update panel ?

  2. free audio software download Says:

    Wow, wonderful weblog layout! How lengthy have you been blogging for?
    you made running a blog glance easy. The entire glance
    of your web site is fantastic, let alone the content material!

    • Chandra Dev Says:

      Thank you.

  3. Gunjan Says:

    to get the full path use : string FilePath=ConfigurationManager.AppSettings[“FilePath”].ToString();
    string path = Server.MapPath(FilePath) + filename;

  4. homur Says:

    To use this library in my web application , Does Microsoft office needs to be installed in the server?

    • Chandra Dev Says:

      No. It is not required.

  5. Prakash Paul Says:

    Hi,
    I also use the same code, code works fine in dev server and UAT server but it is not working in production server.
    even I am not getting any error.
    code excelReader.AsDataSet() returns dataset with out any table.
    do you have any Idea?

  6. Stéphane Château Says:

    You can directly bind class with ExcelDataReader like with LinqToExcel.
    I wrote an adpater to make ExcelDataReader work like LinqToExcel. Of course, not all functions are implemented but it’s a good beginning…
    Have a look to : https://exceldatareader.codeplex.com/discussions/658724

    • Chandra Dev Says:

      Thanks for update. I will review it.


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

%d bloggers like this: