Display 0 as “- “ and preserve the real numeric value in JavaScript

Background:

To display 0 as “- “ and preserve the real numeric value we create a custom cell type based on the text cell type, and utilize the paint and setDefaultStyle methods.

Note: Cell Types are native to SpreadJS and the cell type functionality will not be exported with the SpreadJS instance to an xlsx file. In this example, the numeric value 0 is preserved, so when exported the 0's will appear as 0’s not “-”

Steps to Complete:

1. Create a custom cell type based on the text cell type

2. Utilize the paint method

3. Paint over the identified values that are 0 as a –

4. Set the newly created cell type using the setDefaultStyle method

Getting Started:

Create a custom cell type based on the text cell type:

function ZeroCells() {}
ZeroCells.prototype = new GC.Spread.Sheets.CellTypes.Text();

Utilize the paint method:

Use the paint method to control how a cell is painted, this is how we will change just the appearance of 0 to be “–”

ZeroCells.prototype.paint = function(
        ctx,
        value,
        x,
        y,
        w,
        h,
        style,
        options
      ) {}

Paint over the identified values that are 0 as a “–”:

Create an If/Else statement that will be used with the paint method to paint over the identified values that are 0 as a “–”.

We will change the value of the paint method to "        -" in the if statement
Note: Ensure to use the same number of space to have proper spacing

if (value === 0) {
          GC.Spread.Sheets.CellTypes.Base.prototype.paint.apply(this, [
            ctx,
            "        -",
            x,
            y,
            w,
            h,
            style,
            options
          ]);
     }
else {
          GC.Spread.Sheets.CellTypes.Base.prototype.paint.apply(this, [
            ctx,
            value,
            x,
            y,
            w,
            h,
            style,
            options
          ]);
        }

Set the newly created cell type using the setDefaultStyle method:

Set the newly created cell type to the style of our worksheet

var cellType = new ZeroCells();
      var defaultStyle = new GC.Spread.Sheets.Style();
        defaultStyle.cellType = cellType;    

Apply this cell type to the entire Spread instance by using the setDefaultStyle method

//sheet is referencing the active spreadsheet
sheet.setDefaultStyle(
          defaultStyle,
          GC.Spread.Sheets.SheetArea.viewport
        );