コード例 #1
0
ファイル: XL.cs プロジェクト: vezone/FinApp
        public static void CreateChart(
            ref Excel.Worksheet page,
            ExcelChartInfo excelCI)
        {
            try
            {
                var xlCharts =
                    page.ChartObjects() as Excel.ChartObjects;
                Excel.ChartObject myChart =
                    xlCharts.Add(excelCI.m_Left, excelCI.m_Top, excelCI.m_Width, excelCI.m_Height);
                Excel.Chart chartPage = myChart.Chart;

                var chartRange  = page.get_Range(excelCI.m_NameLRange, excelCI.m_NameHRange);
                var chartRange2 = page.get_Range(excelCI.m_SourceLRange, excelCI.m_SourceHRange);

                chartPage.SetSourceData(chartRange);
                chartPage.ChartType =
                    excelCI.m_ChartType; //xlLine || xlColumnClustered
                chartPage.ChartWizard(
                    Source: chartRange2,
                    Title: excelCI.m_Title,
                    CategoryTitle: excelCI.m_CategoryTitle,
                    ValueTitle: excelCI.m_ValueTitle);
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
コード例 #2
0
        public void DrawChart()
        {
            var processes = Process.GetProcesses()
                            .OrderBy(p => p.WorkingSet64);
            int i = 2;

            foreach (var p in processes)
            {
                if (p.ProcessName == "WINWORD" ||
                    p.ProcessName == "OUTLOOK" ||
                    p.ProcessName == "EXCEL")
                {
                    excel.get_Range("A" + i).Value2 = p.ProcessName;
                    excel.get_Range("B" + i).Value2 = p.WorkingSet64;
                    i++;
                }
            }

            Excel.Range range = excel.get_Range("A1");
            Excel.Chart chart = (Excel.Chart)excel.ActiveWorkbook.Charts.Add(
                After: excel.ActiveSheet);

            chart.ChartWizard(Source: range.CurrentRegion,
                              Title: "Memory Usage of Office Applications");
            chart.ChartType  = Excel.XlChartType.xl3DArea;
            chart.ChartStyle = 14;
            chart.ChartArea.Copy();
        }
コード例 #3
0
ファイル: ExcelInterface.cs プロジェクト: secondmover/TVGL
        /// <summary>
        /// Creates a new graph in a new excel workbook. Values is a list of data series, where a series is a list of X,Y value pairs.
        /// </summary>
        /// <param name="values"></param>
        /// <param name="seriesTitle"></param>
        /// <param name="xAxis"></param>
        /// <param name="yAxis"></param>
        /// <param name="headers"></param>
        public static void PlotEachSeriesSeperately(List <List <double[]> > values, string seriesTitle = "",
                                                    string xAxis = "", string yAxis = "", List <string> headers = null)
        {
            //Create a new excel workbook and sheet
            Excel.Application xlApp;
            Excel.Workbook    xlWorkbook;
            Excel.Worksheet   xlWorksheet;
            object            misValue = System.Reflection.Missing.Value;

            xlApp       = new Excel.Application();
            xlWorkbook  = xlApp.Workbooks.Add(misValue);
            xlWorksheet = xlWorkbook.Worksheets.get_Item(1);

            //Export data to Excel Sheet from List<List<double>>
            //Set headers if given.
            if (headers != null)
            {
                for (var i = 0; i < headers.Count; i++)
                {
                    xlWorksheet.Cells[1, i].Value = headers[i];
                }
            }

            //Create Chart
            Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorksheet.ChartObjects(Type.Missing);

            for (var i = 0; i < values.Count; i++) //For each series
            {
                var xValues = new double[values[i].Count];
                var yValues = new double[values[i].Count];
                for (var j = 0; j < values[i].Count; j++) //For each point in series
                {
                    xValues[j] = values[i][j][0];
                    yValues[j] = values[i][j][1];
                }

                //Create a new chart and offset from previous
                var offset = 300 * (i);
                Excel.ChartObject myChart = xlCharts.Add(200, 30 + offset, 400, 300);
                Excel.Chart       chart   = myChart.Chart;
                chart.ChartType = Excel.XlChartType.xlXYScatterLines;
                Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection();
                Excel.Series           series           = seriesCollection.NewSeries();
                series.Values  = yValues;
                series.XValues = xValues;

                var title = seriesTitle + " " + i;
                chart.ChartWizard(
                    Title: title,
                    CategoryTitle: xAxis,
                    ValueTitle: yAxis);
            }

            xlApp.Visible = true;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
        }
コード例 #4
0
        /// <summary>
        /// Writes an Excel worksheet containing a Respiratory Signal's values and Plot
        /// </summary>
        /// <param name="ws"> The Excel worksheet object </param>
        /// <param name="SignalName"> The name of the respiratory signal </param>
        /// <param name="table"> The respiratory signal values and peak/onset locations </param>
        /// <param name="ROWS"> The number of rows in the table </param>
        /// <param name="COLUMNS"> The number of columns in the table </param>
        public static void AddRespiratorySignalToWorksheet(Excel.Worksheet ws, string SignalName, object[,] table, int ROWS, int COLUMNS)
        {
            // Make Table with Values
            Excel.Range range = ws.Range[ws.Cells[3, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
            range.Value = table;
            ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Reflection.Missing.Value, Excel.XlYesNoGuess.xlGuess, System.Reflection.Missing.Value).Name = ws.Name;
            ws.ListObjects[ws.Name].TableStyle    = "TableStyleLight9";
            ws.Columns["A:I"].ColumnWidth         = 20;
            ws.Columns["E:H"].Hidden              = true;
            ws.Columns["B:H"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            // Add Conditional Formatting
            Excel.Range range2 = ws.Range[ws.Cells[4, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
            range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($E4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($F4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($G4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($H4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            range2.FormatConditions[1].Interior.Color = 5296274;
            range2.FormatConditions[2].Interior.Color = 255;
            range2.FormatConditions[3].Interior.Color = 65535;
            range2.FormatConditions[4].Interior.Color = 15773696;
            range2.Columns[2].NumberFormat            = "m/d/yyyy h:mm:ss.000";

            // Add Chart
            Excel.Chart chart = ((Excel.ChartObject)((Excel.ChartObjects)ws.ChartObjects()).Add(500, 100, 900, 500)).Chart;
            chart.SetSourceData(range.Columns["B:G"]);
            chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines;
            chart.ChartWizard(Source: range.Columns["B:G"], Title: SignalName, CategoryTitle: "Time", ValueTitle: SignalName);
            chart.PlotVisibleOnly = false;

            ((Excel.Series)chart.SeriesCollection(1)).ChartType   = Excel.XlChartType.xlXYScatterLinesNoMarkers;
            ((Excel.Series)chart.SeriesCollection(2)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
            ((Excel.Series)chart.SeriesCollection(3)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
            ((Excel.Series)chart.SeriesCollection(4)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
            ((Excel.Series)chart.SeriesCollection(5)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;

            ((Excel.Series)chart.SeriesCollection(2)).Format.Fill.ForeColor.RGB = 5296274;
            ((Excel.Series)chart.SeriesCollection(3)).Format.Fill.ForeColor.RGB = 255;
            ((Excel.Series)chart.SeriesCollection(4)).Format.Fill.ForeColor.RGB = 65535;
            ((Excel.Series)chart.SeriesCollection(5)).Format.Fill.ForeColor.RGB = 15773696;

            ((Excel.Series)chart.SeriesCollection(1)).Format.Line.ForeColor.RGB = 38450;
            ((Excel.Series)chart.SeriesCollection(2)).Format.Line.ForeColor.RGB = 5296274;
            ((Excel.Series)chart.SeriesCollection(3)).Format.Line.ForeColor.RGB = 255;
            ((Excel.Series)chart.SeriesCollection(4)).Format.Line.ForeColor.RGB = 65535;
            ((Excel.Series)chart.SeriesCollection(5)).Format.Line.ForeColor.RGB = 15773696;

            System.Runtime.InteropServices.Marshal.ReleaseComObject(chart);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
        }
コード例 #5
0
ファイル: ExcelExport.cs プロジェクト: Hvoroba/CassinisOval
        internal static void Export(DataGridView dataTable)
        {
            _Application excelApp = new _Excel.Application();

            Workbook wb;

            wb = excelApp.Workbooks.Add();

            Worksheet ws = wb.Worksheets[1];

            double[] arrOfX = new double[dataTable.Rows.Count - 1];
            double[] arrOfY = new double[dataTable.Rows.Count - 1];

            GetValues.FillArrays(arrOfX, arrOfY, dataTable);

            excelApp.Visible = true;

            excelApp.Cells[1, 1] = "X:";
            excelApp.Cells[1, 2] = "Y:";


            int row    = 3;
            int column = 1; //начинать заполнение необходимо с 3 строчки, т к при построении графика левая верхняя клеточка должна быть пустой

            for (int i = 0; i < arrOfX.Length; i++)
            {
                excelApp.Cells[row, column] = arrOfX[i];
                column++;
                excelApp.Cells[row, column] = arrOfY[i];
                column--;
                row++;
            }

            _Excel.Range eRange = ws.get_Range("a2", "b" + row);

            _Excel.ChartObjects eChartObjects = (_Excel.ChartObjects)ws.ChartObjects(Type.Missing);
            _Excel.ChartObject  eChartObj     = eChartObjects.Add(10, 30, 300, 300); //размеры диаграммы
            _Excel.Chart        eChart        = eChartObj.Chart;

            eChart.ChartType = _Excel.XlChartType.xlLine;
            eChart.ChartWizard(
                Source: eRange,
                Title: "Cassini Oval",
                CategoryTitle: "xAxis",
                CategoryLabels: 2,
                ValueTitle: "yAxis",
                HasLegend: false);

            eChart.SetSourceData(eRange);
        }
コード例 #6
0
        private void btnCreateChart_Click(object sender, EventArgs e)
        {
            try
            {
                Excel.Chart oChart = xlWBook.Charts.Add();


                Excel.Range rngChart = (Excel.Range)xlWSheet.get_Range("" + cmb1.Text + (int)No1.Value + "", "" + cmb2.Text + (int)No2.Value + "");
                FormatAsTable(rngChart, "Table1", "TableStyleMedium1");
                oChart.ChartWizard(rngChart, Excel.XlChartType.xl3DColumn);
                oChart.Refresh();
                //xlWBook.AcceptAllChanges();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
コード例 #7
0
ファイル: ExcelHandler.cs プロジェクト: toa-aw/campusPark
        public static void CreateChart(string filename)
        {
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.Visible = true;

            //Opens the excel file
            Excel.Workbook  excelWorkbook  = excelApplication.Workbooks.Add();
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets.get_Item(1);

            excelWorksheet.Cells[1, 1] = "Product ID";
            excelWorksheet.Cells[1, 2] = "Stock";
            excelWorksheet.Cells[2, 1] = "BB12";
            excelWorksheet.Cells[2, 2] = "15";
            excelWorksheet.Cells[3, 1] = "xy40";
            excelWorksheet.Cells[3, 2] = "18";
            excelWorksheet.Cells[4, 1] = "AX50";
            excelWorksheet.Cells[4, 2] = "23";

            //Add a char object
            Excel.Chart        myChart  = null;
            Excel.ChartObjects charts   = excelWorksheet.ChartObjects();
            Excel.ChartObject  chartObj = charts.Add(50, 50, 300, 300); //left; top; width; height
            myChart = chartObj.Chart;

            //set chart range -- cell values to be used in the graph
            Excel.Range myRange = excelWorksheet.get_Range("B1:B4");
            myChart.SetSourceData(myRange);

            //chart properties using the named properties and default parameters functionality in
            //the .Net Framework
            myChart.ChartType = Excel.XlChartType.xlLine;
            myChart.ChartWizard(Source: myRange,
                                Title: "Graph Title",
                                CategoryTitle: "Title of X axis... ",
                                ValueTitle: "Title of Y axis... ");

            excelWorkbook.SaveAs(filename);
            excelWorkbook.Close();
            excelApplication.Quit();

            ReleaseCOMObjects(excelWorksheet);
            ReleaseCOMObjects(excelWorkbook);
            ReleaseCOMObjects(excelApplication);
        }
コード例 #8
0
        static void GenerateChart(bool copyToWord = false)
        {
            var excel = new Excel.Application();

            excel.Visible = true;
            excel.Workbooks.Add();

            excel.get_Range("A1").Value2 = "Process Name";
            excel.get_Range("B1").Value2 = "Memory Usage";

            var processes = Process.GetProcesses()
                            .OrderByDescending(p => p.WorkingSet64)
                            .Take(10);
            int i = 2;

            foreach (var p in processes)
            {
                excel.get_Range("A" + i).Value2 = p.ProcessName;
                excel.get_Range("B" + i).Value2 = p.WorkingSet64;
                i++;
            }

            Excel.Range range = excel.get_Range("A1");
            Excel.Chart chart = (Excel.Chart)excel.ActiveWorkbook.Charts.Add(
                After: excel.ActiveSheet);

            chart.ChartWizard(Source: range.CurrentRegion,
                              Title: "Memory Usage in " + Environment.MachineName);

            chart.ChartStyle = 45;
            chart.CopyPicture(Excel.XlPictureAppearance.xlScreen,
                              Excel.XlCopyPictureFormat.xlBitmap,
                              Excel.XlPictureAppearance.xlScreen);

            if (copyToWord)
            {
                var word = new Word.Application();
                word.Visible = true;
                word.Documents.Add();

                word.Selection.Paste();
            }
        }
コード例 #9
0
        /// <summary>
        /// Writes an Excel worksheet containing a signal's values and Plot
        /// </summary>
        /// <param name="ws"> The Excel worksheet object </param>
        /// <param name="SignalName"> The name of the signal </param>
        /// <param name="table"> The signal values </param>
        /// <param name="ROWS"> The number of rows in the table </param>
        /// <param name="COLUMNS"> The number of columns in the table </param>
        /// <param name="color"> The color of the plot </param>
        public static void AddSignalToWorksheet(Excel.Worksheet ws, string SignalName, object[,] table, int ROWS, int COLUMNS, OxyColor color)
        {
            Excel.Range range = ws.Range[ws.Cells[3, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
            range.Value = table;
            ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Reflection.Missing.Value, Excel.XlYesNoGuess.xlGuess, System.Reflection.Missing.Value).Name = ws.Name;
            ws.ListObjects[ws.Name].TableStyle    = "TableStyleLight9";
            ws.Columns["A:I"].ColumnWidth         = 20;
            ws.Columns["B:H"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            range.Columns[2].NumberFormat = "m/d/yyyy h:mm:ss.000";

            Excel.Chart chart = ((Excel.ChartObject)((Excel.ChartObjects)ws.ChartObjects()).Add(500, 100, 900, 500)).Chart;
            chart.SetSourceData(range.Columns["B:C"]);
            chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines;
            chart.ChartWizard(Source: range.Columns["B:C"], Title: SignalName, CategoryTitle: "Time", ValueTitle: SignalName);
            ((Excel.Series)chart.SeriesCollection(1)).ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
            ((Excel.Series)chart.SeriesCollection(1)).Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(color.A, color.R, color.G, color.B));
            System.Runtime.InteropServices.Marshal.ReleaseComObject(chart);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
        }
コード例 #10
0
ファイル: Form1.cs プロジェクト: a-vodka/ics
        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = true;

            Excel.Workbook  wb    = excelApp.Workbooks.Add();
            Excel.Worksheet sheet = wb.ActiveSheet;

//add data
            sheet.Cells[1, 1] = "";
            sheet.Cells[1, 2] = "Student1";
            sheet.Cells[1, 3] = "Student2";
            sheet.Cells[1, 4] = "Student3";
            sheet.Cells[2, 1] = "Term1";
            sheet.Cells[2, 2] = "80";
            sheet.Cells[2, 3] = "65";
            sheet.Cells[2, 4] = "45";
            sheet.Cells[3, 1] = "Term2";
            sheet.Cells[3, 2] = "78";
            sheet.Cells[3, 3] = "72";
            sheet.Cells[3, 4] = "60";
            sheet.Cells[4, 1] = "Term3";
            sheet.Cells[4, 2] = "82";
            sheet.Cells[4, 3] = "80";
            sheet.Cells[4, 4] = "65";
            sheet.Cells[5, 1] = "Term4";
            sheet.Cells[5, 2] = "75";
            sheet.Cells[5, 3] = "82";
            sheet.Cells[5, 4] = "68";

            Excel.Range        chartRange = sheet.get_Range("A1", "d5");
            Excel.ChartObjects xlCharts   = (Excel.ChartObjects)sheet.ChartObjects();
            Excel.ChartObject  myChart    = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
            Excel.Chart        chartPage  = myChart.Chart;
            chartPage.ChartWizard(chartRange, Excel.XlChartType.xlColumnClustered, Title: "Diagram title");

            chartPage.Export(Application.StartupPath + @"./excel_chart_export.png", "png");
        }
コード例 #11
0
        /// <summary>
        /// Initialize Excel Application
        /// </summary>
        /// <param name="sheetname">Desired Sheetname</param>
        /// <param name="visible">Determine whether Excel-Sheet is visible or not</param>
        public static void InitializeExcel(string sheetname, bool visible = true)
        {
            // Create a new Excel Application
            _app         = new Excel.Application();
            _app.Visible = visible;

            // Create a new, empty workbook and add it to the collection returned
            _workbook = _app.Workbooks.Add(Missing.Value);
            _workbook.Worksheets.Item[1].Name = sheetname;

            // Initialize _mainChart.
            var worksheet = _workbook.Worksheets.Item[1] as
                            Microsoft.Office.Interop.Excel.Worksheet;
            var charts = worksheet.ChartObjects() as
                         Microsoft.Office.Interop.Excel.ChartObjects;
            var chartObject = charts.Add(10, 10, 800, 450) as
                              Microsoft.Office.Interop.Excel.ChartObject;

            _mainChart           = chartObject.Chart;
            _mainChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLinesNoMarkers;
            _mainChart.ChartWizard(Title: sheetname);
            _mainChartSeriesCollection = _mainChart.SeriesCollection();
        }
コード例 #12
0
        public static void WriteToExcelFile(string path, List <string[]> listasMin, List <string[]> listasMax, List <string[]> listasAvg, string[] parametros)
        {
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.Visible = false;
            Excel.Workbook  excelWorkbook  = excelApplication.Workbooks.Open(path);
            Excel.Worksheet excelWorksheet = excelWorkbook.ActiveSheet;



            //escrever dados para o excel
            int linha;
            int coluna = 600;
            int aux    = 0;

            foreach (string[] lista in listasMin)
            {
                linha = 0;

                Excel.Chart        myChart     = null;
                Excel.ChartObjects charts      = excelWorksheet.ChartObjects();
                Excel.ChartObject  chartObject = charts.Add(10, (300 * aux) + 10, 300, 300); //left, top, width, heigh
                myChart = chartObject.Chart;

                foreach (string dado in lista)
                {
                    linha++;
                    string dadoaux = dado.Replace(",", ".");
                    excelWorksheet.Cells[linha, coluna].Value = dadoaux;
                }

                //set chart range
                Excel.Range c1      = excelWorksheet.Cells[1, coluna];
                Excel.Range c2      = excelWorksheet.Cells[linha, coluna];
                Excel.Range myrange = excelWorksheet.get_Range(c1, c2);
                myChart.SetSourceData(myrange);
                //chart properties using the named parameters and default parameters functionality in the .NET
                myChart.ChartType = Excel.XlChartType.xlLine;
                myChart.ChartWizard(Source: myrange,
                                    Title: parametros[aux],
                                    CategoryTitle: "Hours",
                                    ValueTitle: "Min Values");
                coluna++;
                aux++;
            }
            aux = 0;
            coluna++;
            foreach (string[] lista in listasMax)
            {
                linha = 0;

                Excel.Chart        myChart     = null;
                Excel.ChartObjects charts      = excelWorksheet.ChartObjects();
                Excel.ChartObject  chartObject = charts.Add(320, (300 * aux) + 10, 300, 300); //left, top, width, heigh
                myChart = chartObject.Chart;

                foreach (string dado in lista)
                {
                    linha++;
                    string dadoaux = dado.Replace(",", ".");
                    excelWorksheet.Cells[linha, coluna].Value = dadoaux;
                }

                //set chart range
                Excel.Range c1      = excelWorksheet.Cells[1, coluna];
                Excel.Range c2      = excelWorksheet.Cells[linha, coluna];
                Excel.Range myrange = excelWorksheet.get_Range(c1, c2);
                myChart.SetSourceData(myrange);
                //chart properties using the named parameters and default parameters functionality in the .NET
                myChart.ChartType = Excel.XlChartType.xlLine;
                myChart.ChartWizard(Source: myrange,
                                    Title: parametros[aux],
                                    CategoryTitle: "Hours",
                                    ValueTitle: "Max Values");
                coluna++;
                aux++;
            }
            aux = 0;
            coluna++;
            foreach (string[] lista in listasAvg)
            {
                linha = 0;

                Excel.Chart        myChart     = null;
                Excel.ChartObjects charts      = excelWorksheet.ChartObjects();
                Excel.ChartObject  chartObject = charts.Add(640, (300 * aux) + 10, 300, 300); //left, top, width, heigh
                myChart = chartObject.Chart;

                foreach (string dado in lista)
                {
                    linha++;
                    string dadoaux = dado.Replace(",", ".");
                    excelWorksheet.Cells[linha, coluna].Value = dadoaux;
                }

                //set chart range
                Excel.Range c1      = excelWorksheet.Cells[1, coluna];
                Excel.Range c2      = excelWorksheet.Cells[linha, coluna];
                Excel.Range myrange = excelWorksheet.get_Range(c1, c2);
                myChart.SetSourceData(myrange);
                //chart properties using the named parameters and default parameters functionality in the .NET
                myChart.ChartType = Excel.XlChartType.xlLine;
                myChart.ChartWizard(Source: myrange,
                                    Title: parametros[aux],
                                    CategoryTitle: "Hours",
                                    ValueTitle: "Average Values");
                coluna++;
                aux++;
            }



            excelWorkbook.Save();
            excelWorkbook.Close();
            excelApplication.Quit();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
            excelWorkbook = null;
            //Don’t forget to free the memory used by excel objects
            //...
            GC.Collect();
        }
コード例 #13
0
ファイル: ExcelInterface.cs プロジェクト: secondmover/TVGL
        /// <summary>
        /// Creates a new graph in a new excel workbook. Values is a list of data series, where a series is a list of X,Y value pairs.
        /// </summary>
        /// <param name="dataSet2"></param>
        /// <param name="seriesTitle"></param>
        /// <param name="xAxis"></param>
        /// <param name="yAxis"></param>
        /// <param name="headers"></param>
        /// <param name="dataSet1"></param>
        public static void PlotDataSets(List <List <double[]> > dataSet1, List <List <double[]> > dataSet2, string seriesTitle = "",
                                        string xAxis = "", string yAxis = "", List <string> headers = null)
        {
            //Create a new excel workbook and sheet
            Excel.Application xlApp;
            Excel.Workbook    xlWorkbook;
            Excel.Worksheet   xlWorksheet;
            object            misValue = System.Reflection.Missing.Value;

            xlApp       = new Excel.Application();
            xlWorkbook  = xlApp.Workbooks.Add(misValue);
            xlWorksheet = xlWorkbook.Worksheets.get_Item(1);

            //Create Chart
            var xlCharts = (Excel.ChartObjects)xlWorksheet.ChartObjects(Type.Missing);

            for (var i = 0; i < dataSet1.Count; i++) //For each series in dataSet1
            {
                //Create a new chart and offset from previous
                var offset = 300 * (i);
                Excel.ChartObject myChart = xlCharts.Add(200, 30 + offset, 400, 300);
                Excel.Chart       chart   = myChart.Chart;
                chart.ChartType = Excel.XlChartType.xlXYScatterLines;
                Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection();

                //Add first series
                var xValues = new double[dataSet1[i].Count];
                var yValues = new double[dataSet1[i].Count];
                for (var j = 0; j < dataSet1[i].Count; j++) //For each point in series
                {
                    xValues[j] = dataSet1[i][j][0];
                    yValues[j] = dataSet1[i][j][1];
                }
                Excel.Series series1 = seriesCollection.NewSeries();
                series1.Values  = yValues;
                series1.XValues = xValues;

                //Add second series
                xValues = new double[dataSet2[i].Count];
                yValues = new double[dataSet2[i].Count];
                for (var j = 0; j < dataSet2[i].Count; j++) //For each point in series
                {
                    xValues[j] = dataSet2[i][j][0];
                    yValues[j] = dataSet2[i][j][1];
                }
                Excel.Series series2 = seriesCollection.NewSeries();
                series2.Values  = yValues;
                series2.XValues = xValues;

                var title = seriesTitle + " " + i;
                chart.ChartWizard(
                    Title: title,
                    CategoryTitle: xAxis,
                    ValueTitle: yAxis);
            }

            xlApp.Visible = true;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
        }
コード例 #14
0
 //Excel.XlChartType.xl3DColumn
 /// <summary>
 /// 给工作添加一个图标工作表
 /// </summary>
 /// <param name="xlBook">工作簿</param>
 /// <param name="dataSheet">数据工作表</param>
 /// <param name="xData">x轴数据区域:格式A1:A14</param>
 /// <param name="ydata">y轴数据区域:格式B1:E14</param>
 /// <param name="chartName">图标名称</param>
 /// <param name="xname">y轴名称</param>
 /// <param name="yname">y轴名称</param>
 /// <param name="chartType">图表类型</param>
 public static void createSingleChart(Excel.Workbook xlBook, Excel.Worksheet dataSheet, string xData, string ydata, string chartName, string xname, string yname, Excel.XlChartType chartType)
 {
     Excel.Chart xlChart   = (Excel.Chart)xlBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
     Excel.Range chartRage = dataSheet.get_Range(xData, ydata);
     xlChart.ChartWizard(chartRage, chartType, Missing.Value, Excel.XlRowCol.xlRows, 1, 1, true, chartName, xname, yname, Missing.Value);
 }
コード例 #15
0
        static void Main(string[] args)
        {
            // Create an instance of Excel and show it.
            Excel.Application xlApp = new Excel.ApplicationClass();
            xlApp.Visible = true;

            // Create new Excel Workbook with one worksheet.
            xlApp.SheetsInNewWorkbook = 1;
            Excel.Workbook wb = xlApp.Workbooks.Add(Type.Missing);

            // Set worksheet object to first worksheet in workbook.
            Excel.Worksheet ws = wb.Worksheets[1] as Excel.Worksheet;

            // Change the name of the worksheet.
            ws.Name = "MyWorksheet";

            // Fill the worksheet.
            int i;

            for (i = 1; i < 10; i++)
            {
                (ws.Cells[i, 1] as Excel.Range).Value2 = String.Format("Row {0}", i);
                (ws.Cells[i, 2] as Excel.Range).Value2 = i;
            }

            // Fill total row.
            (ws.Cells[i, 1] as Excel.Range).Value2  = "Total";
            (ws.Cells[i, 2] as Excel.Range).Formula = String.Format("=Sum(B1:B{0})", i - 1);

            // Make first column bold and draw line before total row.
            ws.get_Range(String.Format("A1:A{0}", i), Type.Missing).Font.Bold = true;
            ws.get_Range(String.Format("A{0}:B{1}", i - 1, i - 1), Type.Missing).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;

            // Create chart on separate worksheet and format the chart using the chart wizard.
            Excel.Chart ch = wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Excel.Chart;
            ch.ChartWizard(ws.get_Range(String.Format("A1:B{0}", i), Type.Missing), Excel.XlChartType.xlCylinderCol, 1,
                           Excel.XlRowCol.xlColumns, 1, 0, true, "Chart with values of each row",
                           "Rows", "Value", "Extra Title");

            // Create chart on same worksheet and format the chart using the chart wizard.
            ch = (ws.ChartObjects(Type.Missing) as Excel.ChartObjects).Add(150, 0, 400, 400).Chart;
            ch.ChartWizard(ws.get_Range(String.Format("A1:B{0}", i), Type.Missing), Excel.XlChartType.xl3DColumn, 1,
                           Excel.XlRowCol.xlColumns, 1, 0, true, "Chart with values of each row",
                           "Rows", "Value", "Extra Title");

            // Save workbook.
            try
            {
                wb.SaveAs("MyWorkbook.xls", Type.Missing, Type.Missing, Type.Missing,
                          Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
                          Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                // user cancelled save.
            }

            // Ask to print workbook.

            /*	if (MessageBox.Show("Print workbooks?", "Question", MessageBoxButtons.YesNo)==DialogResult.Yes)
             *      {
             *              wb.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
             *      }*/

            // Close the workbook.
            wb.Close(Type.Missing, Type.Missing, Type.Missing);

            // Close Excel.
            xlApp.Quit();
        }
コード例 #16
0
        public void ExportRectangle(List <string> txDataColumnc, List <string> tyProductColumnc, List <int> tyNumberColumnc, Excel.Application objExcel = null, Excel.Workbook objWorkbook = null, Excel.Worksheet objsheet = null)
        {
            List <string> txDataColumn = txDataColumnc;
            List <string> tyDataOk     = tyProductColumnc;
            List <int>    tyDataNo     = tyNumberColumnc;


            try
            {
                //设置属性标签
                objsheet.Range["A:A"].ColumnWidth       = 20; //设置宽度
                objsheet.Range["A:A"].NumberFormatLocal = "@";
                objsheet.Range["B:B"].ColumnWidth       = 20; //设置宽度
                objsheet.Range["C:C"].ColumnWidth       = 20; //设置宽度

                #region 管理人员
                int col = 1;
                objExcel.Cells[1, col]     = "日期";
                objExcel.Cells[1, col + 1] = "产品名称";
                objExcel.Cells[1, col + 2] = "数量";
                int row  = 2;   //row和 i得对应关系是row = i+2 ; i = row -2
                int temp = row;
                int cell = 0;
                for (int i = 0; i < txDataColumn.Count; i++)
                {
                    objExcel.Cells[row, col] = txDataColumn[i].ToString();
                    row++;
                }

                for (int i = 0; i < txDataColumn.Count; i++)
                {
                    if (i == 0)
                    {
                        //objExcel.Cells[row, col] = txDataColumn[i];
                    }
                    else
                    {
                        if (txDataColumn[i] != txDataColumn[i - 1])
                        {
                            cell = i - 1 + 2;
                            Excel.Range rangeChange = objsheet.Range["A" + temp, "A" + cell];
                            rangeChange.Value2 = Type.Missing;
                            rangeChange.Merge(Type.Missing);
                            rangeChange.Value2 = txDataColumn[i - 1].ToString();

                            temp = i + 2;
                        }
                    }
                    row++;
                }

                row = 2;
                for (int i = 0; i < tyDataOk.Count; i++)
                {
                    objExcel.Cells[row, col + 1] = tyDataOk[i];
                    row++;
                }
                row = 2;
                for (int i = 0; i < tyDataNo.Count; i++)
                {
                    objExcel.Cells[row, col + 2] = tyDataNo[i];
                    row++;
                }
                #endregion

                int         num      = txDataColumn.Count + 1;
                Excel.Range rangeAll = objsheet.Range["A1", "C" + num];
                rangeAll.Borders.Color       = 0;
                rangeAll.Borders.Weight      = 2;
                rangeAll.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                rangeAll.VerticalAlignment   = Excel.XlVAlign.xlVAlignCenter;

                //设置属性标签
                objsheet.Range["A:A"].ColumnWidth = 20;   //设置宽度
                objsheet.Range["B:B"].ColumnWidth = 20;   //设置宽度
                objsheet.Range["C:C"].ColumnWidth = 20;   //设置宽度

                //柱状图
                Excel.Chart xlChart2  = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
                Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[1, 1], (Excel.Range)objsheet.Cells[1 + txDataColumn.Count, 3]);
                //1-cellRange:数据源的范围,2-图表类型,3-Type.Missing,4-在图表上将列或行用作数据系列的方式,5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行,7-图表是否有图例,8、9、10-设置标题
                xlChart2.ChartWizard(cellRange,
                                     Excel.XlChartType.xlColumnClustered, //2-图表类型
                                     Type.Missing,                        //内置自动套用格式的选项编号。 可为从 1 到 10 的数字,其取值依赖于库的类型。 如果省略此参数, 则 Excel 根据库的类型和数据源选择默认值。
                                     Excel.XlRowCol.xlColumns,            //在图表上将列或行用作数据系列的方式
                                     2,                                   //第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行--这个2代表数据源的x轴由两个参数确认;可以不写,默认的就很难看
                                     1,                                   //--这个2代表数据源的x轴由1个参数确认;可以不写,默认的就很难看
                                     true,                                //图表是否有图例
                                     "每日总量统计",                            //以下都是标题
                                     Type.Missing,
                                     Type.Missing,
                                     "");

                xlChart2.ChartStyle = 201;
                xlChart2.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd);                    // 设置图表上图表元素。 为可读/写属性。
                xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);                //将图表移动到新位置。

                objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); // xlLocationAsObject,将图表嵌入到现有工作表中。
                objsheet.Shapes.Item("Chart 1").Top    = 100;                                              //调图表的位置上边距
                objsheet.Shapes.Item("Chart 1").Left   = 400;
                objsheet.Shapes.Item("Chart 1").Width  = txDataColumn.Count * 30;                          //调图表的宽度
                objsheet.Shapes.Item("Chart 1").Height = 300;                                              //调图表的高度

                //保存


                objsheet.Name      = "每日占用";
                objsheet.Tab.Color = 3394611;
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
        }
コード例 #17
0
ファイル: ToolHelper.cs プロジェクト: galatiayxh/WinformTest1
        public void Export()
        {
            //申明保存对话框
            SaveFileDialog dlg = new SaveFileDialog();

            //默然文件后缀
            dlg.DefaultExt = "xlsx ";
            //文件后缀列表
            dlg.Filter = "EXCEL文件(*.XLSX)|*.xlsx ";
            //默然路径是系统当前路径
            dlg.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
            //打开保存对话框
            if (dlg.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }
            //返回文件路径
            string fileNameString = dlg.FileName;

            //验证strFileName是否为空或值无效
            if (fileNameString.Trim() == " ")
            {
                return;
            }
            Excel.Application objExcel    = null;
            Excel.Workbook    objWorkbook = null;
            Excel.Worksheet   objsheet    = null;
            try
            {
                //申明对象
                objExcel    = new Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet    = (Excel.Worksheet)objWorkbook.ActiveSheet;
                //合格率
                objExcel.Cells[1, 1] = "开始时间";
                objExcel.Cells[1, 3] = "结束时间";
                objExcel.Cells[2, 1] = "不合格";
                objExcel.Cells[3, 1] = "合格";
                objExcel.Cells[2, 2] = tyDataPie[0];
                objExcel.Cells[3, 2] = tyDataPie[1];
                //饼图
                Excel.Range oResizeRange;
                Excel.Chart xlChart = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
                xlChart.ChartType = Excel.XlChartType.xlPie;//设置图形
                xlChart.SetSourceData(objsheet.get_Range("A2", "B3"), Excel.XlRowCol.xlColumns);
                objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, "合格率");
                objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);
                oResizeRange = (Excel.Range)objsheet.Rows.get_Item(7, Missing.Value);

                objsheet.Shapes.Item("Chart 1").Top    = 70;  //调图表的位置上边距
                objsheet.Shapes.Item("Chart 1").Left   = (float)(double)oResizeRange.Left;
                objsheet.Shapes.Item("Chart 1").Width  = 200; //调图表的宽度
                objsheet.Shapes.Item("Chart 1").Height = 150; //调图表的高度
                #region 管理人员
                int col = 6;
                objExcel.Cells[2, col]     = "用户名";
                objExcel.Cells[2, col + 1] = "合格";
                objExcel.Cells[2, col + 2] = "不合格";
                int row = 3;
                for (int i = 0; i < txDataColumn.Count; i++)
                {
                    objExcel.Cells[row, col] = txDataColumn[i];
                    row++;
                }
                row = 3;
                for (int i = 0; i < tyDataOk.Count; i++)
                {
                    objExcel.Cells[row, col + 1] = tyDataOk[i];
                    row++;
                }
                row = 3;
                for (int i = 0; i < tyDataNo.Count; i++)
                {
                    objExcel.Cells[row, col + 2] = tyDataNo[i];
                    row++;
                }
                #endregion
                //柱状图
                Excel.Chart xlChart2  = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
                Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[2, 6], (Excel.Range)objsheet.Cells[3 + txDataColumn.Count - 1, 8]);
                //1-cellRange:数据源的范围,2-图表类型,3-Type.Missing,4-在图表上将列或行用作数据系列的方式,5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行,7-图表是否有图例,8、9、10-设置标题
                xlChart2.ChartWizard(cellRange,
                                     Excel.XlChartType.xlColumnStacked, Type.Missing,
                                     Excel.XlRowCol.xlColumns, 1, 1, true,
                                     "管理人员校准情况", "用户名", "校准个数",
                                     "");
                xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);
                Excel.Range oResizeRange1 = (Excel.Range)objsheet.Rows.get_Item(1);
                Excel.Range oResizeRange2 = (Excel.Range)objsheet.Columns.get_Item(10);
                objsheet.Shapes.Item("Chart 2").Top    = (float)oResizeRange1.Top;          //调图表的位置上边距--1行的高度
                objsheet.Shapes.Item("Chart 2").Left   = (float)(double)oResizeRange2.Left; //调图表的位置左边距--10列的宽度
                objsheet.Shapes.Item("Chart 2").Width  = 300;                               //调图表的宽度
                objsheet.Shapes.Item("Chart 2").Height = 200;                               //调图表的高度
                //保存文件
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                //关闭Excel应用
                if (objWorkbook != null)
                {
                    objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                }
                if (objExcel.Workbooks != null)
                {
                    objExcel.Workbooks.Close();
                }
                if (objExcel != null)
                {
                    objExcel.Quit();
                }
                objsheet    = null;
                objWorkbook = null;
                objExcel    = null;
            }
        }
コード例 #18
0
        /// <summary>
        /// 柱形图
        /// </summary>
        /// <param name="txDataColumnc"></param>
        /// <param name="tyProductColumnc"></param>
        /// <param name="tyNumberColumnc"></param>
        public void ExportRectangle(List <string> txDataColumnc, List <string> tyProductColumnc, List <int> tyNumberColumnc, string DateBegin, string DateEnd)
        {
            Open = true;
            intExcelTempIndex++;
            List <string> txDataColumn = txDataColumnc;
            List <string> tyDataOk     = tyProductColumnc;
            List <int>    tyDataNo     = tyNumberColumnc;

            Excel.Application objExcel    = null;//创建一个excel的实例
            Excel.Workbook    objWorkbook = null;
            Excel.Worksheet   objsheet    = null;

            string fileNameString = "";

            try
            {
                //申明对象
                objExcel    = new Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet    = (Excel.Worksheet)objWorkbook.ActiveSheet;

                //设置属性标签
                objsheet.Range["A:A"].ColumnWidth       = 20; //设置宽度
                objsheet.Range["A:A"].NumberFormatLocal = "@";
                objsheet.Range["B:B"].ColumnWidth       = 20; //设置宽度
                objsheet.Range["C:C"].ColumnWidth       = 20; //设置宽度

                #region 管理人员
                int col = 1;
                objExcel.Cells[1, col]     = "日期";
                objExcel.Cells[1, col + 1] = "产品名称";
                objExcel.Cells[1, col + 2] = "数量";
                int row  = 2;   //row和 i得对应关系是row = i+2 ; i = row -2
                int temp = row;
                int cell = 0;
                for (int i = 0; i < txDataColumn.Count; i++)
                {
                    objExcel.Cells[row, col] = txDataColumn[i].ToString();
                    row++;
                }

                for (int i = 0; i < txDataColumn.Count; i++)
                {
                    if (i == 0)
                    {
                        //objExcel.Cells[row, col] = txDataColumn[i];
                    }
                    else
                    {
                        if (txDataColumn[i] != txDataColumn[i - 1])
                        {
                            cell = i - 1 + 2;
                            Excel.Range rangeChange = objsheet.Range["A" + temp, "A" + cell];
                            rangeChange.Value2 = Type.Missing;
                            rangeChange.Merge(Type.Missing);
                            rangeChange.Value2 = txDataColumn[i - 1].ToString();

                            temp = i + 2;
                        }
                    }
                    row++;
                }

                row = 2;
                for (int i = 0; i < tyDataOk.Count; i++)
                {
                    objExcel.Cells[row, col + 1] = tyDataOk[i];
                    row++;
                }
                row = 2;
                for (int i = 0; i < tyDataNo.Count; i++)
                {
                    objExcel.Cells[row, col + 2] = tyDataNo[i];
                    row++;
                }
                #endregion

                int         num      = txDataColumn.Count + 1;
                Excel.Range rangeAll = objsheet.Range["A1", "C" + num];
                rangeAll.Borders.Color       = 0;
                rangeAll.Borders.Weight      = 2;
                rangeAll.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                rangeAll.VerticalAlignment   = Excel.XlVAlign.xlVAlignCenter;



                //柱状图
                Excel.Chart xlChart2  = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
                Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[1, 1], (Excel.Range)objsheet.Cells[1 + txDataColumn.Count, 3]);
                //1-cellRange:数据源的范围,2-图表类型,3-Type.Missing,4-在图表上将列或行用作数据系列的方式,5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行,7-图表是否有图例,8、9、10-设置标题
                xlChart2.ChartWizard(cellRange,
                                     Excel.XlChartType.xlColumnClustered,         //2-图表类型
                                     Type.Missing,                                //内置自动套用格式的选项编号。 可为从 1 到 10 的数字,其取值依赖于库的类型。 如果省略此参数, 则 Excel 根据库的类型和数据源选择默认值。
                                     Excel.XlRowCol.xlColumns,                    //在图表上将列或行用作数据系列的方式
                                     2,                                           //第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行--这个2代表数据源的x轴由两个参数确认;可以不写,默认的就很难看
                                     1,                                           //--这个2代表数据源的x轴由1个参数确认;可以不写,默认的就很难看
                                     true,                                        //图表是否有图例
                                     "每日总量统计(" + DateBegin + "-" + DateEnd + ")", //以下都是标题
                                     Type.Missing,
                                     Type.Missing,
                                     "");

                xlChart2.ChartStyle = 201;
                xlChart2.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd);                                                                          // 设置图表上图表元素。 为可读/写属性。
                xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);                                                                      //将图表移动到新位置。

                objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);                                                       // xlLocationAsObject,将图表嵌入到现有工作表中。
                objsheet.Shapes.Item("Chart 1").Top    = 100;                                                                                                    //调图表的位置上边距
                objsheet.Shapes.Item("Chart 1").Left   = 400;
                objsheet.Shapes.Item("Chart 1").Width  = objsheet.Shapes.Item("Chart 1").Width = txDataColumn.Count * 100 > 400 ? txDataColumn.Count * 30 : 400; //调图表的宽度
                objsheet.Shapes.Item("Chart 1").Height = 300;                                                                                                    //调图表的高度

                //保存
                objsheet.Name      = "每日总量统计" + intExcelTempIndex.ToString();
                objsheet.Tab.Color = Excel.XlThemeColor.xlThemeColorLight1;
                fileNameString     = Application.StartupPath + "\\" + objsheet.Name + ".xlsx";

                objExcel.DisplayAlerts          = false;
                objExcel.AlertBeforeOverwriting = false;
                //保存文件
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show("该文件已打开,请关闭后重试!", "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                Open = false;
                return;
            }
            finally
            {
                //确认进程关闭
                if (objWorkbook != null)
                {
                    objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                }
                if (objExcel.Workbooks != null)
                {
                    objExcel.Workbooks.Close();
                }
                if (objExcel != null)
                {
                    objExcel.Quit();
                }
                // 安全回收进程
                var aa = System.GC.GetGeneration(objExcel);

                objsheet    = null;
                objWorkbook = null;
                objExcel    = null;

                if (Open == true)
                {
                    Process.Start(fileNameString);
                }
            }
        }
コード例 #19
0
ファイル: 图形.cs プロジェクト: galatiayxh/BaseLayer
        private void button1_Click(object sender, EventArgs e)
        {
            //申明保存对话框
            SaveFileDialog dlg = new SaveFileDialog();

            //默然文件后缀
            dlg.DefaultExt = "xlsx ";
            //文件后缀列表
            dlg.Filter = "EXCEL文件(*.XLSX)|*.xlsx ";
            //默然路径是系统当前路径
            dlg.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
            //打开保存对话框
            if (dlg.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }
            //返回文件路径
            string fileNameString = dlg.FileName;

            //验证strFileName是否为空或值无效
            if (fileNameString.Trim() == " ")
            {
                return;
            }
            Excel.Application objExcel = new Excel.Application();//创建一个excel的实例

            Excel.Workbook objWorkbook = null;

            Excel.Worksheet objsheet = null;


            //Excel.Sheets objsheets = objExcel.Workbooks.Item[1].Worksheets;
            //objsheets.Item[1] = "";
            //Excel.Worksheet ws = (Excel.Worksheet)objExcel.Worksheets.get_Item(1);
            //ws.Name = "狐狸!";



            // Excel.Sheets objsheets = objExcel.Sheets;
            //var ss =  objsheets.Item[1];
            // var aa = (Excel.Worksheet)ss;
            // aa.Name = "dasdasdasd";
            //objsheets["Sheet1"].Nasdasda = "11";


            //objsheet = (Excel.Worksheet)objsheets.get_Item(objsheets.Count);

            //worksheet.Name = "sadas ";
            ////objsheets.get_Item(1


            //objWorkbooks = objExcel.Workbooks;
            ////oBooks.Open(sTemplate, 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);
            //objWorkbook = objWorkbooks.get_Item(1);
            //objsheets = objWorkbook.Worksheets;

            //objsheet = (Excel.Worksheet)objsheets.get_Item(1);
            ////命名该sheet
            //objsheet.Name = "Sheet1";

            try
            {
                //申明对象
                objExcel    = new Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet    = (Excel.Worksheet)objWorkbook.ActiveSheet;
                //合格率
                objExcel.Cells[1, 1] = "客户";
                objExcel.Cells[1, 2] = "数量";
                objExcel.Cells[1, 3] = "占比";

                objExcel.Cells[2, 1] = "不合格";
                objExcel.Cells[3, 1] = "合格";
                objExcel.Cells[2, 2] = tyDataPie[0];
                objExcel.Cells[3, 2] = tyDataPie[1];
                objExcel.Cells[2, 3] = @"=B2 / B4";
                objExcel.Cells[3, 3] = @"=B3 / B4";

                //求和
                Excel.Range rangesummary73 = objsheet.Range["B4"];//--ActiveCell = rangesummary110
                rangesummary73.Formula             = "=SUM(R[-2]C:R[-1]C)";
                rangesummary73.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)";
                //            Range("A2:A3,C2:C3").Select
                //Range("C2").Activate
                //ActiveSheet.Shapes.AddChart2(251, xlPie).Select
                //ActiveChart.SetSourceData Source:= Range("Sheet1!$A$2:$A$3,Sheet1!$C$2:$C$3")
                //ActiveChart.SetElement(msoElementDataLabelBestFit)

                //            Excel.Range rangesummary5 = objsheet.Range["A2:A3", "C2:C3"];


                //设置百分比格式
                Excel.Range rangesummary4 = objsheet.Range["C2", "C3"];
                //Range("C2:C3").Select;
                rangesummary4.NumberFormatLocal = "0.00%";
                //饼图

                //新建一个饼图
                Excel.Chart xlChart = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
                xlChart.ChartType = Excel.XlChartType.xlPie;                                         //设置图形
                xlChart.SetSourceData(objsheet.get_Range("A1:A3, C1:C3"), Excel.XlRowCol.xlColumns); //两种方法都可以
                                                                                                     // xlChart.SetSourceData(objsheet.Range["A1:A3", "C1:C3"], Excel.XlRowCol.xlColumns);


                //加border和居中设置
                Excel.Range rangesummary110 = objsheet.Range["A1", "C3"];
                rangesummary110.Borders.Color       = 0;
                rangesummary110.Borders.Weight      = 2;
                rangesummary110.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //设置属性标签
                xlChart.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd);  //数据标签
                xlChart.SetElement(MsoChartElementType.msoElementLegendBottom);         //设为底部显示
                xlChart.SetElement(MsoChartElementType.msoElementChartTitleAboveChart); //设置标题
                objsheet.Range["F:F"].ColumnWidth = 20.5;                               //设置宽度
                xlChart.ChartTitle.Text           = "客户占比(2020.3.23-3.28)";



                //objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, "合格率");//xlLocationAutomatic :Excel 控制图表位置。
                objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); // xlLocationAsObject,将图表嵌入到现有工作表中。

                // oResizeRange = (Excel.Range)objsheet.Rows.get_Item(7, Missing.Value);

                objsheet.Shapes.Item("Chart 1").Top    = 150; //调图表的位置上边距
                objsheet.Shapes.Item("Chart 1").Left   = 10;
                objsheet.Shapes.Item("Chart 1").Width  = 200; //调图表的宽度
                objsheet.Shapes.Item("Chart 1").Height = 250; //调图表的高度



                ///////////////////////////////////////////////////////////////



                #region 管理人员
                int col = 6;
                objExcel.Cells[2, col]     = "日期";
                objExcel.Cells[2, col + 1] = "产品名称";
                objExcel.Cells[2, col + 2] = "数量";
                int row = 3;
                for (int i = 0; i < txDataColumn.Count; i++)
                {
                    objExcel.Cells[row, col] = txDataColumn[i];
                    row++;
                }
                row = 3;
                for (int i = 0; i < tyDataOk.Count; i++)
                {
                    objExcel.Cells[row, col + 1] = tyDataOk[i];
                    row++;
                }
                row = 3;
                for (int i = 0; i < tyDataNo.Count; i++)
                {
                    objExcel.Cells[row, col + 2] = tyDataNo[i];
                    row++;
                }
                #endregion
                //假定要合并excel文件中第2行的1~3列,并且显示黑色边框7a686964616fe4b893e5b19e31333264656666
                //objExcel.ActiveSheet.Columns[7] = 5;
                Microsoft.Office.Interop.Excel.Range rangesummary1 = objsheet.Range["F3", "F5"];
                Microsoft.Office.Interop.Excel.Range rangesummary2 = objsheet.Range["F6", "F8"];
                Microsoft.Office.Interop.Excel.Range rangesummary3 = objsheet.Range["F9", "F12"];
                Excel.Range rangesummary120 = objsheet.Range["F2", "H12"];
                rangesummary120.Borders.Color       = 0;
                rangesummary120.Borders.Weight      = 2;
                rangesummary120.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                rangesummary120.VerticalAlignment   = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;


                rangesummary1.Value2 = Type.Missing;
                rangesummary1.Merge(Type.Missing);
                rangesummary1.Value2 = "2020-03-23";


                rangesummary2.Value2 = Type.Missing;
                rangesummary2.Merge(Type.Missing);
                rangesummary2.Value2 = "2020-03-24";


                rangesummary3.Value2 = Type.Missing;
                rangesummary3.Merge(Type.Missing);
                rangesummary3.Value2 = "2020-03-25";


                //柱状图
                Excel.Chart xlChart2  = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
                Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[2, 6], (Excel.Range)objsheet.Cells[3 + txDataColumn.Count - 1, 8]);
                //1-cellRange:数据源的范围,2-图表类型,3-Type.Missing,4-在图表上将列或行用作数据系列的方式,5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行,7-图表是否有图例,8、9、10-设置标题
                xlChart2.ChartWizard(cellRange,
                                     Excel.XlChartType.xlColumnClustered, //2-图表类型
                                     Type.Missing,                        //内置自动套用格式的选项编号。 可为从 1 到 10 的数字,其取值依赖于库的类型。 如果省略此参数, 则 Excel 根据库的类型和数据源选择默认值。
                                     Excel.XlRowCol.xlColumns,            //在图表上将列或行用作数据系列的方式
                                     2,                                   //第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行--这个2代表数据源的x轴由两个参数确认;可以不写,默认的就很难看
                                     1,                                   //--这个2代表数据源的x轴由1个参数确认;可以不写,默认的就很难看
                                     true,                                //图表是否有图例
                                     "每日总量统计",                            //以下都是标题
                                     Type.Missing,
                                     Type.Missing,
                                     "");
                xlChart2.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd);
                xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);

                Excel.Range oResizeRange1 = (Excel.Range)objsheet.Rows.get_Item(1);
                Excel.Range oResizeRange2 = (Excel.Range)objsheet.Columns.get_Item(10);
                objsheet.Shapes.Item("Chart 2").Top    = (float)oResizeRange1.Top;          //调图表的位置上边距--1行的高度
                objsheet.Shapes.Item("Chart 2").Left   = (float)(double)oResizeRange2.Left; //调图表的位置左边距--10列的宽度
                objsheet.Shapes.Item("Chart 2").Width  = 300;                               //调图表的宽度
                objsheet.Shapes.Item("Chart 2").Height = 200;                               //调图表的高度

                //保存文件
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                ////关闭Excel应用
                //if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                //if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                //if (objExcel != null) objExcel.Quit();
                //objsheet = null;
                //objWorkbook = null;
                //objExcel = null;
                objsheet.Name      = "牙模盒使用记录";
                objsheet.Tab.Color = Excel.XlThemeColor.xlThemeColorLight1;
                ClosePro(fileNameString, objExcel, objWorkbook);
                System.Diagnostics.Process.Start(fileNameString);
            }
        }