ExcelRpt_ChartNew Bugged Help...........

[James@Motorola]

Hi,

I have a problem with ExceRpt_ChartNew; I am trying to create 8 worksheets and 8 charts following the order worksheet, chart, worksheet, chart etc.......

For some reason the 3rd chart doesn't get put in the correct position it should be inserted after the last sheet as I am using '-1' in the position variable.

This is a snippet of code I am using (sorry if I forgot some variables):


CAObjHandle excelAppHandle = 0;
CAObjHandle excelWorkbookHandle = 0;
CAObjHandle excelWorksheetHandle[8] = {0};
CAObjHandle excelChartHandle[8] = {0};
int sheetIndex = 0
int graphIndex;

ExcelRpt_ApplicationNew (VTRUE, &excelAppHandle);
ExcelRpt_WorkbookNew (excelAppHandle, &excelWorkbookHandle);

for(graphIndex = 0; graphIndex < 8; graphIndex++) {

/* the first 3 worksheets already exist in a new workbook */
if(graphIndex < 3) {
/* get the worksheets handle */
ExcelRpt_GetWorksheetFromIndex (excelWorkbookHandle, ++sheetIndex, &excelWorksheetHandle[graphIndex]);

/* create a blank chart */
if(graphIndex < 2) {
ExcelRpt_ChartNew (excelWorkbookHandle, ++sheetIndex, &excelChartHandle[graphIndex]);
} else {
/* INSERTS THE CHART IN THE WRONG POSITION */
ExcelRpt_ChartNew (excelWorkbookHandle, -1, &excelChartHandle[graphIndex]);
}

} else {

/* create a new worksheet */
ExcelRpt_WorksheetNew (excelWorkbookHandle, -1, &excelWorksheetHandle[graphIndex]);

/* create a blank chart */
ExcelRpt_ChartNew (excelWorkbookHandle, -1, &excelChartHandle[graphIndex]);
}
}

[Ian.W]

I believe the ExcelRpt_ChartNew function is different from the ExcelRpt_WorksheetNew function, in that the location parameter of -1 means different things.

For the ExcelRpt_ChartNew "Insert After Sheet Index" parameter of -1 means insert at the beginning of the workbook, but for ExcelRpt_WorksheetNew a -1 for the "Insert Before Index" means put it at the end.

Hope this helps...

--Ian

[James@Motorola]

It does seem to say that above the parameter but if you press F1 (Help) on the parameter on either ExcelRpt_ChartNew or ExcelRpt_WorksheetNew it says.

Pass –1 to insert the new sheet after the last sheet.

So I would assume that inserting -1 should always place the new sheet after the last sheet.

[Ian.W]

That's strange. In my version (CVI 6.0) pressing F1 on the parameter in ExcelRpt_ChartNew gives this:

The new chart sheet will be added after the sheet indexed "insert After Sheet Index"
To insert at the beginning of the workbook pass -1 to "insert After Sheet Index"

but pressing F1 on the parameter in ExcelRpt_WorksheetNew gives this:

The new sheet will be placed before the current worksheet with this index.
Pass -1 to insert the new sheet at the end of all the worksheets.

Seems like we may not be looking at the same version of excelreport.fp.

--Ian

[James@Motorola]

Looks like it, I'm using CVI 7.1.1.

Can anyone from NI help clear this up please?

Thanks
James

[bilalD]

Maybe I might have missed something, but the function seems to be working ok

I tried the following

ExcelRpt_ApplicationNew (VTRUE, &excelAppHandle);
ExcelRpt_WorkbookNew (excelAppHandle, &excelWorkbookHandle);

for(i=0;i<8;i++)
{
ExcelRpt_WorksheetNew(excelWorkbookHandle,-1,&workSheetArray[i]);
ExcelRpt_ChartNew(excelWorkbookHandle,-1,&chartArray[i]);

}
for(i=0;i<8;i++)
{
CA_DiscardObjHandle(chartArray[i]);
CA_DiscardObjHandle(workSheetArray[i]);
}

CA_DiscardObjHandle(excelAppHandle);
CA_DiscardObjHandle(excelWorkbookHandle);


I got a workbook that followed the order worksheet, chart, worksheet, chart...

The ExcelRpt_ChartNew function is different between the 2 versions of CVI. The source for the Excel Report functions in included, so you can see how it is implemented. Check out the excelreport.c file.

In CVI 6.0, the function just did a call to Excel_SheetsAdd() if the parameter is -1. In this case, the default behavior is to add the sheet to the very beginning of the workbook. Which is why the help says
To insert at the beginning of the workbook pass -1 to "Insert After Sheet Index". Notice the name of the parameter here is Insert After Sheet Index.

In 7.x, the function implementation was changed so that if a -1 is passed, we get the sheet count and insert the new sheet right at the end. If you wanted always insert the sheet at the beginning, you would pass a 1 value here. The parameter name is now called Insert Before Sheet index.

So it looks like the function behavior was changed between the 2 versions.

Let me know if I missed anything. I hope this helps.

[James@Motorola]

I think you are missing something, or my CVI is behaving differently.
When running your code the last chart did not appear in mine excel workbook.
When stepping through the code it apperas to be inserting the charts in front of the last sheet not after the last sheet when using the -1 parameter.

James

[bilalD]

Aha, I see the problem now.

This seems to be a Excel COM more rather than a CVI issue. I replicated the same thing in VB 6.0. Apparently if its a Chart, it gets added right before the last sheet. Here is the VB code i tried with Excel 2003. I don't whether Microsoft changed this behavior recently

excelApp.Visible = True
Set excelBook = excelApp.Workbooks.Add
Set excelSheet(0) = excelBook.Worksheets.Add
With excelBook
.Sheets.Add After:=.Worksheets(Worksheets.Count), Type:=xlChart
End With


The only way around this would be to move the newly created chart to the end. I added some code to the ExcelRpt_ChartNew function in reportexcel.c like so. I tried this with CVI 7.1 and it did the trick.

==========================================
HRESULT CVIFUNC ExcelRpt_ChartNew(CAObjHandle workbookHandle, int insertBeforeIndex, CAObjHandle *chartHandle)
{
HRESULT __result = S_OK;
CAObjHandle sheetsHandle = 0;
CAObjHandle insWorksheetHandle = 0;
VARIANT SheetV;
LPDISPATCH dispatchPtr = NULL;
int sheetnum;

CA_VariantSetEmpty (&SheetV);
if(!chartHandle)
__caErrChk (E_INVALIDARG);
__caErrChk (Excel_GetProperty (workbookHandle, NULL, Excel_WorkbookSheets, CAVT_OBJHANDLE, &sheetsHandle));
.
.
.
.
__caErrChk (Excel_SheetsAdd (sheetsHandle, NULL, (insertBeforeIndex != -1) ? SheetV : CA_DEFAULT_VAL,
(insertBeforeIndex == -1) ? SheetV : CA_DEFAULT_VAL, CA_DEFAULT_VAL,
CA_VariantDouble(ExcelConst_xlChart), chartHandle));

if(insertBeforeIndex== -1)
Excel_ChartMove (*chartHandle, NULL, CA_DEFAULT_VAL, SheetV);


Error:
CA_DiscardObjHandle(sheetsHandle);
CA_DiscardObjHandle(insWorksheetHandle);
CA_VariantClear(&SheetV);
if (dispatchPtr)
dispatchPtr->lpVtbl->Release(dispatchPtr);
return __result;
}
=========================================


I'll report this issue to the developers so we can address this in a better way for future releases.

Hope this helps