Rendering standalone objects (VB.Net / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\vb\VS2022\netframework\25.Printing and Exporting\45.Render Objects and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/vb/VS2022/netframework/Modules/25.Printing and Exporting/45.Render Objects
Overview
While you might normally want to render a full sheet (or a range of cells), you can also use FlexCel to render specific objects in the workbook.
Concepts
This is a simple application where we periodically update a number, and use FlexCel to recalculate the formulas and render a chart of the values. While you would normally not use FlexCel this way (and it is probably better to use a separate chart package), it gives a nice tasting on FlexCel capabilities.
How to use RenderObject to render a simple object in a sheet. In this demo, we are rendering the object named "datachart".
As the chart and the calculations are defined in the spreadsheet, you can add new themes to the application or modify the existing ones by creating and modifying the xls files in the templates folders, without needing to recompile the application. You can even do it in real time. Have a template open in Excel, make changes, save, and reload the template in the application by selecting it again in the listbox. Changes will appear instantly without needing to close the main application. This technique can be quite useful to let users customize your application.
In this example, we named the chart "DataChart", so we can identify it from the application. In order to name a chart object, ctrl-click in the chart (it should show white handles, not black), and then change the name in the name box at the top left in Excel.
Files
AssemblyInfo.vb
Imports System.Reflection
Imports 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 - 2014 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("6.2.1.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.Designer.vb
Imports System.Drawing.Drawing2D
Imports System.Collections
Imports System.ComponentModel
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports FlexCel.Render
Imports System.IO
Imports System.Reflection
Imports System.Text
Namespace RenderObjects
Partial Public Class mainForm
Inherits System.Windows.Forms.Form
Private components As System.ComponentModel.IContainer = Nothing
''' <summary>
''' Clean up any resources being used.
''' </summary>
Protected Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If components IsNot Nothing Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
#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 Sub InitializeComponent()
Me.components = New System.ComponentModel.Container()
Dim resources As New System.ComponentModel.ComponentResourceManager(GetType(mainForm))
Me.panel1 = New System.Windows.Forms.Panel()
Me.panelError = New System.Windows.Forms.Panel()
Me.labelError = New System.Windows.Forms.Label()
Me.chartBox = New System.Windows.Forms.PictureBox()
Me.panel7 = New System.Windows.Forms.Panel()
Me.cbTheme = New System.Windows.Forms.ComboBox()
Me.label2 = New System.Windows.Forms.Label()
Me.checkBox4 = New System.Windows.Forms.CheckBox()
Me.updater = New System.Windows.Forms.Timer(Me.components)
Me.mainToolbar = New System.Windows.Forms.ToolStrip()
Me.btnRun = New System.Windows.Forms.ToolStripButton()
Me.toolStripSeparator1 = New System.Windows.Forms.ToolStripSeparator()
Me.btnExit = New System.Windows.Forms.ToolStripButton()
Me.btnCancel = New System.Windows.Forms.ToolStripButton()
Me.panel1.SuspendLayout()
Me.panelError.SuspendLayout()
CType(Me.chartBox, System.ComponentModel.ISupportInitialize).BeginInit()
Me.panel7.SuspendLayout()
Me.mainToolbar.SuspendLayout()
Me.SuspendLayout()
'
' panel1
'
Me.panel1.BackColor = System.Drawing.Color.White
Me.panel1.Controls.Add(Me.panelError)
Me.panel1.Controls.Add(Me.chartBox)
Me.panel1.Controls.Add(Me.panel7)
Me.panel1.Dock = System.Windows.Forms.DockStyle.Fill
Me.panel1.Location = New System.Drawing.Point(0, 38)
Me.panel1.Name = "panel1"
Me.panel1.Size = New System.Drawing.Size(464, 392)
Me.panel1.TabIndex = 3
'
' panelError
'
Me.panelError.Controls.Add(Me.labelError)
Me.panelError.Location = New System.Drawing.Point(136, 128)
Me.panelError.Name = "panelError"
Me.panelError.Size = New System.Drawing.Size(200, 100)
Me.panelError.TabIndex = 52
Me.panelError.Visible = False
'
' labelError
'
Me.labelError.Location = New System.Drawing.Point(8, 16)
Me.labelError.Name = "labelError"
Me.labelError.Size = New System.Drawing.Size(100, 23)
Me.labelError.TabIndex = 0
'
' chartBox
'
Me.chartBox.Anchor = (CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
Me.chartBox.Location = New System.Drawing.Point(24, 120)
Me.chartBox.Name = "chartBox"
Me.chartBox.Size = New System.Drawing.Size(416, 250)
Me.chartBox.SizeMode = System.Windows.Forms.PictureBoxSizeMode.StretchImage
Me.chartBox.TabIndex = 51
Me.chartBox.TabStop = False
'
' panel7
'
Me.panel7.Anchor = (CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
Me.panel7.BackColor = System.Drawing.Color.FromArgb((CInt((CByte(224)))), (CInt((CByte(224)))), (CInt((CByte(224)))))
Me.panel7.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
Me.panel7.Controls.Add(Me.cbTheme)
Me.panel7.Controls.Add(Me.label2)
Me.panel7.Location = New System.Drawing.Point(16, 16)
Me.panel7.Name = "panel7"
Me.panel7.Size = New System.Drawing.Size(432, 72)
Me.panel7.TabIndex = 44
'
' cbTheme
'
Me.cbTheme.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
Me.cbTheme.Location = New System.Drawing.Point(8, 32)
Me.cbTheme.Name = "cbTheme"
Me.cbTheme.Size = New System.Drawing.Size(248, 21)
Me.cbTheme.TabIndex = 46
' Me.cbTheme.SelectedIndexChanged += New System.EventHandler(Me.cbTheme_SelectedIndexChanged)
'
' label2
'
Me.label2.Font = New System.Drawing.Font("Arial", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, (CByte(0)))
Me.label2.Location = New System.Drawing.Point(8, 8)
Me.label2.Name = "label2"
Me.label2.Size = New System.Drawing.Size(192, 16)
Me.label2.TabIndex = 19
Me.label2.Text = "Select Theme:"
'
' checkBox4
'
Me.checkBox4.Location = New System.Drawing.Point(0, 0)
Me.checkBox4.Name = "checkBox4"
Me.checkBox4.Size = New System.Drawing.Size(104, 24)
Me.checkBox4.TabIndex = 0
'
' updater
'
' Me.updater.Tick += New System.EventHandler(Me.updater_Tick)
'
' mainToolbar
'
Me.mainToolbar.Items.AddRange(New System.Windows.Forms.ToolStripItem() { Me.btnRun, Me.toolStripSeparator1, Me.btnExit, Me.btnCancel})
Me.mainToolbar.Location = New System.Drawing.Point(0, 0)
Me.mainToolbar.Name = "mainToolbar"
Me.mainToolbar.Size = New System.Drawing.Size(464, 38)
Me.mainToolbar.TabIndex = 11
Me.mainToolbar.Text = "toolStrip1"
'
' btnRun
'
Me.btnRun.Image = (CType(resources.GetObject("btnRun.Image"), System.Drawing.Image))
Me.btnRun.ImageTransparentColor = System.Drawing.Color.Magenta
Me.btnRun.Name = "btnRun"
Me.btnRun.Size = New System.Drawing.Size(35, 35)
Me.btnRun.Text = "Run!"
Me.btnRun.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText
' Me.btnRun.Click += New System.EventHandler(Me.btnRun_Click)
'
' toolStripSeparator1
'
Me.toolStripSeparator1.Name = "toolStripSeparator1"
Me.toolStripSeparator1.Size = New System.Drawing.Size(6, 38)
'
' btnExit
'
Me.btnExit.Alignment = System.Windows.Forms.ToolStripItemAlignment.Right
Me.btnExit.Image = (CType(resources.GetObject("btnExit.Image"), System.Drawing.Image))
Me.btnExit.ImageTransparentColor = System.Drawing.Color.Magenta
Me.btnExit.Name = "btnExit"
Me.btnExit.Size = New System.Drawing.Size(59, 35)
Me.btnExit.Text = " E&xit "
Me.btnExit.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText
' Me.btnExit.Click += New System.EventHandler(Me.button2_Click)
'
' btnCancel
'
Me.btnCancel.Enabled = False
Me.btnCancel.Image = (CType(resources.GetObject("btnCancel.Image"), System.Drawing.Image))
Me.btnCancel.ImageTransparentColor = System.Drawing.Color.Magenta
Me.btnCancel.Name = "btnCancel"
Me.btnCancel.Size = New System.Drawing.Size(47, 35)
Me.btnCancel.Text = "Cancel"
Me.btnCancel.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText
' Me.btnCancel.Click += New System.EventHandler(Me.btnCancel_Click)
'
' mainForm
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(464, 430)
Me.Controls.Add(Me.panel1)
Me.Controls.Add(Me.mainToolbar)
Me.MaximumSize = New System.Drawing.Size(800, 800)
Me.Name = "mainForm"
Me.Text = "Using FlexCel to render just a part of a spreadshet"
Me.panel1.ResumeLayout(False)
Me.panelError.ResumeLayout(False)
CType(Me.chartBox, System.ComponentModel.ISupportInitialize).EndInit()
Me.panel7.ResumeLayout(False)
Me.mainToolbar.ResumeLayout(False)
Me.mainToolbar.PerformLayout()
Me.ResumeLayout(False)
Me.PerformLayout()
End Sub
#End Region
Private panel1 As System.Windows.Forms.Panel
Private checkBox4 As System.Windows.Forms.CheckBox
Private panel7 As System.Windows.Forms.Panel
Private label2 As System.Windows.Forms.Label
Private chartBox As System.Windows.Forms.PictureBox
Private WithEvents updater As System.Windows.Forms.Timer
Private WithEvents cbTheme As System.Windows.Forms.ComboBox
Private panelError As System.Windows.Forms.Panel
Private labelError As System.Windows.Forms.Label
Private mainToolbar As ToolStrip
Private WithEvents btnRun As ToolStripButton
Private toolStripSeparator1 As ToolStripSeparator
Private WithEvents btnExit As ToolStripButton
Private WithEvents btnCancel As ToolStripButton
End Class
End Namespace
Form1.vb
Imports System.Drawing.Drawing2D
Imports System.Collections
Imports System.ComponentModel
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports FlexCel.Render
Imports System.IO
Imports System.Reflection
Imports System.Text
Namespace RenderObjects
''' <summary>
''' An Example on how to render a chart.
''' </summary>
Partial Public Class mainForm
Inherits System.Windows.Forms.Form
Public Sub New()
InitializeComponent()
ResizeToolbar(mainToolbar)
End Sub
Private Sub ResizeToolbar(ByVal toolbar As ToolStrip)
Using gr As Graphics = CreateGraphics()
Dim xFactor As Double = gr.DpiX / 96.0
Dim yFactor As Double = gr.DpiY / 96.0
toolbar.ImageScalingSize = New Size(CInt(Fix(24 * xFactor)), CInt(Fix(24 * yFactor)))
toolbar.Width = 0 'force a recalc of the buttons.
End Using
End Sub
#Region "Global variables"
Private Xls As XlsFile
Private ValueRange As TXlsNamedRange
Private MinValue As Double
Private MaxValue As Double
Private StepValue As Double
Private ActualValue As Double
Private ChartIndex As Integer
Private ChartProps As TShapeProperties
#End Region
Private Sub InitApp()
Xls = New XlsFile()
Dim TemplatePath As String = Path.Combine(Path.Combine(Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), ".."), ".."), "templates") & Path.DirectorySeparatorChar
Dim di As New DirectoryInfo(TemplatePath)
Dim fi() As FileInfo = di.GetFiles("*.xls")
If fi.Length = 0 Then
Throw New Exception("Sorry, no templates found in the templates folder.")
End If
cbTheme.Items.Clear()
For Each f As FileInfo In fi
cbTheme.Items.Add(New FileHolder(f.FullName))
Next f
cbTheme.SelectedIndex = 0
End Sub
Private Sub LoadFile(ByVal FileName As String)
Xls.Open(FileName)
ActualValue = 0
ValueRange = Xls.GetNamedRange("Value", 0)
If ValueRange Is Nothing Then
Throw New Exception("There is no range named ""value"" in the template")
End If
MinValue = ReadDoubleName("Minimum")
MaxValue = ReadDoubleName("Maximum")
StepValue = ReadDoubleName("Step")
ChartIndex = -1
For i As Integer = 1 To Xls.ObjectCount
Dim ObjName As String = Xls.GetObjectName(i)
If String.Compare(ObjName, "DataChart", True) = 0 Then
ChartIndex = i
Exit For
End If
Next i
If ChartIndex < 0 Then
Throw New Exception("There is no object named ""DataChart"" in the template")
End If
ChartProps = Xls.GetObjectProperties(ChartIndex, True)
End Sub
Private Function ReadDoubleName(ByVal Name As String) As Double
Dim Range As TXlsCellRange = Xls.GetNamedRange(Name, 0)
If Range Is Nothing Then
Throw New Exception("There is no range named " & Name & " in the template")
End If
Dim val As Object = Xls.GetCellValue(Range.Top, Range.Left)
If Not(TypeOf val Is Double) Then
Throw New Exception("The range named " & Name & " does not contain a number")
End If
Return CDbl(val)
End Function
Private Sub button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExit.Click
Close()
End Sub
Private Sub updater_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles updater.Tick
Try
ActualValue += StepValue
If ActualValue > MaxValue Then
ActualValue = MinValue
End If
Xls.SetCellValue(ValueRange.Top, ValueRange.Left, ActualValue)
Xls.Recalc()
If chartBox.Image IsNot Nothing Then
chartBox.Image.Dispose()
End If
chartBox.Image = GetChart()
Catch ex As Exception 'We don't want any dialog popping up every second.
labelError.Text = ex.Message
labelError.Dock = DockStyle.Fill
panelError.Dock = DockStyle.Fill
panelError.Visible = True
updater.Enabled = False
End Try
End Sub
Private Function GetChart() As Image
'We could get the chart with the following command,
'but it would be fixed size. In this example we are going to be a little more complex.
'Xls.RenderObject(ChartIndex);
'A more complex way to retrieve the chart, to show how to use
'all parameters in renderobject.
Dim ImageDimensions As TUIRectangle
Dim Origin As TPointF
Dim SizePixels As TUISize
'First calculate the chart dimensions without actually rendering it. This is fast.
Xls.RenderObject(ChartIndex, 96, ChartProps, SmoothingMode.AntiAlias, InterpolationMode.HighQualityBicubic, True, False, Origin, ImageDimensions, SizePixels)
Dim dpi As Double = 96 'default screen resolution
If SizePixels.Height > 0 AndAlso SizePixels.Width > 0 Then
Dim AspectX As Double = CDbl(chartBox.Width) / SizePixels.Width
Dim AspectY As Double = CDbl(chartBox.Height) / SizePixels.Height
Dim Aspect As Double = Math.Max(AspectX, AspectY)
'Make the dpi adjust the screen resolution and the size of the form.
dpi = CDbl(96 * Aspect)
If dpi < 20 Then
dpi = 20
End If
If dpi > 500 Then
dpi = 500
End If
End If
Return Xls.RenderObject(ChartIndex, dpi, ChartProps, SmoothingMode.AntiAlias, InterpolationMode.HighQualityBicubic, True, True, Origin, ImageDimensions, SizePixels)
End Function
Private Sub cbTheme_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbTheme.SelectedIndexChanged
If cbTheme.SelectedItem Is Nothing Then
Return
End If
LoadFile((TryCast(cbTheme.SelectedItem, FileHolder)).FullName)
End Sub
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
If Xls Is Nothing Then
InitApp()
End If
updater.Enabled = True
btnRun.Enabled = False
btnCancel.Enabled = True
End Sub
Private Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click
updater.Enabled = False
btnRun.Enabled = True
btnCancel.Enabled = False
panelError.Visible = False
End Sub
End Class
Friend Class FileHolder
Friend FullName As String
Private Caption As String
Friend Sub New(ByVal aFullName As String)
FullName = aFullName
Caption = Path.GetFileNameWithoutExtension(aFullName)
End Sub
Public Overrides Function ToString() As String
Return Caption
End Function
End Class
End Namespace
Program.vb
Namespace RenderObjects
Friend NotInheritable Class Program
Private Sub New()
End Sub
''' <summary>
''' The main entry point for the application.
''' </summary>
<STAThread> _
Shared Sub Main()
Application.EnableVisualStyles()
Application.SetCompatibleTextRenderingDefault(False)
Application.Run(New mainForm())
End Sub
End Class
End Namespace