HotXLS-dokumentasjon
Innhold
Hjem
Oversikt
XLSX Facade
Hjem
Innhold
XLSX Facade Overview
klasse TXLSXWorkbook
Top-level container for an Excel 2007+ (.xlsx) workbook. Declared in
lxHandleX. Holds a TXLSXSheets
collection and provides SaveAs / Open entry
points for OOXML workbooks, CSV / TSV import, and OpenDocument Spreadsheet
(.ods) read/write workflows
Declaration
type
TXLSXFileFormat = (xlsxOpenXMLWorkbook, xlsxOpenDocumentSpreadsheet);
TODSExportOptions = class
constructor Create;
property Generator: WideString;
property IncludeCharts: Boolean;
property IncludeImages: Boolean;
end;
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 OpenCSV(const FileName: WideString): Integer; overload;
function OpenCSV(const FileName: WideString; ASeparator: WideChar;
ADetectTypes: Boolean): Integer; overload;
function OpenCSV(Stream: TStream): Integer; overload;
function OpenCSV(Stream: TStream; ASeparator: WideChar;
ADetectTypes: Boolean): Integer; overload;
function OpenODS(const FileName: WideString): Integer; overload;
function OpenODS(Stream: TStream): Integer; overload;
function SaveAsODS(const FileName: WideString): Integer; overload;
function SaveAsODS(Stream: TStream): Integer; overload;
function SaveAsODS(const FileName: WideString; Options: TODSExportOptions): Integer; overload;
function SaveAsODS(Stream: TStream; Options: TODSExportOptions): Integer; overload;
function GetODSSheetNames(const FileName: WideString; AList: TStrings): Integer; overload;
function GetODSSheetNames(Stream: TStream; AList: TStrings): Integer; overload;
function GetSheetNames(const FileName: WideString; AList: TStrings): Integer; overload;
function GetSheetNames(Stream: TStream; AList: TStrings): 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; Options: TXLSXHtmlExportOptions): Integer; overload;
function SaveAsHTML(const FileName: WideString; SheetIndex: Integer): Integer; overload;
function SaveAsHTML(const FileName: WideString; SheetIndex: Integer; Options: TXLSXHtmlExportOptions): Integer; overload;
function SaveAsHTML(Stream: TStream): Integer; overload;
function SaveAsHTML(Stream: TStream; Options: TXLSXHtmlExportOptions): Integer; overload;
function SaveAsHTML(Stream: TStream; SheetIndex: Integer): Integer; overload;
function SaveAsHTML(Stream: TStream; SheetIndex: Integer; Options: TXLSXHtmlExportOptions): 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) write path.
// OpenEncrypted still raises for encrypted packages.
function SaveAsEncrypted(const FileName, APassword: WideString): Integer;
function OpenEncrypted(const FileName, APassword: WideString): Integer;
function CanReadEncrypted(const FileName: WideString): Boolean;
function Calculate(const Formula: WideString): Variant;
property OnUserFunction: TXLSUserFunctionEvent;
property OnUserFunctionEx: TXLSUserFunctionExEvent;
// 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 VbaProjectDefined: Boolean;
property VbaProject: AnsiString;
property ParsedVBAProject: TXLSVBAProject;
// 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 Minimized: Boolean;
property WindowHidden: Boolean;
property ShowHorizontalScroll: Boolean;
property ShowVerticalScroll: Boolean;
property ShowSheetTabs: Boolean;
property XWindow: Integer;
property YWindow: Integer;
property WindowWidth: Integer;
property WindowHeight: Integer;
property TabRatio: Integer;
property FirstSheet: Integer;
property AutoFilterDateGrouping: Boolean;
property Date1904: Boolean;
property SourceFormat: TXLSXFileFormat;
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
Calculate
Compiles and evaluates an Excel formula string against the active sheet, and a leading = is accepted
Cell references, ranges, formulas stored in referenced cells, cross-sheet references, defined-name ranges, and supported text, date, workday, engineering/base-conversion, bitwise, statistical, aggregate, and math functions are evaluated through the shared HotXLS formula engine
See OnUserFunction to handle custom or unsupported functions
OnUserFunction, OnUserFunctionEx
Callback used by Calculate when a
formula calls a custom or unsupported funksjon. See
OnUserFunction / OnUserFunctionEx callback
GetSheetNames(FileName, AList), GetSheetNames(Stream, AList)
Reads xl/workbook.xml and appends
worksheet names to AList in workbook order without
loading worksheet XML. The stream overload expects a complete XLSX
package stream. Returnerer the number of names read, or
-1 when the package cannot be opened
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 / VbaProjectDefined
LoadVbaProjectFromFile imports a raw vbaProject.bin payload; VbaProject exposes the bytes; HasVbaProject and read-only VbaProjectDefined both report whether a payload is present. Save with a .xlsm extension when macros should be enabled by Excel
ParsedVBAProject
Read-only parsed view of the current
vbaProject.bin payload. When the payload is a valid OLE
VBA storage, returns a TXLSVBAProject exposing module
names and source text; otherwise returns nil. The raw
VbaProject payload remains the authoritative bytes used
for SaveAs/Open round-trip
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
ECMA-376 Standard Encryption write support
SaveAsEncrypted writes a standard-conforming AES-128
protected OLE Compound File container for non-empty passwords, using
AES-NI acceleration when available and a portable software fallback
otherwise. The encrypted output opens in Excel 2007 and later with
the password used to save it. CanReadEncrypted detects
the OLE2 magic bytes; OpenEncrypted transparently falls
back to plain Open for unencrypted files and still raises
EXlsxEncryptionNotImplemented for encrypted packages
until decrypt support lands
SaveAs(FileName)
Writes the workbook to the given file. Returnerer
1 on success, negative on failure. If the workbook has no
sheets a default Sheet1 is added before writing
SaveAs(FileName, FileFormat)
Accepts xlsxOpenXMLWorkbook for OOXML output or xlsxOpenDocumentSpreadsheet for a direct OpenDocument Spreadsheet (.ods) package. SaveAsODS is the named shortcut for the ODS path and offers the same file-format output plus optional TODSExportOptions control
SaveAs(Stream) / SaveAs(Stream, FileFormat)
Writes the workbook directly into any TStream. The FileFormat overload accepts xlsxOpenXMLWorkbook or xlsxOpenDocumentSpreadsheet, making it suitable for in-memory XLSX or ODS generation and BLOB storage paths. Returnerer 1 on success
Open(FileName)
Reads an OOXML .xlsx archive and replaces the
Sheets collection with its contents. Returnerer 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. Returnerer 1 on success
OpenCSV(FileName) / OpenCSV(Stream)
Imports CSV or TSV text into the workbook with
automatic separator detection (,, ;,
tab, or pipe), BOM-based UTF-8 / UTF-16 encoding detection, RFC
4180-style quoted fields, and optional value type inference through
the ADetectTypes overloads. Calling
Open(FileName) on .csv or
.tsv routes to this importer automatically. See
OpenCSV method
OpenODS(FileName) / OpenODS(Stream)
Imports an OpenDocument Spreadsheet (.ods) package from a file or stream. The reader loads content.xml, shared style information from styles.xml, and view settings when present, preserving sheet names, scalar values, cached formula results, basic formula reference conversion, repeated rows/columns/cells, merged ranges, rich text, hyperlinks, comments, embedded images, embedded charts, hidden rows/columns/sheets, row heights, column widths, inherited/default styles, number/date/time/currency/scientific/fraction formats, cell and sheet protection state, named ranges, print areas, manual page breaks, freeze panes, AutoFilter ranges, row/column outline groups, headers/footers, data validations including prompts, conditional formats including formulas/color scales/data bars, and sheet tab colour. Full OpenFormula semantics, ODS protection keys, comment geometry, advanced comment formatting, and package features outside spreadsheet content are not converted
SaveAsODS(FileName) / SaveAsODS(Stream)
Saves the workbook as a valid OpenDocument Spreadsheet package. It writes the ODF mimetype, manifest, metadata, styles, and worksheet content directly, carrying typed values, formulas, styles, rich text, dimensions, merges, hyperlinks, images, charts, named ranges, AutoFilter ranges, conditional formats, data validations, freeze panes, page setup, headers/footers, print ranges, manual page breaks, outline groups, tab colour, and sheet visibility where those features are represented in the HotXLS workbook model
TODSExportOptions
Controls ODS export. Generator overrides the meta:generator value in meta.xml; an empty value uses the built-in HotXLS generator string. IncludeCharts and IncludeImages default to True and, when False, remove the matching drawing frames, package parts, and manifest entries together so the output has no orphan chart or image references. The caller owns the options instance and must free it
GetODSSheetNames(FileName, AList), GetODSSheetNames(Stream, AList)
Reads only ODS table names from content.xml into AList without resetting or populating the workbook. Use it when a file picker or import workflow needs sheet names before loading the workbook
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 cells are calculated before
export, and formula text is used only when calculation is unavailable
Returnerer 0 on success
SaveAsHTML overloads
Options overloads accept TXLSXHtmlExportOptions for Title, WriteDocument, IncludeStyles, IncludeBOM, and TableClass. Set WriteDocument to False for a table fragment; set IncludeBOM to False when embedding output in an existing UTF-8 response. Exported output now preserves cell hyperlinks as <a> anchors and anchored images as inline data: image URIs; workbook-level export includes anchors outside populated cells. Cell comments are also emitted as escaped <td> title attributes, and workbook-level export includes comment-only anchors. When IncludeStyles is enabled, explicit ColWidth values are emitted as <col> widths and explicit RowHeight values as <tr> height styles. Rich-text cell runs are emitted as escaped <span> elements with inline font, color, emphasis, and underline styles when IncludeStyles is enabled. Cell borders are emitted as CSS border-left, border-right, border-top, and border-bottom rules when IncludeStyles is enabled. Hidden rows and columns marked by RowHidden / ColHidden, plus explicit zero-width columns, are skipped in the generated table and <colgroup>. Vertical alignment settings are emitted as CSS vertical-align rules when IncludeStyles is enabled
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. Returnerer 0 on
success
ActiveSheet
1-based index of the active (selected) sheet (0 when none), matching the classic XLS engine's ActiveSheetIndex. SaveAs writes activeTab="N" on <workbookView> (the OOXML activeTab attribute itself stays 0-based) when workbook view options need to be emitted, and marks the matching sheet with tabSelected="1"
Minimized / WindowHidden / ShowHorizontalScroll / ShowVerticalScroll
Workbook-window chrome flags written to <workbookView>. Minimized writes minimized="1", WindowHidden writes visibility="hidden", and ShowHorizontalScroll / ShowVerticalScroll hide or show the workbook scrollbars. Open round-trips all four values
XWindow / YWindow / WindowWidth / WindowHeight
Workbook-window geometry written to <workbookView>. XWindow and YWindow store the window origin, while WindowWidth and WindowHeight store its size. Values are clamped to 0..65535 and round-trip on load
ShowSheetTabs / TabRatio / FirstSheet / AutoFilterDateGrouping
Workbook-window view options written to <workbookView>. ShowSheetTabs hides or shows the sheet-tab bar, TabRatio controls the tab-bar-to-scrollbar width split and clamps to 0..1000, FirstSheet selects the first visible tab as a 0-based sheet index and clamps to an existing sheet, and AutoFilterDateGrouping controls Excel date grouping in AutoFilter dropdowns. Open round-trips all four values
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
SourceFormat
Reports the package family loaded by the most recent successful Open or OpenODS call. It returns xlsxOpenXMLWorkbook for XLSX and xlsxOpenDocumentSpreadsheet for ODS; a newly created workbook reports xlsxOpenXMLWorkbook until a file is opened
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
Eksempel
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;
Se også
Copyright © 2010-2026 losLab Software