lxHandleX, including HTML export options and helper functions for callers working directly with XLSX cell references or XML payload text
property FormulaArrayMemoryLimit: Int64;
MUNIT, defaults to 64 MiB, applies to parallel recalculation workers, and clamps values below a single Double element
type
TXLSXHtmlExportOptions = class
constructor Create;
property Title: WideString;
property WriteDocument: Boolean;
property IncludeStyles: Boolean;
property IncludeBOM: Boolean;
property TableClass: WideString;
end;
| Title | Sets the document title when WriteDocument is True |
| WriteDocument | When True, writes a complete HTML document. When False, writes just the table fragment |
| IncludeStyles | Controls whether exported HTML includes generated CSS and inline styling information |
| IncludeBOM | Controls whether file and stream output starts with a UTF-8 BOM |
| TableClass | Adds a CSS class name to the generated <table> element |
function XlsxColumnLabel(Col: Integer): WideString;
function XlsxCellRef(Row, Col: Integer): WideString;
function XlsxColumnIndex(const ColumnLabel: WideString): Integer;
function XlsxParseCellRef(const Ref: WideString; out Row, Col: Integer): Boolean;
function XlsxParseRangeRef(const Ref: WideString; out Row1, Col1, Row2, Col2: Integer): Boolean;
| XlsxColumnLabel | Converts a 1-based column index to an A1-style column label such as A, Z, or AA |
| XlsxCellRef | Builds an A1-style cell reference from 1-based row and column numbers |
| XlsxColumnIndex | Converts an A1-style column label to a 1-based column index |
| XlsxParseCellRef | Parses a single A1-style cell reference and returns row and column coordinates |
| XlsxParseRangeRef | Parses a rectangular A1-style range and returns inclusive 1-based bounds |
function XlsxEscapeText(const Value: WideString): WideString;
function XlsxEscapeAttr(const Value: WideString): WideString;
XlsxEscapeText escapes XML text-node content, while XlsxEscapeAttr escapes XML attribute values and keeps attribute whitespace that Excel must read back exactly
The advanced XLSX members documented across this help file pass and return the plain types below, declared in lxHandleX
{ Find and replace }
TXLSXFindFlag = (lxfMatchCase, lxfWholeCell, lxfSearchFormulas,
lxfUseWildcards, lxfBackward, lxfByColumns);
TXLSXFindOptions = set of TXLSXFindFlag;
TXLSXReplaceAction = (raReplace, raSkip, raAbort);
TXLSXReplaceEvent = function(ARow, ACol: Integer;
const ACurrentText: WideString; var ANewText: WideString):
TXLSXReplaceAction of object;
{ Progress and recalculation policy }
TXLSXProgressPhase = (xlsxProgressReading, xlsxProgressWriting,
xlsxProgressCalculating, xlsxProgressExporting);
TXLSXProgressState = (ppsBegin, ppsWork, ppsEnd);
TXLSXRecalcMode = (xrmManual, xrmIfDirty, xrmAlways);
TXLSXPasswordEvent = procedure(Sender: TObject;
var PassWord: WideString) of object;
{ Custom document properties (docProps/custom.xml) }
TXLSXCustomPropType = (xcptText, xcptNumber, xcptDate, xcptBoolean);
TXLSXCustomProperty = class
Name: WideString;
PropType: TXLSXCustomPropType;
{ + TextValue, NumberValue, DateValue, BoolValue accessors }
end;
{ Table styles, fills, sorts, external links, controls, sparklines }
TXLSXTableStyleSectionKind = (xltssWholeTable, xltssHeaderRow,
xltssTotalRow, xltssFirstColumn, xltssLastColumn, xltssFirstRowStripe,
xltssSecondRowStripe, xltssFirstColumnStripe, xltssSecondColumnStripe,
xltssFirstHeaderCell, xltssLastHeaderCell, xltssFirstTotalCell,
xltssLastTotalCell { + subtotal and subheading slots });
TXLSXFillPattern = (xlsxFillNone, xlsxFillSolid, xlsxFillGray125
{ + the remaining ECMA-376 pattern values });
TXLSXGradientKind = (xlsxGradientNone, xlsxGradientLinear, xlsxGradientPath);
TXLSXSortBy = (xlsxBsValue, xlsxBsCellColor, xlsxBsFontColor, xlsxBsIcon);
TXLSXSortMethod = (xlsxSmNone, xlsxSmStroke, xlsxSmPinYin);
TXLSXExternalLinkKind = (xlsxExternalLinkUnknown, xlsxExternalLinkWorkbook,
xlsxExternalLinkDde { + the remaining link kinds });
TXLSXFormControlType = (xfcButton, xfcCheckBox, xfcRadioButton, xfcComboBox,
xfcListBox);
TXLSXSparklineAxisType = (xlsxSparklineAxisIndividual,
xlsxSparklineAxisGroup, xlsxSparklineAxisCustom);
TXLSXSparklineEmptyCells = (xlsxSparklineEmptyGap, xlsxSparklineEmptyZero,
xlsxSparklineEmptySpan);
{ Chart authoring and layout }
TXLSXChartBarGrouping = (xlsxBarGroupClustered, xlsxBarGroupStacked,
xlsxBarGroupPercentStacked);
TXLSXChartMarkerSymbol = (xlsxMarkerAuto, xlsxMarkerCircle, xlsxMarkerDash,
xlsxMarkerDiamond, xlsxMarkerDot, xlsxMarkerPlus, xlsxMarkerSquare,
xlsxMarkerStar, xlsxMarkerTriangle, xlsxMarkerX, xlsxMarkerNone);
TXLSXChartDataLabelPos = (xlsxDlblPosDefault, xlsxDlblPosCenter,
xlsxDlblPosInEnd, xlsxDlblPosOutEnd, xlsxDlblPosBestFit, xlsxDlblPosLeft,
xlsxDlblPosRight, xlsxDlblPosTop, xlsxDlblPosBottom);
TXLSXChartMutable = class; { mutable base of the chart model nodes }
TXLSXChartLayoutRect = record
Left, Top, Right, Bottom: Double;
end;
TXLSXChartResolvedLayout = record
ChartRect: TXLSXChartLayoutRect;
TitleRect: TXLSXChartLayoutRect;
LegendRect: TXLSXChartLayoutRect;
PlotOuterRect: TXLSXChartLayoutRect;
PlotInnerRect: TXLSXChartLayoutRect;
{ + per-axis title rectangles and visibility flags }
end;
{ Pagination keep-together blocks }
TXLSXKeepTogetherRange = record
Row1, Row2: Integer;
Level: Integer;
ReplaceBiggerLevels: Boolean;
end;
TXLSXKeepTogetherCols = record
Col1, Col2: Integer;
Level: Integer;
ReplaceBiggerLevels: Boolean;
end;
{ Diagnostics records }
TXLSXValidationIssue = record
Row, Col: Integer;
ValidationIndex: Integer;
Reason: WideString;
end;
TXLSXUnknownFunctionInfo = record
Sheet: Integer;
Row, Col: Integer;
Name: WideString;
LocationKind: TXLSFormulaSymbolLocationKind;
DefinedNameIndex: Integer;
DefinedName: WideString;
end;
TXLSXRowMajorOrder = record
CellOrder: array of Integer;
RowStarts: array of Integer;
MaxRow: Integer;
end;
| Find and replace | TXLSXWorksheet.FindText / FindTextIn accept TXLSXFindOptions (match case, whole cell, search formulas, wildcards, backward, by columns) and ReplaceText takes a TXLSXReplaceEvent whose result selects raReplace, raSkip, or raAbort per match, with a writable ANewText for per-cell substitutions |
| Progress and saving | TXLSXWorkbook.OnProgress / OnProgressEx deliver TXLSXProgressPhase and (extended form) TXLSXProgressState with an Abort flag; RecalcBeforeSave is a TXLSXRecalcMode that selects manual, dirty-only, or always recalculation before serialisation; OnPassword is a TXLSXPasswordEvent that supplies open and write passwords |
| Custom properties | TXLSXWorkbook.CustomProperties is a TXLSXCustomProperties collection with typed AddText / AddNumber / AddDate / AddBoolean creators, FindByName, Delete, and Clear, plus Count and Items enumeration; each TXLSXCustomProperty tags its value with a TXLSXCustomPropType matching the ECMA-376 variant mapping |
| Style overlays | TXLSXStyleOverlays manages per-range overrides on top of stored styles: Add creates one, ApplyToCell restyles a single cell, Resolve, ResolveRowDefault, and ResolveColumnDefault answer style lookups during load, HasVirtualStyle reports generated styles, MaxVirtualRow bounds virtual rows, ApplyStructuralEdit shifts overlays after row or column edits, and Clear, Count, and Items maintain the collection |
| Styles, fills, and overlays | TXLSXFill exposes Pattern, GradientKind, and GradientStops through TXLSXFillPattern, TXLSXGradientKind, and TXLSXGradientStop; TXLSXWorksheet.StyleOverlays is a TXLSXStyleOverlays collection whose TXLSXStyleOverlay entries carry a rectangle, the affected format parts, and the overriding cell styling |
| Tables, sorts, and links | Custom table styles (TXLSXWorkbook.CustomTableStyles) build section formatting from TXLSXTableStyleSectionKind slots: the collection offers Add, FindByName, Delete, Clear, Count, and Items, whilst each TXLSXCustomTableStyle exposes Name, SectionSize, PivotStyle, and TableStyleFlag and answers HasSection / ResolveSection lookups; sort conditions classify keys with TXLSXSortBy and text ordering with TXLSXSortMethod; TXLSXExternalLink.Kind is a TXLSXExternalLinkKind and each link lists TXLSXExternalDefinedName entries with Name and RefersTo |
| Custom XML parts | TXLSXWorkbook.CustomXmlParts preserves item-specific custom XML: Add, Delete, and Clear maintain the parts, each TXLSXCustomXmlPart carries its payload in Xml plus an ItemId and a SchemaRefs string list of schema relationships (AddSchemaRef appends), and preserved parts round-trip through save untouched |
| Cell style payloads | TXLSXCellStylePayload is a shareable immutable style bundle attached to individual cells: Acquire / Release manage its reference count, Clone copies it, IsDefault tests the default style, and InternKey identifies equal payloads so storage can deduplicate them |
| Controls, drawings, and sparklines | TXLSXWorksheet.FormControls is a TXLSXFormControls collection of TXLSXFormControl objects — each exposes ControlType, Caption, MacroName, LinkedCell, and its Items list — typed by TXLSXFormControlType, with TXLSXFormControlItems holding per-control item text; drawing objects report their kind through TXLSDrawingObjectKind, and sparkline groups expose axis scaling and empty-cell behaviour through TXLSXSparklineAxisType and TXLSXSparklineEmptyCells |
| Charts | Plot groups and series classify bar grouping, marker symbols, and data-label positions via the TXLSXChartBarGrouping, TXLSXChartMarkerSymbol, and TXLSXChartDataLabelPos enums; the chart model nodes share the TXLSXChartMutable base class, and TXLSXChartLayoutResolver.ResolveChart fills a TXLSXChartResolvedLayout of TXLSXChartLayoutRect rectangles for chart, title, legend, plot, and axis titles |
| Pagination keep blocks | KeepRowsTogether / KeepColumnsTogether on the XLSX pagination surface maintain TXLSXKeepTogetherRange and TXLSXKeepTogetherCols records so page-break planning keeps a block on one page |
| Diagnostics | TXLSXWorksheet.CheckDataValidations returns TXLSXValidationIssue records (cell, validation index, reason); TXLSXWorkbook.ScanUnknownFunctions returns TXLSXUnknownFunctionInfo records locating unimplemented calls in cells and defined names; TXLSXCells.GetStorageStatistics fills a TXLSXCellStorageStatistics record with cell, row-block, per-payload byte, and styled-blank accounting; TXLSXWorksheet.BuildRowMajorCellOrder returns a TXLSXRowMajorOrder describing the in-memory traversal order of populated cells |
TXLSXWorkbook.FormulaLocale reads or writes the locale tag used when parsing and serialising formulas, mirroring the classic workbook's formula-locale handlingTable slicers (Excel 2013+) are written and inspected through two collections: TXLSXWorksheet.Slicers holds the per-sheet slicer views (TXLSXSlicer) and TXLSXWorkbook.SlicerCaches holds the shared data sources (TXLSXSlicerCache)
property TXLSXWorksheet.Slicers: TXLSXSlicers;
property TXLSXWorkbook.SlicerCaches: TXLSXSlicerCaches;
{ TXLSXSlicers }
function Add(ATable: TXLSXTable; const AColumnName: WideString): TXLSXSlicer; overload;
function Add(ATable: TXLSXTable; const AColumnName: WideString;
ARow1, ACol1, ARow2, ACol2: Integer): TXLSXSlicer; overload;
function IndexByName(const AName: WideString): Integer;
function FindByName(const AName: WideString): TXLSXSlicer;
procedure Delete(Index: Integer);
procedure Clear;
property Count: Integer;
property Items[Index: Integer]: TXLSXSlicer;
{ TXLSXSlicer view }
property Name: WideString; { workbook-unique }
property CacheName: WideString;
property Cache: TXLSXSlicerCache;
property Caption: WideString;
property StartItem: Integer;
property ColumnCount: Integer;
property ColumnWidth: WideString;
property ShowCaption: Integer;
property StyleName: WideString;
property LockedPosition: Integer;
property RowHeight: WideString;
property RowHeightEmu: Integer;
procedure SetBounds(ARow1, ACol1, ARow2, ACol2: Integer);
property FromRow, FromCol, ToRow, ToCol: Integer; { anchor cell corners }
property FromRowOff, FromColOff, ToRowOff, ToColOff: Integer; { EMU offsets }
property AnchorType: TXLSXAnchorEditAs;
property HasAnchor: Boolean;
{ TXLSXSlicers, load-path entry }
function AddInternal: TXLSXSlicer;
{ TXLSXSlicerCache }
property Name: WideString; { e.g. Slicer_Region }
property SourceName: WideString; { source table column / pivot field }
property IsTableCache: Boolean;
property IsPivot: Boolean;
property TableId: Integer;
property TableColumnId: Integer;
property SortOrder: WideString; { '' | ascending | descending }
property CrossFilter: WideString; { '' | none | showItemsWithDataAtTop | showItemsWithNoData }
property CustomListSort: Integer; { -1 absent, else 0/1 }
{ TXLSXSlicerCaches }
function Add: TXLSXSlicerCache;
function FindByName(const AName: WideString): TXLSXSlicerCache;
function IndexOfName(const AName: WideString): Integer;
function FindForTableColumn(ATableId, ATableColumnId: Integer): TXLSXSlicerCache;
procedure Delete(Index: Integer);
procedure Clear;
property Count: Integer;
property Items[Index: Integer]: TXLSXSlicerCache;
Slicers.Add(Table, ColumnName) builds a slicer end to end: it registers or reuses the backing cache named Slicer_<column>, reserves the matching #N/A defined name, writes the worksheet slicerList extension, and anchors the view with a markup-compatibility drawing anchor whose fallback notice covers older Excel releases; the second overload pins the view rectangle, otherwise a default anchor to the right of the table is used
Loaded views and caches parse back on open, shared caches are reused across views, and deleting the last view of a cache drops the cache; unrecognised attributes and extensions keep their original representation, whilst table bindings track saved table ids and supported Pivot connections track saved sheet identities
Supported imported non-OLAP Pivot caches expose typed member inspection and guarded selection changes via IsNonOlapPivot, ItemCount, ItemCacheIndex, ItemSelected, SetSelectedItems, and ClearSelection; see Pivot Slicer selection for shared-view behaviour, failure atomicity, and the imported same-field mask boundary
Unsupported Pivot caches keep their original bytes; a loaded Slicer view is also reachable as a drawing object through TXLSXDrawingObject.Slicer
var
Ref: WideString;
Row, Col: Integer;
begin
Ref := XlsxCellRef(10, 5);
if XlsxParseCellRef(Ref, Row, Col) then
Worksheet.Cells.Item[Row, Col].Value := 'Checked';
end;
See workbook infrastructure API for I/O callbacks, serialiser style pools, encrypted-file verification, and workspace dependency-graph contracts