Excel import in ASP.Net


Hi,

I want to import an Excel file and display the data in asp.net datagrid. How can I import the value from excel file and display in datagrid?

Thanks in advance.
LVL 1
indiageniousAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fanpagesCommented:
Hi,

You'll need to create a reference to the Excel.Application object, open the workbook, retrieve the relevant data from the appropriate worksheet within the workbook, then close the workbook, & quit the object.

For example:

(Pseudo code, as I'm not 100% sure of the ASP.Net syntax)

Dim objExcel_Application ' As Object
Dim vntValue ' As Variant

Set objExcel_Application = CreateObject("Excel.Application")

objExcel_Application.Workbooks.Open "c:\folder\subfolder\filename.xls"

' To retrieve a value from Cell(2, 3) i.e. row 2, column 3, or C3...

vntValue = objExcel_Application.Worksheets("Name of Worksheet").Cells(2,3)

' or

vntValue = objExcel_Application.Worksheets("Name of Worksheet").Range("C3")

' Populate DataGrid as normal, using a loop through the Cells(<row:1..65536>, <column:1..256>) collection

objExcel_Application.ActiveWorkbook.Close False ' Do not save changes

objExcel_Application.Quit ' Close the instance of MS-Excel

Set objExcel_Application = Nothing ' Release resources back to memory pool



Does that help?

BFN,

fp.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blackbrocoliCommented:
I personnally don't like using Excel directly within a web application. It creates an Excel instance on the server and it is really easy to mess up with it. Sometimes, it is even impossible to create an Excel instance on the server (Office is not installed or your application does not have sufficient privileges).

There is a better way to do it, using ADO.NET.
An OleDbCommand object is able to read and write in an Excel file.

Here is a C# example that reads a simple file containing three columns on the first sheet :

//Create the connection
//More can be found about an OleDB Excel connection at http://www.connectionstrings.com
OleDbConnection cnt = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\book1.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");

//Create an empty DataSet
DataSet ds = new DataSet();

//Create an adapter to read the data
// Note that the column names are the values of the first row (because of the "HDR=Yes" option)
// Note that the table name is the name of the sheet followed by a "$" and enclosed in brackets -- it could also be a named range
OleDbDataAdapter adapt = new OleDbDataAdapter("SELECT id, name, job FROM [Sheet1$]", cnt);

//Fill the DataSet
adapt.Fill(ds);


And there you go, you have a DataSet containing the data from the Excel file without using automation.
All that's left is to bind the grid to the DataSet or DataTable.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.