//System.Data.DataSet ds, string[] sheetName, string fileName, List<DropdownModelExcel> list,int[] DateArry
public static void ExportMultiSheetExcelExtend(ExcelPara Para)
{
try
{
using (XLWorkbook wb = new XLWorkbook())
{
for (int i = 0; i < Para.sheetName.Length; i++)
{
if (Para.ds.Tables[i].Rows.Count > 0)
{
Para.ds.Tables[i].TableName = Para.sheetName[i];
wb.Worksheets.Add(Para.ds.Tables[i]);
}
}
IXLWorksheet worksheet = null;;
if (Para.list != null)
{
foreach (DropdownModelExcel item in Para.list)
{
worksheet = wb.Worksheet(item.SheetShowDrop);
if (item.IsHideSheetDataDrop)
{
wb.Worksheet(item.SheetDataDrop).Hide();
}
var worksheet2 = wb.Worksheet(item.SheetDataDrop);
worksheet.Column(item.ColumnShowDropData).SetDataValidation().List(worksheet2.Range(item.RangeSheetData), true);
}
}
else
{
worksheet = wb.Worksheet(1);
}
if (Para.DateColumns.Length > 0)
{
for (int i = 0; i < Para.DateColumns.Length; i++)
{
worksheet.Column(Para.DateColumns[i]).Style.DateFormat.Format = Para.DateFormat;
}
}
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + Para.fileName + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
}
catch (Exception ex) { }
}
/// <summary>
///
/// </summary>
/// <param name="UserID">Manager/DirectManagerID</param>
/// <param name="FD">FromDate</param>
/// <param name="TD">ToDate</param>
/// <param name="AID">Associate ID</param>
/// <returns></returns>
public string ExportExDailyTMS(int UserID, string FD, string TD, int AID)
{
DataSet ds = new DataSet();
ExternalDailyCollection collection = T_LMS_Trans_LeaveStoryManager.ExportExDailyTMS(UserID, FD, TD, AID);
DataTable dt = collection.ToDataTable <ExternalDaily>();
string fileName = AID + "_Monthly_TimeSheet_" + SystemConfig.CurrentDate.ToString("MM-dd-yyyy");
string[] RemoveColumn = { "CompanyID", "TargetDisplayID", "ReturnDisplay", "TotalRecord", "CreatedUser", "CreatedDate", "ErrorMesssage" };
for (int i = 0; i < RemoveColumn.Length; i++)
{
if (dt.Columns.Contains(RemoveColumn[i]))
{
dt.Columns.Remove(RemoveColumn[i]);
}
}
if (collection.Count > 0)
{
ds.Tables.Add(dt);
int[] DateColumn = { 6, 7 };
string[] sheetName = { "Daily_TMS" };
ExcelPara mypara = new ExcelPara()
{
ds = ds,
sheetName = sheetName,
fileName = fileName,
DateColumns = DateColumn,
DateFormat = "h:mm:ss"
};
FileInputHelper.ExportMultiSheetExcelExtend(mypara);
}
else
{
FileInputHelper.ExportExcel(dt, fileName, "Monthly_TimeSheet", false);
}
//set Column Date Format
//
return(fileName);
}
public string ExportExcel()
{
MissCanTimePara b = new MissCanTimePara()
{
StartDate = DateTime.Now.AddDays(-31),
EndDate = DateTime.Now,
EmployeeCode = 0
};
MissingScanTimeCollection collection = ReportManager.GetExMonthlyReport(b);
DataTable dt = collection.ToDataTable <MissingScanTime>();
string fileName = "RBVHEmployee_" + SystemConfig.CurrentDate.ToString("MM-dd-yyyy");
string[] RemoveColumn = { "CompanyID", "TargetDisplayID", "ReturnDisplay", "TotalRecord", "CreatedUser", "CreatedDate" };
for (int i = 0; i < RemoveColumn.Length; i++)
{
if (dt.Columns.Contains(RemoveColumn[i]))
{
dt.Columns.Remove(RemoveColumn[i]);
}
}
DataSet ds = new DataSet();
ds.Tables.Add(dt);
//set Column Date Format
int[] DateColumn = { 8, 9, 10, 11 };
string[] sheetName = { "Daily_TMS" };
ExcelPara mypara = new ExcelPara()
{
ds = ds,
sheetName = sheetName,
fileName = fileName,
DateColumns = DateColumn,
DateFormat = "h:mm:ss"
};
FileInputHelper.ExportMultiSheetExcelExtend(mypara);
FileInputHelper.ExportExcel(dt, fileName, "RBVHEmployee List", false);
return(fileName);
}
public void ExportExcel(string searchprm)
{
DataSet ds = new DataSet();
DataTable mydata;
int RBVH = 10001;
//T_CMS_Master_ContractCollection collection = T_CMS_Master_ContractManager.GetAll(false);
SearchFilter mySearch = BindSearch(searchprm);
T_CMS_Master_ContractCollection collection = T_CMS_Master_ContractManager.Search(mySearch);
mydata = collection.ToDataTable <T_CMS_Master_Contract>();
string fileName = "Contract_" + SystemConfig.CurrentDate.ToString("MM-dd-yyyy");
string[] RemoveColumn = { "CompanyID", "TargetDisplayID", "ReturnDisplay", "TotalRecord",
"CreatedUser", "CreatedDate", "StatusID", "ApproverLevel", "ContractLevel", "SalutationID", "PositionID", "LocationID",
"PassportNo", "PassportDOI", "PassportPOI", "LabourBookNo", "LabourDOI",
"LabourPOI", "AnnualLeave", "EmpTypeID",
"EmpSubTypeID", "WorkPermitNo", "WorkPermitFrom", "WorkPermitTo",
"HomeGrossOffer", "HomeGrossOfferEffectiveFrom",
"HomeGrossOfferEffectiveTo", "RelocationallowanceCurrency", "Relocationallowance",
"GoabroadallowanceCurrency", "Goabroadallowance", "WaivingallowanceCurrency", "Waivingallowance","HostCountryCurrency",
"HostGrossOfferEffectiveFrom", "HostGrossOfferEffectiveTo", "Remarks", "CreatedBy", "ModifiedBy",
"ModifiedDate", "IsActive", "OriginalDate", "WorkFlowStatus", "HandPhone", "TempAddress", "WorkHoursID", "ContractTerm","ProbationsPeriod","IDPOI", "Fullname",
//"FirstName_EN","MiddleName_EN","LastName_EN","ContractNo","EmployeeCode",
"GradeID", "CMSOrg", "Mode", "DeptID", "ErrorMesssage" };
for (int i = 0; i < RemoveColumn.Length; i++)
{
if (mydata.Columns.Contains(RemoveColumn[i]))
{
mydata.Columns.Remove(RemoveColumn[i]);
}
}
ds.Tables.Add(mydata);
//DNH Position
T_COM_Master_PositionCollection deColection = T_COM_Master_PositionManager.GetAll();
mydata = deColection.ToDataTable <T_COM_Master_Position>();
ds.Tables.Add(mydata);
//DNH Grade
T_COM_Master_GradeCollection objGrades = T_COM_Master_GradeManager.GetAll();
mydata = objGrades.ToDataTable <T_COM_Master_Grade>();
ds.Tables.Add(mydata);
//DN Saluation
T_CMS_Master_SalutationCollection objSalutation = T_CMS_Master_SalutationManager.GetAll();
mydata = objSalutation.ToDataTable <T_CMS_Master_Salutation>();
ds.Tables.Add(mydata);
//DN Working Hours
T_CMS_Master_WorkHoursCollection objWK = T_CMS_Master_WorkHoursManager.GetAll();
//T_CMS_InterfaceLacviet_WorkingHoursCollection objWK = T_CMS_InterfaceLacviet_WorkingHoursManager.GetAll();
mydata = objWK.ToDataTable <T_CMS_Master_WorkHours>();
ds.Tables.Add(mydata);
//DN Working Contract Term
T_CMS_Static_ContractTermCollection objCT = T_CMS_Static_ContractTermManager.GetAll();
mydata = objCT.ToDataTable <T_CMS_Static_ContractTerm>();
ds.Tables.Add(mydata);
//DN Working Probation
T_CMS_Static_PeriodOfProbationCollection objPr = T_CMS_Static_PeriodOfProbationManager.GetAll();
objPr.Add(new T_CMS_Static_PeriodOfProbation()
{
ID = 100,
Period = 0,
});
mydata = objPr.ToDataTable <T_CMS_Static_PeriodOfProbation>();
ds.Tables.Add(mydata);
//DN Degree
T_COM_Master_DegreeCollection objDg = T_COM_Master_DegreeManager.GetAll();
mydata = objDg.ToDataTable <T_COM_Master_Degree>();
ds.Tables.Add(mydata);
//DN Working Location
T_COM_Master_LocationCollection objLocation = T_COM_Master_LocationManager.GetAllByEntityID(RBVH);
mydata = objLocation.ToDataTable <T_COM_Master_Location>();
ds.Tables.Add(mydata);
//DN Place of Issues
T_COM_Master_PlaceOfIssueCollection objPOI = T_COM_Master_PlaceOfIssueManager.GetAll();
mydata = objPOI.ToDataTable <T_COM_Master_PlaceOfIssue>();
ds.Tables.Add(mydata);
//DN Place of Issues
T_COm_Master_OrgCollection objDepartment = T_COm_Master_OrgManager.GetAll();
mydata = objDepartment.ToDataTable <T_COm_Master_Org>();
ds.Tables.Add(mydata);
// ds.Tables.Add(dt2);
//ds.Tables.Add(dt3);
List <string> groupCodeList = new List <string>();
string[] sheetName = { "Contract", "Position", "Grade", "Saluation", "Working Hours", "Contract Term", "Probation", "Degree", "Location", "Place Of Issue", "Department" };
List <DropdownModelExcel> listDrop = new List <DropdownModelExcel>()
{
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 11,
ColumnShowDropData = 2,
RangeSheetData = "B2:B" + (objDepartment.Count + 1),
IsHideSheetDataDrop = true
},
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 4,
ColumnShowDropData = 3,
RangeSheetData = "B2:B" + (objSalutation.Count + 1),
IsHideSheetDataDrop = true
},
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 3,
ColumnShowDropData = 10,
RangeSheetData = "B2:B" + (objGrades.Count + 1),
IsHideSheetDataDrop = true
},
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 2,
ColumnShowDropData = 11, //column 12
RangeSheetData = "B2:B" + (deColection.Count + 1),
IsHideSheetDataDrop = true
},//location
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 9,
ColumnShowDropData = 16, //column 16 -Location
RangeSheetData = "B2:B" + (objLocation.Count + 1),
IsHideSheetDataDrop = true
},
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 5,
ColumnShowDropData = 17, //column 16
RangeSheetData = "B2:B" + (objWK.Count + 1),
IsHideSheetDataDrop = true
},
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 6,
ColumnShowDropData = 18, //column 16
RangeSheetData = "B2:B" + (objCT.Count + 1),
IsHideSheetDataDrop = true
},
//propration Period
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 7,
ColumnShowDropData = 19, //column 16
RangeSheetData = "B2:B" + (objPr.Count + 1),
IsHideSheetDataDrop = true
},
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 8,
ColumnShowDropData = 25, //column 16
RangeSheetData = "B2:B" + (objDg.Count + 1),
IsHideSheetDataDrop = true
},
//ID Place of Birth
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 10,
ColumnShowDropData = 21, //column 16
RangeSheetData = "D2:D" + (objPOI.Count + 1),
IsHideSheetDataDrop = true
},
//ID Place of issues
new DropdownModelExcel()
{
SheetShowDrop = 1,
SheetDataDrop = 10,
ColumnShowDropData = 24, //column 16
RangeSheetData = "B2:B" + (objPOI.Count + 1),
IsHideSheetDataDrop = true
},
};
//set Column Date Format
int[] DateColumn = { 14, 15, 20, 23 };
ExcelPara mypara = new ExcelPara()
{
ds = ds,
sheetName = sheetName,
fileName = fileName,
list = listDrop,
DateColumns = DateColumn,
DateFormat = "dd-mmm-yyyy"
};
//FileInputHelper.ExportExcel(dt, fileName, "Contract List", false);
FileInputHelper.ExportMultiSheetExcelExtend(mypara);
//return fileName;
}
