ActiveX Excel export using Excel_ChartChartWizard()

[RSteiner]

Hi there,

I have to do a excel report which contains a chart.

I added a chart sheet to the Workbook and generated a XYScatter graph. (ExcelConst_xlXYScatter)

But the chart still doesn't look as it should. The X-Axis is the count of Rows contained in the columns I passed to the Wizard.

When I change the style of the chart to have connecting lines and no markers then the main grid of the X-Axis disappears.

Does anyone know how I have to combine the Functions Excel_ChartApplyCustomType() and Excel_ChartChartWizard() to obtain a correct result?

Thanks in advance for your help!

[Aurelie]

Hi,

which CVI version are you using ? What have you already tried ?

Aurelie

[dcl9000]

You're not along.  Excel_ChartChartWizard(...) function cannot create XY scatter plot, which troubled me a while before.  Finally found a solution later.  Here's what I did by modifying the sample "excel2000dem.prj" to make it.

//-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/* Assume the data is arranged in three columns from cell E2 to cell G11.  I want to use the values in column E as X-axis.  Column F and G will be the values plotted against Y-axis. */

/* Assume you have the excel file opened and connected to your CVI program already. */

// Assign the data range in the worksheet to plot
    error = CA_VariantSetCString (&MyCellRangeV, "E2:G11");      // column E will be the X-values

   error = Excel_WorksheetRange (ExcelWorksheetHandle, NULL, MyCellRangeV, CA_DEFAULT_VAL, &ExcelRangeHandle);
    if (error<0)  goto Error;

   status = CA_GetDispatchFromObjHandle (ExcelRangeHandle, &MyDispatch);      // Get dispatch for range

    // Create a new chart
    ClearObjHandle(&ExcelChartObjHandle);   // Must clear previous handle before assigning new values!
   

   status = Excel_ChartObjectsAdd (ExcelChartsHandle, NULL, 400, 175.0, 300.0, 200.0, &ExcelChartObjHandle);
    if (status<0) goto Error;

   ClearObjHandle(&ExcelChartHandle);      // Must clear previous handle before assigning new values!
     

   status = Excel_GetProperty (ExcelChartObjHandle, NULL, Excel_ChartObjectChart, CAVT_OBJHANDLE, &ExcelChartHandle);
    if (status<0) goto Error;

   // Use Chart Wizard to setup Chart
    status = CA_VariantSetCString (&chart_title, "Chart #2");
    status = CA_VariantSetCString (&x_title, "x2 axis");
    status = CA_VariantSetCString (&y_title, "y2 axis");

    status = Excel_ChartChartWizard (ExcelChartHandle, &ErrorInfo,
                                     CA_VariantDispatch(MyDispatch),
                                     CA_VariantLong(ExcelConst_xlXYScatter),
                                     CA_DEFAULT_VAL,
                                     CA_VariantInt(ExcelConst_xlColumns),
                                     CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                                     CA_DEFAULT_VAL, chart_title,
                                     x_title, y_title,
                                     CA_DEFAULT_VAL);

    // This is the correct method to modify and plot multiple lines in XYScatter format.
    Excel_ChartSetSourceData (ExcelChartHandle, NULL, ExcelRangeHandle, CA_VariantInt(ExcelConst_xlColumns));

//-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The above codes will draw two lines (XY Scatter plot) according to the values from columns E, F, G, using column E as the X-axis values.

[ElectroLund]

Excellent tutorial and sample code!

Would this work for adding other chart series from other sheets?  In your example ("E2:G11"), all value columns are already on the same sheet.

I have an Excel template I'm making, but the value and X columns are located in successive sheets.  It's not clear to me how I would extend your code to that scenario, since the single call to Excel_ChartChartWizard requires the ExcelRangeHandle dispatch, which itself is based on a single sheet range.