Formatting the Essential JavaScript Spreadsheet: Number Formatting

Spreadsheet is one of the most popular file formats, used to view, analyze, and calculate data. It is used in calculating tax, goods quantity, total marks in a student report, expenses, and so on.

The Syncfusion JavaScript Spreadsheet is a feature-rich control that supports various formatting options to display a cell or content. Its appearance will help you to view and understand the data. The following are the different types of formatting options available in Syncfusion Spreadsheet:

• Number formatting
• Cell formatting
• Conditional formatting

In this blog, we will look at the number formatting and how to use it in the Syncfusion Essential JavaScript Spreadsheet control with appropriate code. I will cover the other formatting types in my future blogs.

Number formatting

Number formatting is used to present the numerical type of the data in the Spreadsheet cells. It will change the numbers’ look without changing the actual data. This feature will automatically detect the type of data in the cell, thereby reducing our effort. The feature helps the Spreadsheet to look more professional and attractive.

The following table has the list of types and formatting applied to them in our Spreadsheet control.

You can enable or disable the number formatting by using the allowNumberFormatting property.

Where can you find number formats?

You can find the built-in number formats in a drop-down menu in the ribbon’s Home tab. Here, we have provided the active cell type in the preview option. It will be helpful to know the number format before applying the formatting.

Refer to the following screenshot.

This will give you a better experience, similar to the number formatting feature in MS Excel.

Adding number formatting in Spreadsheet through code

In the Syncfusion JavaScript Spreadsheet control, you can add number formatting by using the numberFormat public method and pass the format type and range as parameters to this method. Also, you can use the format property to set the number format string to a cell object.

The following code example showcases the number formatting.

/**
 * Number formatting sample.
 */
let sheet: SheetModel[] = [{
        name: 'Restaurant Invoice',
        rows: [
          {
                index: 14, cells: [
                    { index: 1, value: 'Subtotal:' },
                    { index: 4, formula: '=SUBTOTAL(9,E4:E14)', format: '$#,##0.00' }
                ]
            },
            {
                cells: [
                    { index: 1, value: 'Discount (8%):' },
                    { index: 4, formula: '=PRODUCT(8,E15)/100', format: '$#,##0.00' }
                ]
            },
            {
                cells: [
                    { index: 1, value: 'Total Amount:' },
                    {
                        index: 4, formula: '=SUM(E15-E16)',
                        format: getFormatFromType('Accounting')
                    }
                ]
            }
        ]
    }];

    // Initialize Spreadsheet component.
    let spreadsheet: Spreadsheet = new Spreadsheet({
        sheets: sheet,
        created: (): void => {
            spreadsheet.numberFormat('$#,##0.00', 'D4:E14');
            spreadsheet.numberFormat('$#,##0.00', 'E15:E17');
        }
    });
    // Render initialized Spreadsheet component.
    spreadsheet.appendTo('#spreadsheet');

You can also use the getFormatFromType method to get the format code from the format type like number, currency, and accounting. So, this will be very useful while preparing the number formatting sample in the JavaScript Spreadsheet.

The following screenshot will show you the number formatting in Spreadsheet.

Conclusion

I hope you now have a better understanding of the number formatting feature in the Syncfusion JavaScript Spreadsheet control. In the upcoming blog, we will discuss the cell formatting feature. What else do you expect from a spreadsheet? Please share your thoughts in the comments section below.

If you’re already a Syncfusion user, you can download the Essential Studio® for JavaScript product setup to try out this control. Otherwise, you can download a free 30-day trial.

If you have any questions about these features, please contact us through our support forum, Direct-Trac, or feedback portal. We are always happy to assist you!