Reading Excel files (Delphi)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\10.API\20.Reading Files and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​VCL-​demos/​tree/​master/​Delphi/​Modules/​10.​API/​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
  Windows, Messages, SysUtils, Variants, Classes, Graphics,
  Controls, Forms, Dialogs, ImgList, ActnList, StdCtrls,
  Tabs, Grids,ExtCtrls, ComCtrls, ToolWin, UPaths,
  FlexCel.VCLSupport, FlexCel.Core, FlexCel.XlsAdapter;

type
  TFReadingFiles = class(TForm)
    ToolBar1: TToolBar;
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    ToolButton3: TToolButton;
    ToolButton5: TToolButton;
    ToolButton4: TToolButton;
    ToolButton6: TToolButton;
    SheetData: TStringGrid;
    Tabs: TTabSet;
    Panel2: TPanel;
    Label1: TLabel;
    FmtBox: TEdit;
    Actions: TActionList;
    ActionOpen: TAction;
    ActionValueInCurrentCell: TAction;
    ActionInfo: TAction;
    ActionClose: TAction;
    OpenDialog: TOpenDialog;
    ToolbarImages: TImageList;
    StatusBar: TStatusBar;
    ToolButton7: TToolButton;
    ActionFormatValues: TAction;
    ToolbarImages_300Scale: TImageList;
    ToolbarImages_100Scale: TImageList;
    Panel1: TPanel;
    procedure ActionCloseExecute(Sender: TObject);
    procedure ActionInfoExecute(Sender: TObject);
    procedure ActionOpenExecute(Sender: TObject);
    procedure ActionValueInCurrentCellExecute(Sender: TObject);
    procedure SheetDataSelectCell(Sender: TObject; ACol, ARow: Integer;
      var CanSelect: Boolean);
    procedure ActionFormatValuesExecute(Sender: TObject);
    procedure TabsClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    UpdatingCount, UpdatingCellsCount: integer;
    Xls: TExcelFile;

    procedure ImportFile(const FileName: string);
    procedure FillTabs;
    procedure FillGrid(const Formatted: boolean);
    procedure SelectedCell(const aCol, aRow: integer);
    procedure AnalizeFile(const Row, Col: integer);
    function FormatValue(const v: TCellValue; const Row,
      Col: integer): String;
    procedure ClearGrid;
    procedure ResizeGrid;
    procedure BeginUpdatingGrid;
    procedure EndUpdatingGrid;
    procedure BeginUpdatingGridCells;
    procedure EndUpdatingGridCells;
    { Private declarations }
  public
    destructor Destroy; override;
    { Public declarations }
  end;

var
  FReadingFiles: TFReadingFiles;

implementation
uses UFlexCelHDPI;

{$R *.dfm}

procedure TFReadingFiles.ActionCloseExecute(Sender: TObject);
begin
  Close;
end;

procedure TFReadingFiles.ActionFormatValuesExecute(Sender: TObject);
begin
  ActionFormatValues.Checked := not ActionFormatValues.Checked;
  FillGrid(ActionFormatValues.Checked);
end;

procedure TFReadingFiles.ActionInfoExecute(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.ActionOpenExecute(Sender: TObject);
begin
  if not OpenDialog.Execute then exit;
  ImportFile(OpenDialog.FileName);
end;

procedure TFReadingFiles.ActionValueInCurrentCellExecute(Sender: TObject);
begin
  AnalizeFile(SheetData.Row, SheetData.Col);
end;

destructor TFReadingFiles.Destroy;
begin
  FreeAndNil(Xls);
  inherited;
end;

procedure TFReadingFiles.SheetDataSelectCell(Sender: TObject; ACol,
  ARow: Integer; var CanSelect: Boolean);
begin
  SelectedCell(aCol, aRow);
  CanSelect := true;
end;

procedure TFReadingFiles.TabsClick(Sender: TObject);
begin
  FillGrid(ActionFormatValues.Checked);
end;

procedure TFReadingFiles.FillTabs;
var
  s: integer;
begin

  Tabs.Tabs.Clear;
  for s := 1 to Xls.SheetCount do
  begin
    Tabs.Tabs.Add(Xls.GetSheetName(s));
  end;
end;


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

  FillTabs;
  Tabs.TabIndex := Xls.ActiveSheet - 1;

  EndFill := Now;
  StatusBar.SimpleText := 'Time to load file: '+  ElapsedTime(EndOpen, StartOpen) + '     Time to load file and fill grid: ' + ElapsedTime(EndFill, StartOpen);
  Caption := 'Reading Files: ' + ExtractFileName(FileName);
end;

procedure TFReadingFiles.ClearGrid;
var
  r: integer;
begin
  BeginUpdatingGrid;
  try
    for r := 1 to SheetData.RowCount do SheetData.Rows[r].Clear;
  finally
    EndUpdatingGrid;
  end;
end;

procedure TFReadingFiles.BeginUpdatingGrid;
begin
  Inc(UpdatingCount);
  if (UpdatingCount = 1) then
  begin
    SendMessage(SheetData.Handle, WM_SETREDRAW, 0, 0);
  end;
end;

procedure TFReadingFiles.EndUpdatingGrid;
begin
  Dec(UpdatingCount);
  if (UpdatingCount = 0) then
  begin
    SendMessage(SheetData.Handle, WM_SETREDRAW, 1, 0);
    RedrawWindow(SheetData.Handle, nil, 0, RDW_ERASE or RDW_FRAME or RDW_INVALIDATE or RDW_ALLCHILDREN);
  end;
end;

procedure TFReadingFiles.BeginUpdatingGridCells;
var
  c: integer;
begin
  Inc(UpdatingCellsCount);
  if (UpdatingCellsCount = 1) then
  begin
    for c := 1 to SheetData.ColCount do SheetData.Cols[c].BeginUpdate;
  end;
end;

procedure TFReadingFiles.EndUpdatingGridCells;
var
  c: integer;
begin
  Dec(UpdatingCellsCount);
  if (UpdatingCellsCount = 0) then
  begin
    for c := 1 to SheetData.ColCount do SheetData.Cols[c].EndUpdate;
  end;
end;

procedure TFReadingFiles.FillGrid(const Formatted: boolean);
var
  r, c, cIndex: Integer;
  v: TCellValue;
begin
  if Xls = nil then exit;
  BeginUpdatingGrid;
  try
    if (Tabs.TabIndex + 1 <= Xls.SheetCount) and (Tabs.TabIndex >= 0) then Xls.ActiveSheet := Tabs.TabIndex + 1 else Xls.ActiveSheet := 1;
    //Clear data in previous grid
    ClearGrid;
    SheetData.RowCount := 1;
    SheetData.ColCount := 1;
    FmtBox.Text := '';

    SheetData.RowCount := Xls.RowCount + 1; //Include fixed row
    SheetData.ColCount := Xls.ColCount + 1; //Include fixed col. NOTE THAT COLCOUNT IS SLOW. We use it here because we really need it. See the Performance.pdf doc.

    BeginUpdatingGridCells;
    try

      if (SheetData.ColCount > 1) then SheetData.FixedCols := 1; //it is deleted when we set the width to 1.
      if (SheetData.RowCount > 1) then SheetData.FixedRows := 1;

      for r := 1 to Xls.RowCount do
      begin
        //Instead of looping in all the columns, we will just loop in the ones that have data. This is much faster.
        for cIndex := 1 to Xls.ColCountInRow(r) do
        begin
          c := Xls.ColFromIndex(r, cIndex); //The real column.
          if Formatted then
          begin
            SheetData.Cells[c, r] := Xls.GetStringFromCell(r, c);
          end
          else
          begin
            v := Xls.GetCellValue(r, c);
            SheetData.Cells[c, r] := v.ToString;
          end;
        end;
      end;

      ResizeGrid;
      SelectedCell(1,1);
    finally
      EndUpdatingGridCells;
    end;
  finally
    EndUpdatingGrid;
  end;

end;

procedure TFReadingFiles.ResizeGrid;
var
  r,c: integer;
begin
  BeginUpdatingGrid;
  try
    BeginUpdatingGridCells;
    try
      if Xls = nil then
      begin
        SheetData.DefaultColWidth := Round(64.0 * Font.PixelsPerInch / 96.0);
        SheetData.DefaultRowHeight := Round(18.0 * Font.PixelsPerInch / 96.0);
        exit;
      end;
      if (Tabs.TabIndex + 1 <= Xls.SheetCount) and (Tabs.TabIndex >= 0) then Xls.ActiveSheet := Tabs.TabIndex + 1 else Xls.ActiveSheet := 1;

      SheetData.RowHeights[0] := Round(20 * Font.PixelsPerInch / 96.0);
      SheetData.ColWidths[0] := Round(50 * Font.PixelsPerInch / 96.0);

      //Fill the row headers
      for r := 1 to SheetData.RowCount - 1 do
      begin
        SheetData.Cells[0, r] := IntToStr(r);
        SheetData.RowHeights[r] := Round(Xls.GetRowHeight(r) / TExcelMetrics.RowMultDisplay(Xls) * Font.PixelsPerInch / 96.0);
      end;

      //Fill the column headers
      for c := 1 to SheetData.ColCount - 1 do
      begin
        SheetData.Cells[c, 0] := TCellAddress.EncodeColumn(c);
        SheetData.ColWidths[c] := Round(Xls.GetColWidth(c) / TExcelMetrics.ColMult(Xls) * Font.PixelsPerInch / 96.0);
      end;
    finally
      EndUpdatingGridCells;
    end;
  finally
    EndUpdatingGrid;
  end;
end;


procedure TFReadingFiles.SelectedCell(const aCol, aRow: integer);
var
  Fmt: TFlxFormat;
begin
  if  Xls = nil then exit;
  if (aRow < 1) or (aCol < 1) then exit;

  Fmt := Xls.GetCellVisibleFormatDef(aRow, aCol);
  FmtBox.Text := Fmt.Format;
end;


procedure TFReadingFiles.AnalizeFile(const Row, Col: integer);
var
  v: TCellValue;
  Fmla: TFormula;
begin
  if (Xls = nil) then
  begin
    ShowMessage('You need to open a file first');
    exit;
  end;

  if (Row < 1) or (Col < 1) or (Row > SheetData.RowCount - 1) or (Col > SheetData.ColCount - 1) then
  begin
    ShowMessage('The selected cell is not valid.');
    exit;
  end;


  ShowMessage(('Active sheet is "' + xls.SheetName) + '"');
  v := xls.GetCellValue(Row, Col);

  //First see if it is a formula
  if v.IsFormula then
  begin
    Fmla := v.AsFormula;
    ShowMessage('Cell ' + TCellAddress.Create(Row, Col).CellRef + ' contains the Formula: ' + Fmla.Text + #10 +
                'The result of the formula is ' + FormatValue(Fmla.FormulaResult, Row, Col));
    exit;
  end;

  ShowMessage('Cell ' + TCellAddress.Create(Row, Col).CellRef + ' is ' + FormatValue(v, Row, Col));
end;

function TFReadingFiles.FormatValue(const v: TCellValue; const Row, Col: integer): String;
var
  CellValue: String;
  Formatted: string;
  HasDate, HasTime: boolean;
  CellColor: TUIColor;
begin

  case v.ValueType of
  TCellValueType.Empty: exit('empty');
  TCellValueType.Boolean: exit('a boolean: ' + BoolToStr(v.AsBoolean, true));
  TCellValueType.Error: exit('an error: ' + TFormulaMessages.ErrString(v.AsError));

  TCellValueType.Number:
    begin  //Remember, dates are doubles with date format. Also, all numbers are returned as doubles, even if they are integers.
      CellColor := TUIColor.Empty;
      CellValue := TFlxNumberFormat.FormatValue(v, xls.GetCellVisibleFormatDef(Row, Col).Format, CellColor, xls, HasDate, HasTime).ToString;
      if HasDate or HasTime then
      begin
        Result := 'a DateTime value: ' + DateTimeToStr(v.ToDateTime(xls.OptionsDates1904)) + #10 + 'The value is displayed as: ' + CellValue;
      end
      else
      begin
        Result := 'a double: ' + FloatToStr(v.AsNumber) + #10 + 'The value is displayed as: ' + CellValue + #10;
      end;
      exit;
    end;

  TCellValueType.DateTime:  //FlexCel won't currently return DateTime values, as dates are numbers.
  begin
    exit('a DateTime value: ' + DateTimeToStr(v.AsDateTime));
  end;

  TCellValueType.StringValue:
    begin
      if v.AsString.RTFRunCount > 0 then Formatted := ' FORMATTED ' else Formatted := ' ';

      Result := 'a' + Formatted +'string: ' + v.AsString.ToString
      + #10 + 'In html: ' + v.AsString.ToHtml(xls, xls.GetCellVisibleFormatDef(Row, Col), THtmlVersion.Html_32, THtmlStyle.Simple, TEncoding.UTF8);
      exit;
    end;
  end;

  raise Exception.Create('Unexpected value on cell');
end;


procedure TFReadingFiles.FormCreate(Sender: TObject);
begin
  RegisterForHDPI(Self, ResizeGrid);
end;

end.