Posted 8 September 2017, 1:41 am EST
Some spreadsheet applications [ex. Excel, OpenOffice] provide an option for displaying zero values as empty cells [ex. Tools | Options | View | ZeroValues in Excel 2000, Tools | Options | Spreadsheet | View | ZeroValues in OpenOffice 1.0]. FarPoint’s Spread for WinForms product does not provide this option.
The above mentioned work arounds are worth considering, but be careful of a few things.
An empty cell value [null in C# or Nothing in VB], an empty string value [“”], and a zero number value [0.0] are three distinct values. While most built-in operators and functions [ex. SUM] will treat the three values the same, a few buit-in functions [ex. ISBLANK, ISNUMBER] will treat the three values differently. Be aware of these small differences.
Spread uses the same formula syntax as Excel. This syntax does not provide a means of representing an empty cell value [null].
Scotts’s solution for caluclated values [if(SUM(…)=0,“”,SUM(…))] replaces a zero number value [0.0] with an empty string value [“”]. Be aware that an empty string value [“”] is different that an empty cell value [null].
Canis’s solution for caluclated values [if(SUM(…)=0,DBNull.Value,SUM(…))] replaces a zero number [0.0] with a custom name [DBNull.Value]. Unless the custom name is defined [see AddCustomName method in SheetView class], the custom name will evaluate to a #NAME? error value. This is most likely not the intended result.
Canis’s attempted solution for user entered values [if(RC=0,…,RC)] results in a circular reference. This is most likely not the intended result. If iterations are off [see Iterataion property in SheetView class] then the formulas will never be evaluated. If iterations are on then the formulas may not be evaluated in the desired order.
Another solution is to use custom cell types which render zero number values as empty cell values. This would more closely mimic Excel’s feature. The code for a custom cell type might look something like…
```
in c#:
public class MyGeneralCellType : GeneralCellType
{
public override void PaintCell(System.Drawing.Graphics g, System.Drawing.Rectangle r, FarPoint.Win.Spread.Appearance appearance, object value, bool isSelected, bool isLocked, float zoomFactor)
{
if (value is double && (double)value == 0.0)
value = null;
base.PaintCell(g, r, appearance, value, isSelected, isLocked, zoomFactor);
}
}