Excel Automation: how to place NULL value instead of 0 for number cells

Hi experts,

I want to either put a numeric value or blank when there is no data when automating Excel sheet. Instead of blank cell for no data, I get '0' instead. I use the following codes:

FillSafeArray_R4(atof(csFData[nColumn]), nRow, &saRet[nColumn]);

void FillSafeArray_R4(float d, int iRow, COleSafeArray* sa)
{
      VARIANT v;
      long index[2];

      VariantInit(&v);
      v.vt = VT_R4;
      index[0] = iRow;
      index[1] = 0;
      sa->PutElement(index, &d);
      VariantClear(&v);
}

The problem is that I cannot get 'd' in the FillSafeArray_R4 to show as NULL, instead of 0.0000. What shoud I do to show blank cells when I have defined the cells as number (float)?

Thank you.
jper_hmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance programmer / ConsultantCommented:
You may have to change the formatting of the cell.
0
AndyAinscowFreelance programmer / ConsultantCommented:
Further - have the cells formatted that 0 is displayed as a blank.  (This won't work if 0 is also a valid number to display, in that case you would have to fill each cell individually)
0
jper_hmAuthor Commented:
I have fixed some columns to receive only numbers, and I don't know how to change the format for some of the cells. My code looks something like this:

DWORD numElements[]={cElements,1};
COleSafeArray saRet[10];
for(int n=1; n<=10; n++)
      saRet[n].Create(VT_R4, 2, numElements);
...
...
FillSafeArray_R4(CString2Double(csFData[nColumn]), nRow, &saRet[nColumn]);
...
...
for (int n=1; n<=10; n++)
{
   cell1 = range.GetItem(COleVariant((long)1),COleVariant((long)10));
   cell2 = range.GetItem(COleVariant((long)2),COleVariant((long)10));
   range1 = sheet.GetRange(cell1, cell2);
   VariantClear(&cell1);
   VariantClear(&cell2);
   range1 = range1.GetResize(COleVariant(cElements),COleVariant((short)1));
   range1.SetValue(COleVariant(saRet[n]));
}

Thanks you
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

AndyAinscowFreelance programmer / ConsultantCommented:
I meant  that you modify the formatting in the excel sheet itself, not by automation.
something like
#;-#;"";""
for the user defined format  (It is possible to format the cell also by automation, just much simpler to do it from excel if you supply the worksheet)
0
jper_hmAuthor Commented:
How to format a number cells to display 0 as a blank?
0
jper_hmAuthor Commented:
I need to format the cells by automation, as the final worksheet is generated by the users.
0
AndyAinscowFreelance programmer / ConsultantCommented:
Try
      range.SetNumberFormat(COleVariant(_T("#;-#;'';''")));
where range is a range of cells you wish to format
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jper_hmAuthor Commented:
I use

range.SetNumberFormat(COleVariant("0.0###;-0.0###;\"\""));

It seems to give me the best formatting I want. One more question: what is the meaning for the code "" (two double-quotes)? Why do you need to repeat it in 2 sections? I just use one and it seems to work all right.

Your last message reduced to one double-quote in one section, will it work?

Thanks.
0
AndyAinscowFreelance programmer / ConsultantCommented:
Number formatting contains 4 sections.  +ve, -ve, zero, NULL - if you leave the later ones out then the default behaviour takes place.  (The NULL could be useful to show missing entries for example)
0
jper_hmAuthor Commented:
I see. Thanks for the explanation.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.