Search results

Formatting in ASP.NET Core Spreadsheet control

Formatting options make your data easier to view and understand. The different types of formatting options in the Spreadsheet are,

  • Number Formatting
  • Text Formatting
  • Cell Formatting

Number Formatting

Number formatting provides a type for your data in the Spreadsheet. Use the allowNumberFormatting property to enable or disable the number formatting option in the Spreadsheet. The different types of number formatting supported in Spreadsheet are,

Types Format
General(default) NA
Number 0.00
Currency $#,##0.00
Accounting _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
ShortDate mm-dd-yyyy
LongDate dddd, mmmm dd, yyyy
Time h:mm:ss AM/PM
Percentage 0.00%
Fraction # ?/?
Scientific 0.00E+00
Text @

Number formatting can be applied in following ways,

  • Using the format property in cell, you can set the desired format to each cell at initial load.
  • Using the numberFormat method, you can set the number format to a cell or range of cells.
  • Selecting the number format option from ribbon toolbar.

The following code example shows the number formatting in cell data.

tagHelper
numberFormatController.cs
<ejs-spreadsheet id="spreadsheet" created="created" showRibbon="false" showSheetTabs="false" showFormulaBar="false" allowDelete="false" allowInsert="false">
        <e-spreadsheet-sheets>
            <e-spreadsheet-sheet selectedRange="U15">
                <e-spreadsheet-ranges>
                    <e-spreadsheet-range dataSource="ViewBag.DefaultData" startCell="A2"></e-spreadsheet-range>
                </e-spreadsheet-ranges>
                <e-spreadsheet-rows>
                    <e-spreadsheet-row height="35" customHeight="true">
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell value="Sales Team Summary" colSpan="6">
                                <e-spreadsheet-cellstyle textAlign="Center" fontWeight="Bold" verticalAlign="Middle" fontStyle="Italic" fontSize="16pt" border="1px solid #e0e0e0" backgroundColor="#EEEEEE" color="#279377"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row index="10">
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell value="Total:" index="1">
                                <e-spreadsheet-cellstyle fontWeight="Bold" verticalAlign="Middle" fontStyle="Italic"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                            <e-spreadsheet-cell formula="=SUM(C3:C10)" format="$#,##0.00"></e-spreadsheet-cell>
                            <e-spreadsheet-cell formula="=SUM(D3:D10)" format='_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@@_)'></e-spreadsheet-cell>
                            <e-spreadsheet-cell formula="=SUM(E3:E10)" format='_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@@_)'></e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                </e-spreadsheet-rows>
                <e-spreadsheet-columns>
                    <e-spreadsheet-column width="140"></e-spreadsheet-column>
                    <e-spreadsheet-column width="140"></e-spreadsheet-column>
                    <e-spreadsheet-column width="160"></e-spreadsheet-column>
                    <e-spreadsheet-column width="160"></e-spreadsheet-column>
                    <e-spreadsheet-column width="160"></e-spreadsheet-column>
                    <e-spreadsheet-column width="120"></e-spreadsheet-column>
                </e-spreadsheet-columns>
            </e-spreadsheet-sheet>
        </e-spreadsheet-sheets>
    </ejs-spreadsheet>


    <script>

       function created() {
        this.cellFormat({ fontWeight: 'bold', fontSize: '12pt', backgroundColor: '#279377', textAlign: 'center', color: '#ffffff', borderBottom: '1px solid #e0e0e0' }, 'A2:F2');
        this.cellFormat({ borderTop: '1px solid #e0e0e0', backgroundColor: '#EEEEEE' }, 'A11:F11');
        this.setBorder({ border: '1px solid #e0e0e0' }, 'A2:F11', 'Outer');
        // Applied Accounting format to the cells from C3 to E10 range.
        this.numberFormat('_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@@_)', 'C3:E10');
        // Applied Percentage format to the cells from C3 to E11 range.
        this.numberFormat('0%', 'F3:F10');
    }

    </script>
public IActionResult Index()
        {
             List<object> data = new List<object>()
            {
               new { Salesperson= "Jeffrey Burke",  RegionCovered= "Oklahoma",  February2019Sales= "28000",  CostofSales= "2460",  January2019Sales= "21238", PercentChange = ".32"},
               new { Salesperson= "Amy Fernandez",  RegionCovered= "North Carolina",  February2019Sales= "23138",  CostofSales= "1521",  January2019Sales= "23212", PercentChange = "0"},
               new { Salesperson= "Mark Hayes",  RegionCovered= "Massachusetts",  February2019Sales= "25092",  CostofSales= "1521",  January2019Sales= "20454", PercentChange = ".23"},
               new { Salesperson= "Judith Ray",  RegionCovered= "California",  February2019Sales= "21839",  CostofSales= "1923",  January2019Sales= "24619", PercentChange = "-.11"},
               new { Salesperson= "Rany Graham",  RegionCovered= "South Carolina",  February2019Sales= "23342",  CostofSales= "2397",  January2019Sales= "20045", PercentChange = ".16"},
               new { Salesperson= "Christina Foster",  RegionCovered= "Delaware",  February2019Sales= "23368",  CostofSales= "1500",  January2019Sales= "17537", PercentChange = ".33"},
               new { Salesperson= "Judy Green",  RegionCovered= "Texas",  February2019Sales= "21510",  CostofSales= "1657",  January2019Sales= "17537", PercentChange = "-.14"},
               new { Salesperson= "Paula Hall",  RegionCovered= "Virginia",  February2019Sales= "21314",  CostofSales= "2418",  January2019Sales= "18082", PercentChange = ".18"},
            };
            ViewBag.DefaultData = data;
            return View();
        }

Text and cell formatting

Text and cell formatting enhances the look and feel of your cell. It helps to highlight a particular cell or range of cells from a whole workbook. You can apply formats like font size, font family, font color, text alignment, border etc. to a cell or range of cells. Use the allowCellFormatting property to enable or disable the text and cell formatting option in Spreadsheet. You can set the formats in following ways,

  • Using the style property, you can set formats to each cell at initial load.
  • Using the cellFormat method, you can set formats to a cell or range of cells.
  • You can also apply by clicking the desired format option from the ribbon toolbar.

Fonts

Various font formats supported in the spreadsheet are font-family, font-size, bold, italic, strike-through, underline and font color.

Text Alignment

You can align text in a cell either vertically or horizontally using the textAlign and verticalAlign property.

Indents

To enhance the appearance of text in a cell, you can change the indentation of a cell content using textIndent property.

Fill color

To highlight cell or range of cells from whole workbook you can apply background color for a cell using backgroundColor property.

Borders

You can add borders around a cell or range of cells to define a section of worksheet or a table. The different types of border options available in the spreadsheet are,

Types Actions
Top Border Specifies the top border of a cell or range of cells.
Left Border Specifies the left border of a cell or range of cells.
Right Border Specifies the right border of a cell or range of cells.
Bottom Border Specifies the bottom border of a cell or range of cells.
No Border Used to clear the border from a cell or range of cells.
All Border Specifies all border of a cell or range of cells.
Horizontal Border Specifies the top and bottom border of a cell or range of cells.
Vertical Border Specifies the left and right border of a cell or range of cells.
Outside Border Specifies the outside border of a range of cells.
Inside Border Specifies the inside border of a range of cells.

You can also change the color, size, and style of the border. The size and style supported in the spreadsheet are,

Types Actions
Thin Specifies the 1px border size (default).
Medium Specifies the 2px border size.
Thick Specifies the 3px border size.
Solid Used to create the solid border (default).
Dashed Used to create the dashed border.
Dotted Used to create the dotted border.
Double Used to create the double border.

Borders can be applied in the following ways,

  • Using the border, borderLeft, borderRight, borderBottom properties, you can set the desired border to each cell at initial load.
  • Using the setBorder method, you can set various border options to a cell or range of cells.
  • Selecting the border options from ribbon toolbar.

The following code example shows the style formatting in text and cells of the spreadsheet.

tagHelper
cellFormatController.cs
<ejs-spreadsheet id="spreadsheet" created="created" showRibbon="false" showSheetTabs="false" showFormulaBar="false" allowDelete="false" allowInsert="false">
        <e-spreadsheet-sheets>
            <e-spreadsheet-sheet selectedRange="U15" showGridLines="false">
                <e-spreadsheet-ranges>
                    <e-spreadsheet-range dataSource="ViewBag.DefaultData" startCell="A2"></e-spreadsheet-range>
                </e-spreadsheet-ranges>
                <e-spreadsheet-rows>
                    <e-spreadsheet-row height="40" customHeight="true">
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell value="Order Summary" colSpan="5">
                                <e-spreadsheet-cellstyle textAlign="Center" fontWeight="Bold" verticalAlign="Middle" fontStyle="Italic" fontSize="16pt" border="1px solid #e0e0e0" backgroundColor="#EEEEEE" color="#279377"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row height="30">
                        <e-spreadsheet-cells>
                            <e-spreadsheet-cell index="2">
                                <e-spreadsheet-cellstyle textAlign="Right"></e-spreadsheet-cellstyle>
                            </e-spreadsheet-cell>
                        </e-spreadsheet-cells>
                    </e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                    <e-spreadsheet-row height="30"></e-spreadsheet-row>
                </e-spreadsheet-rows>
                <e-spreadsheet-columns>
                    <e-spreadsheet-column width="100"></e-spreadsheet-column>
                    <e-spreadsheet-column width="200"></e-spreadsheet-column>
                    <e-spreadsheet-column width="110"></e-spreadsheet-column>
                    <e-spreadsheet-column width="140"></e-spreadsheet-column>
                    <e-spreadsheet-column width="90"></e-spreadsheet-column>
                </e-spreadsheet-columns>
            </e-spreadsheet-sheet>
        </e-spreadsheet-sheets>
    </ejs-spreadsheet>


    <script>

      
    function created() {
        // Setting common styles to table header cells
        this.cellFormat({ fontWeight: 'bold', fontSize: '12pt', backgroundColor: '#279377', color: '#ffffff' }, 'A2:E2');
        // Setting common styles to whole table cells
        this.cellFormat({ verticalAlign: 'middle', fontFamily: 'Axettac Demo' }, 'A2:E12');
        // Column wise styles setting
        this.cellFormat({ textAlign: 'center' }, 'A2:A12');
        // Setting text-indent to 2 and 4 column
        var style = { textAlign: 'left', textIndent: '8pt' };
        this.cellFormat(style, 'B2:B12');
        this.cellFormat(style, 'D2:D12');
        this.cellFormat({ fontStyle: 'italic', textAlign: 'right' }, 'C3:C12');
        this.cellFormat({ textAlign: 'center' }, 'E2:E12');
        // Applied border to range of cells using 'setBorder' method
        this.setBorder({ borderLeft: '1px solid #e0e0e0', borderRight: '1px solid #e0e0e0' }, 'A2:E2');
        this.setBorder({ border: '1px solid #e0e0e0' }, 'A4:E11', 'Horizontal');
        this.setBorder({ border: '1px solid #e0e0e0' }, 'A3:E12', 'Outer');
        this.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E3:E4');
        this.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E9');
        this.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E12');
        this.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E5');
        this.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E8');
        this.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E11');
        this.cellFormat({ color: '#62c9e8' }, 'E6');
        this.cellFormat({ color: '#62c9e8' }, 'E10');
        this.cellFormat({ color: '#ff5b5b' }, 'E7');
    }

    </script>
public IActionResult Index()
        {
            List<object> data = new List<object>()
            {
               new { OrderId= "SF1001",  Product= "Laptop Backpack (Blue)",  OrderedDate= "02/14/2014",  OrderedBy= "Rahul Sharma",  Shipment= "Delivered"},
               new { OrderId= "SF1002",  Product= "Oppo F1 S mobile back cover",  OrderedDate= "06/11/2014",  OrderedBy= "Adi Pathak",  Shipment= "Delivered"},
               new { OrderId= "SF1003",  Product= "Tupperware 4 bottle set",  OrderedDate= "07/27/2014",  OrderedBy= "Himani Arora",  Shipment= "Pending"},
               new { OrderId= "SF1004",  Product= "Tupperware Lunch box",  OrderedDate= "11/21/2014",  OrderedBy= "Samuel Samson",  Shipment= "Shipped"},
               new { OrderId= "SF1005",  Product= "Panosonic Hair Dryer",  OrderedDate= "06/23/2014",  OrderedBy= "Neha",  Shipment= "Cancelled"},
               new { OrderId= "SF1006",  Product= "Philips LED 2 bulb set",  OrderedDate= "07/22/2014",  OrderedBy= "Christine J",  Shipment= "Pending"},
               new { OrderId= "SF1007",  Product= "Moto G4 plus headphone",  OrderedDate= "02/04/2014",  OrderedBy= "Shiv Nagar",  Shipment= "Delivered"},
               new { OrderId= "SF1008",  Product= "Lakme Eyeliner Pencil",  OrderedDate= "11/30/2014",  OrderedBy= "Cherry",  Shipment= "Shipped"},
               new { OrderId= "SF1009",  Product= "Listerine mouthwash",  OrderedDate= "07/09/2014",  OrderedBy= "Siddartha Mishra",  Shipment= "Pending"},
               new { OrderId= "SF1010",  Product= "Protinex original",  OrderedDate= "10/31/2014",  OrderedBy= "Ravi Chugh",  Shipment= "Delivered"},
            };
            ViewBag.DefaultData = data;
            return View();
        }

See Also