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