如何使用c＃增加excel中的图表数据范围？ [英] How to increase the chart data range in excel using c#?

问题描述

我对使用c＃代码增加excel中的图表数据范围存有疑问，请查看附图以供参考。 
$


我'当excel更新新列时，我试图自动增加图表图表范围的大小，请建议我这样做，假设FW28，FW29是程序新添加的列但是当我更新excel时，图表应该增加图表
范围FW28，FW29，但其静止图仅显示FW27。请查看我尝试过的以下代码。

I have a doubt on increasing chart data range in excel using c# code , please check the attached image for your reference. 

I'm trying to increase the size of graph chart range automatically whenever excel updates new column , please suggest me to do that , Assume that  the FW28,FW29 are newly added columns by program but as i update excel , the graph should increase the chart range FW28,FW29 ,but its still graph shows upto FW27 only. please check the  below code which i have tried .

Excel.Application app = new Excel.Application();
           // Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel._Worksheet sheet;
            Excel.ChartObject chartObj;
            Excel._Chart chart;
            Excel.Series series;
            object useDefault = Type.Missing;
            Excel.Workbook book;
            Excel.ChartObjects grph;
         
            xlWorkBook = app.Workbooks.Open(@"E:\\test\\DTS-CODE");//Open the work book
            sheet = xlWorkBook.Sheets["Trend_NCM"];//Select the sheet the chart is on
            char oldformula , newformula;
            long i, s;
            Excel.Range orng = null;
            string[] stmp, sbase;
            string cha, rta;
            string startingpoint, endpoint;
            chart = sheet.ChartObjects("Chart 1").chart;           
            chart.PlotArea.Select();

            ChartObjects chartObjs = (ChartObjects)sheet.ChartObjects();
            ChartObject chartObj1;
            Chart xlChart = sheet.ChartObjects("Chart 1").chart;
// tried different methods to increase the chart range . 
            Range rg = sheet.get_Range("A2", "AP7");
            chart.SetSourceData(sheet.Range["$A$2:$AP$7"], Excel.XlRowCol.xlRows);
            chart.SetSourceData(sheet.Range["$A$2:$AP$7"], Excel.XlRowCol.xlColumns);
            chart.SetSourceData(rg, Excel.XlRowCol.xlColumns);

            Range xValues = sheet.Range["A2", "A7"];
            Range values = sheet.Range["A2", "A7"];

            SeriesCollection seriesCollection = xlChart.SeriesCollection();

            Series series1 = seriesCollection.NewSeries();
            series1.XValues = xValues;
            series1.Values = values;
//another method i'm trying to impliment for the same work,but the error at Excel.Range and offset.
            int q;
            int r=0;
            int n;
            int p1;
            int p2;
            int p3;

           Excel.Range rng ;
            Excel.Range ax ;
    
            for (n = 1; n < chart.SeriesCollection().count; n++ )
            {
                for(q=1;q<chart.SeriesCollection(n).Formula;q++)
                {
                    if ((chart.SeriesCollection(n).Formula.Substring((q - 1), 1) == ","))
                    {
                        r = (r + 1);
                        if ((r == 1))
                        {
                            p1 = (q + 1);
                        }

                        if ((r == 2))
                        {
                            p2 = q;
                        }

                        if ((r == 3))
                        {
                            p3 = q;
                        }
                    }
                    rng = Excel.Range(chart.SeriesCollection(n).Formula.Substring(p2, (p3 - (p2 - 1))));
                   
                    rng = Range(rng, rng.Offset(0, 1));
 chart.SeriesCollection(n).Values = rng;
                    // Updates axis
                    ax = Excel.Range(ActiveChart.SeriesCollection(n).Formula.Substring((p1 - 1), (p2 - p1)));
                    ax = Excel.Range(ax, ax.Offset(0, 1));
                    chart.SeriesCollection(n).XValues = ax;

推荐答案

你好，

由于这是用C＃实现的更多Excel问题，你的问题最好在Excel for developer论坛。如果您喜欢我自己或其他主持人可以将此问题移至该论坛。

Since this is more of an Excel question implemented with C# your question would be best seen in the Excel for developer forum. If you like myself or another moderator can move this question to that forum.

这篇关于如何使用c＃增加excel中的图表数据范围？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！