C# Worksheet.ChartObjects方法代码示例

        static void DrawCombinedLineChart(IEnumerable<Worksheet> source, Worksheet dest, string SeriesName, double maximum, double minimum)
        {
            Excel.ChartObjects xlCharts = (Excel.ChartObjects)dest.ChartObjects(Type.Missing);
            Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 800, 400);
            Excel.Chart chartPage = myChart.Chart;
            myChart.Select();

            chartPage.ChartType = Excel.XlChartType.xlXYScatterLines;
            Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
            Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
            Excel.Axis xAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
            xAxis.MaximumScale = maximum;
            //xAxis.LogBase = 10;
            Excel.Axis yAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
            yAxis.MaximumScale = 1;

            foreach (var sheet in source)
            {
                Excel.Series series1 = seriesCollection.NewSeries();
                series1.Name = sheet.Name;
                series1.XValues = sheet.UsedRange.get_Range("A:A");
                series1.Values = sheet.UsedRange.get_Range("B:B");
                series1.Smooth = true;
                series1.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
            }
        }

    //function to create charts, can be called for multiple charts
    static void chart(Worksheet thisWorkSheet,string x, string y, string name,string holder)
    {
        ChartObjects newCharts = (ChartObjects)thisWorkSheet.ChartObjects(Type.Missing);//creates a chart object
        ChartObject myChart = (ChartObject)newCharts.Add(10, 80, 1350, 550);//position of chart(pos X, pos y, width, height)
        Chart chartPage = myChart.Chart;
        Range chartRange = thisWorkSheet.get_Range(x,y);
        chartPage.Rotation = 0;
        chartPage.ChartType = XlChartType.xl3DColumnClustered;
        Axis xAxis = (Axis)chartPage.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
        xAxis.TickLabelPosition =XlTickLabelPosition.xlTickLabelPositionNone;
        int startCol = 3;
        int startRow = 1;
        int maxRow = 2;
        int maxCol = 14; //With this value the loop below will iterate until column 9 (inclusive)
        for (int i = startCol; i <= maxCol; i++)
        {
            Range holder1 = thisWorkSheet.Cells[1, i];
            Range holder2 = thisWorkSheet.Cells[2, i];
            Range holder3 = thisWorkSheet.get_Range(holder1, holder2);
            bool check = false;
            for (int j = startRow; j <= maxRow; j++) {

                Range currentRange = (Range)thisWorkSheet.Cells[j, i];//iterates through the data in the excel document
                if (currentRange.Value2 != null)
                {
                    check = true;
                    string curVal = currentRange.Value2.ToString();
                    //Console.WriteLine("\n :loading value "+i+" part "+j);
                }
            }
            //simple check statement to add more colums.
            if (check)
            {
                //Series newSeries;
                SeriesCollection seriesCollect = chartPage.SeriesCollection();
                //chartPage.Axes().text += holder1;
                Series seriesI= seriesCollect.NewSeries();
                seriesI.XValues = holder1;
                seriesI.Values = holder2;
                seriesI.HasDataLabels = true;
                seriesI.HasLeaderLines = true;
                //Console.WriteLine (xAxis.TickLabels.Name);
                seriesI.Name = holder1.Value;

            }
        }
        //chartPage.SetSourceData(chartRange, Type.Missing);

        chartPage.HasTitle = true;//makes the chart have a title
        chartPage.ChartTitle.Text = "Sales Data, Week of "+thisWorkSheet.get_Range("a2","a2").Value;//title of the chart
        chartPage.ChartTitle.Font.Size = 45;//sets the font size of the title
        chartPage.ChartTitle.Font.Color = XlRgbColor.rgbGoldenrod;//color of title
        chartPage.ChartStyle = 42;//style of chart

        //EVERYTHING THAT HAS AN EFFECT ON THE CHART SHOULD HAPPEN BEFORE THIS!
        chartPage.Export(loc + name +holder+ ".jpg", "JPG", false);//takes the newly generated chart and saves it as a jpg
        //THIS IS BASICALLY THE RETURN STATEMENT  /\  DO NOT DELETE!
        //      (not really but it save the immage)
    }

        private void PopulateSummaryChart(Worksheet xlWorkSheet, string title)
        {
            ChartObjects xlCharts = (ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            ChartObject myChart = (ChartObject)xlCharts.Add(200, 50, 500, 450);
            Chart chartPage = myChart.Chart;
            chartPage.ChartType = XlChartType.xlColumnClustered;
            chartPage.HasLegend = false;
            chartPage.HasTitle = true;
            chartPage.ChartTitle.Text = String.Format("The dependence of {0} as {1}", this.DependentTitle, title);

            Axis xAxis = (Axis)chartPage.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
            xAxis.HasTitle = true;
            xAxis.AxisTitle.Text = "Attributes";

            Axis yAxis = (Axis)chartPage.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
            yAxis.HasTitle = true;
            yAxis.AxisTitle.Text = this.DependentTitle;

            Range chartRange = xlWorkSheet.get_Range("A2", String.Format("B{0}", this.Independents.Count + 1));
            chartPage.SetSourceData(chartRange, XlRowCol.xlColumns);

            Series series = (Series)chartPage.SeriesCollection(1);
            series.InvertIfNegative = true;
            series.Format.Fill.ForeColor.RGB = Microsoft.VisualBasic.Information.RGB(0, 0, 250);
            series.InvertColor = Microsoft.VisualBasic.Information.RGB(250, 0, 0);
        }

        private Chart CreateChartHeader(Worksheet xlWorkSheet, string independentTitle, double? pearson, double? spearman)
        {
            ChartObjects xlCharts = (ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            ChartObject myChart = (ChartObject)xlCharts.Add(200, 50, 500, 450);
            Chart chartPage = myChart.Chart;
            chartPage.ChartType = XlChartType.xlXYScatter;
            chartPage.HasLegend = false;
            chartPage.HasTitle = true;
            chartPage.ChartTitle.Text = String.Format("The dependence of {0} on {1}", this.DependentTitle, independentTitle);

            if (pearson.HasValue)
            {
                chartPage.ChartTitle.Text += "\nPearson: " + pearson.Value.ToString("0.000");
            }

            if (spearman.HasValue)
            {
                chartPage.ChartTitle.Text += "\nSpearman: " + spearman.Value.ToString("0.000");
            }

            Axis xAxis = (Axis)chartPage.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
            xAxis.HasTitle = true;
            xAxis.AxisTitle.Text = independentTitle;

            Axis yAxis = (Axis)chartPage.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
            yAxis.HasTitle = true;
            yAxis.AxisTitle.Text = this.DependentTitle;

            return chartPage;
        }

        //public void ExportReport(System.Data.DataTable dataTableRpt,
        //    System.Data.DataTable dataTableRptAvilability, List<ReportColumn> ColumnList, string PivotAlias, double Left, double Top)
        //{
        //    System.Data.DataTable dtPivot = new System.Data.DataTable();

        //    List<ReportColumn> usedColumnList = EntityGeneric.CloneList<ReportColumn>(ColumnList);

        //    System.Data.DataTable dtTempPivot = PivotTable.GetInversedDataTable(dataTableRpt, "DataMember", "Username", "Duration", "0", true);

        //    dtTempPivot.PrimaryKey = new DataColumn[] { dtTempPivot.Columns["Username"] };
        //    dataTableRptAvilability.PrimaryKey = new DataColumn[] { dataTableRptAvilability.Columns["Username"] };

        //    dtTempPivot.Columns.Add(GenerateTotalColumn(usedColumnList, dtTempPivot));

        //    dtPivot = dtTempPivot.Copy();
        //    dtPivot.Merge(dataTableRptAvilability);

        //    AddSheet("PivotChart");

        //    CreateChart(dtPivot, GetSheet("PivotChart"), PivotAlias, usedColumnList, Left, Top);
        //}

        public void CreateChart(System.Data.DataTable pivotDataRange, Worksheet targetSheet, string chartName, List<ReportColumn> usedColumnList, double Left, double Top)
        {
            ChartObjects _ChartObjects = (ChartObjects)(targetSheet.ChartObjects(Type.Missing));
            ChartObject _ChartObject = _ChartObjects.Add(Left, Top, 1200, 400);
            Chart _Chart = _ChartObject.Chart;

            _Chart.HasTitle = true;
            _Chart.ChartTitle.Text = chartName;
            _Chart.ChartTitle.Font.Size = 12;
            _Chart.Legend.Position = XlLegendPosition.xlLegendPositionBottom;

            SeriesCollection seriesCollection;
            seriesCollection = (SeriesCollection)_Chart.SeriesCollection(Type.Missing);

            foreach (ReportColumn cl in usedColumnList)
            {
                if (cl.ChartXAxis)
                    continue;

                Series series = seriesCollection.NewSeries();

                series.Name = cl.DisplayMember;
                series.XValues = pivotDataRange.AsEnumerable().Select(s => s.Field<string>("Username")).ToArray<string>();
                series.Values = ConvertDTColumn2Array(pivotDataRange, cl.DataMember);
                series.ChartType = (XlChartType)cl.xlSeriesChartType;

                if (cl.xlSeriesLabelListOrientation != 0)
                {
                    series.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, false,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                    series.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;

                    foreach (Point point in (Points)series.Points())
                    {
                        point.DataLabel.Orientation = (Microsoft.Office.Core.XlChartOrientation)cl.xlSeriesLabelListOrientation;
                    }

                    foreach (DataLabel label in (DataLabels)series.DataLabels())
                    {
                        //label.Orientation = (Microsoft.Office.Core.XlChartOrientation)cl.xlSeriesLabelListOrientation;
                        label.Position = (XlDataLabelPosition)cl.xlSeriesLabelListPosition;
                        label.Top = cl.xlSeriesLabelListTop;
                    }
                }
            }

            usedColumnList.Clear();
        }

        private static void fillSheet(QueryReport queryReport, Worksheet sheetReport)
        {
            object misval = System.Reflection.Missing.Value;

            string reportName = Constantes.getMessage(queryReport.Name);

            // DataSet que contiene el resultado de la query del report

            QueryReportDatos qd = new QueryReportDatos();

            DataSet ds = qd.ExecuteQueryReport(queryReport);

            // Rellenamos la hoja del Excel con los datos del Report

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                // Rellenamos el nombre de las columnas

                int columna = 0;
                int[] tamMax = new int[ds.Tables[0].Rows[0].ItemArray.Length];
                int[] tamActual = new int[ds.Tables[0].Rows[0].ItemArray.Length];

                foreach (DataColumn column in ds.Tables[0].Columns)
                {
                    sheetReport.Cells[2, (1 + columna)] = Constantes.getMessage(column.ColumnName);
                    tamActual[columna] = Constantes.getMessage(column.ColumnName).Length;
                    if (tamMax[columna] < tamActual[columna])
                    {
                        tamMax[columna] = tamActual[columna];
                    }
                    columna++;
                }

                // Rellenamos la tabla de datos

                for (int f = 0; f < ds.Tables[0].Rows.Count; f++)
                {
                    for (int c = 0; c < ds.Tables[0].Rows[0].ItemArray.Length; c++)
                    {
                        sheetReport.Cells[(3 + f), (1 + c)] = ds.Tables[0].Rows[f].ItemArray[c];
                        tamActual[c] = ds.Tables[0].Rows[f].ItemArray[c].ToString().Length;
                        if (tamMax[c] < tamActual[c])
                        {
                            tamMax[c] = tamActual[c];
                        }
                    }
                }
                for (int i = 0; i < tamMax.Length; i++)
                {
                    ((Range)sheetReport.Cells[1, 1 + i]).EntireColumn.ColumnWidth = tamMax[i] + 5;
                }

                // Rellenamos el gráfico del informe
                ChartObjects chartObjects = (ChartObjects)(sheetReport.ChartObjects(Type.Missing));
                ChartObject myChart = (ChartObject)chartObjects.Item(1);
                Chart chartPage = myChart.Chart;
                Range chartRange = Report.getRangeFrom(queryReport, sheetReport, ds);
                chartPage.SetSourceData(chartRange, misval);

                object categoryTitle = misval, valueTitle = misval;

                // Comprobamos si hay que rellenar los textos de los ejes y/o series de datos, para el tipo de grafico a generar en el informe
                if (queryReport.HasAxis)
                {
                    int categoryCol = Report.convertLetterToIndex(queryReport.Range_X.Substring(0, 1));
                    categoryTitle = (String)(sheetReport.Cells[2, categoryCol] as Range).Value2;

                    int valueCol = Report.convertLetterToIndex(queryReport.Range_Y);
                    if (valueCol == categoryCol + 1)
                    {
                        // 1 serie de datos, pintamos el otro eje directamente.
                        valueTitle = (String)(sheetReport.Cells[2, valueCol] as Range).Value2;
                    }
                }

                chartPage.ChartWizard(chartRange, (XlChartType)Enum.Parse(typeof(XlChartType), queryReport.Char_Type, true),
                                          misval, misval, misval, misval, misval, reportName, categoryTitle, valueTitle, misval);
            }
            else
            {
                queryReport.Description = "EMPTY";
            }

            // Rellenamos la descripción del informe
            fillDescription(queryReport, sheetReport);
        }

        private static void CreateUsageSheet(Worksheet ws, Version[] knownVersions, ref int pos, ref int chartPos, ReadOnlyCollection<UsageTable.Row> data, string dateFormat, string title)
        {
            ws.Cells[pos++, 1] = title;
            int startpos = pos;
            ws.Cells[pos, 1] = "Date";
            for (int i = 0; i < knownVersions.Length; i++)
                ws.Cells[pos, 2 + i] = knownVersions[i].ToString();
            ws.Cells[pos, 2 + knownVersions.Length] = "Other";
            ws.Columns["A"].ColumnWidth = 10;
            pos++;

            object[,] excelData = new object[data.Count, 2 + knownVersions.Length];
            for (int i = 0; i < data.Count; i++)
            {
                var row = data[i];
                if (dateFormat != null)
                    excelData[i, 0] = "'" + row.Date.ToString(dateFormat);
                else
                    excelData[i, 0] = row.Date;
                for (int j = 0; j < row.Values.Length; j++)
                    excelData[i, j + 1] = row.Values[j];
            }

            ws.Cells[pos, 1].Resize[data.Count, 2 + knownVersions.Length].Value = excelData;
            pos += data.Count;

            var dataRange = ws.Cells[startpos, 1].Resize[data.Count + 1, 2 + knownVersions.Length];

            ChartObjects chartObjects = (ChartObjects)ws.ChartObjects();
            ChartObject newChartObject = chartObjects.Add(250, chartPos, 650, 350);
            chartPos += 400;
            newChartObject.Chart.ChartWizard(dataRange, XlChartType.xlAreaStacked,
                                             PlotBy: XlRowCol.xlColumns,
                                             Title: title);

            pos += 1;
        }