/// <summary>
///
/// </summary>
/// <param name="e"></param>
/// <param name="workbook"></param>
/// <param name="col"></param>
/// <param name="fromRow"></param>
/// <param name="toRow"></param>
public void CreateDropDownEnum(string enumName, WorkBook workbook, DataColumn col, int fromRow, int toRow)
{
var list = Enum.GetValues(typeof(RecordStatus)).Cast <RecordStatus>().ToList();
var validation = workbook.CreateDataValidation();
validation.Type = DataValidation.eUser;
var validateList = "\"{0}\"".FormatWith(string.Join(",", list.Select(l => l.Description() + " ({0})".FormatWith((int)l)).ToList()));
// formula string length cannot be greater than 256
if (validateList.Length < 256)
{
// set formula by string
validation.Formula1 = validateList;
}
else
{
var columnName = GetExcelColumnName(col.Ordinal + 1);
// select info sheet
workbook.Sheet = 1;
// write list into info sheet
foreach (var item in list.Select((value, index) => new { value, index }))
{
workbook.setText(item.index + 1, col.Ordinal, item.value.Description() + " ({0})".FormatWith((int)item.value));
}
// select
workbook.Sheet = 0;
// set formula by selected range
validation.Formula1 = "Info!${0}$2:${0}${1}".FormatWith(columnName, list.Count);
}
// selection range
workbook.setSelection(fromRow, col.Ordinal, toRow, col.Ordinal);
workbook.DataValidation = validation;
}
/// <summary>
/// Tạo nhiều drop down tại 1 cột
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="workbook"></param>
/// <param name="serverPath">đường dẫn file</param>
/// <param name="colNum">vị trí cột</param>
/// <param name="fromRow">từ dòng</param>
/// <param name="toRow">đến dòng</param>
/// <param name="items">danh sách</param>
/// <param name="showField">trường hiển thị</param>
public static void CreateCustomDropDown <T>(WorkBook workbook, string serverPath, int colNum, int fromRow, int toRow, List <T> items, string showField)
{
try
{
var validation = workbook.CreateDataValidation();
validation.Type = DataValidation.eUser;
//
var validateString = string.Empty;
validateString += "\"{0}\"".FormatWith(string.Join(",", items.Select(item => item.GetType().GetProperty(showField)?.GetValue(item))));
// formula string length cannot be greater than 256
if (validateString.Length < 256)
{
// set formula by string
validation.Formula1 = validateString;
}
else
{
// string to list
var validateList = validateString.Trim('"').Split(',');
// select info sheet
workbook.Sheet = 1;
// write list into info sheet
foreach (var item in validateList.Select((value, index) => new { value, index }))
{
workbook.setText(item.index + 1, colNum, item.value);
}
// select back to sheet 0
workbook.Sheet = 0;
// set formula by selected range
validation.Formula1 = "Info!${0}$2:${0}${1}".FormatWith((colNum + 1).ToExcelColumnName(), validateList.Length + 1);
}
// selection range
workbook.setSelection(fromRow, colNum, toRow, colNum);
workbook.DataValidation = validation;
workbook.writeXLSX(serverPath);
}
catch (Exception e)
{
Debug.WriteLine(e);
throw;
}
}
private void CreateDropDownExcel(string obj, WorkBook workbook, DataColumn col)
{
var list = CatalogController.GetAll(obj, null, null, null, false, null, null);
if (list == null)
{
return;
}
var validation = workbook.CreateDataValidation();
validation.Type = DataValidation.eUser;
var validateList = "\"{0}\"".FormatWith(string.Join(",", list.Select(l => l.Name + " ({0})".FormatWith(l.Id)).ToList()));
// formula string length cannot be greater than 256
if (validateList.Length < 256)
{
// set formula by string
validation.Formula1 = validateList;
}
else
{
var columnName = GetExcelColumnName(col.Ordinal + 1);
// select info sheet
workbook.Sheet = 1;
// write list into info sheet
foreach (var item in list.Select((value, index) => new { value, index }))
{
workbook.setText(item.index + 1, col.Ordinal, item.value.Name + " ({0})".FormatWith(item.value.Id));
}
// select
workbook.Sheet = 0;
// set formula by selected range
validation.Formula1 = "Info!${0}$2:${0}${1}".FormatWith(columnName, list.Count);
}
// selection range
workbook.setSelection(2, col.Ordinal, 50, col.Ordinal);
// custom style selection
var range = workbook.getRangeStyle();
range.CustomFormat = "";
//workbook.setRangeStyle(range);
workbook.DataValidation = validation;
}
/// <summary>
///
/// </summary>
/// <param name="workbook"></param>
/// <param name="col"></param>
/// <param name="fromRow"></param>
/// <param name="toRow"></param>
private void CreateDropDownSerialNumberExcel(WorkBook workbook, DataColumn col, int fromRow, int toRow)
{
var list = TimeSheetMachineController.GetAll(null, null, null, null, null, null, null);
if (list == null)
{
return;
}
var validation = workbook.CreateDataValidation();
validation.Type = DataValidation.eUser;
var validateList = "\"{0}\"".FormatWith(string.Join(",", list.Select(l => l.SerialNumber + " ({0})".FormatWith(l.Id)).ToList()));
// formula string length cannot be greater than 256
if (validateList.Length < 256)
{
// set formula by string
validation.Formula1 = validateList;
}
else
{
var columnName = GetExcelColumnName(col.Ordinal + 1);
// select info sheet
workbook.Sheet = 1;
// write list into info sheet
foreach (var item in list.Select((value, index) => new { value, index }))
{
workbook.setText(item.index + 1, col.Ordinal, item.value.Name + " ({0})".FormatWith(item.value.Id));
}
// select
workbook.Sheet = 0;
// set formula by selected range
validation.Formula1 = "Info!${0}$2:${0}${1}".FormatWith(columnName, list.Count);
}
// selection range
workbook.setSelection(fromRow, col.Ordinal, toRow, col.Ordinal);
workbook.DataValidation = validation;
}
/// <summary>
///
/// </summary>
/// <param name="serverPath"></param>
/// <param name="dataTable"></param>
private void CreateExcelFile(string serverPath, DataTable dataTable)
{
var workbook = new WorkBook();
workbook.ImportDataTable(dataTable, false, 0, 0, dataTable.Rows.Count + 1, dataTable.Columns.Count + 1);
// set header style
var range = workbook.getRangeStyle();
range.VerticalAlignment = RangeStyle.VerticalAlignmentCenter;
range.HorizontalAlignment = RangeStyle.HorizontalAlignmentCenter;
range.FontBold = true;
range.FontSize = 11 * 20; // TODO : font size -> need move to param
workbook.setRangeStyle(range, 0, 0, 0, workbook.LastCol);
workbook.setSheetName(0, "Thêm mới thông tin hồ sơ");
workbook.insertSheets(1, 1);
workbook.setSheetName(1, "Info");
workbook.SheetHidden = WorkBook.SheetStateHidden;
workbook.Sheet = 0;
//WriteExcelFile(serverPath, dataTable, workbook);
// auto resize columns
for (var i = 0; i < workbook.LastCol; i++)
{
workbook.setColWidthAutoSize(i, true);
}
// hide prop name row
workbook.setRowHidden(1, true);
foreach (DataColumn col in dataTable.Columns)
{
switch (col.ColumnName)
{
case nameof(hr_Record.DepartmentId):
CreateDropDownExcel("cat_Department", workbook, col, 2, 50);
break;
case nameof(hr_Record.ManagementDepartmentId):
CreateDropDownExcel("cat_Department", workbook, col, 2, 50);
break;
case nameof(hr_Record.Sex):
var validation = workbook.CreateDataValidation();
validation.Type = DataValidation.eUser;
var validateList = ConstGender;
validation.Formula1 = validateList;
workbook.setSelection(2, col.Ordinal, 50, col.Ordinal);
workbook.DataValidation = validation;
break;
case nameof(hr_Record.MaritalStatusId):
CreateDropDownExcel("cat_MaritalStatus", workbook, col, 2, 50);
break;
case nameof(hr_Record.ReligionId):
CreateDropDownExcel("cat_Religion", workbook, col, 2, 50);
break;
case nameof(hr_Record.PersonalClassId):
CreateDropDownExcel("cat_PersonalClass", workbook, col, 2, 50);
break;
case nameof(hr_Record.FolkId):
CreateDropDownExcel("cat_Folk", workbook, col, 2, 50);
break;
case nameof(hr_Record.FamilyClassId):
CreateDropDownExcel("cat_FamilyClass", workbook, col, 2, 50);
break;
case nameof(hr_Record.PositionId):
CreateDropDownExcel("cat_Position", workbook, col, 2, 50);
break;
case nameof(hr_Record.JobTitleId):
CreateDropDownExcel("cat_JobTitle", workbook, col, 2, 50);
break;
case nameof(hr_Record.BasicEducationId):
CreateDropDownExcel("cat_BasicEducation", workbook, col, 2, 50);
break;
case nameof(hr_Record.EducationId):
CreateDropDownExcel("cat_Education", workbook, col, 2, 50);
break;
case nameof(hr_Record.PoliticLevelId):
CreateDropDownExcel("cat_PoliticLevel", workbook, col, 2, 50);
break;
case nameof(hr_Record.ManagementLevelId):
CreateDropDownExcel("cat_ManagementLevel", workbook, col, 2, 50);
break;
case nameof(hr_Record.LanguageLevelId):
CreateDropDownExcel("cat_LanguageLevel", workbook, col, 2, 50);
break;
case nameof(hr_Record.ITLevelId):
CreateDropDownExcel("cat_ITLevel", workbook, col, 2, 50);
break;
case nameof(hr_Record.CPVPositionId):
CreateDropDownExcel("cat_CPVPosition", workbook, col, 2, 50);
break;
case nameof(hr_Record.VYUPositionId):
CreateDropDownExcel("cat_VYUPosition", workbook, col, 2, 50);
break;
case nameof(hr_Record.ArmyLevelId):
CreateDropDownExcel("cat_ArmyLevel", workbook, col, 2, 50);
break;
case nameof(hr_Record.HealthStatusId):
CreateDropDownExcel("cat_HealthStatus", workbook, col, 2, 50);
break;
case nameof(hr_Record.FamilyPolicyId):
CreateDropDownExcel("cat_FamilyPolicy", workbook, col, 2, 50);
break;
case nameof(hr_Record.IDIssuePlaceId):
CreateDropDownExcel("cat_IDIssuePlace", workbook, col, 2, 50);
break;
//case nameof(hr_Record.WorkStatusId):
// CreateDropDownExcel("cat_WorkStatus", workbook, col, 2, 50);
// break;
case nameof(hr_Record.EmployeeTypeId):
CreateDropDownExcel("cat_EmployeeType", workbook, col, 2, 50);
break;
case nameof(hr_Record.IndustryId):
CreateDropDownExcel("cat_Industry", workbook, col, 2, 50);
break;
default:
break;
}
}
workbook.writeXLSX(serverPath);
}
/// <summary>
///
/// </summary>
/// <param name="typeName"></param>
/// <param name="workbook"></param>
/// <param name="colNum"></param>
/// <param name="fromRow"></param>
/// <param name="toRow"></param>
private static void CreateDropDownExcel(WorkBook workbook, int colNum, int fromRow, int toRow, string typeName)
{
// get type
var type = Type.GetType(typeName);
if (type == null)
{
return;
}
// create validation
var validation = workbook.CreateDataValidation();
validation.Type = DataValidation.eUser;
//
string validateString;
// check type enum
if (type.IsEnum)
{
// get list enum
var list = type.GetIntAndDescription();
validateString = $"\"{string.Join(",", list.Select(l => $"{l.Value} ({l.Key})"))}\"";
}
else
{
// get list catalog
var list = cat_BaseServices.GetAll(type.Name, null, null, null, false, null, null);
if (list == null || list.Count == 0)
{
return;
}
//
validateString = "\"{0}\"".FormatWith(string.Join(",", list.Select(l => l.Name + " ({0})".FormatWith(l.Id))));
}
// formula string length cannot be greater than 256
if (validateString.Length < 256)
{
// set formula by string
validation.Formula1 = validateString;
}
else
{
// string to list
var validateList = validateString.Trim('"').Split(',');
// select info sheet
workbook.Sheet = 1;
// write list into info sheet
foreach (var item in validateList.Select((value, index) => new { value, index }))
{
workbook.setText(item.index + 1, colNum, item.value);
}
// select back to sheet 0
workbook.Sheet = 0;
// set formula by selected range
validation.Formula1 = "Info!${0}$2:${0}${1}".FormatWith((colNum + 1).ToExcelColumnName(), validateList.Length);
}
// selection range
workbook.setSelection(fromRow, colNum, toRow, colNum);
workbook.DataValidation = validation;
}
protected void DownloadTemplate_Click(object sender, DirectEventArgs e)
{
try
{
var serverPath = Server.MapPath(relativePath + "/importHumanRecord.xlsx");
var workbook = new WorkBook();
var dataTable = new DataTable();
dataTable.Rows.Add();
dataTable.Rows.Add();
// create header by property name and description
foreach (var prop in typeof(hr_Record).GetProperties().Select((value, index) => new { value, index }))
{
dataTable.Columns.Add();
// get prop description
var attribute = prop.value.GetCustomAttribute(typeof(DescriptionAttribute));
if (attribute != null)
{
dataTable.Rows[0][prop.index] = ((DescriptionAttribute)attribute).Description;
}
dataTable.Rows[1][prop.index] = prop.value.Name;
// set column datatable name
dataTable.Columns[prop.index].ColumnName = prop.value.Name;
}
workbook.ImportDataTable(dataTable, false, 0, 0, dataTable.Rows.Count + 1, dataTable.Columns.Count + 1);
// set header style
RangeStyle range = workbook.getRangeStyle();
range.VerticalAlignment = RangeStyle.VerticalAlignmentCenter;
range.HorizontalAlignment = RangeStyle.HorizontalAlignmentCenter;
range.FontBold = true;
range.FontSize = 11 * 20;
workbook.setRangeStyle(range, 0, 0, 0, workbook.LastCol);
workbook.setSheetName(0, "Thêm mới thông tin hồ sơ");
workbook.insertSheets(1, 1);
workbook.setSheetName(1, "Info");
workbook.SheetHidden = WorkBook.SheetStateHidden;
workbook.Sheet = 0;
foreach (DataColumn col in dataTable.Columns)
{
switch (col.ColumnName)
{
case nameof(hr_Record.DepartmentId):
CreateDropDownExcel("cat_Department", workbook, col);
break;
case nameof(hr_Record.ManagementDepartmentId):
CreateDropDownExcel("cat_Department", workbook, col);
break;
case nameof(hr_Record.Sex):
var validation = workbook.CreateDataValidation();
validation.Type = DataValidation.eUser;
var validateList = "\"Nam,Nữ\"";
validation.Formula1 = validateList;
workbook.setSelection(2, col.Ordinal, 50, col.Ordinal);
workbook.DataValidation = validation;
break;
case nameof(hr_Record.MaritalStatusId):
CreateDropDownExcel("cat_MaritalStatus", workbook, col);
break;
case nameof(hr_Record.ReligionId):
CreateDropDownExcel("cat_Religion", workbook, col);
break;
case nameof(hr_Record.PersonalClassId):
CreateDropDownExcel("cat_PersonalClass", workbook, col);
break;
case nameof(hr_Record.FolkId):
CreateDropDownExcel("cat_Folk", workbook, col);
break;
case nameof(hr_Record.FamilyClassId):
CreateDropDownExcel("cat_FamilyClass", workbook, col);
break;
case nameof(hr_Record.PositionId):
CreateDropDownExcel("cat_Position", workbook, col);
break;
case nameof(hr_Record.JobTitleId):
CreateDropDownExcel("cat_JobTitle", workbook, col);
break;
case nameof(hr_Record.BasicEducationId):
CreateDropDownExcel("cat_BasicEducation", workbook, col);
break;
case nameof(hr_Record.EducationId):
CreateDropDownExcel("cat_Education", workbook, col);
break;
case nameof(hr_Record.PoliticLevelId):
CreateDropDownExcel("cat_PoliticLevel", workbook, col);
break;
case nameof(hr_Record.ManagementLevelId):
CreateDropDownExcel("cat_ManagementLevel", workbook, col);
break;
case nameof(hr_Record.LanguageLevelId):
CreateDropDownExcel("cat_LanguageLevel", workbook, col);
break;
case nameof(hr_Record.ITLevelId):
CreateDropDownExcel("cat_ITLevel", workbook, col);
break;
case nameof(hr_Record.CPVPositionId):
CreateDropDownExcel("cat_CPVPosition", workbook, col);
break;
case nameof(hr_Record.VYUPositionId):
CreateDropDownExcel("cat_VYUPosition", workbook, col);
break;
case nameof(hr_Record.ArmyLevelId):
CreateDropDownExcel("cat_ArmyLevel", workbook, col);
break;
case nameof(hr_Record.HealthStatusId):
CreateDropDownExcel("cat_HealthStatus", workbook, col);
break;
case nameof(hr_Record.FamilyPolicyId):
CreateDropDownExcel("cat_FamilyPolicy", workbook, col);
break;
case nameof(hr_Record.WorkStatusId):
CreateDropDownExcel("cat_WorkStatus", workbook, col);
break;
case nameof(hr_Record.IndustryId):
CreateDropDownExcel("cat_Industry", workbook, col);
break;
default:
break;
}
}
// auto resize columns
for (var i = 0; i < workbook.LastCol; i++)
{
workbook.setColWidthAutoSize(i, true);
}
// hide prop name row
workbook.setRowHidden(1, true);
workbook.writeXLSX(serverPath);
Response.AddHeader("Content-Disposition", "attachment; filename=" + "importHumanRecord.xlsx");
Response.WriteFile(serverPath);
Response.End();
}
catch (Exception ex)
{
Dialog.ShowError(ex.Message);
}
}