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

Question

How to read and write data from excel sheet using c# .net? Give me the articles and solution?

Joseph Barnes · Answer

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.

Lasantha PW · Answer

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; }

Sandeep Shekhawat · Answer

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.

Gomathi Palaniswamy · Answer

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

Mamta M · Answer

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

Krishna Garad · Answer

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