Consolidating files (Delphi)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\10.API\80.Consolidating Files and also at https://github.com/tmssoftware/TMS-FlexCel.VCL-demos/tree/master/Delphi/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 TExcelFile.InsertAndCopyRange and TExcelFile.DeleteRange, that copy and delete ranges on existing sheets.
This is a real-world example on how you can use TExcelFile.InsertAndCopyRange to copy the first sheet of many different Excel files into one big file.
Concepts
You can use TExcelFile.InsertAndCopyRange and/or TExcelFile.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.
TExcelFile.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 TExcelFile.ConvertFormulasToValues and TExcelFile.ConvertFormulasToValues
Files
UConsolidatingFiles.pas
unit UConsolidatingFiles;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, FlexCel.VCLSupport, FlexCel.Core, FlexCel.XlsAdapter,
{$if CompilerVersion >= 23.0} System.UITypes, {$IFEND}
ShellAPI;
type
TFConsolidatingFiles = class(TForm)
Memo1: TMemo;
btnGo: TButton;
SaveDialog: TSaveDialog;
cbOnlyData: TCheckBox;
OpenDialog: TOpenDialog;
procedure btnGoClick(Sender: TObject);
private
function Consolidate(const fileNames: TArray<string>;
const OnlyData: Boolean): TExcelFile;
end;
var
FConsolidatingFiles: TFConsolidatingFiles;
implementation
uses IOUtils;
{$R *.dfm}
procedure TFConsolidatingFiles.btnGoClick(Sender: TObject);
var
FileNames: TArray<String>;
XlsOut: TExcelFile;
begin
if not OpenDialog.Execute then exit;
FileNames := OpenDialog.Files.ToStringArray;
if Length(FileNames) <= 0 then
begin
ShowMessage('You must select at least one file');
exit;
end;
XlsOut := Consolidate(FileNames, cbOnlyData.Checked);
try
if SaveDialog.Execute then
begin
XlsOut.Save(SaveDialog.FileName);
if MessageDlg('Do you want to open the generated file?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
begin
ShellExecute(0, 'open', PCHAR(SaveDialog.FileName), nil, nil, SW_SHOWNORMAL);
end;
end;
finally
XlsOut.Free;
end;
end;
function TFConsolidatingFiles.Consolidate(const fileNames: TArray<string>; const OnlyData: Boolean): TExcelFile;
var
XlsIn: TExcelFile;
XlsOut: TExcelFile;
i: Int32;
s: string;
begin
XlsIn := TXlsFile.Create;
try
XlsOut := TXlsFile.Create(true);
try
XlsOut.NewFile(1, TExcelFileFormat.v2019);
if (Length(fileNames) > 1) and cbOnlyData.Checked then
XlsOut.InsertAndCopySheets(1, 2, Length(fileNames) - 1);
for i := 0 to Length(fileNames) - 1 do
begin
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 then
XlsOut.InsertAndCopyRange(TXlsCellRange.FullRange, 1, 1, 1, TFlxInsertMode.ShiftRangeDown, TRangeCopyMode.All, XlsIn, 1) else
begin
XlsOut.InsertAndCopySheets(1, XlsOut.ActiveSheet, 1, XlsIn);
end;
s := TPath.GetFileName(fileNames[i]); //Change sheet name.
if Length(s) > 32 then
XlsOut.SheetName := System.Copy(s, 1, 29) + '...' else
XlsOut.SheetName := s;
end;
if not cbOnlyData.Checked then
begin
XlsOut.ActiveSheet := XlsOut.SheetCount;
XlsOut.DeleteSheet(1); //Remove the empty sheet that came with the workbook.
end;
XlsOut.ActiveSheet := 1;
except
XlsOut.Free;
raise;
end;
finally
XlsIn.Free;
end;
Result := XlsOut;
end;
end.