How to read and write data from excel sheet using c# .net?

Hello,

I don't recommend you to use Microsoft automation to read and write Excel files. It is very slow on large files.

In my project I used Excel .NET component to read data from a spreadsheet and export it to the database. It works excellent.

try this way

        protected void Button1_Click(object sender, EventArgs e)
        {
            // manage csv file
            DataSet ds = loadCVS(-1);
            DataTable table = ds.Tables[0];


            foreach (DataRow row in table.Rows)
            {
               
                //iterate through datatable
            }
              
              
            }
          
        }

        public DataSet loadCVS(int noofrows)
        {
            DataSet ds = new DataSet();
            try
            {
                // Creates and opens an ODBC connection
                string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
                string sql_select;
                OdbcConnection conn;
                conn = new OdbcConnection(strConnString.Trim());
                conn.Open();

                //Creates the select command text
                if (noofrows == -1)
                {
                    sql_select = "select * from [" + this.FileNevCSV.Trim() + "]";
                }
                else
                {
                    sql_select = "select top " + noofrows + " * from [" + this.FileNevCSV.Trim() + "]";
                }

                //Creates the data adapter
                OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);

                //Fills dataset with the records from CSV file
                obj_oledb_da.Fill(ds, "csv");

                //closes the connection
                conn.Close();
            }

            catch (Exception e) //Error
            {

            }
            return ds;

        }

Read from .xls file (Microsoft Execl 2003).

Use Following namespace

using System.Data.OleDb;

 

Make a connection from .xls file(Excel File)

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(@"~/ExcelFileFolderName/" + Filename.xls) + ";Extended Properties=Excel 8.0;");

 

Retrieve data from Filename.xls file

OleDbDataAdapter dap = new OleDbDataAdapter("select * from [Sheet1$]", con);

 

Now excel file table insert in DataSet

DataSet ds = new DataSet();

dap.Fill(ds);

 

Now fruther any working can be DataSet

and DataSet show by GridView using GridView

DataSource property and DataBind() method.

Hi friend,

check http://social.msdn.microsoft.com/Forums/en/vsto/thread/b6e8a28c-6760-4e86-a1aa-e2ce9ec36380

You need to use office automation features with C#.

Refer this article and implement similarly for Excel

http://www.c-sharpcorner.com/UploadFile/amrish_deep/WordAutomation05102007223934PM/WordAutomation.aspx

See this article http://tempuri.org/tempuri.html