none
Excel chart for multiple segregated column using C# RRS feed

  • 问题

  • Hello 

     

    I want to create excel chart for multiple columns which are segregated. Following is my code for single range

     

    xlRange = xlSheetScript.get_Range("B6", "B400");

    xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineStacked, Type.Missing,

    Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

    "Daily", Type.Missing, Type.Missing, Type.Missing);

     

     

    I want to include B6:B400, D6Big Smile400 and G6:G400 ranges also in the chart. (only 3 Columns)

     

    Please let me know how can i do that.

     

    Thanks.

     

    2007年9月4日 14:52

答案

  • Hi,

     

    Try these codes. It works for me.

    Code Snippet

                Excel.Series xlSeries = (Excel.Series)xlChart.SeriesCollection(1);

                xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;

                xlSeries.ChartType = Excel.XlChartType.xlColumnClustered;

                Excel.ChartGroup cc = app.ActiveChart.ChartGroups(1) as Excel.ChartGroup;

                cc.GapWidth = 0;

     

    Thanks

    Ji

     

    2007年9月12日 6:18
    版主
  • Hi Zoddiax,

     

    The get_Range method only need an object parameter, so you can pass a string like this “B6: B400, D6: D400, G6: G400”, to get segregated range. This code works fine in my side:

       

    Code Snippet

        this.Application.Workbooks.Open(@"C:\test.xlsx",missing,missing,missing,

                    missing,missing,missing,missing,missing,missing,missing,missing,

                    missing,missing,missing);

              

                Excel.Range xlRange = this.Application.get_Range("B1:B10,D1:D10,G1:G10", missing);

                Excel.Chart xlChart = (Excel.Chart)this.Application.Charts.Add(missing, missing, missing, missing);

               

                xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineStacked, Type.Missing,

                Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

                "Daily", Type.Missing, Type.Missing, Type.Missing);

     

    Hope this helps!

     

    Thanks

    Ji

     

    2007年9月7日 9:25
    版主

全部回复

  • Hi Zoddiax,

     

    The get_Range method only need an object parameter, so you can pass a string like this “B6: B400, D6: D400, G6: G400”, to get segregated range. This code works fine in my side:

       

    Code Snippet

        this.Application.Workbooks.Open(@"C:\test.xlsx",missing,missing,missing,

                    missing,missing,missing,missing,missing,missing,missing,missing,

                    missing,missing,missing);

              

                Excel.Range xlRange = this.Application.get_Range("B1:B10,D1:D10,G1:G10", missing);

                Excel.Chart xlChart = (Excel.Chart)this.Application.Charts.Add(missing, missing, missing, missing);

               

                xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineStacked, Type.Missing,

                Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

                "Daily", Type.Missing, Type.Missing, Type.Missing);

     

    Hope this helps!

     

    Thanks

    Ji

     

    2007年9月7日 9:25
    版主
  • Thanks Ji

    It worked !  

     

    I have one more query regarding column charts in Excel.

     

    How do I set gap width property in column clustered charts in Excel using C#

     

    Following is my code snippet

     

    Code Snippet

    xlSeries4 = (Excel.Series)xlChart.SeriesCollection(4);

    xlSeries4.AxisGroup = Excel.XlAxisGroup.xlSecondary;

    xlSeries4.ChartType = Excel.XlChartType.xlColumnClustered;

     

     

    I want to decrease the gap width of xlSeries4 to minimum value. I am using Excel 2007.

     

    Please help me out of this.

     

    Thanks.

    2007年9月12日 3:25
  • Hi,

     

    Try these codes. It works for me.

    Code Snippet

                Excel.Series xlSeries = (Excel.Series)xlChart.SeriesCollection(1);

                xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;

                xlSeries.ChartType = Excel.XlChartType.xlColumnClustered;

                Excel.ChartGroup cc = app.ActiveChart.ChartGroups(1) as Excel.ChartGroup;

                cc.GapWidth = 0;

     

    Thanks

    Ji

     

    2007年9月12日 6:18
    版主
  • Thanks a lot Ji

     

    It worked. Appreciate your help.

     

    Thanks

    2007年9月12日 8:36
  • Hi Ji

     

    I want to know one more thing.

     

    I want to write a program to download a file from internet and save it on my computer. Following are the broad steps

    • Open the website and click on the link
    • An Open/Save/Cancel dialog pops up because the file is in zip folder
    • I want to open the zip folder and then copy the file inside it and then paste it on my computer at a specified location

    It would be great if you can help me out with the code. I am able to open the website and click on the link but i dont know how to handle the Open/Save/Cancel dialog

     

    Thanks.

     

     

    2007年9月13日 3:04
  • Hi,

     

    But this question is far off-topic here. I think it could be achieved. If you are using C#, you can try to ask in C# language forum. Hope members there have a quick idea about that.

     

     

    Thanks

    Ji

     

    2007年9月13日 10:55
    版主
  • I am drawing an excel chart using C#. But the chart is occupying entire plot area no matter how many rows i give. I want to leave some space at the end of the chart.

     

    Means if i am drawing chart for 400 rows, I want to see all the 400 rows on the x axis without omitting the blank rows. How can i do that.

     

    Following is my code

     

    Code Block

    /* Open the destination file */

    xlBookScript = (Excel.Workbook)xlBooks.Open(sDestinationFile, 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);

    xlSheetExtra = (Excel.Worksheet)xlBookScript.Worksheets["Extra"];

    xlSheetScript = (Excel.Worksheet)xlBookScript.Worksheets[sSheetType];

    xlChart = (Excel.Chart)xlBookScript.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    xlRange = xlSheetScript.get_Range("B7:B400,D7:D400,J7:J400,O7:O400", Type.Missing);

    xlChart.ChartWizard(xlRange, Excel.XlChartType.xlLineMarkers, Type.Missing,

    Excel.XlRowCol.xlColumns, Type.Missing, Type.Missing, false,

    sSheetType, Type.Missing, Type.Missing, Type.Missing);

     

     

     

    xlSeriesVol = (Excel.Series)xlChart.SeriesCollection(4);

    xlSeriesVol.AxisGroup = Excel.XlAxisGroup.xlSecondary;

    xlSeriesVol.ChartType = Excel.XlChartType.xlColumnClustered;

    xlZAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary);

    xlZAxis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionNone;

    xlZAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone;

    xlZAxis.MaximumScale = 3 * xlZAxis.MaximumScale;

    xlZAxis.MinimumScale = 0;

     

     

     

    /* Give the location for the chart */

    xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Extra");

    xlRange = (Excel.Range)xlSheetExtra.Rows.get_Item(iRowNo, Type.Missing);

    xlSheetExtra.Shapes.Item(sChartNo).Top = (float)(double)xlRange.Top;

    xlRange = (Excel.Range)xlSheetExtra.Columns.get_Item(1, Type.Missing);

    xlSheetExtra.Shapes.Item(sChartNo).Left = (float)(double)xlRange.Left;

    xlSheetExtra.Shapes.Item(sChartNo).Height = 380;

    xlSheetExtra.Shapes.Item(sChartNo).Width = 710;

    xlBookScript.Close(true, Type.Missing, Type.Missing);

     

    2007年10月9日 6:35
  • Hi there,

    Im having the same situation as zoddiax had, I want to create an excel chart from multiple nonadjunt columns.

    Im using Excel 2010, .NET 4.0 and C#, the problem is that the solution mentined before doesnt work anymore.

    I try yo separate colums by comas but it doesn work, in fact a COM Exception comes up if you try this., Excel.Range xlRange = this.Application.get_Range("B1:B10,D1:D10,G1:G10", missing.

    If someone know how to do it, please let me know.

    Thank you  in advance.

    2012年3月15日 11:42
  • It's late but maybe for someone else:

    In some countries excel uses something else then ','.

    In my case I have to use:

    chartRange = xlWorkSheet.get_Range("A2:A12; L2:L12; J2:J12; K2:K12", Type.Missing); 

    2014年7月3日 7:31