[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-11-11
10
Medium Priority
?
819 Views
Last Modified: 2013-11-20
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.
0
Comment
Question by:jper_hm
  • 5
  • 5
10 Comments
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 12563610
You may have to change the formatting of the cell.
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 12563625
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
 

Author Comment

by:jper_hm
ID: 12563689
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Expert Comment

by:AndyAinscow
ID: 12563843
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
 

Author Comment

by:jper_hm
ID: 12563895
How to format a number cells to display 0 as a blank?
0
 

Author Comment

by:jper_hm
ID: 12563919
I need to format the cells by automation, as the final worksheet is generated by the users.
0
 
LVL 45

Accepted Solution

by:
AndyAinscow earned 2000 total points
ID: 12564931
Try
      range.SetNumberFormat(COleVariant(_T("#;-#;'';''")));
where range is a range of cells you wish to format
0
 

Author Comment

by:jper_hm
ID: 12565202
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
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 12565514
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
 

Author Comment

by:jper_hm
ID: 12566061
I see. Thanks for the explanation.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In this post we will learn different types of Android Layout and some basics of an Android App.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question