Skip to main content

DHTMLX Spreadsheet. Custom formats example

This demo shows how to define custom number formats in DHTMLX Spreadsheet using the formats configuration property, enabling currency symbols, custom masks, and locale-specific formatting.

Live example​

const spreadsheet = new dhx.Spreadsheet("spreadsheet", {
    // custom number formats
    formats: [
        {
            name: "U.S. Dollar",
            id: "currency",
            mask: "$#,##0.00"
        },
        {
            name: "Euro",
            id: "euro",
            mask: "[$€]#.##0,00",
            example: "1000.50"
        },
        {
            name: "Swiss franc",
            id: "franc",
            mask: "[$CHF ]#.##0,00"
        }
    ],
    menu: true
});

spreadsheet.parse(dataset);
<!-- component container -->
<div style="height: 100%; max-width:100%" id="spreadsheet"></div>

<!-- dataset -->

<script>
const dataset = {
      styles: {
          bold: {
              "font-weight": "bold",
          },
          right: {
              "justify-content": "flex-end",
              "text-align": "right",
          },
      },
      data: [
      { cell: "a1", value: "Country", css:"bold" },
      { cell: "b1", value: "Product", css:"bold" },
      { cell: "c1", value: "Price", css:"right bold" },
      { cell: "d1", value: "Amount", css:"right bold" },
      { cell: "e1", value: "Total Price", css:"right bold" },

      { cell: "a2", value: "Ecuador" },
      { cell: "b2", value: "Banana" },
      { cell: "c2", value: 6.68, format: "euro" },
      { cell: "d2", value: 430 },
      { cell: "e2", value: 2872.4, format: "euro" },

      { cell: "a3", value: "Belarus" },
      { cell: "b3", value: "Apple" },
      { cell: "c3", value: 3.75, format: "euro" },
      { cell: "d3", value: 600 },
      { cell: "e3", value: 2250, format: "euro" },

      { cell: "a4", value: "Peru" },
      { cell: "b4", value: "Grapes" },
      { cell: "c4", value: 7.69, format: "euro" },
      { cell: "d4", value: 740 },
      { cell: "e4", value: 5690.6, format: "euro" },

      { cell: "a5", value: "Egypt" },
      { cell: "b5", value: "Orange" },
      { cell: "c5", value: 5.86, format: "euro" },
      { cell: "d5", value: 560 },
      { cell: "e5", value: 3281.6, format: "euro" },

      { cell: "a6", value: "South Africa" },
      { cell: "b6", value: "Grapefruit" },
      { cell: "c6", value: 8.58, format: "euro" },
      { cell: "d6", value: 800 },
      { cell: "e6", value: 6864, format: "euro" },

      { cell: "a7", value: "Spain" },
      { cell: "b7", value: "Lemon" },
      { cell: "c7", value: 9.12, format: "euro" },
      { cell: "d7", value: 650 },
      { cell: "e7", value: 5928, format: "euro" },

      { cell: "a8", value: "Iran" },
      { cell: "b8", value: "Pomegranate" },
      { cell: "c8", value: 9.67, format: "euro" },
      { cell: "d8", value: 300 },
      { cell: "e8", value: 2901, format: "euro" }
    ],
  };
</script>

Financial and accounting applications often require displaying values in multiple currencies or region-specific number formats: euros with comma decimals, Swiss francs with space separators, or yen without decimal places. Custom formats let developers define exactly how numbers appear without altering the underlying data.

This example configures the Spreadsheet with a formats array containing three format objects: U.S. Dollar ("$#,##0.00"), Euro ("[$€]#.##0,00"), and Swiss franc ("[$CHF ]#.##0,00"). Each format includes an id, mask, and name. The dataset references these formats via the format property on individual cells, such as { cell: "C2", value: 6.68, format: "euro" }.

Solution overview​

  1. Define a formats array with objects containing id, mask, name, and optionally example
  2. Pass the formats array in the Spreadsheet constructor config
  3. Set format on individual data cells when calling parse() to apply a format at load time

Key points​

  • Mask characters: # omits insignificant zeros, 0 shows them; use [$€] or [$CHF ] for custom currency symbols within brackets

API reference​

  • formats: Defines the list of number formats available in the Spreadsheet.

Additional resources​