A Pivot table can remain in an XLSX file even when its cached source records are absent. For a prepared external relational cache with SaveData=False, HotXLS can omit the records part from the saved package while retaining the live values in the open workbook
A Pivot table is more than one XML file
An XLSX workbook is a ZIP package, and a Pivot report spans several connected parts. The table definition describes the layout and fields shown by the report; the cache definition describes the source and cached field metadata; a records part carries cached data when those records are saved. Relationships link those pieces. The table definition can therefore remain present when the records part is omitted. Connection metadata and the setting controlling stored records are also distinct pieces of information. Opening the package makes the distinction visible: look for the definition, its relationships and the records part rather than expecting a single XML file to contain the whole report
More than one Pivot table can refer to the same cache. A summary on one worksheet and a regional view on another may consequently share the setting that determines whether their cache records are written. Before you change it, identify the cache through the table the user selected and check which other tables use that cache. Make this relationship visible in your application's workflow: an operation initiated from one report can affect another report that depends on the same data. A workbook may also contain unrelated caches, so choosing the first cache merely because it appears first in a collection is a poor substitute for following the table's reference
What does saveData say?
The cache definition can include a saveData attribute. This simplified fragment shows the setting; a usable definition also needs its source, fields, namespaces and package relationships
<pivotCacheDefinition saveData="0">
<!-- cache source and fields -->
</pivotCacheDefinition>The value 0 means false. It controls whether cache data is saved with the workbook; it neither removes the table definition nor retrieves new database rows. For the prepared populated external relational cache described here, HotXLS omits the records part when SaveData is false, removes the associated relationship, records relationship ID and content-type entry, and writes a serialized recordCount of zero. The open cache can still hold its current records. An XLSX package is a graph of parts and references, so removing only the records bytes while leaving references to them would not describe the same saved package. Refresh credentials, connection availability and the recipient's environment remain decisions for the delivery workflow
Set the cache property in Delphi
The first Delphi routine receives an open workbook and a cache that the application has already selected and prepared. It checks the source type, sets the property and saves a copy. In the calling workflow, follow the relevant Pivot table to its cache and complete the provider refresh before using this routine
uses
SysUtils, lxHandleX, lxPivot, lxConnections;
procedure SaveWithoutCacheRecords(Book: TXLSXWorkbook;
Cache: TXLSPivotCache; const OutputFile: string);
begin
if (Book = nil) or (Cache = nil) then
raise Exception.Create('Workbook and cache are required');
if (Cache.SourceDataType <> xlPivotCacheSourceExternal) or
(Cache.ExternalSourceKind <> xlpesRelational) then
raise Exception.Create('Expected an external relational cache');
if Cache.FromRawBlobs then
raise Exception.Create('Prepare the imported cache before saving');
Cache.SaveData := False;
Book.SaveAs(OutputFile);
end;The cache passed to the routine must belong to the open workbook. An imported file can contain several caches and can share one among multiple tables; an unchanged imported cache may also replay its original package parts. Select the intended external relational cache deliberately rather than assuming that toggling an arbitrary first item exercises the prepared-cache workflow. Refresh is an explicit step. Opening or saving the workbook does not itself contact a provider, and after reopening a file saved without those records, the application needs to refresh with a provider before using them again. The following routine takes the selected cache ID and a provider owned by the caller, then calls the provider overload of RefreshPivotCache. Treat a refused or canceled refresh as its own outcome so an empty report is not mistaken for successfully retrieved data
procedure RefreshExternalCache(Book: TXLSXWorkbook;
Cache: TXLSPivotCache; Provider: TXLSQueryTableProvider);
begin
if (Book = nil) or (Cache = nil) or (Provider = nil) then
raise Exception.Create('Workbook, cache and provider are required');
if Book.RefreshPivotCache(Cache.CacheId, Provider) <> 1 then
raise Exception.Create('Pivot cache refresh did not complete');
end;Add lxConnections to the uses clause shown in the first example. The provider must be configured for the actual connection, and the cache should be ready for the save workflow before its records are omitted
What remains in memory?
The saved package and the open workbook answer different questions. HotXLS can keep the populated cache's live values and shared items available to the running application while preparing an output file without the records part. A missing records part also does not prove that the workbook contains no source information: cache definitions, shared items, connection metadata or other content can retain information. If you need to remove sensitive data, apply a package-wide sanitization policy and examine the entire output. The SaveData setting determines how the cache records are persisted; it should not be treated as a complete data-scrubbing operation
Verify the package, then verify the workflow
Use a workbook with two populated caches: the prepared external relational cache whose records should be omitted, and an unrelated cache whose records should remain. Set SaveData to false for the selected cache and save a copy. In the ZIP package, follow its cache definition and relationship files, locate the records part, and check [Content_Types].xml. The selected cache should have the records part and its associated references removed as described above. The unrelated cache should still contain its expected records. That second cache makes the scope of the operation visible; a workbook with only one cache cannot show whether another report's records were inadvertently affected
A valid package is only half of the delivery decision. Open the saved workbook in the spreadsheet application you expect the recipient to use and exercise the intended refresh with the source available. The connection must be reachable and the delivery account must have the required credentials. A successful refresh on a developer workstation with an existing login does not establish that the recipient can obtain the data. Test the actual account and network path, including the behavior when the source cannot be reached. The package tells us what was saved; the refresh operation shows whether the receiving environment can retrieve the rows the report needs
Check the offline case before you distribute the file. A workbook opened on a laptop away from its external source cannot rely on an immediate refresh, so decide what the application should show when the records were omitted and explain that behavior to its users. A cached report and a report that must refresh on opening lead to different delivery expectations. We would decide whether the recipient needs the saved data before setting the persistence flag, then test the intended behavior with the actual connection unavailable as well as available
Choose the setting for the report's purpose
Choose the setting for the way you intend the report to be used. An offline report needs enough data to remain useful without its source, while a workbook delivered into an environment with an authenticated refresh path can omit records when that is the designed workflow. Make that decision before optimizing file size: a smaller workbook is of little use to a recipient who cannot refresh it. Account for shared caches and verify the file actually delivered to the recipient. For related formats, the Pivot records guide discusses BIFF8, and slicer cache round trips covers cache identity. The examples use the HotXLS Delphi Component