private void OnExport(int numToEdit = 0) { int len1 = MonthUseList.Count(); int len2 = 9; int len3; int len4; object[,] arrRes = new object[len1, len2]; object[,] arrChart = new object[len1, 2]; object[] arrCaption = new object[] { "Код", "Ресурс", "ЕдИзм", "План", "Факт", "Откл", "ПланРуб", "ФактРуб", "ОтклРуб" }; object[] arrCaption1 = new object[] { "Ресурс", "ФактРуб" }; DBListToArray(); ArrToExcel(); void DBListToArray() { int i = 0; foreach (var r in MonthUseList) { arrRes[i, 0] = r.IdEnergyResource; arrRes[i, 1] = r.ResourceName; arrRes[i, 2] = r.UnitName; arrRes[i, 3] = r.Plan; arrRes[i, 4] = r.Fact; arrRes[i, 5] = r.Difference; arrRes[i, 6] = r.PlanCost; arrRes[i, 7] = r.FactCost; arrRes[i, 8] = r.DifferenceCost; i++; } var qry2 = from o in MonthUseList select new { o.FactCost, o.DifferenceCost, o.ResourceName }; double Total = qry2.Sum(n => n.FactCost); //var qry3 = from o in MonthUseList // where o.FactCost >= Total * Proc / 100 // orderby o.FactCost descending // select new // { // o.FactCost, // o.ResourceName // }; //var qry4 = from o in MonthUseList // where o.FactCost < Total * Proc / 100 // select new // { // o.FactCost, // o.ResourceName // }; //double Total1 = qry4.Sum(n => n.FactCost); //int j = 0; //ChartList.Clear(); //foreach (var newY in qry3.ToList()) //{ // ViewResult r = new ViewResult(); // r.ResourceName = newY.ResourceName; // r.FactCost = newY.FactCost; // ChartList.Add(r); // j++; //} //ViewResult s = new ViewResult(); //s.ResourceName = "Прочие"; //s.FactCost = Total1; //ChartList.Add(s); //len3 = ChartList.Count(); //len4 = 2; //i = 0; //foreach (var l in ChartList) //{ // arrChart[i, 0] = l.ResourceName; // arrChart[i, 1] = l.FactCost; // i++; //} } void ArrToExcel() { Excel.Application ex = new Excel.Application(); //ex.SheetsInNewWorkbook = 1; //Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing); ex.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + @"tmpcostcenters.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); ex.DisplayAlerts = true; Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(1); sheet.Name = "ЦентрыЗатрат"; Excel.Range c1 = (Excel.Range)sheet.Cells[1, 1]; Excel.Range c2 = (Excel.Range)sheet.Cells[1, 1]; Excel.Range rangeCapt = sheet.get_Range(c1, c2); rangeCapt.Value = ChartCaption; c1 = (Excel.Range)sheet.Cells[3, 1]; c2 = (Excel.Range)sheet.Cells[3, 9]; Excel.Range rangeCaption = sheet.get_Range(c1, c2); rangeCaption.Value = arrCaption; c1 = (Excel.Range)sheet.Cells[4, 1]; c2 = (Excel.Range)sheet.Cells[4 + len1 - 1, 9]; Excel.Range range = sheet.get_Range(c1, c2); range.Value = arrRes; sheet = (Excel.Worksheet)ex.Worksheets.get_Item(2); c1 = (Excel.Range)sheet.Cells[1, 1]; c2 = (Excel.Range)sheet.Cells[1, 1]; //rangeCapt = sheet.get_Range(c1, c2); //rangeCapt.Value = "Структура фактического потребления, руб."; //c1 = (Excel.Range)sheet.Cells[3, 1]; //c2 = (Excel.Range)sheet.Cells[3, 2]; //rangeCaption = sheet.get_Range(c1, c2); //rangeCaption.Value = arrCaption1; //c1 = (Excel.Range)sheet.Cells[4, 1]; //c2 = (Excel.Range)sheet.Cells[4 + len3 - 1, 2]; //range = sheet.get_Range(c1, c2); //range.Value = arrChart; ex.Visible = true; ex.DisplayAlerts = true; //Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog(); //dlg.FileName = "CostCenters01"; // Default file name //dlg.DefaultExt = ".xlsx"; // Default file extension //dlg.Filter = "Книга Excel (.xlsx)|*.xlsx"; // Filter files by extension //dlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); //Nullable<bool> result = dlg.ShowDialog(); //if (result == true) //{ // string filename = dlg.FileName; // ex.Application.ActiveWorkbook.SaveAs(filename + @"", Type.Missing, // Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, // Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // ex.WindowState = Excel.XlWindowState.xlMaximized; // MessageBoxResult result1 = System.Windows.MessageBox.Show("Отчёт успешно сформирован", // "Поздравляю", // MessageBoxButton.OK, // MessageBoxImage.Information); // //ex.Visible = true; //} //ex.Quit(); } }
public void DiagrammInit() { string[] SeriesTitle = new string[] { "Потребл", "Продажа", "Потери" }; int i = 0; int dlina = MonthUseList.Count(); SeriesCollection.Clear(); StackedAreaSeries ls1 = new StackedAreaSeries { Title = SeriesTitle[i], LineSmoothness = 0, DataLabels = true, StrokeThickness = 2.5, //Stroke = System.Windows.Media.Brushes.DeepSkyBlue, Fill = System.Windows.Media.Brushes.AliceBlue, FontSize = 10, Foreground = System.Windows.Media.Brushes.Black, FontWeight = FontWeights.Bold, LabelPoint = point => string.Format("{0:N0}", point.Y), Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i])) }; StackedAreaSeries ls2 = new StackedAreaSeries { Title = SeriesTitle[i + 1], LineSmoothness = 0, DataLabels = true, StrokeThickness = 2.5, //Stroke = System.Windows.Media.Brushes.Red, Fill = System.Windows.Media.Brushes.Pink, FontSize = 10, Foreground = System.Windows.Media.Brushes.Black, FontWeight = FontWeights.Bold, LabelPoint = point => string.Format("{0:N0}", point.Y), Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i + 1])) }; StackedAreaSeries ls3 = new StackedAreaSeries { Title = SeriesTitle[i + 2], LineSmoothness = 0, DataLabels = true, FontSize = 10, StrokeThickness = 2.5, Foreground = System.Windows.Media.Brushes.Black, Stroke = System.Windows.Media.Brushes.DarkRed, Fill = System.Windows.Media.Brushes.Moccasin, FontWeight = FontWeights.Bold, LabelPoint = point => string.Format("{0:N0}", point.Y), AreaLimit = 0, Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i + 2])) }; SeriesCollection.Add(ls1); SeriesCollection.Add(ls2); SeriesCollection.Add(ls3); Labels.Clear(); foreach (var newX in MonthUseList) { Labels.Add(newX.Period.ToString()); } ; //Labels.Clear(); //Labels = ObservableCollection<string>(MonthUseList.Select(x => x.Period)); ChartValues <ObservableValue> ValueYFill(string typeSeries = "") { ChartValues <ObservableValue> rez = new ChartValues <ObservableValue>(); switch (typeSeries) { case ("Потребл"): foreach (ViewFabricateUse newY in MonthUseList) { rez.Add(new ObservableValue(newY.Fact1)); } break; case ("Продажа"): foreach (ViewFabricateUse newY in MonthUseList) { rez.Add(new ObservableValue(newY.Fact0)); } break; case ("Потери"): foreach (ViewFabricateUse newY in MonthUseList) { rez.Add(new ObservableValue(newY.Loss)); } break; } return(rez); } }
public void DiagrammInit() { int dlina = MonthUseList.Count(); string[] SeriesTitle = new string[] { "План", "Факт", "Откл", "ПланРуб", "ФактРуб", "ОтклРуб" }; int i = !RBCostIsChecked ? 0 : 3; SeriesCollection.Clear(); LineSeries ls1 = new LineSeries { Title = SeriesTitle[i], LineSmoothness = 0, StrokeThickness = 2.5, //Stroke = System.Windows.Media.Brushes.DeepSkyBlue, Fill = System.Windows.Media.Brushes.AliceBlue, DataLabels = dlina <= 12 ? true : false, FontSize = 10, FontWeight = FontWeights.Bold, LabelPoint = point => string.Format("{0:N0}", point.Y), Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i])) }; SeriesCollection.Add(ls1); LineSeries ls2 = new LineSeries { Title = SeriesTitle[i + 1], LineSmoothness = 0, DataLabels = true, StrokeThickness = 2.5, //Stroke = System.Windows.Media.Brushes.Red, Fill = System.Windows.Media.Brushes.Transparent, FontSize = 10, FontWeight = FontWeights.Bold, LabelPoint = point => string.Format("{0:N0}", point.Y), Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i + 1])) }; SeriesCollection.Add(ls2); LineSeries ls3 = new LineSeries { Title = SeriesTitle[i + 2], LineSmoothness = 0, DataLabels = true, FontSize = 10, FontWeight = FontWeights.Bold, LabelPoint = point => string.Format("{0:N0}", point.Y), AreaLimit = 0, Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i + 2])) }; SeriesCollection.Add(ls3); Labels.Clear(); foreach (var newX in MonthUseList) { Labels.Add(newX.Period.ToString()); } ; //Labels.Clear(); //Labels = new ObservableCollection<string>(MonthUseList.Select(x => x.Period)); Formatter = val => val.ToString("N0"); ChartValues <ObservableValue> ValueYFill(string typeSeries = "") { ChartValues <ObservableValue> rez = new ChartValues <ObservableValue>(); switch (typeSeries) { case ("План"): foreach (ViewResult newY in MonthUseList) { rez.Add(new ObservableValue(newY.Plan)); } break; case ("Факт"): foreach (ViewResult newY in MonthUseList) { rez.Add(new ObservableValue(newY.Fact)); } break; case ("Откл"): foreach (ViewResult newY in MonthUseList) { rez.Add(new ObservableValue(newY.Difference)); } break; case ("ПланРуб"): foreach (ViewResult newY in MonthUseList) { rez.Add(new ObservableValue(newY.PlanCost)); } break; case ("ФактРуб"): foreach (ViewResult newY in MonthUseList) { rez.Add(new ObservableValue(newY.FactCost)); } break; case ("ОтклРуб"): foreach (ViewResult newY in MonthUseList) { rez.Add(new ObservableValue(newY.DifferenceCost)); } break; } return(rez); } }
public void DiagrammInit() { int dlina = MonthUseList.Count(); string[] SeriesTitle = new string[] { "Всего", "Потери", "ВсегоРуб", "ПотериРуб" }; int i = !RBCostIsChecked ? 0 : 2; SeriesCollection.Clear(); LineSeries ls1 = new LineSeries { Title = SeriesTitle[i], LineSmoothness = 0, StrokeThickness = 2.5, Fill = System.Windows.Media.Brushes.AliceBlue, DataLabels = true, FontSize = 10, FontWeight = FontWeights.Bold, LabelPoint = point => string.Format("{0:N0}", point.Y), Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i])) }; SeriesCollection.Add(ls1); LineSeries ls2 = new LineSeries { Title = SeriesTitle[i + 1], LineSmoothness = 0, DataLabels = true, StrokeThickness = 2.5, Fill = System.Windows.Media.Brushes.Transparent, FontSize = 10, FontWeight = FontWeights.Bold, LabelPoint = point => string.Format("{0:N0}", point.Y), Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i + 1])) }; SeriesCollection.Add(ls2); Labels.Clear(); foreach (var newX in MonthUseList) { Labels.Add(newX.Period.ToString()); } ; //Labels.Clear(); //Labels = new ObservableCollection<string>(MonthUseList.Select(x => x.Period)); Formatter = val => val.ToString("N0"); ChartValues <ObservableValue> ValueYFill(string typeSeries = "") { ChartValues <ObservableValue> rez = new ChartValues <ObservableValue>(); switch (typeSeries) { case ("Всего"): foreach (ViewLossesFact newY in MonthUseList) { rez.Add(new ObservableValue(newY.FactTotal)); } break; case ("Потери"): foreach (ViewLossesFact newY in MonthUseList) { rez.Add(new ObservableValue(newY.FactLoss)); } break; case ("ВсегоРуб"): foreach (ViewLossesFact newY in MonthUseList) { rez.Add(new ObservableValue(newY.FactTotalCost)); } break; case ("ПотериРуб"): foreach (ViewLossesFact newY in MonthUseList) { rez.Add(new ObservableValue(newY.FactLossCost)); } break; } return(rez); } }