mainexcelexport.pas

delphi读写取excel,内附源码和Demo文件

    scExcelExport1.ExportDataset;

    // Add data (of vendors) in existing worksheet Animals
    scExcelExport1.Disconnect;
    scExcelExport1.Dataset:=TableVendors;
    scExcelExport1.WorksheetName:='Animals';
    scExcelExport1.ConnectTo := ctNewWorksheet;
    scExcelExport1.BeginColumnData := 10;
    scExcelExport1.ShowTitles := True;
    scExcelExport1.ExportDataset;
  finally
    scExcelExport1.Disconnect;
  end;
end;

//-----------------------------------------------------------------------------
procedure TFormExcelExport.ChangeCellColors(Sender: TObject; Field: TField; var ColorBackground: TColor);
begin
  if Field.FieldName = 'CustNo' then
  begin
    if Field.Value > 2000 then
      ColorBackground := clRed;
    if Field.Value > 3000 then
      ColorBackground := clGreen;
  end;

  if Field.FieldName = 'EmpNo' then
  begin
    if Field.Value > 100 then
      ColorBackground := clYellow;
  end;
end;

//-----------------------------------------------------------------------------
// Export datasets to Excel and change background colors of cells
// the depending values of the fields (see procedure ChangeCellColors)
//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportDatasetColorsClick(Sender: TObject);
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.WorksheetName:='TscExcelExport colors example';
    scExcelExport1.ConnectTo := ctNewExcel;
    scExcelExport1.OnGetCellBackgroundColorEvent := ChangeCellColors;
    scExcelExport1.ExportDataset;
  finally
    scExcelExport1.Disconnect;
    scExcelExport1.OnGetCellBackgroundColorEvent := nil;
  end;
end;

//-----------------------------------------------------------------------------
// Export data to Excel by using the export events
// No dataset is connected to the TscExcelExport component
// Fieldnames, FieldValues, EOF, ... will be retrieved in the triggered events
//-----------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportEventsClick(Sender: TObject);
var
  Duration : TDateTime;
begin
  try
    TableOrders.DisableControls;
    TableOrders.First;
    scExcelExport2.ExcelVisible:=True;
    scExcelExport2.LoadDefaultProperties;
    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.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;
end;

//-----------------------------------------------------------------------------
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;
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.BitBtnExportExistingFileClick(Sender: TObject);
begin
  try
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.ConnectTo:=ctNewExcel;
    scExcelExport1.Dataset:=TableAnimals;
    // Open this file
    scExcelExport1.Filename:='c:\ExcelExport.xls';
    // Add data to existing worksheet
    scExcelExport1.WorksheetName:='TscExcelExport example';
    scExcelExport1.BeginColumnData := 30;
    scExcelExport1.ExportDataset;
  finally
    scExcelExport1.Disconnect;
  end;
end;

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

//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnExportGroupsClick(Sender: TObject);
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 example';
    scExcelExport1.ConnectTo := ctNewExcel;
    scExcelExport1.StyleColumnWidth := cwEnhAutoFit;
    scExcelExport1.GroupFields.Clear;
    scExcelExport1.GroupFields.Add('ShipVia');
    scExcelExport1.GroupFields.Add('Terms');
    //scExcelExport1.GroupFields.Add('OrderNo');

    scExcelExport1.BorderHeader.BackColor := clGreen;
    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;

    scExcelExport1.ExportDataset;
  finally
    scExcelExport1.Disconnect;
  end;
end;

//------------------------------------------------------------------------------
procedure TFormExcelExport.BitBtnAccessingExcelClick(Sender: TObject);
begin
  try
    scExcelExport1.ExcelVisible:=True;
    scExcelExport1.LoadDefaultProperties;
    scExcelExport1.Dataset:=TableOrders;
    scExcelExport1.WorksheetName:='TscExcelExport example';
    scExcelExport1.ConnectTo := ctNewExcel;
    scExcelExport1.Connect;
    scExcelExport1.ExcelWorkSheet.Range['A1','C10'].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';
  finally
    scExcelExport1.Disconnect;
  end;
end;

end.