Advanced API (VB.Net / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\vb\VS2022\netframework\10.API\12.Advanced API and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/vb/VS2022/netframework/Modules/10.API/12.Advanced API
Overview
This demo shows a lot of unrelated things you can do with the FlexCel API.
It is a complement of what was shown in GettingStarted: this means things that were shown there (like how to insert an image) will not be repeated here. You should look at both demos when studying the API.
The things shown here do not make much sense in the real world; they are explicitly designed to show what can be done.
Also remember that most of this stuff can be found easier with APIMate
The created file will be password protected, and all the passwords are "flexcel" (without quotes)
Concepts
FlexCel shines at modifying existing Excel files. It has been designed from the start with that in mind, and to not losing anything in the original file. So we will start from an existing file with the skeleton of what we want to do, and modify it with code. This starting file (template.xls/x) could be embedded in the exe file, but this is not shown here. To see an example on how to embed the template inside the exe, look at Templates In The Exe
The starting template has a macro. At this time we can not create macros with FlexCel, but we can modify a file with existing macros and those macros will be in the final file.
How to copy a chart from other template and fill the values.
How to add Outlines.
How to freeze panes.
How to add data validation.
How to search and replace in a range of cells.
How to sort a range of cells.
How to Autofit the rows in a sheet.
How to protect the file and cells with a password.
As FlexCel doesn't convert charts between xlsx and xls, if you want to create an xls file you need to start with an xls template, and the same goes for xlsx. So we need two templates.
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 FlexCel.Core
Imports FlexCel.XlsAdapter
Imports System.IO
Imports System.Reflection
Imports System.Text
Namespace AdvancedAPI
Partial Public Class mainForm
Inherits System.Windows.Forms.Form
Private WithEvents button1 As System.Windows.Forms.Button
Private saveFileDialog1 As System.Windows.Forms.SaveFileDialog
Private label1 As System.Windows.Forms.Label
''' <summary>
''' Required designer variable.
''' </summary>
Private components As System.ComponentModel.Container = 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.button1 = New System.Windows.Forms.Button()
Me.saveFileDialog1 = New System.Windows.Forms.SaveFileDialog()
Me.label1 = New System.Windows.Forms.Label()
Me.cbXlsxTemplate = New System.Windows.Forms.CheckBox()
Me.SuspendLayout()
'
' button1
'
Me.button1.Anchor = System.Windows.Forms.AnchorStyles.Bottom
Me.button1.Location = New System.Drawing.Point(136, 160)
Me.button1.Name = "button1"
Me.button1.Size = New System.Drawing.Size(75, 23)
Me.button1.TabIndex = 0
Me.button1.Text = "GO!"
' Me.button1.Click += New System.EventHandler(Me.button1_Click)
'
' saveFileDialog1
'
Me.saveFileDialog1.Filter = "Excel Files (97-2003)|*.xls|Excel Files (2007 or newer)|*.xlsm"
Me.saveFileDialog1.RestoreDirectory = True
'
' label1
'
Me.label1.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.label1.BackColor = System.Drawing.Color.FromArgb((CInt((CByte(255)))), (CInt((CByte(255)))), (CInt((CByte(192)))))
Me.label1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
Me.label1.Location = New System.Drawing.Point(16, 16)
Me.label1.Name = "label1"
Me.label1.Size = New System.Drawing.Size(320, 90)
Me.label1.TabIndex = 1
Me.label1.Text = resources.GetString("label1.Text")
'
' cbXlsxTemplate
'
Me.cbXlsxTemplate.AutoSize = True
Me.cbXlsxTemplate.Location = New System.Drawing.Point(16, 120)
Me.cbXlsxTemplate.Name = "cbXlsxTemplate"
Me.cbXlsxTemplate.Size = New System.Drawing.Size(108, 17)
Me.cbXlsxTemplate.TabIndex = 2
Me.cbXlsxTemplate.Text = "Use xlsx template"
Me.cbXlsxTemplate.UseVisualStyleBackColor = True
'
' mainForm
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(344, 197)
Me.Controls.Add(Me.cbXlsxTemplate)
Me.Controls.Add(Me.label1)
Me.Controls.Add(Me.button1)
Me.Name = "mainForm"
Me.Text = "Advanced API"
Me.ResumeLayout(False)
Me.PerformLayout()
End Sub
#End Region
Private cbXlsxTemplate As CheckBox
End Class
End Namespace
Form1.vb
Imports System.Collections
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports System.IO
Imports System.Reflection
Imports System.Text
Namespace AdvancedAPI
''' <summary>
''' A demo on creating a file using more advanced features.
''' </summary>
Partial Public Class mainForm
Inherits System.Windows.Forms.Form
Public Sub New()
InitializeComponent()
End Sub
Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click
Dim Xls As ExcelFile = New XlsFile(True)
AddData(Xls)
NormalOpen(Xls)
End Sub
''' <summary>
''' We will use this path to find the template.xls. Code is a little complex because it has to run in mono.
''' </summary>
Private ReadOnly Property PathToExe() As String
Get
Return Path.Combine(Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), ".."), "..") & Path.DirectorySeparatorChar
End Get
End Property
'some silly data to fill in the cells. A real app would read this from somewhere else.
Private Country() As String = { "USA", "Canada", "Spain", "France", "United Kingdom", "Australia", "Brazil", "Unknown" }
Private DataRows As Integer = 100
''' <summary>
''' Will return a list of countries separated by Character(0) so it can be used as input for a built in list.
''' </summary>
''' <returns></returns>
Private Function GetCountryList() As String
Dim sb As New StringBuilder()
Dim sep As String = ""
For Each c As String In Country
sb.Append(sep)
sb.Append(c)
sep = vbNullChar 'not very efficient method to concat, but good enough for this demo.
Next c
Return sb.ToString()
End Function
Private Sub AddChart(ByVal DataCell As TXlsNamedRange, ByVal Xls As ExcelFile)
'Find the cell where the cart will go.
Dim ChartRange As TXlsNamedRange = Xls.GetNamedRange("ChartData", -1)
'Insert cells to expand the range for the chart. It already has 2 rows, so we need to insert Country.Length - 2
'Note also that we insert after ChartRange.Top, so the chart is updates with the new range.
Xls.InsertAndCopyRange(New TXlsCellRange(ChartRange.Top, ChartRange.Left, ChartRange.Top, ChartRange.Left + 1), ChartRange.Top + 1, ChartRange.Left, Country.Length - 2, TFlxInsertMode.ShiftRangeDown) 'we use shiftrangedown so not all the row goes down and the chart stays in place.
'Get the cell addresses of the data range.
Dim FirstCell As New TCellAddress(DataCell.Top, DataCell.Left)
Dim SecondCell As New TCellAddress(DataCell.Top + DataRows, DataCell.Left + 1)
Dim FirstSumCell As New TCellAddress(DataCell.Top, DataCell.Left + 1)
'Fill a table with the data to be used in the chart.
For r As Integer = ChartRange.Top To ChartRange.Top + Country.Length - 1
Xls.SetCellValue(r, ChartRange.Left, Country(r - ChartRange.Top))
Xls.SetCellValue(r, ChartRange.Left + 1, New TFormula("=SUMIF(" & FirstCell.CellRef & ":" & SecondCell.CellRef & ",""" & Country(r - ChartRange.Top) & """, " & FirstSumCell.CellRef & ":" & SecondCell.CellRef & ")"))
Next r
End Sub
Private Sub AddData(ByVal Xls As ExcelFile)
Dim TemplateFile As String = "template.xls"
If cbXlsxTemplate.Checked Then
If Not XlsFile.SupportsXlsx Then
Throw New Exception("Xlsx files are not supported in this version of the .NET framework")
End If
TemplateFile = "template.xlsm"
End If
' Open an existing file to be used as template. In this example this file has
' little data, in a real situation it should have as much as possible. (Or even better, be a report)
Xls.Open(Path.Combine(PathToExe, TemplateFile))
'Find the cell where we want to fill the data. In this case, we have created a named range "data" so the address
'is not hardcoded here.
Dim DataCell As TXlsNamedRange = Xls.GetNamedRange("Data", -1)
'Add a chart with totals
AddChart(DataCell, Xls)
'Note that "DataCell" will change because we inserted rows above it when creating the chart. But we will keep using the old one.
'Add the captions. This should probably go into the template, but in a dynamic environment it might go here.
Xls.SetCellValue(DataCell.Top - 1, DataCell.Left, "Country")
Xls.SetCellValue(DataCell.Top - 1, DataCell.Left + 1, "Quantity")
'Add a rectangle around the cells
Dim ApplyFormat As New TFlxApplyFormat()
ApplyFormat.SetAllMembers(False)
ApplyFormat.Borders.SetAllMembers(True) 'We will only apply the borders to the existing cell formats
Dim fmt As TFlxFormat = Xls.GetDefaultFormat
fmt.Borders.Left.Style = TFlxBorderStyle.Double
fmt.Borders.Left.Color = Color.Black
fmt.Borders.Right.Style = TFlxBorderStyle.Double
fmt.Borders.Right.Color = Color.Black
fmt.Borders.Top.Style = TFlxBorderStyle.Double
fmt.Borders.Top.Color = Color.Black
fmt.Borders.Bottom.Style = TFlxBorderStyle.Double
fmt.Borders.Bottom.Color = Color.Black
Xls.SetCellFormat(DataCell.Top - 1, DataCell.Left, DataCell.Top, DataCell.Left + 1, fmt, ApplyFormat, True) 'Set last parameter to true so it draws a box.
'Freeze panes
Xls.FreezePanes(New TCellAddress(DataCell.Top, 1))
Dim Rnd As New Random()
'Fill the data
Dim z As Integer = 0
Dim OutlineLevel As Integer = 0
For r As Integer = 0 To DataRows
'Fill the values.
Xls.SetCellValue(DataCell.Top + r, DataCell.Left, Country(z Mod Country.Length)) 'For non C# users, "%" means "mod" or modulus in other languages. It is the rest of the integer division.
Xls.SetCellValue(DataCell.Top + r, DataCell.Left + 1, Rnd.Next(1000))
'Add the country to the outline
Xls.SetRowOutlineLevel(DataCell.Top + r, OutlineLevel)
'increment the country randomly
If Rnd.Next(3) = 0 Then
z += 1
OutlineLevel = 0 'Break the group and create a new one.
Else
OutlineLevel = 1
End If
Next r
'Make the "+" signs of the outline appear at the top.
Xls.OutlineSummaryRowsBelowDetail = False
'Collapse the outline to the first level.
Xls.CollapseOutlineRows(1, TCollapseChildrenMode.Collapsed)
'Add Data Validation for the first column, it must be a country.
Dim dv As New TDataValidationInfo(TDataValidationDataType.List, TDataValidationConditionType.Between, "=""" & GetCountryList() & """", Nothing, False, True, True, True, "Unknown country", "Please make sure that the country is in the list", False, Nothing, Nothing, TDataValidationIcon.Stop) 'We will use the stop icon so no invalid input is permitted. - We will not use an input box, so this is false and the 2 next entries are null - Note that as we entered the data directly in FirstFormula, we need to set this to true - no need for a second formula, not used in List - We could have used a range of cells here with the values (like "=C1..C4") Instead, we directly entered the list in the formula. - This parameter does not matter since it is a list. It will not be used. - We will use a built in list.
Xls.AddDataValidation(New TXlsCellRange(DataCell.Top, DataCell.Left, DataCell.Top + DataRows, DataCell.Left), dv)
'Add Data Validation for the second column, it must be an integer between 0 and 1000.
dv = New TDataValidationInfo(TDataValidationDataType.WholeNumber, TDataValidationConditionType.Between, "=0", "=1000", False, False, False, True, "Invalid Quantity", Nothing, True, "Quantity:", "Please enter a quantity between 0 and 1000", TDataValidationIcon.Stop) 'We will use the stop icon so no invalid input is permitted. - We will leave the default error message. - Second formula is the second part. - First formula marks the first part of the "between" condition. - We will request a number.
Xls.AddDataValidation(New TXlsCellRange(DataCell.Top, DataCell.Left + 1, DataCell.Top + DataRows, DataCell.Left + 1), dv)
'Search country "Unknown" and replace it by "no".
'This does not make any sense here (we could just have entered "no" to begin)
'but it shows how to do it when modifying an existing file
Xls.Replace("Unknown", "no", TXlsCellRange.FullRange(), True, False, True)
'Autofit the rows. As we keep the row height automatic this will not show when opening in Excel, but will work when directly printing from FlexCel.
Xls.AutofitRowsOnWorkbook(False, True, 1)
Xls.Recalc() 'Calculate the SUMIF formulas so we can sort by them. Note that FlexCel automatically recalculates before saving,
'but in this case we haven't saved yet, so the sheet is not recalculated. You do not normally need to call Recalc directly.
'Sort the data. As in the case with replace, this does not make much sense. We could have entered the data sorted to begin
'But it shows how you can use the feature.
'Find the cell where the chart goes.
Dim ChartRange As TXlsNamedRange = Xls.GetNamedRange("ChartData", -1)
Xls.Sort(New TXlsCellRange(ChartRange.Top, ChartRange.Left, ChartRange.Top + Country.Length, ChartRange.Left + 1), True, New Integer() { 2 }, New TSortOrder() { TSortOrder.Descending }, Nothing)
'Protect the Sheet
Dim Sp As New TSheetProtectionOptions(False) 'Create default protection options that allows everything.
Sp.InsertColumns = False 'Restrict inserting columns.
Xls.Protection.SetSheetProtection("flexcel", Sp)
'Set a modify password. Note that this does *not* encrypt the file.
Xls.Protection.SetModifyPassword("flexcel", True, "flexcel")
Xls.Protection.OpenPassword = "flexcel" 'OpenPasword is the only password that will actually encrypt the file, so you will not be able to open it with flexcel if you do not know the password.
'Select cell A1
Xls.SelectCell(1, 1, True)
End Sub
'This is part of an advanced feature (showing the user using a file) , you do not need to use
'this method on normal places.
Private Function GetLockingUser(ByVal FileName As String) As String
Try
Dim xerr As New XlsFile()
xerr.Open(FileName)
Return " - File might be in use by: " & xerr.Protection.WriteAccess
Catch
Return String.Empty
End Try
End Function
Private Sub NormalOpen(ByVal Xls As ExcelFile)
If cbXlsxTemplate.Checked Then
saveFileDialog1.FilterIndex = 1
Else
saveFileDialog1.FilterIndex = 0
End If
If saveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
If (Not XlsFile.SupportsXlsx) AndAlso Path.GetExtension(saveFileDialog1.FileName) = ".xlsm" Then
Throw New Exception("Xlsx files are not supported in this version of the .NET framework")
End If
Try
Xls.Save(saveFileDialog1.FileName)
Catch ex As IOException 'This is not really needed, just to show the username of the user locking the file.
Throw New IOException(ex.Message & GetLockingUser(saveFileDialog1.FileName), ex)
End Try
If MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) = System.Windows.Forms.DialogResult.Yes Then
Process.Start(saveFileDialog1.FileName)
End If
End If
End Sub
End Class
End Namespace
Program.vb
Namespace AdvancedAPI
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