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