(2020-05-13) Reading excel files (by FireMonkey on Desktop & Mobile)

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/​Fire​Monkey Desktop/​Modules/​20.​Reading Files

 

Overview

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

 

Concepts Shown:

* Excel 파일을 읽으려면 TXlsFile 클래스를 사용하면 Excel 97 이상 파일을 읽고 쓸 수 있습니다.

* 단일 셀의 값을 얻으려면 TXlsFile.CellValue를 사용하십시오.

* 전체 시트를 반복 할 때 셀의 값을 얻으려면 TXlsFile.GetCellValueIndexed를 사용하십시오. 데이터가있는 셀로만 이동하므로 모든 열을 반복하는 것보다 빠릅니다.

* CellValue는 Excel 셀에서 허용되는 개체 중 하나 일 수있는 TCellValue를 반환합니다.

* GetCellValue 및 GetCellValueIndexed를 사용하면 실제 값을 얻을 수 있지만 실제로 서식이 지정된 데이터를 표시하려면 (예를 들어, 숫자가 2 인 소수점 2가 있고 2 대신 2.00을 표시하려는 경우) 다른 방법을 사용해야합니다. 실제로 두 가지 방법이 있습니다.

* TXlsFile.GetStringFromCell은 셀 서식이 지정된 서식있는 문자열을 반환합니다.

* TFlxNumberFormat.FormatValue는 지정된 형식으로 오브젝트를 형식화 한 다음 해당 리치 문자열을 리턴합니다.

  TFlxNumberFormat.FormatValue는 GetStringFromCell에 의해 내부적으로 사용됩니다.

* Excel에서 Dates는 double이며, 날짜와 double의 유일한 차이점은 셀의 형식에 있으며 TFlxNumberFormat.FormatValue를 사용하면 Excel에 표시되는 실제 문자열을 얻을 수 있으며이 double을 DateTime으로 변환하려면 FlxDateTime.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}FMX.Grid.Style, {$IFEND}
  FMX.FlexCel.Core, FlexCel.XlsAdapter, FMX.Edit;

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);
  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.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.