0
Answer

Import Data From excel into Sql Server using C# and Eppplus

ricki Hutagaol

ricki Hutagaol
4y
883
1
I Have a Problem with my project ,
 
It's a Employee absence application,
 
one of the features is  upload schedule, the people from human resource department ,can upload schedule of each employee by using this form /features, but there is some problem with this features,
 
in some case people can upload excel file with wrong date format 
 
 
but in excel file format cells like this
 
when i upload this excel file ,it's failed ,
 
but i try to changes into format cells like this 
 
 
and it was succesfully uploaded ,
it  become a problem because not only me use this application, 
 
how can i solve this?

this is my code
  1. protected void excel(object sender, EventArgs e) {  
  2.  using(ExcelPackage package = new ExcelPackage(existingFile)) //mypath  
  3.  {  
  4.   ExcelWorksheet worksheet = package.Workbook.Worksheets[1];  
  5.   int colCount = worksheet.Dimension.End.Column;  
  6.   int rowCount = worksheet.Dimension.End.Row;  
  7.   table.Columns.Add("Schedule_Date", typeof(DateTime));  
  8.   for (int i = 2; i <= rowCount; i++) {  
  9.    if (!worksheet.Cells[i, 1].Text.ToString().Equals("")) {  
  10.   
  11.     if (!worksheet.Cells[i, 2].Text.ToString().Equals("")) {  
  12.      if (!worksheet.Cells[i, 3].Text.ToString().Equals("")) {  
  13.       DateTime d;  
  14.       string[] formats = {  
  15.        "MM/dd/yyyy",  
  16.        "M/d/yyyy",  
  17.        "MM/d/yyyy",  
  18.        "M/dd/yyyy",  
  19.        "MM/dd/yyyy hh:mm:ss",  
  20.        "MM/dd/yyyy hh:mm:ss tt",  
  21.        "M/dd/yyyy hh:mm:ss tt",  
  22.        "M/d/yyyy hh:mm:ss tt",  
  23.        "MM/d/yyyy hh:mm:ss tt",  
  24.        "MM/dd/yyyy h:mm:ss",  
  25.        "MM/dd/yyyy",  
  26.        "M/d/yyyy h:mm:ss"  
  27.       };  
  28.       TableRow row = new TableRow();  
  29.       TableCell cell1 = new TableCell();  
  30.       TableCell cell2 = new TableCell();  
  31.   
  32.       string schedule_date = Convert.ToString(worksheet.Cells[i, 3].Value.ToString());  
  33.   
  34.       string currentrow = worksheet.Cells[i, 1].Text.ToString() + worksheet.Cells[i, 3].Text.ToString();  
  35.       for (int j = i + 1; j < rowCount + 1; j++) {  
  36.        string nextrow = worksheet.Cells[j, 1].Text.ToString() + worksheet.Cells[j, 3].Text.ToString();  
  37.        if (!currentrow.Equals(nextrow)) {  
  38.         if (DateTime.TryParseExact(schedule_date, formats, new CultureInfo("en-US"), DateTimeStyles.None, out d)) {  
  39.          count += 0;  
  40.          string labelmonth = ddlTest.SelectedValue.ToString();  
  41.          //   string shift = worksheet.Cells[i, 2].Value.ToString();  
  42.          string employee_id = worksheet.Cells[i, 1].Value.ToString();  
  43.          string user = Session["LogedUserID"].ToString();  
  44.          bool validatemonth = ddlTest.SelectedValue.ToString() == DateTime.Parse(worksheet.Cells[i, 3].Value.ToString()).ToString("MM");  
  45.          if (validatemonth.Equals(false)) {  
  46.           count += 1;  
  47.           cell1.Text = "Line" + i + "  "; // +employee_id + " " + schedule_date;//" "  + Convert.ToDateTime(schedule_date).ToString("MM/dd/yyyy");//+ " Baris ke " + i + " Bulan Tidak Sesuai";  
  48.           cell2.Text = "Invalid Month ";  
  49.           row.Cells.Add(cell1);  
  50.           row.Cells.Add(cell2);  
  51.           mytable.Rows.Add(row);  
  52.          } else {  
  53.           if (!shifts.Equals(sshift)) {  
  54.            count += 1;  
  55.            count += 1;  
  56.            cell1.Text = "Line " + i + "  "; // +employee_id + " " + schedule_date;//" "  + Convert.ToDateTime(schedule_date).ToString("MM/dd/yyyy");//+ " Baris ke " + i + " Bulan Tidak Sesuai";  
  57.            cell2.Text = "No Shift";  
  58.            row.Cells.Add(cell1);  
  59.            row.Cells.Add(cell2);  
  60.            mytable.Rows.Add(row);  
  61.           }  
  62.          }  
  63.         } else {  
  64.          count += 1;  
  65.          cell1.Text = "Line " + i + " " + Convert.ToString(schedule_date); //.ToString("MM/dd/yyyy");  
  66.          cell2.Text = "Invalid Format";  
  67.          row.Cells.Add(cell1);  
  68.          row.Cells.Add(cell2);  
  69.          mytable.Rows.Add(row);  
  70.         }  
  71.        } else {  
  72.         cell1.Text = " Line  " + i.ToString();  
  73.         cell2.Text = "same value with  " + j.ToString();  
  74.         row.Cells.Add(cell1);  
  75.         row.Cells.Add(cell2);  
  76.         mytable.Rows.Add(row);  
  77.         count += 1;  
  78.        }  
  79.       }  
  80.      }  
  81.     }  
  82.    }  
  83.   }  
  84.  }  
  85.  if (count.Equals(0)) {  
  86.   insert();  
  87.   File.Move(tempfile, arsipfile);  
  88.  } else {  
  89.   Response.Write("<script>window.alert('Data Tidak Tersimpan')</script>");  
  90.   File.Delete(tempfile);  
  91.  }  
  92. }  
  93. else {  
  94.  File.Delete(tempfile);  
  95.  Response.Write("<script>window.alert('Pilih Bulan Terlebih dahulu')</script>");  
  96. }  
  97. }  
  98. } else {  
  99.  Response.Write("<script>window.alert('File Belum Di Upload')</script>");  
  100. }
  101. }
Next Recommended Forum