I have creaded an application that starts excel, writes numeric data to cells and activates excel. The format of cell defaults to text and excel shows error that numberic data is stored as text.
I try to change the cell format by this MFC code:
This code is in MFC. The similar code in CLR also does the same.
CRange range;
range = sheet.get_Range(COleVariant(L"A1"), COleVariant(L"A1"));
range.put_NumberFormat( (COleVariant(L"$0.00") )
The code that writes to excel Cell A1 is like this:
COleSafeArray saRet;
DWORD numElements[]={1,1};
saRet.Create(VT_BSTR, 2, numElements);
FillSafeArray(L"123456", 0, 0, &saRet);
CRange range;
Range = sheet.get_Range(COleVariant("A1"),COleVariant("A1"));
range.put_Value2(COleVariant(saRet));
void FillSafeArray(OLECHAR FAR* sz, int iRow, int iCol, COleSafeArray* sa)
{
long index[2];
index[0] = iRow;
index[1] = iCol;
VARIANT v;
VariantInit(&v);
v.vt = VT_BSTR;
v.bstrVal = SysAllocString(sz);
sa->PutElement(index, v.bstrVal);
SysFreeString(v.bstrVal);
VariantClear(&v);
}
but It does not work. I can not change the Cell format to any of Number formats at all, But once I convert the cell format to number width excel UI, then I can change to any number format. can any body tell me what is wrong with this code, or what other approach should I take?
best regards
abzadeh