uxlsreadwriteagent.pas

抽象三层访问数据库示例

{*******************************************************}
{       软件名称: --通用--                              }
{       单元名称: uXLSReadWriteAgent.pas                }
{       中文名称: Excel访问代理实现子类                 }
{       单元描述: 通过TXLSReadWriteII2实现Excel读写。此 }
{                 不需客户机安装Excel即可实现读写。     }
{       创    建: SamonHua                              }
{       创建日期: 2007-12-19                            }
{       修    改: 参见VSS记录                           }
{       版权所有 (C)2002-2007 深圳壹平台信息技术有限公司}
{*******************************************************}
unit uXLSReadWriteAgent;

interface

uses
  SysUtils, Classes, Variants, Cell2, XLSUtils2, XLSReadWriteII2, SheetData2,
  uExcelAgent;

type
  TXLSReadWriteAgent = class(TExcelAgent)
  private
    FXLSReadWrite: TXLSReadWriteII2;
    function GetXLSReadWrite: TXLSReadWriteII2;
    function GetMergedCellIndex(Col, Row: integer): integer;
    procedure DeleteMergedCellByIndex(Index: integer);
  protected
    function GetSheetCount: Integer; override;
    function GetActiveSheetName: string; override;
  public
    property XLSReadWrite: TXLSReadWriteII2 read GetXLSReadWrite;
    //读入和保存文件
    procedure ReadFile; override;
    procedure WriteFile; override;
    procedure WriteToStream(Stream: TStream); override;
    //Sheet相关方法
    function AddSheet(SheetName: string): Integer; override;
    function InsertSheet(SheetName: string; SheetIndex: Integer = -1): Integer; override;
    procedure DeleteSheet(SheetIndex: Integer); override;
    //单元格读方法
    function GetCellValue(Col, Row: integer): Variant; override;
    function GetCellStringValue(Col, Row: integer; Default: string = ''): string; override;
    function GetCellIntegerValue(Col, Row: integer; Default: Integer = 0): Integer; override;
    function GetCellFloatValue(Col, Row: integer; Default: Double = 0): Double; override;
    function GetCellDateTimeValue(Col, Row: integer): TDateTime; override;
    function GetCellBooleanValue(Col, Row: integer; Default: Boolean = False): Boolean; override;
    //单元格写方法
    procedure SetCellValue(Col, Row: integer; Value: Variant); override;
    procedure SetCellStringValue(Col, Row: integer; Value: string); override;
    procedure SetCellIntegerValue(Col, Row: integer; Value: integer); override;
    procedure SetCellFloatValue(Col, Row: integer; Value: Double); override;
    procedure SetCellDateTimeValue(Col, Row: integer; Value: TDateTime); override;
    procedure SetCellBooleanValue(Col, Row: integer; Value: Boolean); override;
    //合并/拆分单元格
    procedure MergedCells(Col1, Row1, Col2, Row2: integer); override;
    procedure DeleteMergedCell(Col1, Row1, Col2, Row2: integer); override;
    procedure DeleteMergedCellByCell(Col, Row: integer); override;
    //获取包含指定单元格的坐标
    function GetMergedCell(Col, Row: integer; var Col1, Row1, Col2, Row2: integer): boolean; override;
  end;

implementation

{ TXLSReadWriteAgent }

function TXLSReadWriteAgent.AddSheet(SheetName: string): Integer;
begin
  FActiveSheetIndex := XLSReadWrite.Sheets.Add.Index;
  Result := ActiveSheetIndex;
end;

procedure TXLSReadWriteAgent.DeleteSheet(SheetIndex: Integer);
begin
  inherited;
  if SheetIndex = -1 then
    SheetIndex := ActiveSheetIndex;
  XLSReadWrite.Sheets.Delete(SheetIndex);
end;

function TXLSReadWriteAgent.GetActiveSheetName: string;
begin
  Result := XLSReadWrite.Sheets[ActiveSheetIndex].Name
end;

function TXLSReadWriteAgent.GetCellBooleanValue(Col, Row: integer;
  Default: Boolean): Boolean;
begin
  result := XLSReadWrite.Sheets[ActiveSheetIndex].AsBoolean[Col, Row];
end;

function TXLSReadWriteAgent.GetCellDateTimeValue(Col,
  Row: integer): TDateTime;
begin
  result := XLSReadWrite.Sheets[ActiveSheetIndex].AsDateTime[Col, Row];
end;

function TXLSReadWriteAgent.GetCellFloatValue(Col, Row: integer;
  Default: Double): Double;
begin
  result := XLSReadWrite.Sheets[ActiveSheetIndex].AsFloat[Col, Row];
end;

function TXLSReadWriteAgent.GetCellIntegerValue(Col, Row,
  Default: Integer): Integer;
begin
  result := XLSReadWrite.Sheets[ActiveSheetIndex].AsInteger[Col, Row];
end;

function TXLSReadWriteAgent.GetCellStringValue(Col, Row: integer;
  Default: string): string;
begin
  result := XLSReadWrite.Sheets[ActiveSheetIndex].AsString[Col, Row];
end;

function TXLSReadWriteAgent.GetCellValue(Col, Row: integer): Variant;
begin
  result := Unassigned;
  result := XLSReadWrite.Sheets[ActiveSheetIndex].AsVariant[Col, Row];
end;

function TXLSReadWriteAgent.GetMergedCell(Col, Row: integer; var Col1,
  Row1, Col2, Row2: integer): boolean;
var
  intIndex: Integer;
begin
  intIndex := GetMergedCellIndex(Col, Row);
  Result := intIndex <> -1;
  if Result then
  begin
    Col1 := XLSReadWrite.Sheets[ActiveSheetIndex].MergedCells.Items[intIndex].Col1;
    Row1 := XLSReadWrite.Sheets[ActiveSheetIndex].MergedCells.Items[intIndex].Row1;
    Col2 := XLSReadWrite.Sheets[ActiveSheetIndex].MergedCells.Items[intIndex].Col2;
    Row2 := XLSReadWrite.Sheets[ActiveSheetIndex].MergedCells.Items[intIndex].Row1;
  end;
end;

function TXLSReadWriteAgent.GetMergedCellIndex(Col, Row: integer): integer;
var
  i: Integer;
begin
  result := -1;
  for i := 0 to XLSReadWrite.Sheets[ActiveSheetIndex].MergedCells.Count - 1 do
    if XLSReadWrite.Sheets[ActiveSheetIndex].MergedCells.Items[i].CellInArea(Col, Row) then
    begin
      result := i;
      break;
    end;
end;

function TXLSReadWriteAgent.GetSheetCount: Integer;
begin
  Result := XLSReadWrite.Sheets.Count;
end;

function TXLSReadWriteAgent.GetXLSReadWrite: TXLSReadWriteII2;
begin
  if not Assigned(FXLSReadWrite) then
  begin
    FXLSReadWrite := TXLSReadWriteII2.Create(self);
  end;
  Result := FXLSReadWrite;
end;

function TXLSReadWriteAgent.InsertSheet(SheetName: string;
  SheetIndex: Integer): Integer;
begin
  if SheetIndex = -1 then
    SheetIndex := ActiveSheetIndex;
  FActiveSheetIndex := XLSReadWrite.Sheets.Insert(SheetIndex).Index;
  Result := ActiveSheetIndex;
end;

procedure TXLSReadWriteAgent.MergedCells(Col1, Row1, Col2, Row2: integer);
begin
  inherited;
  XLSReadWrite.Sheets[ActiveSheetIndex].MergedCells.Add(Col1, Row1, Col2, Row2);
end;

procedure TXLSReadWriteAgent.ReadFile;
begin
  inherited;
  Readed := false;
  FActiveSheetIndex := -1;
  if FileExists(FileName) then
  begin
    XLSReadWrite.Filename := FileName;
    XLSReadWrite.Read;
    if SheetCount > 0 then
      FActiveSheetIndex := 0;
  end
  else
    FActiveSheetIndex := 0;
end;

procedure TXLSReadWriteAgent.SetCellBooleanValue(Col, Row: integer;
  Value: Boolean);
begin
  inherited;
  XLSReadWrite.Sheets[ActiveSheetIndex].AsBoolean[Col, Row] := Value;
end;

procedure TXLSReadWriteAgent.SetCellDateTimeValue(Col, Row: integer;
  Value: TDateTime);
begin
  inherited;
  XLSReadWrite.Sheets[ActiveSheetIndex].AsDateTime[Col, Row] := Value;
end;

procedure TXLSReadWriteAgent.SetCellFloatValue(Col, Row: integer;
  Value: Double);
begin
  inherited;
  XLSReadWrite.Sheets[ActiveSheetIndex].AsFloat[Col, Row] := Value;
end;

procedure TXLSReadWriteAgent.SetCellIntegerValue(Col, Row, Value: integer);
begin
  inherited;
  XLSReadWrite.Sheets[ActiveSheetIndex].AsInteger[Col, Row] := Value;
end;

procedure TXLSReadWriteAgent.SetCellStringValue(Col, Row: integer;
  Value: string);
begin
  inherited;
  XLSReadWrite.Sheets[ActiveSheetIndex].AsString[Col, Row] := Value;
end;

procedure TXLSReadWriteAgent.SetCellValue(Col, Row: integer;
  Value: Variant);
begin
  inherited;
  XLSReadWrite.Sheets[ActiveSheetIndex].AsVariant[Col, Row] := Value;
end;

procedure TXLSReadWriteAgent.DeleteMergedCell(Col1, Row1, Col2, Row2: integer);
begin
  inherited;
  XLSReadWrite.Sheets[ActiveSheetIndex].MergedCells.Delete(Col1, Row1, Col2, Row2);
end;

procedure TXLSReadWriteAgent.WriteFile;
begin
  inherited;
  XLSReadWrite.Filename := FileName;
  XLSReadWrite.Write;
end;

procedure TXLSReadWriteAgent.WriteToStream(Stream: TStream);
begin
  inherited;
  XLSReadWrite.WriteToStream(Stream);
end;

procedure TXLSReadWriteAgent.DeleteMergedCellByCell(Col, Row: integer);
var
  intIndex: integer;
begin
  inherited;
  intIndex := GetMergedCellIndex(Col, Row);
  if intIndex <> -1 then
    XLSReadWrite.Sheets[ActiveSheetIndex].MergedCells.Delete(intIndex);
end;

procedure TXLSReadWriteAgent.DeleteMergedCellByIndex(Index: integer);
begin

end;

end.