HotXLS Docs
Home Contents XLSX Facade Overview

TXLSXWorkbook class

Top-level container for an Excel 2007+ (.xlsx) workbook. Declared in lxHandleX. Holds a TXLSXSheets collection and provides the SaveAs / Open entry points for writing and reading OOXML archives.

Declaration

type
  TXLSXWorkbook = class
    constructor Create;
    destructor Destroy; override;
    function SaveAs(const FileName: WideString): Integer; overload;
    function SaveAs(const FileName: WideString; FileFormat: TXLSXFileFormat): Integer; overload;
    function SaveAs(Stream: TStream): Integer; overload;
    function SaveAs(Stream: TStream; FileFormat: TXLSXFileFormat): Integer; overload;
    function Open(const FileName: WideString): Integer; overload;
    function Open(const FileName: WideString; const Password: WideString): Integer; overload;
    function Open(Stream: TStream): Integer; overload;
    function SaveAsCSV(const FileName: WideString): Integer; overload;
    function SaveAsCSV(const FileName: WideString; SheetIndex: Integer; Delimiter: WideChar): Integer; overload;
    function SaveAsCSV(Stream: TStream): Integer; overload;
    function SaveAsCSV(Stream: TStream; SheetIndex: Integer; Delimiter: WideChar): Integer; overload;
    function SaveAsHTML(const FileName: WideString): Integer; overload;
    function SaveAsHTML(const FileName: WideString; SheetIndex: Integer): Integer; overload;
    function SaveAsHTML(Stream: TStream): Integer; overload;
    function SaveAsHTML(Stream: TStream; SheetIndex: Integer): Integer; overload;
    function SaveAsRTF(const FileName: WideString): Integer; overload;
    function SaveAsRTF(const FileName: WideString; SheetIndex: Integer): Integer; overload;
    function SaveAsRTF(Stream: TStream): Integer; overload;
    function SaveAsRTF(Stream: TStream; SheetIndex: Integer): Integer; overload;
    // AES encryption (ECMA-376 Standard Encryption) — API surface only;
    // raises EXlsxEncryptionNotImplemented until the algorithm lands.
    function SaveAsEncrypted(const FileName, APassword: WideString): Integer;
    function OpenEncrypted(const FileName, APassword: WideString): Integer;
    function CanReadEncrypted(const FileName: WideString): Boolean;
    // Document properties (docProps/core.xml + docProps/app.xml).
    property Title, Author, Subject, Keywords, Description,
             Category, LastModifiedBy, Company,
             Application, AppVersion: WideString;
    property Created, Modified: TDateTime;
    // Workbook protection (16-bit legacy hash).
    procedure ProtectWorkbook; overload;
    procedure ProtectWorkbook(const APassword: WideString); overload;
    procedure ProtectWorkbook(const APassword: WideString; ALockStructure, ALockWindows: Boolean); overload;
    procedure UnProtectWorkbook;
    property IsWorkbookProtected: Boolean;
    property LockStructure, LockWindows: Boolean;
    // VBA project payload (round-trips byte-for-byte). Workbook is saved
    // with macro-enabled content type when HasVbaProject is True.
    procedure LoadVbaProjectFromFile(const AFileName: WideString);
    procedure ClearVbaProject;
    function HasVbaProject: Boolean;
    property VbaProject: AnsiString;
    // Chart sheet helper — creates a sheet flagged IsChartSheet and
    // seeds Charts[0] with a default full-page anchor.
    function AddChartSheet(const AName: WideString;
      AChartType: TXLSXChartType; const ATitle: WideString): TXLSXWorksheet;
    // Streaming SaveAs path (TXLSWideStringBuilder + one sheet at a time).
    property StreamingWrite: Boolean;
    // Workbook-level collections.
    property ActiveSheet: Integer;
    property Date1904: Boolean;
    property IndexedColor[Index: Integer]: LongWord;
    function  HasCustomIndexedColor(Index: Integer): Boolean;
    function  CustomIndexedColorCount: Integer;
    procedure ResetIndexedColors;
    property Sheets: TXLSXSheets;
    property Fonts: TXLSXFonts;
    property Fills: TXLSXFills;
    property Borders: TXLSXBorders;
    property NumberFormats: TXLSXNumberFormats;
    property Alignments: TXLSXAlignments;
    property DefinedNames: TXLSXDefinedNames;
    property ExternalLinks: TXLSXExternalLinks;
  end;

Members

Sheets Worksheet collection. See TXLSXSheets.
Fonts Workbook-level font palette. Cells pick a font by setting TXLSXCell.FontIndex to a 1-based index into this collection. See TXLSXFont / TXLSXFonts.
Fills Workbook-level fill palette. Cells pick a fill by setting TXLSXCell.FillIndex to a 1-based index into this collection. See TXLSXFill / TXLSXFills.
Borders Workbook-level border palette. Cells pick a border by setting TXLSXCell.BorderIndex to a 1-based index into this collection. See TXLSXBorder / TXLSXBorders.
NumberFormats Workbook-level custom number-format palette. Cells pick a format by setting TXLSXCell.NumberFormatIndex to a 1-based index into this collection. See TXLSXNumberFormat / TXLSXNumberFormats.
DefinedNames Workbook-level named ranges (workbook-scoped or sheet-scoped). See TXLSXDefinedName / TXLSXDefinedNames.
Alignments Workbook-level cell-alignment palette. Cells pick an entry by setting TXLSXCell.AlignmentIndex to a 1-based index into this collection. See TXLSXAlignment.
Document properties Title, Author, Subject, Keywords, Description, Category, LastModifiedBy, Company, Application, AppVersion, Created, Modified. Setting any one of them populates docProps/core.xml + docProps/app.xml on SaveAs (otherwise the workbook keeps the minimal part set).
ProtectWorkbook / UnProtectWorkbook Workbook protection toggle with an optional 16-bit legacy password hash and LockStructure / LockWindows flags. Reflected as <workbookProtection> in xl/workbook.xml.
LoadVbaProjectFromFile / VbaProject / HasVbaProject Round-trips a vbaProject.bin payload byte-for-byte so existing macro projects survive a SaveAs/Open cycle. SaveAs switches to the macro-enabled workbook content type and rels target when HasVbaProject is True. Save the workbook with a .xlsm extension for Excel to enable macros.
AddChartSheet(Name, Type, Title) Creates a worksheet flagged IsChartSheet = True and seeds Charts[0] with a sensible default full-page anchor. SaveAs writes such sheets to xl/chartsheets/sheetN.xml. See TXLSXChart.
StreamingWrite When True, SaveAs uses WriteWorksheetXmlStreaming per sheet — no sheetXmls cache is held, sharedStrings.xml is emitted last after every sheet has populated the SST. Combined with the per-row TXLSWideStringBuilder this keeps memory linear on large sheets. Default is False (existing behavior).
SaveAsEncrypted / OpenEncrypted / CanReadEncrypted Reserved API surface for AES + Compound File encryption. CanReadEncrypted detects the OLE2 magic bytes; OpenEncrypted transparently falls back to plain Open for unencrypted files. Writing an actually- encrypted file or reading one currently raises EXlsxEncryptionNotImplemented.
SaveAs(FileName) Writes the workbook to the given file. Returns 1 on success, negative on failure. If the workbook has no sheets a default Sheet1 is added before writing.
SaveAs(FileName, FileFormat) Same as above with an explicit file format. The only accepted value today is xlsxOpenXMLWorkbook.
SaveAs(Stream) / SaveAs(Stream, FileFormat) Writes the workbook directly into any TStream. Useful for in-memory generation or BLOB fields. Returns 1 on success.
Open(FileName) Reads an OOXML .xlsx archive and replaces the Sheets collection with its contents. Returns 1 on success.
Open(FileName, Password) Reserved for future encrypted XLSX support. Behaves like Open(FileName) today — the password is ignored.
Open(Stream) Reads an OOXML .xlsx archive from a TStream. Returns 1 on success.
SaveAsCSV overloads Four overloads (FileName or Stream × active-sheet default / explicit SheetIndex + Delimiter). Writes the chosen sheet as a UTF-8 CSV with BOM. Date values render as yyyy-mm-dd hh:nn:ss; formula text is written verbatim. Returns 0 on success.
SaveAsHTML overloads Four overloads (FileName or Stream × active-sheet default / explicit SheetIndex). Writes the chosen sheet as a UTF-8 HTML5 file with a single <table>. Merged cells become colspan / rowspan attributes. Basic cell styles (font name/size/bold/italic, color, fill, alignment) are emitted as inline CSS. Theme colors are skipped (no theme1.xml available). Returns 0 on success.
SaveAsRTF overloads Four overloads (FileName or Stream × active-sheet default / explicit SheetIndex). Writes the chosen sheet as an RTF 1.6 document with a plain table. Column widths are derived from ColWidth data (1 char unit ≈ 96 twips; default 809 twips). Per-cell bold / italic / font size are applied; non-ASCII characters are encoded as signed-16-bit \uN? RTF unicode escapes. Merged cells are not spanned. Returns 0 on success.
ActiveSheet 0-based index of the active (selected) sheet. SaveAs writes <workbookView activeTab="N"/> and marks the matching sheet with tabSelected="1".
Date1904 Workbook date base. False (default) = Windows 1900 epoch; True = Mac 1904 epoch (shifts every date serial by 1462 days). Set this before assigning TDateTime cell values so Excel renders the same calendar date.
IndexedColor[Index] ARGB value for the given OOXML indexed-color palette slot (0..63). Reading returns the user override if set, otherwise the built-in OOXML default (XlsxDefaultIndexedPalette). Writing overrides slot N; SaveAs then emits a full <indexedColors> block with all 64 slots.
HasCustomIndexedColor / CustomIndexedColorCount / ResetIndexedColors HasCustomIndexedColor(N) returns True when slot N was explicitly overridden. CustomIndexedColorCount returns the number of overridden slots (0 = no <colors> block emitted by SaveAs). ResetIndexedColors discards every override and restores the default palette.
ExternalLinks Collection of external workbook references. Each TXLSXExternalLink entry holds a Target URL and a SheetNames list. SaveAs emits the <externalReferences> block in xl/workbook.xml and the corresponding xl/externalLinks/ parts. Open round-trips the Target and SheetNames; cached cell values inside <sheetDataSet> are not preserved.

Example

Writing and re-opening a workbook: 
uses lxHandleX;

var
  wb: TXLSXWorkbook;
begin
  wb := TXLSXWorkbook.Create;
  try
    wb.Sheets.Add('Demo').Cells.Item[1, 1].Value := 'Hello';
    wb.SaveAs('demo.xlsx');

    wb.Open('demo.xlsx');
    ShowMessage(wb.Sheets[0].Cells.Item[1, 1].Value);
  finally
    wb.Free;
  end;
end;

See also