How to add the Chart sheet in Excel using C#

Question

I have created the sheet1 and populated some data in the sheet, using the data from sheet1 i want to create a chart sheet with ploting the data

try
{
    app = new Excel.Application();
    app.Visible = true;
    workbook = app.Workbooks.Add(1);
    worksheet = (Excel.Worksheet)workbook.Sheets[1];

    PopulateDateInExcel(pathtologsfolder, startdate, enddate);
    // create a chart

    Excel.Range chartRange;
    object misValue = System.Reflection.Missing.Value;
    Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
    Excel.ChartObject myChart = (Excel.ChartObject)workbook.Charts[2];
    Excel.Chart chartPage = myChart.Chart;
    chartRange = worksheet.get_Range("AN1", "AP6");
    chartPage.SetSourceData(chartRange, misValue);
    chartPage.ChartType = Excel.XlChartType.xl3DLine; 
}
catch (Exception e)
{
    //Console.Write("Error");
}
finally
{

}

Thanks in advance, Excel Automation

user_3307903 · Answer

The way to do this would be to use the Add method:

Excel.Workbook xlWorkbook;
Excel.Chart chartName = (Excel.Chart)xlWorkbook.Charts.Add();

Admittedly, at least in my experiments this causes trouble as the dataset it is using by default is fairly random (Excel magic...) so you might be needing to reformat it afterwards (delete and add datasets, etc), but that's the method to do it.

EDIT:

I should note that Excel support documentation seems to recommend the Add2 method method, but I haven't managed to use it successfully:

Excel.Workbook xlWorkbook;
Excel.Chart chartName = (Excel.Chart)xlWorkbook.Charts.Add2();

user_15775293 · Answer

Excel.Application oXL = new Excel.Application();

and after then

oXL.Run() and see attached:

How to add the Chart sheet in Excel using C#

3 Answers