Formatting cells with numeric values

Formatting cells with numeric values

Posted by: 16pa1a0576 on 20 September 2021, 5:00 am EST

• 

  16pa1a0576

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 20 September 2021, 5:00 am EST

  Hi,
  
  I have several columns which are at random indexes with numeric values in all the cells of the column. In my worksheet i have not only numeric values, but also string , date columns as well. I have to format the numeric cells such that if a cell is having 1000 as a value it should be 1,000 and , if 123.456 then it should be formatted such that value is 123.46.
  
  Can you please help me with this?

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 21 September 2021, 3:00 am EST

  Hi,

  For this, you need to create a custom format. Please refer to the following code snippet and attached sample that demonstrates the same.

  
  function CustomNumberFormat(formatString) {
      this.formatString = formatString ? formatString : "##,###.###############";
    }
  
    CustomNumberFormat.prototype = new GC.Spread.Formatter.FormatterBase();
    CustomNumberFormat.prototype.format = function (obj, formattedData) {
      if (typeof obj === "number") {
        let currentCultureName = GC.Spread.Common.CultureManager.culture();
        let defaultFormatter = new GC.Spread.Formatter.GeneralFormatter(
          this.formatString,
          currentCultureName
        );
        let formatString = defaultFormatter.format(obj);
        let decSeperator = GC.Spread.Common.CultureManager.getCultureInfo(
          currentCultureName
        ).NumberFormat.numberDecimalSeparator;
  
        /*remove the last decimal character from the string ex: 1,000. ==>1,000*/
        formatString =
          formatString[formatString.length - 1] === decSeperator
            ? formatString.substring(0, formatString.length - 1)
            : formatString;
        return formatString;
      }
      return obj;
    };
  
    CustomNumberFormat.prototype.parse = function (str) {
      return new GC.Spread.Formatter.GeneralFormatter().parse(str);
    };
    spread
      .getActiveSheet()
      .setFormatter(0, 0, new CustomNumberFormat("##,##.##"));
    spread.getActiveSheet().setValue(0, 0, 1234.567);
    spread.getActiveSheet().setColumnWidth(0, 120);
  
  

  sample: https://codesandbox.io/s/customformat-forked-oepgx?file=/src/index.js:2080-3426

  Regards
  
  Avinash

• 

  16pa1a0576

  • Post Options:
  • Link
    Copy

  Posted 23 September 2021, 4:30 am EST

  Lets say, i have a column with strings. Assume a string value as “123456” this is also getting formatted. So to avoid that, should i need to apply formatter for every column based on the type? However, i am using setDataSource method for filling the sheet with data.
  
  If so how can i handle -ve numbers as well ?

  "##,##.##"

  formatter not handling for large numbers. And lets say we have cell value as 120 or whole number, we should not get any decimal points

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 23 September 2021, 5:55 am EST

  Hi,

  1. I have a column with strings. Assume a string value as “123456” this is also getting formatted.

  Setting the formatter of the column would be the recommended approach. Please refer to the following sample that demonstrates how you use the formatter on column level when using the setDataSource method.

  sample: https://codesandbox.io/s/customformat-forked-oepgx?file=/src/index.js:754-756

  2. If so how can I handle -ve numbers as well?

  You need to apply the formatter accordingly. Further, the provided above works well on negative numbers. Could you please explain exactly the issue that you facing?

  3. ``“##,##.##”` formatter not handling for large numbers

  Could you please explain more about the issue and what number you are formatting using this format and is the result that you expecting?

  4. let’s say we have a cell value as 120 or a whole number, we should not get any decimal points.

  Actually, the requirement is the combination of two formatter first formatter will apply ##,##.## to number if the number contains floating-point digit and the second formatter applied when the number is an integer that is, showing only the number, not decimal point.

  For merging these formatter we need to create a custom formatter that is provided in above sample
  
  Further, The custom format that we provided is working fine with this(120) number. please refer to the following sample.

  sample: https://codesandbox.io/s/customformat-forked-xmxgz?file=/index.html

  Regards,
  
  Avinash

• 

  16pa1a0576

  • Post Options:
  • Link
    Copy

  Posted 26 September 2021, 1:27 am EST

  Cells are showing #### if the value is occupying more length than width of the cell before expanding. After expanding the value is shown correctly. How to resolve this ?

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 27 September 2021, 6:26 am EST

  Hi,

  This is expected behavior from SJS. When the formatted text is larger than the column’s width it shows the #### value just like the excel. For this, you may use the auto-fit column method. Please refer to the following code snippet and attached sample that demonstrates the same.

  
  for (let i = 0; i < sheet.getColumnCount(); i++) {
      sheet.autoFitColumn(i);
    }
  
    sheet.bind(GC.Spread.Sheets.Events.CellChanged, (e, args) => {
      args.sheet.autoFitColumn(args.col);
    });
  
  

  sample: https://codesandbox.io/s/customformat-forked-srwlw?file=/src/index.js:1119-1313

  Regards
  
  Avinash