none
C# How to create line char in excel from data RRS feed

  • Question

  • i have stored data in my datatable what has two column like for date and numeric value.

    see how data is stored in my datatable.

                DataTable dt = new DataTable();
                dt.Columns.Add("Date", typeof(DateTime));
                dt.Columns.Add("Data", typeof(Int32));
    
                DataRow dr = dt.NewRow();
                dr[0] = DateTime.Parse("01-03-2017");
                dr[1] = 20;
                dt.Rows.Add(dr);
    
                dr = dt.NewRow();
                dr[0] = DateTime.Parse("01-04-2017");
                dr[1] = 0;
                dt.Rows.Add(dr);
    
                dr = dt.NewRow();
                dr[0] = DateTime.Parse("01-05-2017");
                dr[1] = 0;
                dt.Rows.Add(dr);
    
                dr = dt.NewRow();
                dr[0] = DateTime.Parse("01-09-2017");
                dr[1] = 10;
                dt.Rows.Add(dr);

    now from the above data i have to create line chart in excel file dynamically. i have to create excel file dynamically at run time and i have to insert line chart into excel work sheet dynamically based on my data table data. i will use microsoft interop excel library.

    i want my line chart would look like as per below screen shot.

    so please show me with code sample that how to create line chart from data table data into excel work sheet.

    thanks


    • Edited by Sudip_inn Wednesday, February 1, 2017 9:08 AM
    Wednesday, February 1, 2017 9:05 AM

Answers

  • Hi,

    Please see the code below. It inserts the datatable into worksheet and then add a line chart.

         Excel.Application xlApp = new Excel.Application();
                xlApp.Visible = true;
                Excel.Workbook xlMyWorkbook = xlApp.Workbooks.Add();
                Excel.Worksheet worksheet = xlMyWorkbook.Worksheets[1];
    
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        worksheet.Cells[i+1, j+1] = dt.Rows[i][j];
                    }
                }
    
                Excel.ChartObject chartObject = worksheet.ChartObjects().Add(60, 10, 300, 300);
                chartObject.Chart.SetSourceData(worksheet.UsedRange);
                chartObject.Chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, February 3, 2017 1:15 PM
    Thursday, February 2, 2017 5:00 AM
    Moderator

All replies

  • i have stored data in my data table what has two column like for date and numeric value.

    see how data is stored in my data table.

                DataTable dt = new DataTable();
                dt.Columns.Add("Date", typeof(DateTime));
                dt.Columns.Add("Data", typeof(Int32));
    
                DataRow dr = dt.NewRow();
                dr[0] = DateTime.Parse("01-03-2017");
                dr[1] = 20;
                dt.Rows.Add(dr);
    
                dr = dt.NewRow();
                dr[0] = DateTime.Parse("01-04-2017");
                dr[1] = 0;
                dt.Rows.Add(dr);
    
                dr = dt.NewRow();
                dr[0] = DateTime.Parse("01-05-2017");
                dr[1] = 0;
                dt.Rows.Add(dr);
    
                dr = dt.NewRow();
                dr[0] = DateTime.Parse("01-09-2017");
                dr[1] = 10;
                dt.Rows.Add(dr);

    now from the above data i have to create line chart in power point slide dynamically. i have to create power point dynamically at run time and i have to insert line chart into power point slide dynamically based on my data table data. i will use microsoft interop power point library.

    i want my line chart would look like as per below screen shot.

    so please show me with code sample that how to create line chart from data table data into power point slide.

    thanks

    Wednesday, February 1, 2017 9:11 AM
  • Hi,

    Please see the code below. It inserts the datatable into worksheet and then add a line chart.

         Excel.Application xlApp = new Excel.Application();
                xlApp.Visible = true;
                Excel.Workbook xlMyWorkbook = xlApp.Workbooks.Add();
                Excel.Worksheet worksheet = xlMyWorkbook.Worksheets[1];
    
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        worksheet.Cells[i+1, j+1] = dt.Rows[i][j];
                    }
                }
    
                Excel.ChartObject chartObject = worksheet.ChartObjects().Add(60, 10, 300, 300);
                chartObject.Chart.SetSourceData(worksheet.UsedRange);
                chartObject.Chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, February 3, 2017 1:15 PM
    Thursday, February 2, 2017 5:00 AM
    Moderator