mainexcelexport.pas

导出delphi程序的一些数据到

    TableOrdersShipVIA.Visible := True;
    TableOrdersPO.Visible := True;
    TableOrdersTerms.Visible := True;
    scExcelExport1.Disconnect;
  end;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExecute9Click(Sender: TObject);
begin
  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.ExcelVisible:=False;
    scExcelExport1.WorksheetName := 'TscExcelExport DEMO 9';
    scExcelExport1.Dataset:=TableOrders;
    StatusBar.Panels[1].Text := '';
    scExcelExport1.ExportDataset;

    scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExportDefault',ffDefault); //without file extension

    // Excel 2007 b鑤a Open XML format (file extension XLSX)
    if scExcelExport1.ExcelVersion = 12 then
      scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExport2007.xlsx',ffXLSX);

    // Excel 2000/XP/2003 format (file extension XLS)
    scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExport2003.xls',ffXLS);

    // Excel 95 and 97 compatible format
    // Does not work in Excel 2007
    if scExcelExport1.ExcelVersion <> 12 then
      scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExport97.xls',ffXL97);

    scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExportCSV.csv',ffCSV);

    // HTML
    // Only works with Excel 2000/XP/2003/2007
    if scExcelExport1.ExcelVersion >= 10 then
      scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExportHTM.htm',ffHTM);

    // XML spreadsheet
    // Only works with Excel XP/2003/2007
    if scExcelExport1.ExcelVersion >= 11 then
      scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExportXML.xml',ffXML);
  finally
    scExcelExport1.Disconnect(True);
  end;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExecute10Click(Sender: TObject);
begin
  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.ExcelVisible:=False;
    scExcelExport1.WorksheetName := 'TscExcelExport DEMO 10';
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.ExportDataset;
    StatusBar.Panels[1].Text := '';
    scExcelExport1.ExcelWorkSheet.PageSetup.Orientation := xlLandscape;
    scExcelExport1.PrintPreview(True);
  finally
    scExcelExport1.Disconnect(True);
  end;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExecute11Click(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    // Only use ctNewWorkbook and ctNewWorksheet when performance is
    // important and when an Excel instance has been started first
    // with ctNewExcel
    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;
    scExcelExport1.Disconnect;

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

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

    // Add data (of Biolife) in existing worksheet Animals
    scExcelExport1.Dataset:=TableBiolife;
    scExcelExport1.WorksheetName:='Animals';
    scExcelExport1.ConnectTo := ctNewWorksheet;
    scExcelExport1.BeginRowTitles := 14;
    scExcelExport1.BeginRowData := 15;
    scExcelExport1.BeginColumnData := 2;
    scExcelExport1.ShowTitles := True;

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

//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExecute12Click(Sender: TObject);
begin
  // Create new workbook, add worksheet with Orders and save it
  scExcelExport1.LoadDefaultProperties;
  scExcelExport1.FontData.Color := clRed;
  scExcelExport1.ExcelVisible:=False;
  scExcelExport1.Dataset:=TableOrders;
  scExcelExport1.WorksheetName:='Orders1';
  scExcelExport1.HeaderText.Text := 'ORDERS';
  scExcelExport1.BeginRowHeader := 1;
  scExcelExport1.BeginRowTitles := 3;
  scExcelExport1.BeginRowData := 4;
  scExcelExport1.FontHeader.Size := 12;
  scExcelExport1.FontHeader.Style := [fsBold];
  scExcelExport1.AutoFilter := True;
  scExcelExport1.ExportDataset;

  // Do not disconnect, so set other font color and other worksheet
  // and export dataset again
  scExcelExport1.FontData.Color := clGreen;
  scExcelExport1.WorksheetName:='Orders2';
  scExcelExport1.ExportDataset;

  // Save worksheet
  scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExport.xls',ffXLS);
  scExcelExport1.Disconnect;

  // Load workbook, add worksheet with Orders again to new worksheet and save it
  // Clear all properties (font, position, text, ...)
  scExcelExport1.LoadDefaultProperties;
  scExcelExport1.ExcelVisible:=False;
  scExcelExport1.Dataset:=TableOrders;
  scExcelExport1.WorksheetName:='Orders3';
  scExcelExport1.Filename:=ExtractFilePath(Application.ExeName)+'ExcelExport.xls';
  scExcelExport1.HeaderText.Text := 'ORDERS';
  scExcelExport1.ExportDataset;
  scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExport.xls',ffXLS);
  scExcelExport1.Disconnect;

  // Load workbook, add worksheet with Animals and save it
  // Set font, beginrow and header
  scExcelExport1.LoadDefaultProperties;
  scExcelExport1.FontData.Name := 'Times New Roman';
  scExcelExport1.FontData.Color := clRed;
  scExcelExport1.FontHeader.Size := 16;
  scExcelExport1.BeginRowHeader := 7;
  scExcelExport1.BeginRowTitles := 9;
  scExcelExport1.BeginRowData := 10;
  scExcelExport1.HeaderText.Text := 'ANIMALS';
  scExcelExport1.StyleColumnWidth := cwAutoFit;

  scExcelExport1.ExcelVisible:=False;
  scExcelExport1.Dataset:=TableAnimals;
  scExcelExport1.WorksheetName:='Animals';
  scExcelExport1.Filename:=ExtractFilePath(Application.ExeName)+'ExcelExport.xls';
  scExcelExport1.ExportDataset;
  scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExport.xls',ffXLS);
  scExcelExport1.Disconnect;

  // Load workbook, add worksheet with Biolife and save it
  // Clear only properties of position and text. Do not clear font
  scExcelExport1.LoadDefaultProperties([pgPositions,pgText]);
  scExcelExport1.ExcelVisible:=False;
  scExcelExport1.Dataset:=TableBiolife;
  scExcelExport1.WorksheetName:='Biolife';
  scExcelExport1.Filename:=ExtractFilePath(Application.ExeName)+'ExcelExport.xls';
  scExcelExport1.ExportDataset;
  scExcelExport1.SaveAs(ExtractFilePath(Application.ExeName)+'ExcelExport.xls',ffXLS);
  scExcelExport1.Disconnect;

  // Load workbook, use existing worksheet Biolife, add Animals to
  // it at row 35 and column 3. Rename worksheet after exporting
  // and show the result
  // Clear all properties
  scExcelExport1.LoadDefaultProperties;
  scExcelExport1.ExcelVisible:=True; // Last action, so show the result
  scExcelExport1.Dataset:=TableAnimals;
  scExcelExport1.WorksheetName:='Biolife'; //Existing worksheet
  scExcelExport1.Filename:=ExtractFilePath(Application.ExeName)+'ExcelExport.xls';
  scExcelExport1.BeginRowTitles := 35;
  scExcelExport1.BeginRowData := 38;
  scExcelExport1.BeginColumnHeader := 3;
  scExcelExport1.BeginColumnData := 3;
  scExcelExport1.ExportDataset;
  scExcelExport1.ExcelWorkSheet.Name := 'Biolife and Animals'; // Rename worksheet
  scExcelExport1.Disconnect;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExecute13Click(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=QuerySortShipVia;

    scExcelExport1.SummarySelection := ssValues;
    scExcelExport1.SummaryCalculation := scAVG;
    scExcelExport1.SummaryDisplayFormat := '###0.000';
    scExcelExport1.BorderSummary.BackColor := clGreen;
    scExcelExport1.BorderSummary.BorderColor := clRed;
    scExcelExport1.BorderSummary.LineStyle := blLine;
    scExcelExport1.BorderSummary.Weight := bwThick;

    scExcelExport1.FontGroup := TxlFont(PanelTitle13.Font);
    scExcelExport1.BorderGroup.BackColor := clGray;
    scExcelExport1.WorksheetName:='TscExcelExport DEMO 13';
    scExcelExport1.StyleColumnWidth := cwEnhAutoFit;
    scExcelExport1.GroupFields.Clear;
    scExcelExport1.GroupFields.Add('ShipVia');
    scExcelExport1.GroupFields.Add('Terms');
    //scExcelExport1.GroupFields.Add('OrderNo');

    scExcelExport1.BorderHeader.BackColor := clBlue;
    scExcelExport1.FontHeader := TxlFont(PanelTitle13.Font);
    scExcelExport1.FontHeader.Alignment := haCenter;
    scExcelExport1.FontFooter.Alignment := haCenter;
    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.MergeHeaderCells := True;
    scExcelExport1.MergeFooterCells := True;
    scExcelExport1.AutoFilter := True;
    scExcelExport1.BeginRowHeader := 3;

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

//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExecute14Click(Sender: TObject);
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableAnimals;
    scExcelExport1.WorksheetName:='TscExcelExport DEMO 14';
    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])].Value2 := 'Adding extra information to Excel worksheet';

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

    scExcelExport1.ExcelWorkSheet.Range['N1','N20'].Value2 := '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;

    scExcelExport1.ExcelWorkSheet.Range['B2','B2'].AddComment('This is comment for a cell');

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

//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExecute15Click(Sender: TObject);
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableAnimals;
    scExcelExport1.WorksheetName:='TscExcelExport DEMO 15';
    scExcelExport1.ConnectTo := ctNewExcel;
    scExcelExport1.BorderTitles.BackColor := clSilver;
    scExcelExport1.AutoFilter := True;

    scExcelExport1.ExportDataset;

    scExcelExport1.ExcelWorkSheet.Range['A2','A'+IntToStr(scExcelExport1.EndRowData)].Interior.Color := clSilver;

    scExcelExport1.ExcelWorkSheet.Range['B2','B2'].Select;
    scExcelExport1.ExcelApplication.ActiveWindow.FreezePanes := True;

    {$IFDEF EXCELXP}
    scExcelExport1.ExcelWorkSheet.Range['C1','C'+IntToStr(scExcelExport1.EndRowData)].AutoFilter(3,'>=8',xlAnd,EmptyParam,True);
    {$ENDIF}

    {$IFNDEF EXCEL97}
    scExcelExport1.ExcelWorkBook.WebPagePreview;
    {$ENDIF}

    scExcelExport1.ExcelWorkBook.PivotTableWizard;
  finally
    scExcelExport1.Disconnect;
  end;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExecute16Click(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;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExecute17Click(Sender: TObject);
begin
  scExcelExport1.CloseAllExcelApps;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExecute18Click(Sender: TObject);
begin
  ShowMessage(IntToStr(scExcelExport1.ExcelVersion));
end;

end.