jcamtableexporttoexcel.pas

销售软件

(*===========================================================*)
(*                                                           *)
(*              Jerk Computer Assembly Manager               *)
(*                                                           *)
(*                    程序作者：杨芹勍                       *)
(*          武汉科技大学 理学院 信息与计算科学031班          *)
(*                  武汉科技大学 莘特工作室                  *)
(*                                                           *)
(*              IDE：Borland Delphi 2006 Update 2            *)
(*                 第三方控件：Raize 4.03                    *)
(*             数据库：Microsoft SQL Server 2000             *)
(*            数据库访问引擎：原生ADO（ADODB_TLB）           *)
(*           数据库管理引擎：JERK DBMANAGER ALPHA            *)
(*                                                           *)
(*           此软件及源代码归 JERK SYSTEM 版权所有           *)
(*            (C)Copyright 2002-2006 Jerk System.            *)
(*                                                           *)
(*===========================================================*)

unit JCAMTableExportToExcel;

interface

uses
  Windows,
  Messages,
  SysUtils,
  Variants,
  Classes,
  StrUtils,
  Graphics,
  Controls,
  Forms,
  ExcelXP,
  ADODB_TLB,
  ComCtrls,
  JSDBListView,
  JCAMConsts,
  JCAMUtils;

type
  TJCAMTETEExportFields = ( efExportAll, efExportDBLVFields );
  TJCAMTETEExportSelected = ( esExportAll, esExportSelected );
  TJCAMTETECellsAdding = procedure(
    Sender: TObject; ews: TExcelWorksheet; varKeyFieldValue: Variant ) of
    object;
  TJCAMTableExportToExcel = class
  private
    m_cnTETE: _Connection;
    m_dvwTETE: TJSDBListView;
    m_ewsTETE: TExcelWorksheet;
    m_ewbTETE: TExcelWorkbook;
    m_eaTETE: TExcelApplication;
    m_efTETE: TJCAMTETEExportFields;
    m_esTETE: TJCAMTETEExportSelected;
    m_eCellsAdding: TJCAMTETECellsAdding;
    m_sTableViewName: string;
    m_sKeyFieldName: string;
    m_sCondition: string;
    procedure SetDBListView( const Value: TJSDBListView );
    procedure SetTableViewName( const Value: string );
    procedure SetExcelApplication( const Value: TExcelApplication );
    procedure SetExcelWorkBook( const Value: TExcelWorkbook );
    procedure SetExcelWorkSheet( const Value: TExcelWorkSheet );
    procedure SetExportFields( const Value: TJCAMTETEExportFields );
    procedure SetConnection( const Value: _Connection );
    procedure SetExportSelected( const Value: TJCAMTETEExportSelected );
    procedure SetOnCellsAdding( const Value: TJCAMTETECellsAdding );
    procedure SetKeyFieldName( const Value: string );
    procedure SetCondition( const Value: string );
  protected
  public
    constructor Create;
    destructor Destroy; override;
    property Connection: _Connection
      read m_cnTETE
      write SetConnection;
    property ExportFields: TJCAMTETEExportFields
      read m_efTETE
      write SetExportFields
      default efExportDBLVFields;
    property ExportSelected: TJCAMTETEExportSelected
      read m_esTETE
      write SetExportSelected
      default esExportAll;
    property ExcelWorkSheet: TExcelWorkSheet
      read m_ewsTETE
      write SetExcelWorkSheet;
    property ExcelWorkBook: TExcelWorkbook
      read m_ewbTETE
      write SetExcelWorkBook;
    property ExcelApplication: TExcelApplication
      read m_eaTETE
      write SetExcelApplication;
    property DBListView: TJSDBListView
      read m_dvwTETE
      write SetDBListView;
    property TableViewName: string
      read m_sTableViewName
      write SetTableViewName;
    property OnCellsAdding: TJCAMTETECellsAdding
      read m_eCellsAdding
      write SetOnCellsAdding;
    property KeyFieldName: string
      read m_sKeyFieldName
      write SetKeyFieldName;
    property Condition: string
      read m_sCondition
      write SetCondition;
    procedure ConnectExcel;
    procedure StartSurf; virtual;
    function InsertFieldsHeader( ewsTETE: TExcelWorksheet ): Boolean; virtual;
    procedure ExportTableData( ewsTETE: TExcelWorksheet; bExportFieldsHeader:
      Boolean = True ); virtual;
  end;

function RangeToExcelRange( nRowIdx, nFieldIdx: Integer ): string;

implementation

uses
  untTField;

{ TJCAMTableExportToExcel }

function RangeToExcelRange( nRowIdx, nFieldIdx: Integer ): string;
var
  n: Integer;
begin
  Result := EmptyStr;
  repeat
    n := nFieldIdx mod 26;
    nFieldIdx := nFieldIdx div 26;
    Result := Chr( 64 + n ) + Result;
  until nFieldIdx = 0;
  Result := Result + IntToStr( nRowIdx );
end;

procedure TJCAMTableExportToExcel.ConnectExcel;
begin
  if ( m_eaTETE <> nil ) and ( m_ewbTETE <> nil ) and ( m_ewsTETE <> nil ) then
  begin
    m_eaTETE.Connect;
    m_eaTETE.Visible[ 0 ] := True;
    m_ewbTETE.ConnectTo( m_eaTETE.Workbooks.Add( Null, 0 ) );
    m_ewsTETE.ConnectTo( m_ewbTETE.Sheets[ 1 ] as _WorkSheet );
  end;
end;

constructor TJCAMTableExportToExcel.Create;
begin
  m_efTETE := efExportDBLVFields;
  m_esTETE := esExportAll;
  m_eaTETE := TExcelApplication.Create( Application );
  m_ewbTETE := TExcelWorkbook.Create( Application );
  m_ewsTETE := TExcelWorksheet.Create( Application );
end;

destructor TJCAMTableExportToExcel.Destroy;
begin
  (*FreeAndNil( m_eaTETE );
  FreeAndNil( m_ewsTETE );
  FreeAndNil( m_ewbTETE );*)

  inherited;
end;

procedure TJCAMTableExportToExcel.ExportTableData( ewsTETE: TExcelWorksheet;
  bExportFieldsHeader: Boolean );

  function GetDBLVFields( DBListView: TJSDBListView; var bAutoIncludeKeyField:
    Boolean ): string;
  var
    rs: _Recordset;
    sFieldName: string;
    n: Integer;
    bHaveKeyField: Boolean;
  begin
    Result := EmptyStr;
    bAutoIncludeKeyField := False;
    if DBListView <> nil then
    begin
      rs := CoRecordset.Create;
      rs.Open( DBListView.SQLString, DBListView.Connection, adOpenForwardOnly,
        adLockReadOnly, adCmdText );

      bHaveKeyField := False;
      // TODO: 根据DBLV的列表头生成字段
      for n := 0 to DBListView.Columns.Count - 1 do
      begin
        sFieldName := rs.Fields[ DBListView.Columns[ n ].Tag ].Name;
        Result := Result + '[' + sFieldName + '],';
        if sFieldName = KeyFieldName then
          bHaveKeyField := True;
      end;
      Result := LeftBStr( Result, Length( Result ) - 1 );

      if not bHaveKeyField then
      begin
        bAutoIncludeKeyField := True;
        Result := '[' + KeyFieldName + '],' + Result;
      end;

      rs.Close;
      rs := nil;
    end;
  end;

  function RecordsetToExcelCells( ews: TExcelWorksheet; rs: _Recordset;
    bShowKeyField: Boolean ): Boolean;
  var
    n, nInsertRow, nColumnIdx: Integer;
  begin
    Result := False;
    if RecordsetValid( rs ) and ( KeyFieldName <> EmptyStr ) then
    begin
      repeat
        // TODO: 获取尾行行号
        nInsertRow := ews.UsedRange[ 1 ].Rows.Count + 1;
        nColumnIdx := 0;
        for n := 0 to rs.Fields.Count - 1 do
          if ( rs.Fields[ n ].Name <> KeyFieldName ) or bShowKeyField then
          begin
            Inc( nColumnIdx );
            ews.Cells.Item[ nInsertRow, nColumnIdx ] := rs.Fields[ n ].Value;
          end;

        if Assigned( m_eCellsAdding ) then
          m_eCellsAdding( Self, ews, rs.Fields[ KeyFieldName ].Value );
        rs.MoveNext;
      until rs.EOF;
      rs.Close;
      Result := True;
    end;
  end;

var
  rs: _Recordset;
  li: TListItem;
  sFields, sKeyFieldValue, sSQLString: string;
  bAutoIncludeKeyField, bShowKeyField: Boolean;
begin
  { 导出数据 }

  if ( ewsTETE <> nil ) and ( TableViewName <> EmptyStr ) then
  begin
    rs := CoRecordset.Create;

    // TODO: 插入列表头
    if bExportFieldsHeader then
      InsertFieldsHeader( ewsTETE );

    // TODO: 判断导出全部字段还是DBListView的字段
    if ExportFields = efExportAll then
      sFields := '*'
    else if DBListView <> nil then
      sFields := GetDBLVFields( DBListView, bAutoIncludeKeyField );
    bShowKeyField := not bAutoIncludeKeyField or ( ExportFields = efExportAll );

    // TODO: 判断为全部导出还是只导出选择的部分
    if ExportSelected = esExportAll then
    begin
      // TODO: 根据SQL获取记录集
      sSQLString := 'SELECT ' + sFields + ' FROM [' + TableViewName + ']';
      if Condition <> EmptyStr then
        sSQLString := sSQLString + ' WHERE ' + Condition;
      rs.Open( sSQLString, Connection, adOpenForwardOnly, adLockReadOnly,
        adCmdText );
      RecordsetToExcelCells( ewsTETE, rs, bShowKeyField );
    end
    else if DBListView <> nil then
    begin
      // TODO: 以下代码为导出选中的记录
      li := nil;
      repeat
        li := DBListView.GetNextItem( li, sdAll, [ isSelected ] );
        if li <> nil then
        begin
          sKeyFieldValue := DBListView.ItemKeyFieldValue( li.Index );
          sSQLString := 'SELECT ' + sFields + ' FROM [' + TableViewName +
            '] WHERE [' + DBListView.KeyFieldName + ']=' + sKeyFieldValue;
          if Condition <> EmptyStr then
            sSQLString := sSQLString + ' AND ' + Condition;
          rs.Open( sSQLString, Connection, adOpenForwardOnly, adLockReadOnly,
            adCmdText );
          RecordsetToExcelCells( ewsTETE, rs, bShowKeyField );
        end;
      until li = nil;
    end;
    rs := nil;
  end;
end;

function TJCAMTableExportToExcel.InsertFieldsHeader( ewsTETE: TExcelWorksheet
  ): Boolean;
var
  rs: _Recordset;
  tf: TTableField;
  n, nInsertRow, nFieldsCount: Integer;
begin
  { 插入列表头 }

  nFieldsCount := -1;
  Result := False;
  if ewsTETE <> nil then
  begin
    tf := TTableField.Create;
    tf.Connection := Self.Connection;

    // TODO: 插入的行号，如果没有指定则插入最下面一行
    nInsertRow := ewsTETE.UsedRange[ 1 ].Rows.Count;
    if nInsertRow <> 1 then
      Inc( nInsertRow );

    // TODO: 判断是否为导出所有列表头还是指定的DBLV的列表头
    if ( ExportFields = efExportDBLVFields ) and ( DBListView <> nil ) then
    begin
      nFieldsCount := DBListView.Columns.Count;
      for n := 0 to nFieldsCount - 1 do
      begin
        tf.FieldName := DBListView.FieldName( n + 1 );
        ewsTETE.Cells.Item[ nInsertRow, n + 1 ] := tf.FieldChs;
      end;
      Result := True;
    end
    else if ( Connection <> nil ) and ( TableViewName <> EmptyStr ) then
    begin
      rs := CoRecordset.Create;
      rs.Open( 'SELECT TOP 0 * FROM [' + TableViewName + ']',
        Connection, adOpenForwardOnly, adLockReadOnly, adCmdText );
      if rs.State <> adStateClosed then
      begin
        nFieldsCount := rs.Fields.Count;
        for n := 0 to nFieldsCount - 1 do
        begin
          tf.FieldName := rs.Fields[ n ].Name;
          ewsTETE.Cells.Item[ nInsertRow, n + 1 ] := tf.FieldChs;
        end;
        rs.Close;
      end;
      rs := nil;
      Result := True;
    end;
    FreeAndNil( tf );

    // TODO: 设置粗体
    if Result and ( nFieldsCount <> -1 ) then
      ewsTETE.Range[ RangeToExcelRange( nInsertRow, 1 ), RangeToExcelRange(
        nInsertRow, nFieldsCount ) ].Font.Bold := True;
  end;
end;

procedure TJCAMTableExportToExcel.SetCondition( const Value: string );
begin
  m_sCondition := Value;
end;

procedure TJCAMTableExportToExcel.SetConnection( const Value: _Connection );
begin
  m_cnTETE := Value;
end;

procedure TJCAMTableExportToExcel.SetDBListView( const Value: TJSDBListView );
begin
  m_dvwTETE := Value;
end;

procedure TJCAMTableExportToExcel.SetExcelApplication(
  const Value: TExcelApplication );
begin
  m_eaTETE := Value;
end;

procedure TJCAMTableExportToExcel.SetExcelWorkBook( const Value: TExcelWorkbook
  );
begin
  m_ewbTETE := Value;
end;

procedure TJCAMTableExportToExcel.SetExcelWorkSheet(
  const Value: TExcelWorkSheet );
begin
  m_ewsTETE := Value;
end;

procedure TJCAMTableExportToExcel.SetExportFields(
  const Value: TJCAMTETEExportFields );
begin
  m_efTETE := Value;
end;

procedure TJCAMTableExportToExcel.SetExportSelected(
  const Value: TJCAMTETEExportSelected );
begin
  m_esTETE := Value;
end;

procedure TJCAMTableExportToExcel.SetKeyFieldName( const Value: string );
begin
  m_sKeyFieldName := Value;
end;

procedure TJCAMTableExportToExcel.SetOnCellsAdding( const Value:
  TJCAMTETECellsAdding );
begin
  m_eCellsAdding := Value;
end;

procedure TJCAMTableExportToExcel.SetTableViewName( const Value: string );
begin
  m_sTableViewName := Value;
end;

procedure TJCAMTableExportToExcel.StartSurf;
begin

end;

end.