Formatting excel columns already formatted as text (in c++)?

Question

4.00/5 (1 vote)

See more:

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};//1x1 element array
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

Posted 13-Apr-11 12:41pm

mr.abzadeh

Updated 14-Apr-11 5:36am

v11

Add a Solution

Comments

Peter_in_2780 14-Apr-11 3:25am

Edited: changed title so it doesn't look to be a duplicate of your earlier question.

mr.abzadeh 15-Apr-11 14:10pm

Very clever.
My earlier questions subject was c++, and I thought Changing subject to Office, C++ could be a good idea.

2 solutions

Solution 2

You might find this interesting:
ExcelFormat Library[^]

Regards
Espen Harlinn

Posted 14-Apr-11 10:57am

Espen Harlinn

Comments

JF2015 15-Apr-11 0:53am

Great link - very informative article that I haven't seen before. Thanks for pointing out!

Espen Harlinn 15-Apr-11 8:08am

Thanks JF2015!

mr.abzadeh 15-Apr-11 13:47pm

Thanks, I loaded link, source code, demo project and I am examining It.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Accepted Answer · 2011-04-14T05:49:00

Solution 1

saRet.Create(VT_BSTR, 2, numElements);
FillSafeArray(L"123456", 0, 0, &saRet);

Well although that field contains numeric digits, it's still a text string, not a number.

Posted 14-Apr-11 5:49am

Richard MacCutchan

Comments

mr.abzadeh 15-Apr-11 13:42pm

Well, It is a good idea. I wrote another function to pass double values.

<pre>void FillSafeArrayNumber(double fValue, int iRow, int iCol, COleSafeArray* sa){
long index[2];
index[0] = iRow;
index[1] = iCol;
sa->PutElement(index, &fValue);
}</pre>
It compiled, but didnt work. (exception occured)
The help for COleSafeArray::PutElement shows an example that calls it exactly as above(2nd param as double ptr). but there is not any overload.
<pre>void PutElement( long* rgIndices, void* pvData )</pre>
do you have any idea?
);PutElement

Richard MacCutchan 15-Apr-11 15:09pm

Sorry, no; I guess you need to try and find out why it gave the exception. Are you sure it's OK to try and put a double rather than some variant type?

mr.abzadeh 16-Apr-11 2:09am

I found the error. I have created a COleSafeArray conaining of only strings, as in
saRet.Create(VT_BSTR, 2, numElements);
I can not Fill this ayyay with double or other types of data
Thanks for your reply