public List <RefundReportDTO> GetListRefund(List <string> lstStoreIds, DateTime dFrom, DateTime dTo, int mode) { var result = new List <RefundReportDTO>(); //get business day var lstBusinessDays = _baseFactory.GetBusinessDays(dFrom, dTo, lstStoreIds, mode); if (lstBusinessDays != null && lstBusinessDays.Any()) { var dMin = lstBusinessDays.Min(ss => ss.DateFrom); var dMax = lstBusinessDays.Max(ss => ss.DateTo); using (var cxt = new NuWebContext()) { var query = (from r in cxt.R_Refund join rd in cxt.R_RefundDetail on r.Id equals rd.RefundId where r.CreatedDate >= dMin && r.CreatedDate <= dMax && lstStoreIds.Contains(r.StoreId) select new { r, rd }); if (query != null && query.Any()) { var lstHeader = query.Select(ss => ss.r).Distinct().ToList(); var lstRefundDetail = query.Select(ss => new RefundDetailReportDTO() { RefundId = ss.rd.RefundId, ItemId = ss.rd.ItemId, ItemType = ss.rd.ItemType, ItemName = ss.rd.ItemName, PriceValue = ss.rd.PriceValue, Qty = ss.rd.Qty, ServiceCharged = ss.rd.ServiceCharged, Tax = ss.rd.Tax, PromotionAmount = ss.rd.PromotionAmount, DiscountAmount = ss.rd.DiscountAmount }).ToList(); RefundReportDTO refund = null; foreach (var item in lstHeader) { refund = new RefundReportDTO(); refund.Id = item.Id; refund.BusinessDayId = item.BusinessDayId; refund.CreatedDate = item.CreatedDate; refund.CreatedUser = item.CreatedUser; refund.Description = item.Description; refund.StoreId = item.StoreId; refund.TotalRefund = item.TotalRefund; refund.Promotion = item.Promotion; refund.ServiceCharged = item.ServiceCharged; refund.Tax = item.Tax; refund.Discount = item.Discount; refund.OrderId = item.OrderId; refund.ReceiptDate = item.ReceiptDate; refund.ListDetails = lstRefundDetail.Where(ww => ww.RefundId == refund.Id).ToList(); result.Add(refund); } } } } return(result); }
public XLWorkbook ExportExcel_New(BaseReportModel model, List <StoreModels> lstStore) { string sheetName = "No_Sale_Detail_Report"; var wb = new XLWorkbook(); var ws = wb.Worksheets.Add(sheetName); string titleReport = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("No Sale Report"); CreateReportHeaderNew(ws, 8, model.FromDate, model.ToDate, titleReport.ToUpper()); // Get list business day var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode); // Get data use business day if (_lstBusDayAllStore == null || !_lstBusDayAllStore.Any()) { // Format header report ws.Range(1, 1, 4, 8).Style.Border.InsideBorder = XLBorderStyleValues.Thin; ws.Range(1, 1, 4, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; return(wb); } //model.FromDate = _lstBusDayAllStore.Min(oo => oo.DateFrom); //model.ToDate = _lstBusDayAllStore.Max(oo => oo.DateTo); var _lstBusDayIdAllStore = _lstBusDayAllStore.Select(ss => ss.Id).ToList(); // Get list data var listData = GetData_New(model.ListStores, _lstBusDayIdAllStore, model.Mode); if (listData == null || !listData.Any()) { // Format header report ws.Range(1, 1, 4, 8).Style.Border.InsideBorder = XLBorderStyleValues.Thin; ws.Range(1, 1, 4, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; return(wb); } int row = 5; string storeId = string.Empty, storeName = string.Empty; // Column Title ws.Cell("A" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Store")); ws.Cell("B" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Business Day")); ws.Cell("C" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Shift")); ws.Cell("D" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Drawer")); ws.Cell("E" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time")); ws.Cell("F" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Staff")); ws.Cell("G" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Reason")); ws.Cell("H" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Total")); ws.Range("A" + row + ":H" + row).Style.Font.SetBold(true); ws.Range("A" + row + ":H" + row).Style.Fill.BackgroundColor = XLColor.FromHtml(Commons.BgColorDataRow); row++; List <NoSaleDetailReportModels> listDataStore = new List <NoSaleDetailReportModels>(); for (int i = 0; i < lstStore.Count; i++) { listDataStore = new List <NoSaleDetailReportModels>(); storeId = lstStore[i].Id; storeName = lstStore[i].Name; #region // List Id of business day in a store var businessInStoreId = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId).Select(ss => ss.Id).ToList(); // Get list data depend on list business day in store if (businessInStoreId != null && businessInStoreId.Any()) { listDataStore = listData.Where(ww => businessInStoreId.Contains(ww.BusinessId)).ToList(); } if (listDataStore != null && listDataStore.Any()) { int startRowStore = row; ws.Cell("A" + row).SetValue(storeName); var businessInLstData = listDataStore.Select(s => s.BusinessId).Distinct().ToList(); var businessInfoInLstData = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId && businessInLstData.Contains(ww.Id)).OrderBy(o => o.DateFrom).ToList(); int total = 0; int countBusDay = 0; int countShift = 0; int countDrawer = 0; int countTime = 0; int countStaff = 0; foreach (var busDay in businessInfoInLstData) { int startRowBusDay = row; countBusDay = 0; // Set value for column Business day ws.Cell("B" + row).SetValue(busDay.DateFrom.ToString("MM/dd/yyyy HH:mm tt") + " - " + busDay.DateTo.ToString("MM/dd/yyyy HH:mm tt")); var listDataInBusDay = listData.Where(m => m.BusinessId == busDay.Id).OrderBy(m => m.StartedShift).ToList(); var listShift = listDataInBusDay .Select(s => new { ShiftId = s.ShiftId, startShift = s.StartedShift.Value, closeShift = s.ClosedShift.Value }).Distinct().ToList(); foreach (var shift in listShift) { int startRowShift = row; countShift = 0; // Set value for column Shift ws.Cell("C" + row).SetValue(shift.startShift.ToString("MM/dd/yyyy HH:mm tt") + " - " + shift.closeShift.ToString("MM/dd/yyyy HH:mm tt")); var listDataInShift = listDataInBusDay.Where(w => w.ShiftId == shift.ShiftId).OrderBy(w => w.DrawerName).ToList(); var listDrawer = listDataInShift.Select(s => new { DrawerId = s.DrawerId, DrawerName = s.DrawerName }).Distinct().ToList(); foreach (var drawer in listDrawer) { int startRowDrawer = row; countDrawer = 0; // Set value for column Drawer ws.Cell("D" + row).SetValue(drawer.DrawerName); var listDataInDrawer = listDataInShift.Where(w => w.DrawerId == drawer.DrawerId).OrderBy(o => o.CreatedDate).ToList(); var listTime = listDataInDrawer.Select(s => new { s.CreatedDate.Hour, s.CreatedDate.Minute }).Distinct().ToList(); foreach (var time in listTime) { int startRowTime = row; countTime = 0; // Set value for column Time ws.Cell("E" + row).SetValue(new DateTime(1, 1, 1, time.Hour, time.Minute, 0).ToString("t")); var listDataInTime = listDataInDrawer.Where(w => w.CreatedDate.Hour == time.Hour && w.CreatedDate.Minute == time.Minute).OrderBy(w => w.CreatedDate).ToList(); var listStaffID = listDataInTime.Select(s => s.CashierId).Distinct().ToList(); foreach (var staffID in listStaffID) { int startRowStaff = row; countStaff = 0; // Set value for column Staff ws.Cell("F" + row).SetValue(listDataInTime.Where(w => w.CashierId == staffID).Select(s => s.CashierName).FirstOrDefault()); var listReason = listDataInTime.Where(w => w.CashierId == staffID).OrderBy(w => w.CreatedDate).Select(o => o.Reason).ToList(); foreach (var reason in listReason) { total++; countBusDay++; countShift++; countDrawer++; countTime++; countStaff++; // Set value for column Reason ws.Cell("G" + row).SetValue(reason); row++; } ws.Range(startRowStaff, 6, startRowStaff + countStaff - 1, 6).Merge(); } ws.Range(startRowTime, 5, startRowTime + countTime - 1, 5).Merge(); } ws.Range(startRowDrawer, 4, startRowDrawer + countDrawer - 1, 4).Merge(); } ws.Range(startRowShift, 3, startRowShift + countShift - 1, 3).Merge(); } ws.Range(startRowBusDay, 2, startRowBusDay + countBusDay - 1, 2).Merge(); // Set value for column Total ws.Cell("H" + startRowBusDay).SetValue(countBusDay); ws.Range(startRowBusDay, 8, startRowBusDay + countBusDay - 1, 8).Merge(); } ws.Range(startRowStore, 1, startRowStore + total - 1, 1).Merge(); } } #endregion // Format Report ws.Range(5, 1, row - 1, 8).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Range(5, 1, row - 1, 8).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Range(1, 1, row - 1, 8).Style.Border.InsideBorder = XLBorderStyleValues.Thin; ws.Range(1, 1, row - 1, 8).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Range(1, 1, row - 1, 8).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000"); ws.Range(1, 1, row - 1, 8).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000"); ws.Columns().AdjustToContents(); ws.Column(1).Width = 20; ws.Column(1).Style.Alignment.WrapText = true; ws.Column(2).Width = 40; ws.Column(3).Width = 40; ws.Column(4).Width = 20; ws.Column(5).Width = 10; ws.Column(6).Width = 20; ws.Column(7).Width = 30; return(wb); }
public XLWorkbook ExportExcelCreditInvoice(BaseReportModel model, List <StoreModels> lstStore, int cashType) { string sheetName = "Credit_Invoice_Report"; var wb = new XLWorkbook(); var ws = wb.Worksheets.Add(sheetName); // Set header report CreateReportHeaderNew(ws, 5, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Credit Invoice Report")); ws.Range(1, 1, 4, 5).Style.Border.InsideBorder = XLBorderStyleValues.Thin; ws.Range(1, 1, 4, 5).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; //Get business day var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode); if (_lstBusDayAllStore == null || !_lstBusDayAllStore.Any()) { return(wb); } // Get data use business day model.FromDate = _lstBusDayAllStore.Min(oo => oo.DateFrom); model.ToDate = _lstBusDayAllStore.Max(oo => oo.DateTo); List <CreditInvoiceReportModels> lstCreditModel = GetDataReport(model, cashType); if (lstCreditModel == null || !lstCreditModel.Any()) { return(wb); } ws.Cell("A5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date"); ws.Cell("B5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time"); ws.Cell("C5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Invoice No."); ws.Cell("D5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Amount"); ws.Cell("E5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Remark"); //ws.Cell("F5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Amount"); //ws.Cell("G5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Reason"); //ws.Cell("H5").Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("User"); //set width column ws.Column("A").Width = 150; ws.Column("B").Width = 150; ws.Column("C").Width = 250; ws.Column("D").Width = 250; ws.Column("E").Width = 180; //ws.Column("F").Width = 20; //ws.Column("G").Width = 35; //ws.Column("H").Width = 15; ws.Range("A" + 5 + ":E" + 5).Style.Font.SetBold(true); ws.Range("A" + 5 + ":E" + 5).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#d9d9d9")); ws.Range(5, 1, 5, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; int index = 7; DateTime dFrom = DateTime.Now, dTo = DateTime.Now; string storeId = string.Empty; for (int i = 0; i < lstStore.Count(); i++) { storeId = lstStore[i].Id; var lstBusDayByStore = _lstBusDayAllStore.Where(ww => ww.StoreId == storeId).ToList(); if (lstBusDayByStore != null && lstBusDayByStore.Any()) { dFrom = lstBusDayByStore.Min(ss => ss.DateFrom); dTo = lstBusDayByStore.Max(ss => ss.DateTo); var lstCreditModelByStore = lstCreditModel.Where(ww => ww.StoreId == storeId && ww.CreatedDate >= dFrom && ww.CreatedDate <= dTo).ToList(); if (lstCreditModelByStore != null && lstCreditModelByStore.Any()) { ws.Range("A" + (index - 1) + ":E" + (index - 1)).Merge().SetValue(lstStore[i].Name); ws.Range("A" + (index - 1) + ":E" + (index - 1)).Style.Font.SetBold(true); ws.Range("A" + (index - 1) + ":E" + (index - 1)).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#d9d9d9")); for (int j = 0; j < lstCreditModelByStore.Count; j++) { ws.Range("A" + (index) + ":C" + (index)).Merge().SetValue(lstCreditModelByStore[j].SupplierName); ws.Range("A" + (index) + ":E" + (index)).Style.Font.SetBold(true); ws.Range("A" + (index) + ":E" + (index)).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#d9d9d9")); ws.Cell("D" + (index)).Value = "'" + lstCreditModelByStore[j].TotalAmout.ToString("F"); ws.Cell("D" + (index)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; foreach (var itemSupplier in lstCreditModelByStore[j].Item) { ws.Cell("A" + (index + 1)).Value = "'" + itemSupplier.CreatedDate.ToString("MM/dd/yyyy"); ws.Cell("B" + (index + 1)).Value = "'" + itemSupplier.CreatedDate.ToString("hh:mm tt"); ws.Cell("C" + (index + 1)).Value = itemSupplier.InvoiceNo; ws.Cell("D" + (index + 1)).Value = "'" + itemSupplier.Amount.ToString("F"); ws.Cell("E" + (index + 1)).Value = "'" + itemSupplier.Remark; ws.Cell("E" + (index + 1)).Style.Alignment.WrapText = true; ws.Range("A" + (index + 1) + ":C" + (index + 1)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; ws.Cell("D" + (index + 1)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Cell("C" + (index + 1)).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; index++; } index++; } index++; } } }//End loop store //ws.Range("F5" + ":F" + index).Style.NumberFormat.Format = "#,##0.00"; //set Border ws.Range("A5:E" + (index - 2)).Style.Border.InsideBorder = XLBorderStyleValues.Thin; ws.Range("A5:E" + (index - 2)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Columns().AdjustToContents(); return(wb); }
public XLWorkbook ExportExcelSummary(BaseReportModel model, List <StoreModels> lstStore) { string sheetName = "Time_Clock_Summary"; var wb = new XLWorkbook(); var ws = wb.Worksheets.Add(sheetName); var lstEmpChecked = model.ListEmployees.Where(m => m.Checked).ToList(); if (lstEmpChecked == null || !lstEmpChecked.Any()) { // Set header report CreateReportHeader(ws, 12, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report")); // Format header report ws.Range(1, 1, 3, 12).Style.Border.InsideBorder = XLBorderStyleValues.Thin; ws.Range(1, 1, 3, 12).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; return(wb); } List <string> lstUserID = lstEmpChecked.Select(m => m.Name).ToList(); model.ListStores = lstEmpChecked.Select(m => m.StoreId).ToList(); DateTimeHelper.GetDateTime(ref model); var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode); // When data null if (_lstBusDayAllStore == null || !_lstBusDayAllStore.Any()) { // Set header report CreateReportHeader(ws, 12, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report")); // Format header report ws.Range(1, 1, 3, 12).Style.Border.InsideBorder = XLBorderStyleValues.Thin; ws.Range(1, 1, 3, 12).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; return(wb); } var _lstBusDayIdAllStore = _lstBusDayAllStore.Select(ss => ss.Id).ToList(); var data = GetTimeClockSummary(model.ListStores, lstUserID, _lstBusDayIdAllStore, model.Mode); // When data null if (data == null || !data.Any()) { // Set header report CreateReportHeader(ws, 12, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report")); // Format header report ws.Range(1, 1, 3, 12).Style.Border.InsideBorder = XLBorderStyleValues.Thin; ws.Range(1, 1, 3, 12).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; return(wb); } //Table //row 1: Table Header CreateReportHeader(ws, 8, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report")); DateTime fromDate = model.FromDate; DateTime toDate = model.ToDate; int row = 4; int startRow = row; int startRow_Store = row; int endRow_Store = row; int startRow_User = row; int endRow_User = row; string storeId = string.Empty, storeName = string.Empty; lstStore = lstStore.Where(ww => model.ListStores.Contains(ww.Id)).OrderBy(oo => oo.Name).ToList(); for (int i = 0; i < lstStore.Count; i++) { storeId = lstStore[i].Id; storeName = lstStore[i].Name; var _lstBusDayIdStore = _lstBusDayAllStore.Where(x => x.StoreId == storeId).Select(ss => ss.Id).ToList(); if (_lstBusDayIdStore != null && _lstBusDayIdStore.Any()) { var dataStore = data.Where(x => x.StoreId == storeId && _lstBusDayIdStore.Contains(x.BusinessId)).OrderBy(oo => oo.DateTimeIn).ToList(); if (dataStore != null && dataStore.Any()) { startRow_Store = row; endRow_Store = row; row++; //Timer Header ws.Cell("A" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Employee")); ws.Cell("B" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date In")); ws.Cell("C" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time In")); ws.Cell("D" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date Out")); ws.Cell("E" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Out")); ws.Cell("F" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Hours Worked")); ws.Cell("G" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Late(Minutes)")); ws.Cell("H" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Early Leave(Minutes)")); ws.Range("A" + row + ":H" + row).Style.Font.SetBold(true); ws.Range("A" + row + ":H" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); row++; #region ======Get Employees List <RPEmployeeItemModels> lstUser = lstEmpChecked.Where(ww => ww.StoreId == storeId).Select(m => new RPEmployeeItemModels { ID = m.ID, Name = m.Name, StoreId = m.StoreId }).OrderBy(oo => oo.Name).ToList(); int frmDateOutMerge = 0, toDateOutMerge = 0; List <DateOutInfo> lstDateOuts = new List <DateOutInfo>(); foreach (var employee in lstUser) { var dataEmp = dataStore.Where(x => x.UserId == employee.ID).GroupBy(gg => gg.CreatedDate.Date).OrderBy(oo => oo.Key).ToList(); if (dataEmp != null && dataEmp.Any()) { startRow_User = row; endRow_User = row; #region ====Date double _sumHw = 0, _sumLate = 0, _sumEarly = 0; var _checkObj = new TimeClockReportModels(); foreach (var itmData in dataEmp) { int startRow_Date = row; int endRow_Date = row; DateTime _date = itmData.Key; var lstdateDateGroup = itmData.GroupBy(gg => gg.DateTimeIn).OrderBy(oo => oo.Key).ToList(); lstDateOuts = new List <DateOutInfo>(); frmDateOutMerge = row; toDateOutMerge = row; //loop data in date foreach (var itemGroup in lstdateDateGroup) { _checkObj = itemGroup.Where(ww => ww.DateTimeOut.Date != Commons.MinDate.Date).FirstOrDefault(); if (_checkObj == null) { _checkObj = itemGroup.FirstOrDefault(); } //Time In ws.Cell("C" + row).SetValue(DateTimeHelper.GetAMPM(_checkObj.DateTimeIn.TimeOfDay)); ws.Cell("C" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); //check merge time out if (_checkObj.DateTimeOut.Date != Commons.MinDate.Date) { var checkExist = lstDateOuts.Where(ww => ww.DateOut.Date == _checkObj.DateTimeOut.Date).FirstOrDefault(); if (checkExist == null) { var info = new DateOutInfo(); info.DateOut = _checkObj.DateTimeOut; info.FromRow = frmDateOutMerge; info.ToRow = toDateOutMerge; lstDateOuts.Add(info); frmDateOutMerge++; toDateOutMerge++; } else { checkExist.ToRow++; } //E time out ws.Cell("E" + row).SetValue(DateTimeHelper.GetAMPM(_checkObj.DateTimeOut.TimeOfDay)); ws.Cell("E" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); } else { frmDateOutMerge++; toDateOutMerge++; } var _hoursWorked = Math.Round(_checkObj.HoursWork, 2); // F hours work ws.Cell("F" + row).SetValue(_hoursWorked); ws.Cell("F" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); ws.Cell("F" + row).Style.NumberFormat.Format = "#,##0.00"; _sumHw += _hoursWorked; ws.Cell("G" + row).SetValue(Math.Round(_checkObj.Late, 2)); ws.Cell("G" + row).Style.NumberFormat.Format = "#,##0.00"; ws.Cell("G" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); _sumLate += Math.Round(_checkObj.Late, 2); ws.Cell("H" + row).SetValue(Math.Round(_checkObj.Early, 2)); ws.Cell("H" + row).Style.NumberFormat.Format = "#,##0.00"; ws.Cell("H" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); _sumEarly += Math.Round(_checkObj.Early, 2); //======================================================== row++; endRow_User++; endRow_Date++; //Date Out foreach (var item in lstDateOuts) { ws.Range("D" + item.FromRow + ":D" + item.ToRow).Merge().SetValue(item.DateOut.ToString("MM/dd/yyyy")); ws.Range("D" + item.FromRow + ":D" + item.ToRow).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); ws.Range("D" + item.FromRow + ":D" + item.ToRow).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); ws.Range("D" + item.FromRow + ":D" + item.ToRow).Style.Font.SetBold(true); } //============================================================================== //row++; //endRow_User++; //endRow_Date++; }//end dateDate.Count > 0 ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Merge().SetValue(_date.ToString("MM/dd/yyyy")); ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Style.Font.SetBold(true); ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); } #endregion //===> Total row++; endRow_User++; ws.Cell("B" + (row - 1)).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Total")); ws.Cell("B" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left); ws.Range("B" + (row - 1) + ":E" + (row - 1)).Merge(); ws.Cell("F" + (row - 1)).SetValue(_sumHw); ws.Cell("F" + (row - 1)).Style.NumberFormat.Format = "#,##0.00"; ws.Cell("F" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); ws.Cell("G" + (row - 1)).SetValue(_sumLate); ws.Cell("G" + (row - 1)).Style.NumberFormat.Format = "#,##0.00"; ws.Cell("G" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); ws.Cell("H" + (row - 1)).SetValue(_sumEarly); ws.Cell("H" + (row - 1)).Style.NumberFormat.Format = "#,##0.00"; ws.Cell("H" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); ws.Range("B" + (row - 1) + ":H" + (row - 1)).Style.Font.SetBold(true); ws.Range("B" + (row - 1) + ":H" + (row - 1)).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#d9d9d9")); //======================== ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Merge().SetValue(employee.Name); ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Style.Font.SetBold(true); ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); } } #endregion //Get StoreName ws.Range("A" + startRow_Store + ":H" + endRow_Store).Merge().SetValue(storeName); ws.Range("A" + startRow_Store + ":H" + endRow_Store).Style.Font.SetBold(true); ws.Range("A" + startRow_Store + ":H" + endRow_Store).Style.Fill.SetBackgroundColor(XLColor.FromHtml(Commons.BgColorStore)); } } } //Set Border ws.Range("A1:H" + (row - 1)).Style.Border.InsideBorder = XLBorderStyleValues.Thin; ws.Range("A1:H" + (row - 1)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Columns().AdjustToContents(); ws.Column(1).Width = 30; ws.Column(2).Width = 15; ws.Column(4).Width = 15; return(wb); }