Cell Range in ASP.NET MVC Spreadsheet control

30 Jul 202624 minutes to read

A group of cells in a sheet is known as cell range.

Wrap text

Wrap text displays lengthy content on multiple lines within a single cell. By default, wrap text support is enabled. Use the allowWrap property to enable or disable wrap text support in the Spreadsheet.

You can apply or remove wrap text in the following ways:

  • Using the wrap property in cell, you can enable or disable wrap text to a cell at initial load.
  • Select or clear the Wrap button in the ribbon toolbar to apply or remove wrap text for the selected range.
  • Using the wrap method, you can apply or remove the wrap text once the component is loaded.

The wrap method accepts the following arguments:

  • address: Specifies the cell or cell range to which wrap text is applied.
  • wrap: Specifies whether wrap text is enabled or disabled.

Use true to apply wrap text and false to remove it.

The following code example demonstrates wrap text functionality in the Spreadsheet.

@Html.EJS().Spreadsheet("spreadsheet").ShowFormulaBar(false).Created("created").Sheets(sheet =>
{
    sheet.Name("Movie List").Ranges(ranges =>
    {
        ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();
    }).Rows(row =>
    {
        row.Height(30).Add();
        row.Cells(cell =>
        {
            cell.Index(7).Wrap(true).Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Index(7).Wrap(true).Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Index(7).Wrap(true).Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Index(7).Wrap(true).Add();
        }).Add();
    }).Columns(column =>
    {
        column.Width(100).Index
        (1).Add();
        column.Width(140).Add();
        column.Width(90).Add();
        column.Width(150).Add();
        column.Width(120).Add();
        column.Width(90).Add();
        column.Width(180).Add();
    }).Add();
}).Render()

<script>

    function created() {
        this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
        this.cellFormat({ verticalAlign: 'middle' }, 'A1:H5');
        this.cellFormat({ textAlign: 'center' }, 'A2:B5');
        this.cellFormat({ textAlign: 'center' }, 'D2:D5');
        // To wrap the cells from E2 to E5 range
        this.wrap('E2:E5');
        // To unwrap the H3 cell
        this.wrap('H3', false);
    }

</script>
public ActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { No= "1",  ReleasedOn= "1994",  Title= "Forrest Gump",  Rating= "5 Stars",  Casts= "Tom Hanks, Robin Wright, Gary Sinise",  DirectedBy= "Robert Zemeckis", Genre= "Drama", Comments= "Based on the 1986 novel of the same name by Winston Groom" },
        new { No= "2",  ReleasedOn= "1946",  Title= "It’s a Wonderful Life",  Rating= "2 Stars",  Casts= "James Stewart, Donna Reed, Lionel Barrymore",  DirectedBy= "Frank Capra", Genre= "Drama", Comments= "Colorized version"  },
        new { No= "3",  ReleasedOn= "1988",  Title= "Big",  Rating= "4 Stars",  Casts= "Tom Hanks, Elizabeth Perkins, Robert Loggia",  DirectedBy= "Penny Marshall", Genre= "Comedy", Comments= "A thirteen-year-old boy wishes to be big, and his wish comes true."  },
        new { No= "4",  ReleasedOn= "1954",  Title= "Rear Window",  Rating= "4 Stars",  Casts= "James Stewart, Grace Kelly, Wendell Corey",  DirectedBy= "Alfred Hitchcock" , Genre= "Suspense", Comments= "Truly suspenseful and masterfully crafted" },
    };
    ViewBag.DefaultData = data;
    return View();
}

Limitations of Wrap text

The following features have some limitations in wrap text:

  • Sorting with wrap text applied data.
  • Merge with wrap text

Merge cells

The Merge Cells feature allows users to combine two or more cells into a single cell. When cells containing multiple values are merged, the value of the top-left cell is retained in the merged cell. By default, the Merge Cells feature is enabled. Use the allowMerge property to enable or disable this feature in the Spreadsheet.

You can merge cells in the following ways:

  • Set the rowSpan and colSpan properties of a cell to merge cells during the initial rendering.
  • Select a range and choose the required merge option from the ribbon toolbar.
  • Use the merge method to merge a range after the component is loaded.

The merge method requires a cell range and the applicable merge type. The range identifies the cells to merge, and the merge type determines whether all cells, rows, or columns in the range are merged.

The available merge options in spreadsheet are,

Type Action
Merge All Combines all cells in the selected range into a single cell. This is the default option.
Merge Horizontally Combines the cells in each row of the selected range.
Merge Vertically Combines the cells in each column of the selected range.
UnMerge Separates the merged cell into individual cells.

The following code example shows the merge cells operation in spreadsheet.

@Html.EJS().Spreadsheet("spreadsheet").ShowFormulaBar(false).Created("created").Sheets(sheet =>
{
    sheet.Name("Movie List").Ranges(ranges =>
    {
        ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();
    }).Rows(row =>
    {
        row.Height(35).Add();
        row.Height(35).Cells(cell =>
        {
            cell.Index(1).RowSpan(2).Add();
            cell.ColSpan(2).Add();
            cell.Index(6).ColSpan(3).Add();
            cell.Index(10).RowSpan(2).ColSpan(3).Add();
            cell.ColSpan(2).Index(13).Add();
            cell.ColSpan(2).Index(17).Add();
        }).Add();
        row.Height(35).Cells(cell =>
        {
            cell.Index(3).ColSpan(3).Add();
            cell.ColSpan(4).Index(6).Add();
            cell.Index(13).ColSpan(3).Add();
            cell.Index(17).ColSpan(2).Add();
        }).Add();
        row.Height(35).Cells(cell =>
        {
            cell.Index(2).ColSpan(3).Add();
            cell.ColSpan(2).Index(5).Add();
            cell.Index(7).ColSpan(3).Add();
            cell.Index(15).ColSpan(2).Add();
            cell.Index(17).ColSpan(2).Add();
        }).Add();
       row.Height(35).Cells(cell =>
        {
            cell.Index(2).ColSpan(3).Add();
            cell.ColSpan(4).Index(6).Add();
            cell.Index(16).ColSpan(2).Add();
        }).Add();
         row.Height(35).Cells(cell =>
        {
            cell.Index(2).ColSpan(4).Add();
            cell.ColSpan(3).Index(7).Add();
            cell.Index(15).ColSpan(2).Add();
            cell.Index(17).ColSpan(2).Add();
        }).Add();
    }).Columns(column =>
    {
        column.Width(90).Add();
        column.Width(150).Add();
        column.Width(100).Add();
        column.Width(100).Add();
        column.Width(100).Add();
        column.Width(100).Add();
        column.Width(100).Add();
        column.Width(100).Add();
        column.Width(100).Add();
        column.Width(120).Add();
        column.Width(120).Add();
        column.Width(120).Add();
        column.Width(120).Add();
        column.Width(120).Add();
        column.Width(120).Add();
        column.Width(120).Add();
        column.Width(100).Add();
        column.Width(100).Add();
        column.Width(100).Add();
        column.Width(100).Add();
    }).Add();
}).Render()

<script>

    function created() {
       this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:S1');
        this.numberFormat('h:mm AM/PM', 'C1:S1');
        this.cellFormat({ verticalAlign: 'middle' }, 'A1:S11');
        // Merging the `K4:M4` cells using method
        this.merge('K4:M4');
        // Merging the 5th and 6th row cells across 11th, 12th and 13th column
        this.merge('K5:M6', 'Vertically');
        // Merging the 18th and 19th column cells across 2nd, 3rd and 4th row
        this.merge('N4:O6', 'Horizontally');
    }

</script>
public ActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { EmployeeID= "10001",  EmployeeName= "Davolio",  NineAM= "Analysis Task",  NinethirtyAM= "Analysis Task",  TenAM= "Team Meeting",  TenthirtyAM= "Testing", ElevenAM= "Development",  EleventhirtyAM= "Development",  TwelvePM= "Development",  TwelvethirtyPM= "Support", OnePM= "Lunch Break",  OnethirtyPM= "Lunch Break",  TwoPM= "Lunch Break",  TwothirtyPM= "Testing", ThreePM= "Testing",  ThreethirtyPM= "Development",  FourPM= "Conference",  FourthirtyPM= "Team Meeting", FivePM= "Team Meeting" },
        new { EmployeeID= "10002",  EmployeeName= "Buchanan",  NineAM= "Task Assign",  NinethirtyAM= "Support",  TenAM= "Support",  TenthirtyAM= "Support", ElevenAM= "Testing",  EleventhirtyAM= "Testing",  TwelvePM= "Testing",  TwelvethirtyPM= "Testing", OnePM= "Lunch Break",  OnethirtyPM= "Lunch Break",  TwoPM= "Lunch Break",  TwothirtyPM= "Development", ThreePM= "Development",  ThreethirtyPM= "Check Mail",  FourPM= "Check Mail",  FourthirtyPM= "Team Meeting", FivePM= "Team Meeting" },
        new { EmployeeID= "10003",  EmployeeName= "Fuller",  NineAM= "Check Mail",  NinethirtyAM= "Check Mail",  TenAM= "Check Mail",  TenthirtyAM= "Analysis Tasks", ElevenAM= "Analysis Tasks",  EleventhirtyAM= "Support",  TwelvePM= "Support",  TwelvethirtyPM= "Support", OnePM= "Lunch Break",  OnethirtyPM= "Lunch Break",  TwoPM= "Lunch Break",  TwothirtyPM= "Development", ThreePM= "Development",  ThreethirtyPM= "Team Meeting",  FourPM= "Team Meeting",  FourthirtyPM= "Development", FivePM= "Development" },
        new { EmployeeID= "10004",  EmployeeName= "Leverling",  NineAM= "Testing",  NinethirtyAM= "Check Mail",  TenAM= "Check Mail",  TenthirtyAM= "Support", ElevenAM= "Testing",  EleventhirtyAM= "Testing",  TwelvePM= "Testing",  TwelvethirtyPM= "Testing", OnePM= "Lunch Break",  OnethirtyPM= "Lunch Break",  TwoPM= "Lunch Break",  TwothirtyPM= "Development", ThreePM= "Development",  ThreethirtyPM= "Check Mail",  FourPM= "Conference",  FourthirtyPM= "Conference", FivePM= "Team Meeting" },
        new { EmployeeID= "10005",  EmployeeName= "Peacock",  NineAM= "Task Assign",  NinethirtyAM= "Task Assign",  TenAM= "Task Assign",  TenthirtyAM= "Task Assign", ElevenAM= "Check Mail",  EleventhirtyAM= "Support",  TwelvePM= "Support",  TwelvethirtyPM= "Support", OnePM= "Lunch Break",  OnethirtyPM= "Lunch Break",  TwoPM= "Lunch Break",  TwothirtyPM= "Development", ThreePM= "Development",  ThreethirtyPM= "Team Meeting",  FourPM= "Team Meeting",  FourthirtyPM= "Testing", FivePM= "Testing" },
    };
    ViewBag.DefaultData = data;
    return View();
}

Limitations of Merge

The following features have some limitations in Merge:

  • Merge with filter.
  • Merge with wrap text.

Data Validation

The Data Validation restricts the type of data or values that users can enter into cells. Use the allowDataValidation property to enable or disable data validation in the Spreadsheet.

NOTE

  • The default value of the allowDataValidation property is true.

The Spreadsheet supports predefined validation types, such as numbers, dates, text lengths, lists, and custom formulas.

Apply Validation

Data validation can restrict the type of data or the range of values that users enter into a cell.

You can apply data validation in the following ways:

  • Select the required cells, open the Data tab in the Ribbon, and choose Data Validation.
  • Use the addDataValidation() method to apply a validation rule programmatically.

The addDataValidation() method requires:

  • A validation rule that defines the validation type, operator, and permitted values or formula.
  • A cell or range address to which the rule is applied.

Clear Validation

The Clear Validation feature is used to remove data validations from the specified ranges or the entire worksheet.

You can clear data validation in the following ways:

  • Select the required cells, open the Data tab in the Ribbon, and choose Clear Validation.
  • Use the removeDataValidation() method to remove validation programmatically.

Highlight Invalid Data

The Highlight Invalid Data feature is used to highlight previously entered invalid values.

You can highlight invalid data in the following ways:

  • Open the Data tab in the Ribbon and choose Highlight Invalid Data.
  • Use the addInvalidHighlight() method to highlight invalid values programmatically.

Clear Highlighted Invalid Data

This feature removes the highlighting applied to invalid cell values without removing the associated validation rules.

You can clear invalid-data highlighting in the following ways:

  • Open the Data tab in the ribbon and choose Clear Highlight.
  • Use the removeInvalidHighlight() method programmatically.
@Html.EJS().Spreadsheet("spreadsheet").ShowFormulaBar(false).Created("created").Sheets(sheet =>
{
    sheet.Name("PriceDetails").Rows(row =>
    {
        row.Cells(cell =>
        {
            cell.Value("Seller Name").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Customer Id").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Customer Name").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Product Name").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Product Price").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Sales Date").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Billing Time").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Total Price").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Value("John").Add();
            cell.Value("1").Validation(new SpreadsheetValidation() { Type = ValidationType.WholeNumber, Operator = ValidationOperator.NotEqualTo, Value1 = "1" } ).Add();
            cell.Value("Nash").Add();
            cell.Value("Digger").Validation(new SpreadsheetValidation() { Type = ValidationType.List, Value1 = "Digger, Digger, Cherrypicker" } ).Add();
            cell.Value("50000").Validation(new SpreadsheetValidation() { Type = ValidationType.List, Value1 = "50000,50000,45000" } ).Add();
            cell.Value("04/11/2019").Add();
            cell.Value("11:34:32 AM").Add();
            cell.Value("1,45,000.00").Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Value("Mike").Add();
            cell.Value("2").Validation(new SpreadsheetValidation() { Type = ValidationType.WholeNumber, Operator = ValidationOperator.NotEqualTo, Value1 = "1" } ).Add();
            cell.Value("Jim").Add();
            cell.Value("Cherrypicker").Validation(new SpreadsheetValidation() { Type = ValidationType.List, Value1 = "Cherrypicker, JCB, Wheelbarrow" } ).Add();
            cell.Value("45000").Validation(new SpreadsheetValidation() { Type = ValidationType.List, Value1 = "45000,90000,40" } ).Add();
            cell.Value("04/11/2019").Add();
            cell.Value("11:34:32 AM").Add();
            cell.Value("1,45,000.00").Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Value("shane").Add();
            cell.Value("3").Validation(new SpreadsheetValidation() { Type = ValidationType.WholeNumber, Operator = ValidationOperator.NotEqualTo, Value1 = "1" } ).Add();
            cell.Value("Sean").Add();
            cell.Value("Kango").Validation(new SpreadsheetValidation() { Type = ValidationType.List, Value1 = "Kango, Ropes" } ).Add();
            cell.Value("450").Validation(new SpreadsheetValidation() { Type = ValidationType.List, Value1 = "450, 95" } ).Add();
            cell.Value("06/25/2019").Add();
            cell.Value("01:30:11 PM").Add();
            cell.Value("545.00").Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Value("John").Add();
            cell.Value("1").Validation(new SpreadsheetValidation() { Type = ValidationType.WholeNumber, Operator = ValidationOperator.NotEqualTo, Value1 = "1" } ).Add();
            cell.Value("Nash").Add();
            cell.Value("JCB").Validation(new SpreadsheetValidation() { Type = ValidationType.List, Value1 = "JCB, Ropes, scaffolding" } ).Add();
            cell.Value("90000").Validation(new SpreadsheetValidation() { Type = ValidationType.List, Value1 = "90000, 95, 10000" } ).Add();
            cell.Value("09/22/2019").Add();
            cell.Value("12:30:02 PM").Add();
            cell.Value("1,00,095.00").Add();
        }).Add();
    }).Columns(column =>
    {
        column.Width(88).Add();
        column.Width(88).Add();
        column.Width(106).Add();
        column.Width(98).Add();
        column.Width(88).Add();
        column.Width(86).Add();
        column.Width(107).Add();
        column.Width(81).Add();
    }).Add();
}).Render()


<script>
    function created() {
        //Add Data Validation to range.
        this.addDataValidation({ type: 'TextLength', Operator: 'LessThanOrEqualTo', Value1: '4' }, 'A2:A5');
        this.addDataValidation({ type: 'WholeNumber', Operator: 'NotEqualTo', Value1: '1' }, 'B2:B5');
        this.addDataValidation({ type: 'Date', Operator: 'NotEqualTo', Value1: '04/11/2019' }, 'F2:F5');
        this.addDataValidation({ type: 'Time', Operator: 'Between', Value1: '10:00:00 AM', value2: '11:00:00 AM' }, 'G2:G5');
        this.addDataValidation({ type: 'Decimal', Operator: 'LessThan', Value1: '100000.00' }, 'H2:H5');
        //Highlight Invalid Data.
        this.addInvalidHighlight('A1:H5');
    }
</script>
public ActionResult Index()
{
    return View();
}

Custom Data validation

The Spreadsheet supports custom data validation, allowing users to define their own validation rules for specific cells or ranges. This feature enables you to set conditions that the entered data must meet, making it particularly useful when predefined validation options, such as numbers, dates, or lists, are insufficient.

With custom validation, you can enforce rules using logical expressions or formulas, ensuring that only valid data is entered into the Spreadsheet.

For example, consider a scenario where you want to ensure that a cell contains a number between 10 and 100. To achieve this, define a validation rule using a formula that checks if the entered value is greater than 10 and less than 100. The formula for this validation is =AND(A1>10, A1<100), where A1 refers to the cell being validated.

When this rule is applied, the Spreadsheet evaluates the entered value against the formula. If a user enters a value outside the specified range, an alert notifies them of the invalid input. This helps users correct errors efficiently and ensures that only desired values are accepted.

You can apply custom data validation using two methods.

  • The first is through the Data Validation dialog in the Ribbon toolbar. Navigate to the Data tab, select the Data Validation option, and choose the Custom type from the Allow dropdown menu.
  • The second method is programmatically, using the addDataValidation() method, which allows developers to set custom rules dynamically via code.

The following code example demonstrates how to add custom data validation with a formula in a Spreadsheet.

@Html.EJS().Spreadsheet("spreadsheet").ShowFormulaBar(false).Created("created").Sheets(sheet =>
{
    sheet.Name("PriceDetails").Rows(row =>
    {
        row.Cells(cell =>
        {
            cell.Value("Seller Name").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Customer Id").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Customer Name").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Product Name").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Product Price").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
            cell.Value("Total Price").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Value("John").Add();
            cell.Value("101").Add();
            cell.Value("Nash").Add();
            cell.Value("Digger").Add();
            cell.Value("50000").Add();
            cell.Value("1,45,000.00").Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Value("Mike").Add();
            cell.Value("25").Add();
            cell.Value("Jim").Add();
            cell.Value("Cherrypicker").Add();
            cell.Value("45000").Add();
            cell.Value("1,45,000.00").Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Value("shane").Add();
            cell.Value("35").Add();
            cell.Value("Sean").Add();
            cell.Value("Kango").Add();
            cell.Value("35000").Add();
            cell.Value("1,54,500.00").Add();
        }).Add();
        row.Cells(cell =>
        {
            cell.Value("John").Add();
            cell.Value("101").Add();
            cell.Value("Nash").Add();
            cell.Value("JCB").Add();
            cell.Value("90000").Add();
            cell.Value("1,00,095.00").Add();
        }).Add();
    }).Columns(column =>
    {
        column.Width(88).Add();
        column.Width(88).Add();
        column.Width(106).Add();
        column.Width(98).Add();
        column.Width(88).Add();
        column.Width(81).Add();
    }).Add();
}).Render()


<script>
    function created() {
        var spreadsheet = document.getElementById("spreadsheet").ej2_instances[0];
        //Add Data Validation to range.
        spreadsheet.addDataValidation({ type: 'Custom', value1: '=AND(B2>10, B2<100)' }, 'E2:E5');
        //Highlight Invalid Data.
        spreadsheet.addInvalidHighlight('E2:E5');
    }
</script>
public ActionResult Index()
{
    return View();
}

Limitations of Data validation

The following features have some limitations in Data Validation:

  • Entire row data validation.
  • Insert row between the data validation.
  • Copy/paste with data validation.
  • Delete cells between data validation applied range.

Auto Fill

Auto Fill is used to fill the cells with data based on adjacent cells. It also follows a pattern from adjacent cells if available. There is no need to enter the repeated data manually. You can use allowAutoFill property to enable/disable the auto fill support. You can also use showFillOptions property to enable/disable the fill option and fillType property to change the default auto fill option which is available in autoFillSettings.

The autoFillSettings property configures auto fill behavior:

  • showFillOptions: Specifies whether the auto fill Options menu is displayed after an auto fill operation.
  • fillType: Specifies the default auto fill operation.
  • direction: Specifies the direction in which values are filled.

You can perform auto fill in the following ways:

  • Drag the fill handle to the required range and select an option from the Auto Fill Options menu.
  • Use the autoFill() method programmatically.

The autoFill() method accepts the following parameters:

Parameter Type Description
dataRange string Specifies the data range.
fillRange string Specifies the fill range.
direction AutoFillDirection Specifies the direction(“Up”,”Right”,”Down”,”Left”)to be filled.
fillType AutoFillType Specifies the fill type(“CopyCells”,”FillSeries”,”FillFormattingOnly”,”FillWithoutFormatting”) for autofill action.

In Auto Fill we have following options,

  • Copy Cells
  • Fill Series
  • Fill Formatting Only
  • Fill Without Formatting

NOTE

  • The default auto fill option is FillSeries, which is configured through the fillType property.

Copy Cells

To copy the selected cell content to the adjacent cells. You can do this by one of the following ways,

  • Using fill handle to select the adjacent cell range and “Copy Cells” option in “AutoFillOptions” menu to fill the adjacent cells.
  • Using “CopyCells” as fill type in autoFill method to fill the adjacent cells.

Fill Series

To fill the series of numbers, characters, or dates based on selected cell content to the adjacent cells with their formats.

You can do this by one of the following ways,

  • Using fill handle to select the adjacent cell range and “Fill Series” option in “AutoFillOptions” menu to fill the adjacent cells.
  • Using “FillSeries” as fill type in autoFill method to fill the adjacent cells.

Fill Formatting Only

To fill the cell style and number formatting based on the selected cell content to the adjacent cells without their content.

You can do this by one of the following ways,

  • Using fill handle to select the adjacent cell range and “Fill Formatting Only” option in “AutoFillOptions” menu to fill the adjacent cells.
  • Using “FillFormattingOnly” as fill type in autoFill method to fill the adjacent cells.

Fill Without Formatting

To fill series of numbers, characters, or dates based on the selected cells to the adjacent cells without their formats.

You can do this by one of the following ways,

  • Using fill handle to select the adjacent cell range and “Fill Without Formatting” option in “AutoFillOptions” menu to fill the adjacent cells.
  • Using “FillWithoutFormatting” as fill type in autoFill method to fill the adjacent cells.

In the following sample, you can enable/disable the fill option on the button click event by using the showFillOptions property in autoFillSettings.

@Html.EJS().Button("showfillbtn").Content("Change showFillOptions").Render();
@Html.EJS().Spreadsheet("spreadsheet").Created("created").Sheets(sheet =>
{
     sheet.Name("Price Details").Rows(row =>
	   {
		   row.Height(30).Add();
	   }).Ranges(ranges =>
	   {
		   ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).StartCell("A1").Add();
	   }).Columns(column =>
	   {
		   column.Width(130).Add();
		   column.Width(100).Add();
		   column.Width(100).Add();
	   }).Add();
   }).Render()

<script>

	function created() {
		this.cellFormat({ backgroundColor: '#357cd2', color: '#fff', fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
		this.autoFill('D4:D11', 'D2:D3', 'Down', 'CopyCells');
		this.autoFill('E4:E11', 'E2:E3', 'Down', 'FillSeries');
		this.autoFill('B4:B11', 'B2:B3', 'Down', 'FillFormattingOnly');
		this.autoFill('C4:C11', 'C2:C3', 'Down', 'FillWithoutFormatting');
    }
	document.getElementById("showfillbtn").addEventListener('click', function () {
            var spreadsheetObj = document.getElementById("spreadsheet").ej2_instances[0];
            var showFillOptions = spreadsheetObj.autoFillSettings.showFillOptions;
            spreadsheetObj.autoFillSettings.showFillOptions = !showFillOptions;
        });

</script>
public ActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { ItemName= "Casual Shoes", Date= "02/14/2014", Time= "11:34:32 AM", Quantity= "10", Price= "20", Amount= "200", Discount= "1", Profit= "10" },
        new { ItemName= "Sports Shoes", Date= "06/11/2014", Time= "05:56:32 AM", Quantity= "20", Price= "30", Amount= "600", Discount= "5", Profit= "50" },
        new { ItemName= "Formal Shoes", Date= "07/27/2014", Time= "03:32:44 AM", Quantity= "20", Price= "15", Amount= "300", Discount= "7", Profit= "27" },
        new { ItemName= "Sandals & Floaters", Date= "11/21/2014", Time= "06:23:54 AM", Quantity= "15", Price= "20", Amount= "300", Discount= "11", Profit= "67" },
        new { ItemName= "Flip- Flops & Slippers", Date= "06/23/2014", Time= "12:43:59 AM", Quantity= "30", Price= "10", Amount= "300", Discount= "10", Profit= "70" },
        new { ItemName= "Sneakers", Date= "07/22/2014", Time= "10:55:53 AM", Quantity= "40", Price= "20", Amount= "800", Discount= "13", Profit= "66" },
        new { ItemName= "Running Shoes", Date= "02/04/2014", Time= "03:44:34 AM", Quantity= "20", Price= "10", Amount= "200", Discount= "3", Profit= "14" },
        new { ItemName= "Loafers", Date= "11/30/2014", Time= "03:12:52 AM", Quantity= "31", Price= "10", Amount= "310", Discount= "6", Profit= "29" },
        new { ItemName= "Cricket Shoes", Date= "07/09/2014", Time= "11:32:14 AM", Quantity= "41", Price= "30", Amount= "1210", Discount= "12", Profit= "166" },
        new { ItemName= "T-Shirts", Date= "10/31/2014", Time= "12:01:44 AM", Quantity= "50", Price= "10", Amount= "500", Discount= "9", Profit= "55" }
    };
    ViewBag.DefaultData = data;
    return View();

}

Clear

Clear feature helps you to clear the cell contents (formulas and data), formats (including number formats, conditional formats, and borders) in a spreadsheet. When you apply clear all, both the contents and the formats will be cleared simultaneously.

Apply Clear Feature

You can apply clear feature by using one of the following ways,

  • Select the clear icon in the Ribbon toolbar under the Home Tab.
  • Using the clear() method to clear the values.

Clear has the following types in the spreadsheet,

Options Uses
Clear All Used to clear all contents, formats, and hyperlinks.
Clear Formats Used to clear the formats (including number formats, conditional formats, and borders) in a cell.
Clear Contents Used to clear the contents (formulas and data) in a cell.
Clear Hyperlinks Used to clear the hyperlink in a cell.

Methods

Clear the cell contents and formats in the Spreadsheet document by using the clear method. The clear method has type and range as parameters. The following code example shows how to clear the cell contents and formats in the button click event.

@Html.EJS().DropDownButton("element").Content("Clear").Items((IEnumerable<object>)ViewBag.items).Select("itemSelect").Render()


@Html.EJS().Spreadsheet("spreadsheet").Created("created").Sheets((sheet) =>
{
    sheet.Ranges((ranges) =>
    {
        ranges.DataSource(@ViewBag.defaultData).Add();
    }).Add();
}).Render()

<script>

   function created() {
        this.cellFormat({ fontWeight: 'bold', fontSize: '12pt'}, 'A1:E1');
        this.cellFormat({ color: '#10c469' }, 'B1:B10');
    }

      function itemSelect(args) {
        var spreadsheet = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
        if (args.item.text === 'Clear All')
      spreadsheet.clear({ type: 'Clear All', range: 'D1:D10' }); // Clear the content, formats and hyperlinks applied in the provided range.
    if (args.item.text === 'Clear Formats')
      spreadsheet.clear({ type: 'Clear Formats', range: 'B1:B10' }); // Clear the formats applied in the provided range
    if (args.item.text === 'Clear Contents')
      spreadsheet.clear({ type: 'Clear Contents', range: 'A1:A10' }); // Clear the content in the provided range
    if (args.item.text === 'Clear Hyperlinks')
      spreadsheet.clear({ type: 'Clear Hyperlinks', range: 'F2:F6' }); // Clear the hyperlinks applied in the provided range
    }

</script>
public ActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { OrderID= "10248",  CustomerID= "VINET",  EmployeeID= "5",  ShipName= "Vins et alcools Chevalier",  ShipCity= "Reims",  Website= "https://www.amazon.com/" },
        new { OrderID= "10249",  CustomerID= "TOMSP",  EmployeeID= "6",  ShipName= "Toms Spezialitäten",  ShipCity= "Münster",  Website= "https://www.overstock.com/" },
        new { OrderID= "10250",  CustomerID= "HANAR",  EmployeeID= "4",  ShipName= "Hanari Carnes",  ShipCity= "Rio de Janeiro",  Website= "https://www.aliexpress.com/" },
        new { OrderID= "10251",  CustomerID= "VICTE",  EmployeeID= "3",  ShipName= "Victuailles en stock",  ShipCity= "Lyon",  Website= "http://www.alibaba.com/" },
        new { OrderID= "10252",  CustomerID= "SUPRD",  EmployeeID= "4",  ShipName= "Suprêmes délices",  ShipCity= "Charleroi",  Website= "https://taobao.com/" },
        
    };
    List<object> items = new List<object>();
    items.Add(new
    {
        text = "Clear All"
    });
    items.Add(new
    {
        text = "Clear Formats"
    });
    items.Add(new
    {
        text = "Clear Contents"
    });
    items.Add(new
    {
        text = "Clear Hyperlinks"
    });
    ViewBag.items = items;
    ViewBag.DefaultData = data;
    return View();
}

See Also