How to save many data into Excel file?-CodePudding

I would like to save data into Excel file based on data from text file. I tried to put 2 lines in text file. When saving the data in Excel file, I can only save 1 line from text file. I cannot save both lines in excel.

Below is my text file data :

1957041.0F1,9850-LOGOUT PROBE,B27A,Waiting,12
4511533.021,9050-OFFLOAD SEND,B18A,24,Waiting,12

output excel file out should be printed out that two lines as well. my output excel file

1957041.0F1,9850-LOGOUT PROBE,B27A,Waiting,12

below is my sample code:

use strict;  
use Excel::Writer::XLSX;  

my $workbook= Excel::Writer::XLSX->new( 'C:/Users/pphyuphway/Downloads/myExcel.xlsx' );  
 
my $worksheet = $workbook->add_worksheet(); 


my $file =  "C:/Users/pphyuphway/Downloads/lot.txt";
my $file1 =  "C:/Users/pphyuphway/Downloads/test321.txt";

open( my $fh4, "<", $file) or die "Could not open file '$file' $!";
my @check;
my $count = 0;
foreach my $check (<$fh4>) {
   my ($lot,$step,$designID,$status,$duration)  = split(/\,/,$check);

                    $count = $.;
                    print($count);
                    for(my $i = 1; $i<= $count ; $i  )
                      {
                            $worksheet->write( "A$i", "$lot");
                            $worksheet->write( "B$i", "$designID");
                            $worksheet->write( "C$i", "$step");
                            $worksheet->write( "D$i", "$duration");
                            $worksheet->write( "E$i", "$status");
                            $worksheet->write( "F$i", "Error");
                            $workbook->close;  
                            
                      }
 
                        
                     }

CodePudding user response：

You don't need nested loops. For each line of input, write to the spreadsheet. Close the spreadsheet after the loop.

use warnings;
use strict;
use Excel::Writer::XLSX;

my $workbook= Excel::Writer::XLSX->new( 'C:/Users/pphyuphway/Downloads/myExcel.xlsx' );
my $worksheet = $workbook->add_worksheet();

my $file = "C:/Users/pphyuphway/Downloads/lot.txt";
open( my $fh4, "<", $file) or die "Could not open file '$file' $!";

my $i = 1;
foreach my $check (<$fh4>) {
    my ($lot,$step,$designID,$status,$duration) = split(/,/, $check);
    $worksheet->write( "A$i", $lot);
    $worksheet->write( "B$i", $designID);
    $worksheet->write( "C$i", $step);
    $worksheet->write( "D$i", $duration);
    $worksheet->write( "E$i", $status);
    $worksheet->write( "F$i", "Error");
    $i  ;
}
$workbook->close;