Formatting cells in a table

Cell formatting refers to setting the Color, Font, Separating Lines, Alignment, Indent, Digital format for one or more data cells.

The cells are formatted using the GetCellsRange method and the IXlsFileCellsRangeEh interface.

Using the Sheet.GetCellsRange method, you need to get a reference to the specified array of cells in the table (GetCellsRange returns an array of cells as a reference to the IXlsFileCellsRangeEh interface).

Then, use the methods of the IXlsFileCellsRangeEh interface to perform the cell format changes and call the IXlsFileCellsRangeEh.ApplyChages method to apply the changes to the table.

The following code gets a reference to a range of four cells (LeftCol = 0, TopRow = 0, ColCount = 1, RowCount = 1) and sets the font size for the selected range to 24.

Demo1

var
  cr: IXlsFileCellsRangeEh;
begin
  cr := XlsFile.Workbook.Worksheets[0].GetCellsRange(0, 0, 1, 1);
  cr.Font.Size := 24;
  cr.ApplyChages;
end;

Demo2

  cr := Sheet.GetCellsRange(0,4,6,i+4);
  cr.Border.Top.Style := clsMediumEh;
  cr.Border.Bottom.Style := clsMediumEh;
  cr.Border.Left.Style := clsMediumEh;
  cr.Border.Right.Style := clsMediumEh;

  cr.InsideBorder.Top.Style := clsThinEh;
  cr.InsideBorder.Bottom.Style := clsThinEh;
  cr.InsideBorder.Left.Style := clsThinEh;
  cr.InsideBorder.Right.Style := clsThinEh;

  cr.NumberFormat := '#,##0.0000';
  cr.VertAlign := cvaCenterEh;
  cr.HorzAlign := chaCenterEh;
  AFont := DBVertGridEh1.VisibleFieldRow[i].Font;
  cr.Font.Name := AFont.Name;
  cr.Font.Size := AFont.Size;
  cr.Font.Color := AFont.Color;
  cr.Font.IsBold := fsBold in AFont.Style;
  cr.Font.IsItalic := fsItalic in AFont.Style;
  cr.Font.IsUnderline := fsUnderline in AFont.Style;

  cr.ApplyChages;

Use the following IXlsFileCellsRangeEh interface properties to set other format properties:

property Font: TXlsFileCellsRangeFontEh;

Sets the font properties for the selected range.

property Fill: TXlsFileCellsRangeFillEh;

Sets the cell shading properties for the selected range.

property Border: TXlsFileCellsRangeLinesEh;

Sets the properties of the colors and the width of the dividing lines of the cells for the selected range.

property HorzAlign: TXlsFileCellHorzAlign;

Sets the horizontal text in cells for the selected range.

property VertAlign: TXlsFileCellVertAlign;

Specifies the vertical alignment of text in cells for the selected range.

property WrapText: Boolean;

Specifies whether the text should be wrapped but a new line if the text does not fit into the width of the cell.

property Rotation: Integer; //Degrees

Specifies the rotation of the text.

property Indent: Integer;

Sets the indentation of the text to the left inside the cell.

property CharsFlowDirection: TXlsFileCharsFlowDirectionEh;

Specifies how the letters of the text are positioned relative to each other.

property NumberFormat: String read GetNumberFormat write SetNumberFormat;

Specifies the number format for cells that contain numbers or date / time. The format is set according to the rules of MS Excel. For a description of the number format, see the MS Excel documentation. https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68