uses ... ComObj
Type
TExcelExport =
class
strict protected
FExcelApp: OleVariant;
FWorkbook: OleVariant;
FWorksheet: OleVariant;
FRange: OleVariant;
FData: OleVariant;
FValue: OleVariant;
FMaxCol, FMaxRow: integer;
public
constructor create(StringGrid : TStringGrid; Pfad:
string; Space:integer);
function RefToCell(Col, Row, Space : Integer) :
string;
function GetExcelApp: OleVariant;
function GetWorkbook: OleVariant;
function GetWorksheet: OleVariant;
function GetRange: OleVariant;
function GetData: OleVariant;
function GetValue: OleVariant;
function GetMaxCol: integer;
function GetMaxRow: integer;
procedure SetExcelApp;
procedure SetWorkbook;
procedure SetWorksheet;
procedure SetRange;
procedure SetData;
procedure SetValue;
property ExcelApp: OleVariant
read GetExcelApp;
property Workbook: OleVariant
read GetWorkbook;
property Worksheet: OleVariant
read GetWorksheet;
property Range: OleVariant
read GetRange;
property Data: OleVariant
read GetData;
property Value: OleVariant
read GetValue;
property MaxCol: integer
read GetMaxCol;
property MaxRow: integer
read GetMaxRow;
end;
implementation
function Min(X1, X2 : Integer) : Integer;
begin
if (X1 < X2)
then
Result := X1
else
Result := X2;
end;
{=============================================================================}
function Max(X1, X2 : Integer) : Integer;
begin
if (X1 > X2)
then
Result := X1
else
Result := X2;
end;
{=============================================================================}
//Inhalt eines TStringGrid nach Excel exportieren
constructor TExcelExport.create(StringGrid : TStringGrid; Pfad:
string; Space:integer);
var Row, Col: integer;
begin
//Verbindung zu Excel herstellen
FExcelApp := CreateOleObject('
Excel.Application');
try
if not VarIsNull(FExcelApp)
then
begin
//Neues FWorkbook öffnen
FWorkbook :=FExcelApp.Workbooks.open(pfad);
if not VarIsNull(FWorkbook)
then
begin
//Maximalen Bereich bestimmen
FMaxCol := Min(StringGrid.ColCount, FExcelApp.Columns.Count);
FMaxRow := Min(StringGrid.RowCount, FExcelApp.Rows.Count);
if (FMaxRow > 0)
and (FMaxCol > 0)
then
begin
//FWorksheet auswählen
FWorksheet := FWorkbook.ActiveSheet;
//Bereich auswählen
FRange := FWorksheet.Range[RefToCell(1, 1, Space), RefToCell(FMaxCol, FMaxRow, Space)];
if not VarIsNull(FRange)
then
begin
//Daten aus Grid holen
FData := VarArrayCreate([1, FMaxRow, 1, FMaxCol], varVariant);
for Row := 0
to Pred(FMaxRow)
do
begin
for Col := 0
to Pred(FMaxCol)
do
begin
FValue := StringToVariant(StringGrid.Cells[Col, Row]);
FData[Succ(Row), Succ(Col)] := FValue
end;
end;
//Daten dem Excelsheet übergeben
FRange.Value := FData;
//Excel anzeigen
FWorkbook.Activate;
FExcelApp.Visible := True;
end;
end;
end;
end;
finally
FData := UnAssigned;
end;
end;
function TExcelExport.RefToCell(Col, Row, Space : Integer) :
string;
var
Pos : Integer;
begin
//Spalte bestimmen
Result := '
';
while Col > 0
do
begin
Pos := Col
mod 26;
if Pos = 0
then
begin
Pos := 26;
Dec(Col);
end;
Result := Chr(Ord('
A') + Pos -1) + Result;
Col := Col
div 26;
end;
//Spalte und Zeile zusammenführen
Result := Result + IntToStr(Row+Space);
end;
function TExcelExport.GetExcelApp;
begin
Result:=FExcelApp;
end;
function TExcelExport.GetWorkbook;
begin
Result:=FWorkbook;
end;
function TExcelExport.GetWorksheet;
begin
Result:=FWorksheet;
end;
function TExcelExport.GetRange;
begin
Result:=FRange;
end;
function TExcelExport.GetData;
begin
Result:=FData;
end;
function TExcelExport.GetValue;
begin
Result:=FValue;
end;
function TExcelExport.GetMaxCol;
begin
Result:=FMaxCol;
end;
function TExcelExport.GetMaxRow;
begin
Result:=FMaxRow;
end;
procedure TExcelExport.SetExcelApp;
begin
FExcelApp:=UnAssigned;
end;
procedure TExcelExport.SetWorkbook;
begin
FWorkBook:=UnAssigned;
end;
procedure TExcelExport.SetWorksheet;
begin
FWorksheet:=UnAssigned;
end;
procedure TExcelExport.SetRange;
begin
FRange:=UnAssigned;
end;
procedure TExcelExport.SetData;
begin
FData:=UnAssigned;
end;
procedure TExcelExport.SetValue;
begin
FValue:=UnAssigned;
end;
{=============================================================================}