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:
TXlsFile.GetStringFromCell will return a rich string with the cell formatted.
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.