Reading Excel files (FireMonkey Desktop)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\FireMonkey Desktop\Modules\20.Reading Files and also at https://github.com/tmssoftware/TMS-FlexCel.VCL-demos/tree/master/FireMonkey Desktop/Modules/20.Reading Files

Overview

A demo showing how to read the contents of an Excel file using FlexCel.

Concepts

To read an Excel file you use the TXlsFile class, from where you can read and write to any Excel 2.0 or newer file.
To get the value for a single cell, use TXlsFile.GetCellValue.
To get the value for a cell when looping a full sheet, use TXlsFile.GetCellValueIndexed. It is faster than using GetCellValue since you will only read the used cells.
TXlsFile.GetCellValue and TXlsFile.GetCellValueIndexed will return a TCellValue that will be one of the objects allowed in an Excel cell
With GetCellValue and GetCellValueIndexed you will get the actual values. But if you want to actually display formatted data (for example if you have the number 2 with 2 decimals, and you want to display 2.00 instead of 2), you need to use other methods. There are 2 ways to do it:
1. TXlsFile.GetStringFromCell will return a rich string with the cell formatted.
2. FormatValue will format an object with a specified format and then return the corresponding rich string. TFlxNumberFormat.FormatValue is used internally by GetStringFromCell.
In Excel, Dates are doubles. The only difference between a date and a double is on the format on the cell. With FormatValue you can get the actual string that is displayed on Excel. Also, to convert this double to a DateTime, you can use TFlxDateTime.FromOADate.

Files

UReadingFiles.pas

unit UReadingFiles;

interface

uses
  System.SysUtils, System.Types, System.UITypes, System.Classes, System.Variants,
  FMX.Types, FMX.StdCtrls, FMX.Controls, FMX.Forms, FMX.Dialogs, FMX.Layouts, FMX.Grid,
  FMX.TabControl, FMX.Objects, System.Math, System.Rtti,
  {$if CompilerVersion < 31.0}{$else}FMX.Grid.Style, {$IFEND}
  FlexCel.FMXSupport, FlexCel.Core, FlexCel.XlsAdapter, FMX.Edit, FMX.Graphics,
  FMX.ScrollBox, FMX.Controls.Presentation;

type
  TFReadingFiles = class(TForm)
    ToolBar1: TToolBar;
    OpenDialog: TOpenDialog;
    btnOpen: TButton;
    SheetData: TGrid;
    Image1: TImage;
    btnFormatValues: TButton;
    Image2: TImage;
    btnInfo: TButton;
    Image4: TImage;
    Tabs: TTabControl;
    procedure btnInfoClick(Sender: TObject);
    procedure btnOpenClick(Sender: TObject);
    procedure SheetDataGetValue(Sender: TObject; const Col, Row: Integer;
      var Value: TValue);
    procedure btnFormatValuesClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure SheetDataDrawColumnCell(Sender: TObject; const Canvas: TCanvas;
      const Column: TColumn; const Bounds: TRectF; const Row: Integer;
      const Value: TValue; const State: TGridDrawStates);
  private
    Xls: TExcelFile;
    procedure ClearGrid;
    procedure SetupGrid;
    procedure ImportFile(const FileName: string);
    procedure FillTabs;
    procedure SheetChanged(Sender: TObject);
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FReadingFiles: TFReadingFiles;

implementation

{$R *.fmx}

procedure TFReadingFiles.btnFormatValuesClick(Sender: TObject);
begin
  SheetData.Repaint; //when repainting, we will read the new value of this button.
end;

procedure TFReadingFiles.btnInfoClick(Sender: TObject);
begin
  ShowMessage('This demo shows how to read the contents of an xls file' + #10 +
      'The ''Open File'' button will load an Excel file into a dataset.'+ #10 +
      'The ''Format Values'' button will apply the format to the cells, or show the raw data.'+ #10 +
      'The ''Value in Current Cell'' button will show more information about the cell selected in the grid. Try it with formulas.');

end;

procedure TFReadingFiles.btnOpenClick(Sender: TObject);
begin
  if not OpenDialog.Execute then exit;
  ImportFile(OpenDialog.FileName);
end;

procedure TFReadingFiles.ImportFile(const FileName: string);
begin
   //Open the Excel file.
  if Xls = nil then Xls := TXlsFile.Create(false);
  xls.Open(FileName);

  FillTabs;
  SetupGrid;

  Caption := 'Reading Files: ' + ExtractFileName(FileName);
end;

procedure TFReadingFiles.SheetChanged(Sender: TObject);
begin
  Xls.ActiveSheet := (Sender as TComponent).Tag;
  SetupGrid;
end;

procedure TFReadingFiles.SheetDataDrawColumnCell(Sender: TObject;
  const Canvas: TCanvas; const Column: TColumn; const Bounds: TRectF;
  const Row: Integer; const Value: TValue; const State: TGridDrawStates);
var
  fmt: TFlxFormat;
  FillBrush: TBrush;
  BoundsExt: TRectF;
begin
   //Here we will show how to do colors
   if Xls = nil then exit;
   BoundsExt := Bounds;
   BoundsExt.Inflate(4, 4);
   fmt := Xls.GetCellVisibleFormatDef(Row + 1, Column.Index + 1);

   if (fmt.FillPattern.Pattern = TFlxPatternStyle.Solid) then
   begin
      FillBrush := TBrush.Create(TBrushKind.Solid, fmt.FillPattern.FgColor.ToColor(xls));
      try
        Canvas.FillRect(BoundsExt, 0, 0, [], 1, FillBrush);
      finally
        FillBrush.Free;
      end;
      Canvas.Font.Size := fmt.Font.Size20 / 20.0 * 96.0/ 72.0;  //Firemonkey's Font.size is smaller than in VCL. So we multiply by 96/72.
      Canvas.Font.Family := fmt.Font.Name;
      //You could assign the font style here too.

      Canvas.Fill.Color := fmt.Font.Color.ToColor(xls);
      Canvas.FillText(Bounds, Xls.GetStringFromCell(Row + 1, Column.Index + 1).ToString,
         fmt.WrapText, 1, [], TTextAlign.Leading);
   end;
end;

procedure TFReadingFiles.SheetDataGetValue(Sender: TObject; const Col,
  Row: Integer; var Value: TValue);
begin
  if Xls = nil then
  begin
    Value := '';
    exit;
  end;

  if btnFormatValues.IsPressed then
  begin
    value := Xls.GetStringFromCell(Row + 1, Col + 1).ToString;
  end
  else
  begin
    value := Xls.GetCellValue(Row + 1, Col + 1);
  end;
end;

procedure TFReadingFiles.FillTabs;
var
  s, i: integer;
  btn: TTabItem;
begin
  for i := Tabs.TabCount - 1 downto 0 do Tabs.Tabs[i].Free;

  for s := 1 to Xls.SheetCount do
  begin
    btn := TTabItem.Create(Tabs);
    btn.Text := Xls.GetSheetName(s);
    btn.Tag := s;

    btn.OnClick := SheetChanged;
    Tabs.AddObject(btn);
  end;
end;


procedure TFReadingFiles.FormDestroy(Sender: TObject);
begin
  Xls.Free;
end;

procedure TFReadingFiles.ClearGrid;
var
  i: integer;
begin
  SheetData.RowCount := 0;
  for i := SheetData.ColumnCount - 1 downto 0 do SheetData.Columns[i].Free;

end;

procedure TFReadingFiles.SetupGrid;
var
  ColCount: integer;
  Column: TColumn;
  c: Integer;
begin
  SheetData.BeginUpdate;
  try
    ClearGrid;

    SheetData.RowCount := Xls.RowCount;
    ColCount := Xls.ColCount; // NOTE THAT COLCOUNT IS SLOW. We use it here because we really need it. See the Performance.pdf doc.
    //Create the columns
    for c := 1 to ColCount do
    begin
      Column := TColumn.Create(SheetData);
      Column.Width := Xls.GetColWidth(c) / TExcelMetrics.ColMult(Xls);
      Column.Header := TCellAddress.EncodeColumn(c);
      Column.Parent := SheetData;
    end;
  finally
    SheetData.EndUpdate;
  end;

  SheetData.Repaint;
end;

end.

Table of Contents

Reading Excel files (FireMonkey Desktop)

Note

Overview

Concepts

Files

UReadingFiles.pas