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.

Database schema diagram

In this post we’ll show how to use TableNode objects to display tabular data, more specifically database schema information. A Visual Studio sample project containing the code from this post is available for download here:

DatabaseSchema.zip

To start, create a new Windows Forms application, and place a text field for connection string, a button and a DiagramView on the form. In the code-behind file, add following field to map table name to respective TableNode objects:

Dictionary tables = new Dictionary();

Add a RectangleF that stores default size passed to CreateTableNode method:

RectangleF defaultSize = new RectangleF(0, 0, 30, 30);

Create a ReadTables method, which provided an SqlConnection, parses its schema information and creates diagram nodes:

void ReadTables(SqlConnection connection)
{
	// get table schema definitions from connection
	var schema = connection.GetSchema("Tables");
	foreach (DataRow row in schema.Rows)
	{
		// fetch table name
		var name = row["TABLE_NAME"].ToString();

		// create respective node
		var table = diagram.Factory.CreateTableNode(defaultSize);
		table.Caption = name;
		table.Shape = SimpleShape.RoundedRectangle;
		table.Brush = new MindFusion.Drawing.SolidBrush(Color.LightGray);

		// register node in dictionary for future foreign key reference
		tables[name.Replace(" ", "_")] = table;
		ReadFields(table, connection,
			row["TABLE_CATALOG"].ToString(), null, name);
	}

	ReadForeignKeys(connection);
}

The ReadFields method takes table node and name parameters and creates node cells that will show information for the column name and type of database tables:

void ReadFields(TableNode node,
	SqlConnection connection, string db, string owner, string tableName)
{
	// remove default cells
	node.RowCount = 0;

	// reserve one column for name and one for data type
	node.ColumnCount = 2;

	// read column definitions of specified table
	var schema = connection.GetSchema("Columns", new[] { db, owner, tableName });
	foreach (DataRow row in schema.Rows)
	{
		// add a new row to the node
		int r = node.AddRow();

		// set cells' text to the column name and type
		node[0, r].Text = row["COLUMN_NAME"].ToString();
		node[1, r].Text = row["DATA_TYPE"].ToString();

	}

	// make table cells big enough to show all text
	node.ResizeToFitText(false);
}

The ReadForeignKeys method creates DiagramLink connectors between table nodes to show the relationships between database tables:

void ReadForeignKeys(SqlConnection connection)
{
	var schema = connection.GetSchema("ForeignKeys");
	foreach (DataRow row in schema.Rows)
	{
		// read foreign key information
		string fkName = row["CONSTRAINT_NAME"].ToString();
		string tableName = row["TABLE_NAME"].ToString().Replace(" ", "_");
		string prefix = "FK_" + tableName + "_";
		if (fkName.StartsWith(prefix))
		{
			string targetName = fkName.Substring(prefix.Length);

			// get table nodes registered for specified names
			if (tables.ContainsKey(targetName) && tables.ContainsKey(tableName))
			{
				var table = tables[tableName];
				var targetTable = tables[targetName];

				// create a link between the nodes to show relationship
				diagram.Factory.CreateDiagramLink(table, targetTable);
			}
		}
	}
}

Finally handle the button’s click event to open specified connection and call ReadTables. Apply AnnealLayout to arrange the tables so that they do not overlap:

private void btnOpen_Click(object sender, System.EventArgs e)
{
	diagram.ClearAll();

	try
	{
		var connection = new SqlConnection(tbConnection.Text);
		connection.Open();

		// read schema and create corresponding diagram items
		ReadTables(connection);

		connection.Close();
	}
	catch (Exception exception)
	{
		MessageBox.Show(exception.Message);
		diagram.ClearAll();
	}

	// arrange the tables to remove overlaps
	var layout = new AnnealLayout();
	layout.SplitGraph = true;
	layout.Randomize = false;
	layout.MultipleGraphsPlacement = MultipleGraphsPlacement.MinimalArea;
	layout.Margins = new SizeF(10, 10);
	layout.Arrange(diagram);
}

If you run the project and open the Northwind sample database by Microsoft, you should see this diagram:

database schema layout

The code above uses MindFusion’s .NET API and can be used with Windows Forms, WPF, Silverlight and ASP.NET diagramming components. The Java API for Android and desktop Swing application will look similar, with setter method calls instead of property assignments.

You can download the trial version of any MindFusion.Diagramming component from this page.

Enjoy!

WinForms Spreadsheet Databinding

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:

http://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.