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
Chandradev's Blog 
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
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 ?
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!
Thank you.
to get the full path use : string FilePath=ConfigurationManager.AppSettings[“FilePath”].ToString();
string path = Server.MapPath(FilePath) + filename;
To use this library in my web application , Does Microsoft office needs to be installed in the server?
No. It is not required.
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?
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
Thanks for update. I will review it.