main.pas

一个经典的读写Excel的控件

unit Main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, XLSReadWriteII2, DrawingObjChart2, BIFFRecsII2, XLSFonts2,
  ComCtrls, ColorSelect2, ExtCtrls, CellFormats2;

type
  TfrmMain = class(TForm)
    Button1: TButton;
    Label1: TLabel;
    edFilename: TEdit;
    Button3: TButton;
    dlgSave: TSaveDialog;
    Button4: TButton;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    TabSheet3: TTabSheet;
    TabSheet4: TTabSheet;
    Label2: TLabel;
    Label3: TLabel;
    Title: TLabel;
    Button2: TButton;
    cbLegend: TCheckBox;
    cb3D: TCheckBox;
    edTitle: TEdit;
    Button5: TButton;
    Button6: TButton;
    Button7: TButton;
    Button8: TButton;
    Button9: TButton;
    Button10: TButton;
    Button11: TButton;
    Button12: TButton;
    Button13: TButton;
    Button14: TButton;
    Button15: TButton;
    Label4: TLabel;
    edPictFile: TEdit;
    dlgOpen: TOpenDialog;
    Button16: TButton;
    Button17: TButton;
    Button18: TButton;
    Button19: TButton;
    shpColor1: TShape;
    shpColor2: TShape;
    cbGradientStyle: TComboBox;
    Label5: TLabel;
    Button20: TButton;
    TabSheet5: TTabSheet;
    Button21: TButton;
    Memo1: TMemo;
    Memo2: TMemo;
    Memo3: TMemo;
    Memo4: TMemo;
    Button22: TButton;
    Button23: TButton;
    dlgFont: TFontDialog;
    Button24: TButton;
    Label6: TLabel;
    cbLineMarkers: TComboBox;
    XLS: TXLSReadWriteII2;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure Button8Click(Sender: TObject);
    procedure Button9Click(Sender: TObject);
    procedure Button10Click(Sender: TObject);
    procedure Button11Click(Sender: TObject);
    procedure Button12Click(Sender: TObject);
    procedure Button13Click(Sender: TObject);
    procedure Button14Click(Sender: TObject);
    procedure Button16Click(Sender: TObject);
    procedure Button15Click(Sender: TObject);
    procedure Button18Click(Sender: TObject);
    procedure Button19Click(Sender: TObject);
    procedure Button17Click(Sender: TObject);
    procedure Button20Click(Sender: TObject);
    procedure Button21Click(Sender: TObject);
    procedure Button22Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button23Click(Sender: TObject);
    procedure Button24Click(Sender: TObject);
  private
    FFontA: TFont;
    FFontB: TFont;

    FGradientColor1,FGradientColor2: TExcelColor;
  public
    { Public declarations }
  end;

var
  frmMain: TfrmMain;

implementation

{$R *.dfm}

procedure TfrmMain.Button1Click(Sender: TObject);
begin
  Close;
end;

procedure TfrmMain.Button2Click(Sender: TObject);
begin
  XLS.Sheets[0].Charts.Clear;
  with XLS.Sheets[0].AddChart do begin
    Col1 := 5;
    Col2 := 11;
    Series[0].Values := 'Sheet1!$A$1:$A$10';
    PlotArea.HasLegend := cbLegend.Checked;
    PlotArea.Is3D := cb3D.Checked;
    DefaultTextA.Font.Assign(FFontA);
    DefaultTextB.Font.Assign(FFontB);

    with Series[0].DataPoints.Add do begin
      PointIndex := 2;
      AreaFormat.ForegroundColor := xcYellow;
    end;

  end;
  if edTitle.Text <> '' then begin
    with XLS.Sheets[0].Charts[0].Labels.Add do begin
      LabelType := cltTitle;
      Text := edTitle.Text;
    end;
  end;
end;

procedure TfrmMain.Button3Click(Sender: TObject);
begin
  dlgSave.FileName := edFilename.Text;
  if dlgSave.Execute then
    edFilename.Text := dlgSave.FileName;
end;

procedure TfrmMain.Button4Click(Sender: TObject);
begin
  XLS.Filename := edFilename.Text;
  XLS.Write;
end;

procedure TfrmMain.FormCreate(Sender: TObject);
var
  i: integer;
begin
  cbLineMarkers.ItemIndex := 1;
  FFontA := TFont.Create;
  FFontB := TFont.Create;
  Randomize;
  PageControl1.ActivePage := TabSheet1;
  FGradientColor1 := xcWhite;
  FGradientColor2 := xcGray50;
  shpColor1.Brush.Color := ExcelColorPalette[Integer(FGradientColor1)];
  shpColor2.Brush.Color := ExcelColorPalette[Integer(FGradientColor2)];
  cbGradientStyle.ItemIndex := 0;
  for i := 0 to 9 do begin
    XLS.Sheets[0].AsFloat[0,i] := Random(100);
    XLS.Sheets[0].AsFloat[1,i] := Random(100);
    XLS.Sheets[0].AsFloat[2,i] := Random(100);
    XLS.Sheets[0].AsFloat[3,i] := Random(100);
    XLS.Sheets[0].AsFloat[4,i] := Random(100);
  end;
end;

procedure TfrmMain.Button5Click(Sender: TObject);
begin
  XLS.Sheets[0].Charts.Clear;
  with XLS.Sheets[0].AddChart do begin
    Col1 := 5;
    Col2 := 11;
    Series[0].Values := 'Sheet1!$A$1:$A$10';
    Series[0].HasDefDataPoint := True;
    Series[0].DefDataPoint.AreaFormat.ForegroundColor := xc28;

    with Labels.Add do begin
      LabelType := cltDataPoint;
      SerieIndex := 0;
      DataPointIndex := 1;
      Text := 'Hello';
    end;

    PlotArea.HasLegend := cbLegend.Checked;
    PlotArea.Is3D := cb3D.Checked;

    PlotArea.Frame.AreaFormat.ForegroundColor := xc27;
    TChartStyleBarColumn(PlotArea.ChartStyle).IsBar := True;
  end;
  if edTitle.Text <> '' then begin
    with XLS.Sheets[0].Charts[0].Labels.Add do begin
      LabelType := cltTitle;
      Text := edTitle.Text;
    end;
  end;
end;

procedure TfrmMain.Button6Click(Sender: TObject);
begin
  XLS.Sheets[0].Charts.Clear;
  with XLS.Sheets[0].AddChart do begin
    Col1 := 5;
    Col2 := 11;
    Series[0].Values := 'Sheet1!$A$1:$A$10';

    PlotArea.ChartType := xctArea;
    PlotArea.HasLegend := cbLegend.Checked;
    PlotArea.Is3D := cb3D.Checked;
    DefaultTextA.Font.Assign(FFontA);
    DefaultTextB.Font.Assign(FFontB);

    PlotArea.Frame.AreaFormat.ForegroundColor := xcPaleGreen;
  end;
  if edTitle.Text <> '' then begin
    with XLS.Sheets[0].Charts[0].Labels.Add do begin
      LabelType := cltTitle;
      Text := edTitle.Text;
    end;
  end;
end;

procedure TfrmMain.Button7Click(Sender: TObject);
begin
  XLS.Sheets[0].Charts.Clear;
  with XLS.Sheets[0].AddChart do begin
    Col1 := 5;
    Col2 := 11;
    Series[0].Values := 'Sheet1!$A$1:$A$10';
    Series[0].Values2 := 'Sheet1!$B$1:$B$10';

    PlotArea.ChartType := xctBubble;
    PlotArea.HasLegend := cbLegend.Checked;
    PlotArea.Is3D := cb3D.Checked;
    DefaultTextA.Font.Assign(FFontA);
    DefaultTextB.Font.Assign(FFontB);

    PlotArea.Frame.AreaFormat.ForegroundColor := xcPaleGreen;
    TChartStyleScatter(PlotArea.ChartStyle).IsBubble := True;
  end;
  if edTitle.Text <> '' then begin
    with XLS.Sheets[0].Charts[0].Labels.Add do begin
      LabelType := cltTitle;
      Text := edTitle.Text;
    end;
  end;
end;

procedure TfrmMain.Button8Click(Sender: TObject);
begin
  XLS.Sheets[0].Charts.Clear;
  with XLS.Sheets[0].AddChart do begin
    Col1 := 5;
    Col2 := 11;
    Series[0].Values := 'Sheet1!$A$1:$A$10';
    Series[0].Geomtery := sigCylinder;

    PlotArea.HasLegend := cbLegend.Checked;
    // Cylinder charts are always 3D
    PlotArea.Is3D := True;
    DefaultTextA.Font.Assign(FFontA);
    DefaultTextB.Font.Assign(FFontB);

    PlotArea.Frame.AreaFormat.ForegroundColor := xcPaleGreen;
  end;
  if edTitle.Text <> '' then begin
    with XLS.Sheets[0].Charts[0].Labels.Add do begin
      LabelType := cltTitle;
      Text := edTitle.Text;
    end;
  end;
end;

procedure TfrmMain.Button9Click(Sender: TObject);
begin
  XLS.Sheets[0].Charts.Clear;
  with XLS.Sheets[0].AddChart do begin
    Col1 := 5;
    Col2 := 11;
    Series[0].Values := 'Sheet1!$A$1:$A$10';

    PlotArea.ChartType := xctPie;
    PlotArea.HasLegend := cbLegend.Checked;
    PlotArea.Is3D := cb3D.Checked;
    DefaultTextA.Font.Assign(FFontA);
    DefaultTextB.Font.Assign(FFontB);