private void button3_Click(object sender, EventArgs e)
{
exel.Application application = new exel.Application();
exel.Sheets sheets;
exel.Worksheet worksheet;
exel.Range cells;
application.SheetsInNewWorkbook = 1;
application.Workbooks.Add(Type.Missing);
sheets = application.ActiveWorkbook.Sheets;
worksheet = (exel.Worksheet)sheets.get_Item(1);
worksheet.Name = "Статистика обновления";
worksheet.Activate();
int row = chart1.Series[0].Points.Count;
int col = chart1.Series.Count;
for (int i = 0; i < row; i++)
{
cells = (exel.Range)worksheet.Cells[i + 1, 1];
cells.Value2 = chart1.Series[0].Points[i].XValue + " г";
}
for (int i = 0; i < col; i++)
{
for (int j = 0; j < row; j++)
{
cells = (exel.Range)worksheet.Cells[j + 1, i + 2];
cells.Value2 = chart1.Series[i].Points[j].YValues;
}
}
string cur = "c" + (row);
cells = worksheet.get_Range("a1", cur.ToString());
cells.Select();
exel.Chart chart = (exel.Chart)application.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
chart.Activate();
chart.Select(Type.Missing);
application.ActiveChart.PlotBy = exel.XlRowCol.xlColumns;
application.ActiveChart.HasTitle = true;
application.ActiveChart.ChartTitle.Text = "Обновления";
application.ActiveChart.HasLegend = true;
application.ActiveChart.Legend.Position = exel.XlLegendPosition.xlLegendPositionBottom;
application.ActiveChart.SeriesCollection(1).Name = "Требуется обновить";
application.ActiveChart.SeriesCollection(2).Name = "Обновлено";
application.ActiveChart.ChartType = exel.XlChartType.xlColumnClustered;
application.ActiveChart.Location(exel.XlChartLocation.xlLocationAsObject, "Статистика обновления");
application.ActiveChart.ApplyDataLabels(exel.XlDataLabelsType.xlDataLabelsShowValue, false, true, false, false, false, false, true, false, true);
application.Visible = true;
}
private void btnReport_Click(object sender, EventArgs e)
{
ReportInfo();
if (dateRange.Count == 0 || dataRange.Count == 0)
{
MessageBox.Show("Нет данных");
return;
}
Excel.Application excelapp = new Excel.Application();
excelapp.SheetsInNewWorkbook = 1;
excelapp.Workbooks.Add(Type.Missing);
Excel.Sheets excelsheets = excelapp.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
Excel.Range excelcells = null;
int rows = dataRange.Count;
//head
excelcells = excelworksheet.Cells[1, 2];
excelcells.Clear();
excelcells.Value2 = "Прибыль";
for (int i = 1; i <= rows; i++)
{
excelcells = excelworksheet.Cells[i + 1, 1];
excelcells.Clear();
excelcells.Value2 = dateRange[i - 1];
}
for (int i = 1; i <= rows; i++)
{
excelcells = excelworksheet.Cells[i + 1, 2];
excelcells.Clear();
excelcells.Value2 = dataRange[i - 1];
}
Excel.Range c1 = excelworksheet.Cells[1, 1];
Excel.Range c2 = excelworksheet.Cells[dataRange.Count + 1, 2];
excelcells = excelworksheet.get_Range(c1, c2);
excelcells.Select();
Excel.Chart excelchart = (Excel.Chart)excelapp.Charts.Add(Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
excelchart.Activate();
excelchart.Select(Type.Missing);
excelchart.ChartTitle.Text = "Прибыль по месяцам";
excelapp.Visible = true;
}
private void ExportToExcel()
{
progress = 0;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Add();
Excel.Worksheet xlWorksheet = xlWorkbook.ActiveSheet;
for (int i = 1; i <= chart.Series.Count; i++)
{
xlWorksheet.Cells[i, 1] = chart.Series[i - 1].Name;
xlWorksheet.Cells[i, 1].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
xlWorksheet.Cells[i, 1].Borders.Color = Color.Black;
xlWorksheet.Cells[i, 1].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
progress += 4;
for (int j = 1; j <= chart.Series[i - 1].Points.Count; j++)
{
xlWorksheet.Cells[i, j + 1] = chart.Series[i - 1].Points[j - 1].YValues[0].ToString().Replace(",", ".");
xlWorksheet.Cells[i, j + 1].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
xlWorksheet.Cells[i, j + 1].Borders.Color = Color.Black;
xlWorksheet.Cells[i, j + 1].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
progress += 4;
}
}
Excel.Chart xlChart = xlWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlChart.Activate();
xlChart.Select(Type.Missing);
progress += 3;
xlChart = SetStyleXlsChart(xlChart);
try
{
if (chart.Titles[0].Text == null || chart.Titles[0].Text.Length == 0)
{
xlWorkbook.SaveAs(Properties.Settings.Default.ChartsExcelFilesDirectoryPath + $"Chart for { DateTime.Now.ToString().Replace(":", "-").Replace("/", ".")}.xlsx");
}
else
{
if (File.Exists(Properties.Settings.Default.ChartsExcelFilesDirectoryPath + $"{chart.Titles[0].Text}.xlsx"))
{
MessageDialog md = new MessageDialog(MessageDialog.QuestionTitle, MessageDialog.QuestionText4, MessageDialog.Icon.Question);
if (md.DialogResult == DialogResult.Yes)
{
goto Save;
}
else
{
goto Exit;
}
}
else
{
goto Save;
}
Save:
xlWorkbook.SaveAs(Properties.Settings.Default.ChartsExcelFilesDirectoryPath + $"{chart.Titles[0].Text}.xlsx");
}
}
catch (System.Runtime.InteropServices.COMException e)
{
MessageDialog.Show(MessageDialog.ErrorTitle, e.Message, MessageDialog.Icon.Cross);
}
Exit:
try
{
xlApp.AlertBeforeOverwriting = false;
xlApp.Visible = true;
}
catch (System.Runtime.InteropServices.COMException e)
{
MessageDialog.Show(MessageDialog.ErrorTitle, e.Message, MessageDialog.Icon.Cross);
xlApp.Quit();
}
}
public void Excele()
{
DB();
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workBook;
Excel.Worksheet workSheet;
workBook = excelApp.Workbooks.Add(System.Reflection.Missing.Value);
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
workSheet.Name = "Рейтинг" + sotr;
workSheet.Cells[1] = "Фамилия сотрудника";
workSheet.Cells[2] = "Дата оценки рейтинга";
workSheet.Cells[3] = "Критерии оценки";
workSheet.Cells[4] = "Вес критерия";
workSheet.Cells[5] = "Личные показатели";
workSheet.Cells[6] = "Результат";
workSheet.Cells[2, 1] = sotr;
workSheet.Cells[2, 2] = DateTime.Now.ToString("yyyy-MM-dd");
try
{
for (int j = 0; j < tabl.Rows.Count; j++)
{
workSheet.Cells[j + 2, 3] = tabl.Rows[j].Field <string>(1).ToString();
workSheet.Cells[j + 2, 4] = tabl.Rows[j].Field <int>(2).ToString();
workSheet.Cells[j + 2, 5] = lnums[j];
if (workSheet.Cells[j + 2, 5].Text == "0" || workSheet.Cells[j + 2, 5].Text == " ")
{
Microsoft.Office.Interop.Excel.Range cel = (Excel.Range)workSheet.Rows[j + 2];
cel.EntireRow.Delete(Type.Missing);
}
}
workSheet.Cells[2, 6] = Convert.ToDouble(FINALOCHKA.ToString("0.00"));
}
catch
{
}
try
{
for (int i = 1; i < tabl.Rows.Count; i++)
{
if (workSheet.Cells[i, 5].text == "")
{
workSheet.Range[$"C{i}", $"E{i}"].Rows.Delete(Type.Missing);
}
}
}
catch
{ }
var excelcells = workSheet.get_Range("E2", "E10");
excelcells.Select();
Excel.Chart excelchart = (Excel.Chart)excelApp.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelchart.Activate();
excelchart.Select(Type.Missing);
excelApp.ActiveChart.ChartType = Excel.XlChartType.xlPie;
excelApp.ActiveChart.HasTitle = true;
excelApp.ActiveChart.ChartTitle.Text
= "Рейтинг сотрудника";
excelApp.ActiveChart.FullSeriesCollection(1).XValues = "=Рейтинг" + sotr + "!$C$2:$C$10";
excelApp.ActiveChart.HasLegend = true;
excelApp.ActiveChart.Legend.Position
= Excel.XlLegendPosition.xlLegendPositionBottom;
excelApp.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, workSheet.Name);
var excelsheets = workBook.Worksheets;
workSheet = (Excel.Worksheet)excelsheets.get_Item(1);
workSheet.Shapes.Item(1).IncrementLeft(-201);
workSheet.Shapes.Item(1).IncrementTop((float)20.5);
workSheet.Shapes.Item(1).Height = 350;
workSheet.Shapes.Item(1).Width = 300;
workSheet.Columns.AutoFit();
excelApp.Visible = true;
excelApp.UserControl = true;
} // Формирование Excel отчета