mainexcelexport.pas

delphi读写取excel,内附源码和Demo文件

{--------------------------------------------------------------------------------
* Description : Examples how to use the TscExcelExport component
* Author : Stefan Cruysberghs
* Email : [e-mail]
* Website : http://users.pandora.be/stefancr (http://www.stefancr.yucom.be)
--------------------------------------------------------------------------------}

unit MainExcelExport;

interface

{$IFDEF VER150}
  {$DEFINE DELPHI7}
{$ENDIF}

{$IFDEF VER140}
  {$DEFINE DELPHI6}
{$ENDIF}

{$IFDEF VER130}
  {$DEFINE DELPHI5}
{$ENDIF}

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Db, DBTables, scExcelExport, Grids, DBGrids, ExtCtrls, Buttons,
  ComCtrls, OleServer, ImgList, {$IFDEF DELPHI5} Excel97 {$ELSE} Excel2000 {$ENDIF};

  // Use the same Excel unit as you use in the scExcelExport component !

type
  TFormExcelExport = class(TForm)
    TableOrders: TTable;
    DataSourceOrders: TDataSource;
    TableAnimals: TTable;
    TableBiolife: TTable;
    StatusBar: TStatusBar;
    PanelTitle: TPanel;
    scExcelExport1: TscExcelExport;
    QuerySortShipVia: TQuery;
    TableOrdersOrderNo: TFloatField;
    TableOrdersCustNo: TFloatField;
    TableOrdersSaleDate: TDateTimeField;
    TableOrdersShipDate: TDateTimeField;
    TableOrdersEmpNo: TIntegerField;
    TableOrdersShipToContact: TStringField;
    TableOrdersShipToAddr1: TStringField;
    TableOrdersShipToAddr2: TStringField;
    TableOrdersShipToCity: TStringField;
    TableOrdersShipToState: TStringField;
    TableOrdersShipToZip: TStringField;
    TableOrdersShipToCountry: TStringField;
    TableOrdersShipToPhone: TStringField;
    TableOrdersShipVIA: TStringField;
    TableOrdersPO: TStringField;
    TableOrdersTerms: TStringField;
    TableOrdersPaymentMethod: TStringField;
    TableOrdersItemsTotal: TCurrencyField;
    TableOrdersTaxRate: TFloatField;
    TableOrdersFreight: TCurrencyField;
    TableOrdersAmountPaid: TCurrencyField;
    DataSourceAnimals: TDataSource;
    TableAnimalsNAME: TStringField;
    TableAnimalsSIZE: TSmallintField;
    TableAnimalsWEIGHT: TSmallintField;
    TableAnimalsAREA: TStringField;
    TableAnimalsBMP: TBlobField;
    TableAnimalsCalcField: TFloatField;
    PageControl1: TPageControl;
    TabSheetTemp: TTabSheet;
    TabSheetOrders: TTabSheet;
    TabSheetAnimals: TTabSheet;
    DBGridAnimals: TDBGrid;
    PanelOrders: TPanel;
    DBGridOrders: TDBGrid;
    PanelAnimals: TPanel;
    TabSheetBiolife: TTabSheet;
    PanelVendors: TPanel;
    DBGridVendors: TDBGrid;
    DataSourceVendors: TDataSource;
    TabSheetFeatures: TTabSheet;
    BitBtnSimpleExport1: TBitBtn;
    LabelSimpleExport1: TLabel;
    LabelSimpleExportInfo1: TLabel;
    BitBtnExportLayout: TBitBtn;
    LabelExportLayout: TLabel;
    LabelExportLayoutInfo: TLabel;
    TableAnimalsAreaText: TStringField;
    TableBiolifeSpeciesNo: TFloatField;
    TableBiolifeCategory: TStringField;
    TableBiolifeCommon_Name: TStringField;
    TableBiolifeSpeciesName: TStringField;
    TableBiolifeLengthcm: TFloatField;
    TableBiolifeLength_In: TFloatField;
    TableBiolifeNotes: TMemoField;
    TableBiolifeGraphic: TGraphicField;
    BitBtnSimpleExport3: TBitBtn;
    LabelSimpleExport3: TLabel;
    LabelSimpleExportInfo3: TLabel;
    BitBtnSimpleExport2: TBitBtn;
    LabelSimpleExport2: TLabel;
    LabelSimpleExportInfo2: TLabel;
    BitBtnSimpleExport4: TBitBtn;
    LabelSimpleExport4: TLabel;
    LabelSimpleExportInfo4: TLabel;
    BitBtnExportColors: TBitBtn;
    LabelExportColors: TLabel;
    LabelExportColorsInfo: TLabel;
    BitBtnExportSave: TBitBtn;
    LabelExportSave: TLabel;
    LabelExportSaveInfo: TLabel;
    BitBtnPrintPreview: TBitBtn;
    LabelPrintPreview: TLabel;
    LabelPrintPreviewInfo: TLabel;
    BitBtnConnectTo: TBitBtn;
    LabelConnectTo: TLabel;
    LabelConnectToInfo: TLabel;
    BitBtnUseExisting: TBitBtn;
    LabelUseExisting: TLabel;
    LabelUseExistingInfo: TLabel;
    BitBtnExportSummaries: TBitBtn;
    LabelExportSummaries: TLabel;
    LabelExportSummariesInfo: TLabel;
    ImageExcelExportLogo: TImage;
    LabelTitle1: TLabel;
    LabelAuthor: TLabel;
    LabelWebsite: TLabel;
    LabelRegister: TLabel;
    LabelFreeware: TLabel;
    Bevel1: TBevel;
    LabelBDE: TLabel;
    BitBtnGrouping: TBitBtn;
    LabelGrouping: TLabel;
    LabelGroupingInfo: TLabel;
    BitBtnUseWorksheet: TBitBtn;
    LabelUseWorksheet: TLabel;
    LabelUseWorksheetInfo: TLabel;
    BitBtnUseEvents: TBitBtn;
    LabelUseEvents: TLabel;
    BitBtnCloseExcel: TBitBtn;
    LabelCloseExcel: TLabel;
    LabelCloseExcelInfo: TLabel;
    LabelUseEventsInfo: TLabel;
    scExcelExport2: TscExcelExport;
    LabelHeaderFont: TLabel;
    LabelTitleFont: TLabel;
    TableAnimalsSizeText: TStringField;
    ImageListTabs: TImageList;
    TableAnimalsWeight2: TFloatField;
    TableAnimalsWeight3: TFloatField;
    TableAnimalsWeight4: TStringField;
    TableAnimalsWeight5: TCurrencyField;
    TableAnimalsWeight6: TCurrencyField;
    LabelExtraInfo: TLabel;
    TableAnimalsTime1: TDateTimeField;
    TableAnimalsTime2: TDateTimeField;
    TableAnimalsTime3: TDateTimeField;
    procedure scExcelExport1ExportRecords(Sender: TObject; IntRecordNumber: Integer);
    procedure BitBtnSimpleExport1Click(Sender: TObject);
    procedure BitBtnPrintPreviewClick(Sender: TObject);
    procedure BitBtnExportLayoutClick(Sender: TObject);
    procedure scExcelExport2GetEOF(Sender: TObject; var BlnEOF: Boolean);
    procedure scExcelExport2GetFieldCount(Sender: TObject;
      var IntFieldCount: Integer);
    procedure scExcelExport2GetFieldDataSize(Sender: TObject;
      const FieldIndex: Integer; var IntFieldDataSize: Integer);
    procedure scExcelExport2GetFieldDataType(Sender: TObject;
      const FieldIndex: Integer; var FieldDataType: TFieldType);
    procedure scExcelExport2GetFieldDisplayName(Sender: TObject;
      const FieldIndex: Integer; var StrFieldDisplayName: String);
    procedure scExcelExport2GetFieldName(Sender: TObject;
      const FieldIndex: Integer; var StrFieldName: String);
    procedure scExcelExport2GetFieldValue(Sender: TObject;
      const FieldIndex: Integer; var VarValue: Variant);
    procedure scExcelExport2GetFieldVisible(Sender: TObject;
      const FieldIndex: Integer; var BlnFieldVisible: Boolean);
    procedure scExcelExport2ExportRecords(Sender: TObject;
      IntRecordNumber: Integer);
    procedure scExcelExport2GotoFirstRecord(Sender: TObject);
    procedure scExcelExport2GotoNextRecord(Sender: TObject);
    procedure TableAnimalsCalcFields(DataSet: TDataSet);
    procedure BitBtnSimpleExport2Click(Sender: TObject);
    procedure BitBtnSimpleExport3Click(Sender: TObject);
    procedure TableBiolifeNotesGetText(Sender: TField; var Text: String;
      DisplayText: Boolean);
    procedure BitBtnSimpleExport4Click(Sender: TObject);
    procedure BitBtnExportColorsClick(Sender: TObject);
    procedure LabelWebsiteClick(Sender: TObject);
    procedure BitBtnExportSaveClick(Sender: TObject);
    procedure BitBtnExportSummariesClick(Sender: TObject);
    procedure BitBtnConnectToClick(Sender: TObject);
    procedure BitBtnUseExistingClick(Sender: TObject);
    procedure BitBtnGroupingClick(Sender: TObject);
    procedure BitBtnUseWorksheetClick(Sender: TObject);
    procedure BitBtnUseEventsClick(Sender: TObject);
    procedure BitBtnCloseExcelClick(Sender: TObject);
  private
    procedure ChangeCellColors(Sender: TObject; Field: TField;
      var ColorBackground: TColor);
  public
  end;

var
  FormExcelExport: TFormExcelExport;

implementation

uses ShellAPI;

{$R *.DFM}

//-----------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport1ExportRecords(Sender: TObject;
  IntRecordNumber: Integer);
begin
  StatusBar.Panels[0].Text := 'Records : '+IntToStr(IntRecordNumber);
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.ChangeCellColors(Sender: TObject; Field: TField; var ColorBackground: TColor);
begin
  if Field.FieldName = 'CustNo' then
  begin
    if Field.Value > 2000 then
      ColorBackground := clRed;
    if Field.Value > 3000 then
      ColorBackground := clGreen;
  end;

  if Field.FieldName = 'EmpNo' then
  begin
    if Field.Value > 100 then
      ColorBackground := clYellow;
  end;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2GetEOF(Sender: TObject; var BlnEOF: Boolean);
begin
  BlnEOF := QuerySortShipVia.Eof;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2GetFieldCount(Sender: TObject;
  var IntFieldCount: Integer);
begin
  IntFieldCount := QuerySortShipVia.FieldCount;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2GetFieldDataSize(Sender: TObject;
  const FieldIndex: Integer; var IntFieldDataSize: Integer);
begin
  IntFieldDataSize := QuerySortShipVia.Fields[FieldIndex].Size;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2GetFieldDataType(Sender: TObject;
  const FieldIndex: Integer; var FieldDataType: TFieldType);
begin
  FieldDataType := QuerySortShipVia.Fields[FieldIndex].DataType;

  if SameText(QuerySortShipVia.Fields[FieldIndex].FieldName,'NewOrderNo') then
    FieldDataType := ftString;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2GetFieldDisplayName(
  Sender: TObject; const FieldIndex: Integer;
  var StrFieldDisplayName: String);
begin
  StrFieldDisplayName := '* '+QuerySortShipVia.Fields[FieldIndex].FieldName+' *';
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2GetFieldName(Sender: TObject;
  const FieldIndex: Integer; var StrFieldName: String);
begin
  StrFieldName := QuerySortShipVia.Fields[FieldIndex].FieldName;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2GetFieldValue(Sender: TObject;
  const FieldIndex: Integer; var VarValue: Variant);
begin
  VarValue := QuerySortShipVia.Fields[FieldIndex].Value;

  if SameText(QuerySortShipVia.Fields[FieldIndex].FieldName,'NewOrderNo') then
    VarValue := '00'+QuerySortShipVia.Fields[FieldIndex].AsString;
end;

//------------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2GotoFirstRecord(Sender: TObject);
begin
  QuerySortShipVia.First;
end;

//------------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2GotoNextRecord(Sender: TObject);
begin
  QuerySortShipVia.Next;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2GetFieldVisible(Sender: TObject;
  const FieldIndex: Integer; var BlnFieldVisible: Boolean);
begin
  BlnFieldVisible := True;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.scExcelExport2ExportRecords(Sender: TObject;
  IntRecordNumber: Integer);
begin
  StatusBar.Panels[0].Text := 'Records : '+IntToStr(IntRecordNumber);
end;

//------------------------------------------------------------------------------
procedure TFormExcelExport.TableAnimalsCalcFields(DataSet: TDataSet);
var
  StrSize : String;
  IntLength : Integer;
begin
  Dataset.FieldByName('WeightSize').AsFloat :=
    Dataset.FieldByName('Weight').AsFloat * Dataset.FieldByName('Size').AsFloat;
  Dataset.FieldByName('AreaText').AsString :=
    LowerCase(Dataset.FieldByName('Area').AsString)+
    '-'+
    LowerCase(Dataset.FieldByName('Area').AsString);

  Dataset.FieldByName('Weight2').AsFloat := Dataset.FieldByName('Weight').AsFloat * 12.34;
  Dataset.FieldByName('Weight3').AsFloat := Dataset.FieldByName('Weight').AsFloat * 12.34;
  Dataset.FieldByName('Weight4').AsFloat := Dataset.FieldByName('Weight').AsFloat * 12.34;
  Dataset.FieldByName('Weight5').AsCurrency := Dataset.FieldByName('Weight').AsFloat * 12.34;
  Dataset.FieldByName('Weight6').AsCurrency := Dataset.FieldByName('Weight').AsFloat * 12.34;

  Dataset.FieldByName('Time1').AsDateTime :=
    StrToDateTime('01'+DateSeparator+'01'+DateSeparator+'2003 10'+TimeSeparator+'11'+TimeSeparator+'12')
    +Dataset.FieldByName('Weight').AsFloat*1.1;
  Dataset.FieldByName('Time2').AsDateTime := Dataset.FieldByName('Time1').AsDateTime;
  Dataset.FieldByName('Time3').AsDateTime := Dataset.FieldByName('Time1').AsDateTime;

  StrSize := Dataset.FieldByName('Size').AsString;
  IntLength := Length(StrSize);
  StrSize := StringOfChar('0',10-IntLength)+StrSize;
  Dataset.FieldByName('SizeText').AsString := StrSize;
end;

//------------------------------------------------------------------------------
procedure TFormExcelExport.TableBiolifeNotesGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
begin
  Text := UpperCase(Copy(Sender.AsString,1,20));
end;

//------------------------------------------------------------------------------
procedure TFormExcelExport.LabelWebsiteClick(Sender: TObject);
begin
  ShellExecute(Handle,'open','http://users.pandora.be/stefancr',nil,nil,SW_NORMAL);
end;

//-----------------------------------------------------------------------------
// Simple export of dataset to new worksheet in Excel. Use default properties. (ORDERS)
//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnSimpleExport1Click(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.WorksheetName:='TscExcelExport Simple Export 1';
    scExcelExport1.ConnectTo := ctNewExcel;
    Duration := Now();
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//------------------------------------------------------------------------------
// Simple export of dataset with calculated fields. (ANIMALS)
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnSimpleExport2Click(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.ExcelVisible:=True;