TWorkspace Class

This class links together a group of spreadsheets, so you can recalculate among linked spreadsheets. In order to use it, just define an object of this class and add all the files you need for the linked recalculation. If you don't know in advance which files you will need, you can use the LoadLinkedFile event.

Note that whenever you recalculate any file in the workspace, all files will be recalculated, so you don't need to calculate them twice.

Remarks

Files are case-insensitive, even if running in Linux. "a.xls" is the same as "A.XLS"

Syntax

Unit: FlexCel.Core

TWorkspace = class(TFlexCelObject);

Constructors

Name	Description
Create	Creates a new workspace.

Methods

Name	Description
OnLoadLinkedFile	Replace this event when creating a custom descendant of TWorkspace. See also LoadLinkedFile
Add	Adds a file to the workspace. Whenever you recalculate any file in this workspace, all linked files will be recalculated too. Note that you can't add two files with the same name or same reference twice to this collection.
GetFile	Returns the file at index.
Clear	Removes all files from the workspace.
Recalc	Use this method to force a recalculation of all the spreadsheets in the workspace. This is the same as calling Recalc() in any of the files in the workspace.
RecalcAndVerify	This method will do the same as TExcelFile.​Recalc​AndVerify, but for a workspace of files.
GetEnumerator	Returns an enumerator with all the files in the Workspace.

Properties

Name	Description
CellStackTrace​MaxSize	Defines what is the maximum number of entries returned in the stack trace when calling RecalcAndVerify. In order to keep the stack trace not too big, this number is limited, but if you need a bigger stack trace to see the full loop of cells, you can increase this number.
Count	Number of linked files in this workspace.
Item[const fileName]	Returns the Excel file with the given name. To get the file at a given position, use GetFile

Events

Name	Description
LoadLinkedFile	Use this event to load files to recalculate on demand, if you don't know a priori which linked files you need. Note that this event will add the new file to the workspace. It will only be called once for each file, even if the file is used many times.

Examples

If you have 3 files, xls1, xls2 and xls3, you can recalculate them together with the following code:

var
  work: TWorkspace;
begin
  //The Workspace will own the TXlsFile objects,
  //so we won't need to free them.
  //If we set the parameter to false,
  //we would have to manually free the TXlsFile objects.
  work := TWorkspace.Create(true);
  try
    work.Add('xls1', TXlsFile.Create('File1.xlsx'));
    work.Add('xls2', TXlsFile.Create('File2.xlsx'));
    work.Add('xls3', TXlsFile.Create('File3.xlsx'));

   //Either work.Recalc, xls1.Recalc, xls2.Recalc or xls3.Recalc will recalculate all the files in the workspace.
    work.Recalc(true);
  finally
    work.Free;
  end;
end;