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

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Db, DBTables, scExcelExport, Grids, DBGrids, ExtCtrls, Buttons,
  ComCtrls, OleServer, Excel2000;

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

type
  TFormExcelExport = class(TForm)
    TableOrders: TTable;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    TableAnimals: TTable;
    TableVendors: TTable;
    LabelHeaderFont: TLabel;
    LabelTitleFont: TLabel;
    PanelSubTitle: TPanel;
    BitBtnExportDataset: TBitBtn;
    StatusBar: TStatusBar;
    BitBtnSaveXLS: TBitBtn;
    BitBtnPrintPreview: TBitBtn;
    BitBtnExportPropeties: TBitBtn;
    BitBtnExportConnectTo: TBitBtn;
    PanelTitle: TPanel;
    scExcelExport1: TscExcelExport;
    BitBtnExportDatasetColors: TBitBtn;
    scExcelExport2: TscExcelExport;
    BitBtnExportEvents: TBitBtn;
    BitBtnExportExistingFile: TBitBtn;
    BitBtnCloseAll: TBitBtn;
    BitBtnExportGroups: TBitBtn;
    QuerySortShipVia: TQuery;
    BitBtnAccessingExcel: TBitBtn;
    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;
    procedure scExcelExport1ExportRecords(Sender: TObject; IntRecordNumber: Integer);
    procedure BitBtnExportDatasetClick(Sender: TObject);
    procedure BitBtnSaveXLSClick(Sender: TObject);
    procedure BitBtnPrintPreviewClick(Sender: TObject);
    procedure BitBtnExportPropetiesClick(Sender: TObject);
    procedure BitBtnExportConnectToClick(Sender: TObject);
    procedure BitBtnExportDatasetColorsClick(Sender: TObject);
    procedure BitBtnExportEventsClick(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 BitBtnExportExistingFileClick(Sender: TObject);
    procedure BitBtnCloseAllClick(Sender: TObject);
    procedure BitBtnExportGroupsClick(Sender: TObject);
    procedure scExcelExport2GotoFirstRecord(Sender: TObject);
    procedure scExcelExport2GotoNextRecord(Sender: TObject);
    procedure BitBtnAccessingExcelClick(Sender: TObject);
  private
    procedure ChangeCellColors(Sender: TObject; Field: TField;
      var ColorBackground: TColor);
  public
  end;

var
  FormExcelExport: TFormExcelExport;

implementation

{$R *.DFM}

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

//-----------------------------------------------------------------------------
// Export dataset to Excel and show duration
//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportDatasetClick(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.ExcelVisible:=True;
    //scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.WorksheetName:='TscExcelExport example';
    scExcelExport1.ConnectTo := ctNewExcel;
    Duration := Now();
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//-----------------------------------------------------------------------------
// Export dataset to Excel, adding summaries and using all the layout properties
//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportPropetiesClick(Sender: TObject);
begin
  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.WorksheetName := 'TscExcelExport layout example';
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.StyleColumnWidth:=cwOwnerWidth;
    scExcelExport1.ColumnWidth := 20;

    scExcelExport1.HeaderText.Text:= 'Header';
    scExcelExport1.HeaderText.Add('Header - Line2');
    scExcelExport1.HeaderText.Add('Header - Line3');
    scExcelExport1.FooterText.Add('Footer - Line1');
    scExcelExport1.FooterText.Add('Footer - Line2');
    scExcelExport1.BeginRowHeader := 5;
    scExcelExport1.BorderHeader.BackColor := clGreen;
    scExcelExport1.FontHeader := TxlFont(LabelHeaderFont.Font);

    scExcelExport1.BeginRowTitles := 5;
    scExcelExport1.FontTitles := TxlFont(LabelTitleFont.Font);
    scExcelExport1.FontTitles.Orientation := 45;
    scExcelExport1.BorderTitles.BackColor := clYellow;
    scExcelExport1.BorderTitles.BorderColor := clRed;
    scExcelExport1.BorderTitles.LineStyle := blDouble;
    scExcelExport1.BorderTitles.Weight := bwThick;

    scExcelExport1.BeginRowData := 8;
    scExcelExport1.SummarySelection := ssValues;
    scExcelExport1.SummaryCalculation := scMAX;
    scExcelExport1.BorderSummary.BackColor := clYellow;
    scExcelExport1.BorderSummary.BorderColor := clRed;
    scExcelExport1.BorderSummary.LineStyle := blLine;
    scExcelExport1.BorderSummary.Weight := bwThick;

    scExcelExport1.BorderData.LineStyle := blDouble;    

    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.ExportDataset;
  finally
    scExcelExport1.Disconnect;
  end;
end;

//-----------------------------------------------------------------------------
// Export dataset to Excel without showing Excel and save worksheet to file
//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnSaveXLSClick(Sender: TObject);
begin
  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.ExcelVisible:=False;
    scExcelExport1.WorksheetName := 'TscExcelExport example';
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.ExportDataset;
    scExcelExport1.SaveAs('c:\ExcelExport.xls',ffXLS);
    //scExcelExport1.SaveAs('c:\ExcelExport.htm',ffHTM);
    //scExcelExport1.SaveAs('c:\ExcelExport.csv',ffCSV);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//-----------------------------------------------------------------------------
// Export dataset to Excel and show print preview
//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnPrintPreviewClick(Sender: TObject);
begin
  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.ExcelVisible:=False;
    scExcelExport1.WorksheetName := 'TscExcelExport example';
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.ExportDataset;
    scExcelExport1.PrintPreview(True);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//-----------------------------------------------------------------------------
// Export datasets to Excel, using the different connect properties
//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportConnectToClick(Sender: TObject);
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;

    // Start excel and create new workbook and worksheet Orders
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.WorksheetName:='Orders';
    scExcelExport1.ConnectTo := ctNewExcel;
    scExcelExport1.ShowTitles := False;
    scExcelExport1.ExportDataset;

    // Create new workbook and new worksheet Animals in active excel
    scExcelExport1.Disconnect;
    scExcelExport1.Dataset:=TableAnimals;
    scExcelExport1.WorksheetName:='Animals';
    scExcelExport1.ConnectTo := ctNewWorkbook;
    scExcelExport1.ShowTitles := False;
    scExcelExport1.ExportDataset;

    // Create new worksheet Vendors in active workbook in active excel
    scExcelExport1.Disconnect;
    scExcelExport1.Dataset:=TableVendors;
    scExcelExport1.WorksheetName:='Vendors';
    scExcelExport1.ConnectTo := ctNewWorksheet;
    scExcelExport1.ShowTitles := True;