Chip 2003 January/Chip_2003-01_cd1.bin/zkuste/delphi/kolekce/d567/FLEXCEL.ZIP/FlexCel/UFlexCelImport.pas

Pascal/Delphi Source File | 2002-10-10 | 17.0 KB | 531 lines

unit UFlexCelImport;

interface

uses
  SysUtils, Classes, UExcelAdapter,
  {$IFDEF ConditionalExpressions}{$if CompilerVersion >= 14} variants,{$IFEND}{$ENDIF} //Delphi 6 or above
  UFlxMessages, UFlxFormats;

type
  TFlexCelImport = class(TComponent)
  private
    function GetIsFormula(Row, Column: integer): boolean;
    function GetAutoRowHeight(Row: integer): boolean;
    procedure SetAutoRowHeight(Row: integer; const Value: boolean);
    function GetCellFormat(Row, Col: integer): integer;
    procedure SetCellFormat(Row, Col: integer; const Value: integer);
    function GetCell(Row, Col: integer): TXlsCellValue;
    procedure SetCell(Row, Col: integer; const Value: TXlsCellValue);
    function GetColorPalette(index: TColorPaletteRange): LongWord;
    procedure SetColorPalette(index: TColorPaletteRange; const Value: LongWord);
    function GetColorPaletteCount: integer;
    function GetColumnFormat(aColumn: integer): integer;
    function GetRowFormat(aRow: integer): integer;
    procedure SetColumnFormat(aColumn: integer; const Value: integer);
    procedure SetRowFormat(aRow: integer; const Value: integer);
    function GetFormatListCount: integer;
    function GetFlxFormatList(index: integer): TFlxFormat;
    function GetCellFormatDef(aRow, aCol: integer): TFlxFormat;
    function GetColByIndex(Row, ColIndex: integer): integer;
    function GetColIndexCount(Row: integer): integer;
    function GetColIndex(Row, Col: integer): integer;
    function GetDefaultColWidth: integer;
    function GetDefaultRowHeight: integer;
    function GetShowGridLines: boolean;
    procedure SetShowGridLines(const Value: boolean);
    function GetPictureName(aPos: integer): Widestring;
    function GetPicturesCount: integer;
    function GetCellMergedBounds(aRow, aCol: integer): TXlsCellRange;

  private
    Workbook: TExcelFile;
    FAdapter: TExcelAdapter;
    procedure SetAdapter(const Value: TExcelAdapter);
    function GetCellValue(Row, Col: integer): variant;
    procedure SetCellValue(Row, Col: integer; const Value: variant);
    function GetActiveSheet: integer;
    procedure SetActiveSheet(const Value: integer);
    function GetSheetCount: integer;
    function GetMaxRow: integer;
    function GetMaxCol: integer;
    function GetComments(Row, Col: integer): variant;
    procedure SetComments(Row, Col: integer; const Value: variant);
    function GetCommentsCount(Row: integer): integer;
    function GetActiveSheetName: widestring;
    procedure SetActiveSheetName(const Value: widestring);
    function GetColumnWidth(aCol: integer): integer;
    function GetRowHeight(aRow: integer): integer;
    procedure SetColumnWidth(aCol: integer; Value: integer);
    procedure SetRowHeight(aRow: integer; Value: integer);

    { Private declarations }
  protected
    procedure Notification(AComponent: TComponent; Operation: TOperation); override;
    { Protected declarations }
  public
    { Public declarations }
    destructor Destroy;override;

    procedure OpenFile(const FileName: TFileName);
    procedure Save(const FileName: TFileName);
    procedure CloseFile;

    property SheetCount: integer read GetSheetCount;
    property ActiveSheet:integer read GetActiveSheet write SetActiveSheet ;
    property ActiveSheetName: widestring read GetActiveSheetName write SetActiveSheetName;

    property MaxRow: integer read GetMaxRow;
    property MaxCol: integer read GetMaxCol;
    function IsEmptyRow(const aRow: integer): boolean;

    property CellValue[Row, Col: integer]: variant read GetCellValue write SetCellValue;
    property CellFormat[Row, Col: integer]: integer read GetCellFormat write SetCellFormat;
    property IsFormula[Row, Column: integer]: boolean read GetIsFormula;
    property Cell[Row, Col: integer]: TXlsCellValue read GetCell write SetCell;

    property ColByIndex[Row, ColIndex: integer]: integer read GetColByIndex;
    property ColIndex[Row, Col: integer]: integer read GetColIndex;
    property ColIndexCount[Row: integer]: integer read GetColIndexCount;

    property ColorPalette[index: TColorPaletteRange]: LongWord read GetColorPalette write SetColorPalette;
    property ColorPaletteCount: integer read GetColorPaletteCount;

    property FormatList[index: integer]: TFlxFormat read GetFlxFormatList;
    property CellFormatDef[aRow, aCol: integer]: TFlxFormat read GetCellFormatDef;
    property FormatListCount: integer read GetFormatListCount;

    property ColumnWidth[aCol: integer]: integer read GetColumnWidth write SetColumnWidth;
    property RowHeight[aRow: integer]: integer read GetRowHeight write SetRowHeight;
    property AutoRowHeight[Row: integer]: boolean read GetAutoRowHeight write SetAutoRowHeight;

    property DefaultRowHeight: integer read GetDefaultRowHeight;
    property DefaultColWidth: integer read GetDefaultColWidth;

    property ColumnFormat[aColumn: integer]: integer read GetColumnFormat write SetColumnFormat;
    property RowFormat[aRow: integer]: integer read GetRowFormat write SetRowFormat;

    property CellMergedBounds[aRow, aCol: integer]: TXlsCellRange read GetCellMergedBounds;

    procedure InsertAndCopyRows(const FirstRow, LastRow, DestRow, aCount: integer);
    procedure DeleteRows(const aRow, aCount: integer);

    property CommentsCount[Row: integer]: integer read GetCommentsCount;
    property Comments[Row, Col: integer]: variant read GetComments write SetComments;

    property PicturesCount: integer read GetPicturesCount;
    property PictureName[aPos: integer]: Widestring read GetPictureName;
    procedure AssignPicture(const aPos: integer; const Pic: string; const PicType: TXlsImgTypes);
    procedure GetPicture(const aPos: integer; const Pic: TStream; var PicType: TXlsImgTypes; var ClientAnchor: TClientAnchor);

    property ShowGridLines: boolean read GetShowGridLines write SetShowGridLines;

    function CanOptimizeRead:boolean;

    function IsLoaded: boolean;
  published
    property Adapter: TExcelAdapter read FAdapter write SetAdapter;
    { Published declarations }
  end;

procedure Register;

implementation
{$R IFlexCelImport.res}
procedure Register;
begin
  RegisterComponents('FlexCel', [TFlexCelImport]);
end;

{ TFlexCelImport }

procedure TFlexCelImport.CloseFile;
begin
  if Workbook=nil then exit;
  try
    Workbook.CloseFile;
  except
    //nothing;
  end; //Except
  try
    Workbook.Disconnect;
  except
    //nothing;
  end; //Except
  FreeAndNil(Workbook);
end;

procedure TFlexCelImport.DeleteRows(const aRow, aCount: integer);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.DeleteRows(aRow, aCount);
end;

destructor TFlexCelImport.Destroy;
begin
  CloseFile;
  inherited;
end;

function TFlexCelImport.GetActiveSheet: integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.ActiveSheet;
end;

function TFlexCelImport.GetCellValue(Row, Col: integer): variant;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:= Workbook.CellValue[Row, Col];
end;

function TFlexCelImport.GetComments(Row, Col: integer): variant;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:= Workbook.CommentText[Row, Col];
end;

function TFlexCelImport.GetCommentsCount(Row: integer): integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.CommentsCount[Row];
end;

function TFlexCelImport.GetMaxRow: integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.MaxRow;
end;

function TFlexCelImport.GetMaxCol: integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.MaxCol;
end;


function TFlexCelImport.GetSheetCount: integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.SheetCount;
end;

function TFlexCelImport.GetActiveSheetName: widestring;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:= Workbook.ActiveSheetName;
end;

procedure TFlexCelImport.InsertAndCopyRows(const FirstRow, LastRow,
  DestRow, aCount: integer);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.InsertAndCopyRows(FirstRow, LastRow, DestRow, aCount, False);
end;

function TFlexCelImport.IsLoaded: boolean;
begin
  Result:= Workbook<>nil;
end;

procedure TFlexCelImport.Notification(AComponent: TComponent; Operation: TOperation);
begin
  inherited Notification(AComponent, Operation);
  if Operation = opRemove then
  begin
    if AComponent = FAdapter then
        FAdapter:= nil;
  end;
end;

procedure TFlexCelImport.OpenFile(const FileName: TFileName);
begin
  if FAdapter=nil then raise Exception.Create(ErrNoAdapter);
  CloseFile;
  Workbook:=FAdapter.GetWorkbook;
  try
    Workbook.Connect;
    Workbook.OpenFile(FileName);
    Workbook.SelectSheet(Workbook.ActiveSheet);
  except
    CloseFile;
    raise;
  end;
end;

procedure TFlexCelImport.Save(const FileName: TFileName);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.Save(true, FileName, nil);
end;

procedure TFlexCelImport.SetActiveSheet(const Value: integer);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.ActiveSheet:=Value;
  Workbook.SelectSheet(Value);
end;

procedure TFlexCelImport.SetAdapter(const Value: TExcelAdapter);
begin
  FAdapter := Value;
end;

procedure TFlexCelImport.SetCellValue(Row, Col: integer;
  const Value: variant);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.CellValue[Row, Col]:= Value;
end;

procedure TFlexCelImport.SetComments(Row, Col: integer;
  const Value: variant);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.AssignComment(Row, Col, Value);
end;

procedure TFlexCelImport.SetActiveSheetName(const Value: widestring);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.ActiveSheetName:=Value;

end;

function TFlexCelImport.IsEmptyRow(const aRow: integer): boolean;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.IsEmptyRow(aRow);
end;

function TFlexCelImport.GetColumnWidth(aCol: integer): integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.ColumnWidth[aCol];
end;

function TFlexCelImport.GetRowHeight(aRow: integer): integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.RowHeight[aRow];
end;

procedure TFlexCelImport.SetColumnWidth(aCol: integer; Value: integer);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.ColumnWidth[aCol]:=Value;
end;

procedure TFlexCelImport.SetRowHeight(aRow: integer; Value: integer);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.RowHeight[aRow]:=Value;
end;

function TFlexCelImport.GetIsFormula(Row, Column: integer): boolean;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.CellValueX[Row, Column].IsFormula;
end;

function TFlexCelImport.GetAutoRowHeight(Row: integer): boolean;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.AutoRowHeight[Row];
end;

procedure TFlexCelImport.SetAutoRowHeight(Row: integer; const Value: boolean);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.AutoRowHeight[Row]:=Value;
end;

function TFlexCelImport.GetCellFormat(Row, Col: integer): integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.CellValueX[Row, Col].XF;
end;

procedure TFlexCelImport.SetCellFormat(Row, Col: integer;
  const Value: integer);
var
  C: TXlsCellValue;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  C.IsFormula:=false;
  C.Value:=GetCellValue(Row, Col);
  C.XF:=Value;
  Workbook.CellValueX[Row, Col]:=C;
end;

function TFlexCelImport.GetCell(Row, Col: integer): TXlsCellValue;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.CellValueX[Row, Col];
end;

procedure TFlexCelImport.SetCell(Row, Col: integer;
  const Value: TXlsCellValue);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.CellValueX[Row, Col]:=Value;
end;

function TFlexCelImport.GetColorPalette(index: TColorPaletteRange): LongWord;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:= Workbook.ColorPalette[Index];
end;

procedure TFlexCelImport.SetColorPalette(index: TColorPaletteRange;
  const Value: LongWord);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.ColorPalette[Index]:=Value;
end;

function TFlexCelImport.GetColorPaletteCount: integer;
begin
  Result:=56;
end;


function TFlexCelImport.GetColumnFormat(aColumn: integer): integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.ColumnFormat[aColumn];
end;

function TFlexCelImport.GetRowFormat(aRow: integer): integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.RowFormat[aRow];
end;

procedure TFlexCelImport.SetColumnFormat(aColumn: integer;
  const Value: integer);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.ColumnFormat[aColumn]:=Value;
end;

procedure TFlexCelImport.SetRowFormat(aRow: integer; const Value: integer);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.RowFormat[aRow]:=Value;
end;

function TFlexCelImport.GetFormatListCount: integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.FormatListCount;
end;

function TFlexCelImport.GetFlxFormatList(index: integer): TFlxFormat;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  if (Index<0) or (Index>=GetFormatListCount) then
    raise Exception.CreateFmt(ErrIndexOutBounds,[index,'FormatList',0,GetFormatListCount]);
  Result:=Workbook.FormatList[index];
end;

function TFlexCelImport.GetCellFormatDef(aRow, aCol: integer): TFlxFormat;
var
  XF: integer;
begin
  XF:= CellFormat[aRow, aCol];
  if XF<0 then
  begin
    XF:=RowFormat[aRow];
    if XF<=0 then XF:=ColumnFormat[aCol];
  end;
  if (XF<0) then XF:=0;
  Result:=FormatList[XF];
end;

function TFlexCelImport.GetColByIndex(Row, ColIndex: integer): integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  if (ColIndex<=0) or (ColIndex>ColIndexCount[Row]) then
    raise Exception.CreateFmt(ErrIndexOutBounds,[ColIndex,'ColByIndex',1,ColIndexCount[Row]]);
  Result:=Workbook.ColByIndex(Row, ColIndex);
end;

function TFlexCelImport.GetColIndexCount(Row: integer): integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.ColIndexCount(Row);
end;

function TFlexCelImport.GetColIndex(Row, Col: integer): integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.ColIndex(Row, Col);
end;

function TFlexCelImport.GetDefaultColWidth: integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.DefaultColWidth;
end;

function TFlexCelImport.GetDefaultRowHeight: integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.DefaultRowHeight;
end;

function TFlexCelImport.GetShowGridLines: boolean;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.ShowGridLines;
end;

procedure TFlexCelImport.SetShowGridLines(const Value: boolean);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.ShowGridLines:=Value;
end;

function TFlexCelImport.GetPictureName(aPos: integer): Widestring;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.PictureName[-1, aPos];
end;

function TFlexCelImport.GetPicturesCount: integer;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.PicturesCount[-1];
end;

procedure TFlexCelImport.AssignPicture(const aPos: integer;
  const Pic: string; const PicType: TXlsImgTypes);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.AssignPicture(-1, aPos, Pic, PicType);
end;

procedure TFlexCelImport.GetPicture(const aPos: integer;
  const Pic: TStream; var PicType: TXlsImgTypes;
  var ClientAnchor: TClientAnchor);
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Workbook.GetPicture(-1, aPos, Pic, PicType, ClientAnchor);
end;

function TFlexCelImport.CanOptimizeRead: boolean;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.CanOptimizeRead;
end;


function TFlexCelImport.GetCellMergedBounds(aRow, aCol: integer): TXlsCellRange;
begin
  if Workbook=nil then raise Exception.Create(ErrNoOpenFile);
  Result:=Workbook.CellMergedBounds[aRow, aCol];
end;

end.