Upload Monthly data

Question

Dear Sir,

 

I have  a table its contains EMP_code,date , 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 

 

Answer 1

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

 

Answer 2

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