Upload Monthly data

Question

Dear Sir I have a table its contains EMP_codedate  status So i need to upload month data in a table can any one help to me Excel: Code 1/01/2018 2/01/2018 3/01/2018 4/01/2018 5/01/2018 1 P P P P A 2 P A P P P 3 P P P A P 4 P P P P P 5 P P P P P Through Excel sheet i have to save in the table please help to me sir

Dharmraj Thakur · Answer

Hellow 1. Create following table in database 2. Read one by one rows from excel file. 3. Insert in below database tables. Database Tables: Employees ID Name 1 Yesubabu 2 Dharmraj Addendances ID EmployeeID Date Status 1 2 01/01/2018 1 2 2 02/01/2018 1 3 2 03/01/2018 1 4 2 04/01/2018 1 5 2 05/01/2018 0 6 1 01/01/2018 1 7 1 02/01/2018 0 8 1 03/01/2018 1 9 1 04/01/2018 1 10 1 05/01/2018 1

Altaf Ansari · Answer

Try This : private DataTable GetTableDataXl( string XlFile) { DataTable dt = new DataTable(); try { string Ext = Path.GetExtension(XlFile); string connectionString =  ; if (Ext == .xls ) { //For Excel 97-03 connectionString = Provider=Microsoft.Jet.OLEDB.4.0; Data Source = + XlFile + ; Extended Properties = 'Excel 8.0;HDR=YES' ; } else if (Ext == .xlsx ) { //For Excel 07 and greater connectionString = Provider=Microsoft.ACE.OLEDB.12.0; Data Source = + XlFile + ; Extended Properties = 'Excel 8.0;HDR=YES' ; } OleDbConnection conn = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); cmd.Connection = conn; //Fetch 1st Sheet Name conn.Open(); DataTable dtSchema; dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables null ); string ExcelSheetName = dtSchema.Rows[2][ TABLE_NAME ].ToString(); conn.Close(); //Read all data of fetched Sheet to a Data Table conn.Open(); cmd.CommandText = SELECT * From [ + ExcelSheetName + ] ; dataAdapter.SelectCommand = cmd; dataAdapter.Fill(dt); conn.Close(); } catch (Exception ex) { } return dt; }