Using Linq as datasource (C# / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\netframework\20.Reports\22.Linq and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/csharp/VS2022/netframework/Modules/20.Reports/22.Linq
Overview
THIS DEMO NEEDS .NET 3.5 OR NEWER.
Most of the demos here use datasets as datasources. This is just for convenience, because Linq is not supported in .NET 2.0 and so if we used Linq those demos wouldn't work for everybody, and also because the focus is in the Excel templates, not so much in the data layer. But you can use any IQueryable<T> collection as a datasource in a FlexCel report, and this is what we will show here.
Concepts
How to run a report from a List<> of objects.
When using IQueryable<T> as datasource, you can use any public property of T in the report. So if type T has a public property "LastName", you can access it with <#dt.LastName>.
Master detail with implicit relationships. When a public property of a collection of objects is other collection of objects, the property is considered as a detail of the main collection. In this example "Elements" is a property of "Categories", and so there is an implicit relationship between them.
Master detail with explicit relationships. While when using LINQ you will normally use implicit relationships, you can also relate any two collections of objects with a relationship, as you could with datasets. In this example, "ElementName" is explicitly related to "Elements" with a call to FlexCelReport.AddRelationship.
Files
AssemblyInfo.cs
using System.Reflection;
using System.Runtime.CompilerServices;
//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("")]
[assembly: AssemblyCopyright("(c) 2002 - 2024 TMS Software")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("7.21.0.0")]
//
// In order to sign your assembly you must specify a key to use. Refer to the
// Microsoft .NET Framework documentation for more information on assembly signing.
//
// Use the attributes below to control which key is used for signing.
//
// Notes:
// (*) If no key is specified, the assembly is not signed.
// (*) KeyName refers to a key that has been installed in the Crypto Service
// Provider (CSP) on your machine. KeyFile refers to a file which contains
// a key.
// (*) If the KeyFile and the KeyName values are both specified, the
// following processing occurs:
// (1) If the KeyName can be found in the CSP, that key is used.
// (2) If the KeyName does not exist and the KeyFile does exist, the key
// in the KeyFile is installed into the CSP and used.
// (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
// When specifying the KeyFile, the location of the KeyFile should be
// relative to the project output directory which is
// %Project Directory%\obj\<configuration>. For example, if your KeyFile is
// located in the project directory, you would specify the AssemblyKeyFile
// attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
// (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
// documentation for more information on this.
//
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("")]
[assembly: AssemblyKeyName("")]
Form1.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using FlexCel.Report;
using System.Collections.Generic;
using System.Linq;
namespace Linq
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public partial class mainForm : System.Windows.Forms.Form
{
public mainForm()
{
InitializeComponent();
}
private void button1_Click(object sender, System.EventArgs e)
{
AutoRun();
}
public void AutoRun()
{
using (FlexCelReport report = new FlexCelReport(true))
{
LoadTables(report);
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
report.Run(DataPath + "Linq.template.xls", saveFileDialog1.FileName);
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
using (Process p = new Process())
{
p.StartInfo.FileName = saveFileDialog1.FileName;
p.StartInfo.UseShellExecute = true;
p.Start();
}
}
}
}
}
private void LoadTables(FlexCelReport report)
{
List<Categories> Categories = new List<Categories>();
Categories Animals = new Categories("Animals");
Animals.Elements.Add(new Elements(1, "Penguin"));
Animals.Elements.Add(new Elements(2, "Cat"));
Animals.Elements.Add(new Elements(3, "Unicorn"));
Categories.Add(Animals);
Categories Flowers = new Categories("Flowers");
Flowers.Elements.Add(new Elements(4, "Daisy"));
Flowers.Elements.Add(new Elements(5, "Rose"));
Flowers.Elements.Add(new Elements(6, "Orchid"));
Categories.Add(Flowers);
report.AddTable("Categories", Categories );
//We don't need to call AddTable for elements since it is already added when we add Categories.
List<ElementName> ElementNames = new List<ElementName>();
ElementNames.Add(new ElementName(1, "Linus"));
ElementNames.Add(new ElementName(1, "Gerard"));
ElementNames.Add(new ElementName(2, "Rover"));
ElementNames.Add(new ElementName(3, "Mike"));
ElementNames.Add(new ElementName(5, "Rosalyn"));
ElementNames.Add(new ElementName(5, "Monica"));
ElementNames.Add(new ElementName(6, "Lisa"));
report.AddTable("ElementName", ElementNames);
//ElementName doesn't have an intrinsic relationship with categories, so we will have to manually add a relationship.
//Non intrinsic relationships should be rare, but we do it here to show how it can be done.
report.AddRelationship("Elements", "ElementName", "ElementID", "ElementID");
}
private void btnCancel_Click(object sender, System.EventArgs e)
{
Close();
}
}
public class Categories
{
//Public properties can be used in reports.
public string Name { get; private set; }
//Elements is in master-detail relationship with this element, even when we don't explicitly add a relationship.
//Relationship is inferred because Elements is a property of this object
public List<Elements> Elements { get; private set; }
public Categories(string name)
{
this.Name = name;
Elements = new List<Elements>();
}
}
public class Elements
{
//We will relate this property with the table of colors by adding a relationship.
public int ElementID { get; private set; }
public string Name { get; private set; }
public Elements(int elementID, string name)
{
this.Name = name;
this.ElementID = elementID;
}
}
public class ElementName
{
public int ElementID { get; private set; }
public string Name { get; private set; }
public ElementName(int elementID, string name)
{
this.ElementID = elementID;
this.Name = name;
}
}
}
Form1.Designer.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using FlexCel.Report;
namespace Linq
{
public partial class mainForm : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.SaveFileDialog saveFileDialog1;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Button btnCancel;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
this.label1 = new System.Windows.Forms.Label();
this.btnCancel = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// button1
//
this.button1.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.button1.BackColor = System.Drawing.Color.Green;
this.button1.ForeColor = System.Drawing.Color.White;
this.button1.Location = new System.Drawing.Point(152, 88);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(112, 23);
this.button1.TabIndex = 0;
this.button1.Text = "GO!";
this.button1.UseVisualStyleBackColor = false;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// saveFileDialog1
//
this.saveFileDialog1.Filter = "Excel Files|*.xls";
this.saveFileDialog1.RestoreDirectory = true;
//
// label1
//
this.label1.Location = new System.Drawing.Point(24, 24);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(272, 24);
this.label1.TabIndex = 2;
this.label1.Text = "Press \"GO\" to create a report from a List<> of objects.";
//
// btnCancel
//
this.btnCancel.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.btnCancel.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(192)))), ((int)(((byte)(0)))), ((int)(((byte)(0)))));
this.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel;
this.btnCancel.ForeColor = System.Drawing.Color.White;
this.btnCancel.Location = new System.Drawing.Point(272, 88);
this.btnCancel.Name = "btnCancel";
this.btnCancel.Size = new System.Drawing.Size(112, 23);
this.btnCancel.TabIndex = 3;
this.btnCancel.Text = "Cancel";
this.btnCancel.UseVisualStyleBackColor = false;
this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
//
// mainForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(416, 133);
this.Controls.Add(this.btnCancel);
this.Controls.Add(this.label1);
this.Controls.Add(this.button1);
this.Name = "mainForm";
this.Text = "Linq Reports";
this.ResumeLayout(false);
}
#endregion
}
}
Program.cs
using System;
using System.Windows.Forms;
namespace Linq
{
static class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new mainForm());
}
}
}