//В эксельку кто когда сваливает с депо public async Task <ReportResponse> EscapeFromDepoReport(ExcelDataRequest input) { var sqlRPlaneTrain = new PlanedRouteTrainsRepository(_logger); var sqlRRoute = new RoutesRepository(_logger); var sqlRTrain = new TrainRepository(_logger); var sqlRTrip = new TripsRepository(_logger); var sqlRUser = new UserRepository(_logger); var sqlRPlaneStation = new PlanedStationOnTripsRepository(_logger); var sqlRPlaneBrigade = new PlaneBrigadeTrainsRepository(_logger); if (input.Date == null) { throw new ValidationException("Не задан StartDate"); } var result = new ReportResponse { Rows = new List <Row>() }; result.Columns = new List <Column> { new Column("col0", "Маршрут", "string"), //№ поезда(Походу рейс https://alcodevelop.atlassian.net/browse/CPPK-4) new Column("col1", "№ поезда", "string"), new Column("col2", "Машинист", "string"), new Column("col3", "Состав", "string"), new Column("col4", "КП", "string"), }; //Бля ну получим все поезда наверно var planeTrains = await sqlRPlaneTrain.GetAll(); //За указанные сутки planeTrains = planeTrains.Where(x => x.Date.Date == input.Date.Date).ToList(); foreach (var planeTrain in planeTrains) { var route = await sqlRRoute.ById(planeTrain.RouteId); var train = await sqlRTrain.ById(planeTrain.TrainId); //Берем все станки маршрута var planeStations = await sqlRPlaneStation.ByPlannedRouteTrainId(planeTrain.Id); var startStation = planeStations.OrderBy(x => x.OutTime).FirstOrDefault(); var trip = new Trip(); if (startStation != null) { trip = await sqlRTrip.ById(startStation.TripId); } var planeBrigades = await sqlRPlaneBrigade.ByPlanedRouteTrainId(planeTrain.Id); //TODO брать 1-го юзера и делать машинистом, както неправильно) хДД User motorman = null; if (planeBrigades.Count != 0) { motorman = await sqlRUser.ById(planeBrigades.First().UserId); } var toadd = new Row { Id = new RowId(DateTime.Now.Ticks.GetHashCode(), 2), HasItems = false.ToString(), ParentId = null, //Маршрут Col0 = route.Name, //№ поезда(Походу рейс https://alcodevelop.atlassian.net/browse/CPPK-4) Col1 = trip.Name, //Машинист Col2 = motorman?.Name, //Состав Col3 = train.Name, //КП Col4 = startStation?.OutTime.ToStringTimeOnly() }; result.Rows.Add(toadd); } return(result); }
public async Task <IActionResult> EscapeFromDepo([FromBody] ExcelDataRequest input) { if (input == null) { throw new ValidationException("Не распарсилось"); } var service = new ReportTableService(_logger, _mapper); var toExcel = await service.EscapeFromDepoReport(input); var depo = "МохнатаяКокаина"; //TODO хз хз... string sWebRootFolder = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location); string sFileName = $@"Отчет_выход_из_депо_{depo}_{input.Date.Date.ToStringDateOnly()}.xlsx"; string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName); FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); var memory = new MemoryStream(); using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write)) { //TODO Уточнить var workbook = new XSSFWorkbook(); ISheet excelSheet = workbook.CreateSheet($"Отчет_выход_из_депо_{depo}_{input.Date.Date.ToStringDateOnly()}"); //Наверно дефолтная ширина колонок //excelSheet.SetColumnWidth(3, 256 * 40); ICellStyle styleAlingCenter = workbook.CreateCellStyle(); styleAlingCenter.Alignment = HorizontalAlignment.Center; ICellStyle styleRedAlingCenter = workbook.CreateCellStyle(); styleRedAlingCenter.Alignment = HorizontalAlignment.Center; XSSFFont redFont = (XSSFFont)workbook.CreateFont(); redFont.Color = IndexedColors.Red.Index; styleRedAlingCenter.SetFont(redFont); IRow row = excelSheet.CreateRow(0); var cell = row.CreateCell(0); cell.CellStyle = styleAlingCenter; cell.SetCellValue($"Выход из депо {depo} на утро"); //cell.CellStyle.WrapText = true; excelSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 4)); //2 строчка заголовка row = excelSheet.CreateRow(1); cell = row.CreateCell(0); cell.SetCellValue($"{DateTime.Now.ToStringDateOnly()}г. ({GetStringDayOfWeek(DateTime.Now.DayOfWeek)})"); cell.CellStyle = styleRedAlingCenter; //cell.CellStyle.WrapText = true; excelSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 4)); //Заголовки для данных row = excelSheet.CreateRow(2); for (int i = 0; i < toExcel.Columns.Count; i++) { row.CreateCell(i).SetCellValue(toExcel.Columns[i].Title); } var firstDataRowIndex = 3; for (var index = 0; index < toExcel.Rows.Count; index++) { var deRow = toExcel.Rows[index]; row = excelSheet.CreateRow(index + firstDataRowIndex); row.CreateCell(0).SetCellValue(deRow.Col0); row.CreateCell(1).SetCellValue(deRow.Col1); row.CreateCell(2).SetCellValue(deRow.Col2); row.CreateCell(3).SetCellValue(deRow.Col3); row.CreateCell(4).SetCellValue(deRow.Col4); row.CreateCell(5).SetCellValue(deRow.Col5); row.CreateCell(6).SetCellValue(deRow.Col6); row.CreateCell(7).SetCellValue(deRow.Col7); row.CreateCell(8).SetCellValue(deRow.Col8); row.CreateCell(9).SetCellValue(deRow.Col9); row.CreateCell(10).SetCellValue(deRow.Col10); } //Ширина столбцов for (int i = 0; i < 11; i++) { excelSheet.SetColumnWidth(i, 5000); } workbook.Write(fs); } using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open)) { await stream.CopyToAsync(memory); } memory.Position = 0; return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName)); }