How to read an excel file in asp.netAsk Questions

 Posted on 05/04/2019

I have an Excel file with some data. How can I read it by using OLEDB in asp.net?

Share On: facebook gplus twitter
profile
Asked by Narendra Shah on 05/04/2019 Score: 115 points
Add Comment:

Comments

1 Answers

0 Corrected Answers
Aproved Answers
2
Profile
Answered by Haresh Chaudhari on 5/14/2019 3:15:40 PM Score: 344 points

Hi, here you have to use OLEDB.NET data provider to connect your Excel via connection string and read using ADO.NET. I can show you here with an example. Make sure you have already installed access database engine.

I hope you ahve already created project in .NET environment so here first task is to add connection string in your web.config file as per below guideline.

<connectionStrings>
<add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0"/>
<add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0"/>
</connectionStrings>

You have to take two key for two different file XLS and XLSX. When you upload your excel file, you may check their extension and then use one of above connection string via key name. Here, XLS use Microsoft.Jet.OLEDB.4.0 data provider driver and XLSX use Microsoft.Jet.OLEDB.12.0 data provider driver.

Example in C#:

string strExcelFile = System.Configuration.ConfigurationSettings.AppSettings["ExcelFilePath"].ToString();
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["xlsx"].ToString();


DataTable dtExistingExcel = new DataTable();
OleDbConnection conn = new OleDbConnection(connectionString);
try
{   
conn = new OleDbConnection(connectionString);
conn.Open();
}
catch(Exception Ex)
{
throw Ex;
}

OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", conn);
DataSet excelDataSet = new DataSet();
objDA.Fill(excelDataSet);
if (excelDataSet.Tables[0].Rows.Count > 0)
  //Read your dataset here and bind to any control.
else
//No records found

I hope this help you to read your excel file in asp.net

Thank you

Comments

Add Comment:

Post Your Answers

Existing Members

Sign in to your account
Email Address
Password
New Member?
Sign up and complete profile
Full Name
Email Address
I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the StoodQ newsletters
Guideline to answer a question:

Useful tips to submit your answer
Please read below guidelines before you submit your answer for question.

  • Read and understand question for which you are submitting your answer.
  • Try to avoid grammatical and spell mistake while answering.
  • Do not post any irrelevant information in your answer.
  • Explain your answer with example or any reference link to help who posted question.
  • If you find irrelevant question, please report it to support. Click here to contact support.
  • You agree to the privacy policy and terms of use to submit any contents.

Note: StoodQ is online developers community which helps developer for their difficulty, lets help them with your value contribution.