excel_setproperty

[Hong_Kong]

Hi,

I would like to create an Excel report, with text and data in different cells.  For example:

Voltage: 12.345

Power supply: Agilent 6653A

The excel2000dem.cws shows how to write data to excel.  I'm trying the excel_setproperty command.  It's perfectly OK to write data, but not quite successful to write text.  I've tried different tyes of VARIANTS already.

Please advise

[RobertoBozzolo · Accepted solution]

In case you need to write to a single cell you can use 

CA_VariantSetCString (&MyVariant, "Text");
Excel_RangeSetItem (ExcelRangeHandle, NULL, CA_VariantInt (row), CA_VariantInt (column), MyVariant);

On the other hand, to write an array of strings to a column, if this is what you want to do, you can use this function:

//----------------------------------------------------------------------
//
// Function ArrayTransfer ()
//
//----------------------------------------------------------------------
/// HIFN ArrayTransfer ()
/// HIFN Transfer a one-column array to an Excel sheet
/// HIRET Error code (0 if all is OK)
/// HIPAR column/The column to write to
/// HIPAR nRows/Number of elements in the array
/// HIPAR MyVariant/Working variant
/// HIPAR ExcelWorksheetHandle/Handle of the sheet to write to
/// HIPAR ExcelRangeHandle/Handle of the range of cells to write
/// HIPAR aType/Array datatype
/// HIPAR array/Data to trasfer
int ArrayTransfer (int column, int nRows, VARIANT MyVariant, CAObjHandle ExcelWorksheetHandle, CAObjHandle *ExcelRangeHandle, unsigned int aType, void *array)

{
	int		error = 0;
	char	msg[64];

	if (column > 52)		// In case of 53+ columns
    	sprintf (msg, "B%c2:B%c%d", 64 + column - 52, 64 + column - 52, nRows);
    else if (column > 26)	// In case of 26+ columns
    	sprintf (msg, "A%c2:A%c%d", 64 + column - 26, 64 + column - 26, nRows);
    else
    	sprintf (msg, "%c2:%c%d", 64 + column, 64 + column, nRows);
    errChk (CA_VariantSetCString (&MyVariant, msg));
	if (*ExcelRangeHandle) errChk (ClearObjHandle (ExcelRangeHandle));
    errChk (Excel_WorksheetRange (ExcelWorksheetHandle, NULL, MyVariant, CA_DEFAULT_VAL, ExcelRangeHandle));

	switch (aType) {
    	case CAVT_CSTRING:
			errChk (CA_VariantSet2DArray (&MyVariant, CAVT_CSTRING, nRows, 1, (char *)array));
			break;
		case CAVT_DOUBLE:
			errChk (CA_VariantSet2DArray (&MyVariant, CAVT_DOUBLE, nRows, 1, (double *)array));
			break;
		case CAVT_INT:
			errChk (CA_VariantSet2DArray (&MyVariant, CAVT_INT, nRows, 1, (int *)array));
			break;
    }
	errChk (Excel_SetProperty (*ExcelRangeHandle, NULL, Excel_RangeValue2, CAVT_VARIANT, MyVariant));
	errChk (ClearObjHandle (ExcelRangeHandle));

Error:
	return error;
}

The function can be called this way:

int		i, error;
int		nRows;			// Number of elements
char	**dh = NULL;
VARIANT	MyVariant;



// Allocate memory for strings
dh = malloc (nRows * sizeof (char *));	// Data/ora registrazione
for (i = 0; i < nRows; i++) {
	dh[i] = malloc (50 * sizeof (char));
}

// Fill string array
// ....

// Write to the Excel sheet
error = ArrayTransfer (column, nRows, MyVariant, ExcelWorksheetHandle, &ExcelRangeHandle, CAVT_CSTRING, dh);
// Test error code

// Code follows here
// ...