C# .NET - Reading from Excel File in .Net - Asked By Rahul Saxena on 16-Nov-06 12:28 PM

Another way of reading an Excel file and loading it to a DataTable is by using OleDbConnection, OleDbCommand and OleDbDataReaders.  Basically you will treat the Excel file like a database where each work sheet represents a table.  Here's a sample code taken from the following link:

http://vbcity.com/forums/topic.asp?tid=135275

OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Temp\Test1.xls;Extended Properties=Excel 8.0");
OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
OleDbDataReader dr;

connection.Open();
dr = command.ExecuteReader(CommandBehavior.CloseConnection);

DataTable excelData = new DataTable("ExcelData");
excelData.Load(dr);

dataGridView1.DataSource = excelData;

http://www.sql-server-helper.com

You can read the excel file and display data in datagrid

Also you can update using datagrid approach

put in your .aspx.cs file:
------------------------------------
    protected void btnImportExcelFile_Click(object sender, EventArgs e)
    {

        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
        "Data Source=C:\\exceltest.xls;" +
        "Extended Properties=Excel 8.0;";
        //You must use the $ after the object you reference in the spreadsheet
        OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Tabelle1$]", strConn);

        DataSet myDataSet = new DataSet();
        myCommand.Fill(myDataSet, "ExcelInfo");
        DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
        DataGrid1.DataBind();

    }

put in your .aspx file:
------------------------------------
<asp:Button ID="btnImportExcelFile" runat="server" OnClick="btnImportExcelFile_Click"
Text="Import Excel File" /></div><br />
<asp:DataGrid id=DataGrid1 runat="server"/>

You can use DataReader also for display of data

to insert data from a .Net datatable into an excel sheet.

Am getting the following error during the same:

"The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data."

Any idea,how to fix this???

Here is the code:

Thanks,

 

As I've mentioned you can treat the Excel file as a database with the worksheet being a table.  Given this, to insert records to it, you can also use an OleDbCommand with an INSERT statement.  This is faster than doing it cell by cell.

Hi,

have you considered using some 3rd party component? You could try using GemBox http://www.gemboxsoftware.com/GBSpreadsheet.htm which works very fast and it is easy to use. Free version is available if you need to use less then 150 rows so maybe it could be useful to you.

Also here is a list of reasons why it is better to use this component then http://www.gemboxsoftware.com/Excel-Automation.htm.

Filip
GemBox.Spreadsheet - http://www.gemboxsoftware.com/

How can I load data from excel to dataset without using OLEDB connection....?
Can anyone help in this...???

I suggest to use a excel component, I use from last year, quit good. It  load data from excel to dataset without using OLEDB connection. reading and writing to excel 97 ~ 2010 from c#,extremely easy to use.

http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html

hello ,rahul,
did u get any solution for the below issue.if u r having please tell me.I am facing the same issue in my application.

Thank You.
Anitha