Thread Excel und Win32::OLE --> Spaltenbreite und Sortier (18 answers)
Opened by Teutales at 2006-05-03 17:21

Teutales
 2006-05-08 13:25
#65545 #65545
User since
2006-03-21
47 Artikel
BenutzerIn
[default_avatar]
Code: (dl )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
...
#use strict; # deaktiviert wegen Excel-Sortierung
use Net::LDAP;
use Date::Calc qw(Delta_Days Today);
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3; #Fehler genau ausgeben

...

#########
#
# Methode die Excel-Datei erzeugt
##
sub createExcel {

# Lokale Variaben:
my @toDo = @_;
my $EVAL_ERROR;
my $excel;
my $book;
my $sheet;

showLine();
print "FUNCTION createExcel\n";

print "Try to open Excel.\n";

# falls Instanz von Excel schon läuft benutze diese:
eval {$excel = Win32::OLE->GetActiveObject('Excel.Application')};
return 1 if $EVAL_ERROR;

#wenn $excel noch nichts zugewiesen starte Excel:
unless (defined $excel) {
$excel = Win32::OLE->new('Excel.Application') #, sub {$_[0]->Quit;}
or return 1;
}
print "Excel ok.\n";

# Excel sichtbar/unsichtbar machen
$excel->{Visible} = 1;

# Alerts vertecken (0=verstecken)
$excel->{DisplayAlerts} = 0;

# Erzeuge leere Tabelle:
$book = $excel->Workbooks->Add;
$sheet = $book->Worksheets(1);

print "Write to File. \n";

my $length = @toDo;
if ($length eq 0) {
$sheet->Cells(1,1)->{Value} = "Keine passenden Einträge gefunden!";
}
else {
# Kopfzeile einfügen:
$sheet->Cells(1,1)->{Value} = "Last Login";
$sheet->Cells(1,2)->{Value} = "Days";
$sheet->Cells(1,3)->{Value} = "Fullname";
$sheet->Cells(1,4)->{Value} = "Context";
$sheet->Range('A1:E1')->Font->{Bold}=1;
$sheet->Range('A1:E1')->Font->{ColorIndex}=2;
$sheet->Range('A1:E1')->Interior->{ColorIndex}=5;

# Werte eintragen
for (my $i=0; $i<$length; $i++) {
$sheet->Cells($i+2,1)->{Value} = $toDo[$i][0];
$sheet->Cells($i+2,2)->{Value} = $toDo[$i][1];
$sheet->Cells($i+2,3)->{Value} = $toDo[$i][2];
$sheet->Cells($i+2,4)->{Value} = $toDo[$i][3];
}

# Breite anpassen:
my @columns = qw(C:D);
foreach my $range (@columns) {
$sheet->Columns($range)->AutoFit();
}

# Sortieren
my $excelLength = $length + 1;
$sheet->Range("A2:D$excelLength")->Sort({
Key1 => $sheet->Range("a1"),
Order1 => xlDescending
})
}
return 0;
}
\n\n

<!--EDIT|Teutales|1147080429-->

View full thread Excel und Win32::OLE --> Spaltenbreite und Sortier