locked
creating excel charts dynamically from vb.net

    Question

  • Hi, I would like to request some help. Is the any online resource which can explain how to create excel charts using vb.net? assistance is higly appreciated.

     

    I also have finally managed to get my app to get a database query results from my datagridview to excel after a lot of help from this forum and others. So now i have a table in excel from which I would like to dynamically generate charts from. I have checked the web and found a few good examples of how this is done but they assume that the range of cells involved is know. My excel table was generated through the datagridview iteration. Is there a good and safe way to program my app to create charts from tables generated dynamically?

     

    I just have this to do before I can submit this project for my studies. being a nub to vb.net, I have yet to master the intricacies it but its rather excitingtosee your app works  .

     

    Thanks in advance

    Thursday, February 14, 2008 2:47 AM

Answers

  • Hi,

     

    You can refer this KB article which tells how to automate Excel using C#. One of the tasks is how to create a chart and use ChartWizard to set the chart:

    Code Snippet
     //Add a Chart for the selected data.
     oWB = (Excel._Workbook)oWS.Parent;
     oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value,
      Missing.Value, Missing.Value );

     //Use the ChartWizard to create a new chart from the selected data.
     oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize(
      Missing.Value, iNumQtrs);
     oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value,
      Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value,
      Missing.Value, Missing.Value, Missing.Value, Missing.Value );
     oSeries = (Excel.Series)oChart.SeriesCollection(1);
     oSeries.XValues = oWS.get_Range("A2", "A6");
     for( int iRet = 1; iRet <= iNumQtrs; iRet++)
     {
      oSeries = (Excel.Series)oChart.SeriesCollection(iRet);
      String seriesName;
      seriesName = "=\"Q";
      seriesName = String.Concat( seriesName, iRet );
      seriesName = String.Concat( seriesName, "\"" );
      oSeries.Name = seriesName;
     }               
     
     oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name );

     //Move the chart so as not to cover your data.
     oResizeRange = (Excel.Range)oWS.Rows.get_Item(10, Missing.Value );
     oWS.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;
     oResizeRange = (Excel.Range)oWS.Columns.get_Item(2, Missing.Value );
     oWS.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;

     

    Using Excel Object Model to create the chart, you must specify the Range where the data locates. If your table is dynamically created from datagridview, you can select a cell in that table and use cell.CurrentRegion to get the whole table Range, and pass it into ChartWizard method.

    http://msdn2.microsoft.com/en-us/library/aa247345(office.10).aspx

     

     

    Thanks,

    Ji

     

    Tuesday, February 19, 2008 11:40 AM
    Moderator
  •  A.F.B wrote:

    but I need some guide on how to get the ranges for both axis dynamically.

     

    Hi,

     

    As my previous post already pointed out, if you need to get the ranges for both axis dynamically, you can try about range.CurrentRegion.

    For example, your source table always starts from A2 to somewhere depends how many rows and columns retrieved from DB. Then you can use Application.Range("A2").CurrentRegion to get the whole table range.

    Otherwise, you need query the Data Base to see how many rows and columns you are retrieving and then sepcify the range with that information.

    Hope this may clarify

     

     

    Thanks,

    Ji

    Tuesday, February 26, 2008 2:14 AM
    Moderator

All replies

  • Hi,

     

    You can refer this KB article which tells how to automate Excel using C#. One of the tasks is how to create a chart and use ChartWizard to set the chart:

    Code Snippet
     //Add a Chart for the selected data.
     oWB = (Excel._Workbook)oWS.Parent;
     oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value,
      Missing.Value, Missing.Value );

     //Use the ChartWizard to create a new chart from the selected data.
     oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize(
      Missing.Value, iNumQtrs);
     oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value,
      Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value,
      Missing.Value, Missing.Value, Missing.Value, Missing.Value );
     oSeries = (Excel.Series)oChart.SeriesCollection(1);
     oSeries.XValues = oWS.get_Range("A2", "A6");
     for( int iRet = 1; iRet <= iNumQtrs; iRet++)
     {
      oSeries = (Excel.Series)oChart.SeriesCollection(iRet);
      String seriesName;
      seriesName = "=\"Q";
      seriesName = String.Concat( seriesName, iRet );
      seriesName = String.Concat( seriesName, "\"" );
      oSeries.Name = seriesName;
     }               
     
     oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name );

     //Move the chart so as not to cover your data.
     oResizeRange = (Excel.Range)oWS.Rows.get_Item(10, Missing.Value );
     oWS.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;
     oResizeRange = (Excel.Range)oWS.Columns.get_Item(2, Missing.Value );
     oWS.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;

     

    Using Excel Object Model to create the chart, you must specify the Range where the data locates. If your table is dynamically created from datagridview, you can select a cell in that table and use cell.CurrentRegion to get the whole table Range, and pass it into ChartWizard method.

    http://msdn2.microsoft.com/en-us/library/aa247345(office.10).aspx

     

     

    Thanks,

    Ji

     

    Tuesday, February 19, 2008 11:40 AM
    Moderator
  • Thank Ji,

    But I was more of looking for something like in here https://secure.codeproject.com/KB/office/Excel_Automation.aspx

    . I think its fit what I want to do with my app, but I need some guide on how to get the ranges for both axis dynamically. I s there really no way to do it? If so then I have to resort to coding the ranges before hand...

     

    Any assistance is higly appreciated.

     

    Thank you.

     

    Tuesday, February 26, 2008 12:10 AM
  •  A.F.B wrote:

    but I need some guide on how to get the ranges for both axis dynamically.

     

    Hi,

     

    As my previous post already pointed out, if you need to get the ranges for both axis dynamically, you can try about range.CurrentRegion.

    For example, your source table always starts from A2 to somewhere depends how many rows and columns retrieved from DB. Then you can use Application.Range("A2").CurrentRegion to get the whole table range.

    Otherwise, you need query the Data Base to see how many rows and columns you are retrieving and then sepcify the range with that information.

    Hope this may clarify

     

     

    Thanks,

    Ji

    Tuesday, February 26, 2008 2:14 AM
    Moderator
  • yes it works, thank you ever so much.

    Wednesday, April 23, 2008 1:27 PM
  • check this link for generating chart from excel.

    http://vb.net-informations.com/excel-2007/vb.net_excel_create_chart.htm

    bolton
    Saturday, July 12, 2008 6:20 AM