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

I am working on an C# application which takes excel sheet as input, reads it and then transfers the data to the database.
The Excel sheet would contain about 80 rows and 30 columns on an average.
Right now,am using Excel Interop API to read excel sheet data cell by cell and then transfering it to a .net Datatable. But using this approach, the applications seems to be behaving pretty slow.
Is there any other faster way of doing this???

Here is the code snippet:

   //Transfer the row data to the excel
   for(int dsRowIndex=0; dsRowIndex <  excelRowCount;dsRowIndex++)
   {     
      if(rowDeleted)
       {
        ((Excel.Range)wkSheet.Rows[dsRowIndex + 2, Type.Missing]).Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
    }
    
    //Read the datatable and transfer each cell's value to the excel sheet
    if(dsRowIndex < excelDataTable.Rows.Count)
    {
     for(int dsColumnIndex = 0; dsColumnIndex < excelColCount ;dsColumnIndex++)
     {
      excApp.Cells[dsRowIndex + 2, dsColumnIndex + 1] = excelDataTable.Rows[dsRowIndex].ItemArray[dsColumnIndex].ToString();
     }
    }        
    
   }

Thanks in advance...!

Cheers,

OleDbConnection, OleDbCommand, OleDbDataReader - F Cali replied to Rahul Saxena on 16-Nov-06 12:34 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

Sushila Patel replied to Rahul Saxena on 16-Nov-06 12:45 PM

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

I am now using OleDbCommand INSERT INTO query - Rahul Saxena replied to F Cali on 20-Nov-06 07:14 AM

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,

 



Use Insert OleDbCommand - F Cali replied to Rahul Saxena on 17-Nov-06 09:07 AM
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.
Filip Krnjic replied to Rahul Saxena on 29-Jul-09 10:14 AM
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/
Mathi4u 4ever replied to Filip Krnjic on 15-Feb-10 12:47 AM
How can I load data from excel to dataset without using OLEDB connection....?
Can anyone help in this...???
mick wen replied to Mathi4u 4ever on 08-Jul-10 10:22 PM

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

anitha replied to Rahul Saxena on 01-Sep-10 08:00 AM
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