asked on

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.

AndyAinscow

You may have to change the formatting of the cell.

AndyAinscow

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)

jper_hm

ASKER

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

AndyAinscow

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)

jper_hm

ASKER

How to format a number cells to display 0 as a blank?

jper_hm

ASKER

I need to format the cells by automation, as the final worksheet is generated by the users.

ASKER CERTIFIED SOLUTION

AndyAinscow

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jper_hm

ASKER

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.

AndyAinscow

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)

jper_hm

ASKER

I see. Thanks for the explanation.