Category Archives: Sample code

WinForms.Spreadsheet: Sorting Data

Posted on by

In this blog we will discuss how to sort existing data in MindFusion.Spreadsheet for WinForms both programmatically and by using the built-in form.

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.

Initializing the data

The data that should be sorted is contained in an CSV (comma-separated value) file, which is loaded at the start of the application. Some minor formatting is applied to the imported data, such as emphasizing the header row. The columns are automatically resized to fit the content through the ResizeColumnsToFit method of the WorkbookView class.

Sorting programmatically

The sorting is performed by calling the Sort method of the Worksheet class and providing a SortOptions object. This object contains information about the sort, such as which row or column to sort by, the sort direction (horizontal or vertical) and so on. In this particular case, we sort the data by columns, therefore we specify TopToBottom for the sort direction:

sortOptions.Direction = SortDirection.TopToBottom;

The columns (or rows) to sort by are specified by SortKey objects, added to the Keys collection of the SortOptions class. The order of the keys in the collection is important. For example, to sort by the first column and then by the fourth column, we define the following keys:

sortOptions.Keys.Add(new SortKey(0));
sortOptions.Keys.Add(new SortKey(3));

The Sort method accepts as argument the range to be sorted. In this sample we would like to prevent the header row from being sorted and specify A2:D15 for the target range.

The following image shows the running sample:

spreadsheet-sorting

Sorting through the built-in form

Sorting can also be performed through the built-in SortForm form, contained in the MindFusion.Spreadsheet.WinForms.StandardForms.dll assembly. The image below displays the form with the first and fourth columns selected as sort keys:

spreadsheet-sortform

The source code is available for download from here:

https://mindfusion.eu/_samples/SpreadsheetSorting.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.

WinForms.Spreadsheet: Apply Conditional Formatting

Posted on by

In this blog we will discuss how to apply conditional formatting to a range of cells in MindFusion.Spreadsheet for WinForms. Conditional formatting is a feature that allows you to apply different formatting options, such as background color, borders, or font to cells that meet certain criteria and have this formatting automatically change depending on the value or formula of the cell.

Introduction

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.

Initializing the data

The goal of the application is to display an array of temperature values, colored differently based on a standard temperature scale. The data of the application is hard-coded in several arrays, which are used to initialize the worksheet:

var names = new CultureInfo("en-US").DateTimeFormat.AbbreviatedMonthNames;
var avgHigh = new int[] { 4, 7, 12, 17, 21, 25, 28, 28, 23, 18, 10, 4 };
var avgLow = new int[] { -5, -4, 0, 5, 9, 13, 14, 14, 10, 6, 0, -4 };
var recordHigh = new int[] { 16, 17, 24, 28, 29, 32, 37, 34, 34, 30, 24, 17 };
var recordLow = new int[] { -21, -19, -15, -5, -1, 4, 7, 8, -2, -2, -7, -16 };

Applying the conditional formatting

In MindFusion.Spreadsheet conditional formatting is applied to a range of cells through its style. This is done by calling the Style.ConditionalFormats.Add method to create IConditionalFormat objects and then setting the properties of the newly created object to specify the condition criteria and the style to apply when the criteria are met. In this application we use a simple algorithm to distribute temperature values linearly along a blue-yellow-red color scale. When a cell value falls within a specific temperature interval, the conditional formatting applies a background color to this cell to visually indicate the value.

The following image shows the running sample:

spreadsheet-conditionalformats

Conditional formatting can also be created and modified through the built-in ConditionalFormatForm form, contained in the MindFusion.Spreadsheet.WinForms.StandardForms.dll assembly. The image below displays the form for the temperature cell range.

spreadsheet-conditionalformatform

The source code is available for download from here:

https://mindfusion.eu/_samples/SpreadsheetConditionalFormats.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.

WinForms Spreadsheet Databinding

Posted on by

In this blog we will discuss how to display the information from a database inside MindFusion.Spreadsheet for WinForms, how to validate and edit the data and how to write back any changes.

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 Categories table. After compiling the application we can add the DataSource and the CategoriesTableAdapter 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. To prevent certain columns from being edited (for example auto-increment keys and image fields), we mark these columns by setting their Tag property. All columns and rows beyond those that actually display data are hidden by setting their IsHidden property to true. The column titles are set to the names of the corresponding database columns. 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.

Note, that during the data loading, the loading flag is set to true. This is done to prevent some unnecessary operations in the CellChanging and CellChanged event handlers.

Setting up the view

To resemble a data grid, we need to disable certain functions in the view. More specifically, we need to hide the tabs, the formula bar, the auto-fill handle and the hidden header indicator.

workbookView1.ShowHiddenHeaderIndicators = false;
workbookView1.AllowAutoFill = false;
workbookView1.ShowTabs = false;
workbookView1.ShowFormulaBar = false;

Performing validation

We want to prevent the users from changing the values of certain cells – for example, the cells in the auto-increment column and the cells representing pictures. To do this, we will handle the InplaceEditStarting event of the WorkbookView and the WorksheetCellChanging event of the Workbook. In the event handlers we check the Tag value of the related column. If the column is marked as read-only, we set the Cancel property of the event argument to true, to prevent the edit or change.

Adding new rows

When we populated the data from the dataset, we left an empty row at the bottom of the spreadsheet. The intent is that this empty row should be used to add new rows to the table. When the user edits a cell of the empty row, the row key is automatically calculated and a new empty row is added at the bottom of the spreadsheet. To implement this functionality, we handle the WorksheetCellChanged event of the Workbook. In the event handler, we inspect the row of the edited cell. If this is the last visible row in the spreadsheet, we calculate a key for this row and reveal another row at the bottom of the spreadsheet by setting its IsHidden property to false.

Saving changes to the database

Changes are immediately reflected back to the database from within the WorksheetCellChanged event handler. New rows are added via the Rows.Add method of the Categories data table. Existing rows are updated directly through the respective DataRow object. The changes are effectively performed by calling the Update method of the table adapter.

The following image shows the running sample:

spreadsheet-databinding

The source code is available for download from here:

https://mindfusion.eu/_samples/SpreadsheetDatabase.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.

Scatter Line Chart in WinForms with a Custom Legend

Posted on by

In this post we show you how to build a multi-series scatter chart with a legend and a separator. We use MindFusion.Charting tool for WinForms.

The Data

The properties that specify data in the control are XData and YData. We add three series to each one for the three series that we want to show. Before that, we clear the arrays, to make sure no previously added data shows on the chart:

 lineChart1.XData.Clear();
 lineChart1.XData.Add(new List{3,4,5,6,7,8,9});
 lineChart1.XData.Add(new List{1,2,3,4,5,6,7,8});
 lineChart1.XData.Add(new List{1,2,3,4,5,6,7,8,9,10});

 lineChart1.YData.Clear();
 lineChart1.YData.Add(new List{92, 112, 241, 195, 201, 188, 212});
 lineChart1.YData.Add(new List{512, 480, 321, 491, 460, 320, 298, 241});
 lineChart1.YData.Add(new List { 340, 302, 322, 401, 487, 503, 421, 460, 513, 490 });

Chart Series

We want to show line series with scatters – since this is the default LineType, we don’t have to set anything. In order to customize our series, we add new pens to the ChartPens property. The colors of the scatters are customized with ShapePens and ShapeBrushes. We make the chart pens a bit thicker – 3 pixels.

 lineChart1.ShapeBrushes.Add(new MindFusion.Drawing.SolidBrush(Color.FromArgb(175, 251, 175)));
 lineChart1.ShapeBrushes.Add(new MindFusion.Drawing.SolidBrush(Color.FromArgb(176, 224, 230)));
 lineChart1.ShapeBrushes.Add(new MindFusion.Drawing.SolidBrush(Color.FromArgb(216, 191, 216)));

 lineChart1.ChartPens.Add(new MindFusion.Drawing.Pen(Color.FromArgb(144,238,144), 3.0f));
 lineChart1.ChartPens.Add(new MindFusion.Drawing.Pen(Color.FromArgb(70, 130, 180), 3.0f));
 lineChart1.ChartPens.Add(new MindFusion.Drawing.Pen(Color.FromArgb(186, 85, 211), 3.0f));

 lineChart1.ShapePens.Add(new MindFusion.Drawing.Pen(Color.FromArgb(144, 238, 144)));
 lineChart1.ShapePens.Add(new MindFusion.Drawing.Pen(Color.FromArgb(70, 130, 180)));
 lineChart1.ShapePens.Add(new MindFusion.Drawing.Pen(Color.FromArgb(186, 85, 211)));

Separator Line

We would like to show a separator line that indicates the average value from all chart data. We add a SummaryValue to the SummaryValues collection. Then we customize the summary line by specifying its pen, scatter type and scatter size. We also set the pen and the brush for the scatters. Here is how we do it:

  lineChart1.SummaryPens.Add(new MindFusion.Drawing.Pen(Color.FromArgb(255,69,0), 4.0f);
  lineChart1.SummaryShapeBrushes.Add(new MindFusion.Drawing.SolidBrush(Color.FromArgb(255,228,225))); 
  lineChart1.SummaryShapePens.Add(new MindFusion.Drawing.Pen(Color.FromArgb(255,69,0));
  lineChart1.SummaryShapes.Add(MindFusion.Charting.Shape.Rhombus);
  lineChart1.SummaryShapeSizes.Add(15.0);
  lineChart1.SummaryValues.Add(MindFusion.Charting.Summary.Average);

Axes Labels

We want to show custom text at the X-axis, so we set XAxisSettings.LabelType to AxisLabelType.CustomText. We use the XLabels property to add the labels. We also add a title with the XAxisSettings.TitleLabel property.

For the Y-axis we want to show the auto scale – we set it with YAxisSettings.MaxValue, YAxisSettings.MinValue and YAxisSettings.AxisDelta. We show ticks on both axes with the MajorTickLength property.

 lineChart1.YAxisSettings.AxisDelta = 50;
 lineChart1.YAxisSettings.MajorTickLength = 2F;
 lineChart1.YAxisSettings.MaxValue = 600;
 lineChart1.YAxisSettings.MinValue = 0;

 lineChart1.XAxisSettings.AxisDelta = 1;
 lineChart1.XAxisSettings.LabelType = MindFusion.Charting.AxisLabelType.CustomText;
 lineChart1.XAxisSettings.MajorTickLength = 5F;
 lineChart1.XAxisSettings.MaxValue = 11;
 lineChart1.XAxisSettings.MinValue = 0;
 lineChart1.XAxisSettings.TitleLabel = "Year";

The Legend

The labels for the legend are set with the LegendLabels property. The colors are picked automatically from the ChartPens property for each series. We place the legend at the bottom with LegendPosition and increase its offset with LegendOffset. We want the legend in one row, so we set LegendColumns to the count of the labels – 3.

 lineChart1.LegendColumns = 3;
 lineChart1.LegendLabels = new List{"Europe, Asia, North America"};
 lineChart1.LegendOffset = 30f;
 lineChart1.LegendPosition = MindFusion.Charting.Position.Bottom;

Here is a screenshot from the final chart:

Scatter Chart with a Custom Legend

Scatter Chart with a Custom Legend

You can download the sample from this link:

Download Scatter Chart with a Custom Legend Sample

The trial version of MindFusion.Chart for WinForms boasts many different samples, great charting tips and step by step tutorials. You can download it directly from here:

Download MindFusion.Charting for WinForms 3.5 Trial Version

About MindFusion.Charting for WinForms: a professional programming component for WinForms, which lets you create remarkable charts fast and easy. The tool supports all major chart types – line, pie, radar and bar – and numerous variations of them – column, area, bubble, polar, doughnut etc. 3D charts are supported as well.

Charting for WinForms supports a rich user interaction model with features like zoom, hit testing, drill down, mouse dragging and more. You can use delegates to present mathematical functions, undefined values are also acceptable. Values can be data arrays or retrieved through a database.

The appearance of each chart is fully customizable. The control offers strong design-time support with custom collection editors and chart wizards. At your disposal is a set of predefined appearance themes and a theme editor tool. A full list of the features can be read here.

Formula shapes with adjustable parameters in Flowchart.NET

Posted on by

In this post we’ll show how to create custom formula shapes with adjustable control points. Shape control points are a new feature in version 6.1 of the Flowchart.NET control, which is currently in beta tests. You can download a copy of the beta version from the following link.

https://mindfusion.eu/_beta/DiagWinForms61.zip

A shape formula is defined using a script, which calls one of the following functions to draw the node shape.

MoveTo (x,y) Moves the current position to the specified point without drawing.
LineTo (x,y) Draws a line from the current position to the specified point.
BezierTo (x1,y1,x2,y2,x3,y3) Draws a Bezier curve from the current position to (x3,y3) using (x1,y1) and (x2,y2) as control points.
ArcTo (x,y,largeArc,clockwiseArc,rx,ry) Draws an arc from the specified point to (x,y) where rx and ry are the ellipse radiuses and the arc flags are boolean values specifying which of the four possible arcs to draw.

For example, the following formula defines a rounded rectangle shape using lines and arcs, and expects to receive a “radius” control point parameter that will control the corner radii:

// a rounded rectangle shape, with an arc at each corner
string roundRect = @"
	r = Min(Width / 2, radius.X);
	MoveTo(r, 0);
	LineTo(Width - r, 0);
	ArcTo(Width, r, false, false, r, r);
	LineTo(Width, Height - r);
	ArcTo(Width - r, Height, false, false, r, r);
	LineTo(r, Height);
	ArcTo(0, Height - r, false, false, r, r);
	LineTo(0, r);
	ArcTo(r, 0, false, false, r, r);
 ";

When creating a Shape instance, we must add a ShapeControlPoint object to it that defines the radius parameter and its constraints. The following code specifies that the default radius is 5, the minimum and maximum values allowed are 1 and 15 respectively, and prevents the control point from moving vertically by setting minY and maxY to 0.

var myRect = new Shape(roundRect, "MyRect");

// add a control point for the 'radius' parameter
myRect.ControlPoints.Add(new ShapeControlPoint(
	"radius", 5, 1, 15, UnitType.Fixed, 0, 0, 0, UnitType.Fixed));

Here is another example that defines an anchor-like shape with two parameters controlling the tips of the anchor arms.

// an anchor shape, with two arcs outlining each anchor arm
string anchor = @"
	r = Width / 3;
	y1 = p1.Y * Height / 100;
	y2 = p2.Y * Height / 100;
	MoveTo(Width / 2, Height);
	MoveTo(Width / 2 + 3, Height - 5);
	ArcTo(Width, y2, false, true, r, r);
	ArcTo(Width / 2 + 3, Height - 10, false, false, r, r);
	LineTo(Width / 2 + 3, 0);
	LineTo(Width / 2 - 3, 0);
	LineTo(Width / 2 - 3, Height - 10);
	ArcTo(0, y1, false, false, r, r);
	ArcTo(Width / 2 - 3, Height - 5, false, true, r, r);
	LineTo(Width / 2, Height);
 ";

var myAnchor = new Shape(anchor, "MyAnchor");

// add control points at the tips of anchor arms
myAnchor.ControlPoints.Add(new ShapeControlPoint(
	"p1", 0, 0, 0, UnitType.Percentage, 55, 50, 80, UnitType.Percentage));
myAnchor.ControlPoints.Add(new ShapeControlPoint(
	"p2", 100, 100, 100, UnitType.Percentage, 55, 50, 80, UnitType.Percentage));

The following diagram contains several nodes displaying the shapes above, with some of the control points moved to different positions.

Some additional functions that you can call from shape scripts are listed below.

PI() Returns the value of PI.
Abs(x) Returns the absolute value of x.
Atn(x) Returns the angle, measured in radians, whose tangent is the specified number.
Cos(x) Returns the cosine of the specified angle.
Acos(x) Returns the angle whose cosine is the specified number.
Exp(x) Returns e raised to the specified power.
Log(x) Returns the natural (base e) logarithm of the specified value.
Pow(x,power) Returns a specified number raised to the specified power.
Sin(x) Returns the sine of the specified angle.
Asin(x) Returns the angle whose sine is the specified number.
Sqrt(x) Returns the square root of a number.
Tan(x) Returns the tangent of the specified angle.
Min(x,y) Returns the smaller of two numbers.
Max(x,y) Returns the larger of two numbers.

The complete sample project is available for download here:
https://mindfusion.eu/_samples/ParamShapes.zip

Enjoy!