Code Review zu Fehlern in win32::OLE:Excel (Allgemeines zu Perl)

[thread]20758[/thread]

Code Review zu Fehlern in win32::OLE:Excel

Tags: perl5 use Subroutinen Ähnliche Threads

Leser: 11

Articles: hide open all | hide show old branches

+2 replies
Gast timunix

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:
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)
- Crian
  
  2020-03-20 08:01
  User since
  2003-08-04
  5872 Artikel
  ModeratorIn
  Versteht
  
  Code: (dl )
  
  Replace(What => " ", Replacement => "")
  
  auch reguläre Ausdrücke? Dann könntest du "^ *" durch "", " *$" durch "" und " +" durch " " ersetzen.
  
  Die Medlung in Zeile 65 scheint mir durch Besonderheiten der Exceltabellen zu kommen. Testest du mit verschiedenen Exceltabellen?
  
  Sind die Spalten D und Q gleich lang?
  
  Edit: Um dem Fehler auf den Grund zu gehen, könntet du
  
  Code: (dl )
  
  my $last_row = $sheet -> Range("D1000")->End(xlUp)->Row; # letzte Zeile ermitteln
  
  auflösen in Zwischenobjekte und jedes testweise ausgeben. Dann siehst du, was undefiniert ist.
  
  Edit 2: Gibt es D1000 immer?
  Last edited: 2020-03-20 08:03:41 +0100 (CET)
  s--Pevna-;s.([a-z]).chr((ord($1)-84)%26+97).gee; s^([A-Z])^chr((ord($1)-52)%26+65)^gee;print;
  
  use strict; use warnings; Link zu meiner Perlseite

View all threads created 2020-03-20 01:09.