Consolidating files (C# / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\netframework\10.API\80.Consolidating Files and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/csharp/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.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 - 2025 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.24.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 FlexCel.Core;
using FlexCel.XlsAdapter;
using System.IO;
using System.Diagnostics;
using System.Reflection;
namespace ConsolidatingFiles
{
/// <summary>
/// A demo on how to copy many sheets from different files into one file.
/// </summary>
public partial class mainForm: System.Windows.Forms.Form
{
public mainForm()
{
InitializeComponent();
}
/// <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 byte[] WebRun(Stream[] fileDatas, string[] fileNames, bool OnlyData)
{
if (fileNames.Length <= 0)
{
throw new ApplicationException("You must select at least one file");
}
ExcelFile XlsOut = Consolidate(fileDatas, fileNames, OnlyData);
using (MemoryStream OutStream = new MemoryStream())
{
XlsOut.Save(OutStream);
return OutStream.ToArray();
}
}
private ExcelFile Consolidate(Stream[] fileDatas, string[] fileNames, bool OnlyData)
{
ExcelFile XlsIn = new XlsFile();
ExcelFile XlsOut = new XlsFile(true);
XlsOut.NewFile(1, TExcelFileFormat.v2019);
if (fileNames.Length > 1 && cbOnlyData.Checked) XlsOut.InsertAndCopySheets(1, 2, fileNames.Length - 1);
for (int i = 0; i < fileNames.Length; i++)
{
if (fileDatas != null) XlsIn.Open(fileDatas[i]);
else XlsIn.Open(fileNames[i]);
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)
XlsOut.InsertAndCopyRange(TXlsCellRange.FullRange(), 1, 1, 1, TFlxInsertMode.ShiftRangeDown, TRangeCopyMode.All, XlsIn, 1);
else
{
XlsOut.InsertAndCopySheets(1, XlsOut.ActiveSheet, 1, XlsIn);
}
//Change sheet name.
string s = Path.GetFileName(fileNames[i]);
if (s.Length > 32) XlsOut.SheetName = s.Substring(0, 29) + "...";
else XlsOut.SheetName = s;
}
if (!cbOnlyData.Checked)
{
XlsOut.ActiveSheet = XlsOut.SheetCount;
XlsOut.DeleteSheet(1); //Remove the empty sheet that came with the workbook.
}
XlsOut.ActiveSheet = 1;
return XlsOut;
}
private void button1_Click(object sender, System.EventArgs e)
{
if (openFileDialog1.ShowDialog() != DialogResult.OK) return;
string[] fileNames = openFileDialog1.FileNames;
if (fileNames.Length <= 0)
{
MessageBox.Show("You must select at least one file");
return;
}
ExcelFile XlsOut = Consolidate(null, fileNames, cbOnlyData.Checked);
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
XlsOut.Save(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();
}
}
}
}
}
}
Form1.Designer.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using System.IO;
using System.Diagnostics;
using System.Reflection;
namespace ConsolidatingFiles
{
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.OpenFileDialog openFileDialog1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.CheckBox cbOnlyData;
/// <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.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
this.label2 = new System.Windows.Forms.Label();
this.cbOnlyData = new System.Windows.Forms.CheckBox();
this.SuspendLayout();
//
// button1
//
this.button1.Anchor = System.Windows.Forms.AnchorStyles.Bottom;
this.button1.Location = new System.Drawing.Point(180, 152);
this.button1.Name = "button1";
this.button1.TabIndex = 0;
this.button1.Text = "GO!";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// saveFileDialog1
//
this.saveFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All files|*.*";
this.saveFileDialog1.RestoreDirectory = true;
//
// label1
//
this.label1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.label1.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(255)), ((System.Byte)(192)));
this.label1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
this.label1.Location = new System.Drawing.Point(16, 16);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(408, 32);
this.label1.TabIndex = 1;
this.label1.Text = "A demo on how to consolidate several files into one.";
//
// openFileDialog1
//
this.openFileDialog1.DefaultExt = "xls";
this.openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All files|*.*";
this.openFileDialog1.Multiselect = true;
this.openFileDialog1.Title = "Select ALL the files you want to consolidate.";
//
// label2
//
this.label2.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.label2.BackColor = System.Drawing.Color.White;
this.label2.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
this.label2.Location = new System.Drawing.Point(16, 64);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(408, 32);
this.label2.TabIndex = 2;
this.label2.Text = "After pressing the button, multi-select all the files you want with ctrl and shif" +
"t.";
//
// cbOnlyData
//
this.cbOnlyData.Location = new System.Drawing.Point(24, 112);
this.cbOnlyData.Name = "cbOnlyData";
this.cbOnlyData.Size = new System.Drawing.Size(344, 24);
this.cbOnlyData.TabIndex = 3;
this.cbOnlyData.Text = "Copy only data. (dont copy margins zoom, etc)";
//
// mainForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(432, 197);
this.Controls.Add(this.cbOnlyData);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.button1);
this.Name = "mainForm";
this.Text = "Form1";
this.ResumeLayout(false);
}
#endregion
}
}
Program.cs
using System;
using System.Windows.Forms;
namespace ConsolidatingFiles
{
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());
}
}
}