mainexcelexport.~pas

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

    scExcelExport1.FontData.Color := clBlue;
    scExcelExport1.OnGetCellStyleEvent := ChangeCellColors;
    Duration := Now();
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    scExcelExport1.Disconnect;
    scExcelExport1.OnGetCellStyleEvent := nil;
  end;
end;

//------------------------------------------------------------------------------
// Only export the visible fields (ORDERS)
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportVisibleFieldsClick(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.WorksheetName:='TscExcelExport Visible fields';
    scExcelExport1.ConnectTo := ctNewExcel;
    scExcelExport1.FontData.Color := clBlue;
    scExcelExport1.OnGetCellStyleEvent := ChangeCellColors;
    TableOrdersShipToContact.Visible := False;
    TableOrdersShipToAddr1.Visible := False;
    TableOrdersShipToAddr2.Visible := False;
    TableOrdersShipToCity.Visible := False;
    TableOrdersShipToState.Visible := False;
    TableOrdersShipToZip.Visible := False;
    TableOrdersShipToCountry.Visible := False;
    TableOrdersShipToPhone.Visible := False;
    Duration := Now();
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    TableOrdersShipToContact.Visible := True;
    TableOrdersShipToAddr1.Visible := True;
    TableOrdersShipToAddr2.Visible := True;
    TableOrdersShipToCity.Visible := True;
    TableOrdersShipToState.Visible := True;
    TableOrdersShipToZip.Visible := True;
    TableOrdersShipToCountry.Visible := True;
    TableOrdersShipToPhone.Visible := True;
    scExcelExport1.Disconnect;
    scExcelExport1.OnGetCellStyleEvent := nil;
  end;
end;

//------------------------------------------------------------------------------
// Export dataset and save it as XLS, CVS and HTM in current folder. (ORDERS)
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportSaveClick(Sender: TObject);
begin
  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.ExcelVisible:=False;
    scExcelExport1.WorksheetName := 'TscExcelExport Save';
    scExcelExport1.Dataset:=TableOrders;
    StatusBar.Panels[1].Text := '';
    scExcelExport1.ExportDataset;
    scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExport.xls',ffXLS);
    scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExport.csv',ffCSV);
    {$IFNDEF DELPHI5}
    scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExport.htm',ffHTM);
    {$ENDIF}
  finally
    scExcelExport1.Disconnect;
  end;
end;

//-----------------------------------------------------------------------------
// Export dataset and show print preview of Excel (ORDERS)
//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnPrintPreviewClick(Sender: TObject);
begin
  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.ExcelVisible:=False;
    scExcelExport1.WorksheetName := 'TscExcelExport Print preview';
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := '';
    scExcelExport1.PrintPreview(True);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//------------------------------------------------------------------------------
// Export several datasets and use all options of the ConnectTo and Worksheetname property.
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnConnectToClick(Sender: TObject);
var
  Duration : TDateTime;
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 Biolife in active workbook in active excel
    scExcelExport1.Disconnect;
    scExcelExport1.Dataset:=TableBiolife;
    scExcelExport1.WorksheetName:='Biolife';
    scExcelExport1.ConnectTo := ctNewWorksheet;
    scExcelExport1.ShowTitles := True;
    scExcelExport1.ExportDataset;

    // Add data (of Biolife) in existing worksheet Animals
    scExcelExport1.Disconnect;
    scExcelExport1.Dataset:=TableBiolife;
    scExcelExport1.WorksheetName:='Biolife';
    scExcelExport1.ConnectTo := ctNewWorksheet;
    scExcelExport1.BeginColumnData := 10;
    scExcelExport1.ShowTitles := True;

    Duration := Now();
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//------------------------------------------------------------------------------
// Call routine of Save example. Open existing file and data to existing worksheet. (ANIMALS)
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnUseExistingClick(Sender: TObject);
var
  Duration : TDateTime;
begin
  BitBtnExportSaveClick(Sender);

  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.ConnectTo:=ctNewExcel;
    scExcelExport1.Dataset:=TableAnimals;
    // Open this file
    scExcelExport1.Filename:=ExtractFilePath(Application.ExeName)+'ExcelExport.xls';
    if FileExists(scExcelExport1.Filename) then
    begin
      // Add data to existing worksheet, starting at column 27 (=AA)
      scExcelExport1.WorksheetName:='TscExcelExport Save';
      scExcelExport1.BeginColumnData := 27;
      Duration := Now();
      scExcelExport1.ExportDataset;
      StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
    end;
  finally
    scExcelExport1.Disconnect;
  end;
end;

//------------------------------------------------------------------------------
// Export dataset, use grouping of 2 fields and add summaries
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnGroupingClick(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=QuerySortShipVia;

    scExcelExport1.SummarySelection := ssValues;
    scExcelExport1.SummaryCalculation := scSUM;
    scExcelExport1.BorderSummary.BackColor := clGreen;
    scExcelExport1.BorderSummary.BorderColor := clRed;
    scExcelExport1.BorderSummary.LineStyle := blLine;
    scExcelExport1.BorderSummary.Weight := bwThick;

    scExcelExport1.FontGroup := TxlFont(LabelTitleFont.Font);
    scExcelExport1.BorderGroup.BackColor := clYellow;
    scExcelExport1.WorksheetName:='TscExcelExport Grouping';
    scExcelExport1.ConnectTo := ctNewExcel;
    scExcelExport1.StyleColumnWidth := cwEnhAutoFit;
    scExcelExport1.GroupFields.Clear;
    scExcelExport1.GroupFields.Add('ShipVia');
    scExcelExport1.GroupFields.Add('Terms');
    //scExcelExport1.GroupFields.Add('OrderNo');

    scExcelExport1.BorderHeader.BackColor := clAqua;
    scExcelExport1.FontHeader := TxlFont(LabelHeaderFont.Font);
    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 := 3;

    Duration := Now();
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    scExcelExport1.Disconnect;
  end;
end;

//------------------------------------------------------------------------------
// Export dataset and change contents of the Excel worksheet manually
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnUseWorksheetClick(Sender: TObject);
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableAnimals;
    scExcelExport1.WorksheetName:='TscExcelExport example';
    scExcelExport1.ConnectTo := ctNewExcel;
    scExcelExport1.Connect;
    scExcelExport1.ExcelWorkSheet.Range['A2','C8'].Borders.Color := clRed;

    scExcelExport1.ExportDataset;

    scExcelExport1.ExcelWorkSheet.Range['B5','E7'].Cells.Clear;

    scExcelExport1.ExcelWorkSheet.Range[Format('A%d',[scExcelExport1.EndRowData+3]),
      Format('A%d',[scExcelExport1.EndRowData+3])].Font.Size := 16;
    scExcelExport1.ExcelWorkSheet.Range[Format('A%d',[scExcelExport1.EndRowData+3]),
      Format('A%d',[scExcelExport1.EndRowData+3])].Value := 'Adding extra information to Excel worksheet';

    scExcelExport1.ExcelWorkSheet.Range['M1','M1'].Value := 10;
    scExcelExport1.ExcelWorkSheet.Range['M2','M2'].Value := 5;
    scExcelExport1.ExcelWorkSheet.Range['M3','M3'].Value := '=M1+M2';
    scExcelExport1.ExcelWorkSheet.Range['M3','M3'].Font.Color := clRed;

    scExcelExport1.ExcelWorkSheet.Range['N1','N20'].Value := 'Filling extra column with autofit';
    scExcelExport1.ExcelWorkSheet.Range['N1','N20'].Font.Size := 12;
    scExcelExport1.ExcelWorkSheet.Range['N1','N20'].Font.Color := clBlue;
    scExcelExport1.ExcelWorkSheet.Range['N1','N20'].EntireColumn.Autofit;

    StatusBar.Panels[1].Text := '';
  finally
    scExcelExport1.Disconnect;
  end;
end;

//------------------------------------------------------------------------------
// Do not export a dataset, but export data using the events (OnGetFieldName, OnGetFieldValue, ...)
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnUseEventsClick(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    TableOrders.DisableControls;
    TableOrders.First;
    scExcelExport2.ExcelVisible:=True;
    scExcelExport2.ConnectTo := ctNewExcel;
    scExcelExport2.DataPipe := dpCustom;
    scExcelExport2.GroupFields.Clear;
    scExcelExport2.GroupFields.Add('ShipVia');
    Duration := Now();
    scExcelExport2.ExportDataset;
    StatusBar.Panels[1].Text := 'Duration : '+TimeToStr(Now() - Duration);
  finally
    scExcelExport2.Disconnect;
    TableOrders.EnableControls;
  end;
end;

//------------------------------------------------------------------------------
// Close all active Excel applications
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnCloseExcelClick(Sender: TObject);
begin
  scExcelExport1.CloseAllExcelApps;
end;

//------------------------------------------------------------------------------
// Get Excel version
//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExcelVersionClick(Sender: TObject);
begin
  ShowMessage(IntToStr(scExcelExport1.ExcelVersion));
end;

end.