Opened by timunix at 2020-03-20 01:09
I am writing a Perl script on a Windows Machine that is supposed to
iterate through some sheets of an Excel workbook
(`@typen` is a list of the workbook's required sheet names).
`$tbl` is the sheet name being treated at the moment.
The following steps are done inside the loops:
--
- Select sheet
- Unprotect sheet
- Delete entire rows with a `0` inside a cell in column D, starting from row 11 till last row of col D
- At the same time remove all white spaces in column Q
(The reason for this is there are companies inside the cells which start like this (- = empty spaces): ------------------My-Cool-Company----)
- Save the sheet as a csv-File (now properly formatted for DB import)
(If possible, I would like to keep the empty spaces between characters!)
In my script below I got the following errors (the first error doesnt appear all the time, the second one does):
`Use of uninitialized value in string eq at script.pl line 65`
`Can't use an undefined value as a HASH reference on line 65` **(I marked line 65!)**
`Can't use an undefined value as a method call for this: $sheet->Range("Q" . $i)->{Value}->Replace(What => " ", Replacement => ""); `
I GOT NO WARNINGS, Excel opens correctly and the first sheet is also selected. The sheet is unprotected and the last_row values is correctly 282. I don't understand why my variables are supposed to be NOT DEFINED or INITIALIZED. Can you see any syntax error or some other typo? Any help is highly appreciated, here is the code:
Last edited: 2020-03-20 07:56:05 +0100 (CET)
iterate through some sheets of an Excel workbook
(`@typen` is a list of the workbook's required sheet names).
`$tbl` is the sheet name being treated at the moment.
The following steps are done inside the loops:
--
- Select sheet
- Unprotect sheet
- Delete entire rows with a `0` inside a cell in column D, starting from row 11 till last row of col D
- At the same time remove all white spaces in column Q
(The reason for this is there are companies inside the cells which start like this (- = empty spaces): ------------------My-Cool-Company----)
- Save the sheet as a csv-File (now properly formatted for DB import)
(If possible, I would like to keep the empty spaces between characters!)
In my script below I got the following errors (the first error doesnt appear all the time, the second one does):
`Use of uninitialized value in string eq at script.pl line 65`
`Can't use an undefined value as a HASH reference on line 65` **(I marked line 65!)**
`Can't use an undefined value as a method call for this: $sheet->Range("Q" . $i)->{Value}->Replace(What => " ", Replacement => ""); `
I GOT NO WARNINGS, Excel opens correctly and the first sheet is also selected. The sheet is unprotected and the last_row values is correctly 282. I don't understand why my variables are supposed to be NOT DEFINED or INITIALIZED. Can you see any syntax error or some other typo? Any help is highly appreciated, here is the code:
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
use strict;
use warnings;
use Cwd;
use utf8;
use POSIX qw/ strftime /;
use File::Copy;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
#Global#
my $start_dir = cwd;
my $d = strftime("%d.%m.%Y %H:%M:%S", localtime);
my @typen = ("Befestigungsmaterial", "Ausbaumaterial", "Hls", "Installation", "Profilholz", "Spanplatten", "Dachstuhlholz", "Deckenbalken", "Wandelementholz", "Giebelbau", "Wandfertigung", "Individual");
# CALL #
excellent(@typen);
####################
### SUBROUTINE ###
####################
sub excellent{
my $filename = cwd . "/" . "inventur.xls"; #dateiname
my $excel = CreateObject OLE "Excel.Application"; #excel-objekt erstellen
$excel -> {Visible} = 1; #parameter festlegen
$excel->{DisplayAlerts}=0;
my $workbook = $excel -> Workbooks -> Open($filename); # Arbeitsmappe öffnen
foreach my $tbl (@_)
{
# Sheet selektieren
my $sheet = $workbook -> Worksheets("$tbl");
$sheet -> Select;
# Unprotect Sheet
$sheet->Unprotect();
# leere Zeilen löschen
my $last_row = $sheet -> Range("D1000")->End(xlUp)->Row; # letzte Zeile ermitteln
for(my $i=11;$i<=$last_row;$i++) ###### LINE 65 ######
{
if( $sheet->Range("D" . $i)->{Value} eq "0")
{
$sheet->Cells($i, 4)->EntireRow->Delete;
}
# Leerzeichen in Spalte entfernen
$sheet->Range("Q" . $i)->{Value}->Replace(What => " ", Replacement => "");
}
# Speichern und Schließen
$workbook->SaveAs({Filename => cwd . "/$tbl.csv",
FileFormat => xlCSV});
}
$workbook->Close();
$excel->Quit();
}
Last edited: 2020-03-20 07:56:05 +0100 (CET)