activex excel chart and series

[sruly]

Hi,

I am trying to insert chart to excel, not by chosing a specific colum but insert array of my own.

I know that it can be done with SERIES function but I am having hard time to understand those function.

I don't know if i need to insert chart and then series function.

There is source tag in the series function that i don't know what data to insert to it.

it will be a graet helpful if you can find me example  how to insert chart with array that i insert from CVI 6.

[bilalD]

You will need to write out the data from CVI into a worksheet. Then add a chart and specify that particular column in that worksheet as a source for that chart.

Take a look at the Excel_ChartChartWizard() that is generated for you when you use the CVI Activex controller wizard to generate the Excel wrappers. To se an examle of this function, check out the excel report API that ships with CVI. The function panel for that is located under C:\Program Files\National Instruments\CVI81\toolslib\activex\excel. The source for the report API is available in the same folder so you can see how those functions were implemented.

If you are using CVI and Excel, you will need to be familiar with the Excel automation API. This API is documented in the Excel help.

[sruly]

Hi,

I don't want to insert data to a worksheet and then make a chart- there is no problem in that.

 I want to write the numbers(data) in cvi and see the chart in excel by using MakeChart function.

There is no example in the cvi samples for that. 

I did it manualy by using Series in the chart wizard, I need to know how to do this in Cvi.

[bilalD]

Using VBA, you would do the following

Charts("Chart1").SeriesCollection(1).Values = _ Array(1, 3, 5, 7, 11, 13, 17, 19)

Lets break it down. This is all in the Excel VBA help which ships with Excel.

Charts("Chart1") <- get handle to chart object
.SeriesCollection(1) <- use chart handle and call its method "SeriesCollection" passing it an index of one. Since this is a collection, 1 is used to index the items in its collection. This returns a Series object.
.Values = _ Array(1, 3, 5, 7, 11, 13, 17, 19)  <- Array creates a VB array which is basically a safearray. The Values property (member of the Series object) takes a variant, so VB does the converting for you in the background.


Now lets map that to CVI using the Excel Report toolkit ( C:\Program Files\National Instruments\CVI81\toolslib\activex\excel ) and the Excel 9.0 Wrappers that are included with CVI. I tested this with Excel 2003, which is Excel 11.0. The wrappers work with newer excel versions.

//declare and initialize variables
double data[] = {1,2,3,4,5};
LPSAFEARRAY safearray;
VARIANT safearrayV;

main() {
...
//Convert C array to safearray
CA_Array1DToSafeArray (data, CAVT_DOUBLE, 5, &safearray);

//convert safearray to variant
CA_VariantSetSafeArray (&safearrayV, CAVT_DOUBLE, safearray);

//Open excel, get workbook and worksheet handles via excel report
//Get chart handle "Charts("Chart1")"
ExcelRpt_GetEmbeddedChartFromIndex(worksheetHandle,1,&chartHandle);

//Use the handles returned by excel reports to do advanced excel manipulation
//.SeriesCollection(1)
Excel_ChartSeriesCollection(chartHandle,&errorInfo,CA_VariantInt(1),&seriesHandle);

// sets the Values property
Excel_SetProperty (seriesHandle, &errorInfo, Excel_SeriesValues, CAVT_VARIANT, safearrayV);
..
//cleanup handles, variants and safearrays.
}

Message Edited by bilalD on 03-14-2007 07:04 PM

[sruly]

HI,

Thank you so much for your help, I'm feeling I'm so close but it's still doesn't work .

I'm Getting ERROR in the Excel_ChartSeriesCollection command.

I opened excel and got workbook and worksheet handle like you told me but no luck ,

I'm getting blanck chart inside the Excel.

I think there is a problem with the ExcelRpt_GetEmbeddedChartFromIndex command because there is

ExcelRpt_ChartAddtoWorksheet command to add a chart into excel and get is handle.

I'm using Excel 9.0.
 

  double data[]={5,8,9,12,15};
  LPSAFEARRAY safearray;
  VARIANT     safearrayv;
  CAObjHandle Series,chartHandle;    
 
 
   
    error = CA_Array1DToSafeArray(data,CAVT_DOUBLE,5,&safearray);
    error = CA_VariantSetSafeArray(&safearrayv,CAVT_DOUBLE,safearray);
 
    error = ExcelRpt_GetEmbeddedChartFromIndex(worksheet,1,&chartHandle);
    error = Excel_ChartSeriesCollection (chartHandle, NULL, CA_VariantInt(1), &Series);
    error = Excel_SetProperty(Series,NULL,Excel_SeriesValues,CAVT_VARIANT,safearrayv);

Please Help me........

[Patrick_P.]

Hi sruly,

I believe the piece that you may have been missing is that Bilal's code assumes you already have a chart created that has a series in it.  I have pasted some more code below that should work with a completely new chart because it creates a new series as well.  Hope this helps!

//Convert C array to safearray
CA_Array1DToSafeArray (data, CAVT_DOUBLE, 5, &safearray);

//convert safearray to variant
CA_VariantSetSafeArray (&safearrayV, CAVT_DOUBLE, safearray);

//Open excel, get workbook, worksheet, and chart handles via excel report
//Get seriesCollection and then create new series
Excel_ChartSeriesCollection(chartHandle,&errorInfo,CA_DEFAULT_VAL,&seriesCollectionHandle);
Excel_SeriesCollectionNewSeries (seriesCollectionHandle, NULL, &seriesHandle);
// sets the Values property
Excel_SetProperty (seriesHandle, &errorInfo, Excel_SeriesValues, CAVT_VARIANT, safearrayV);

[sruly]

Hi patrik,

Thank you so much for your help , it worked.

But I Still have a small problem with the array , if I insert array bigger then 90 I'm getting Error.

Here is the code that i wrote:

  double data[100]={
                 1.0,2.0,3.0,4.0,5.0,6.0,7.0,8.0,9.0,10.0,11.0,12.0,13.0,14.0,15.0,16.0,17.0,18.0,19.0,20.0,
                 11.0,12.0,13.0,14.0,15.0,16.0,17.0,18.0,19.0,20.0,21.0,22.0,23.0,24.0,25.0,26.0,27.0,28.0,29.0,30.0,
                 1.0,2.0,3.0,4.0,5.0,6.0,7.0,8.0,9.0,10.0,11.0,12.0,13.0,14.0,15.0,16.0,17.0,18.0,19.0,20.0,
                 1.0,2.0,3.0,4.0,5.0,6.0,7.0,8.0,9.0,10.0,11.0,12.0,13.0,14.0,15.0,16.0,17.0,18.0,19.0,20.0,
                 1.0,2.0,3.0,4.0,5.0,6.0,7.0,8.0,9.0,10.0,11.0,12.0,13.0,14.0,15.0,16.0,17.0,18.0,19.0,20.0};
  long dataX[100]={
                10,12,14,16,18,10,12,14,16,18,10,12,14,16,18,10,12,14,16,18,
                10,12,14,16,18,10,12,14,16,18,10,12,14,16,18,10,12,14,16,18,
                10,12,14,16,18,10,12,14,16,18,10,12,14,16,18,10,12,14,16,18,
                10,12,14,16,18,10,12,14,16,18,10,12,14,16,18,10,12,14,16,18,
                10,12,14,16,18,10,12,14,16,18,10,12,14,16,18,10,12,14,16,18};
  LPSAFEARRAY safearray,safearrayX;
  VARIANT     safearrayv,safearrayXX;

    //data array
    error = CA_Array1DToSafeArray(data,CAVT_DOUBLE,90,&safearray);
    error = CA_VariantSetSafeArray(&safearrayv,CAVT_DOUBLE,safearray);
 if (error < 0) goto Error;
   
    //X array
    error = CA_Array1DToSafeArray(dataX,CAVT_LONG,90,&safearrayX);
 error = CA_VariantSetSafeArray(&safearrayXX,CAVT_LONG,safearrayX);
 if (error < 0) goto Error;
 
 //open chart
 error = ExcelRpt_ChartAddtoWorksheet (worksheet, 300, 60, 400, 250,&chart);
    if (error < 0) goto Error;  
 
 //Open Series
 error = Excel_ChartSeriesCollection (chart, NULL, CA_DEFAULT_VAL,&Series);
    if (error < 0) goto Error; 
   
    //Get Series Handle
    error = Excel_SeriesCollectionNewSeries(Series,NULL,&SerisHandle);
    if (error < 0) goto Error;  
   
    //Set data Array
    error = Excel_SetProperty(SerisHandle,NULL,Excel_SeriesValues,CAVT_VARIANT,safearrayv);
    if (error < 0) goto Error;
   
    //Set X array
    error = Excel_SetProperty (SerisHandle, NULL, Excel_SeriesXValues,CAVT_VARIANT, safearrayXX);
    if (error < 0) goto Error;
 
 //Set chart Type to line.
 error = Excel_SetProperty (chart, NULL, Excel_SeriesChartType,CAVT_LONG, ExcelConst_xlLineMarkers);
    if (error < 0) goto Error;
   
   

Error:   
    if (error < 0)
        MessagePopup (APP_AUTOMATION_ERR, LAUNCHERR);
       
    return ;

 Help , Help , Help

Sruli

[Patrick_P.]

Hey Sruli,

The problem that you are running into is that Excel only allows 255 characters in the Values area.  Since you are trying to pass a large constant array, you are going over this limit of 255 characters.  In other words you are doing everything correctly, but you are running into a limitation of Excel.  You might have to set the Values into cells on the worksheet, and then reference that for the values of your chart.  Hope this helps!

[sruly]

I entered array of 90 to the chart series and then I added more data manualy and it worked.

I think the problem is through Cvi function..

Please see if i am right...

[sruly]

I'm sorry, you were right , it is excel problem - i'm loosing it......................

I can't follow your advise because It's alot of graphs and it will take me forever.

I have idea but I need your help.

maybe I will use the Microsoft Graph Chart with the insert object command but i dont know how to insert

and change the graph properties.

what do you think?..do you have a better idea?..

if you agree with mine , It will be great if you send me example..

I realy need to finish this work and the graphs are making me crazy....Please help.....