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