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));
}