Reading Excel in C#

Question

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

jc mandapat · Answer

Hey! thanks to all who replied to my question and also for those who supplied the codes. They really helped.thanks again!

saravanan gajendran · Answer

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(pathfilename); ------------------------------------------------------------------------- private void Add(string strstring filename) { OleDbConnection strConn =new OleDbConnection ( Provider=Microsoft.Jet.OLEDB.4.0; + Data Source=+ str +; + Extended Properties=Excel 8.0;); strConn.Open(); strConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tablesnull); 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 Bist · Answer

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 { 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 Lama · Answer

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.