static public FileContentResult Export_to_Excel_TERperMonthperTonne(ReportInfo reportInfo) { var exportToExcel = new ExcelExport(); var RecordsToShow_perMonths = reportInfo.Records_List["TERperMonthperTonne"].Select(r => (TERperMonthperTonne)r); var RecordsToShow_cycles = reportInfo.Records_List["TERperCycleperTonne"].Select(r => (TERperCycleperTonne)r); var RecordsToShow_perMonths_Plan = reportInfo.Records_List["TER_Plan"].Select(r => (TERperMonthperTonne)r); string rep_name = reportInfo.ReportName.Rus; var start_Date = reportInfo.ReportDates.StartDate; var end_Date = reportInfo.ReportDates.EndDate; int tesYear = reportInfo.ReportDates.TesYear; var baseStrings = reportInfo.BaseStrings; bool isCurrYear = tesYear == DateTime.Now.Year; //добавляем в файл выгрузки головную часть файла exportToExcel.Add_head_reportName( rep_name , start_Date , end_Date); string[] list_months = new string[] { }; exportToExcel.Add_head(new string[] { " ", " " }); exportToExcel.Sheet.Row(exportToExcel.Row).Height = 22; exportToExcel.Add_head(new string[] { "Текущие показатели" }, col_adder: 6); exportToExcel.Add_head(new string[] { isCurrYear?"Показатели с начала месяца": @CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(12) }, col_adder: 4); int monthToShow = isCurrYear ? DateTime.Now.Month : 12; for (int i = monthToShow - 1; i > 0; i--) { exportToExcel.Add_head(new string[] { @CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(i) }, col_adder: 4); } exportToExcel.Add_head(new string[] { "Итого средняя за год(истекший период)" }, col_adder: 4); exportToExcel.Add_head(new string[] { $"Бюджет {tesYear} года (исходные табличные данные)" }, col_adder: 4); exportToExcel.Add_head(new string[] { "Отклонение от бюджета" }, col_adder: 4); exportToExcel.Col = 1; exportToExcel.Row++; exportToExcel.Sheet.Row(exportToExcel.Row).Height = 48.5; exportToExcel.Add_head(new string[] { "Передел" }, width: 13); exportToExcel.Add_head(new string[] { "Номенклатура" }, width: 55); exportToExcel.Add_head(new string[] { "Начало цикла производства" }, width: 19); exportToExcel.Add_head(new string[] { "Конец цикла производства" }, width: 19); for (int i = monthToShow + 4; i > 0; i--) { exportToExcel.Add_head(new string[] { "Объем БО тонн" }, width: 15); exportToExcel.Add_head(new string[] { "Средняя скорость м/мин" }, width: 15); exportToExcel.Add_head(new string[] { "Энергоемкость МВт*ч/тонна" }, width: 15); exportToExcel.Add_head(new string[] { "Потребление газа тыс м3 /тонна" }, width: 15); exportToExcel.Add_head(new string[] { "Потребление пара ГКал/тонна" }, width: 15); } int report_body_start_col = 1; int report_body_months_start_col = 10; int report_body_start_row = 7; int count_of_col_per_sku = 5; int curr_sku_row = report_body_start_row; int curr_max_row = report_body_start_row; int month_for_total = 13; exportToExcel.Row = report_body_start_row; foreach (var item in RecordsToShow_perMonths.Select(r => new { r.SKU, r.PlaceName, r.PlaceID }).Distinct()) { exportToExcel.Col = report_body_start_col; exportToExcel.Row = curr_max_row; exportToExcel.Row++; exportToExcel.AddExcelCell(item.PlaceName, width: 13); exportToExcel.AddExcelCell(item.SKU, width: 55); curr_sku_row = exportToExcel.Row; exportToExcel.Row--; var cycles_per_sku = RecordsToShow_cycles.Where(r => r.SKU == item.SKU && r.PlaceID == item.PlaceID); if (cycles_per_sku.Count() > 0) { foreach (var curr_cycle in cycles_per_sku) { exportToExcel.Row++; exportToExcel.Col = report_body_start_col + 2; curr_max_row++; Add_excel_data_per_sku_per_cycles(exportToExcel, curr_cycle); } } else { exportToExcel.Row++; exportToExcel.Col = report_body_start_col + 2; curr_max_row++; for (int i = 0; i < count_of_col_per_sku + 2; i++) { exportToExcel.AddExcelCell("Нет данных", width: 15); } } //Возвращаемся на позицию для прорисовки данных по месяцам и итогам exportToExcel.Col = report_body_months_start_col; exportToExcel.Row = curr_sku_row; for (int curr_month = monthToShow; curr_month > 0; curr_month--) { //Получем строку с параметрами за нужный нам месяц по SKU var curr_record = RecordsToShow_perMonths.Where(r => r.SKU == item.SKU && r.PlaceID == item.PlaceID && r.Month == curr_month).FirstOrDefault(); Add_excel_data_per_sku_per_month(exportToExcel, curr_record, count_of_col_per_sku); } //итог хранится в 13 месяце //Получем строку с параметрами за нужный нам месяц по SKU var for_total_record = RecordsToShow_perMonths.Where(r => r.SKU == item.SKU && r.PlaceID == item.PlaceID && r.Month == month_for_total).FirstOrDefault(); Add_excel_data_per_sku_per_month(exportToExcel, for_total_record, count_of_col_per_sku); //итог хранится в 13 месяце //Получем строку с параметрами за нужный нам месяц по Plan var for_total_record_plan = RecordsToShow_perMonths_Plan.Where(r => r.SKU == item.SKU && r.PlaceID == item.PlaceID && r.Month <= month_for_total).GroupBy(r => new { r.PlaceID, r.SKU, r.PlaceName }, (g, r) => new TER_SKU_cells { ValueOfBO = r.Sum(e => e.ValueOfBO), AverageSpeed = r.Average(e => e.AverageSpeed), EnegryConsumptionperTonne = r.Average(e => e.EnegryConsumptionperTonne), GasConsumptionperTonne = r.Average(e => e.GasConsumptionperTonne), PlaceName = g.PlaceName, SteamConsumptionperTonne = r.Average(e => e.SteamConsumptionperTonne) }).FirstOrDefault(); Add_excel_data_per_sku_per_month(exportToExcel, for_total_record_plan, count_of_col_per_sku); if (for_total_record_plan != null) { var diff_total_period_record = new TERperMonthperTonne(); diff_total_period_record.ValueOfBO = Math.Round(for_total_record_plan.ValueOfBO - for_total_record.ValueOfBO, 3); diff_total_period_record.AverageSpeed = Math.Round(for_total_record_plan.AverageSpeed - for_total_record.AverageSpeed, 3); diff_total_period_record.EnegryConsumptionperTonne = Math.Round(for_total_record_plan.EnegryConsumptionperTonne - for_total_record.EnegryConsumptionperTonne, 3); diff_total_period_record.GasConsumptionperTonne = Math.Round(for_total_record_plan.GasConsumptionperTonne - for_total_record.GasConsumptionperTonne, 3); diff_total_period_record.SteamConsumptionperTonne = Math.Round(for_total_record_plan.SteamConsumptionperTonne - for_total_record.SteamConsumptionperTonne, 3); Add_excel_data_per_sku_per_month(exportToExcel, diff_total_period_record, count_of_col_per_sku); } } //Фиксируем первые колонки exportToExcel.Sheet.View.FreezePanes(1, 3); //Добавляем графики if (baseStrings != null) { int curr_string_index = 0; int Images_part_start_row = exportToExcel.Row + 5; int image_incr_row = 15; int image_incr_col = 4; exportToExcel.Row = Images_part_start_row; exportToExcel.Row = exportToExcel.Row + image_incr_row % 2 + 1; foreach (var item in RecordsToShow_perMonths.Select(r => new { r.SKU, r.PlaceName }).Distinct()) { exportToExcel.Col = report_body_start_col; exportToExcel.AddExcelCell(item.PlaceName, width: 13); exportToExcel.AddExcelCell(item.SKU, width: 55); exportToExcel.Row = exportToExcel.Row + image_incr_row; } exportToExcel.Row = Images_part_start_row; exportToExcel.Col = 2; foreach (var item in baseStrings) { exportToExcel.AddImageToExcel(StaticMethods.Base64StringToImage(baseStrings[curr_string_index].Split(",")[1]), 300, 300); exportToExcel.Col = exportToExcel.Col + image_incr_col; curr_string_index++; if ((curr_string_index) % count_of_col_per_sku == 0) { exportToExcel.Col = 2; exportToExcel.Row = exportToExcel.Row + image_incr_row; } } } //Сохраняем эксель в поток exportToExcel.Save_excel_file_to_Stream(); //Сохраняем ошибки if (reportInfo.Curr_controller != null) { reportInfo.Curr_controller.ViewBag.Excel_export_message_list = exportToExcel.MessageList; //Сохраняем производительность //((Dictionary<string, string>)curr_controller.ViewData["PerfTimes"]).Add("excel_export", StaticMethods.PerfTime(exportToExcel.StartTime)); } //Возвращаем экселевский файл браузеру return(Excel_file_result(exportToExcel, rep_name)); }
static public FileContentResult Export_to_Excel_SkuDataByShifts(ReportInfo reportInfo) { ExcelExport exportToExcel = new ExcelExport(); string rep_name = reportInfo.ReportName.Rus; DateTime start_Date = reportInfo.ReportDates.StartDate; DateTime end_Date = reportInfo.ReportDates.EndDate; string placeName = reportInfo.PlaceName; Controller curr_controller = reportInfo.Curr_controller; string[] baseStrings = reportInfo.BaseStrings; List <SkuDataByShifts> records = reportInfo.Records_List["SkuDataByShifts"] .Select(r => (SkuDataByShifts)r) .Where(r => reportInfo.ReportDates.CurrShiftStart <= r.CycleDateBegin && r.CycleDateEnd <= reportInfo.ReportDates.CurrShiftEnd) .ToList(); List <string> ter_list_shifts = Constants.ter_list_shifts; int shiftId = records.FirstOrDefault().ShiftId; string machinist_name = records.FirstOrDefault().Machinist_name; var head_report_lines = new List <string> { $"Отчёт за смену № {shiftId.ToString()}", $"Дата: {start_Date.ToShortDateString()} - {end_Date.ToShortDateString()}", $"Время: с {start_Date.ToShortTimeString()} до {end_Date.ToShortTimeString()}", $"Передел: {placeName}", $"Машинист: {machinist_name}", $"" }; //exportToExcel.Sheet.Row(exportToExcel.Row).Height = 22; exportToExcel.Sheet.Column(1).Width = 50; //Размер первой колонки //Фиксируем первые колонки exportToExcel.Sheet.View.FreezePanes(1, 2); foreach (var rl in head_report_lines) { exportToExcel.AddExcelCell(rl, outline: false, horizAlign: false); exportToExcel.New_line(); } int curr_string_index = 0; int ter_start_col = 1; foreach (var data in records) { exportToExcel.Col = ter_start_col; exportToExcel.AddExcelCell($"Наименование продукции: {data.Sku}"); exportToExcel.Row++; exportToExcel.Col--; var row_shift = 0; exportToExcel.AddExcelCell($"Жидкий поток: {data.FluidStream}"); exportToExcel.Row++; exportToExcel.Col--; row_shift--; exportToExcel.AddExcelCell($"Средняя влажность: {data.Wetness}"); exportToExcel.Row++; exportToExcel.Col--; row_shift--; exportToExcel.AddExcelCell($"Начало цикла: {data.CycleDateBegin}"); exportToExcel.Row++; exportToExcel.Col--; row_shift--; exportToExcel.AddExcelCell($"Конец цикла: {data.CycleDateEnd}"); row_shift--; exportToExcel.IncreaseRow(row_shift); //смещение графика относительно данных по циклу //Рисуем название графика и сам график int image_incr_row = 12; //int image_incr_col = 9; if (baseStrings != null && baseStrings.Length > 0) //если есть рисунки то выгружаем их { foreach (var ter in ter_list_shifts) { exportToExcel.AddExcelCell(ter, width: 50); exportToExcel.Row++; exportToExcel.IncreaseCol(-2); exportToExcel.AddImageToExcel(StaticMethods.Base64StringToImage(baseStrings[curr_string_index].Split(",")[1]), 300, 200); exportToExcel.Row--; exportToExcel.IncreaseCol(2); curr_string_index++; } } exportToExcel.IncreaseRow(image_incr_row); } //Сохраняем эксель в поток exportToExcel.Save_excel_file_to_Stream(); //Сохраняем ошибки if (curr_controller != null) { curr_controller.ViewBag.Excel_export_message_list = exportToExcel.MessageList; //Сохраняем производительность //((Dictionary<string, string>)curr_controller.ViewData["PerfTimes"]).Add("excel_export", StaticMethods.PerfTime(exportToExcel.StartTime)); } //Возвращаем экселевский файл браузеру return(Excel_file_result(exportToExcel, rep_name)); }