Reading Excel in C#

jc mandapat

20y6.7kVisual Basic .NET

does anyone know how to read an excel file and transfer it's contents to the textboxes? thanks!

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

jc mandapat20y ago
Hey! thanks to all who replied to my question and also for those who supplied the codes. They really helped.thanks again!
saravanan gajendran20y ago
using this sample your import Excel Data using File upload Method ---------------------------------------------------------------- NameSpace: using System.Data.OleDb; using System.Text; using System.Threading; using System.IO; button click event int fileLength=file1.PostedFile.ContentLength; string filename=System.IO.Path.GetFileName(file1.PostedFile.FileName); string path=file1.PostedFile.FileName; Add(path,filename); ------------------------------------------------------------------------- private void Add(string str,string filename) { OleDbConnection strConn =new OleDbConnection ( "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+ str +";" + "Extended Properties=Excel 8.0;"); strConn.Open(); strConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null); DataSet dstObj=new DataSet(); string Sheetname= GetExcelSheetNames(str); OleDbCommand cmd=new OleDbCommand("select * from ["+Sheetname+"]"); cmd.Connection=strConn; OleDbDataAdapter DA=new OleDbDataAdapter(); DA.SelectCommand=cmd; DA.Fill(dstObj); DataGrid1.DataSource=dstObj; DataGrid1.DataBind(); } -------------------------------------------------------------------- private string GetExcelSheetNames(string path) { OleDbConnection objConn = null; System.Data.DataTable dt = null; string strQuery=""; String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended Properties=Excel 8.0;"; objConn = new OleDbConnection(connString); objConn.Open(); dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if(dt == null) {return null; } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; foreach(DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } for(int j=0; j < excelSheets.Length; j++) {// Query each excel sheet. strQuery=excelSheets[j].ToString(); } return strQuery; }
Manoj Bist20y ago
here is the code to read data from excel file and load it in gridview control

app = new Excel.ApplicationClass();
            String filename = "D:\\Test.xls";

            Excel.WorkbookClass workbook = (Excel.WorkbookClass)app.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            Excel.Sheets excelSheets = workbook.Worksheets;
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(1);

            Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A1", "c29");

            DataTable dt = new DataTable("Expense");
            dt.Columns.Add("Date", Type.GetType("System.String"));
            dt.Columns.Add("Item", Type.GetType("System.String"));
            dt.Columns.Add("Price", Type.GetType("System.String"));

            for (int i = 2; i <= excelCell.Rows.Count; i++)
            {
                DataRow dr = dt.NewRow();

                dr["Date"] = ((Excel.Range)excelCell.Cells[i, 1]).Value2.ToString();
                dr["Item"] = ((Excel.Range)excelCell.Cells[i, 2]).Value2.ToString();
                dr["Price"] = ((Excel.Range)excelCell.Cells[i, 3]).Value2.ToString();

                dt.Rows.Add(dr);

            }
            gv.DataSource = dt;
Dipen Lama20y ago
Use ADO.NET to connect to the Excel DataSheet. You can define the connection string datasource to the path of excel file and read in the same way as DB table.