コード例 #1
0
        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;
        }
コード例 #2
0
        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;
        }
コード例 #3
0
        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();
            }
        }
コード例 #4
0
        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 отчета