I want to write my hash map data into Excel

Question

Below is the output I got when I use print Dumper(\%Data) in my code

{
  "" => undef,
  "123456789012:SRIRAMA" => [123456789012, "SRIRAMA", 856.06, 0, 0, 0],
  "389252737122:RAMA" => [389252737122, "RAMA", 345.76, 0, 0, 0],
}

This data I have to write to an Excel file like below

Number          Name    number        name   amt    amt2    amt3    amt4
123456789012    SRIRAMA 123456789012 SRIRAMA 856.06  0      0       0
389252737122    RAMA    389252737122 RAMA    345.76  0      0       0

The first two columns are one SQL result and rest of the columns are another SQL query result.

The first query result I have put in a map and searched based on the key in second query result and finally I got the output above.

Here, Number and Name—the first two columns—are keys for searching the data.

The code below is after getting the SQL result:

    foreach ( @Sqlresult ) {
        $rec_cntr = $rec_cntr + 1;
        my @fields = undef;
        chop;

        next if /^$/;
        next if /ERROR:/;
        next if /ORA-/;

        @fields = split( /::/, $_ );
        my $fldref = @fields;
        $ent_id = undef;
        $ent_id = $fields[0];
        $key    = undef;
        $key    = $fields[0] . ":" . $name;

        push( @{ $Data{$key} }, $fields[1] );
    }

    $rec_cntr = 0;

The below code snippet I use when the records are not there pushing as zero.

    my $kkey = undef;

    for $kkey ( sort keys %Data ) {
        next if $kkey eq '';
        my $Lent = @{ $Data{$kkey} };
        if ( $Lent < 5 ) {
            push( @{ $Data{$kkey} }, 0 );
        }
        print scalar @{ $Data{$kkey} };

    }

    print Dumper( \%Data );

The above print Dumper produces the information shown at the start of the question

Here is where the data is written into an Excel sheet

my $dt = `date +%m-%d-%Y_%\I%\M`;

chop $dt;
my $FileName = "/data_reports/AdjestedFile" . $dt . ".xls";

#my $workbook = Spreadsheet::WriteExcel->new( $FileName );
my $workbook = Excel::Writer::XLSX->new( $FileName );

# Define the format and add it to the worksheet
my $format = $workbook->add_format(
    center_across => 1,
    bold          => 1,
    size          => 10,
    color         => "black",
    bg_color      => "grey",
    border_color  => "black",
    align         => "vcenter",
);

my $formatnum = $workbook->add_format();
$formatnum->set_num_format( '00000000000' );

my $formatamt = $workbook->add_format();
$formatamt->set_num_format( '0.00' );
$formatamt->set_align( 'right' );

my $formattext = $workbook->add_format( num_format => '@' );

my $prev_feetype = "";

my $current_ws;
$current_ws = $workbook->add_worksheet();
$current_ws->keep_leading_zeros( 1 );

$current_ws->set_column( 0, 16, 17, $formattext );
$current_ws->set_column( 1, 1,  13, $formattext );
$current_ws->set_column( 2, 2,  10, $formatnum );
$current_ws->set_column( 3, 3,  10, $formattext );
$current_ws->set_column( 4, 4,  10, $formattext );
$current_ws->set_column( 5, 5,  10, $formattext );
$current_ws->set_column( 6, 6,  10, $formattext );
$current_ws->set_column( 7, 7,  10, $formattext );

my $cl = 0;
$current_ws->write_string( 0, $cl++, "Number", $format );
$current_ws->write_string( 0, $cl++, "Name",   $format );
$current_ws->write_string( 0, $cl++, "amt",    $format );
$current_ws->write_string( 0, $cl++, "NA",     $format );
$current_ws->write_string( 0, $cl++, "NA",     $format );
$current_ws->write_string( 0, $cl++, "NA",     $format );
$current_ws->write_string( 0, $cl++, "NA",     $format );
$current_ws->write_string( 0, $cl++, "NA",     $format );

my $rownum = 1;

foreach ( %Data ) {

    my @fields = undef;
    chop;

    next if /^$/;
    @fields = split( /,/, $_ );

    my $fldref = \@fields;
    my $clcntr = 0;
    my $ent_id = "";

    foreach ( @fields ) {

        if ( $clcntr == 1 ) {
            $ent_id = $_;
        }
        if ( isfloat( $_ ) ) { #and $clcntr != 9 ) {

            $current_ws->write_number( $rownum, $clcntr++, $_ );
        }
        else {
            $current_ws->write_string( $rownum, $clcntr++, $_ );
        }
    }
}

Answer 1

There's a lot to read there, but these ideas may help

• Always use strict and use warnings at the top of every Perl program you write. It is invaluable for locating the more obvious bugs

• Don't initialise arrays with @data = undef. If you want to empty an existing array then write @data = (). If you are declaring a new array then my @data will create a new empty array

  The exact same advice applies to hashes, and that will be the reason for the "" => undef at the start of your %Data hash

• Don't use my $dt = `date +%m-%d-%Y_%\I%\M`. You are starting a whole new shell process just to ask it the time. You should

  use Time::Piece;
  

  and

  my $dt = localtime->strftime('%m-%d-%Y_%I%M');
  

  The result from this won't need chomping

  But are you sure you want %I? That gives you the 12-hour time, so the value will reset to zero at midday. %H gives you 24-hour time, and is much more likely to be useful

• chomp is preferable to chop unless you're doing something unusual. chop will just remove the last character from a string, whatever it is, while chomp will remove the last character if it is a newline

• for ( %Data ) { ... } will loop over the hash setting $_ to key1, val1, key2, val2 etc. That isn't what you want

  In this case, since the information in the key is duplicated in the value, you probably want for ( values %Data ) { ... }. But that value is an array reference so no splitting is required

This is probably closer to what you need

my $rownum = 0;

for my $values ( values %Data ) {

    my $colnum = 0;

    for my $val ( @$values ) {

        if ( isfloat($_) ) {
            $current_ws->write_number( $rownum, $colnum++, $val );
        }
        else {
            $current_ws->write_string( $rownum, $colnum++, $val );
        }
    }
}