public static DataColumn GetDataColumnFrom(string excelFileNameWithoutExtension)
{
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"Data\\{excelFileNameWithoutExtension}.xls");
DataTable dataTable;
using (var workBook = new WorkBook())
{
workBook.read(fileName);
dataTable = workBook.ExportDataTable(0, 0, workBook.LastRow + 1, workBook.LastCol + 1);
}
var timeValues = new List <float>();
var concValues = new List <float>();
foreach (DataRow row in dataTable.Rows)
{
timeValues.Add(row[0].ConvertedTo <float>());
concValues.Add(row[1].ConvertedTo <float>());
}
var baseGrid = new BaseGrid("Time", "Time", Constants.Dimension.NO_DIMENSION)
{
Values = timeValues.ToArray()
};
return(new DataColumn("Value", excelFileNameWithoutExtension, Constants.Dimension.NO_DIMENSION, baseGrid)
{
Values = concValues.ToArray()
});
}
internal DataTable GetDataTable(string filePath)
{
WorkBook workBook = new WorkBook();
this.ReadFile(filePath, workBook);
DataTable dt = workBook.ExportDataTable(0, 0, workBook.LastRow + 1, workBook.LastCol + 1, true);
return(dt);
}
protected void ImportFile(object sender, DirectEventArgs e)
{
try
{
var workbook = new WorkBook();
var fromRow = 2;
var toRow = 2;
// upload file
var path = string.Empty;
if (fileExcel.HasFile)
{
path = UploadFile(fileExcel, Constant.PathTemplate);
if (path != null)
{
// Read data from excel
workbook.readXLSX(path);
// Check validation workbook
if (CheckValidation(workbook, out fromRow, out toRow) == false)
{
return;
}
// Export to datatable
var dataTable = workbook.ExportDataTable(fromRow - 1, //first row
0, //first col
toRow - 1, //last row
workbook.LastCol + 1, //last col
false, //first row as header
false //convert to DateTime object if it match date pattern
);
hdfDataTable.Text = JSON.Serialize(dataTable);
ContinueProcess(count);
}
}
else
{
Dialog.Alert("Bạn chưa chọn tệp tin đính kèm. Vui lòng chọn.");
return;
}
}
catch (Exception ex)
{
Dialog.Alert("Có lỗi xảy ra trong quá trình xử lý {0}".FormatWith(ex.Message));
}
}
private static IEnumerable <DataTable> dataTables(string fileName, string sheetName, bool firstRowAsCaption)
{
using (var workbook = new WorkBook())
{
workbook.ReadExcelFile(fileName);
for (var i = 0; i < workbook.NumSheets; i++)
{
if (!String.IsNullOrEmpty(sheetName) && !workbook.getSheetName(i).Equals(sheetName))
{
continue;
}
workbook.Sheet = i;
//+1 because lastrow starts counting from 0, but export counts from 1
yield return(workbook.ExportDataTable(0, 0, workbook.LastRow + 1, workbook.LastCol + 1, firstRowAsCaption));
}
}
}
protected void fileAttachment_FileSelected(object sender, DirectEventArgs e)
{
try
{
var workbook = new WorkBook();
var path = string.Empty;
if (fileExcel.HasFile)
{
path = UploadFile(fileExcel, relativePath);
if (path != null)
{
// Read data from excel
workbook.readXLSX(path);
if (!txtSheetName.IsEmpty)
{
workbook.Sheet = workbook.findSheetByName(txtSheetName.Text);
}
// Export to datatable
var dataTable = workbook.ExportDataTable(1, //first row
0, //first col
workbook.LastRow, //last row
workbook.LastCol + 1, //last col
true, //first row as header
true //convert to DateTime object if it match date pattern
);
hdfDataTable.Text = JSON.Serialize(dataTable);
}
}
else
{
Dialog.Alert("Bạn chưa chọn tệp tin đính kèm. Vui lòng chọn.");
return;
}
}
catch (Exception ex)
{
Dialog.Alert("Có lỗi xảy ra trong quá trình xử lý {0}".FormatWith(ex.Message));
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpdateImportExcel_Click(object sender, DirectEventArgs e)
{
var workbook = new WorkBook();
// upload file
if (fileExcel.HasFile)
{
var path = UploadFile(fileExcel, Constant.PathTemplate);
if (path != null)
{
// Read data from excel
workbook.readXLSX(Path.Combine(Server.MapPath("~/"), Constant.PathTemplate, path));
// Export to datatable
var dataTable = workbook.ExportDataTable(0, //first row
0, //first col
workbook.LastRow + 1, //last row
workbook.LastCol + 1, //last col
true, //first row as header
false //convert to DateTime object if it match date pattern
);
// get config by ID
var salaryBoardConfig = SalaryBoardConfigController.GetAll(int.Parse(hdfConfigId.Text), null, null, null,
SalaryConfigDataType.DynamicValue, null, null);
foreach (DataRow row in dataTable.Rows)
{
// get employee code
var employeeCode = row[EmployeeCode].ToString();
if (string.IsNullOrEmpty(employeeCode))
{
continue;
}
// get record by employee code
var record = RecordController.GetByEmployeeCode(employeeCode);
foreach (DataColumn col in dataTable.Columns)
{
foreach (var item in salaryBoardConfig)
{
// check column name exists
if (item.Display != col.ColumnName)
{
continue;
}
// check empty string
var value = Convert.ToString(row[col], CultureInfo.InvariantCulture);
if (string.IsNullOrEmpty(value))
{
continue;
}
// check if dynamic column value exists
var salaryBoardDynamicColumn = SalaryBoardDynamicColumnController.GetAll(null, record.Id.ToString(), int.Parse(hdfPayrollId.Text), item.ColumnCode, true, null, null);
if (salaryBoardDynamicColumn.Count > 0)
{
// update dynamic column value
salaryBoardDynamicColumn.First().Value = value;
SalaryBoardDynamicColumnController.Update(salaryBoardDynamicColumn.First());
}
else
{
// create dynamic column value
var salaryBoardConfigColumn = new SalaryBoardDynamicColumnModel()
{
RecordId = record.Id,
SalaryBoardId = int.Parse(hdfPayrollId.Text),
ColumnCode = item.ColumnCode,
Value = value,
Display = item.Display
};
SalaryBoardDynamicColumnController.Create(salaryBoardConfigColumn);
}
}
}
}
}
}
else
{
Dialog.Alert("Bạn chưa chọn tệp tin đính kèm. Vui lòng chọn.");
return;
}
Dialog.Alert("Cập nhật thành công");
grpPayrollDetail.Reload();
}
protected void btnUpdateImportExcel_Click(object sender, DirectEventArgs e)
{
var workbook = new WorkBook();
var fromRow = 2;
var toRow = 2;
// upload file
var path = string.Empty;
if (fileExcel.HasFile)
{
path = UploadFile(fileExcel, PathTemplate);
if (path != null)
{
// Read data from excel
workbook.readXLSX(path);
// Check validation workbook
if (CheckValidation(workbook, out fromRow, out toRow) == false)
{
return;
}
// Export to datatable
var dataTable = workbook.ExportDataTable(0, //first row
0, //first col
workbook.LastRow + 1, //last row
workbook.LastCol + 1, //last col
true, //first row as header
false //convert to DateTime object if it match date pattern
);
var salaryBoardConfig = hr_SalaryBoardConfigServices.GetByConfigId(null, int.Parse(hdfConfigId.Text));
foreach (DataRow row in dataTable.Rows)
{
var employeeCode = row[EmployeeCodeColumn].ToString();
if (!string.IsNullOrEmpty(employeeCode))
{
var recordId = hr_RecordServices.GetRecordIdByEmployeeCode(employeeCode);
foreach (DataColumn col in dataTable.Columns)
{
foreach (var item in salaryBoardConfig)
{
if (item.Display == col.ColumnName)
{
// check if item exists
var salaryBoardDynamicColumn = hr_SalaryBoardDynamicColumnService.GetBoardDynamicColumn(recordId, item.ColumnCode, int.Parse(hdfSalaryBoardListID.Text));
var value = Convert.ToString(row[col], CultureInfo.InvariantCulture);
if (salaryBoardDynamicColumn != null)
{
salaryBoardDynamicColumn.Value = value;
hr_SalaryBoardDynamicColumnService.Update(salaryBoardDynamicColumn);
}
else
{
var salaryBoardConfigColumn = new hr_SalaryBoardDynamicColumn()
{
RecordId = recordId,
SalaryBoardId = int.Parse(hdfSalaryBoardListID.Text),
ColumnCode = item.ColumnCode,
Value = value,
IsInUsed = true,
CreatedDate = DateTime.Now,
Display = item.Display
};
hr_SalaryBoardDynamicColumnService.Create(salaryBoardConfigColumn);
}
}
}
}
}
}
}
}
else
{
Dialog.Alert("Bạn chưa chọn tệp tin đính kèm. Vui lòng chọn.");
return;
}
ResetFormUpdate();
Dialog.Alert("Cập nhật thành công");
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpdateImportExcel_Click(object sender, DirectEventArgs e)
{
var workbook = new WorkBook();
if (fileExcel.HasFile)
{
var path = UploadFile(fileExcel, Constant.PathTemplate);
if (path != null)
{
// init list changes
var allowanceChanges = new List <SalaryAllowanceModel>();
// Read data from excel
workbook.readXLSX(Path.Combine(Server.MapPath("~/"), Constant.PathTemplate, path));
// Export to data table
var dataTable = workbook.ExportDataTable(0, //first row
0, //first col
workbook.LastRow + 1, //last row
workbook.LastCol + 1, //last col
true, //first row as header
false //convert to DateTime object if it match date pattern
);
foreach (DataRow row in dataTable.Rows)
{
// get value from cell
var allowanceCode = row[nameof(SalaryAllowanceModel.AllowanceCode)].ToString();
var allowanceName = row[nameof(SalaryAllowanceModel.AllowanceName)].ToString();
var factor = row[nameof(SalaryAllowanceModel.Factor)].ToString();
var percent = row[nameof(SalaryAllowanceModel.Percent)].ToString();
var value = row[nameof(SalaryAllowanceModel.Value)].ToString();
var salaryDecisionId = int.TryParse(hdfId.Text, out var result) && result > 0 ? result : 0;
// check condition
if (string.IsNullOrEmpty(allowanceCode))
{
continue;
}
if (!decimal.TryParse(factor, out var factorResult))
{
continue;
}
if (!decimal.TryParse(percent, out var percentResult))
{
continue;
}
if (!decimal.TryParse(value, out var valueResult))
{
continue;
}
// get catalog by code
var catalogAllowance = CatalogAllowanceController.GetByCode(allowanceCode);
if (catalogAllowance == null)
{
continue;
}
// init model
var salaryAllowance = new SalaryAllowanceModel();
// get allowance by decision and code
var salaryAllowances = SalaryAllowanceController.GetAll(null, salaryDecisionId, allowanceCode, null, 1);
if (salaryAllowances.Any())
{
salaryAllowance = salaryAllowances.First();
}
else
{
salaryAllowance.Id = 0;
salaryAllowance.AllowanceCode = allowanceCode;
}
// check are there any changes
if (salaryAllowance.Factor != factorResult || salaryAllowance.Percent != percentResult ||
salaryAllowance.Value != valueResult)
{
// set value
salaryAllowance.AllowanceName = allowanceName;
salaryAllowance.Factor = factorResult;
salaryAllowance.Percent = percentResult;
salaryAllowance.Value = valueResult;
// save data to hidden field
SaveEditData(JSON.Serialize(salaryAllowance));
// add data to list changes
allowanceChanges.Add(salaryAllowance);
}
if (salaryAllowance.Factor != 0 || salaryAllowance.Percent != 0 || salaryAllowance.Value != 0)
{
allowanceChanges.Add(salaryAllowance);
}
}
// hide window
wdExcel.Hide();
// bind data
storeAllowanceCatalog.DataSource = CreateSalaryAllowanceTable(allowanceChanges);
storeAllowanceCatalog.DataBind();
}
Dialog.ShowNotification("Thêm thành công");
}
else
{
Dialog.Alert("Bạn chưa chọn tệp tin đính kèm. Vui lòng chọn.");
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ImportFile(object sender, DirectEventArgs e)
{
try
{
var workbook = new WorkBook();
if (fileExcel.HasFile)
{
var path = UploadFile(fileExcel, Constant.PathTemplate);
if (path == null)
{
return;
}
// Read data from excel
workbook.readXLSX(Path.Combine(Server.MapPath("~/"), Constant.PathTemplate, path));
if (!txtSheetName.IsEmpty)
{
workbook.Sheet = workbook.findSheetByName(txtSheetName.Text);
}
// Export to datatable
var dataTable = workbook.ExportDataTable(1, //first row
0, //first col
workbook.LastRow, //last row
workbook.LastCol + 1, //last col
true, //first row as header
true //convert to DateTime object if it match date pattern
);
// count success create
var successRecords = new List <int>();
foreach (DataRow row in dataTable.Rows)
{
// Check employee code
var employeeCode = row["EmployeeCode"].ToString().Trim();
var idNumber = row["IDNumber"].ToString().Trim();
if (!string.IsNullOrEmpty(employeeCode))
{
var recordList = RecordController.CheckExistIDNumberAndEmployeeCode(null, null, employeeCode);
if (recordList != null && recordList.Count > 0)
{
continue;
}
}
// Check id number
if (!string.IsNullOrEmpty(idNumber))
{
var recordList = RecordController.CheckExistIDNumberAndEmployeeCode(null, idNumber, null);
if (recordList != null && recordList.Count > 0)
{
continue;
}
}
// Create new record
var record = new hr_Record
{
// Set default work status
WorkStatusId = CatalogWorkStatusController.GetByGroup(RecordStatus.Working).Id
};
foreach (DataColumn col in dataTable.Columns)
{
if (string.IsNullOrEmpty(row[col].ToString()))
{
continue;
}
var condition = " [Name] = N'{0}' ".FormatWith(row[col]);
cat_Location location;
switch (col.ColumnName)
{
case nameof(hr_Record.FullName):
record.FullName = row[col].ToString();
// lấy họ và đệm từ họ tên
var position = record.FullName.LastIndexOf(' ');
record.Name = position == -1 ? record.FullName : record.FullName.Substring(position + 1).Trim();
break;
case nameof(hr_Record.Sex):
record.Sex = row[col].ToString().Equals("Nam");
break;
case nameof(hr_Record.BirthPlaceWardId):
location = cat_LocationServices.GetByCondition(condition);
record.BirthPlaceWardId = location.Id;
break;
case nameof(hr_Record.BirthPlaceDistrictId):
location = cat_LocationServices.GetByCondition(condition);
record.BirthPlaceDistrictId = location.Id;
break;
case nameof(hr_Record.BirthPlaceProvinceId):
location = cat_LocationServices.GetByCondition(condition);
record.BirthPlaceProvinceId = location.Id;
break;
case nameof(hr_Record.HometownWardId):
location = cat_LocationServices.GetByCondition(condition);
record.HometownWardId = location.Id;
break;
case nameof(hr_Record.HometownDistrictId):
location = cat_LocationServices.GetByCondition(condition);
record.HometownDistrictId = location.Id;
break;
case nameof(hr_Record.HometownProvinceId):
location = cat_LocationServices.GetByCondition(condition);
record.HometownProvinceId = location.Id;
break;
default:
// TODO : need create util function
var reg = @"\(([^)]*)\)";
var propVal = row[col];
if (Regex.IsMatch(propVal.ToString(), reg))
{
propVal = Regex.Match(propVal.ToString(), reg).Groups[1].Value;
}
//get the property information based on the type
var propInfo = record.GetType().GetProperty(col.ColumnName);
//find the property type
if (propInfo == null)
{
break;
}
var propType = propInfo.PropertyType;
//equivalent to the specified object.
if (propType.IsEnum)
{
propVal = Enum.ToObject(propType, int.Parse(propVal.ToString()));
}
if (propType.Name == typeof(Nullable <>).Name)
{
if (Nullable.GetUnderlyingType(propType) == typeof(DateTime))
{
if (!DateTime.TryParseExact(row[col].ToString(), "dd/MM/yyyy",
CultureInfo.CurrentCulture, DateTimeStyles.None, out var date))
{
break;
}
propVal = Convert.ChangeType(date, typeof(DateTime));
}
else
{
propVal = Convert.ChangeType(propVal, Nullable.GetUnderlyingType(propType));
}
}
else
{
propVal = Convert.ChangeType(propVal, propType);
}
//Set the value of the property
propInfo.SetValue(record, propVal, null);
break;
}
}
// add record
hr_RecordServices.Create(record);
successRecords.Add(dataTable.Rows.IndexOf(row));
}
Dialog.Alert("Thêm thành công " + successRecords.Count + " bản ghi");
grpImportExcel.Reload();
}
else
{
Dialog.Alert("Bạn chưa chọn tệp tin đính kèm. Vui lòng chọn.");
}
}
catch (Exception ex)
{
Dialog.ShowError("Đã có lỗi xảy ra: " + ex.Message);
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="path">Đường dẫn đến file excel</param>
public static List <T> ImportExcel <T>(string path) where T : new()
{
try
{
var list = new List <T>();
if (path != null)
{
var workbook = new WorkBook();
// Read data from excel
workbook.readXLSX(path);
// Export to datatable
var dataTable = workbook.ExportDataTable(1, //first row
0, //first col
workbook.LastRow, //last row
workbook.LastCol + 1, //last col
true, //first row as header
false //convert to DateTime object if it match date pattern
);
foreach (DataRow row in dataTable.Rows)
{
var obj = new T();
foreach (DataColumn col in dataTable.Columns)
{
foreach (var property in typeof(T).GetProperties())
{
// get prop description
var attributeDisplayName = property.GetCustomAttribute(typeof(DisplayNameAttribute));
var attributeType = property.GetCustomAttribute(typeof(TypeConverterAttribute));
if (attributeDisplayName != null && col.ColumnName == property.Name && property.CanWrite)
{
var value = row[col];
if (attributeType != null)
{
var reg = @"\(([^)]*)\)";
if (Regex.IsMatch(value.ToString(), reg))
{
value = Regex.Match(value.ToString(), reg).Groups[1].Value;
}
}
if (!property.PropertyType.IsEnum)
{
var val = value;
if (!(val is DBNull))
{
typeof(T).InvokeMember(property.Name, BindingFlags.SetProperty, null, obj,
new[] { val });
}
}
else
{
var val = Enum.ToObject(property.PropertyType, Convert.ToInt32(value));
if (!(val is DBNull))
{
typeof(T).InvokeMember(property.Name, BindingFlags.SetProperty, null, obj,
new[] { val });
}
}
}
}
}
// create
list.Add(obj);
}
}
return(list);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpdateImportExcel_Click(object sender, DirectEventArgs e)
{
var workbook = new WorkBook();
// upload file
if (fileExcel.HasFile)
{
var path = UploadFile(fileExcel, Constant.PathTemplate);
if (path != null)
{
// Read data from excel
workbook.readXLSX(Path.Combine(Server.MapPath("~/"), Constant.PathTemplate, path));
// Check validation workbook
if (CheckValidation(workbook, out _, out _, txtFromRow, txtToRow, txtSheetName) == false)
{
return;
}
// Export to datatable
var dataTable = workbook.ExportDataTable(0, //first row
0, //first col
workbook.LastRow + 1, //last row
workbook.LastCol + 1, //last col
true, //first row as header
false //convert to DateTime object if it match date pattern
);
var interview = InterviewController.GetById(Convert.ToInt32(hdfId.Text));
var interviewDate = new DateTime();
if (interview != null)
{
interviewDate = interview.InterviewDate;
}
foreach (DataRow row in dataTable.Rows)
{
//get employee code
var employeeCode = row[EmployeeCode].ToString();
if (string.IsNullOrEmpty(employeeCode))
{
continue;
}
// get record by employee code
var candidate = CandidateController.GetByCode(employeeCode, CandidateType.Interview);
//check if value exists
var candidateInterviewModel = CandidateInterviewController.GetUnique(Convert.ToInt32(hdfId.Text), candidate.RecordId);
if (candidateInterviewModel != null)
{
//update value
EditDataToModel(dataTable, row, candidateInterviewModel, interviewDate);
candidateInterviewModel.EditedBy = CurrentUser.User.UserName;
candidateInterviewModel.EditedDate = DateTime.Now;
CandidateInterviewController.Update(candidateInterviewModel);
}
else
{
var model = new CandidateInterviewModel()
{
RecordId = candidate.RecordId,
InterviewId = Convert.ToInt32(hdfId.Text),
EditedBy = CurrentUser.User.UserName,
EditedDate = DateTime.Now,
CreatedBy = CurrentUser.User.UserName,
CreatedDate = DateTime.Now
};
EditDataToModel(dataTable, row, model, interviewDate);
//create
CandidateInterviewController.Create(model);
}
}
}
}
else
{
Dialog.Alert("Bạn chưa chọn tệp tin đính kèm. Vui lòng chọn.");
return;
}
Dialog.Alert("Cập nhật thành công");
//Reset form excel
fileExcel.Reset();
txtSheetName.Reset();
//close window
wdImportExcelFile.Hide();
}
/// <summary>
///
/// </summary>
/// <param name="keyword"></param>
/// <param name="payrollId"></param>
/// <param name="order"></param>
/// <param name="limit"></param>
/// <returns></returns>
public static DataTable GetPayrollDetail(string keyword, int payrollId, string order, int?limit)
{
// get payroll
var payroll = GetById(payrollId);
if (payroll == null)
{
return(null);
}
// get salary board info
var salaryBoardInfos = SalaryBoardInfoController.GetAll(keyword, payrollId.ToString(), order, limit);
if (payroll.Status == PayrollStatus.Locked)
{
return(JsonConvert.DeserializeObject <DataTable>(payroll.Data));
}
// get salary board config
var salaryBoardConfigs = SalaryBoardConfigController.GetAll(payroll.ConfigId, true, null, null, null, null, null);
// get payroll dynamic data
var salaryBoardDynamicColumns = SalaryBoardDynamicColumnController.GetAll(null, null, payroll.Id, null, true, null, null);
var workbook = new WorkBook {
AutoCalc = false
};
// generate default column
workbook.setText("A1", "Id");
workbook.setText("B1", "RecordId");
workbook.setText("C1", "FullName");
workbook.setText("D1", "EmployeeCode");
workbook.setText("E1", "PositionName");
// generate other column
foreach (var config in salaryBoardConfigs)
{
// set column name as Property name
workbook.setText(config.ColumnExcel + 1, config.ColumnCode);
}
// fill data into table
for (var i = 0; i < salaryBoardInfos.Count; i++)
{
workbook.setNumber("A{0}".FormatWith(i + 2), salaryBoardInfos[i].Id);
workbook.setNumber("B{0}".FormatWith(i + 2), salaryBoardInfos[i].RecordId);
workbook.setText("C{0}".FormatWith(i + 2), salaryBoardInfos[i].FullName);
workbook.setText("D{0}".FormatWith(i + 2), salaryBoardInfos[i].EmployeeCode);
workbook.setText("E{0}".FormatWith(i + 2), salaryBoardInfos[i].PositionName);
// add value for dynamic column
foreach (var config in salaryBoardConfigs)
{
var columnExcel = config.ColumnExcel + (i + 2);
switch (config.DataType)
{
case SalaryConfigDataType.FieldValue:
var type = salaryBoardInfos[i].GetType();
var propInfo = type.GetProperty(config.ColumnCode);
if (propInfo == null)
{
break;
}
var value = Convert.ToString(propInfo.GetValue(salaryBoardInfos[i], BindingFlags.GetProperty, null, null, CultureInfo.InvariantCulture), CultureInfo.InvariantCulture);
// convert to double
if (double.TryParse(value, NumberStyles.Float, CultureInfo.InvariantCulture, out var result))
{
workbook.setNumber(columnExcel, result);
}
else
{
workbook.setText(columnExcel, value);
}
break;
case SalaryConfigDataType.FixedValue:
case SalaryConfigDataType.FieldFormula:
case SalaryConfigDataType.Formula:
workbook.setFormula(columnExcel, config.Formula.FormatWith(i + 2).TrimStart('='));
break;
case SalaryConfigDataType.DynamicValue:
var findObj = salaryBoardDynamicColumns.Find(o =>
o.RecordId == salaryBoardInfos[i].RecordId && o.ColumnCode == config.ColumnCode);
if (findObj != null)
{
// convert to double
if (double.TryParse(findObj.Value, NumberStyles.Float, CultureInfo.InvariantCulture, out var result2))
{
workbook.setNumber(columnExcel, result2);
}
else
{
workbook.setText(columnExcel, findObj.Value);
}
}
else
{
workbook.setText(columnExcel, string.Empty);
}
break;
default:
workbook.setText(columnExcel, string.Empty);
break;
}
}
}
workbook.recalc();
var table = workbook.ExportDataTable(0, 0, workbook.LastRow + 1, workbook.LastCol + 1);
return(workbook.ExportDataTable(0, 0, workbook.LastRow + 1, workbook.LastCol + 1));
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpdateImportExcel_Click(object sender, DirectEventArgs e)
{
var workbook = new WorkBook();
// upload file
if (fileExcel.HasFile)
{
var path = UploadFile(fileExcel, Constant.PathTemplate);
if (path != null)
{
// Read data from excel
workbook.readXLSX(Path.Combine(Server.MapPath("~/"), Constant.PathTemplate, path));
// Check validation workbook
if (CheckValidation(workbook, out _, out _, txtFromRow, txtToRow, txtSheetName) == false)
{
return;
}
// Export to datatable
var dataTable = workbook.ExportDataTable(0, //first row
0, //first col
workbook.LastRow + 1, //last row
workbook.LastCol + 1, //last col
true, //first row as header
false //convert to DateTime object if it match date pattern
);
var argumentModels = ArgumentController.GetAll(null, false, KpiStatus.Active, null, null, null);
foreach (DataRow row in dataTable.Rows)
{
//get employee code
var employeeCode = row[EmployeeCode].ToString();
if (string.IsNullOrEmpty(employeeCode))
{
continue;
}
// get record by employee code
var record = RecordController.GetByEmployeeCode(employeeCode);
foreach (DataColumn col in dataTable.Columns)
{
foreach (var argument in argumentModels)
{
//check column name exists
if (argument.Name != col.ColumnName)
{
continue;
}
//check empty string
var value = Convert.ToString(row[col], CultureInfo.InvariantCulture);
if (string.IsNullOrEmpty(value))
{
continue;
}
//check if value exists
var employeeArgument = EmployeeArgumentController.GetUnique(Convert.ToInt32(hdfGroup.Text), record.Id, argument.Id,
Convert.ToInt32(hdfMonth.Text), Convert.ToInt32(hdfYear.Text));
if (employeeArgument != null)
{
//update value
employeeArgument.Value = value;
employeeArgument.GroupId = !string.IsNullOrEmpty(hdfGroup.Text)
? Convert.ToInt32(hdfGroup.Text)
: 0;
employeeArgument.EditedBy = CurrentUser.User.UserName;
employeeArgument.EditedDate = DateTime.Now;
EmployeeArgumentController.Update(employeeArgument);
}
else
{
var model = new EmployeeArgumentModel()
{
RecordId = record.Id,
ArgumentId = argument.Id,
GroupId = !string.IsNullOrEmpty(hdfGroup.Text)
? Convert.ToInt32(hdfGroup.Text)
: 0,
Month = Convert.ToInt32(hdfMonth.Text),
Year = Convert.ToInt32(hdfYear.Text),
Value = value
};
EmployeeArgumentController.Create(model);
}
}
}
}
//reset
hdfGroup.Reset();
cboGroup.Reset();
hdfDepartmentId.Reset();
cboDepartment.Reset();
}
}
else
{
Dialog.Alert("Bạn chưa chọn tệp tin đính kèm. Vui lòng chọn.");
return;
}
Dialog.Alert("Cập nhật thành công");
//Reset form excel
fileExcel.Reset();
txtSheetName.Reset();
//reload grid
gpEmployeeArgument.Reload();
}
private void openFD_FileOk(object sender, CancelEventArgs e)
{
try
{
ASignal.Clear();
KursCurves.Clear();
comboSignalsAprox.Items.Clear();
var wb = new WorkBook();
wb.readXLSX(openFD.FileName);
DataTable dt = wb.ExportDataTable();
double t = 0;
double d = 0.01;
for (int j = 2; j < dt.Columns.Count; j++)
{
string key = dt.Rows[0][j].ToString();
comboSignalsAprox.Items.Add(key);
KursCurves.Add(key, new PointPairList());
t = 0;
for (int i = 1; i < dt.Rows.Count; i++, t += d)
{
object o = dt.Rows[i][j];
KursCurves[key].Add(new PointPair(i, Convert.ToDouble(o)));
}
}
comboSignalsAprox.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
