public async Task <Stream> OrderDrivingXLSFileStreamResult(ParamsCalculateKpi data)
{
var stream = new MemoryStream();
List <DeliveryRecord> _delivery = await _dataService.GetDeliveryRecordsAsync(data);
ExcelPackage package;
using (package = new ExcelPackage(stream))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("KPI");
using (var range = worksheet.Cells["A1:AH2"])
{
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
}
//First add the headers
worksheet.Cells["A1:A2"].Merge = true;
worksheet.Cells[1, 1].Value = @"№";
worksheet.Cells["B1:B2"].Merge = true;
worksheet.Cells[1, 2].Value = @"Клиент";
worksheet.Cells["C1:C2"].Merge = true;
worksheet.Cells[1, 3].Value = @"Грузополучатель";
worksheet.Cells["D1:D2"].Merge = true;
worksheet.Cells[1, 4].Value = @"Заказ";
worksheet.Cells["E1:E2"].Merge = true;
worksheet.Cells[1, 5].Value = @"Строка";
worksheet.Cells["F1:F2"].Merge = true;
worksheet.Cells[1, 6].Value = @"Дата заявки";
worksheet.Cells["G1:G2"].Merge = true;
worksheet.Cells[1, 7].Value = @"№ заявки продаж";
worksheet.Cells["H1:H2"].Merge = true;
worksheet.Cells[1, 8].Value = @"Заявка ДСТЛ";
worksheet.Cells["I1:I2"].Merge = true;
worksheet.Cells[1, 9].Value = @"Дата входа план";
worksheet.Cells["J1:J2"].Merge = true;
worksheet.Cells[1, 10].Value = @"Дата входа факт";
worksheet.Cells["K1:K2"].Merge = true;
worksheet.Cells[1, 11].Value = @"Дата входа склад план";
worksheet.Cells["L1:L2"].Merge = true;
worksheet.Cells[1, 12].Value = @"Дата входа склад факт";
worksheet.Cells["M1:M2"].Merge = true;
worksheet.Cells[1, 13].Value = @"Дата отгрузки план";
worksheet.Cells["N1:N2"].Merge = true;
worksheet.Cells[1, 14].Value = @"Дата отгрузки в ПЭ";
worksheet.Cells["O1:O2"].Merge = true;
worksheet.Cells[1, 15].Value = @"Дата отгрузки факт";
worksheet.Cells["P1:P2"].Merge = true;
worksheet.Cells[1, 16].Value = @"Дата доставки план";
worksheet.Cells["Q1:Q2"].Merge = true;
worksheet.Cells[1, 17].Value = @"Дата доставки в ПЭ";
worksheet.Cells["R1:R2"].Merge = true;
worksheet.Cells[1, 18].Value = @"Дата доставки факт";
worksheet.Cells["S1:T1"].Merge = true;
worksheet.Cells["S1:T1"].Value = @"Производство";
worksheet.Cells[2, 19].Value = @"Причина срыва в производстве";
worksheet.Cells[2, 20].Value = @"Количество дней";
worksheet.Cells["U1:V1"].Merge = true;
worksheet.Cells["U1:V1"].Value = "Отгрузка";
worksheet.Cells[2, 21].Value = @"Причина срыва/переноса отгрузки";
worksheet.Cells[2, 22].Value = @"Количество дней";
worksheet.Cells["W1:X1"].Merge = true;
worksheet.Cells["W1:X1"].Value = "Доставка";
worksheet.Cells[2, 23].Value = @"Причина срыва доставки";
worksheet.Cells[2, 24].Value = @"Количество дней";
/*worksheet.Cells["Y1:Y2"].Merge = true;
* worksheet.Cells[1, 25].Value = @"Точность доставки %";*/
worksheet.Cells["Y1:Y2"].Merge = true;
worksheet.Cells[1, 25].Value = @"Точность поставки по времени, %";
/*worksheet.Cells["AA1:AA2"].Merge = true;
* worksheet.Cells[1, 27].Value = @"CreateDate";*/
worksheet.Cells["Z1:Z2"].Merge = true;
worksheet.Cells[1, 26].Value = @"Расстояние, км.";
worksheet.Cells["AA1:AA2"].Merge = true;
worksheet.Cells[1, 27].Value = @"Точность выхода на склад, %";
worksheet.Cells["AB1:AB2"].Merge = true;
worksheet.Cells[1, 28].Value = @"Площадка отгрузки";
/* --------------------------------------------------- */
worksheet.Cells["AC1:AC2"].Merge = true;
worksheet.Cells[1, 29].Value = @"Строка отгрузки";
worksheet.Cells["AD1:AD2"].Merge = true;
worksheet.Cells[1, 30].Value = @"№ заказа поставщика";
worksheet.Cells["AE1:AE2"].Merge = true;
worksheet.Cells[1, 31].Value = @"№ ЗНП";
worksheet.Cells["AF1:AF2"].Merge = true;
worksheet.Cells[1, 32].Value = @"Вид отгрузки";
worksheet.Cells["AG1:AG2"].Merge = true;
worksheet.Cells[1, 33].Value = @"Дата создания строки";
worksheet.Cells["AH1:AH2"].Merge = true;
worksheet.Cells[1, 34].Value = @"Справ KPI";
using (var range = worksheet.Cells[$"A2:AH{_delivery.Count + 2}"])
{
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
}
int _beginRow = 3;
for (int i = 0; i < _delivery.Count; i++)
{
worksheet.Column(1).Width = 5;
worksheet.Cells[$"A{i + _beginRow}"].Value = i + 1;
worksheet.Column(2).Width = 50;
worksheet.Cells[$"B{i + _beginRow}"].Value = _delivery[i].NameCustomer;
worksheet.Column(3).Width = 55;
worksheet.Cells[$"C{i + _beginRow}"].Value = _delivery[i].AddressCustomer;
worksheet.Column(4).Width = 30;
worksheet.Cells[$"D{i + _beginRow}"].Value = _delivery[i].CoNum;
worksheet.Column(5).Width = 7;
worksheet.Cells[$"E{i + _beginRow}"].Value = _delivery[i].CoLine;
worksheet.Column(6).Width = 13;
worksheet.Cells[$"F{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"F{i + _beginRow}"].Value = _delivery[i].DateZay;
worksheet.Column(7).Width = 25;
worksheet.Cells[$"G{i + _beginRow}"].Value = _delivery[i].MerchZayNum;
worksheet.Column(8).Width = 28;
worksheet.Cells[$"H{i + _beginRow}"].Value = _delivery[i].ShipZayNum;
worksheet.Column(9).Width = 13;
worksheet.Cells[$"I{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"I{i + _beginRow}"].Value = _delivery[i].DateMfgPlan;
worksheet.Column(10).Width = 13;
worksheet.Cells[$"J{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"J{i + _beginRow}"].Value = _delivery[i].DateMfgFact;
worksheet.Column(11).Width = 13;
worksheet.Cells[$"K{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"K{i + _beginRow}"].Value = _delivery[i].DateWhsPlan;
worksheet.Column(12).Width = 13;
worksheet.Cells[$"L{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"L{i + _beginRow}"].Value = _delivery[i].DateWhsFact;
worksheet.Column(13).Width = 13;
worksheet.Cells[$"M{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"M{i + _beginRow}"].Value = _delivery[i].DateShipPlan;
worksheet.Column(14).Width = 13;
worksheet.Cells[$"N{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"N{i + _beginRow}"].Value = _delivery[i].DateShipZay;
worksheet.Column(15).Width = 13;
worksheet.Cells[$"O{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"O{i + _beginRow}"].Value = _delivery[i].DateShipFact;
worksheet.Column(16).Width = 13;
worksheet.Cells[$"P{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"P{i + _beginRow}"].Value = _delivery[i].DateDostPlan;
worksheet.Column(17).Width = 13;
worksheet.Cells[$"Q{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"Q{i + _beginRow}"].Value = _delivery[i].DateDostPor;
worksheet.Column(18).Width = 13;
worksheet.Cells[$"R{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"R{i + _beginRow}"].Value = _delivery[i].DateDostFact;
worksheet.Column(19).Width = 20;
worksheet.Cells[$"S{i + _beginRow}"].Value = _delivery[i].StatMfg;
worksheet.Column(20).Width = 20;
// worksheet.Cells[$"T{i + _beginRow}"].Value = _delivery[i].StatRow;
worksheet.Cells[$"T{i + _beginRow}"].Value = _delivery[i].DayMfg;
worksheet.Column(21).Width = 13;
worksheet.Cells[$"U{i + _beginRow}"].Value = _delivery[i].StatShip;
worksheet.Column(22).Width = 20;
//worksheet.Cells[$"V{i + _beginRow}"].Value = _delivery[i].DayMfg;
worksheet.Cells[$"V{i + _beginRow}"].Value = _delivery[i].DayShip;
worksheet.Column(23).Width = 13;
worksheet.Cells[$"W{i + _beginRow}"].Value = _delivery[i].StatDost;
worksheet.Column(24).Width = 20;
//worksheet.Cells[$"X{i + _beginRow}"].Value = _delivery[i].DayShip;
worksheet.Cells[$"X{i + _beginRow}"].Value = _delivery[i].DayDost;
/*worksheet.Column(25).Width = 13;
* worksheet.Cells[$"Y{i + _beginRow}"].Value = _delivery[i].DayDost;*/
/*worksheet.Column(26).Width = 20;
* worksheet.Cells[$"Z{i + _beginRow}"].Value = _delivery[i].KpiStat;
* worksheet.Column(27).Width = 13;
* worksheet.Cells[$"AA{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
* worksheet.Column(28).Width = 13;
* worksheet.Cells[$"AA{i + _beginRow}"].Value = _delivery[i].CreateDate;
* worksheet.Column(29).Width = 13;
* worksheet.Cells[$"AB{i + _beginRow}"].Value = _delivery[i].Distance;
* worksheet.Column(30).Width = 13;
* worksheet.Cells[$"AC{i + _beginRow}"].Value = _delivery[i].KpiWhse;*/
worksheet.Column(25).Width = 13;
worksheet.Cells[$"Y{i + _beginRow}"].Value = _delivery[i].KpiStat;
worksheet.Column(26).Width = 13;
worksheet.Cells[$"Z{i + _beginRow}"].Value = _delivery[i].Distance;
worksheet.Column(27).Width = 13;
worksheet.Cells[$"AA{i + _beginRow}"].Value = _delivery[i].KpiWhse;
worksheet.Column(28).Width = 13;
worksheet.Cells[$"AB{i + _beginRow}"].Value = _delivery[i].PlantShip;
/* ---------------------------------------------------------------- */
worksheet.Column(29).Width = 13;
worksheet.Cells[$"AC{i + _beginRow}"].Value = _delivery[i].StatRow;
worksheet.Column(30).Width = 13;
worksheet.Cells[$"AD{i + _beginRow}"].Value = _delivery[i].PoNum;
worksheet.Column(31).Width = 13;
worksheet.Cells[$"AE{i + _beginRow}"].Value = _delivery[i].Job;
worksheet.Column(32).Width = 13;
worksheet.Cells[$"AF{i + _beginRow}"].Value = _delivery[i].VidOtgr;
worksheet.Column(33).Width = 13;
worksheet.Cells[$"AG{i + _beginRow}"].Style.Numberformat.Format = "dd-mm-yyyy";
worksheet.Cells[$"AG{i + _beginRow}"].Value = _delivery[i].CreateDate;
worksheet.Cells[$"AH{i + _beginRow}"].Value = _delivery[i].InList;
}
package.Save(); //Save the workbook.
}
return(stream);
}
/*Сводный отчет фактическим значениям KPI по клиентам*/
public async Task <Stream> ReduceXLSFileStreamResult(ParamsCalculateKpi data)
{
var stream = new MemoryStream();
ExcelPackage package;
int startByRow = 4;
int startByColumn = 2;
List <KpiByCustomer> _kpis = _dataService.ListKpiByCustomers(data);
List <KpiHelper> _selectedKpi = Utils.GetSelectedKpi(data);
int countKpi = _selectedKpi.Count;
List <Tuple <String, double, double, double, double, double> > itog = new List <Tuple <string, double, double, double, double, double> >();
// PreciseDelivery delivery = _kpis.First(e => e.Detail.Count == _kpis.Max(p => p.Detail.Count));
using (package = new ExcelPackage(stream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Reduce");
using (var range = worksheet.Cells[startByRow, startByColumn, startByRow + 1,
startByColumn + countKpi * 3 + 1])
{
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(238, 236, 225));
}
/* =========================== Caption ========================================= */
worksheet.Cells[startByRow - 2, startByColumn + 1].Value = $"Сводный отчет фактическим значениям KPI по клиентам";
worksheet.Cells[startByRow - 1, startByColumn + 1].Value =
$"Период с {data.RangeDate.Start} по {data.RangeDate.End}";
/* ------------------------------------------------------------------------------ */
worksheet.Cells[startByRow, startByColumn, startByRow + 1, startByColumn].Merge = true;
worksheet.Cells[startByRow, startByColumn].Value = @"№";
worksheet.Column(startByColumn).Width = 5;
worksheet.Cells[startByRow, startByColumn + 1, startByRow + 1, startByColumn + 1].Merge = true;
worksheet.Cells[startByRow, startByColumn + 1].Value = @"Клиент";
worksheet.Column(startByColumn + 1).Width = 50;
startByColumn = startByColumn + 1;
for (int i = 0; i < _selectedKpi.Count; i++)
{
worksheet.Cells[startByRow, startByColumn + 1, startByRow, startByColumn + 3].Merge = true;
worksheet.Cells[startByRow, startByColumn + 1, startByRow, startByColumn + 3].Style.WrapText = true;
worksheet.Row(4).Height = 32.25;
worksheet.Cells[startByRow, startByColumn + 1].Value = _selectedKpi[i].Name;
worksheet.Cells[startByRow + 1, startByColumn + 1].Value = "Цель";
worksheet.Cells[startByRow + 1, startByColumn + 2].Value = "Факт";
worksheet.Cells[startByRow + 1, startByColumn + 3].Value = "Откл.";
startByColumn = startByColumn + 3;
}
startByColumn = 2;
using (var range = worksheet.Cells[startByRow + 2, startByColumn,
_kpis.Count + startByRow + 1,
startByColumn + countKpi * 3 + 1])
{
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
}
startByColumn = 3;
startByRow = startByRow + 2;
int count = 0;
int firstAvrgRow = startByRow;
_kpis.ForEach(k =>
{
worksheet.Cells[startByRow, startByColumn - 1].Value = ++count;
worksheet.Cells[startByRow, startByColumn].Value = k.Customer;
k.Kpis.ForEach(e =>
{
worksheet.Cells[startByRow, startByColumn + 1].Style.Numberformat.Format = "0.00";
worksheet.Cells[startByRow, startByColumn + 1].Value = e.Target;
worksheet.Cells[startByRow, startByColumn + 2].Style.Numberformat.Format = "0.00";
worksheet.Cells[startByRow, startByColumn + 2].Value = e.Fact;
worksheet.Cells[startByRow, startByColumn + 3].Style.Numberformat.Format = "0.00";
worksheet.Cells[startByRow, startByColumn + 3].Value = e.Deviation;
startByColumn = startByColumn + 3;
//itog.Add(new Tuple<string, double, double,double,double,double>(e.Description,e.TargetSumma,e.TargetCount, e.FactSumma,e.FactCount,e.Deviation));
itog.Add(new Tuple <string, double, double, double, double, double>(e.Description, e.Target, e.TargetCount, e.Fact, e.FactCount, e.Deviation));
});
startByRow++;
startByColumn = 3;
});
var _groupItog = itog.GroupBy(kpi => kpi.Item1, (name, kpis) => new
{
Key = name,
Count = kpis.Count(),
//SummaTarget = kpis.Sum(kpi=>kpi.Item2),
AverageTarget = kpis.Average(kpi => kpi.Item2),
CountTarget = kpis.Sum(kpi => kpi.Item3),
//SummaFact = kpis.Sum(kpi=>kpi.Item4),
AverageFact = kpis.Average(kpi => kpi.Item4),
CountFact = kpis.Sum(kpi => kpi.Item5),
AverageDeviation = kpis.Average(kpi => kpi.Item6),
}
);
worksheet.Cells[startByRow, 3].Value = @"Итого (среднее значение) по клиентам :";
foreach (var result in _groupItog)
{
worksheet.Cells[startByRow, startByColumn + 1].Style.Numberformat.Format = "0.00";
worksheet.Cells[startByRow, startByColumn + 1].Value = result.AverageTarget; //result.SummaTarget/result.CountTarget;
worksheet.Cells[startByRow, startByColumn + 2].Style.Numberformat.Format = "0.00";
worksheet.Cells[startByRow, startByColumn + 2].Value = result.AverageFact; //result.SummaFact/result.CountFact;
worksheet.Cells[startByRow, startByColumn + 3].Style.Numberformat.Format = "0.00";
worksheet.Cells[startByRow, startByColumn + 3].Value = result.AverageTarget - result.AverageFact; //(result.SummaTarget/result.CountTarget) - (result.SummaFact/result.CountFact);
startByColumn = startByColumn + 3;
}
using (var range = worksheet.Cells[startByRow, 2, startByRow, startByColumn])
{
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
range.Style.Font.Bold = true;
}
package.Save();
}
return(stream);
}
public async Task <Stream> KpiXLSFileStreamResult(ParamsCalculateKpi data)
{
var stream = new MemoryStream();
ExcelPackage package;
int startByRow = 4;
int startByColumn = 2;
List <PreciseDelivery> _kpis = _dataService.CalculateKpi(data);
int countKpi = _kpis.Count;
// PreciseDelivery delivery = _kpis.First(e => e.Detail.Count == _kpis.Max(p => p.Detail.Count));
using (package = new ExcelPackage(stream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("KPI");
using (var range = worksheet.Cells[startByRow, startByColumn, startByRow + 1,
startByColumn + countKpi + 1])
{
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(238, 236, 225));
}
/* =========================== Caption ========================================= */
worksheet.Cells[startByRow - 2, startByColumn + 1].Value = $"Клиент {data.Customer.Name}";
worksheet.Cells[startByRow - 1, startByColumn + 1].Value =
$"Период с {data.RangeDate.Start} по {data.RangeDate.End}";
/* ------------------------------------------------------------------------------ */
worksheet.Cells[startByRow, startByColumn, startByRow + 1, startByColumn].Merge = true;
worksheet.Cells[startByRow, startByColumn].Value = @"Месяц";
worksheet.Column(startByColumn).Width = 20;
worksheet.Cells[startByRow, startByColumn + 1, startByRow + 1, startByColumn + 1].Merge = true;
worksheet.Cells[startByRow, startByColumn + 1].Value = @"Показатель";
worksheet.Column(startByColumn + 1).Width = 20;
worksheet.Cells[startByRow, startByColumn + 2, startByRow, startByColumn + countKpi + 1].Merge = true;
worksheet.Cells[startByRow, startByColumn + 2, startByRow, startByColumn + countKpi + 1].Value =
@"KPI";
Dictionary <int, string> dictionary = new Dictionary <int, string>();
for (int i = startByColumn + 2; i < startByColumn + countKpi + 2; i++)
{
worksheet.Cells[startByRow + 1, i].Value = _kpis[i - (startByColumn + 2)].Description;
dictionary.Add(i, _kpis[i - (startByColumn + 2)].Description);
worksheet.Column(i).Width = 25;
}
int beginKpiValue = startByRow + 2;
int lastRow = beginKpiValue;
Dictionary <String, List <Tuple <int, int> > > points;
int paddingChart = 0;
int countMonth = this.countMonthInKpi(_kpis);
ExcelRangeBase startCell = worksheet.Cells[startByRow + countMonth * 4 + 3, startByColumn - 1];
const double EXCELDEFAULTROWHEIGHT = 20.0;
const double EXCELDEFAULTROWWIDTH = 60.0;
var chartcellheight = (int)Math.Ceiling(400 / EXCELDEFAULTROWHEIGHT);
var chartcellwidth = (int)Math.Ceiling(500 / EXCELDEFAULTROWWIDTH);
foreach (var kpi in _kpis)
{
points = new Dictionary <string, List <Tuple <int, int> > >();
int k = dictionary.FirstOrDefault(x => x.Value.Equals(kpi.Description)).Key;
beginKpiValue = startByRow + 2;
lastRow = beginKpiValue;
List <Tuple <int, int> > _pointMonth = new List <Tuple <int, int> >();
List <Tuple <int, int> > _pointTarget = new List <Tuple <int, int> >();
List <Tuple <int, int> > _pointFact = new List <Tuple <int, int> >();
List <Tuple <int, int> > _pointDeviation = new List <Tuple <int, int> >();
List <Tuple <int, int> > _pointCountOrder = new List <Tuple <int, int> >();
foreach (var dev in kpi.Detail)
{
worksheet.Cells[beginKpiValue, startByColumn].Value = new DateTime(dev.Year, dev.Month, 1);
worksheet.Cells[beginKpiValue, startByColumn].Style.Numberformat.Format = "MMMM";
_pointMonth.Add(new Tuple <int, int>(beginKpiValue, startByColumn));
worksheet.Cells[beginKpiValue, startByColumn, beginKpiValue + 3, startByColumn].Merge = true;
worksheet.Cells[beginKpiValue, startByColumn + 1].Value = "Цель";
worksheet.Cells[beginKpiValue + 1, startByColumn + 1].Value = "Факт";
worksheet.Cells[beginKpiValue + 2, startByColumn + 1].Value = "Откл";
worksheet.Cells[beginKpiValue + 3, startByColumn + 1].Value = "Заказов";
worksheet.Cells[beginKpiValue, k].Style.Numberformat.Format = "0.00";
worksheet.Cells[beginKpiValue, k].Value = dev.Target;
_pointTarget.Add(new Tuple <int, int>(beginKpiValue, k));
worksheet.Cells[beginKpiValue + 1, k].Style.Numberformat.Format = "0.00";
worksheet.Cells[beginKpiValue + 1, k].Value = dev.Fact;
_pointFact.Add(new Tuple <int, int>(beginKpiValue + 1, k));
worksheet.Cells[beginKpiValue + 2, k].Style.Numberformat.Format = "0.00";
worksheet.Cells[beginKpiValue + 2, k].Value = dev.Deviation;
_pointDeviation.Add(new Tuple <int, int>(beginKpiValue + 2, k));
worksheet.Cells[beginKpiValue + 3, k].Style.Numberformat.Format = "0";
worksheet.Cells[beginKpiValue + 3, k].Value = dev.CountOrder;
_pointCountOrder.Add(new Tuple <int, int>(beginKpiValue + 3, k));
beginKpiValue += 4;
lastRow += beginKpiValue;
}
points.Add("month", _pointMonth);
points.Add("target", _pointTarget);
points.Add("fact", _pointFact);
points.Add("deviation", _pointDeviation);
points.Add("countOrder", _pointCountOrder);
CreateChart(worksheet, kpi.Description, points, startCell, paddingChart);
if (k % 2 == 0)
{
chartcellwidth = (int)Math.Ceiling(500 / 160.0);
startCell = startCell.Offset(chartcellheight * 0, chartcellwidth);
}
else
{
/*startCell = startCell.Offset(chartcellheight, 0);
* chartcellwidth = (int)Math.Ceiling(500 / 160.0);*/
startCell = startCell.Offset(chartcellheight, -chartcellwidth);
}
paddingChart++;
}
/*int k = dictionary.FirstOrDefault(x => x.Value.Equals(delivery.Description)).Key;
* int beginKpiValue = startByRow + 2;
*
* foreach (var dev in delivery.Detail)
* {
*
* worksheet.Cells[beginKpiValue , startByColumn].Value = dev.Date;
* worksheet.Cells[beginKpiValue , startByColumn].Style.Numberformat.Format = "MMMM";
* worksheet.Cells[beginKpiValue , startByColumn+1].Value = "Цель";
* worksheet.Cells[beginKpiValue+1, startByColumn+1].Value = "Факт";
* worksheet.Cells[beginKpiValue+2, startByColumn+1].Value = "Откл";
* worksheet.Cells[beginKpiValue+3 , startByColumn+1].Value = "Заказов";
* worksheet.Cells[beginKpiValue , k].Value = dev.Target;
* worksheet.Cells[beginKpiValue+1, k].Value = dev.Fact;
* worksheet.Cells[beginKpiValue+2, k].Value = dev.Deviation;
* worksheet.Cells[beginKpiValue+3 , k].Value = dev.CountOrder;
*
* beginKpiValue += 4;
*
*
*
* }*/
using (var range = worksheet.Cells[startByRow + 2, startByColumn, beginKpiValue - 1,
startByColumn + countKpi + 1])
{
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
}
package.Save();
}
return(stream);
}
