unit TExcelExportUnit;
interface
uses SysUtils, Variants, Dialogs, Grids, BaseGrid, AdvGrid, ShlObj, ComObj, Math;
type
TExcelExport =
class
strict protected
FExcelApp: OleVariant;
FWorkbook: OleVariant;
FWorksheet: OleVariant;
FRange: OleVariant;
FData: OleVariant;
FValue: OleVariant;
FMaxCol, FMaxRow: integer;
public
constructor OpenTemp(Pfad:
string);
procedure exportieren(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;
procedure SetMaxCol(val: integer);
procedure SetMaxRow(val: integer);
procedure SaveDokument(Pfad:
string);
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
write SetMaxCol;
property MaxRow: integer
read GetMaxRow
write SetMaxRow;
end;
var ExportGrid: TExcelExport;
implementation
function StringToVariant(
const SourceString :
string) : Variant;
var
FloatValue : Extended;
begin
if TryStrToFloat(SourceString, FloatValue)
then
Result := FloatValue
else
Result := SourceString;
end;
constructor TExcelExport.OpenTemp(Pfad:
string);
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 VarIsNull(FWorkbook)
then
showmessage('
Datei konnte nicht geöffnet werden.');
end;
Finally
end;
end;
procedure TExcelExport.SaveDokument(Pfad:
string);
begin
FExcelApp.ActiveWorkbook.SaveAs(Pfad);
end;
//Inhalt eines TStringGrid nach Excel exportieren
procedure TExcelExport.exportieren(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
//FValue := UnAssigned;
FData := UnAssigned;
//FRange := UnAssigned;
//FWorkbook := UnAssigned;
//FExcelApp := 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;
procedure TExcelExport.SetMaxCol(val: integer);
begin
FMaxCol:= val;
end;
procedure TExcelExport.SetMaxRow(val: integer);
begin
FMaxRow:= val;
end;
initialization
ExportGrid := TExcelExport.Create;
finalization
if ExportGrid <>
nil then
ExportGrid.Free;
end.