-
-
Save ShilGen/41ffe14fee917943f0411a2fdf3c9396 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// параметры отчёта | |
var sheet = WorkbookNonExcel.Worksheets.FirstOrDefault(x => x.Name == "Приложение № 5.1"); | |
var sheet1 = WorkbookNonExcel.Worksheets.FirstOrDefault(x => x.Name == "Приложение № 5"); | |
ReportParams.StartDt = ReportParams.StartDt.AlignToDay(); | |
ReportParams.EndDt = ReportParams.EndDt.AlignToDay(); | |
DayIntervalData interval = new DayIntervalData() {StartDt = ReportParams.StartDt, EndDt = ReportParams.EndDt}; | |
sheet1.Cells[12, 2].Value = ReportParams.StartDt; | |
sheet1.Cells[12, 5].Value = ReportParams.StartDt; | |
MeterPoint[] meterPoints = ReportParams.ClassifierNodes.SelectMany(x => x.GetAllChildrenOfClass(MeterPoint.GetClassInfo())).OfType<MeterPoint>().Distinct().ToArray(); | |
//параметры таблицы | |
int startRowIndex = 12; | |
int sheet1StartRowIndex = 15; | |
int rowIndex = startRowIndex; | |
int SI_multiples = 1000; // для отображения значений в кВт | |
// Выводимыe параметры | |
Parameter[] parameters = new Parameter[2]; | |
parameters[0]= DirectionBasedParameter.Instances.EnergyActiveForward1hour; | |
parameters[1]= TariffZoneBasedParameter.Instances.EnergyActiveForwardTotalFixDay; | |
// вспомогательная функция, используется в цикле по дням | |
IEnumerable<DateTime> EachDay(DateTime from, DateTime thru) | |
{ | |
for(var day = from.Date; day.Date <= thru.Date; day = day.AddDays(1)) | |
yield return day; | |
} | |
using(PreloadManager.Current.RegisterCache(() => new MeterPointGetEnergyValueCache(meterPoints, parameters, interval))) | |
{ | |
//справочная информация | |
foreach(var meterPoint in meterPoints){ | |
var el = meterPoint.AttributeElectricityMeter; | |
var ns =WorkbookNonExcel.Worksheets.AddCopy(el.AttributeSerialNumber.ToString(),sheet); | |
var parents = meterPoint.GetUpperItems(); | |
var mmp = parents.OfType<SmallPowerConsumerBoundary>().FirstOrDefault(); | |
if (mmp != null){ | |
ns.Cells[5, 1].Value = mmp.ReadValueByAttributeCaption("Адрес"); | |
sheet1.Cells[9, 2].Value = mmp.ReadValueByAttributeCaption("Адрес"); | |
ns.Cells[3, 1].Value = (mmp.ReadValueByAttributeCaption("Инфраструктурная компания") != null) ? mmp.ReadValueByAttributeCaption("Инфраструктурная компания").ToString() : "н/д" ; | |
sheet1.Cells[6, 2].Value = (mmp.ReadValueByAttributeCaption("Инфраструктурная компания") != null) ? mmp.ReadValueByAttributeCaption("Инфраструктурная компания").ToString() : "н/д" ; | |
} | |
ns.Cells[6, 1].Value = meterPoint.AttributeElectricityMeter.AttributeSerialNumber; | |
sheet1.Cells[sheet1StartRowIndex, 1].Value = meterPoint.AttributeElectricityMeter.AttributeSerialNumber; | |
//получение данных | |
var data = meterPoint.GetMeterPointFinalData(parameters[0], interval).OrderBy(x => x.ValueDt); | |
var data_fix = meterPoint.GetMeterPointFinalData(parameters[1], interval).OrderBy(x => x.ValueDt); | |
sheet1.Cells[sheet1StartRowIndex, 2].Value = data_fix.First().Value / SI_multiples; | |
sheet1.Cells[sheet1StartRowIndex, 3].Value = data_fix.Last().Value / SI_multiples; | |
sheet1.Cells[sheet1StartRowIndex, 4].Value = 1; // к-т учёта | |
sheet1.Cells[sheet1StartRowIndex, 5].Value = 0; // % потерь | |
sheet1.Cells[sheet1StartRowIndex,6].Formula = "=IFERROR((D"+(sheet1StartRowIndex +1).ToString()+"-C"+(sheet1StartRowIndex +1).ToString()+")*E"+(sheet1StartRowIndex +1).ToString()+"*(1+F"+(sheet1StartRowIndex +1).ToString()+"),\"н/д\")"; | |
var dCells = sheet1.Cells.GetSubrangeAbsolute(15, 0, sheet1StartRowIndex, 6); | |
dCells.SetBorders(MultipleBorders.All, Color.Black, LineStyle.Thin); | |
rowIndex = 12; | |
// цикл по дням | |
foreach (DateTime day in EachDay(ReportParams.StartDt, ReportParams.EndDt.AddDays(-1))){ | |
DateTime line_day = day.AddHours(1); // начинаем с 1 го часа | |
ns.Cells[rowIndex, 0].Value = day.ToShortDateString(); | |
// цикл по часам | |
int num_of_hour = 1; | |
while (line_day <= day.AddDays(1)){ // цикл по часам | |
var h_data = data.Where(x => x.ValueDt == line_day); | |
if (h_data.Count() > 0) | |
{ | |
ns.Cells[rowIndex, num_of_hour].Value = h_data.Last().Value / SI_multiples; | |
} | |
num_of_hour++; | |
line_day = line_day.AddHours(1); | |
} // end of цикл по часам | |
//Итого за расчетный день (формула) | |
ns.Cells[rowIndex, 25].Formula = string.Format("=IFERROR((SUM(B{0}:Y{0})),\"н/д\")", rowIndex+1, rowIndex+1); | |
rowIndex++; | |
} //end of цикл по дням | |
//рисуем границы таблицы | |
var dataCells = ns.Cells.GetSubrangeAbsolute(startRowIndex, 0, rowIndex-1, 25); | |
dataCells.SetBorders(MultipleBorders.All, Color.Black, LineStyle.Thin); | |
rowIndex++; // пустая строка | |
ns.Cells[rowIndex, 3].Value = "Всего за месяц"; | |
ns.Cells[rowIndex, 5].Formula = string.Format("=IFERROR((SUM(Z{0}:Z{1})),\"н/д\")", startRowIndex+1, rowIndex-1); | |
sheet1StartRowIndex ++; | |
} //end of цикл по ty | |
} //end of PreloadManager | |
WorkbookNonExcel.Worksheets.Remove(1); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment