lxHandleX.
type
TXLSXWorksheet = class
constructor Create(const AName: WideString);
destructor Destroy; override;
function MergeCells(ARow1, ACol1, ARow2, ACol2: Integer): Integer;
function AddHyperlink(ARow, ACol: Integer; const AUrl: WideString): Integer; overload;
function AddHyperlink(ARow, ACol: Integer; const AUrl, ADisplay: WideString): Integer; overload;
function AddHyperlink(ARow, ACol: Integer; const AUrl, ADisplay, ATooltip: WideString): Integer; overload;
function AddComment(ARow, ACol: Integer; const AText: WideString): Integer; overload;
function AddComment(ARow, ACol: Integer; const AText, AAuthor: WideString): Integer; overload;
function AddImage(ARow, ACol: Integer; const AData: AnsiString; AFormat: TXLSXImageFormat): Integer;
function AddImageFromFile(ARow, ACol: Integer; const AFileName: WideString): Integer;
function AddConditionalFormat(const ARange: WideString; AOp: TXLSXCfOperator; const AFormula1: WideString): Integer; overload;
function AddConditionalFormat(const ARange: WideString; AOp: TXLSXCfOperator; const AFormula1, AFormula2: WideString): Integer; overload;
// Excel 2007+ extension rules (v2.36.0+) — mirror the BIFF8 IXLSWorksheet API.
function AddCondFormatDataBar(const ARange: WideString; AColor: LongWord;
AMinKind: TXLSCfValueKind = cfvMinOfRange; const AMinValue: WideString = '';
AMaxKind: TXLSCfValueKind = cfvMaxOfRange; const AMaxValue: WideString = ''): Integer;
function AddCondFormatColorScale2(const ARange: WideString;
AMinColor, AMaxColor: LongWord;
AMinKind: TXLSCfValueKind = cfvMinOfRange; const AMinValue: WideString = '';
AMaxKind: TXLSCfValueKind = cfvMaxOfRange; const AMaxValue: WideString = ''): Integer;
function AddCondFormatColorScale3(const ARange: WideString;
AMinColor, AMidColor, AMaxColor: LongWord;
AMinKind: TXLSCfValueKind = cfvMinOfRange; const AMinValue: WideString = '';
AMidKind: TXLSCfValueKind = cfvPercentile; const AMidValue: WideString = '50';
AMaxKind: TXLSCfValueKind = cfvMaxOfRange; const AMaxValue: WideString = ''): Integer;
function AddCondFormatIconSet(const ARange: WideString;
ASetType: TXLSIconSetType): Integer;
function AddDataValidation(const ARange: WideString; AType: TXLSXDataValidationType; AOp: TXLSXDvOperator; const AFormula1: WideString): Integer; overload;
function AddDataValidation(const ARange: WideString; AType: TXLSXDataValidationType; AOp: TXLSXDvOperator; const AFormula1, AFormula2: WideString): Integer; overload;
function AddListValidation(const ARange, AItems: WideString): Integer;
// Excel-style table (insert table) — see TXLSXTable.
function AddTable(const AName, ARange: WideString; AColumns: TStrings): Integer;
// Chart helpers — anchored or full-page (via AddChartSheet on workbook).
function AddChart(AChartType: TXLSXChartType; const ATitle: WideString;
AFromRow, AFromCol, AToRow, AToCol: Integer): TXLSXChart;
// Internal Sheet!Cell hyperlink (no rels relationship needed).
function AddHyperlinkToCell(ARow, ACol: Integer; const ALocation: WideString): Integer; overload;
function AddHyperlinkToCell(ARow, ACol: Integer; const ALocation, ADisplay: WideString): Integer; overload;
function AddHyperlinkToCell(ARow, ACol: Integer; const ALocation, ADisplay, ATooltip: WideString): Integer; overload;
// Sheet visibility and selection state.
property Visibility: TXLSXSheetVisibility;
property IsSelected: Boolean;
// Auto-fit column width / row height from cell content.
procedure AutoFitColumn(ACol: Integer);
procedure AutoFitColumns(AColMin, AColMax: Integer);
procedure AutoFitRow(ARow: Integer);
procedure AutoFitRows(ARowMin, ARowMax: Integer);
// Row / column insert and delete with cascade shift.
procedure InsertRows(BeforeRow, Count: Integer);
procedure DeleteRows(StartRow, Count: Integer);
procedure InsertCols(BeforeCol, Count: Integer);
procedure DeleteCols(StartCol, Count: Integer);
// Manual page breaks.
procedure AddRowBreak(BeforeRow: Integer);
procedure AddColBreak(BeforeCol: Integer);
function HasRowBreak(BeforeRow: Integer): Boolean;
function HasColBreak(BeforeCol: Integer): Boolean;
procedure RemoveRowBreak(BeforeRow: Integer);
procedure RemoveColBreak(BeforeCol: Integer);
procedure ClearRowBreaks;
procedure ClearColBreaks;
procedure ClearAllPageBreaks;
function RowBreakCount: Integer;
function ColBreakCount: Integer;
function RowBreaks(Index: Integer): Integer;
function ColBreaks(Index: Integer): Integer;
function Calculate(const Formula: WideString): Variant;
function ForEachCell(Callback: TXLSXCellReadEvent): Integer;
function FindText(const SearchText: WideString; out Row, Col: Integer): Boolean; overload;
function FindText(const SearchText: WideString; out Row, Col: Integer; MatchCase: Boolean): Boolean; overload;
function ReplaceText(const SearchText, ReplacementText: WideString): Integer; overload;
function ReplaceText(const SearchText, ReplacementText: WideString; MatchCase: Boolean): Integer; overload;
function WriteCells(const ARange: WideString; Callback: TXLSXCellWriteEvent): Integer; overload;
function WriteCells(ARow1, ACol1, ARow2, ACol2: Integer; Callback: TXLSXCellWriteEvent): Integer; overload;
// Column / row metadata.
procedure SetColWidth(ACol: Integer; AWidth: Double);
function HasColWidth(ACol: Integer): Boolean;
procedure ClearColWidths;
procedure SetRowHeight(ARow: Integer; AHeight: Double);
function HasRowHeight(ARow: Integer): Boolean;
procedure ClearRowHeights;
procedure SetRowOutlineLevel(ARow, ALevel: Integer);
function HasRowOutlineLevel(ARow: Integer): Boolean;
procedure ClearRowOutlineLevels;
procedure SetColOutlineLevel(ACol, ALevel: Integer);
function HasColOutlineLevel(ACol: Integer): Boolean;
procedure ClearColOutlineLevels;
procedure SetRowHidden(ARow: Integer; AHidden: Boolean);
procedure SetColHidden(ACol: Integer; AHidden: Boolean);
procedure SetRowCollapsed(ARow: Integer; ACollapsed: Boolean);
procedure SetColCollapsed(ACol: Integer; ACollapsed: Boolean);
// Range copy / move / clear API.
procedure ClearRange(ARow1, ACol1, ARow2, ACol2: Integer);
procedure CopyRange(ASrcRow1, ASrcCol1, ASrcRow2, ASrcCol2, ADstRow, ADstCol: Integer);
procedure CopyRangeTo(ASrcRow1, ASrcCol1, ASrcRow2, ASrcCol2: Integer;
ATargetSheet: TXLSXWorksheet; ADstRow, ADstCol: Integer);
procedure MoveRange(ASrcRow1, ASrcCol1, ASrcRow2, ASrcCol2, ADstRow, ADstCol: Integer);
// Frozen panes (top rows / left columns).
procedure FreezePane(ACol, ARow: Integer);
procedure UnfreezePane;
property FreezeCol, FreezeRow: Integer;
// Page setup.
procedure SetPageMargins(ALeft, ARight, ATop, ABottom: Double); overload;
procedure SetPageMargins(ALeft, ARight, ATop, ABottom, AHeader, AFooter: Double); overload;
property MarginLeft, MarginRight, MarginTop, MarginBottom,
MarginHeader, MarginFooter: Double;
property PageLandscape: Boolean;
property PaperSize, PageScale, FitToWidth, FitToHeight: Integer;
property HeaderText, FooterText: WideString;
property PrintGridlines: Boolean;
property PrintHeadings: Boolean;
property BlackAndWhite: Boolean;
property Draft: Boolean;
property PrintNotes: Boolean;
property PrintOverThenDown: Boolean;
property PrintArea: WideString;
property PrintTitleRows: WideString;
property PrintTitleCols: WideString;
// Auto-filter range (e.g. "A1:C100"). Empty = no filter.
procedure SetAutoFilter(ARow1, ACol1, ARow2, ACol2: Integer); overload;
procedure SetAutoFilter(const ARange: WideString); overload;
procedure ClearAutoFilter;
property AutoFilterRange: WideString;
property AutoFilterColumns: TXLSXAutoFilterColumns;
function AddAutoFilterColumn(AColId: Integer; AOp1: TXLSXAutoFilterOp; const ACriteria1: WideString): TXLSXAutoFilterColumn; overload;
function AddAutoFilterColumn(AColId: Integer; AOp1: TXLSXAutoFilterOp; const ACriteria1: WideString; AOp2: TXLSXAutoFilterOp; const ACriteria2: WideString; AAndConnector: Boolean): TXLSXAutoFilterColumn; overload;
procedure ClearAutoFilterColumns;
// Sheet protection (16-bit legacy hash).
procedure Protect; overload;
procedure Protect(const APassword: WideString); overload;
procedure UnProtect;
property IsProtected: Boolean;
// Sheet tab color + chart-sheet flag.
property TabColor: LongWord;
property TabColorIsAuto: Boolean;
property IsChartSheet: Boolean;
property Name: WideString;
// Sheet view display options.
property View: TXLSXWindowView;
property Zoom: Integer;
property DisplayGridLines: Boolean;
property DisplayZeros: Boolean;
property DisplayRightToLeft: Boolean;
property StandardWidth: Double;
property StandardHeight: Double;
// Collections owned by the worksheet.
property Cells: TXLSXCells;
property MergedCells: TXLSXMergedCells;
property Hyperlinks: TXLSXHyperlinks;
property Comments: TXLSXComments;
property Images: TXLSXImages;
property ConditionalFormats: TXLSXConditionalFormats;
property DataValidations: TXLSXDataValidations;
property Tables: TXLSXTables;
property Charts: TXLSXCharts;
property ColWidth[ACol: Integer]: Double;
property RowHeight[ARow: Integer]: Double;
property RowOutlineLevel[ARow: Integer]: Integer;
property ColOutlineLevel[ACol: Integer]: Integer;
property RowHidden[ARow: Integer]: Boolean;
property ColHidden[ACol: Integer]: Boolean;
property RowCollapsed[ARow: Integer]: Boolean;
property ColCollapsed[ACol: Integer]: Boolean;
property Range[const Ref: WideString]: TXLSXRange;
property RCRange[R1, C1, R2, C2: Integer]: TXLSXRange;
property UsedRange: TXLSXRange;
end;
TXLSXRange = class
function RefA1: WideString;
function SaveAsHTML(const FileName: WideString): Integer; overload;
function SaveAsHTML(Stream: TStream): Integer; overload;
procedure SetValue(const V: Variant);
procedure SetFormula(const F: WideString);
procedure Clear;
procedure ClearAll;
procedure SetFontIndex(AIdx: Integer);
procedure SetFillIndex(AIdx: Integer);
procedure SetBorderIndex(AIdx: Integer);
procedure SetNumberFormatIndex(AIdx: Integer);
procedure SetAlignmentIndex(AIdx: Integer);
procedure SetNumberFormat(const Fmt: WideString);
procedure SetBorders(Kind: TXLSXBorderEdgeKind; Style: TXLSXBorderStyle); overload;
procedure SetBorders(Kind: TXLSXBorderEdgeKind; Style: TXLSXBorderStyle; Color: LongWord); overload;
procedure SetLocked(ALocked: Boolean);
procedure SetFormulaHidden(AHidden: Boolean);
procedure AutoFitColumns;
procedure AutoFitRows;
procedure Merge; overload;
procedure Merge(Across: OleVariant); overload;
procedure Unmerge;
function Offset(DRow, DCol: Integer): TXLSXRange;
function Resize(NewRowCount, NewColCount: Integer): TXLSXRange;
end;
| Name | Worksheet tab name as shown by Excel. |
| Cells | Cell collection. Use Cells.Item[Row, Col].Value.
See TXLSXCells / TXLSXCell. |
| Calculate | Compiles and evaluates an Excel formula string in
this worksheet context. A leading = is accepted, and
worksheet-local references, referenced formula cells, cross-sheet
references, and defined-name ranges are resolved by the workbook's
shared formula evaluator. |
| ForEachCell / FindText / ReplaceText / WriteCells | Helpers for loaded-cell scanning, text search,
text replacement, and range filling. ForEachCell walks
existing cells in row order with value and formula text.
FindText and ReplaceText scan loaded text
cells in row order, skip formulas and non-text values, and can match
case sensitively. WriteCells requests values for a
numeric or A1-style range and supports per-cell skip and
cancellation. |
| MergedCells | Merged-range collection. See TXLSXMergedCells. |
| Hyperlinks | Hyperlink collection. See TXLSXHyperlinks. |
| Comments | Cell-comment collection. See TXLSXComments. |
| Tables / AddTable | Excel-style table band on the worksheet. See TXLSXTable / TXLSXTables. |
| Charts / AddChart | Anchored chart collection. See
TXLSXChart / TXLSXCharts.
For a full-page chart, use
Workbook.AddChartSheet(...). |
| AddHyperlinkToCell overloads | Internal anchor hyperlink — jumps to a
Sheet!Cell location elsewhere in the workbook. No
rels relationship is generated. See
TXLSXHyperlink.Location. |
| RowOutlineLevel / ColOutlineLevel | Group nesting level (0..7) for the matching row
or column. SaveAs emits outlineLevel="N" on the row /
col entry. |
| RowHidden / ColHidden | Boolean — drops the row or column from view
(hidden="1"). |
| RowCollapsed / ColCollapsed | Boolean — marks a row / column whose grouping
parent is collapsed (collapsed="1"). |
| FreezePane(Col, Row) / UnfreezePane | Sets a frozen split — the top Row
rows and left Col columns stay visible while the
remainder scrolls. FreezeCol and
FreezeRow are read-only properties exposing the
current split. |
| SetPageMargins / Page setup properties | All six margins plus PageLandscape,
PaperSize, PageScale,
FitToWidth, FitToHeight,
HeaderText, FooterText. Setting any one
triggers the corresponding <pageMargins>,
<pageSetup>, and <headerFooter>
blocks on SaveAs. |
| Visibility | Sheet visibility: xlsxSheetVisible
(default), xlsxSheetHidden (hidden but unhideable in
Excel UI), or xlsxSheetVeryHidden (requires VBA or
code to unhide). SaveAs emits a state attribute on the
<sheet> element in xl/workbook.xml. |
| IsSelected | When True, the sheet tab appears
selected (highlighted). Multiple sheets may be selected
simultaneously. SaveAs emits tabSelected="1" on the
matching <sheetView>. |
| AutoFitColumn / AutoFitColumns | AutoFitColumn(Col) measures every
cell in the column and sets ColWidth[Col] to the
widest content (ASCII = 1 ch, CJK = 2 ch, Calibri 11pt scale).
AutoFitColumns(Min, Max) iterates the column range,
clamped to the last cell-bearing column. |
| AutoFitRow / AutoFitRows | AutoFitRow(Row) sets
RowHeight[Row] to the tallest cell content in that row
(in points). AutoFitRows(Min, Max) iterates the row
range, clamped to the last cell-bearing row. |
| InsertRows / DeleteRows | InsertRows(BeforeRow, Count) shifts
every row at BeforeRow or below down by
Count. DeleteRows(StartRow, Count) removes
rows [StartRow, StartRow+Count-1] and pulls the remainder
up. Both cascade through cells, merged ranges, row heights, outline
levels, hidden flags, hyperlinks, comments, freeze pane, and
auto-filter / conditional-format / data-validation references. Images
and charts are not shifted in this release. |
| InsertCols / DeleteCols | Same as InsertRows / DeleteRows but
operating on columns. Column widths, outline levels, and hidden flags
are also updated. |
| AddRowBreak / AddColBreak | AddRowBreak(N) inserts a manual page
break before row N (i.e. row N starts a
new printed page). AddColBreak(N) breaks before column
N. SaveAs writes <rowBreaks> /
<colBreaks> in the sheet XML. |
| HasRowBreak / HasColBreak / RemoveRowBreak / RemoveColBreak | Query or remove a single manual break by its "before" index. |
| ClearRowBreaks / ClearColBreaks / ClearAllPageBreaks | Remove all row breaks, all column breaks, or both. |
| RowBreakCount / ColBreakCount / RowBreaks[i] / ColBreaks[i] | Iterate stored break indices (1-based "Before" values). |
| PrintGridlines / PrintHeadings | When True, SaveAs emits
printGridLines="1" /
printRowColHeadings="1" inside
<printOptions> so row/column grid lines or
headings appear on the printed page. |
| BlackAndWhite / Draft / PrintNotes / PrintOverThenDown | Print options in <pageSetup>:
BlackAndWhite renders in B&W;
Draft suppresses graphics for faster preview;
PrintNotes prints cell comments as displayed;
PrintOverThenDown switches page order to
left-to-right-first instead of top-to-bottom-first. |
| PrintArea | The rectangular region Excel prints (e.g.
"$A$1:$D$10"). Written as a workbook-level
<definedName name="_xlnm.Print_Area"> scoped to
this sheet. Empty string = not set. |
| PrintTitleRows / PrintTitleCols | Row or column ranges that repeat on every printed
page (e.g. "$1:$3" or "$A:$B"). Written as
<definedName name="_xlnm.Print_Titles">. |
| SetAutoFilter / AutoFilterRange | Sets the filter range (e.g.
"A1:C100"); SaveAs writes
<autoFilter ref="..."/>. |
| AutoFilterColumns / AddAutoFilterColumn / ClearAutoFilterColumns | Per-column filter criteria attached to the auto-filter
range. AddAutoFilterColumn(ColId, Op, Criteria) adds a
single-criterion filter; the two-criteria overload adds an AND/OR pair.
ColId is the 0-based offset within the auto-filter range
(not the sheet column). SaveAs writes <filterColumn>
children inside <autoFilter>. |
| Protect / UnProtect / IsProtected | Sheet-level protection toggle with an optional
16-bit legacy password hash. Reflected as
<sheetProtection>. |
| TabColor / TabColorIsAuto | ARGB colour for the worksheet tab in Excel.
TabColorIsAuto = True (default) suppresses the
<tabColor> child of <sheetPr>. |
| IsChartSheet | When True, SaveAs writes the sheet to
xl/chartsheets/sheetN.xml instead of
xl/worksheets/sheetN.xml and hosts
Charts[0] full-page. Use the
Workbook.AddChartSheet(...) helper for the typical
setup. |
| View | Worksheet view mode. Values are
xlsxNormalView, xlsxPageBreakPreview, and
xlsxPageLayoutView. SaveAs writes the corresponding
view attribute on <sheetView>; Open
reads it back. |
| Zoom | Sheet view zoom percentage (10–400; default 100,
clamped on assignment). Emits zoomScale="N" inside
<sheetView>; default is suppressed so untouched
sheets stay clean. |
| DisplayGridLines | When False, hides the cell grid lines
in the sheet view. Default True. Emits
showGridLines="0" when False. |
| DisplayZeros | When False, cells with a zero value
render as blank instead of "0". Default True. Emits
showZeros="0" when False. |
| DisplayRightToLeft | When True, the sheet is displayed in
right-to-left layout (column A on the right), suitable for Arabic or
Hebrew text. Emits rightToLeft="1". |
| StandardWidth | Default column width in Excel character units
(e.g. 8.43 = built-in default). Setting a value > 0 emits
<sheetFormatPr defaultColWidth="..."/>.
0 = unset (element suppressed). |
| StandardHeight | Default row height in points (e.g. 15 = built-in
default). Setting a value > 0 emits
defaultRowHeight="..." on
<sheetFormatPr>.
0 = unset. |
| CopyRange / CopyRangeTo / MoveRange / ClearRange | Cell-range operations. Each duplicates the cell
value, formula, all four *Index style references, the
new AlignmentIndex, and any
RichText payload.
MoveRange skips overlap with the destination so
partial-overlap moves preserve the freshly-pasted values. |
| Range / RCRange / UsedRange | Return sheet-owned
TXLSXRange
wrappers for A1 references, 1-based numeric coordinates, or the
populated worksheet closure. TXLSXRange.SaveAsHTML(FileName)
and TXLSXRange.SaveAsHTML(Stream) export only the
selected range as a UTF-8 HTML table. Range wrappers also provide
batch values, formulas, style indexes, number formats, borders,
protection flags, auto-fit, merge, offset, and resize helpers. |
| MergeCells(R1, C1, R2, C2) | Adds a merged rectangle and returns the new entry
index. Coordinates are 1-based, inclusive on both ends. Equivalent to
MergedCells.Add(R1, C1, R2, C2). For range-style code,
Range['A1:C3'].Merge(True) writes one merged range per
row, matching Excel's merge-across command. |
| AddHyperlink overloads | Adds an external URL hyperlink to a single cell.
Display and Tooltip are optional. Equivalent
to Hyperlinks.Add(...). |
| AddComment overloads | Adds a comment to a single cell. Author
is optional. Equivalent to Comments.Add(...). |
| ColWidth[Col] | Column width in Excel units (same scale used by the
Excel column-width dialog). Reading an unset column returns
0; assigning a value sets the width for that 1-based
column. SaveAs emits one <col> entry per customised
column; Open replays the <cols> block back into the
map. |
| RowHeight[Row] | Row height in points (same scale used by the Excel
row-height dialog). Reading an unset row returns 0;
assigning a value sets the height for that 1-based row. SaveAs writes
the ht and customHeight="1" attributes on
each <row> with a custom height, even rows that
carry only a height with no cell data. |
| SetColWidth / SetRowHeight | Direct setters used by the indexed properties above. Provided as named methods for callers that prefer not to use the property syntax. |
| HasColWidth / HasRowHeight | Returns True when the worksheet has a
customised width or height for the given index. Use this to
distinguish "unset" from a deliberate 0 value. |
| ClearColWidths / ClearRowHeights | Drops every customised width or height back to the Excel default. |
| Images | Image collection. Use AddImage /
AddImageFromFile as the convenience entry points. See
TXLSXImage / TXLSXImages. |
| ConditionalFormats | Conditional formatting rules for this worksheet.
Use AddConditionalFormat as the convenience entry.
See TXLSXConditionalFormat / TXLSXConditionalFormats. |
| DataValidations | Data-validation rules for this worksheet. Use
AddDataValidation / AddListValidation as
the convenience entries. See
TXLSXDataValidation / TXLSXDataValidations. |
| AddImage / AddImageFromFile | Adds an image anchored to a cell. AddImage takes
the raw bytes plus a TXLSXImageFormat;
AddImageFromFile reads the file and infers the format from its
extension. Equivalent to Images.Add on the collection. |
| AddConditionalFormat overloads | Adds a cellIs conditional-formatting rule.
Equivalent to ConditionalFormats.Add(...). |
| AddDataValidation overloads / AddListValidation | Adds a data-validation rule.
AddListValidation(Range, Items) wraps a comma-separated
string literal (e.g. 'Yes,No,Maybe') for the common
dropdown case. Equivalent to
DataValidations.Add(...) / AddList(...). |
var
ws: TXLSXWorksheet;
begin
ws := Workbook.Sheets.Add('Demo');
ws.Cells.Item[1, 1].Value := 'Title';
ws.MergeCells(1, 1, 1, 3);
ws.ColWidth[1] := 24; // widen column A
ws.RowHeight[1] := 28; // taller title row
ws.Cells.Item[3, 1].Value := 'Visit losLab';
ws.AddHyperlink(3, 1, 'https://www.loslab.com', 'losLab');
ws.AddComment(3, 1, 'Vendor home page', 'Kevin');
end;