HotXLS ドキュメント
ホーム 目次 TXLSXWorksheet

TXLSXRange クラス

ユニット: lxHandleX
TXLSXWorksheet.Range、RCRange、UsedRange、EntireRow、またはEntireColumnによって返される矩形のXLSXワークシート範囲を表します。範囲はワークシートに対する軽量なビューであり、セルデータ自体は所有しません

Declaration

TXLSXBuiltinCellStyle = (
  xbsNormal, xbsGood, xbsBad, xbsNeutral,
  xbsCalculation, xbsCheckCell, xbsExplanatoryText, xbsInput,
  xbsLinkedCell, xbsNote, xbsOutput, xbsWarningText,
  xbsHeading1, xbsHeading2, xbsHeading3, xbsHeading4, xbsTitle, xbsTotal,
  xbsAccent1, xbsAccent2, xbsAccent3, xbsAccent4, xbsAccent5, xbsAccent6);

TXLSXRange = class
  constructor Create(ASheet: TXLSXWorksheet; ARow1, ACol1, ARow2, ACol2: Integer);
  function RefA1: WideString;
  function SaveAsHTML(const FileName: WideString): Integer; overload;
  function SaveAsHTML(const FileName: WideString; Options: TXLSXHtmlExportOptions): Integer; overload;
  function SaveAsHTML(Stream: TStream): Integer; overload;
  function SaveAsHTML(Stream: TStream; Options: TXLSXHtmlExportOptions): Integer; overload;

  property Sheet: TXLSXWorksheet;
  property Row1, Col1, Row2, Col2: Integer;
  property RowCount, ColCount: Integer;

  procedure SetValue(const V: Variant);
  procedure SetFormula(const F: WideString);
  function SetArrayFormula(const F: WideString): Integer;
  procedure ClearValues;
  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(AMinWidth: Double = 0; AMaxWidth: Double = 0);
  procedure AutoFitRows(AMinHeight: Double = 0; AMaxHeight: Double = 0);
  procedure Merge; overload;
  procedure Merge(Across: OleVariant); overload;
  procedure Unmerge;
  function Offset(DRow, DCol: Integer): TXLSXRange;
  function Resize(NewRowCount, NewColCount: Integer): TXLSXRange;
  procedure Sort(AKeyColumn: Integer; ADescending: Boolean = False); overload;
  procedure Sort(const AKeyColumns: array of Integer; const ADescending: array of Boolean); overload;
  procedure ApplyBuiltinStyle(AStyle: TXLSXBuiltinCellStyle);
  procedure CopyTo(ADestRow, ADestCol: Integer);
  procedure MoveTo(ADestRow, ADestCol: Integer);
  procedure InsertRows;
  procedure DeleteRows;
  procedure InsertCols;
  procedure DeleteCols;
  procedure ApplyFormatPatch(ASource: TXLSXRange; AParts: TXLSXFormatParts);
  function CopyToClipboard: Boolean;
  function SaveAsPDF(const FileName: WideString): Integer; overload;
  function SaveAsPDF(Stream: TStream): Integer; overload;
  function SetDynamicArrayFormula(const F: WideString): Integer;
end;

Members

Member Description
Create(ASheet, ARow1, ACol1, ARow2, ACol2) Builds a new worksheet-owned range and normalizes reversed corners so Row1/Col1 are always the upper-left coordinate (English)
RefA1 返します the range reference as A1 text. Single-cell ranges return a single cell reference such as B2 (English)
SaveAsHTML Options overloads accept TXLSXHtmlExportOptions for Title, WriteDocument, IncludeStyles, IncludeBOM, and TableClass. Set WriteDocument to False for a table fragment; set IncludeBOM to False を指定すると、既存の UTF-8 レスポンスに出力を埋め込めます。選択範囲の HTML エクスポートでも同じオプションを使えます。エクスポート出力はセルのハイパーリンクを <a> anchors and anchored images as inline data: 選択矩形に含まれる画像 URI を出力します。選択矩形に含まれるセル コメントも、エスケープされた <td> title attributes. 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 (English)
SetValue / SetFormula Writes the same value or formula text to every cell in the range through the worksheet cell collection (English)
SetArrayFormula Sets a multi-cell CSE array formula over the range. The top-left cell stores the formula text and anchors the spill range, while the remaining cells point back to that root so each cell evaluates its own element of the matrix result. Pass formula text without the surrounding braces (English)
ClearValues / Clear / ClearAll ClearValues and Clear remove values and formulas while preserving style indexes. ClearAll also clears style indexes (English)
SetFontIndex / SetFillIndex / SetBorderIndex / SetNumberFormatIndex / SetAlignmentIndex Applies 1-based workbook palette indexes to each cell in the range. Pass 0 to clear the corresponding per-cell index (English)
SetNumberFormat Looks up or appends a number-format string in the workbook palette, then applies the resulting 1-based index to the range. An empty format clears the per-cell index (English)
SetBorders Composes border edges over the range. TXLSXBorderEdgeKind accepts xlsxEdgeAll, xlsxEdgeOutline, xlsxEdgeInside, xlsxEdgeInsideHorizontal, xlsxEdgeInsideVertical, xlsxEdgeTop, xlsxEdgeBottom, xlsxEdgeLeft, xlsxEdgeRight, xlsxEdgeDiagonalUp, and xlsxEdgeDiagonalDown. Use outline and inside edge selectors to build table borders without replacing unrelated edge settings (English)
SetLocked / SetFormulaHidden Applies cell-protection flags to every cell in the range by reusing the workbook protection-style pool (English)
AutoFitColumns / AutoFitRows Forwards to the worksheet auto-fit helpers over the range's column or row bounds. Optional width and height limits are passed through to keep report layouts inside design bounds (English)
Merge / Unmerge Merge and Merge(False) create one merged rectangle. Merge(True) creates one merged range per row, matching Excel's merge-across behavior. Unmerge removes an exact merged-range match (English)
Offset / Resize 返します another worksheet-owned range shifted from the current bounds or resized to the requested row and column count (English)
Sort この範囲内の行を、1 つのキー列で 並べ替えるか、複数のキー列を優先順に並べ替えます。 ADescending selects ascending or descending order per key; omitted entries default to ascending. Blank values sort last (English)
ApplyBuiltinStyle Applies the visible appearance of an Excel built-in cell style to every cell in the range. The helper writes direct formatting by adding the matching font and fill to the workbook pools and assigning the resulting indexes to the cells (English)
CopyTo / MoveTo Copies or moves the whole range to a destination top-left cell. Values, formulas, rich-text payloads, style indexes, comments, hyperlinks, and drawing anchors are forwarded through the worksheet range-copy and range-move helpers (English)
InsertRows / DeleteRows / InsertCols / DeleteCols Performs whole-row or whole-column insertion and deletion over the range bounds. These helpers forward to the worksheet row/column shift operations so formulas, references, filters, page setup ranges, chart ranges, names, images, and chart anchors stay aligned with the edited grid (English)
ApplyFormatPatch Applies selective formatting styles from another source range to this range using TXLSXFormatParts flags (English)
CopyToClipboard Copies the range values and styles to the Windows clipboard
SaveAsPDF Exports the cell range as a PDF file or stream using the in-process PDF rendering engine (English)
SetDynamicArrayFormula Sets a dynamic array formula over the range, supporting Excel's dynamic spill behavior (English)

Ownership and bounds

範囲はそれを作成したワークシートによって所有され、そのワークシートと一緒に解放されます。アプリケーションコードはTXLSXRangeのFreeを呼び出すべきではなく、ワークシートまたはワークブックが破棄された後に範囲の参照を保持するべきではありません
数値座標は1ベースです。範囲を作成すると、反転した角が正規化され、境界がXLSXワークシートの制限内にクランプされます

例

var
  Wb: TXLSXWorkbook;
  Ws: TXLSXWorksheet;
  Body: TXLSXRange;
begin
  Wb := TXLSXWorkbook.Create;
  try
    Ws := Wb.Sheets.Add('Report');
    Body := Ws.Range['B2:D20'];
    Body.SetNumberFormat('#,##0.00');
    Body.SetBorders(xlsxEdgeOutline, xlsxBorderThin, $FF808080);
    Body.Sort(2);
    Body.ApplyBuiltinStyle(xbsAccent1);
    Ws.Range['F2:G3'].SetArrayFormula('MMULT(A2:B3,D2:E3)');
    Body.AutoFitColumns;
    Body.SaveAsHTML('report-fragment.html');
  finally
    Wb.Free;
  end;
end;

関連項目

TXLSXWorksheet クラス
TXLSXWorkbook クラス
XLSXファサードの概要