Data Validation, Formulas and Conditional Formatting in Spreadsheet for WinForms

In this blog post we will demonstrate how the developer can use validation, formulas and conditional formatting on cells in a spreadsheet. We will build a sample spreadsheet that calculates application score for students. The final mark is the sum of the points in chemistry and biology. The points in these majors can come from Olympics or a competition: the bigger is considered. The points from the Olympics need to be converted to match the scale of the points from the competition.

Here is the final application:


Continue reading

Spreadsheet Localization

In this blog post we will demonstrate how to customize the spreadsheet control so users can input in another language. We build a budget table whose data is in Korean. We will also use the latest localization capabilities of the component to add some auxiliary forms, which UI is also in Korean.

The sample uses WPF but the same methods of the control are available in Spreadsheet for WinForms.

A spreadsheet in Korean

A spreadsheet in Korean

I. General Settings

We create a new WPF project in Visual Studio and name it “BudgetAllocation”. You can drag and drop the WorkbookView control from the Toolbox if you have installed the Spreadsheet for WPF component or the WPF Pack. Another option is to add the required dll-s manually:

  • MindFusion.Spreadsheet.Wpf
  • MindFusion.Licensing
  • MindFusion.Common

In the XAML tag we add a mapping to the Spreadsheet control:

xmlns:ss="http://mindfusion.eu/spreadsheet/wpf"

Then, in the XAML file we create an instance of the MindFusion.Spreadsheet.Wpf.WorkbookView that holds a MindFusion.Spreadsheet.Wpf.Workbook:

<ss:WorkbookView x:Name=”workbookView”>
<ss:Workbook x:Name=”workbook” />
</ss:WorkbookView>

The next step is to add a new Worksheet to the Workbook and make it the active sheet:

 
var activeSheet = workbook.Worksheets.Add();
workbookView.ActiveWorksheet = activeSheet;
activeSheet.BeginInit();
......
activeSheet.EndInit();

With that we have completed the general settings for the project and is time to pay attention to the localization.

II. Localization

Spreadsheet for WPF can be localized to support different language through external XML files and the Locale property of the Workbook class. In our sample we will use Korean. First, let’s tell the control that we want to type in Korean:

workbook.Locale = new CultureInfo("ko-KR"); 

We need to change the default Font for the spreadsheet to one that does support Korean, we’ve chosen Malgun Gothic:

var globalStyle = activeSheet.CellRanges[0, 0, activeSheet.Columns.Count - 1, activeSheet.Rows.Count - 1].Style;
globalStyle.FontName = "Malgun Gothic";

The next step is to localize the UI of all forms that might appear when the user interacts with the spreadsheet. That happens through an XML file that we load and assign to the workbook:

 var file = @"../../Localization/Localization.KR.xml";
       if (file != null && File.Exists(file))
            workbook.SetLocalizationInfo(file);

The component provides ready to be used UI localization files for 6 of the most common languages in the world. In our case we use the file for Korean. You will find those files in a subfolder called Localization on your hard driver, in the installation folder of the control.

Once we’ve finished localizing the spreadsheet it’s time to add some data:

III. Data

Specifying data for each cell is very easy, it is made through the Data property of the Cell instance:

activeSheet.Cells["A2"].Data = "비율로서의 자금 분배";

A cell can contain a formula, and it is specified this way:

activeSheet.Cells["F4"].Data = "=SUM(B4:E4)";

IV. Appearance

The different cells and cell ranges in our spreadsheet have different formats and we set them through the Style property:

activeSheet.Cells["A3"].Style.FontBold = true;
activeSheet.Cells["A3"].Style.Background = new SolidColorBrush(Color.FromRgb(240, 240, 240));
activeSheet.Cells["A3"].Style.BorderBottomBrush = new SolidColorBrush(Color.FromRgb(220, 220, 220));
activeSheet.Cells["A3"].Style.BorderRightBrush = new SolidColorBrush(Color.FromRgb(220, 220, 220));

You can style a range of cells as well:

var heading = activeSheet.CellRanges["A2:G2"];

var headingStyle = heading.Style;
headingStyle.FontBold = true;
headingStyle.VerticalAlignment = MindFusion.Spreadsheet.Wpf.VerticalAlignment.Middle;
....

Cells can be merged:

heading.Merge();

Cells that render numbers can be formatted according to your needs:

var salesStyle = activeSheet.CellRanges[1, 5, 4, 5].Style;
salesStyle.FontBold = true;
salesStyle.Format = "#,##0.00;(#,##0.00)";

For some cells we use conditional formatting – that means we style them differently based on their data value:

// Set a conditional format
var format1 = salesStyle.ConditionalFormats.Add();
format1.Type = ConditionalFormatType.CellValue;
format1.Operator = ComparisonOperator.LessThan;
format1.First = "35";
format1.Style.Background = new SolidColorBrush(Color.FromArgb(255, 255, 120, 85));
format1.Style.Background.Freeze();

In this case we raise alert by painting red those cells whose value is less than 35 in cells that have column index 1 to 4 and row index 5.

V. Context Menu

We add a context menu to the Workbook control in XAML this way:

<ss:WorkbookView.ContextMenu>
<ContextMenu MenuItem.Click=”ContextMenu_Click”>
<MenuItem Header=”Delete Cells Form…” />
<MenuItem Header=”Insert Cells Form…” />
<MenuItem Header=”Worksheet Rename Form…” />
</ContextMenu>
</ss:WorkbookView.ContextMenu>

The ContextMenu has 3 items that allow the user to delete or insert cells as well to rename the worksheet. Let’s look at the event handler of the Click event:

//handle clicks on the context menu
private void ContextMenu_Click(object sender, RoutedEventArgs e)
   {
       var menu = sender as ContextMenu;
       var index = menu.Items.IndexOf(e.OriginalSource);

       switch (index)
       {
           case 0:
               {
                    new DeleteCellsForm(workbook).ShowDialog();
                    break;
                 }
            case 1:
                 {
                     new InsertCellsForm(workbook).ShowDialog();
                     break;
                 }
             case 2:
                 {
                     new WorksheetRenameForm(workbook, "New name").ShowDialog();
                     break;
                 }
          }
    }

We recognize the menu item that was clicked based on its index. After that we show the appropriate form. The InsertCellsForm, DeleteCellsForm and WorksheetRenameForm are all found in the MindFusion.Spreadsheet.Wpf.StandardForms namespace and we have to add a reference to the MindFusion.Spreadsheet.Wpf.StandardForms.dll. Once we do that, since we’ve set the localization info to point to a Korean file, those forms also show in Korean:

Spreadsheet UI in Korean

Spreadsheet UI in Korean

With that our sample is ready. You can download the full source code together with the localization file and the necessary component libraries from here:

Download Spreadsheet Localization Sample in WPF

About Spreadsheet for WPF: A native WPF component that allows developer to add to their applications functionality similar to those of Microsoft Excel. The diverse features of the component include smart API, rich styling options, support for numerous import/export formats, custom DB functions, formula and charts. Find out more about MindFusion Spreadsheet for WPF here.

MindFusion WinForms Spreadsheet Control: Convert XLSX to PDF

This blog demonstrates how easily you can convert XLSX files to PDF using the Windows Forms Spreadsheet control.

Setup

Create a new WinForms application and add the necessary assemblies to the project. Add a WorkbookView control to the main application window. Note, that this is not necessary for the conversion – it is done only to display the original XLSX file.

Perform the conversion

Add a button to the main form, set its text to ‘Convert…’ and handle its Click event. In the event handler display an OpenFileDialog to query the user for the input XLSX file, then display a SaveFileDialog to request the target PDF file. Once the two files are specified, proceed with the conversion by creating an ExcelImporter and PdfExporter objects and using their Import and Export methods in succession:

// Import the data
var importer = new ExcelImporter();
importer.Import(xlsxPath, workbook1);

// Export the worksheet as PDF
var exporter = new PdfExporter();
exporter.EnableGridLines = true;
exporter.Export(workbook1.Worksheets[0], pdfPath);

// Open the PDF
System.Diagnostics.Process.Start(pdfPath);

The xlsxPath and pdfPath variables identify the respective XLSX and PDF file names. The workbook1 variable represents the Workbook displayed on the form. Once the conversion is complete, the PDF file is opened in the default PDF viewer by calling Process.Start.

The following image illustrates the result:

The source code of the project together with all necessary libraries can be downloaded from here:

Convert .XLSX to .PDF Files Using the WinForms Spreadsheet Control: Download Sample

You are welcome to ask any questions about the WorkbookView control at MindFusion discussion board or per e-mail at support@mindfusion.eu.

Click here here to visit the official page of the MindFusion WinForms Spreadsheet control.

We hope you find this tutorial useful and thank you for your interest in MindFusion developer tools.

MindFusion.WinForms Pack, 2016.R2

MindFusion suite of WinForms controls has just been released and boasts a variety of new features to make you build WinForms applications faster and easier. Here is a review of the new version:

MindFusion Chart Control MindFusion.Charting

New data model

Data that should be drawn in charts is read through an interface called Series, whose instances can be assigned to the Series properties of Chart and SeriesRenderer classes. You can implement this interface in your own model classes to avoid duplicating data. The library includes several pre-defined series classes that let you specify data via IList or array objects.

Different series types in a single plot

The new data model allows adding different series types to a single plot

New rendering model

Chart graphics are drawn inside Plot components by SeriesRenderer-derived objects. Each plot can contain multiple series renderers from same or different types. For example, you can draw area, line and bar graphics in same plot by adding AreaRenderer, LineRenderer and BarRenderer objects to its SeriesRenderers collection. Chart controls automatically generate a series renderer of appropriate type for their Series.

Dashboard

The Dashboard control can contain multiple plots, axes, legends, images, gauges and text blocks arranged in dynamic layout. Individual components can be added to dashboard’s default RootPanel or LayoutPanel containers, or for more complex layouts add intermediary panels such as GridPanel and StackPanel to the default ones. To show different types of chart graphics, add Plot2D to draw in 2D Cartesian coordinate system, Plot3D for 3D Cartesian system, and PolarPlot for polar coordinate system. To draw horizontal or vertical axes, add respectively XAxisRenderer and YAxisRenderer objects. To show gauges, add LinearGaugeRenderer or OvalGaugeRenderer, whose Gauge property contains the gauge model definition.

The new WinForms Chart has a built-in dashboard control.

The new WinForms Chart has a built-in dashboard control.

Print and export

The Dashboard control and Chart controls that derive from it expose Print and PrintPreview methods for printing on paper. Call the ExportImage and CreateImage methods to generate bitmap image of the dashboard. The ExportPdf method exports the chart to a PDF (Portable Document Format) file. The ExportSvg method exports the chart to an SVG (Scalable Vector Graphics) file.

Styling

Values of appearance properties can come from several places in the component hierarchy. SeriesRenderer-derived objects can use attributes from their local SeriesStyle, from plot’s SeriesStyle, or from the *Series properties in current Theme. Component classes use either their local properties or ones defined in the theme. By default, appearance properties in SeriesRenderer > and Component > classes have null values, which makes the drawing code use values from the theme.

A rich choice of styling options are available

A rich choice of styling options are available

MindFusion WebForms Diagrammer MindFusion.Diagramming

Free-form nodes

A FreeFormNode collects all points from users’ mouse or touch input and displays them as node’s outline. To let users draw free-form nodes interactively, set Behavior to DrawFreeForms or LinkFreeForms. Use the Points property of FreeFormNode to get or set outline points programmatically. If the Closed property is set, the node is drawn as a closed shape and its interior filled, or otherwise the node is drawn as a poly-line. If the distance between first and last points drawn by user is shorter than AutoCloseDistance, the node’s Closed property is automatically set to true.

Free form nodes are drawn with the mouse

Free form nodes: just draw the node with the mouse and the control understands the shape you want

LinkLabel edit events

LinkTextEditing and LinkTextEdited events are now raised also when the user edits a LinkLabel. The Label property of the respective event-arguments class identifies the LinkLabel that is being edited. Label is a null reference if the user is editing link’s Text value.

keyboard16x16MindFusion Virtual Keyboard

MindFusion Virtual Keyboard has been initially added to MindFusion Pack for WinForms.

The WinForms virtual keyboard control: extended layout

The WinForms virtual keyboard control: extended layout

WPF Reporting ToolMindFusion.Reporting

Improved charts
MindFusion.Reporting now uses the new MindFusion charting engine to display charts in reports. The presentation of the charts has been greatly improved (particularly when resizing the charts).

Pie charts in a WinForms report

Pie charts in a WinForms report

Spreadsheet-16x16MindFusion.Spreadsheet

New and improved charts
MindFusion.Spreadsheet now uses the new MindFusion charting engine to display charts in worksheets. Along with the improved appearance (particularly when resizing the charts), the following new features have been added:

  • New Candlestick chart type;
  • New BarOverlayed and ColumnOverlayed chart types;
  • Several new legend position types;

Zoom
The worksheets can now be zoomed in and out through the new Zoom property.

Charts in a spreadsheet

The new chart engine makes spreadsheets even more appealing

MindFusion clients can download the installer for the latest version from the clients area on MindFusion website.

A direct link to download the WinForms pack is available from here:

Download MindFusion WinForms Pack 2016.R2

Updated assemblies are also available as MindFusion.Pack NuGet package.

About MindFusion.WinForms Pack: A rich set of programming components that provide WinForms developers with the complete list of features to build even the most complicated business applications fast and easy. The components integrate seamlessly and provide with a mouse click functionality that takes months to develop. Each control boasts various samples and tutorials, extensive documentation and numerous customization options that make it suitable for every type of software and scenario.

Further details about each component in the pack are available from MindFusion website:

Use this link to buy a license online. All components are royalty-free.

WPF Spreadsheet With Conditional Formatting and Sum Function

In this step-by-step guide we will create a spreadsheet in WPF, which represents a  template with the monthly bills.

I. Setup of the Project.

We create a new WPF project and add the following dlls:

Spreadsheet for WPF: referenced dll-s.

Spreadsheet for WPF: referenced dll-s.

Then, in XAML, we map the spreadsheet namespace to the prefix “ss”:

xmlns:ss="http://mindfusion.eu/spreadsheet/wpf"

Finally, we create a WorkbookView and Workbook instances, which we’ll use later in code.

 <ss:WorkbookView x:Name="workbookView">
            <ss:Workbook x:Name="workbook" />
        </ss:WorkbookView>

II. Header.

The next step is to add a sheet to our workbook like this:

            var activeSheet = workbook.Worksheets.Add();
            workbookView.ActiveWorksheet = activeSheet;
            activeSheet.BeginInit();

Now, we can start to build the actual house bills template. First, we define the global formatting for the worksheet:

var globalStyle = activeSheet.CellRanges[0, 0, 
     activeSheet.Columns.Count - 1, activeSheet.Rows.Count - 1].Style;
            globalStyle.FontName = "Tahoma";
            globalStyle.FontSize = 10;

We get all cells from the active sheet and set the font name and font size, which we’ll be used when we edit them.

Then, we define the header of the bills template.

     //initialize header label and style
      var heading = activeSheet.CellRanges["A3:H3"];
      heading.Merge();
      activeSheet.Rows[2].Height = "40pt";
      activeSheet.Columns[0, 8].Width = "80pt";
      activeSheet.Cells["A3"].Data = "Bills for March, 2016";

The header is at the second row and spans from the first to the eighth cell e.g. from A3 to H3. We merge the cells and increase the height. We also increase the width of the first eight columns because we want larger cells to fit the bill content. Last but not least we set the label. The label is assigned to the first cell of the merged cells.

Next, we’ll do some styling of the font and label of the title:

     
      var headingStyle = heading.Style;            
      headingStyle.FontSize = 14;
      headingStyle.FontBold = true;            
      headingStyle.VerticalAlignment = MindFusion.Spreadsheet.Wpf.VerticalAlignment.Middle;
      headingStyle.PaddingLeft = "5pt";
      headingStyle.TextBrush = Brushes.LightSlateGray;

Here we get the Style object of the header, increase the font size and set the font style to bold, vertically align the title in the middle and add some padding to it. We use a slate gray brush for the heading. The underscore is done using two BorderBottom properties:

        headingStyle.BorderBottomBrush = Brushes.DarkGreen;
        headingStyle.BorderBottomSize = "2pt";

III. Description Block.

The description block is made of six cells:

string[] headerLabels = new string[] { "Description", "Start Date",
 "Due Date", "Amount", "Date Paid", "Remarks" };
            for (int i = 0; i < headerLabels.Length; i++)
                activeSheet.Cells[i, 4].Data = headerLabels[i];

Each cell describes certain data we’ll need for the house bills table. We want to apply some formatting to indicate this is a header row. We make the font bold and italic, align text in the middle, add a top border and gray background:

         heading = activeSheet.CellRanges["A5:H5"];
         headingStyle = heading.Style;
         headingStyle.HorizontalAlignment = 
MindFusion.Spreadsheet.Wpf.HorizontalAlignment.Center;
         headingStyle.Background = Brushes.LightGray;
         headingStyle.BorderTopBrush = Brushes.Gray;
         headingStyle.BorderTopSize = "1pt";
         headingStyle.FontItalic = true;
         headingStyle.FontBold = true;
         headingStyle.FontSize = 10;       

The description column will hold presumably more text and we merge three cells for it:

      //merge the cells with the description
      for (int i = 4; i < 15; i++)
        {
           var description = activeSheet.CellRanges[5, i, 7, i];
           description.Merge();
        }

Some of our cells show DateTime values. We want to format the data to show entries like “12-January-2016”. Here is how this is done:

       //format cells that hold date values
       activeSheet.CellRanges["B6:C15"].Style.Format = "dd-mmmm-yy";
       activeSheet.CellRanges["E6:E15"].Style.Format = "dd-mmmm-yy";

IV. Formulas.

We want to have a cell that sums all the current bills to give us an overview how much we have to pay this month. We’ll use the SUM function, which is just one of the many functions Spreadsheet for WPF supports – you can check the list here.

      activeSheet.Cells["D17"].Data = "=SUM(D6:D15)";
      activeSheet.Cells["D17"].Style.Format = "$##.##";

V. Conditional Formatting.

The last feature we want to add is to paint in red those bills whose sum is for more than 100 USD. That is done with conditional formatting:

      var billsStyle = activeSheet.CellRanges[3, 5, 3, 14].Style;
      billsStyle.Format = "$##.##";
      var format1 = billsStyle.ConditionalFormats.Add();
      format1.Type = 
         MindFusion.Spreadsheet.Wpf.ConditionalFormatType.CellValue;
      format1.Operator = 
         MindFusion.Spreadsheet.Wpf.ComparisonOperator.GreaterThan;
      format1.Second = "100";
      format1.Style.TextBrush = Brushes.Red;

We select the cells that render the sums and add a new IConditionalFormat object. It users the operator “greater than” and changes the text brush to red if the data of the cells gets higher than 100.

This is the final type of customization we’ve performed on this workbook. We call

activeSheet.EndInit();

to signalize that initialization for this worksheet is over. Here is the result:

Spreadsheet for WPF Sample: Bills Template

Spreadsheet for WPF Sample: Bills Template

The sample is available for download from this link:

WPF Spreadsheet Control: House Expenses Template

The control’s libraries are included in the archive.

Technical support is available at the discussion board, per e-mail or at the help desk. We are happy to assist you.

About WPF Spreadsheet control from MindFusion: A native WPF component, which provides every single feature you’ll need to create an Excel-like spreadsheet in your own application in a matter of hours. The control is packed with a versatile chart library, which let’s you enhance your spreadsheets with compelling graphs of any type. The various user interaction options fit perfectly with the high demands of today’s application of performance and flexibility. Achieving the desired look and feel is easily accomplished with the efficient style system and the numerous appearance properties. The control provides a line of auxiliary forms, which enable programmers to easily edit and organize the spreadsheet data for the best and fastest possible results.

WPF Spreadsheet control is part of MindFusion WPF Pack of components – the tools that make WPF development fast and easy. Details here.