urealdbexcel.pas

超级好用的韩国数据表控件一共5个 Korea, a data table control

unit URealDBExcel;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  URGrids, URDBGrid, UCustomExcel, Db;

type
  { TRealDBExcel }
  TRealDBExcel = class(TwCustomExcel)
  private
    FGridColAttrib: Boolean;
    FMaxCount: Integer;
    (* property methods *)
    function  GetGrid: TRealDBGrid;
    procedure SetGrid(V: TRealDBGrid);
    procedure SetGridColAttrib(const Value: Boolean);
    procedure SetMaxCount(const Value: Integer);

  protected
    function ConvertBody(rStart: Integer): Integer; override;

  public
    constructor Create(AComponent: TComponent); override;
    destructor  Destroy; override;

    (* properties *)

  published
    (* properties from TwCustomExcel *)
    property AutoFit;
    property Title;
    property Header;
    property Footer;
    property FileName;
//    property RowNumber;
    property SheetName;
    property ShowExcel;
    property GridColAttrib : Boolean read FGridColAttrib write SetGridColAttrib;
    property MaxCount : Integer read FMaxCount write SetMaxCount Default -1;
    (* properties *)
    property Grid: TRealDBGrid read GetGrid write SetGrid;
  end;

implementation

uses UExcelUtils, UEXcelPrograss;


{ TRealDBExcel }
//== init & final ====================================================================//
constructor TRealDBExcel.Create(AComponent: TComponent);
begin
  inherited;
  FMaxCount := -1;
end;

destructor TRealDBExcel.Destroy;
begin
  inherited;
end;

//== property methods ================================================================//
function TRealDBExcel.GetGrid: TRealDBGrid;
begin
  Result := TRealDBGrid(inherited Grid);
end;

procedure TRealDBExcel.SetGrid(V: TRealDBGrid);
begin
  inherited Grid := V;
end;

//== override methods ================================================================//
function TRealDBExcel.ConvertBody(rStart: Integer): Integer;

  function DrawColumn(ARow: Integer): Integer;
  var
    bNum: Integer;
    ii,
    i,j,k,l   : Integer;
    r   : OleVariant;
    Arr : OleVariant;

  begin
    bNum := Ord(RowNumber);

    with Grid do
    begin

      Arr := VarArrayCreate([0,ColCount+2], varVariant );
      I := 0;

      Grid.DataSource.DataSet.First;
      If groupMode Then
        While not Grid.DataSource.DataSet.EOF do
        Begin
          Arr[0] := I + 1;
          L := 1;
          for J := 0 to VGroupCount - 1 do
            for k := 0 to Vgroups[ j ].ColCount[0] - 1 do
            begin
              II := -1;
              If TwDBColumn(ColumnByVCell(WCell(J, 0, k))).Items.Count > 0 Then
                II := TwDBColumn(ColumnByVCell(WCell(J, 0, k))).values.IndexOf(TwDBColumn(ColumnByVCell(WCell(J, 0, k))).Field.Text);

              if II >= 0 Then
                Arr[L] := '''' + TwDBColumn(ColumnByVCell(WCell(J, 0, k))).Items[ II ]
              Else If varType(TwDBColumn(ColumnByVCell(WCell(J, 0, k))).Field.Value) = VarString  then
                Arr[L] := '''' + TwDBColumn(ColumnByVCell(WCell(J, 0, k))).Field.value
              Else
                Arr[L] := TwDBColumn(ColumnByVCell(WCell(J, 0, k))).Field.value;
              Inc(L);
            End;
          r := GetRange(1, rStart+I, L+1, RStart+i);
          R.value := Arr;
          Grid.DataSource.DataSet.Next;
          Inc(i);
          Excel_process(I);
        End
      Else
        While not Grid.DataSource.DataSet.EOF do
        Begin
          Arr[0] := I+1;
          for J := 0 to VColCount - 1 do
          begin
              II := -1;
              If DBVColumns[j].Items.Count > 0 Then
                II := DBVColumns[j].values.IndexOf(DBVColumns[j].Field.Text);

              if II >= 0 Then
                Arr[j+1] := '''' + DBVColumns[j].Items[ II ]
              Else If varType(DBVColumns[j].Field.Value) = VarString  then
                Arr[J+1] := ''''+DBVColumns[j].Field.Text
              Else
                Arr[J+1] := DBVColumns[j].Field.Text;
          End;
          r := GetRange(1, rStart+I, 1+VColCount, RStart+i);
          R.value := Arr;
          Inc(I);
          Grid.DataSource.DataSet.Next;
          Excel_process(I);
        End;

      r := Getrange( 1, rStart, 1, rStart + I);
      with Indicators do
      begin
        SetBorder(r);
        r.HorizontalAlignment := _EXCEL_HALIGN[Alignment];
        r.VerticalAlignment   := _EXCEL_VALIGN[Layout];
        _Convert(Font, r.Font);
        r.Interior.Color      := ColorToRGB(Color);
      end;

      Result := I;
      // 拿烦阑 器窃 救且波搁...
      If not FGridColAttrib Then Exit;

      If groupMode Then
      begin
        L := 2;
        for J := 0 to VGroupCount - 1 do
        Begin
            for k := 0 to groups[ j ].ColCount[0] - 1 do
            begin
              With ColumnByVCell( wCell( J,0,k ) ) Do
              Begin
                r := Getrange( L, rStart, L, rStart + I);
                SetBorder(r);
                r.HorizontalAlignment := _EXCEL_HALIGN[Alignment];
                r.VerticalAlignment   := _EXCEL_VALIGN[Layout];
                _Convert(Font, r.Font);
                r.Interior.Color      := ColorToRGB(Color);
              End;
              Inc(L);
            End;
        End;
      End
      Else
      Begin
        for J := 0 to VColCount - 1 do
        begin
          With VColumns[j] Do
          begin
            r := Getrange( J+2, rStart, J+2, rStart + I);
            SetBorder(r);
            r.HorizontalAlignment := _EXCEL_HALIGN[Alignment];
            r.VerticalAlignment   := _EXCEL_VALIGN[Layout];
            _Convert(Font, r.Font);
            r.Interior.Color      := ColorToRGB(Color);
          End;
        End;
      End;

    end;

  end;

  function DrawGroup(APos, ARow: Integer): Integer;
  var
    g      : TwExcelGroups;
    x      : Integer;
    i, j, k: Integer;
    r, c   : OleVariant;
  begin
    g  := FGroups;
    x  := 1;

    with Grid do
    begin
      if RowNumber then
      begin
        r := GetRange(x, APos, x, APos + g.RowHeight - 1);
        r.Merge;

        with Indicators do
        begin
          SetBorder(r);
          r.HorizontalAlignment := _EXCEL_HALIGN[Alignment];
          r.VerticalAlignment   := _EXCEL_VALIGN[Layout];
          _Convert(Font, r.Font);
          r.Interior.Color      := ColorToRGB(Color);
          r.Value               := ARow + 1;
        end;

        Inc(x);
      end;

      for i := 0 to VGroupCount - 1 do
      begin
        r := GetRange(x, APos, x + g[i].Width - 1, APos + g.RowHeight - 1);

        with VGroups[i] do
          for j := 0 to Levels - 1 do
            for k := 0 to ColCount[j] - 1 do
            begin
                with g[i][j, k] do
//                  c := GetRange(r, X1, Y1, X2, Y2);
                  c := GetRange(X+ X1-1, APos + Y1-1, X+X2-1, aPos + Y2-1);
                c.Merge;

                with Columns[j, k] do
                  If Assigned(TwDBColumn(ColumnByCell(WCell(i, j, k))).Field) Then
                  begin
                    If varType(TwDBColumn(ColumnByCell(WCell(i, j, k))).Field.Value) = VarString  then
                      c.Value := ''''+TwDBColumn(ColumnByCell(WCell(i, j, k))).Field.AsString
                    Else
                      c.Value := TwDBColumn(ColumnByCell(WCell(i, j, k))).Field.AsString;
                  end
                  else
                    c.Value := ' ';

                If FGridColAttrib Then
                begin
                  with Columns[j, k] do
                  begin
                    SetBorder(c);
                    c.HorizontalAlignment := _EXCEL_HALIGN[Alignment];
                    c.VerticalAlignment   := _EXCEL_VALIGN[Layout];
                    _Convert(Font, C.Font);
                    c.Interior.Color      := ColorToRGB(Color);
                  end;
                End;
            end;

        Inc(x, g[i].Width);
      end;
    end;

    Result := g.RowHeight;
  end;

var
  i: Integer;
  p: Integer;
  r: OleVariant;
  BM : TBookmark;
begin

  try
    BM := Grid.DataSource.DataSet.GetBookmark;

    Result := 0;

    with Grid do
      if (DataSource = nil) or (DataSource.DataSet = nil) or
         not DataSource.DataSet.Active or Grid.DataSource.DataSet.IsEmpty then
        Exit;

    I := 0;
    If not grid.ColumnMode Then
      for i := 0 to Grid.ColCount - 1 do
        If grid.Columns[i].Level > 0 Then
          Break;

    If ( i = grid.ColCount ) or Grid.ColumnMode then
    begin

        Excel_Set( FMaxCount );
        Grid.DataSource.DataSet.First;
        Grid.DataSource.DataSet.DisableControls;
        Result := DrawColumn(0);
        Grid.DataSource.DataSet.EnableControls;

      if not AutoFit then
      begin
        r := GetRange(1, rStart, 1, rStart + Result - 1);

        if Grid.Indicators.Visible then
          r.ColumnWidth := (Grid.Indicators.Width + 7) div 8
        else
          r.ColumnWidth := Length(IntToStr(Result)) + 1;
      end;

    end
    else
    begin
      FGroups.Load(Self);

      p := rStart;
      i := 0;
      Grid.DataSource.DataSet.First;

      Excel_Set( FMaxCount );
      while not Grid.DataSource.DataSet.EOF do
      begin
        Inc(Result, DrawGroup(p + Result, i));
        Grid.DataSource.DataSet.Next;
        Inc(i);
        Excel_process(I);
      end;

      if not AutoFit and (Result > 0) then
      begin
        r := GetRange(1, rStart, 1, rStart + FGroups.RowHeight - 1);

        if Grid.Indicators.Visible then
          r.ColumnWidth := (Grid.Indicators.Width + 7) div 8
        else
          r.ColumnWidth := Length(IntToStr(Result)) + 1;
      end;
    end;
  Finally
    Grid.DataSource.DataSet.GotoBookmark( BM );
    Grid.DataSource.DataSet.FreeBookmark( BM );
  End;

end;

procedure TRealDBExcel.SetGridColAttrib(const Value: Boolean);
begin
  FGridColAttrib := Value;
end;

procedure TRealDBExcel.SetMaxCount(const Value: Integer);
begin
  FMaxCount := Value;
end;

end.