mainexcelexport.pas

delphi实现EXCEL复杂导入导出功能的类

{--------------------------------------------------------------------------------
* Description : Examples how to use the TscExcelExport component
* Author : Stefan Cruysberghs
* Email : [e-mail]
* Website : http://www.scip.be
--------------------------------------------------------------------------------}

unit MainExcelExport;

interface

{$IFDEF VER170}
  {$DEFINE DELPHI9}
{$ENDIF}

{$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;
    LabelDelphiVersions: 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;
    TableAnimalsWeight7: TFloatField;
    TableAnimalsTime4: TDateTimeField;
    TableAnimalsTime5: TDateTimeField;
    TableAnimalsTime6: TDateField;
    BitBtnExcelVersion: TBitBtn;
    LabelExcelVersion: TLabel;
    LabelExcelVersionInfo: TLabel;
    BitBtnExportVisibleFields: TBitBtn;
    LabelExportVisibleFields: TLabel;
    LabelExportVisibleFieldsInfo: TLabel;
    LabelExcelVersions: TLabel;
    procedure scExcelExport2GetFieldCellStyleEvent(Sender: TObject; const IntFieldIndex: Integer;
      var ColorBackground: TColor; FontCell: TxlFont);
    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);
    procedure BitBtnExcelVersionClick(Sender: TObject);
    procedure BitBtnExportVisibleFieldsClick(Sender: TObject);
  private
    procedure ChangeCellColors(Sender: TObject; Field: TField; var ColorBackground : TColor; FontCell : TxlFont);
  public
  end;

var
  FormExcelExport: TFormExcelExport;

implementation

{$IFDEF DELPHI5}
uses ShellAPI;
{$ELSE}
uses ShellAPI, DateUtils;
{$ENDIF}

{$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; FontCell : TxlFont);
begin
  if Field.FieldName = 'CustNo' then
  begin
    if Field.Value > 2000 then
    begin
      FontCell.Color := clRed;
      FontCell.Name  := 'Times New Roman';
      FontCell.Size := 14;
    end;
    if Field.Value > 3000 then
    begin
      FontCell.Color := clGreen;
      FontCell.Style := [fsBold];
    end;
  end;

  if Field.FieldName = 'EmpNo' then
  begin
    if Field.Dataset.FieldByName('CustNo').Value > 2000 then
      ColorBackground := clRed;
  end;

  if Field.DataSet.FieldByName('EmpNo').Value > 100 then
    ColorBackground := clYellow;
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;