uxlsviewer.~pas

Delphi/BCB 各种版本都支持的Excel 读写控件.一成功应用在N个项目中 .

unit UXlsViewer;
{$IFDEF LINUX}{$INCLUDE ../../FLXCONFIG.INC}{$ELSE}{$INCLUDE ..\..\FLXCONFIG.INC}{$ENDIF}
interface

uses
  SysUtils, Classes,
  {$IFDEF FLX_VCL}
    Graphics, Controls, Forms, Dialogs, Menus, ActnList,
    StdCtrls, ComCtrls, ImgList, ToolWin, ExtCtrls, Grids, Printers,
  {$ENDIF}
  {$IFDEF FLX_CLX}
    QGraphics, QControls, QForms, QDialogs, QMenus, QActnList,
    QStdCtrls, QComCtrls, QImgList, QTypes, QExtCtrls, QGrids, QPrinters,
  {$ENDIF}

  UExcelAdapter, XLSAdapter,
  UFlexCelImport, UFlexCelGrid, UFormatDialog, UWaitCursor, UFlxMessages, UPreview;

type
  TMain = class(TForm)
    ImageList1: TImageList;
    ImageList2: TImageList;
    ImageList3: TImageList;
    FlexCelImport: TFlexCelImport;
    XLSAdapter: TXLSAdapter;
    MainMenu1: TMainMenu;
    File2: TMenuItem;
    ReadOnly1: TMenuItem;
    ReadOnly2: TMenuItem;
    N2: TMenuItem;
    Close1: TMenuItem;
    View1: TMenuItem;
    Zoom1: TMenuItem;
    N102: TMenuItem;
    N252: TMenuItem;
    N502: TMenuItem;
    N1002: TMenuItem;
    N2002: TMenuItem;
    N4002: TMenuItem;
    N3: TMenuItem;
    FullWorksheet1: TMenuItem;
    Format1: TMenuItem;
    CopyFormat1: TMenuItem;
    PasteFormat1: TMenuItem;
    N1: TMenuItem;
    CellFormat1: TMenuItem;
    ColFormat1: TMenuItem;
    ColFormat2: TMenuItem;
    ActionList1: TActionList;
    ActionZoom: TAction;
    Action10: TAction;
    Action25: TAction;
    ActionCopyFormat: TAction;
    ActionPasteFormat: TAction;
    ActionCellFormat: TAction;
    Action50: TAction;
    ActionRowFormat: TAction;
    ActionColFormat: TAction;
    ActionReadOnly: TAction;
    ActionSaveAs: TAction;
    ActionClose: TAction;
    Action100: TAction;
    Action200: TAction;
    Action400: TAction;
    ActionFullWorksheet: TAction;
    MenuZoom: TPopupMenu;
    N101: TMenuItem;
    N251: TMenuItem;
    N501: TMenuItem;
    N1001: TMenuItem;
    N2001: TMenuItem;
    N4001: TMenuItem;
    TabControl: TTabControl;
    Data: TFlexCelGrid;
    XlsSaveDialog: TSaveDialog;
    ActionOpen: TAction;
    Open1: TMenuItem;
    N4: TMenuItem;
    OpenDialog: TOpenDialog;
    ToolBar1: TToolBar;
    ToolButton13: TToolButton;
    ToolButton15: TToolButton;
    ToolButton16: TToolButton;
    ToolButton14: TToolButton;
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    ToolButton3: TToolButton;
    ToolButton4: TToolButton;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    ToolButton9: TToolButton;
    ToolButton8: TToolButton;
    TrackBarZoom: TTrackBar;
    PanelZoom: TPanel;
    ToolButton10: TToolButton;
    Panel1: TPanel;
    EdCell: TEdit;
    PanelCellVal: TPanel;
    ToolButton7: TToolButton;
    ToolButton11: TToolButton;
    ActionPrint: TAction;
    Print1: TMenuItem;
    N5: TMenuItem;
    ToolButton17: TToolButton;
    ToolButton12: TToolButton;
    ToolButton18: TToolButton;
    ToolButton19: TToolButton;
    ActionCopy: TAction;
    ActionPaste: TAction;
    Edit1: TMenuItem;
    Copy1: TMenuItem;
    Paste1: TMenuItem;
    procedure ActionReadOnlyExecute(Sender: TObject);
    procedure ActionOpenExecute(Sender: TObject);
    procedure ActionSaveAsExecute(Sender: TObject);
    procedure ActionCloseExecute(Sender: TObject);
    procedure ActionCopyFormatExecute(Sender: TObject);
    procedure ActionPasteFormatExecute(Sender: TObject);
    procedure ActionCellFormatExecute(Sender: TObject);
    procedure ActionRowFormatExecute(Sender: TObject);
    procedure ActionColFormatExecute(Sender: TObject);
    procedure ActionZoomExecute(Sender: TObject);
    procedure Action10Execute(Sender: TObject);
    procedure Action25Execute(Sender: TObject);
    procedure Action50Execute(Sender: TObject);
    procedure Action100Execute(Sender: TObject);
    procedure Action200Execute(Sender: TObject);
    procedure Action400Execute(Sender: TObject);
    procedure ActionFullWorksheetExecute(Sender: TObject);
    procedure TabControlChange(Sender: TObject);
    procedure TrackBarZoomChange(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure DataSelectCell(Sender: TObject; ACol, ARow: Integer;
      var CanSelect: Boolean);
    procedure EdCellChange(Sender: TObject);
    procedure EdCellKeyPress(Sender: TObject; var Key: Char);
    procedure DataSetEditText(Sender: TObject; ACol, ARow: Integer;
      const Value: WideString);
    procedure ActionPrintExecute(Sender: TObject);
    procedure ActionPasteExecute(Sender: TObject);
    procedure ActionCopyExecute(Sender: TObject);
  private
    {$IFDEF FLX_VCL}
    PrintDialog: TPrintDialog;
    {$ENDIF}
    FlxPreview: TFPreview;
    ClipFormat: integer;
    FormatDialog: TFormatDialog;
    DisableTabs: boolean;
    LoadingEdCell: boolean;
    procedure LoadFile;

   { Private declarations }
  public
    { Public declarations }
  end;

var
  Main: TMain;

implementation

{$IFDEF FLX_VCL}
  {$R *.dfm}
{$ENDIF}
{$IFDEF FLX_CLX}
  {$R *.xfm}
{$ENDIF}

procedure TMain.ActionReadOnlyExecute(Sender: TObject);
begin
  ActionReadOnly.Checked:=not ActionReadOnly.Checked;
  Data.ReadOnly:=ActionReadOnly.Checked;
end;

procedure TMain.LoadFile;
var
  p: integer;
  ext: string;
begin
  DisableTabs:=true;
  try
    Caption:='XlsViewer';
    Ext:=UpperCase(ExtractFileExt(OpenDialog.FileName));
    if Ext='.CSV' then FlexCelImport.OpenText(OpenDialog.FileName, ListSeparator) else   //Note that ListSeparator mightbe other than "," (for example ";") so CSV might not be "comma" separated. This is the way excel handles it.
      if Ext='.TXT' then FlexCelImport.OpenText(OpenDialog.FileName, #9)  else
      FlexCelImport.OpenFile(OpenDialog.FileName);
    TabControl.Tabs.Clear;

    for p:=1 to FlexCelImport.SheetCount do
    begin
      FlexCelImport.ActiveSheet:=p;
      TabControl.Tabs.Add(FlexCelImport.ActiveSheetName);
    end;

    FlexCelImport.ActiveSheet:=1;
    TabControl.TabIndex:=FlexCelImport.ActiveSheet-1;
    Data.LoadSheet;
    Caption:=Caption+': '+OpenDialog.FileName;
  finally
    DisableTabs:=false;
  end;
end;

procedure TMain.ActionOpenExecute(Sender: TObject);
var
  WaitCursor: IWaitCursor;
begin
  if not OpenDialog.Execute then exit;
  WaitCursor:= TWaitCursor.Create;
  LoadFile;
end;

procedure TMain.ActionSaveAsExecute(Sender: TObject);
var
  WaitCursor: IWaitCursor;
  Ext: string;
begin
  if not XlsSaveDialog.Execute then exit;
  WaitCursor:= TWaitCursor.Create;
  if FileExists(XlsSaveDialog.FileName) then DeleteFile(XlsSaveDialog.FileName);

  Data.ApplySheet;
  Ext:=UpperCase(ExtractFileExt(XlsSaveDialog.FileName));
  if Ext='.CSV' then FlexCelImport.SaveAsText(XlsSaveDialog.FileName, ListSeparator) else   //Note that ListSeparator mightbe other than "," (for example ";") so CSV might not be "comma" separated. This is the way excel handles it.
    if Ext='.TXT' then FlexCelImport.SaveAsText(XlsSaveDialog.FileName, #9)  else
    FlexCelImport.Save(XlsSaveDialog.FileName);
end;

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

procedure TMain.ActionCopyFormatExecute(Sender: TObject);
begin
  ClipFormat:=FlexCelImport.CellFormat[Data.Row, Data.Col];
end;

procedure TMain.ActionPasteFormatExecute(Sender: TObject);
begin
  FlexCelImport.CellFormat[Data.Row, Data.Col]:=ClipFormat;
  Invalidate;
end;

procedure TMain.ActionCellFormatExecute(Sender: TObject);
begin
  if FormatDialog=nil then
  begin
    FormatDialog:= TFormatDialog.Create(Self);
    FormatDialog.SetData(FlexCelImport);
  end;
  FormatDialog.Caption:='Choose CELL Format:';
  FormatDialog.Load;
  FormatDialog.SelectedFormat:=FlexCelImport.CellFormat[Data.Row, Data.Col];
  if FormatDialog.ShowModal<>mrOk then exit;
  FlexCelImport.CellFormat[Data.Row, Data.Col]:=FormatDialog.SelectedFormat;
end;

procedure TMain.ActionRowFormatExecute(Sender: TObject);
begin
  if FormatDialog=nil then
  begin
    FormatDialog:= TFormatDialog.Create(Self);
    FormatDialog.SetData(FlexCelImport);
  end;
  FormatDialog.Caption:='Choose ROW Format:';
  FormatDialog.Load;
  FormatDialog.SelectedFormat:=FlexCelImport.RowFormat[Data.Row];
  if FormatDialog.ShowModal<>mrOk then exit;
  FlexCelImport.RowFormat[Data.Row]:=FormatDialog.SelectedFormat;
  Data.Invalidate;
end;

procedure TMain.ActionColFormatExecute(Sender: TObject);
begin
  if FormatDialog=nil then
  begin
    FormatDialog:= TFormatDialog.Create(Self);
    FormatDialog.SetData(FlexCelImport);
  end;
  FormatDialog.Caption:='Choose COLUMN Format:';
  FormatDialog.Load;
  FormatDialog.SelectedFormat:=FlexCelImport.ColumnFormat[Data.Col];
  if FormatDialog.ShowModal<>mrOk then exit;
  FlexCelImport.ColumnFormat[Data.Col]:=FormatDialog.SelectedFormat;
  Data.Invalidate;
end;

procedure TMain.ActionZoomExecute(Sender: TObject);
begin
  if TrackBarZoom.Position<25 then TrackBarZoom.Position:=25 else
  if TrackBarZoom.Position<50 then TrackBarZoom.Position:=50 else
  if TrackBarZoom.Position<100 then TrackBarZoom.Position:=100 else
  if TrackBarZoom.Position<200 then TrackBarZoom.Position:=200 else
  if TrackBarZoom.Position<400 then TrackBarZoom.Position:=400 else
  TrackBarZoom.Position:=10;
end;

procedure TMain.Action10Execute(Sender: TObject);
begin
  TrackBarZoom.Position:=10;
end;

procedure TMain.Action25Execute(Sender: TObject);
begin
  TrackBarZoom.Position:=25;
end;

procedure TMain.Action50Execute(Sender: TObject);
begin
  TrackBarZoom.Position:=50;
end;

procedure TMain.Action100Execute(Sender: TObject);
begin
  TrackBarZoom.Position:=100;
end;

procedure TMain.Action200Execute(Sender: TObject);
begin
  TrackBarZoom.Position:=200;
end;

procedure TMain.Action400Execute(Sender: TObject);
begin
  TrackBarZoom.Position:=400;
end;

procedure TMain.ActionFullWorksheetExecute(Sender: TObject);
begin
  ActionFullWorksheet.Checked:=not ActionFullWorksheet.Checked;
  Data.FullWorksheet:= ActionFullWorksheet.Checked;
  Data.LoadSheet;
end;


procedure TMain.TabControlChange(Sender: TObject);
begin
  if DisableTabs then exit; 
  Data.ApplySheet;
  FlexCelImport.ActiveSheet:= TabControl.TabIndex+1;
  Data.LoadSheet;
end;

procedure TMain.TrackBarZoomChange(Sender: TObject);
begin
  if FlexCelImport.IsLoaded then
    Data.Zoom:=(Sender as TTrackBar).Position;
  PanelZoom.Caption:=Format('%d%%',[ (Sender as TTrackBar).Position]);
end;

procedure TMain.FormCreate(Sender: TObject);
begin
  TrackBarZoom.Position:=100;
  if ParamCount>0 then
  begin
    OpenDialog.FileName:=ParamStr(1);
    LoadFile;
  end;
end;

procedure TMain.DataSelectCell(Sender: TObject; ACol, ARow: Integer;
  var CanSelect: Boolean);
begin
  LoadingEdCell:=true; //So EdCellChange doesn't fire...
  try
    EdCell.Text:=FlexCelImport.CellValue[ARow, ACol];
  finally
    LoadingEdCell:=false;
  end;
end;

procedure TMain.EdCellChange(Sender: TObject);
begin
  if not LoadingEdCell and (Data.CanEdit) then
    Data.SetCell(Data.Row, Data.Col, EdCell.Text);
end;

procedure TMain.EdCellKeyPress(Sender: TObject; var Key: Char);
begin
  if Key=#13 then
  begin
    Data.SetFocus;
    Key:=#0;
  end;
end;

procedure TMain.DataSetEditText(Sender: TObject; ACol, ARow: Integer;
  const Value: WideString);
begin
  LoadingEdCell:=true; //So EdCellChange doesn't fire...
  try
    EdCell.Text:=Value;
  finally
    LoadingEdCell:=false;
  end;
end;

procedure TMain.ActionPrintExecute(Sender: TObject);
var
  PageCount: integer;
  FirstPage, LastPage: integer;
  PrintRange: TXlsCellRange;
  ReallyPrint: boolean;
begin
  FirstPage:=1;
  LastPage:=-1;



  if FlxPreview=nil then FlxPreview:=TFPreview.Create(Self);

  PrintRange.Left:=1;
  PrintRange.Top:=1;
  PrintRange.Right:=Data.MaxVisibleCol;
  PrintRange.Bottom:=Data.MaxVisibleRow;
  FlxPreview.FlexCelPreview1.FlexCelGrid:=Data;
{$IFDEF FLX_VCL}
  if PrintDialog=nil then PrintDialog:=TPrintDialog.Create(Self);
  PrintDialog.MinPage:=1;
  PrintDialog.FromPage:=1;
  Data.CalcNumberOfPrintingPages(PageCount);
  PrintDialog.Options := [poPageNums, poSelection];
  PrintDialog.MaxPage:=PageCount;
  PrintDialog.ToPage:=PageCount;
  if PrintDialog.Execute then
  begin
    if PrintDialog.PrintRange=prPageNums then
    begin
      FirstPage:=PrintDialog.FromPage;
      LastPage:=PrintDialog.ToPage;
    end;

    if PrintDialog.PrintRange=prSelection then
    begin
       PrintRange.Left:=Data.Selection.Left;
       PrintRange.Top:=Data.Selection.Top;
       PrintRange.Right:=Data.Selection.Right;
       PrintRange.Bottom:=Data.Selection.Bottom;
    end;

    Printer.Title:=ExtractFileName(OpenDialog.FileName);
    FlxPreview.FlexCelPreview1.Init(PrintRange, FirstPage, LastPage);
    try
      ReallyPrint:=FlxPreview.ShowModal=mrOk;
    finally
      FlxPreview.FlexCelPreview1.Done; //Not really necessary, just to free resources
    end; //finally

    if ReallyPrint then Data.Print(PrintRange, FirstPage, LastPage);
  end;
{$ENDIF}
{$IFDEF FLX_CLX}
    FlxPreview.FlexCelPreview1.Init(PrintRange, FirstPage, LastPage);
    try
      ReallyPrint:=FlxPreview.ShowModal=mrOk;
    finally
      FlxPreview.FlexCelPreview1.Done; //Not really necessary, just to free resources
    end; //finally
    Printer.Title:=ExtractFileName(OpenDialog.FileName);

    if ReallyPrint then Data.Print;
{$ENDIF}

end;

procedure TMain.ActionPasteExecute(Sender: TObject);
begin
  Data.PasteFromClipboard;
end;

procedure TMain.ActionCopyExecute(Sender: TObject);
begin
  Data.CopyToClipboard;
end;

end.