//StringGrid-Inhalt nach Excel exportieren
procedure exportKlientengridToExcel(Grid: TStringGrid);
const
xlWBATWorksheet = -4167;
var
SheetCount, SheetColCount, SheetRowCount, BookCount: Integer;
XLApp, Sheet, Data: OLEVariant;
I, J, N, M: Integer;
SaveFileName: String;
begin
//notwendige Sheetanzahl feststellen
SheetCount := (Grid.ColCount div 256) + 1;
if Grid.ColCount mod 256 = 0 then
SheetCount := SheetCount - 1;
//notwendige Bookanzahl feststellen
BookCount := (Grid.RowCount div 65536) + 1;
if Grid.RowCount mod 65536 = 0 then
BookCount := BookCount - 1;
//Create Excel-
OLE Object
try
XLApp := GetActiveOleObject('Excel.Application'); // laden wenn excel offen
except
XLApp := CreateOleObject('Excel.Application'); // oder Excel starten
end;
// nervige Meldungen ausschalten:
XLApp.DisplayAlerts := FALSE;
try
//Excelsheet anzeigen
XLApp.Visible := True;
//Workbook hinzufügen
for M := 1 to BookCount do
begin
XLApp.Workbooks.Add(xlWBATWorksheet);
//XLApp.Workbooks.Open(ExtractFilePath(ParamStr(0))+DIR_VORLAGEN+EXCEL_KLIENTENUEBERSICHT);
//Sheets anlegen
for N := 1 to SheetCount - 1 do
begin
XLApp.Worksheets.Add;
end;
end;
//Sheet ColAnzahl feststellen
if Grid.ColCount <= 256 then
SheetColCount := Grid.ColCount
else
SheetColCount := 256;
//Sheet RowAnzahl feststellen
if Grid.RowCount <= 65536 then
SheetRowCount := Grid.RowCount
else
SheetRowCount := 65536;
//Sheets befüllen
for M := 1 to BookCount do
begin
for N := 1 to SheetCount do
begin
//Daten aus Grid holen
Data := VarArrayCreate([1, Grid.RowCount, 1, SheetColCount], varVariant);
for I := 0 to SheetColCount - 1 do
for J := 0 to SheetRowCount - 1 do
if ((I+256*(N-1)) <= Grid.ColCount) and ((J+65536*(M-1)) <= Grid.RowCount) then
Data[J + 1, I + 1] := Grid.Cells[I+256*(N-1), J+65536*(M-1)];
XLApp.Worksheets[N].Select;
XLApp.Workbooks[M].Worksheets[N].Name := 'Klientenübersicht' + IntToStr(N);
// alle Zellen auswählen:
XLApp.Workbooks[M].Worksheets[N].Range[RefToCell(1, 1), RefToCell(SheetRowCount, SheetColCount)].Select;
// alle Zellen mittig ausrichten und dabei den Inhalt zu Strings formatieren:
XLApp.Selection.NumberFormat := '@';
XLApp.Selection.HorizontalAlignment := xlcenter;
// wieder an erste Zelle links oben hopsen und Daten dem Excelsheet übergeben
XLApp.Workbooks[M].Worksheets[N].Range['A1'].Select;
Sheet := XLApp.Workbooks[M].WorkSheets[N];
Sheet.Range[RefToCell(1, 1), RefToCell(SheetRowCount,SheetColCount)].Value := Data;
// die Kopfleiste formatieren:
XLApp.Range['A1:L1'].Select;
XLApp.Selection.Font.Bold := true;
XLApp.Selection.Interior.ColorIndex := 15; // Zellhintergrund einfärben
// die Border setzen: ------------------------------------------------
//XLApp.Range['A1:L1'].Select;
XLApp.Range['A1:L3'].Select;
XLApp.Selection.Borders.Item(xlDiagonalDown).LineStyle := xlNone;
XLApp.Selection.Borders.Item(xlDiagonalUp).LineStyle := xlNone;
XLApp.Selection.Borders.Item(xlEdgeLeft).LineStyle := xlContinuous;
XLApp.Selection.Borders.Item(xlEdgeLeft).Weight := xlMedium;
XLApp.Selection.Borders.Item(xlEdgeLeft).ColorIndex := xlAutomatic;
XLApp.Worksheets[N].PageSetup.Orientation := xlLandscape;
end;
end;
except
//Excel beenden
//if (not VarIsEmpty(XLApp)) then
//begin
// XLApp.DisplayAlerts := False;
// XLApp.Quit;
// XLAPP := Unassigned;
// Sheet := Unassigned;
//end;
end;
end;