private string CreateXlsRptFuel(RptFuelByParamInput input) { //get data List <RptFuelDto> data = _rptFuelBLL.GetRptFuel(input); var listData = Mapper.Map <List <RptFuelItem> >(data); var slDocument = new SLDocument(); //title slDocument.SetCellValue(1, 1, "Fuel Report Data"); slDocument.MergeWorksheetCells(1, 1, 1, 19); //create style SLStyle valueStyle = slDocument.CreateStyle(); valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center); valueStyle.Font.Bold = true; valueStyle.Font.FontSize = 18; slDocument.SetCellStyle(1, 1, valueStyle); //create header slDocument = CreateHeaderExcelDashboard(slDocument); //create data slDocument = CreateDataExcelDashboard(slDocument, listData, input); var fileName = "RptFuel" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx"; var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName); slDocument.SaveAs(path); return(path); }
public List <FUEL_REPORT_DATA> GetRptFuel(RptFuelByParamInput filter) { Expression <Func <FUEL_REPORT_DATA, bool> > queryFilter = PredicateHelper.True <FUEL_REPORT_DATA>(); if (filter != null) { if (filter.MonthFrom > 0) { queryFilter = queryFilter.And(c => c.REPORT_MONTH >= filter.MonthFrom); } if (filter.YearFrom > 0) { queryFilter = queryFilter.And(c => c.REPORT_YEAR >= filter.YearFrom); } if (filter.MonthTo > 0) { queryFilter = queryFilter.And(c => c.REPORT_MONTH <= filter.MonthTo); } if (filter.YearTo > 0) { queryFilter = queryFilter.And(c => c.REPORT_YEAR <= filter.YearTo); } if (!string.IsNullOrEmpty(filter.VehicleType)) { queryFilter = queryFilter.And(c => c.VEHICLE_TYPE.ToUpper() == filter.VehicleType.ToUpper()); } if (!string.IsNullOrEmpty(filter.Function)) { queryFilter = queryFilter.And(c => c.FUNCTION.ToUpper() == filter.Function.ToUpper()); } if (!string.IsNullOrEmpty(filter.CostCenter)) { queryFilter = queryFilter.And(c => c.COST_CENTER.ToUpper() == filter.CostCenter.ToUpper()); } if (!string.IsNullOrEmpty(filter.PoliceNumber)) { queryFilter = queryFilter.And(c => c.POLICE_NUMBER.ToUpper() == filter.PoliceNumber.ToUpper()); } if (!string.IsNullOrEmpty(filter.Regional)) { queryFilter = queryFilter.And(c => c.REGIONAL.ToUpper() == filter.Regional.ToUpper()); } } return(_rptFuelRepository.Get(queryFilter, null, "").ToList()); }
private SLDocument CreateDataExcelDashboard(SLDocument slDocument, List <RptFuelItem> listData, RptFuelByParamInput input) { int iRow = 3; //starting row data var dataFuel = _rptFuelBLL.GetRptFuelData(); foreach (var data in listData) { if (input.MonthFrom == 1) { input.MonthFrom = 12; input.YearFrom = input.YearFrom - 1; } else { input.MonthFrom = input.MonthFrom - 1; } if (data.Odometer != 0) { var data_temp = dataFuel.Where(x => x.PoliceNumber == data.PoliceNumber && x.ReportMonth == input.MonthFrom && x.ReportYear == input.YearFrom).Select(x => x.Odometer).FirstOrDefault(); if (data_temp == 0) { data.Usage = data.Odometer; if (data.Liter != 0) { data.kmlt = Math.Round(data.Usage / data.Liter, 2); } } else { data.Usage = data.Odometer - data_temp; if (data.Liter != 0) { data.kmlt = Math.Round(data.Usage / data.Liter, 2); } } } slDocument.SetCellValue(iRow, 1, data.PoliceNumber); slDocument.SetCellValue(iRow, 2, data.Liter); slDocument.SetCellValue(iRow, 3, data.Odometer); slDocument.SetCellValue(iRow, 4, data.Usage); slDocument.SetCellValue(iRow, 5, data.kmlt); slDocument.SetCellValue(iRow, 6, data.Cost); slDocument.SetCellValue(iRow, 7, data.FuelType); slDocument.SetCellValue(iRow, 8, data.CostCenter); slDocument.SetCellValue(iRow, 9, data.Function); slDocument.SetCellValue(iRow, 10, data.Manufacturer); slDocument.SetCellValue(iRow, 11, data.Models); slDocument.SetCellValue(iRow, 12, data.Series); slDocument.SetCellValue(iRow, 13, data.BodyType); slDocument.SetCellValue(iRow, 14, data.VehicleType); slDocument.SetCellValue(iRow, 15, data.VehicleUsage); slDocument.SetCellValue(iRow, 16, data.Location); slDocument.SetCellValue(iRow, 17, data.Regional); slDocument.SetCellValue(iRow, 18, data.Month); slDocument.SetCellValue(iRow, 19, data.ReportYear); iRow++; } //create style SLStyle valueStyle = slDocument.CreateStyle(); valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin; valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin; valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin; valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin; slDocument.AutoFitColumn(1, 19); slDocument.SetCellStyle(3, 1, iRow - 1, 19, valueStyle); return(slDocument); }
public List <RptFuelDto> GetRptFuel(RptFuelByParamInput filter) { var data = _RptFuelService.GetRptFuel(filter); return(Mapper.Map <List <RptFuelDto> >(data)); }