Core/DBDExcelVclClasses.pas - master - YakuninAV/Convertors

Core/DBDExcelVclClasses.pas
893 строки29 KB
YakuninAV
begin with gitflick
31 июл 2026, 15:21
31 июл 2026, 15:211dc0fc6
unit DBDExcelVclClasses;

{$I mormot.defines.inc}

interface

uses
{$IFDEF ISDELPHIXE2}
  System.SysUtils, System.Classes, System.Variants,
  WinApi.ActiveX, Winapi.Windows, WinApi.Messages,
  Vcl.StdCtrls,
{$ELSE}
  SysUtils, Classes, StdCtrls, Controls, ComObj, ActiveX, Variants, Windows, Messages,
{$ENDIF}
  OleServer, Excel2000,
  mormot.core.base, mormot.core.variants, mormot.core.json, mormot.core.text, mormot.core.data,
  mormot.core.os, mormot.core.unicode,
      DBDIntfs, DBDExcelInfs, DBDExcelBaseClassess;

type

  TDBDExcelReaderVcl = class(TDBDExcelReaderBase)
  private
    WS: TExcelWorksheet;
  public
    function AsBoolean(const aRow, aCol: Word; out B: Boolean): Boolean; overload; override;
    function AsCurrency(const aRow, aCol: Word; out C: Currency): Boolean; overload; override;
    function AsDateTime(const aRow, aCol: Word; out D: TDateTime): Boolean; overload; override;
    function AsDouble(const aRow, aCol: Word; out D: Double): Boolean; overload; override;
    function AsExtended(const aRow, aCol: Word; out D: Extended): Boolean; overload; override;
    function AsInteger(const aRow, aCol: Word; out I: Integer): Boolean; overload; override;
    function AsInt64(const aRow, aCol: Word; out I: Int64): Boolean; overload; override;
    function AsString(const aRow, aCol: Word; out S: String): Boolean; overload; override;
    function AsVariant(const aRow, aCol: Word; out V: Variant): Boolean; overload; override;
    constructor Create(Owner: IDBDExcelV2; var Worksheet: TExcelWorksheet);
  end;

  TDBDExcelWriterVcl = class(TDBDExcelWriterBase)
  private
    WS: TExcelWorksheet;
  public
    constructor Create(Owner: IDBDExcelV2; var Worksheet: TExcelWorksheet);
    /// ������� ������ ������� ������
    function CellBorders(const aRow, aCol: Word; const Borders: Byte;
                         const LineStyle: Integer = xlContinuous; const LineWeight: Integer = xlThin): boolean; override;
    /// ������� ��������� ������ � ��������� �����
    //  ���� ���� DoCopy ����������, �� ���������� ������� ������ ���������� � �����
    function InsertRow(const aRow: Word; const DoCopy: Boolean=True): Boolean; override;
    /// ������� ���������� ������������ ����������� ������
    // ���� aRow=0, �� ������������ ������������ ��� ���� ����� �������
    // ���� aCol=0, �� ������������ ������������ ��� ���� ����� ������
    function RangeAlign(const aRow, aCol: Word; const VAligment, HAligment: Integer): Boolean; overload; override;
    /// ������� ���������� ������������ ����������� ����� ��������� �������
    function RangeAlign(const aRange: TRect; const VAligment: Integer = xlVAlignTop; const HAligment: Integer = xlHAlignGeneral): Boolean; overload; override;
    /// ������� ��������� ������ � ��������� �����
    //  ���� ���� DoCopy ����������, �� ���������� ������� ������ ���������� � �����
    function WriteBoolean(const aRow, aCol: Word; const B: Boolean): Boolean; override;
    function WriteCurrency(const aRow, aCol: Word; const C: Currency): Boolean; override;
    function WriteDateTime(const aRow, aCol: Word; const D: TDateTime): Boolean; override;
    function WriteDouble(const aRow, aCol: Word; const D: Double): Boolean; override;
    function WriteInteger(const aRow, aCol: Word; const I: Integer): Boolean; override;
    function WriteInt64(const aRow, aCol: Word; const I: Int64): Boolean; override;
    function WriteString(const aRow, aCol: Word; const S: String): Boolean; override;
    function WriteVariant(const aRow, aCol: Word; const V: Variant): Boolean; override;
  end;

  /// ��������� ��� ������ � MS Excel
  TDBDExcelVcl = class(TDBDExcelBase)
  private
    FOptions: TDBDOpenWBOprions;
    FStop: Boolean;
    XL: TExcelApplication;
    WB: TExcelWorkbook;
    WS: TExcelWorksheet;
    ReadyXL: Boolean;
    ReadyWB: Boolean;
    const FUserCodePage: Word = 65001;
    procedure OpenXL;
    procedure CloseXL;
    procedure GetUsedRange;
    function GetReader: IDBDExcelReaderV2; override;
    function GetWriter: IDBDExcelWriterV2; override;
  public
    /// �����������
    constructor Create(FileName: TFileName; const WBOptions: TDBDOpenWBOprions = []; const aNotifier: IDBDNotifierV1 = nil);
    /// ����������
    destructor Destroy; override;
    /// �������� ������ ���� � �����
    function AddWorkSheet(const aName: string; const Index: Word=0): Integer; override;
    /// ������� Excel-����
    procedure Close; override;
    /// ������� �������� �������
    //  Src, Dst - ������ �������������� ������ ��������� � ����������
    //  �������� ������ ����� ���: "����!������1:������2"
    function CopyRange(const Src, Dst: string): Boolean; overload; override;
    /// ������� �������� �������, �������� � ������� SrcRng, � ����� Src �� ���� Dst, ������� � ������ DstCell
    function CopyRange(const Src: string; const SrcRng: TRect; const Dst: string; const DstCell: TPoint): Boolean; overload; override;
    /// ������� �������� �������, �������� � ������� SrcRng, � ����� Src �� ���� Dst, ������� � ������ (Row,Col)
    function CopyRange(const Src,SrcRng,Dst: string; const Row, Col: Word): Boolean; overload; override;
    /// ������� ����� �����
    function CopyWorkSheet(const Src, Dst: string): Boolean; overload; override;
    /// ������� ����� �����
    function CopyWorkSheet(SrcIdx: Word; const Dst: string): Boolean; overload; override;
    /// �������� ������ ������ � �����
    function GetOpenedWorksheetNames: string; override;
    /// �������� ������ �������� �������� ����
    function GetOpenedWorkbookNames: string;
    /// ������� ������ ����� � �������� ������
    function GetWorkSheetIdx(const aWorkSheetName: string): Integer; override;
    /// �������� �������� �����
    function GetWorkSheetName: string;
    /// ������� ��������� ������ � ��������� �����
    //  ���� ���� DoCopy ����������, �� ���������� ������� ������ ���������� � �����
    function InsertRow(const aRow: Word; const DoCopy: Boolean=True): Boolean; override;
    /// �������� ����� ��������� ������� �������� �����
    function LastCol: Integer;
    /// �������� ����� ��������� ������ �������� �����
    function LastRow: Integer;
    /// ������� � ���������� �����, ���� ������� ���� ��������� ���������� false
    function NextWorkSheet: Boolean; override;
    /// ������� Excel ����
    function OpenWorkBook(const aFileName: TFilename; const WBOptions: TDBDOpenWBOprions = []): Integer; override;
    /// ������� (������� �������) ���� �� �������. ������� ���������� ��� ����� ��� �����
    function OpenWorkSheet(const aIndex: Integer): string; overload; override;
    /// ����� � ������� (������� �������) ���� �� ��������. ������� ����������� ������ ����� ��� <=0
    function OpenWorkSheet(const aWorkSheetName: string): Integer; overload; override;
    /// ������� ���������� ������������ ����������� ������
    // ���� aRow=0, �� ������������ ������������ ��� ���� ����� �������
    // ���� aCol=0, �� ������������ ������������ ��� ���� ����� ������
    function RangeAlign(const aRow, aCol: Word;
                        const VAligment: Integer = xlVAlignTop; const HAligment: Integer = xlHAlignGeneral): Boolean; overload; override;
    /// ������� ���������� ������������ ����������� ����� ��������� �������
    function RangeAlign(const aRange: TRect;
                        const VAligment: Integer = xlVAlignTop; const HAligment: Integer = xlHAlignGeneral): Boolean; overload; override;
    /// ������� ������ ������� �������
    //  Borders - �����, ������������� ���� �������� �������� ������� ������
    function RangeBorders(const aRange: string;const Borders: Byte;
                          const LineStyle: Integer = xlContinuous; const LineWeight: Integer = xlThin): Boolean; override;
  end;

  /// ��������� ����� � ������� (R1,C1) � ������ (A1)  (256 �������)
//  function xlRCtoA1(const ARow, ACol: Integer; RowAbsolute: Boolean = False; ColAbsolute: Boolean = False): String;

implementation

resourcestring
  rsEInvalidSheetIndex     = '����� �������� ������ ��� WorkBooks. ��������� ����� ��������';
  rsEInvalidSheetActivate  = '��������� ����� ��������� � �������';
  rsEInvalidBookNotOpen    = '����� ��� �� �������';
  rsEInvaidFileName        = '��� ����� �� ����� ���� ������';
  rsEInvaidExcelConnection = '������ ����������� � MS Excel';
  rsEExcelNotFound         = '���������� MS Excel �� ����������� �� ���� ����������';
  rsInvalidWorkSheetName   = '�������� �������� ����� �� ����� ���� ������';

const
  ExcelApp = 'Excel.Application';
  lcid = LOCALE_USER_DEFAULT;
var
  oFalse: OleVariant;
  oTrue:  OleVariant;
{
function xlRCtoA1(const ARow, ACol: Integer; RowAbsolute: Boolean = False; ColAbsolute: Boolean = False): String;
const
  A1 = Ord('A') - 1;  // ����� "A" ����� 1 (65 - 1 = 64)
  AZ = Ord('Z') - A1; // ���-�� ���� � ����. �������� (90 - 64 = 26)
var
  t, m: Integer;
  S: String[9]; // ���� ��������� ������ IV=256 ��������� �������
begin
  // ����� �������
  t := ACol div AZ;   // ����� �����
  m := (ACol mod AZ); // �������?
  if m = 0 then Dec(t);
  if t > 0 then S := Char(A1 + t) else S := '';
  if m = 0 then t := AZ else t := m;
  S := S + Char(A1 + t);
  // ���� �����
  if ColAbsolute then S := '$' + S;
  if RowAbsolute then S := S + '$';
  S := S + IntToStr(ARow);
  Result := S;
  end;
}
{ TDBDExcelVcl }

function TDBDExcelVcl.AddWorkSheet(const aName: string; const Index: Word=0): Integer;
var w: TExcelWorksheet; i: Integer;
begin
  if Index=0 then i := FCountWS
             else i := Index;
  w := TExcelWorksheet.Create(nil);
  try
    XL.Worksheets.Add(EmptyParam, XL.Worksheets[i], 1, xlWorksheet, lcid);
    FCountWS:=XL.Worksheets.Count;
    Result := i+1;
    w.ConnectTo(XL.Worksheets[Result] as _Worksheet);
    w.Name := aName;
  finally
    w.Disconnect;
    FreeAndNil(w);
  end;
end;

procedure TDBDExcelVcl.Close;
var o, SaveChanges: OleVariant;
begin
  //  owbReadOnly - ������� ������ �� ������
  //  owbVisibleAlwais - �� ������� Excel
  //  owbCloseOnExit - ������� WorkBook ���� �������
  // ������� ����� ��� ���������� ��������� � ��� ���������
  if owbReadOnly in FOptions then SaveChanges := False
                             else SaveChanges := True;
  XL.CutCopyMode[lcid] := oFalse; // �������� ����� ������
  WB.Close(SaveChanges);
//  if owbReadOnly in FOptions then WB.Close(0) // xlDontSaveChanges
//                             else WB.Close(1);
  // ������� ��� ����� ��� ���������� ���������
  //  XL.DisplayAlerts[lcid] := False; // ��������� ��������������
  //  XL.Workbooks.Close(lcid); // ������� ��� �����
  ReadyWB:=False;
  FCountWB:= XL.Workbooks.Count;
  FCountWS:=0; FNameWS:='';
end;

procedure TDBDExcelVcl.CloseXL;
begin
  WS.Disconnect;
  if ReadyWB then Close;
  WB.Disconnect;
  if FCountWB>0 then XL.Calculation[lcid] := xlAutomatic;   // ���������� �������������� ����������� ������
  XL.UserControl := True;                                   // ������� ���������� ������������
  if FCountWB>0 then begin
    XL.Visible[lcid]:=True;                                 // �������� Excel
    XL.Quit;                                                // ������� Excel
  end;
  XL.Disconnect;
end;

function TDBDExcelVcl.CopyRange(const Src, Dst: string): Boolean;
var wSrc,wDst: TExcelWorksheet; nSrc, nDst: string; i: Integer; rSrc, rDst: ExcelRange; o1,o2: OleVariant;
begin
  try
//    wSrc := WB.Range[Src)
    nSrc := '['+WB.Name+']'+'ShabLS!A1:K16'; o1:=nSrc;
    nDst := '['+WB.Name+']'+'��1!A1'; o2:=nDst;
    rSrc :=XL.Range[o1,EmptyParam];
    rSrc.Copy(XL.Range[o2,EmptyParam]);
    XL.CutCopyMode[lcid] := oFalse;
    Result := True;
  except
    on E: Exception do begin
      Result := False;
      FNotifier.Log(dmkErrorInfo, e.HelpContext, e.Message);
    end
  end;
end;

function TDBDExcelVcl.CopyRange(const Src, SrcRng, Dst: string; const Row, Col: Word): Boolean;
var w: TExcelWorksheet; rngS, rngD: ExcelRange; Cell: OleVariant;  pst, op: OleVariant;
begin
  Assert((Src<> '') and (SrcRng<>'') and (Dst<>'') and (Row>0) and (Col>0), '������� ������ ���������');
  Cell:= xlRCtoA1(Row, Col);
  w := TExcelWorksheet.Create(nil);
  try
    try
      w.Disconnect;
      w.ConnectTo(WB.Worksheets[Src] as _Worksheet);
      rngS := w.Range[SrcRng, EmptyParam];
      rngD:=(WB.Worksheets[Dst] as _Worksheet).Range[Cell, EmptyParam];
      rngS.Copy(EmptyParam);
      pst := xlPasteAll; op := xlPasteSpecialOperationNone;
      Result := rngD.PasteSpecial(pst, op, False, False);
    except
      on E: Exception do begin
        Log(dmkErrorInfo,E.HelpContext ,'���������� ��� ����������� �����. ' + E.Message);
        Result := False;
      end;
    end;
  finally
    w.Disconnect;
    FreeAndNil(w);
  end;
end;

function TDBDExcelVcl.CopyRange(const Src: string; const SrcRng: TRect; const Dst: string; const DstCell: TPoint): Boolean;
var w: TExcelWorksheet; o: OleVariant;
begin

end;

function TDBDExcelVcl.CopyWorkSheet(SrcIdx: Word; const Dst: string): Boolean;
var w: TExcelWorksheet; o: OleVariant;
begin
  w := TExcelWorksheet.Create(nil);
  try
    try
      w.Disconnect;
      w.ConnectTo(WB.Worksheets[SrcIdx] as _Worksheet);
//      w.Copy(EmptyParam,EmptyParam,lcid);
//      o := Dst;
      w.Copy(EmptyParam, WB.Worksheets[WB.Worksheets.Count]);

      Result := True;
      Result := OpenWorkSheet(WB.Worksheets.Count) <> '';
      if Result then WS.Name := Dst;
    except
      on E: Exception do begin
        Log(dmkErrorInfo,E.HelpContext ,'���������� ��� ����������� �����. ' + E.Message);
        Result := False;
      end;
    end;
  finally
    w.Disconnect;
    FreeAndNil(w);
  end;
end;

function TDBDExcelVcl.CopyWorkSheet(const Src, Dst: string): Boolean;
var i: integer;
begin
  i := GetWorkSheetIdx(Src);
  Result := i>0;
  if Result then CopyWorkSheet(i, Dst);
end;

constructor TDBDExcelVcl.Create(FileName: TFileName; const WBOptions: TDBDOpenWBOprions = []; const aNotifier: IDBDNotifierV1=nil);
begin
  inherited Create(WBOptions, aNotifier);
  FCurrIdx := 0; FCountWS := 0; FLastRow:=0; FLastCol:=0;
  XL := TExcelApplication.Create(nil);
  WB := TExcelWorkbook.Create(nil);
  WS := TExcelWorksheet.Create(nil);
  ReadyXL := False; ReadyWB:=False;
  FCountWB := XL.Workbooks.Count;
  if FileName<>'' then OpenWorkBook(FileName);
end;

destructor TDBDExcelVcl.Destroy;
begin
  CloseXL;
  FreeAndNil(WS);
  FreeAndNil(WB);
  FreeAndNil(XL);
  inherited;
end;

function TDBDExcelVcl.GetOpenedWorkbookNames: string;
var i: Integer;
begin
  Result := ''; if not ReadyXL then Exit;
  if XL.Workbooks.Count=0 then Exit;
  Result := XL.Workbooks[1].FullName[lcid];
  if XL.Workbooks.Count=1 then Exit;
  for i := 2 to XL.Workbooks.Count do Result := Result + #13#10 + XL.Workbooks[i].FullName[lcid];
end;

function TDBDExcelVcl.GetOpenedWorksheetNames: string;
var i: Integer;
begin
  Result := ''; if not ReadyWB then Exit;
  if WB.Worksheets.Count=0 then Exit;
  Result:= (WB.Worksheets.Item[1] as _Worksheet).Name;
  if WB.Worksheets.Count=1 then Exit;
  for i := 2 to WB.Worksheets.Count do Result := Result + #13#10 + (WB.Worksheets.Item[i] as _Worksheet).Name;
end;

function TDBDExcelVcl.GetReader: IDBDExcelReaderV2;
begin
  if (not Assigned(rdr)) or (rdr.WSName<>WS.Name) then begin
    rdr := nil;
    rdr := TDBDExcelReaderVCL.Create(Self, WS);
  end;
  Result := inherited GetReader;
end;

procedure TDBDExcelVcl.GetUsedRange;
var R: ExcelRange;
begin
  if not ReadyWB then begin
    FLastCol := 0; FLastRow := 0;
  end else begin
    R := WS.UsedRange[lcid];
    R := WS.Range['A1', EmptyParam].SpecialCells(xlCellTypeLastCell, EmptyParam);
    FLastCol := R.Column;
    FLastRow := R.Row;
  end;
end;

function TDBDExcelVcl.GetWorkSheetIdx(const aWorkSheetName: string): Integer;
var i: Integer;
begin
  if not ReadyWB then begin
    Result := 0;
    if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, 0, rsEInvalidBookNotOpen);
  end else begin
    for i := 1 to WB.Worksheets.Count do begin
      if (WB.Worksheets[i] as _Worksheet).Name = aWorkSheetName then begin
        Result := i; Exit;
      end;
    end;
    Result := 0;
  end;
end;

function TDBDExcelVcl.GetWorkSheetName: string;
begin
  Result := FNameWS; Exit;
end;

function TDBDExcelVcl.GetWriter: IDBDExcelWriterV2;
begin
  if (not Assigned(wrt)) or (wrt.WSName<>WS.Name) then begin
    wrt := nil;
    wrt := TDBDExcelWriterVCL.Create(Self, WS);
  end;
  Result := inherited GetWriter;
end;

function TDBDExcelVcl.InsertRow(const aRow: Word; const DoCopy: Boolean): Boolean;
begin

end;

function TDBDExcelVcl.LastCol: Integer;
var R: ExcelRange;
begin
  if not ReadyWB then begin
    Result := 0;
    if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, 0, rsEInvalidBookNotOpen);
  end else begin
    R := WS.UsedRange[lcid];
    R := WS.Range['A1', EmptyParam].SpecialCells(xlCellTypeLastCell, EmptyParam);
    Result := R.Column;
  end;
end;

function TDBDExcelVcl.LastRow: Integer;
var R: ExcelRange;
begin
  if not ReadyWB then begin
    Result := 0;
    if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, 0, rsEInvalidBookNotOpen);
  end else begin
    R := WS.UsedRange[lcid];
    R := WS.Range['A1', EmptyParam].SpecialCells(xlCellTypeLastCell, EmptyParam);
    Result := R.Row;
  end;
end;

function TDBDExcelVcl.NextWorkSheet: Boolean;
begin
  Result := FCurrIdx < FCountWS;
  if Result then OpenWorkSheet(FCurrIdx+1);
end;

function TDBDExcelVcl.OpenWorkBook(const aFileName: TFilename; const WBOptions: TDBDOpenWBOprions = []): Integer;
var ReadOnly, Editable: OleVariant;
begin
  //  owbReadOnly - ������� ������ �� ������
  //  owbVisibleAlwais - �� ������� Excel
  //  owbCloseOnExit - ������� WorkBook ���� �������
  if ReadyWB then Close;
  if aFileName='' then begin
    Result:=-1;
    if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, -1, rsEInvaidFileName);
  end else if not FileExists(aFileName) then begin
    result:=-2;
    if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, -2, '���� "' + aFileName + '" �� ������');
  end else begin
    FOptions := WBOptions;
    Editable := EmptyParam;
    if owbReadOnly in FOptions then ReadOnly := True
                               else ReadOnly := EmptyParam;
    try
//  Open (FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin,
//        Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
      WB.ConnectTo(XL.Workbooks.Open(aFileName,
      2,          // UpdateLinks: OleVariant; 2 - never update
      ReadOnly,       // ReadOnly: OleVariant;
      EmptyParam, // Format: OleVariant;
      EmptyParam, // Password: OleVariant;
      EmptyParam, // WriteResPassword: OleVariant;
      EmptyParam, // IgnoreReadOnlyRecommended: OleVariant;
      EmptyParam, // Origin: OleVariant;
      EmptyParam, // Delimiter: OleVariant;
      Editable,   // Editable: OleVariant;
      EmptyParam, // Notify: OleVariant;
      EmptyParam, // Converter: OleVariant;
      False,      // AddToMru: OleVariant;
//      EmptyParam, // Local: OleVariant;
//      EmptyParam, // CorruptLoad: OleVariant;
      lcid));
      Result:=0;
      FCountWS := WB.Worksheets.Count;
      ReadyWB:=FCountWS>0;
      if ReadyWB then begin
        Inc(FCountWB);
        Result := FCountWS;
        FCurrIdx:=0;
        OpenWorkSheet(1);
      end;
      FCountWB := XL.Workbooks.Count;
    except
      Result:=-3;
      if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, -3, '���������� ��� �������� ����� "' + aFileName + '"');
    end;
  end;
end;

function TDBDExcelVcl.OpenWorkSheet(const aIndex: Integer): string;
begin
  if not ReadyWB then begin
    Result := '';
    if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, -1, rsEInvalidBookNotOpen);
  end else if aIndex<=0 then begin
    if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, -1, '������ �� ����� ���� ������ 1)');
    result := '';
  end else if WB.Worksheets.Count<aIndex then begin
    if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, -1, '�������� �������� �������');
    result := '';
  end else if aIndex <> FCurrIdx then begin
    WS.Disconnect;
    WS.ConnectTo(WB.Worksheets[aIndex] as _Worksheet);
    Result := WS.Name;
    GetUsedRange;
    FCurrIdx := aIndex;
    FNameWS := WS.Name;
  end else Result := WS.Name;
end;

function TDBDExcelVcl.OpenWorkSheet(const aWorkSheetName: string): Integer;
var i: Integer;
begin
  if not ReadyWB then begin
    Result := -1;
    if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, -1, rsEInvalidBookNotOpen);
  end else if aWorkSheetName='' then begin
    Result := -2;
    if Assigned(FNotifier) then FNotifier.Log(dmkErrorInfo, -2, rsInvalidWorkSheetName);
  end else begin
    for i := 1 to WB.Worksheets.Count do begin
      if (WB.Worksheets[i] as _Worksheet).Name = aWorkSheetName then begin
        WS.Disconnect;
        WS.ConnectTo(WB.Worksheets[i] as _Worksheet);
        Result := i; Exit;
      end;
    end;
    Result := 0;
  end;
end;

procedure TDBDExcelVcl.OpenXL;
begin
  if not ReadyXL then
    try
      XL.ConnectKind := ckNewInstance;
      XL.Connect;           // �����������
      XL.AutoQuit := False; // �� ��������� ��� �������� True ������ � unit ExcelXP
      XL.Visible[lcid] := True;
      ReadyXL:= True;
      XL.ScreenUpdating[lcid] := False; // ��������� ����������� ������
      XL.Calculation[lcid] := xlManual; // �������� �������������� ����������� ������
      // �������� �������� �������������� ������ � ������� (��� XP � ����)
//      with XL.ErrorCheckingOptions do begin
//        BackgroundChecking := False;
//        NumberAsText := False;
//        InconsistentFormula := False;
//      end;
    except
      if Assigned(FNotifier) then FNotifier.Log(dmkCrashInfo, 0, rsEInvaidExcelConnection);
      ReadyXL:= True;
    end;
end;

function TDBDExcelVcl.RangeAlign(const aRange: TRect; const VAligment, HAligment: Integer): Boolean;
var Cell: OleVariant;
begin

end;

function TDBDExcelVcl.RangeBorders(const aRange: string; const Borders: Byte; const LineStyle, LineWeight: Integer): Boolean;
begin

end;

function TDBDExcelVcl.RangeAlign(const aRow, aCol: Word; const VAligment, HAligment: Integer): Boolean;
var Cell,r: OleVariant; rng, sel: ExcelRange; s: string;
begin
  Assert((aRow<>0) or (aCol<>0), '�� ������ �� ������, �� �������');
  Result := True;
  try
    if aRow=0 then begin
//      rng := WS.Columns(aCol);
    end else if aCol=0 then begin
      r := 'A' + IntToStr(aRow) + ':K' + IntToStr(aRow);
      rng := WS.Range[r,EmptyParam];
//      rng := WS.Rows.Item[aRow, EmptyParam];
//      rng := (r as ExcelRange);
    end else begin
      Cell := XLRCtoA1(aRow,aRow);
      rng := WS.Range[Cell, EmptyParam];
    end;
    s := rng.Address[oTrue, oTrue,xlA1,oFalse, 0] + ' ';
    rng.HorizontalAlignment := HAligment;
    rng.VerticalAlignment := VAligment;
  except
    on E: Exception do begin
      Result := False;
      FNotifier.Log(dmkErrorInfo, E.HelpContext, s + E.Message);
    end;
  end;
end;

{ TDBDExcelReaderOle }

constructor TDBDExcelReaderVcl.Create(Owner: IDBDExcelV2; var Worksheet: TExcelWorksheet);
begin
  inherited Create(Owner);
  WS:=Worksheet;
end;

function TDBDExcelReaderVcl.AsBoolean(const aRow, aCol: Word; out B: Boolean): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    B := WS.Range[Cell, EmptyParam].Value2;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelReaderVcl.AsCurrency(const aRow, aCol: Word; out C: Currency): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    C := WS.Range[Cell, EmptyParam].Value2;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelReaderVcl.AsDateTime(const aRow, aCol: Word; out D: TDateTime): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    D := WS.Range[Cell, EmptyParam].Value2;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelReaderVcl.AsDouble(const aRow, aCol: Word; out D: Double): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    D := WS.Range[Cell, EmptyParam].Value2;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelReaderVcl.AsExtended(const aRow, aCol: Word; out D: Extended): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    D := WS.Range[Cell, EmptyParam].Value2;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelReaderVcl.AsInt64(const aRow, aCol: Word; out I: Int64): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    I := WS.Range[Cell, EmptyParam].Value2;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelReaderVcl.AsInteger(const aRow, aCol: Word; out I: Integer): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    I := WS.Range[Cell, EmptyParam].Value2;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelReaderVcl.AsString(const aRow, aCol: Word; out S: String): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    S := WS.Range[Cell, EmptyParam].Value2;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelReaderVcl.AsVariant(const aRow, aCol: Word; out V: Variant): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    V := WS.Range[Cell, EmptyParam].Value2;
  except on E: Exception do
    Result:=False;
  end;
end;

{ TDBDExcelWriterVcl }

function TDBDExcelWriterVcl.CellBorders(const aRow, aCol: Word; const Borders: Byte; const LineStyle, LineWeight: Integer): boolean;
var Cell: OleVariant;
begin
  Assert((aRow<>0) and (aCol<>0), '�� ������ �� ������, �� �������');
  Cell := XLRCtoA1(aRow,aRow);
//  WS.Range[Cell, EmptyParam].HorizontalAlignment := HAligment;
//  WS.Range[Cell, EmptyParam].VerticalAlignment := VAligment;
  if (Borders and dbdXlEdgeTop) = dbdXlEdgeTop then begin

  end else if (Borders and dbdXlEdgeBottom) = dbdXlEdgeBottom then begin
  end else if (Borders and dbdXlEdgeRight) = dbdXlEdgeRight then begin
  end else if (Borders and dbdXlEdgeTop) = dbdXlEdgeTop then begin
  end else if (Borders and dbdXlEdgeTop) = dbdXlEdgeTop then begin
 end;
end;

constructor TDBDExcelWriterVcl.Create(Owner: IDBDExcelV2; var Worksheet: TExcelWorksheet);
begin
  inherited Create(Owner);
  WS:=Worksheet;
  FWSName := WS.Name;
end;


function TDBDExcelWriterVcl.InsertRow(const aRow: Word; const DoCopy: Boolean): Boolean;
var Row: ExcelRange; rn: OleVariant;
begin
  try
    rn:=aRow;
    WS.Rows.Item[rn, EmptyParam].Copy;
    (WS.Rows.Item[rn, EmptyParam]).Insert(xlShiftDown);
    rn := WS.Rows.Item[rn, EmptyParam].PasteSpecial(EmptyParam, EmptyParam, EmptyParam, EmptyParam);
    Result := True;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelWriterVcl.RangeAlign(const aRange: TRect; const VAligment, HAligment: Integer): Boolean;
begin

end;

function TDBDExcelWriterVcl.RangeAlign(const aRow, aCol: Word; const VAligment, HAligment: Integer): Boolean;
var Cell: OleVariant;
begin
  Assert((aRow<>0) or (aCol<>0), '�� ������ �� ������, �� �������');
  if aRow=0 then begin
    WS.Columns.Item[aCol, EmptyParam].HorizontalAlignment := HAligment;
    WS.Columns.Item[aCol, EmptyParam].VerticalAlignment := VAligment;
  end else if aCol=0 then begin
    WS.Rows.Item[aCol, EmptyParam].HorizontalAlignment := HAligment;
    WS.Rows.Item[aCol, EmptyParam].VerticalAlignment := VAligment;
  end else begin
    Cell := XLRCtoA1(aRow,aRow);
    WS.Range[Cell, EmptyParam].HorizontalAlignment := HAligment;
    WS.Range[Cell, EmptyParam].VerticalAlignment := VAligment;
  end;
end;

function TDBDExcelWriterVcl.WriteBoolean(const aRow, aCol: Word; const B: Boolean): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    WS.Range[Cell, EmptyParam].Value2 := B;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelWriterVcl.WriteCurrency(const aRow, aCol: Word; const C: Currency): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    WS.Range[Cell, EmptyParam].Value2 := C;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelWriterVcl.WriteDateTime(const aRow, aCol: Word; const D: TDateTime): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    WS.Range[Cell, EmptyParam].Value2 := D;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelWriterVcl.WriteDouble(const aRow, aCol: Word; const D: Double): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    WS.Range[Cell, EmptyParam].Value2 := D;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelWriterVcl.WriteInt64(const aRow, aCol: Word; const I: Int64): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    WS.Range[Cell, EmptyParam].Value2 := I;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelWriterVcl.WriteInteger(const aRow, aCol: Word; const I: Integer): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    WS.Range[Cell, EmptyParam].Value2 := I;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelWriterVcl.WriteString(const aRow, aCol: Word; const S: String): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    WS.Range[Cell, EmptyParam].Value2 := S;
  except on E: Exception do
    Result:=False;
  end;
end;

function TDBDExcelWriterVcl.WriteVariant(const aRow, aCol: Word; const V: Variant): Boolean;
var Cell: OleVariant;
begin
  try
    Result:= True;
    Cell:= xlRCtoA1(aRow,aCol);
    WS.Range[Cell, EmptyParam].Value2 := V;
  except on E: Exception do
    Result:=False;
  end;
end;

initialization

  oFalse:= 'false';
  oTrue := 'true';

end.