Ignite UI for Angular Gains New Spreadsheet Control with Ultimate Release 19.1

Brian Lagunas / Monday, April 22, 2019

Along with the many other new controls and features in the newest release of Ultimate 19.1, we’ve added the much-anticipated new Spreadsheet component to Ignite UI for Angular.

The IgxSpreadsheet is a Microsoft Excel inspired component that gives you the ability to embed Excel document creation and editing experiences directly into your Angular applications. The IgxSpreadsheet is built on top of our existing Excel Library, and provides an interactive control to our robust Excel document capabilities. With powerful editing and cell formatting features, the IgxSpreadsheet brings the familiarity of Excel into your customer’s apps.  With just a few lines of code, you can now create, open, edit, and secure Excel documents without ever having Microsoft Excel installed on your machine.

Let's take a moment to talk about some of the great features in the new IgxSpreadsheet Angular component.

Formula Bar

Probably the most iconic feature of Excel is the formula bar. As you may already know, formulas refer to equations that compute calculations based on the values entered in a spreadsheet. A simple formula can be created using calculation operators and constants. A formula must include a cell reference and begin with the "=" character. An example of a simple formula is "=B2+B3". More complex formulas contain functions; for example, "=SUM(B2:B3)". With support for over 300+ formulas, the IgxSpreadhseet's formular bar gives you the ability to view, create, and edit formulas within your worksheet with the same familiar experience you have in Microsoft Excel.  

The name box of the formula bar also allows the user to define named references (to cell or cell ranges) and quickly navigate to the appropriate cell or cell range(s). 

Worksheet Filtering

As you would expect, you can filter on one or more columns of data. With filtering, you can control not only what you want to see, but what you want to exclude. You can filter based on choices you make from a list, or you can create specific filters to focus on precisely the data that you want to see.  You can filter by a numeric value, text values, font colors, icons, or filter by color for cells that have color formatting applied to their background or text.

Not only that, but we provide some built-in filter options based on the underlying data format. For example, if filtering based on a date, you will be provided a number of date-based filter choices:

Worksheet Sorting

Besides having excellent filtering support, we also have sorting support.  The sort menu item will set the sort of the associated column based on the value/state of the active cell for which the menu was shown. So choosing Sort A to Z will create an ascending value sort for that column.

AutoFilter Support

Even with all those tremendous built-in sorting and filtering features accessible from a simple context menu, we also have AutoFilter support.  This means that now the headers of a worksheet table, and the header cells of the worksheet level filtering region, will display a drop-down button that displays the filter-related menu.  The buttons show the sort and filter state and the tooltip displays a friendly description of the filter for that column. The menu displays various options for sorting and filtering. The filter options displayed are dependent on the data types in the column as they are in excel. So you’ll see one of the number filters, text filters, or date filters available depending on the data in the table column.

Conditional Formatting

Conditional formatting allows you to automatically apply formatting—such as colors, icons, and data bars—to one or more cells based on the cell value. To do this, you'll need to create a conditional formatting rule. For example, a conditional formatting rule might be if the value is less than $2000, color the cell red. By applying this rule, you would be able to quickly see which cells contain values less than $2000.  Pretty basic stuff right?  Well, the best part is that the Spreadsheet supports rendering all conditional formatting features available in Microsoft Excel.   Microsoft Excel has several predefined styles—or presets—you can use to quickly apply conditional formatting to your data. They are grouped into three categories:

Data Bars are horizontal bars added to each cell, much like a bar graph

Color Scales change the color of each cell based on its value. Each color scale uses a two- or three-color gradient. For example, in the Green-Yellow-Red color scale, the highest values are green, the average values are yellow, and the lowest values are red.

Icon Sets add a specific icon to each cell based on its value.

Data Validation

The Spreadsheet has support for data validation.  You can easily validate cell values, provide tooltips, and show error messages based on invalid values.

Cell Dropdowns

We have also added the ability to show a dropdown list in a cell populated with data from the values immediately above and below the active cell. One way to show this cell drop-down is via the context menu for a cell using the “Pick From Drop-down List…” item, and the other is by pressing Alt+Down.  You’re not explicitly defining the contents of the list as you do with a list data validation – it’s implicitly populated based on the cells above/below in the same manner as Excel.  As with Excel it ignores numerical values, stops at blanks and table boundaries, etc.

Deselect a Selection

Sometimes when you're selecting multiple cells or ranges in Excel, you accidentally select one or more that you didn't intend to. Using the Deselect Feature, you can deselect any cells within the selected range. Pressing the Ctrl key, you can click, or click-and-drag, to deselect any cells or ranges within a selection. If you need to reselect any of those cells, continue holding the Ctrl key and reselect those cells.

So you get an overlay like this when you hold ctrl and mouse/press down on a selected cell:

And releasing results in:

Let's Wrap this Baby Up!

As you can see, the IgxSpreadsheet is an extremely powerful component that essentilly takes Microsoft Excel and packages it up into a nice little Angular component for you to use inside your Anglar applications. Keep in mind, we only managed to talk about just a handful of features that the IgxSpreadhseet provides.  There are many more that we didn't have time to cover including creating worksheets and tables, shapes, splitting panes, resizing, zooming, hyperlinks, protecting worksheets, freezing panee, fonts styles, etc.  You get the idea. There is a lot to this component and the best way to learn everything it can do is to try it out for yourself.  Now would be a good time to head on over to the Ignite UI for Angular product page, download the product, and start using the IgxSpreadsheet component. Trust me, you'll be glad you did.

As I always like to point out, if you have ideas about new features we should bring to our controls, important issues we need to fix, or even brand new controls you’d like us to introduce, please let us know by posting them on our Product Ideas website.  Follow and engage with us on Twitter via @infragistics. You can also follow and contact me directly on Twitter at @brianlagunas.  Also, make sure to connect with our various teams via our Community Forums where you can interact with Infragistics engineers and other customers.  

Lastly, when you do build something cool with our controls, please make sure to let me know.