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;
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;
// 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 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;
end;
| Name | Worksheet tab name as shown by Excel. |
| Cells | Cell collection. Use Cells.Item[Row, Col].Value.
See TXLSXCells / TXLSXCell. |
| 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 color 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. |
| 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. |
| 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). |
| 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 customized
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
customized width or height for the given index. Use this to
distinguish "unset" from a deliberate 0 value. |
| ClearColWidths / ClearRowHeights | Drops every customized 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;