home *** CD-ROM | disk | FTP | other *** search
- 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.
-