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.