Recalculation of linked files (VB.Net / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\vb\VS2022\netframework\10.API\77.Recalculation Of Linked Files and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/vb/VS2022/netframework/Modules/10.API/77.Recalculation Of Linked Files
Overview
FlexCel allows complete control over recalculation of linked files. In this demo, we will show how to create 3 linked files and how to recalculate them.
Concepts
How to create files with formulas that link to other files.
There are two ways to create a workspace to calculate linked files:
When you know all the files needed in advance, you just add them to the workspace before recalculating.
If you don't know which files (if any) you are going to need, you can use an event to load them on demand. This is the approach we use in the Validate Recalc example, since it deals with arbitrary files. Whenever you know which files are needed, it is better to use the first method.
Workspaces can take a lot of memory. Make sure you make them null after recalculating, and also the XlsFiles used in it. If you keep the files, those files will point to the workspace even if you make it null, and it will not be released. You can also Clear() the workspace before making it null to make sure you leave no references to it.
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.Collections
Imports System.ComponentModel
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports System.IO
Imports System.Reflection
Imports System.Globalization
Imports System.Drawing.Imaging
Imports System.Drawing.Drawing2D
Namespace RecalculationOfLinkedFiles
Partial Public Class mainForm
Inherits System.Windows.Forms.Form
Private panel2 As System.Windows.Forms.Panel
Private WithEvents button2 As System.Windows.Forms.Button
Private WithEvents CellA1 As System.Windows.Forms.TextBox
Private panel1 As System.Windows.Forms.Panel
Private label1 As System.Windows.Forms.Label
Private label2 As System.Windows.Forms.Label
Private label3 As System.Windows.Forms.Label
Private Cell2 As System.Windows.Forms.TextBox
Private label4 As System.Windows.Forms.Label
Private label5 As System.Windows.Forms.Label
Private label6 As System.Windows.Forms.Label
Private label7 As System.Windows.Forms.Label
Private Cell3 As System.Windows.Forms.TextBox
Private label8 As System.Windows.Forms.Label
Private label9 As System.Windows.Forms.Label
Private Cell4 As System.Windows.Forms.TextBox
Private label18 As System.Windows.Forms.Label
Private panel3 As System.Windows.Forms.Panel
Private WithEvents ChartA1 As System.Windows.Forms.TextBox
Private WithEvents ChartB1 As System.Windows.Forms.TextBox
Private WithEvents ChartB2 As System.Windows.Forms.TextBox
Private WithEvents ChartA2 As System.Windows.Forms.TextBox
Private WithEvents ChartB3 As System.Windows.Forms.TextBox
Private WithEvents ChartA3 As System.Windows.Forms.TextBox
Private chartBox As System.Windows.Forms.PictureBox
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()
Dim resources As New System.ComponentModel.ComponentResourceManager(GetType(mainForm))
Me.panel2 = New System.Windows.Forms.Panel()
Me.button2 = New System.Windows.Forms.Button()
Me.CellA1 = New System.Windows.Forms.TextBox()
Me.panel1 = New System.Windows.Forms.Panel()
Me.label8 = New System.Windows.Forms.Label()
Me.label9 = New System.Windows.Forms.Label()
Me.Cell4 = New System.Windows.Forms.TextBox()
Me.label6 = New System.Windows.Forms.Label()
Me.label7 = New System.Windows.Forms.Label()
Me.Cell3 = New System.Windows.Forms.TextBox()
Me.label5 = New System.Windows.Forms.Label()
Me.label4 = New System.Windows.Forms.Label()
Me.label3 = New System.Windows.Forms.Label()
Me.Cell2 = New System.Windows.Forms.TextBox()
Me.label2 = New System.Windows.Forms.Label()
Me.label1 = New System.Windows.Forms.Label()
Me.label18 = New System.Windows.Forms.Label()
Me.ChartA1 = New System.Windows.Forms.TextBox()
Me.panel3 = New System.Windows.Forms.Panel()
Me.chartBox = New System.Windows.Forms.PictureBox()
Me.ChartB3 = New System.Windows.Forms.TextBox()
Me.ChartA3 = New System.Windows.Forms.TextBox()
Me.ChartB2 = New System.Windows.Forms.TextBox()
Me.ChartA2 = New System.Windows.Forms.TextBox()
Me.ChartB1 = New System.Windows.Forms.TextBox()
Me.panel2.SuspendLayout()
Me.panel1.SuspendLayout()
Me.panel3.SuspendLayout()
CType(Me.chartBox, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
' panel2
'
Me.panel2.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
Me.panel2.Controls.Add(Me.button2)
Me.panel2.Dock = System.Windows.Forms.DockStyle.Bottom
Me.panel2.Location = New System.Drawing.Point(0, 422)
Me.panel2.Name = "panel2"
Me.panel2.Size = New System.Drawing.Size(760, 32)
Me.panel2.TabIndex = 2
'
' button2
'
Me.button2.Anchor = (CType((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
Me.button2.BackColor = System.Drawing.SystemColors.Control
Me.button2.Image = (CType(resources.GetObject("button2.Image"), System.Drawing.Image))
Me.button2.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft
Me.button2.Location = New System.Drawing.Point(697, 2)
Me.button2.Name = "button2"
Me.button2.Size = New System.Drawing.Size(56, 26)
Me.button2.TabIndex = 2
Me.button2.Text = "Exit"
Me.button2.TextAlign = System.Drawing.ContentAlignment.MiddleRight
Me.button2.UseVisualStyleBackColor = False
' Me.button2.Click += New System.EventHandler(Me.button2_Click)
'
' CellA1
'
Me.CellA1.Location = New System.Drawing.Point(24, 88)
Me.CellA1.Name = "CellA1"
Me.CellA1.Size = New System.Drawing.Size(100, 20)
Me.CellA1.TabIndex = 3
' Me.CellA1.TextChanged += New System.EventHandler(Me.CellA1_TextChanged)
'
' panel1
'
Me.panel1.Anchor = (CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
Me.panel1.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
Me.panel1.Controls.Add(Me.label8)
Me.panel1.Controls.Add(Me.label9)
Me.panel1.Controls.Add(Me.Cell4)
Me.panel1.Controls.Add(Me.label6)
Me.panel1.Controls.Add(Me.label7)
Me.panel1.Controls.Add(Me.Cell3)
Me.panel1.Controls.Add(Me.label5)
Me.panel1.Controls.Add(Me.label4)
Me.panel1.Controls.Add(Me.label3)
Me.panel1.Controls.Add(Me.Cell2)
Me.panel1.Controls.Add(Me.label2)
Me.panel1.Controls.Add(Me.label1)
Me.panel1.Controls.Add(Me.CellA1)
Me.panel1.Location = New System.Drawing.Point(8, 8)
Me.panel1.Name = "panel1"
Me.panel1.Size = New System.Drawing.Size(736, 136)
Me.panel1.TabIndex = 4
'
' label8
'
Me.label8.Location = New System.Drawing.Point(520, 72)
Me.label8.Name = "label8"
Me.label8.Size = New System.Drawing.Size(160, 16)
Me.label8.TabIndex = 15
Me.label8.Text = "=[Third File.xls]Sheet1!A1 + 7"
'
' label9
'
Me.label9.Location = New System.Drawing.Point(520, 56)
Me.label9.Name = "label9"
Me.label9.Size = New System.Drawing.Size(100, 16)
Me.label9.TabIndex = 14
Me.label9.Text = "First File: A2"
'
' Cell4
'
Me.Cell4.Enabled = False
Me.Cell4.Location = New System.Drawing.Point(520, 88)
Me.Cell4.Name = "Cell4"
Me.Cell4.Size = New System.Drawing.Size(152, 20)
Me.Cell4.TabIndex = 13
'
' label6
'
Me.label6.Location = New System.Drawing.Point(328, 72)
Me.label6.Name = "label6"
Me.label6.Size = New System.Drawing.Size(184, 16)
Me.label6.TabIndex = 12
Me.label6.Text = "=[Second File.xls]Sheet1!A1 * 5"
'
' label7
'
Me.label7.Location = New System.Drawing.Point(328, 56)
Me.label7.Name = "label7"
Me.label7.Size = New System.Drawing.Size(100, 16)
Me.label7.TabIndex = 11
Me.label7.Text = "Third File: A1"
'
' Cell3
'
Me.Cell3.Enabled = False
Me.Cell3.Location = New System.Drawing.Point(328, 88)
Me.Cell3.Name = "Cell3"
Me.Cell3.Size = New System.Drawing.Size(152, 20)
Me.Cell3.TabIndex = 10
'
' label5
'
Me.label5.Location = New System.Drawing.Point(152, 72)
Me.label5.Name = "label5"
Me.label5.Size = New System.Drawing.Size(152, 16)
Me.label5.TabIndex = 9
Me.label5.Text = "=[First File.xls]Sheet1!A1 * 2"
'
' label4
'
Me.label4.Location = New System.Drawing.Point(24, 72)
Me.label4.Name = "label4"
Me.label4.Size = New System.Drawing.Size(100, 16)
Me.label4.TabIndex = 8
Me.label4.Text = "Constant"
'
' label3
'
Me.label3.Location = New System.Drawing.Point(152, 56)
Me.label3.Name = "label3"
Me.label3.Size = New System.Drawing.Size(100, 16)
Me.label3.TabIndex = 7
Me.label3.Text = "Second File: A1"
'
' Cell2
'
Me.Cell2.Enabled = False
Me.Cell2.Location = New System.Drawing.Point(152, 88)
Me.Cell2.Name = "Cell2"
Me.Cell2.Size = New System.Drawing.Size(144, 20)
Me.Cell2.TabIndex = 6
'
' label2
'
Me.label2.Location = New System.Drawing.Point(24, 56)
Me.label2.Name = "label2"
Me.label2.Size = New System.Drawing.Size(100, 16)
Me.label2.TabIndex = 5
Me.label2.Text = "First File: A1"
'
' label1
'
Me.label1.Anchor = (CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
Me.label1.Location = New System.Drawing.Point(16, 16)
Me.label1.Name = "label1"
Me.label1.Size = New System.Drawing.Size(704, 32)
Me.label1.TabIndex = 4
Me.label1.Text = "In this first example we will dynamically create 3 linked files. We will create a" & " workspace to link the files, and see how recalculation works."
'
' label18
'
Me.label18.Anchor = (CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
Me.label18.Location = New System.Drawing.Point(16, 16)
Me.label18.Name = "label18"
Me.label18.Size = New System.Drawing.Size(704, 32)
Me.label18.TabIndex = 4
Me.label18.Text = "This second example shows how to load files when we don't know a priori which fil" & "es we need to recalculate a file. To make it more interesting, we will use a cha" & "rt linked to other file."
'
' ChartA1
'
Me.ChartA1.Location = New System.Drawing.Point(16, 56)
Me.ChartA1.Name = "ChartA1"
Me.ChartA1.Size = New System.Drawing.Size(64, 20)
Me.ChartA1.TabIndex = 3
Me.ChartA1.Text = "1"
' Me.ChartA1.TextChanged += New System.EventHandler(Me.Chart_TextChanged)
'
' panel3
'
Me.panel3.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.panel3.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
Me.panel3.Controls.Add(Me.chartBox)
Me.panel3.Controls.Add(Me.ChartB3)
Me.panel3.Controls.Add(Me.ChartA3)
Me.panel3.Controls.Add(Me.ChartB2)
Me.panel3.Controls.Add(Me.ChartA2)
Me.panel3.Controls.Add(Me.ChartB1)
Me.panel3.Controls.Add(Me.label18)
Me.panel3.Controls.Add(Me.ChartA1)
Me.panel3.Location = New System.Drawing.Point(8, 176)
Me.panel3.Name = "panel3"
Me.panel3.Size = New System.Drawing.Size(736, 225)
Me.panel3.TabIndex = 5
'
' 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(176, 56)
Me.chartBox.Name = "chartBox"
Me.chartBox.Size = New System.Drawing.Size(544, 152)
Me.chartBox.SizeMode = System.Windows.Forms.PictureBoxSizeMode.StretchImage
Me.chartBox.TabIndex = 52
Me.chartBox.TabStop = False
'
' ChartB3
'
Me.ChartB3.Location = New System.Drawing.Point(88, 104)
Me.ChartB3.Name = "ChartB3"
Me.ChartB3.Size = New System.Drawing.Size(64, 20)
Me.ChartB3.TabIndex = 9
Me.ChartB3.Text = "5"
' Me.ChartB3.TextChanged += New System.EventHandler(Me.Chart_TextChanged)
'
' ChartA3
'
Me.ChartA3.Location = New System.Drawing.Point(16, 104)
Me.ChartA3.Name = "ChartA3"
Me.ChartA3.Size = New System.Drawing.Size(64, 20)
Me.ChartA3.TabIndex = 8
Me.ChartA3.Text = "3"
' Me.ChartA3.TextChanged += New System.EventHandler(Me.Chart_TextChanged)
'
' ChartB2
'
Me.ChartB2.Location = New System.Drawing.Point(88, 80)
Me.ChartB2.Name = "ChartB2"
Me.ChartB2.Size = New System.Drawing.Size(64, 20)
Me.ChartB2.TabIndex = 7
Me.ChartB2.Text = "4"
' Me.ChartB2.TextChanged += New System.EventHandler(Me.Chart_TextChanged)
'
' ChartA2
'
Me.ChartA2.Location = New System.Drawing.Point(16, 80)
Me.ChartA2.Name = "ChartA2"
Me.ChartA2.Size = New System.Drawing.Size(64, 20)
Me.ChartA2.TabIndex = 6
Me.ChartA2.Text = "2"
' Me.ChartA2.TextChanged += New System.EventHandler(Me.Chart_TextChanged)
'
' ChartB1
'
Me.ChartB1.Location = New System.Drawing.Point(88, 56)
Me.ChartB1.Name = "ChartB1"
Me.ChartB1.Size = New System.Drawing.Size(64, 20)
Me.ChartB1.TabIndex = 5
Me.ChartB1.Text = "3"
' Me.ChartB1.TextChanged += New System.EventHandler(Me.Chart_TextChanged)
'
' mainForm
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(760, 454)
Me.Controls.Add(Me.panel3)
Me.Controls.Add(Me.panel1)
Me.Controls.Add(Me.panel2)
Me.Name = "mainForm"
Me.Text = "Calculation of linked files"
Me.panel2.ResumeLayout(False)
Me.panel1.ResumeLayout(False)
Me.panel1.PerformLayout()
Me.panel3.ResumeLayout(False)
Me.panel3.PerformLayout()
CType(Me.chartBox, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
End Class
End Namespace
Form1.vb
Imports System.Collections
Imports System.ComponentModel
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports System.IO
Imports System.Reflection
Imports System.Globalization
Imports System.Drawing.Imaging
Imports System.Drawing.Drawing2D
Namespace RecalculationOfLinkedFiles
''' <summary>
''' Shows how to recalculate linked files.
''' </summary>
Partial Public Class mainForm
Inherits System.Windows.Forms.Form
Public Sub New()
InitializeComponent()
End Sub
Private Sub button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button2.Click
Close()
End Sub
Private Sub CellA1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CellA1.TextChanged
'This is a very slow way to do this (recreating the full 3 files each time you type a character)
'but it is the best for what we want to show. (how to create and recalculate spreadsheets)
'In a real world example you would keep the created files in memory and just recalculate them
'when there is a change.
CreateFilesAndRecalculate()
End Sub
''' <summary>
''' This method will try to convert a text to a string, and if not possible, return the text.
''' </summary>
''' <param name="s"></param>
''' <returns></returns>
Private Function GetValue(ByVal s As String) As Object
Dim d As Double
If Double.TryParse(s, NumberStyles.Any, CultureInfo.CurrentCulture, d) Then
Return d
End If
Return s
End Function
Private Sub CreateFilesAndRecalculate()
'Set up the files.
Dim xls1 As New XlsFile()
xls1.NewFile(1, TExcelFileFormat.v2019)
xls1.SetCellValue(1, 1, GetValue(CellA1.Text))
xls1.SetCellValue(2, 1, New TFormula("=[Third File.xls]Sheet1!A1 + 7"))
Dim xls2 As New XlsFile()
xls2.NewFile(1, TExcelFileFormat.v2019)
xls2.SetCellValue(1, 1, New TFormula("=[First File.xls]Sheet1!A1 * 2"))
Dim xls3 As New XlsFile()
xls3.NewFile(1, TExcelFileFormat.v2019)
xls3.SetCellValue(1, 1, New TFormula("=[Second File.xls]Sheet1!A1 * 5"))
'Create a workspace to recalculate them.
'In this case, as we know what files we need in advance, we will just add them to the workspace
'For an example on how to load files on demand, take a look at the chart example in this demo.
Dim Workspace As New TWorkspace()
Workspace.Add("First File.xls", xls1)
Workspace.Add("Second File.xls", xls2)
Workspace.Add("Third File.xls", xls3)
'Now that the workspace is set, we can recalculate. We could recalc() in the Workspace object or in any of the files in it.
'The effect is the same, all files will be recalculated.
'DO NOT RECALCULATE EVERY FILE. EACH TIME YOU CALCULATE ONE, YOU ARE CALCULATING THEM ALL.
xls1.Recalc()
'Ok, now it is time to show the results.
Cell2.Text = Convert.ToString(CType(xls2.GetCellValue(1, 1), TFormula).Result)
Cell3.Text = Convert.ToString(CType(xls3.GetCellValue(1, 1), TFormula).Result)
Cell4.Text = Convert.ToString(CType(xls1.GetCellValue(2, 1), TFormula).Result)
'In this example both the workspace and the xls files are local objects, so we don't need to worry about memory
'If any of them is a global object, remember that keeping a reference to it will keep a reference to *ALL* the
'files in the workspace (even if you make Workspace = null). You might want to call Workspace.Clear() in that case before setting it to null.
End Sub
Private Sub Chart_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChartA1.TextChanged, ChartB3.TextChanged, ChartA3.TextChanged, ChartB2.TextChanged, ChartA2.TextChanged, ChartB1.TextChanged
'Again, loading the file each time we press a key is incredibly silly. But for this example is ok,
'since loading the files is what we actually want to show.
Dim TemplatePath As String = Path.Combine(Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), ".."), "..")
Dim xlsChart As New XlsFile()
xlsChart.Open(Path.Combine(TemplatePath, "Chart.xls"))
'Create a Workspace.
'Note that if we didn't create this workspace, the chart would not show, since it wouldn't be able to
'find the linked file. You can verify it by commenting the following lines
Dim Workspace As New TWorkspace()
Workspace.Add("Chart.xls", xlsChart) 'We always need to have the main file in the workspace.
'The best thing here would be to also add "ChartData.xls" to the workspace, since we already know which file we need.
'But since we already saw how to do that in the other example in this demo, we are going to pretend we don't know which files
'we need, and load them on demand.
'NOTE: DON'T LOAD FILES ON DEMAND UNLESS YOU REALLY NEED TO, SINCE YOU MIGHT BE CREATING A SECURITY RISK. Read the API GUIDE PDF for more information.
AddHandler Workspace.LoadLinkedFile, AddressOf Workspace_LoadLinkedFile
'Now that the Workspace is created, we can render the chart. We will use the code from "Render Objects" demo.
If chartBox.Image IsNot Nothing Then
chartBox.Image.Dispose()
End If
chartBox.Image = GetChart(xlsChart, 1) 'To do this well, we should name the chart, retrieve the object index and use it here.
'To see how this should be done, look at the Render Objects demo. Here we won't care about that, and just use "1" since we know the chart is the only object in the file.
End Sub
''' <summary>
''' This event is used when there are linked files, to load them on demand.
''' </summary>
Private Sub Workspace_LoadLinkedFile(ByVal sender As Object, ByVal e As LoadLinkedFileEventArgs)
'In order to reduce the risk of opening any file, in this demo we are going to only open files in the same folder we are working on.
Dim xls As New XlsFile()
Dim TemplatePath As String = Path.Combine(Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), ".."), "..")
xls.Open(Path.Combine(TemplatePath, Path.GetFileName(e.FileName)))
e.Xls = xls
'A normal event should end here. Since we need to change the values of the file we loaded in demand, we will do that here.
xls.SetCellValue(4, 2, GetValue(ChartA1.Text))
xls.SetCellValue(5, 2, GetValue(ChartA2.Text))
xls.SetCellValue(6, 2, GetValue(ChartA3.Text))
xls.SetCellValue(4, 3, GetValue(ChartB1.Text))
xls.SetCellValue(5, 3, GetValue(ChartB2.Text))
xls.SetCellValue(6, 3, GetValue(ChartB3.Text))
End Sub
'This code is from the "Render objects" demo, and returns the image of a chart.
Private Function GetChart(ByVal Xls As ExcelFile, ByVal ChartIndex As Integer) As Image
Dim ChartProps As TShapeProperties = Xls.GetObjectProperties(ChartIndex, True)
'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 Single = 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 = CSng(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
End Class
End Namespace
Program.vb
Namespace RecalculationOfLinkedFiles
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