public static void ExcelBriefExport(DateTime datestart, DateTime datefinish, BindingListView <WrapBriefData> Data)
{
if (Data != null)
{
SystemArgs.PrintLog("Началось формирование выгрузки краткого анализа");
SaveFileDialog sfd = new SaveFileDialog()
{
DefaultExt = "",
Title = "Сохранение анализа",
Filter = "Файл Excel| *.xlsx",
FileName = Title + " от " + DateTime.Now.ToString().Replace('.', '_').Replace(':', '_'),
RestoreDirectory = true
};
if (sfd.ShowDialog() == DialogResult.OK)
{
if (!String.IsNullOrEmpty(sfd.FileName))
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
excelPackage.Workbook.Properties.Author = "Дефекты";
excelPackage.Workbook.Properties.Title = "Анализ за период от " + datestart.ToShortDateString() + " до " + datefinish.ToShortDateString();
excelPackage.Workbook.Properties.Created = DateTime.Now;
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Анализ");
worksheet.Cells["G1:M1"].Merge = true;
worksheet.Cells["G1"].Value = "Анализ по дефектности";
worksheet.Cells["G1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["G2:M2"].Merge = true;
worksheet.Cells["G2"].Value = "слитков цилиндрических";
worksheet.Cells["G2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["G3:M3"].Merge = true;
worksheet.Cells["G3"].Value = "за период с " + datestart.ToShortDateString() + " по " + datefinish.ToShortDateString();
worksheet.Cells["G3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["B7"].Value = TitleX;
worksheet.Cells["C7"].Value = TitleY;
int DimensionEndRow;
foreach (var item in Data)
{
DimensionEndRow = worksheet.Dimension.End.Row + 1;
worksheet.Cells["B" + DimensionEndRow.ToString()].Value = item.Name;
worksheet.Cells["C" + DimensionEndRow.ToString()].Value = item.Value;
}
DimensionEndRow = worksheet.Dimension.End.Row;
var modelTable = worksheet.Cells["B7:C" + DimensionEndRow.ToString()];
modelTable.Style.Border.Top.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Left.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Right.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
modelTable.AutoFitColumns();
var chart = (ExcelBarChart)worksheet.Drawings.AddChart(Title, eChartType.ColumnClustered);
chart.SetSize(600, 600);
chart.SetPosition(6, 0, 10, 0);
chart.Title.Text = Title;
chart.Series.Add(ExcelRange.GetAddress(8, 3, DimensionEndRow, 3), ExcelRange.GetAddress(8, 2, DimensionEndRow, 2));
chart.Legend.Remove();
chart.XAxis.Title.Text = TitleX;
chart.YAxis.Title.Font.Size = 14;
chart.XAxis.Title.Font.Size = 14;
chart.YAxis.Title.Text = TitleY;
FileInfo fi = new FileInfo(sfd.FileName);
excelPackage.SaveAs(fi);
}
}
else
{
SystemArgs.PrintLog("Место сохранения отчета равно null");
MessageBox.Show("Необходимо ввести названия файла", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
SystemArgs.PrintLog("Не смогло начатся формирование выгрузки краткого отчета лист равен null");
MessageBox.Show("Для экспорта необходимо сформировать анализ", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static void ByDate(DateTime DateStart, DateTime DateFinish)
{
SystemArgs.PrintLog("Началось формирование отчета");
SaveFileDialog sfd = new SaveFileDialog();
sfd.DefaultExt = "";
sfd.Title = "Сохранение отчета";
sfd.Filter = "Файл Excel| *.xlsx";
sfd.FileName = "Отчет от " + DateTime.Now.ToString().Replace('.', '_').Replace(':', '_');
sfd.RestoreDirectory = true;
if (sfd.ShowDialog() == DialogResult.OK)
{
SystemArgs.PrintLog("Выбрано место сохранения отчета");
if (!String.IsNullOrEmpty(sfd.FileName))
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
//Настройки excel отчета
excelPackage.Workbook.Properties.Author = "Дефекты";
excelPackage.Workbook.Properties.Title = "Отчет за период от " + DateStart.ToShortDateString() + " до " + DateFinish.ToShortDateString();
excelPackage.Workbook.Properties.Created = DateTime.Now;
//Создания листа
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Отчет");
//Добавление шапки
worksheet.Cells["G1:M1"].Merge = true;
worksheet.Cells["G1"].Value = "Отчет по дефектности";
worksheet.Cells["G1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["G2:M2"].Merge = true;
worksheet.Cells["G2"].Value = "слитков цилиндрических";
worksheet.Cells["G2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["G3:M3"].Merge = true;
worksheet.Cells["G3"].Value = "за период с " + DateStart.ToShortDateString() + " по " + DateFinish.ToShortDateString();
worksheet.Cells["G3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var PositionsReport = SystemArgs.Positions.Where(date => (date.DateCreate >= DateStart) && (date.DateCreate <= DateFinish)).ToList();
//Брак
worksheet.Cells["A6"].Value = "Брак:";
var WeightReport = PositionsReport.Sum(pos => pos.Weight);
var CountReport = PositionsReport.Sum(pos => pos.Count);
worksheet.Cells["A7"].Value = "-";
worksheet.Cells["A8"].Value = "-";
worksheet.Cells["B7"].Value = WeightReport.ToString() + " тонн(ы)";
worksheet.Cells["B8"].Value = CountReport.ToString() + " слитки(ов)";
//По видам деффектов
worksheet.Cells["A10:F10"].Merge = true;
worksheet.Cells["A10"].Value = "По видам деффектов (пропуская нулевые):";
var DefectCount = from dc in PositionsReport
group dc by dc.Description.Name into g
select new { NameDefect = g.Key, Value = g.Count() };
int DimensionEndRow;
foreach (var item in DefectCount)
{
DimensionEndRow = worksheet.Dimension.End.Row + 1;
worksheet.Cells["A" + DimensionEndRow.ToString()].Value = "-";
worksheet.Cells["B" + DimensionEndRow.ToString()].Value = item.NameDefect;
worksheet.Cells["C" + DimensionEndRow.ToString()].Value = item.Value;
}
//По бригадам (включая нулевые)
DimensionEndRow = worksheet.Dimension.End.Row + 2;
int ForDiag = DimensionEndRow + 1;
worksheet.Cells["A" + DimensionEndRow.ToString() + ":F" + DimensionEndRow.ToString()].Merge = true;
worksheet.Cells["A" + DimensionEndRow.ToString()].Value = "По бригадам (включая нулевые):";
var BrigadeCount = from dc in PositionsReport
group dc by dc.NumBrigade.Name into g
select new { NameBrigade = g.Key, Weight = g.Sum(t => t.Weight), Melt = g.Count() };
foreach (var item in BrigadeCount)
{
DimensionEndRow = worksheet.Dimension.End.Row + 1;
worksheet.Cells["A" + DimensionEndRow.ToString()].Value = "-";
worksheet.Cells["B" + DimensionEndRow.ToString()].Value = item.NameBrigade;
worksheet.Cells["C" + DimensionEndRow.ToString()].Value = item.Weight;
worksheet.Cells["D" + DimensionEndRow.ToString()].Value = " тонн(ы)";
worksheet.Cells["E" + DimensionEndRow.ToString()].Value = item.Melt;
worksheet.Cells["F" + DimensionEndRow.ToString()].Value = " плавка(и)";
}
worksheet.Cells["A6:F" + DimensionEndRow + 1.ToString()].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["B7:B8"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
worksheet.Cells["A6:G" + DimensionEndRow + 1.ToString()].AutoFitColumns();
//Добавление диаграммы
var chart = (ExcelBarChart)worksheet.Drawings.AddChart("Отчет по параметрам", eChartType.ColumnClustered);
chart.Legend.Position = eLegendPosition.Bottom;
chart.SetSize(600, 300);
chart.SetPosition(4, 0, 10, 0);
chart.Title.Text = "Отчет по параметрам";
chart.Series.Add(ExcelRange.GetAddress(ForDiag, 3, DimensionEndRow, 3), ExcelRange.GetAddress(ForDiag, 2, DimensionEndRow, 2));
chart.Series[0].Header = "тонн(ы)";
var chartType2 = chart.PlotArea.ChartTypes.Add(eChartType.LineMarkers);
var serie2 = chartType2.Series.Add(ExcelRange.GetAddress(ForDiag, 5, DimensionEndRow, 5), ExcelRange.GetAddress(ForDiag, 2, DimensionEndRow, 2));
serie2.Header = "плавка(и)";
//Сохранение
try
{
FileInfo fi = new FileInfo(sfd.FileName);
excelPackage.SaveAs(fi);
}
catch
{
SystemArgs.PrintLog("Директория для сохранения отчета была утеряна");
MessageBox.Show("Невозможно найти директорию для сохранения отчета", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
SystemArgs.PrintLog("Место сохранения отчета равно null");
MessageBox.Show("Необходимо ввести названия файла", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public static void ExcelFullExport(DateTime datestart, DateTime datefinish)
{
if (dataFullExports != null)
{
SystemArgs.PrintLog("Началось формирование выгрузки расширенного анализа");
SaveFileDialog sfd = new SaveFileDialog()
{
DefaultExt = "",
Title = "Сохранение анализа",
Filter = "Файл Excel| *.xlsx",
FileName = Title + " от " + DateTime.Now.ToString().Replace('.', '_').Replace(':', '_'),
RestoreDirectory = true
};
if (sfd.ShowDialog() == DialogResult.OK)
{
if (!String.IsNullOrEmpty(sfd.FileName))
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
excelPackage.Workbook.Properties.Author = "Дефекты";
excelPackage.Workbook.Properties.Title = Title + " от " + datestart.ToShortDateString() + " до " + datefinish.ToShortDateString();
excelPackage.Workbook.Properties.Created = DateTime.Now;
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Анализ");
worksheet.Cells["E1:H1"].Merge = true;
worksheet.Cells["E1"].Value = "Анализ по дефектности";
worksheet.Cells["E1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["E2:H2"].Merge = true;
worksheet.Cells["E2"].Value = "слитков цилиндрических";
worksheet.Cells["E2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["E3:H3"].Merge = true;
worksheet.Cells["E3"].Value = "за период с " + datestart.ToShortDateString() + " по " + datefinish.ToShortDateString();
worksheet.Cells["E3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells["A7"].Value = "Дата формирования";
worksheet.Cells["B7"].Value = "Сумма брака, тонн";
worksheet.Cells["C7"].Value = TitleL2;
worksheet.Cells["D7"].Value = TitleL1;
worksheet.Cells["E7"].Value = TitleL2;
int DimensionEndRow;
foreach (var item in dataFullExports)
{
DimensionEndRow = worksheet.Dimension.End.Row + 1;
worksheet.Cells["A" + DimensionEndRow.ToString()].Value = item.DateFormation.GetDateTimeFormats();
worksheet.Cells["B" + DimensionEndRow.ToString()].Value = item.SumWeight;
worksheet.Cells["C" + DimensionEndRow.ToString()].Value = item.AccumulationWeight;
worksheet.Cells["D" + DimensionEndRow.ToString()].Value = item.DiameterWeight;
worksheet.Cells["E" + DimensionEndRow.ToString()].Value = item.AccumulationDiameter;
}
DimensionEndRow = worksheet.Dimension.End.Row;
var modelTable = worksheet.Cells["A7:E" + DimensionEndRow.ToString()];
modelTable.Style.Border.Top.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Left.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Right.Style = ExcelBorderStyle.Thin;
modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
modelTable.AutoFitColumns();
modelTable.Style.WrapText = true;
modelTable.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var chartOne = worksheet.Drawings.AddChart("Общая", eChartType.Line);
chartOne.SetSize(800, 300);
chartOne.SetPosition(6, 0, 6, 0);
chartOne.Title.Text = "Общая дефектность за период";
chartOne.Series.Add(worksheet.Cells[8, 2, DimensionEndRow, 2], worksheet.Cells[8, 1, DimensionEndRow, 1]);
var chartOneb = chartOne.PlotArea.ChartTypes.Add(eChartType.Line);
chartOneb.Series.Add(worksheet.Cells[8, 3, DimensionEndRow, 3], worksheet.Cells[8, 1, DimensionEndRow, 1]);
chartOne.Series[0].Header = "Сумма брака, тонн";
chartOneb.Series[0].Header = "Накопление брака, тонн";
chartOneb.UseSecondaryAxis = true;
chartOne.Legend.Position = eLegendPosition.Bottom;
chartOne.YAxis.Title.Font.Size = 14;
chartOne.YAxis.Title.Text = TitleY;
var chartTwo = worksheet.Drawings.AddChart("По параметру", eChartType.Line);
chartTwo.SetSize(800, 400);
chartTwo.SetPosition(21, 0, 6, 0);
chartTwo.Title.Text = Title;
chartTwo.Series.Add(worksheet.Cells[8, 4, DimensionEndRow, 4], worksheet.Cells[8, 1, DimensionEndRow, 1]);
var chartTwob = chartTwo.PlotArea.ChartTypes.Add(eChartType.Line);
chartTwob.Series.Add(worksheet.Cells[8, 5, DimensionEndRow, 5], worksheet.Cells[8, 1, DimensionEndRow, 1]);
chartTwo.Series[0].Header = TitleL1;
chartTwob.Series[0].Header = TitleL2;
chartTwob.UseSecondaryAxis = true;
chartTwo.Legend.Position = eLegendPosition.Bottom;
chartTwo.YAxis.Title.Font.Size = 14;
chartTwo.YAxis.Title.Text = TitleY;
FileInfo fi = new FileInfo(sfd.FileName);
excelPackage.SaveAs(fi);
}
}
else
{
SystemArgs.PrintLog("Место сохранения отчета равно null");
MessageBox.Show("Необходимо ввести названия файла", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
SystemArgs.PrintLog("Не смогло начатся формирование выгрузки расширенного отчета лист равен null");
MessageBox.Show("Для экспорта необходимо сформировать анализ", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}