public static WeekOrderDto GetMapDto(IUnitOfWorkAsync unitOfWork, WeekYearDto wyDto, bool needfact = true) { ApplicationDbContext context = unitOfWork.GetContext(); MenuForWeek menuForWeek = unitOfWork.RepositoryAsync <MenuForWeek>().GetWeekMenuByWeekYear(wyDto); if (menuForWeek == null) { return(null); } List <WeekOrderMenu> weekOrderMenus = unitOfWork.RepositoryAsync <WeekOrderMenu>() .OrdersMenuByWeekYear(wyDto).Where(word => word.User.IsExisting).OrderBy(wo => wo.User.LastName).ToList(); return(new WeekOrderDto { WeekYearDto = wyDto, SuCanChangeOrder = menuForWeek.SUCanChangeOrder, UserWeekOrders = weekOrderMenus.Select(woDto => UserWeekOrderDto.MapDto(context, woDto)).ToList(), DayNames = context.GetDayNames(wyDto, true).Result, WeekDishPrices = context.GetWeekDishPrices(wyDto).Result, SummaryDishQuantities = context.GetFactSumWeekUserCounts(wyDto).Result, AllDayNames = context.GetDayNames(wyDto).Result }); }
public static string GetFactOrdersExcelFileWeekYearDto(this IRepositoryAsync <WeekOrderMenu> repository, ForExcelDataDto feDto) { string[] dishCategories = MapHelper.GetCategoriesStrings(repository.Context); List <WeekOrderMenu> weekOrderMenus = repository.OrdersMenuByWeekYear(feDto.WeekYear); List <UserWeekOrderDto> userWeekOrders = weekOrderMenus.Select(woDto => UserWeekOrderDto.MapDto(repository.Context, woDto)).ToList(); string[] dayNames = repository.Context.GetDayNames(feDto.WeekYear, true).Result; double[] weekDishPrices = repository.Context.GetWeekDishPrices(feDto.WeekYear).Result; double[] summaryDishQuantities = repository.Context.GetFactSumWeekUserCounts(feDto.WeekYear).Result; WorkingWeek workWeek = repository.GetRepositoryAsync <MenuForWeek>().WorkWeekByWeekYear(feDto.WeekYear); int workDayCount = workWeek.WorkingDays.Count(wd => wd.IsWorking); int catLength = repository.GetRepositoryAsync <DishType>().GetAll().Count; int dishcount = workDayCount * catLength; int orderscount = userWeekOrders.Count; // Create new Spreadsheet Spreadsheet document = new Spreadsheet(); //document.Workbook.Worksheets.DeleteAll(); // Get worksheet by name Worksheet worksheet = document.Workbook.Worksheets.Add("Заявки фактические"); string titlerang = String.Format("A1:{0}1", GetExcelColumnName(dishcount + 3)); Range range = worksheet.Range(titlerang); range.Merge(); worksheet.Cell("A1").MergedWithCell.Value = "Заявки фактические " + feDto.DataString; range.AlignmentHorizontal = AlignmentHorizontal.Centered; worksheet.Cell(2, 0).Value = "№"; worksheet.Range("A2:A5").Merge(); worksheet.Cell(2, 1).Value = "Ф.И.О."; worksheet.Range("B2:B5").Merge(); worksheet.Range("B2:B5").AlignmentHorizontal = AlignmentHorizontal.Centered; string str; string colname; string colname_2; string endcolname = GetExcelColumnName(dishcount + 3); string allstr = string.Format("A{0}:{2}{1}", 1, userWeekOrders.Count + 6, endcolname); colname = GetExcelColumnName(dishcount + 2); worksheet.Cell(2 + 1, 3).Value = "Цена за одну порцию, грн"; str = String.Format("C4:{0}4", colname); worksheet.Range(str).Merge(); worksheet.Range(str).AlignmentHorizontal = AlignmentHorizontal.Centered; int i = dishcount + 3; colname = GetExcelColumnName(i); worksheet.Cell(1, i - 1).Value = "Стоимость заказа за неделю"; str = String.Format("{0}2:{1}5", colname, colname); worksheet.Range(str).Merge(); worksheet.Range(str).Wrap = true; worksheet.Range(str).AlignmentHorizontal = AlignmentHorizontal.Centered; worksheet.Columns[i - 1].Width = 100; worksheet.Cell(1, i - 1).ShrinkToFit = true; i = userWeekOrders.Count + 5; worksheet.Cell(i, 0).Value = "Всего заказано"; str = String.Format("A{0}:B{1}", i + 1, i + 1); worksheet.Range("A2:B5").AlignmentHorizontal = AlignmentHorizontal.Right; worksheet.Range(str).Merge(); worksheet.Range(allstr).OuterBorderStyle = LineStyle.Medium; worksheet.Range(allstr).InnerBorderStyle = LineStyle.Medium; for (int j = 0; j < dishcount; j++) { worksheet.Cell(4, 2 + j).Value = weekDishPrices[j]; if ((j) % 4 != 0) { worksheet.Cell(4, 2 + j).LeftBorderStyle = LineStyle.Thin; } worksheet.Cell(4, 2 + j).RightBorderStyle = LineStyle.Thin; } i = 0; for (int[] j = { 0 }; j[0] < workDayCount; j[0]++) { colname = GetExcelColumnName(j[0] * catLength + 3); colname_2 = GetExcelColumnName(j[0] * catLength + 6); var elementAtOrDefault = workWeek.WorkingDays.Where(wd => wd.IsWorking).ElementAtOrDefault(j[0]); if (elementAtOrDefault != null) { worksheet.Cell(1, j[0] * catLength + 3).Value = elementAtOrDefault.DayOfWeek.Name; } str = String.Format("{0}2:{1}2", colname, colname_2); worksheet.Range(str).Merge(); } i = 2; for (int j = 0; j < workDayCount; j++) { for (int k = 0; k < catLength; k++) { colname = GetExcelColumnName(2 + 1 + j * catLength + k); worksheet.Cell(2, 2 + j * catLength + k).Value = dishCategories[k]; worksheet.Range(colname + "3").Rotation = 90; if ((k) % 4 != 0) { worksheet.Cell(2, 2 + j * catLength + k).LeftBorderStyle = LineStyle.Thin; } worksheet.Cell(2, 2 + j * catLength + k).RightBorderStyle = LineStyle.Thin; } } str = string.Format("A1:{0}5", GetExcelColumnName(dishcount + 3)); i = 5; Color contentColor = Color.FromArgb(224, 232, 241); Color nullColor = Color.FromArgb(6, 84, 156); for (int j = 0; j < userWeekOrders.Count; j++) { var itsevenrow = (i + j) % 2 != 0; UserWeekOrderDto userweekorder = userWeekOrders[j]; worksheet.Cell(i + j, 0).Value = j + 1; worksheet.Cell(i + j, 1).Value = userweekorder.UserName; if (itsevenrow) { worksheet.Cell(i + j, 0).FillPattern = PatternStyle.Solid; worksheet.Cell(i + j, 0).FillPatternForeColor = contentColor; worksheet.Cell(i + j, 1).FillPattern = PatternStyle.Solid; worksheet.Cell(i + j, 1).FillPatternForeColor = contentColor; } for (int k = 0; k < dishcount + 1; k++) { var celval = userweekorder.UserWeekOrderDishes[k]; if (celval != 0.00) { worksheet.Cell(i + j, k + 2).Value = celval; } if (itsevenrow) { worksheet.Cell(i + j, k + 2).FillPattern = PatternStyle.Solid; worksheet.Cell(i + j, k + 2).FillPatternForeColor = contentColor; } if ((k) % 4 != 0) { worksheet.Cell(i + j, k + 2).LeftBorderStyle = LineStyle.Thin; } worksheet.Cell(i + j, k + 2).RightBorderStyle = LineStyle.Thin; } } i = userWeekOrders.Count + 5; Color evcolor = Color.FromArgb(68, 240, 196); for (int j = 0; j < workDayCount; j++) { for (int k = 0; k < catLength; k++) { Cell curCell = worksheet.Cell(i, j * catLength + k + 2); curCell.Value = summaryDishQuantities[j * catLength + k]; if (j % 2 == 0) { curCell.FillPattern = PatternStyle.Solid; curCell.FillPatternForeColor = evcolor; } if ((k) % 4 != 0) { worksheet.Cell(i, j * catLength + k + 2).LeftBorderStyle = LineStyle.Thin; } worksheet.Cell(i, j * catLength + k + 2).RightBorderStyle = LineStyle.Thin; } } worksheet.Cell(i, dishcount + 2).Value = userWeekOrders.Sum(uo => uo.UserWeekOrderDishes[dishcount]); string headerstr = string.Format("C{0}:{2}{1}", 1, 3, endcolname); worksheet.Range(headerstr).AlignmentHorizontal = AlignmentHorizontal.Centered; string headerusnamesstr = string.Format("A{0}:B{1}", 1, 5); worksheet.Range(headerusnamesstr).AlignmentHorizontal = AlignmentHorizontal.Centered; worksheet.Range(headerusnamesstr).AlignmentVertical = AlignmentVertical.Centered; string usernames = string.Format("A{0}:B{1}", 6, userWeekOrders.Count + 5); worksheet.Range(usernames).AlignmentHorizontal = AlignmentHorizontal.Left; string userquantistr = string.Format("C{0}:{2}{1}", 5, userWeekOrders.Count + 6, endcolname); //worksheet.Range(userquantistr).NumberFormatString = "#.#"; worksheet.Range(userquantistr).AlignmentHorizontal = AlignmentHorizontal.Centered; string sumcol = string.Format("{0}{1}:{2}{3}", endcolname, 5, endcolname, userWeekOrders.Count + 6); worksheet.Range(sumcol).NumberFormatString = "#,##0.00"; worksheet.Range(sumcol).AlignmentHorizontal = AlignmentHorizontal.Centered; for (int j = 0; j < userWeekOrders.Count + 6; j++) { worksheet.Rows[j].Height = (uint)((j != 2) ? 35 : 150); worksheet.Rows[j].AlignmentVertical = AlignmentVertical.Centered; } worksheet.Range(allstr).Font = new Font("Arial", 13, FontStyle.Bold); worksheet.Columns[0].Width = 40; worksheet.Columns[1].Width = 250; //worksheet.Columns[1].AutoFit(); //string _path = AppDomain.CurrentDomain.BaseDirectory.Replace(@"UnitTestProject1\bin\Debug", "") + // @"ACSDining.Web\ExcelFiles\ЗаявкиФакт.xls"; string pathstr = string.Format("~/ExcelFiles/ЗаявкиФакт_{0}.xls", YearWeekHelp.GetWeekTitle(repository.GetRepositoryAsync <MenuForWeek>(), feDto.WeekYear)); string _path = HostingEnvironment.MapPath(pathstr); if (File.Exists(_path)) { File.Delete(_path); } document.SaveAs(_path); // Close document document.Close(); return(_path); }