public int ImportEmployeeFromExcel(string fileName) { // Buat satu Service menggunakan nama ExcelEntryService // yang memiliki Repository, dimana GetContext dapat digunakan // membaca setiap nama sheet dan link dengan nama table di database / domain model // setiap table mengarah ke service terkait, menggunakan fungsi CreateObject // int count = 0; //DbContext conLinq = new DbContext("Data Source=server name;Initial Catalog=Database Name;Integrated Security=true"); using (var conLinq = new AttPayrollEntities()) { try { DataTable dtExcel = new DataTable(); // Note : HDR=Yes indicates that the first row contains column names, not data. HDR=No indicates the opposite. string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties= 'Excel 12.0;HDR=Yes;IMEX=1'"; OleDbConnection conn = new OleDbConnection(SourceConstr); conn.Open(); // Get Sheets list DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetname = dt.Rows[0]["TABLE_NAME"].ToString(); // string.Format("{0}", "Sheet1$"); string query = string.Format("Select * from [{0}]", sheetname); OleDbDataAdapter data = new OleDbDataAdapter(query, conn); data.Fill(dtExcel); for (int i = 0; i < dtExcel.Rows.Count; i++) { try { //count += conLinq.Database.ExecuteSqlCommand("insert into [Sheet1$] values(" + dtExcel.Rows[i][0] + "," + dtExcel.Rows[i][1] + ",'" + dtExcel.Rows[i][2] + "'," + dtExcel.Rows[i][3] + ")"); // Find Or Create Branch var branchcode = dtExcel.Rows[i][4].ToString(); BranchOffice branchOffice = _branchOfficeService.FindOrCreateObject(branchcode.Replace(" ", String.Empty), branchcode, "-", "-", "-", "-", "-", "-", _companyInfoService); // Find Or Create Department & Division var deptcode = dtExcel.Rows[i][5].ToString(); Department department = _departmentService.FindOrCreateObject(branchOffice.Id, deptcode.Replace(" ", String.Empty), deptcode, "", _branchOfficeService); Division division = _divisionService.FindOrCreateObject(department.Id, deptcode.Replace(" ", String.Empty), deptcode, "", _departmentService); // Find Or Create Title var titlename = dtExcel.Rows[i][3].ToString(); TitleInfo titleInfo = _titleInfoService.FindOrCreateObject(titlename.Replace(" ", String.Empty), titlename, "", false); // Find Or Create Employee Employee employee = new Employee() { DivisionId = division.Id, TitleInfoId = titleInfo.Id, NIK = dtExcel.Rows[i][0].ToString(), StartWorkingDate = DateTime.Parse(dtExcel.Rows[i][1].ToString()), Name = dtExcel.Rows[i][2].ToString(), BankAccount = dtExcel.Rows[i][6].ToString(), Bank = dtExcel.Rows[i][7].ToString(), BirthDate = DateTime.Parse(dtExcel.Rows[i][20].ToString()), PlaceOfBirth = "-", PTKPCode = dtExcel.Rows[i][22].ToString(), NPWP = dtExcel.Rows[i][23].ToString(), Religion = (int)Enum.Parse(typeof(Constant.Religion), dtExcel.Rows[i][26].ToString(), true), Address = dtExcel.Rows[i][28].ToString(), IDNumber = dtExcel.Rows[i][29].ToString(), Sex = dtExcel.Rows[i][27].ToString().Substring(0, 1) == "F" ? 1 : 0, MaritalStatus = dtExcel.Rows[i][22].ToString().Substring(0, 1) == "K" ? 1 : 0, Children = int.Parse(dtExcel.Rows[i][22].ToString().Substring(dtExcel.Rows[i][22].ToString().IndexOf("/"))), PhoneNumber = "-", }; _employeeService.FindOrCreateObject(employee, _divisionService, _titleInfoService); // Find Or Create SalaryStandard // Find Or Create SalaryEmployee } catch (Exception ex) { continue; } } if (count == dtExcel.Rows.Count) { //<--Success Message--> } else { //<--Failure Message--> } dtExcel.Dispose(); } catch (Exception ex) { throw ex; } finally { conLinq.Dispose(); } } return(count); }