Consolidating files (VB.Net / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\vb\VS2022\netframework\10.API\80.Consolidating Files and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/vb/VS2022/netframework/Modules/10.API/80.Consolidating Files
Overview
The FlexCel API is oriented to modifying files, instead of reading and creating files as different things. So, some most important commands on it are ExcelFile.InsertAndCopyRange and ExcelFile.DeleteRange, that copy and delete ranges on existing sheets.
This is a real-world example on how you can use ExcelFile.InsertAndCopyRange to copy the first sheet of many different Excel files into one big file.
Concepts
You can use ExcelFile.InsertAndCopyRange and/or ExcelFile.InsertAndCopySheets to copy ranges across different files. Even when it is not as complete as copying from the same file, it does copy most of the things.
ExcelFile.InsertAndCopyRange behaves the same way as Excel. That is, if you copy whole rows, the row height and format will be copied, not only the values. The same happens with columns, only when copying full columns the format and width will be copied to the destination. On this demo, we want to copy all Column and Row format, so we have to select the whole sheet. If we selected a smaller range, say (1,1,65535,255) instead of (1,1,65536,256) no full column or full row would be selected and not column or row format would be copied.
If the sheets you are copying have formulas or names with references to other files or sheets, you might not get the expected results. You could use ExcelFile.ConvertFormulasToValues and ExcelFile.ConvertFormulasToValues
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
Namespace ConsolidatingFiles
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
Private openFileDialog1 As System.Windows.Forms.OpenFileDialog
Private label2 As System.Windows.Forms.Label
Private cbOnlyData As System.Windows.Forms.CheckBox
''' <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()
Me.button1 = New System.Windows.Forms.Button()
Me.saveFileDialog1 = New System.Windows.Forms.SaveFileDialog()
Me.label1 = New System.Windows.Forms.Label()
Me.openFileDialog1 = New System.Windows.Forms.OpenFileDialog()
Me.label2 = New System.Windows.Forms.Label()
Me.cbOnlyData = New System.Windows.Forms.CheckBox()
Me.SuspendLayout()
'
' button1
'
Me.button1.Anchor = System.Windows.Forms.AnchorStyles.Bottom
Me.button1.Location = New System.Drawing.Point(180, 152)
Me.button1.Name = "button1"
Me.button1.TabIndex = 0
Me.button1.Text = "GO!"
' Me.button1.Click += New System.EventHandler(Me.button1_Click)
'
' saveFileDialog1
'
Me.saveFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All files|*.*"
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((CByte(255)), (CByte(255)), (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(408, 32)
Me.label1.TabIndex = 1
Me.label1.Text = "A demo on how to consolidate several files into one."
'
' openFileDialog1
'
Me.openFileDialog1.DefaultExt = "xls"
Me.openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All files|*.*"
Me.openFileDialog1.Multiselect = True
Me.openFileDialog1.Title = "Select ALL the files you want to consolidate."
'
' label2
'
Me.label2.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.label2.BackColor = System.Drawing.Color.White
Me.label2.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
Me.label2.Location = New System.Drawing.Point(16, 64)
Me.label2.Name = "label2"
Me.label2.Size = New System.Drawing.Size(408, 32)
Me.label2.TabIndex = 2
Me.label2.Text = "After pressing the button, multi-select all the files you want with ctrl and shif" & "t."
'
' cbOnlyData
'
Me.cbOnlyData.Location = New System.Drawing.Point(24, 112)
Me.cbOnlyData.Name = "cbOnlyData"
Me.cbOnlyData.Size = New System.Drawing.Size(344, 24)
Me.cbOnlyData.TabIndex = 3
Me.cbOnlyData.Text = "Copy only data. (dont copy margins zoom, etc)"
'
' mainForm
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(432, 197)
Me.Controls.Add(Me.cbOnlyData)
Me.Controls.Add(Me.label2)
Me.Controls.Add(Me.label1)
Me.Controls.Add(Me.button1)
Me.Name = "mainForm"
Me.Text = "Form1"
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
Namespace ConsolidatingFiles
''' <summary>
''' A demo on how to copy many sheets from different files into one file.
''' </summary>
Partial Public Class mainForm
Inherits System.Windows.Forms.Form
Public Sub New()
InitializeComponent()
End Sub
''' <summary>
''' This is the method that will be called by the ASP.NET front end. It returns an array of bytes
''' with the report data, so the ASP.NET application can stream it to the client.
''' </summary>
''' <param name="fileDatas"></param>
''' <param name="fileNames"></param>
''' <param name="OnlyData"></param>
''' <returns>The generated file as a byte array.</returns>
Public Function WebRun(ByVal fileDatas() As Stream, ByVal fileNames() As String, ByVal OnlyData As Boolean) As Byte()
If fileNames.Length <= 0 Then
Throw New ApplicationException("You must select at least one file")
End If
Dim XlsOut As ExcelFile = Consolidate(fileDatas, fileNames, OnlyData)
Using OutStream As New MemoryStream()
XlsOut.Save(OutStream)
Return OutStream.ToArray()
End Using
End Function
Private Function Consolidate(ByVal fileDatas() As Stream, ByVal fileNames() As String, ByVal OnlyData As Boolean) As ExcelFile
Dim XlsIn As ExcelFile = New XlsFile()
Dim XlsOut As ExcelFile = New XlsFile(True)
XlsOut.NewFile(1, TExcelFileFormat.v2019)
If fileNames.Length > 1 AndAlso cbOnlyData.Checked Then
XlsOut.InsertAndCopySheets(1, 2, fileNames.Length - 1)
End If
For i As Integer = 0 To fileNames.Length - 1
If fileDatas IsNot Nothing Then
XlsIn.Open(fileDatas(i))
Else
XlsIn.Open(fileNames(i))
End If
XlsIn.ConvertFormulasToValues(True) 'If there is any formula referring to other sheet, convert it to value.
'We could also call an overloaded version of InsertAndCopySheets() that
'copies many sheets at the same time, so references are kept.
XlsOut.ActiveSheet = i + 1
If OnlyData Then
XlsOut.InsertAndCopyRange(TXlsCellRange.FullRange(), 1, 1, 1, TFlxInsertMode.ShiftRangeDown, TRangeCopyMode.All, XlsIn, 1)
Else
XlsOut.InsertAndCopySheets(1, XlsOut.ActiveSheet, 1, XlsIn)
End If
'Change sheet name.
Dim s As String = Path.GetFileName(fileNames(i))
If s.Length > 32 Then
XlsOut.SheetName = s.Substring(0, 29) & "..."
Else
XlsOut.SheetName = s
End If
Next i
If Not cbOnlyData.Checked Then
XlsOut.ActiveSheet = XlsOut.SheetCount
XlsOut.DeleteSheet(1) 'Remove the empty sheet that came with the workbook.
End If
XlsOut.ActiveSheet = 1
Return XlsOut
End Function
Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click
If openFileDialog1.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then
Return
End If
Dim fileNames() As String = openFileDialog1.FileNames
If fileNames.Length <= 0 Then
MessageBox.Show("You must select at least one file")
Return
End If
Dim XlsOut As ExcelFile = Consolidate(Nothing, fileNames, cbOnlyData.Checked)
If saveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
XlsOut.Save(saveFileDialog1.FileName)
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 ConsolidatingFiles
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