mainexcelexport.~pas

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

//-----------------------------------------------------------------------------
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.scExcelExport2GetFieldCellStyleEvent(Sender: TObject;
  const IntFieldIndex: Integer; var ColorBackground: TColor; FontCell: TxlFont);
begin
  if QuerySortShipVia.FieldByName('OrderNo').Value < 1010 then
    ColorBackground := clYellow;

  if IntFieldIndex = 4 then
  begin
    if QuerySortShipVia.FieldByName('CustNo').Value > 2000 then
    begin
      FontCell.Color := clRed;
      FontCell.Size  := 14;
    end;
  end;
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('Weight7').AsFloat := Dataset.FieldByName('Weight').AsFloat * 1234.56;

  {$IFDEF DELPHI5}
  Dataset.FieldByName('Time1').AsDateTime :=
    EncodeDate(2004,01,20)
    +EncodeTime(10,11,12,0)
    +Dataset.FieldByName('Weight').AsFloat*1.1;
  {$ELSE}
  Dataset.FieldByName('Time1').AsDateTime := EncodeDateTime(2004,01,20,10,11,12,0)
    +Dataset.FieldByName('Weight').AsFloat*1.1;
  {$ENDIF}

  Dataset.FieldByName('Time2').AsDateTime := Dataset.FieldByName('Time1').AsDateTime;
  Dataset.FieldByName('Time3').AsDateTime := Dataset.FieldByName('Time1').AsDateTime;
  Dataset.FieldByName('Time4').AsDateTime := Dataset.FieldByName('Time1').AsDateTime;
  Dataset.FieldByName('Time5').AsDateTime := Dataset.FieldByName('Time1').AsDateTime;
  Dataset.FieldByName('Time6').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,50));
end;

//------------------------------------------------------------------------------
procedure TFormExcelExport.LabelWebsiteClick(Sender: TObject);
begin
  ShellExecute(Handle,'open','http://www.scip.be',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;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableAnimals;
    scExcelExport1.WorksheetName:='TscExcelExport Simple Export 2';
    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 memo field. (BIOLIFE)
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnSimpleExport3Click(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableBiolife;
    scExcelExport1.WorksheetName:='TscExcelExport Simple Export 3';
    scExcelExport1.ConnectTo := ctNewExcel;
    Duration := Now();
    TableBiolifeNotes.OnGetText := nil;
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//------------------------------------------------------------------------------
// Simple export of dataset with memo field and OnGetText event. (BIOLIFE)
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnSimpleExport4Click(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableBiolife;
    scExcelExport1.WorksheetName:='TscExcelExport Simple Export 4';
    scExcelExport1.ConnectTo := ctNewExcel;
    Duration := Now();
    TableBiolifeNotes.OnGetText := TableBiolifeNotesGetText;
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//------------------------------------------------------------------------------
// Export dataset and add summary (SUM, AVG, MAX, ...) for all numeric fields (ORDERS)
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportSummariesClick(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.WorksheetName := 'TscExcelExport Summaries';
    scExcelExport1.Dataset:=TableOrders;

    scExcelExport1.SummarySelection := ssValues;
    scExcelExport1.SummaryCalculation := scSUM;
    scExcelExport1.ExcelVisible:=True;
    Duration := Now();
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//-----------------------------------------------------------------------------
// Export dataset using headers, footers, summaries, fonts, borders, colors, ... (ORDERS)
//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportLayoutClick(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.WorksheetName := 'TscExcelExport Using Layout';
    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.FontData.Size := 12;

    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;
    Duration := Now();
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//------------------------------------------------------------------------------
// Export dataset, and change background and font color depending value of field (ORDERS)
// (see procedure ChangeCellColors)
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportColorsClick(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.WorksheetName:='TscExcelExport Using colors';
    scExcelExport1.ConnectTo := ctNewExcel;