WinForms Spreadsheet Auto-Filtering

In this post we will discuss how to use the auto-filtering feature in MindFusion.Spreadsheet for WinForms.

Introduction

We start off by creating a new Windows Forms Application in Visual Studio, adding a WorkbookView control to the main form and adding the Northwind database (nwind.mdb) as a data source. For simplicity we only add the Orders table. After compiling the application we can add the DataSource and the OrdersTableAdapter as components to the main form.

Loading the data

We traverse the rows in the data source and populate the spreadsheet by assigning the data to the Data property of the respective worksheet cells. The first cell in each column is set to the name of the corresponding database field. Finally, the columns are resized to fit their contents through the ResizeColumnsToFit method of the view. The complete code of the data loading can be found in the LoadData method.

Turning auto-filtering on

Auto-filtering is enabled by calling the AutoFilter method of the CellRange class. If the CellRange represents a range of cells, then auto-filtering is applied to that range. If the CellRange represents a single cell, then auto-filtering is applied to the rectangular area of data cells, which includes this cell. In this application, we enable auto-filtering on the loaded data, by calling AutoFilter on cell A1. In addition, we apply auto-filtering criteria on the 6-th column (ShipVia) by calling the AutoFilter overload.

workbook1.Worksheets[0].CellRanges["A1"].AutoFilter();
workbook1.Worksheets[0].CellRanges["A1"].AutoFilter(
    6, "<>1", AutoFilterOperator.Or, null, true);

The following image shows the running sample:
spreadsheet-autofilter

The source code is available for download from here:
http://mindfusion.eu/_samples/SpreadsheetAutoFilter.zip

MindFusion.Spreadsheet for WinForms can be downloaded from here:
MindFusion.Spreadsheet for WinForms

About MindFusion.Spreadsheet for WinForms: A powerful .net spreadsheet component with great capabilities for editing, styling and formatting large amounts of data.

MindFusion.WinForms Pack, 2015.R1

Licensing
MindFusion no longer supports separate trial and licensed build-s for its components. Instead, you should call MindFusion.Licensing.LicenseManager.AddLicense(yourLicenseKey) from within your application to stop displaying trial labels. The license key is listed at the Keys&Downloads page in your client account.

MindFusion WinForms charting controlCharting

Custom Formatting of Labels
You can set custom format to numeric labels in all chart types. The format uses the .net custom format
strings.

Sorting of Bars
Bars in the charting control can be sorted from within each series/cluster in ascending or descending order. You can also sort the series/clusters – by the sum of their values or the max value. If you set SortColor, the bar brushes and pens will be sorted along the bars as well.

MindFusion WinForms Charting Control: Sorting of bars

Miscellaneous

  • Bars are now outlined with the consecutive pen from the ChartPens collection rather than the AxisPen.
  • Drawing of line and area charts has greatly been improved – the control now draws only the visible portion of the chart rather than the whole chart, which was clipped to the visible rectangle.
  • AreaOpacity property added to radar charts.
  • AxesOnTop property in radar charts sets the order of drawing for the graphic and the axes.
  • and more.

MindFusion WinForms diagramming control Diagramming

Map nodes
The diagramming control has a new MapNode class that represents nodes that can render geographical maps.
Currently map nodes can display the content of Esri shapefile files.

Tree map layout
Tree maps represent hierarchies by nesting child nodes within their parents, where the areas of leaf nodes are proportional to their Weight values. Unlike other layout algorithms, TreeMapLayout expects hierarchies to be defined via grouping or containment (see AttachTo method and ContainerNode class), and will ignore any links in the diagram.

MindFusion WinForms diagramming control: the tree map layout.

The tree map layout.

Decision flowchart layout
The DecisionLayout arranges simple flowcharts consisting of decision boxes with up to three outgoing links per node and activity boxes with a single outgoing link per node. The nodes are arranged in columns and rows, whose distance depends on the HorizontalPadding and VerticalPadding property values.

More new features:

  • Improved arrowhead rendering
  • Import diagrams from *.SVG files
  • Improvements in the Visio2013Exporter
  • and many more… read a full list here.

MindFusion WinForms spreadsheet controlSpreadsheet

Grouping and Outlining
You can now group rows and columns. To group or ungroup a range of rows or columns, use the Group or Ungroup methods of the RowRange and ColumnRange classes.

Formatted text support
The spreadsheet control can now import, display and export texts with mixed formatting. Formatted texts can also be created programmatically by assigning a FormattedText instance to the Data property of cells.

Improved in-place editing
Trat yourself to the new Excel-like editing features that are now part of the spreadsheet component. When you type a new formula pressing the arrow keys will result in navigation within the worksheet (instead of adjusting the caret position) and the selected cell or cell ranges will be inserted as a reference in the currently edited formula.

New Events
MindFusion WPF Spreadsheet control offers a host of new events, which are now part of the WorkbookView
and WorksheetView classes:

  • Header interaction notification events – ColumnsMoved, ColumnsResized, RowsMoved, and RowsResized.
  • Group interaction events – ColumnGroupCollapsed, ColumnGroupExpanded, ColumnOutlineLevelToggled, RowGroupCollapsed, RowGroupExpanded, and RowOutlineLevelToggled.
  • Custom drawing events – DrawColumn and DrawRow.

New Functions
The core calculation engine of the spreadsheet control now offers 22 new functions, including a full set of database functions, such as DGET, DSUM, and so on. The Function Reference section in the online help offers further details.

MindFusion WinForms spreadsheet control: database functions

Miscellaneous

  • Worksheets can be moved and copied to another workbook through the new Move and Copy methods.
  • The new EnableFormulaEvaluation property can be used to disable formula evaluation across the workbook.
  • New ShowActiveCell and ShowSelection properties added to the WorkbookView and WorksheetView classes.

The trial version is available for direct download from this link:

Download MindFusion.WinForms Pack 2015.R1

If you run into problems with any of the components, please let us know. We shall be glad to assist you. MindFusion is proud with its excellent technical support – the majority of the questions are answered within hours of receiving them.

About MindFusion.WinForms Pack: A set of five WinForms programming components that provide your application with a rich choice of diagramming, charting, scheduling, mapping, reporting and gauge features. The tools are very easy to implement and use. They boast intuitive API and various step-by-step tutorials to get you started. Both online and offline documentation is available.

A sample browser presents you with all the samples for each control to let you easily navigate to what you need. You can check some of the features of each component right now if you look at the online demos:

Visit the features – page of the components for more information about their capabilities:

You can check the prices and licensing scheme here. All components are royalty-free.

MindFusion.Spreadsheet for WinForms, V1.3

MindFusion has released a new version of its Spreadsheet for WinForms programming component. Here is an overview of the new features:

Grouping and Outlining
Rows and columns can now be grouped. You can group or ungroup a range of rows or columns with the Group or Ungroup methods of the RowRange and ColumnRange classes. You can set the outline level of a row/column or a range of rows/columns directly with the OutlineLevel property. Groups can be expanded and collapsed interactively, by clicking on the expand/collapse button in the view, or programmatically, by settings the IsCollapsed property of the respective row or column.

Grouping rows and columns in the spreadsheet.

Grouping rows and columns in the spreadsheet.

New Events

  • Several new events have been added to the WorkbookView and WorksheetView classes:
  • Header interaction notification events – ColumnsMoved, ColumnsResized, RowsMoved, and RowsResized.
  • Group interaction events – ColumnGroupCollapsed, ColumnGroupExpanded, ColumnOutlineLevelToggled, RowGroupCollapsed, RowGroupExpanded, and RowOutlineLevelToggled.
  • Custom drawing events – DrawColumn and DrawRow.

Miscellaneous
You can now move and copy spreadsheets to another workbook through the new Move and Copy methods.
Use the new EnableFormulaEvaluation property to disable formula evaluation across the workbook.
New ShowActiveCell and ShowSelection properties are added to the WorkbookView and WorksheetView classes.

You can download the trial version from the link below:

Download MindFusion.Spreadsheet for WinForms V1.3, Trial Version

If you require technical support, you can post a message at the forum, send us an e-mail at support@mindfusion.eu. or use the help desk. MindFusion takes special effort in providing fast and detailed answers to all inquiries that we receive.

About MindFusion.Spreadsheet for WinForms: An easy-to-use programming component suitable for building all types of spreadsheets fast and easy. The tool supports formulas, tool-tips, cell annotations, cell spanning, scrolling and many more. You can add charts and images as well use the flexible style system to design the perfect spreadsheet. The component supports full undo and redo as well copy and paste from Windows clipboard.
You can import spreadsheet data from CSV, XLSX or ODS files and export the final spreadsheet in a number of formats – as images, PDF or CSV, XLSX or ODS files. Various auxiliary forms help you quickly adjust the data and appearance of your spreadsheet. Read more about the features of the component here or check the license prices at the buy page.

Spreadsheet for WinForms is part of MindFusion Pack for WinForms, which offers other useful components that are of great use when you build any type of WinForms application – from a diagramming library to map control to gauges: check them here.

Spreadsheet for WinForms, V1.2

MindFusion has released a new version of Spreadsheet for WinForms with the following new features:

Formatted text support
You can import, display and export texts with mixed formatting. You can create formatted texts programmatically by assigning a FormattedText instance to the Data property of cells. Currently the control supports: font name and size, bold, italic, underlined, strikeout, and text color.

New Functions
At your disposal are 22 new functions, which have been added to the core calculation engine, including a full set of database functions, such as DGET, DSUM, and so on. Check Function Reference for additional information.

Improved in-place editing
Now when you type a new formula, pressing the arrow keys will result in navigation within the worksheet (instead of adjusting the caret position) and the selected cell or cell ranges will be inserted as a reference in the currently edited formula.

Sport events schedule

Sport events schedule

You can download the trial version from the link below:

Download MindFusion.Spreadsheet for WinForms 1.2, Trial Version

If you require technical support, you can post a message at the forum, send us an e-mail at support@mindfusion.eu. or use the help desk. MindFusion takes special effort in providing fast and detailed answers to all inquiries that we receive.

About MindFusion.Spreadsheet for WinForms: An easy-to-use programming component suitable for building all types of spreadsheets fast and easy. The tool supports formulas, tool-tips, cell annotations, cell spanning, scrolling and many more. You can add charts and images as well use the flexible style system to design the perfect spreadsheet. The component supports full undo and redo as well copy and paste from Windows clipboard.
You can import spreadsheet data from CSV, XLSX or ODS files and export the final spreadsheet in a number of formats – as images, PDF or CSV, XLSX or ODS files. Various auxiliary forms help you quickly adjust the data and appearance of your spreadsheet. Read more about the features of the component here or check the license prices at the buy page.

Spreadsheet for WinForms is part of MindFusion Pack for WinForms, which offers other useful components that are of great use when you build any type of WinForms application – from a diagramming library to map control to gauges: check them here.

MindFusion.Spreadsheet: Formatted Text

Starting with version 1.2, MindFusion.Spreadsheet adds support for formatted text. The control can import, create, display and export formatted texts. In this blog we will demonstrate how to create formatted text in a cell.

Introduction

As with all previous blogs we start off by creating a new Windows Forms Application in Visual Studio and adding a WorkbookView control to the main form. The WorkbookView displays a Workbook with a single worksheet.

Creating the formatted text

Formatted texts are created programmatically by instantiating from the FormattedText class. The class provides a constructor, which can initialize the formatted text from a markup string, similar to HTML. The string supports the following tags: <fontname=”fontname“>, <fontsize=”fontsize“>, <i>, <b>, <u>, <s>, and <color=”#rrggbb“>. The following code creates several formatted texts:

worksheet.Cells["A1"].Data = new FormattedText("The meeting is important!");
worksheet.Cells["A2"].Data = new FormattedText("A title (1994)");
worksheet.Cells["A3"].Data = new FormattedText("This text contains some keywords that are highlighted.");

Exporting

The formatted texts are exported to all supported formats, including HTML, PDF, XLSX, ODS, image, and so on. The following image illustrated the formatted texts created earlier exported to HTML:

spreadsheet-formattedtext

The source code of the sample is available for download from here:

http://mindfusion.eu/_samples/SpreadsheetFormattedTexts.zip

The trial version of MindFusion.Spreadsheet for WinForms can be downloaded from here:

Download MindFusion.Spreadsheet for WinForms Trial Version

About MindFusion.Spreadsheet for WinForms: A powerful .NET spreadsheet component with great capabilities for editing, styling and formatting large amounts of data.