Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 20. August 2009 05:55

I wrote a post a few days ago describing how to generate a Line – Column chart in Excel through C#. And then a few things happened. Jon Peltier proposed a much nicer approach, I realized that my code worked for Excel 2003 but not Excel 2007, and someone asked for my code, “Jon-Peltier style”. So here we go: assuming your chart has more than one series, and you want the second series to be formatted as a line, all the rest as columns, you would do something like this:

// Create your chart object first
// formatted as column
Chart chart = ExcelCharts.AddChart(targetWorkbook, “my chart”, “the chart title”, XlChartType.xlColumnClustered, dataRange, XlRowCol.xlRows);
// Select the second series and make it a line
Series series = (Series)chart.SeriesCollection(2);
series.ChartType = XlChartType.xlLine;

Here is a simplified version of my AddChart method, which creates the base chart. Nothing fancy, but gets the job done.

public static Excel.Chart AddChart(Workbook workbook, string chartSheetName, string title, XlChartType chartType, Range dataRange, XlRowCol byRowOrCol)
{
    Excel.Chart chart;
    chart = (Excel.Chart)workbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    chart.ChartType = chartType;
    chart.Location(XlChartLocation.xlLocationAsNewSheet, chartSheetName);
    chart.SetSourceData(dataRange, byRowOrCol);
    chart.HasTitle = true;
    chart.ChartTitle.Text = title;
    return chart;
}

As an aside, I was not happy with myself when I realized the code didn’t run on Excel 2007. I tend to write Excel-related code against Excel 2003 first, assuming it is the smallest common denominator and will likely work with Excel 2007 – but this is a perfect illustration that while it will typically be correct, it will sometimes fail, sometimes in very unexpected and trivial places, like in this example. Moral of the story: as Lenin allegedly said, “Trust is good, control is better”…

Comments

8/17/2009 6:01:41 AM #

trackback

Create Excel Line - Column Chart from C#

Create Excel Line - Column Chart from C#

Clear Lines Blog | Reply

10/11/2009 2:33:09 PM #

zenab

In excel i have data in 10 columns, but i want to draw chart base upon 2nd and 3rd column. how i cant do this ?

Excel.Range chartRange ;

            Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(1, 200, 300, 250);//location of the chart

            //Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(100, 20, 10, 250);
            Excel.Chart chartPage = myChart.Chart;

            chartRange = xlWorkSheet.get_Range("A2", "d5");
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = Excel.XlChartType.xlLine;
Awaiting for your kind resonse.
Thanks

zenab Islamic Republic of Pakistan | Reply

8/1/2010 8:23:12 PM #

genokiller

If the built in Line – Column chart can automatically display the Secondary Y-axis, how can I display it with this codes?

genokiller Republic of the Philippines | Reply

8/15/2012 10:17:03 AM #

trackback

Create Excel Line - Column Chart from C#

Create Excel Line - Column Chart from C#

Clear Lines Blog | Reply

12/19/2016 11:21:58 PM #

pingback

Pingback from steroidsforsale.biz

bodybuilding steroids for sale

steroidsforsale.biz | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS