rockyurock has asked for the wisdom of the Perl Monks concerning the following question:

I am able to read the variables from excel and print them in the above program but I want to read these variables(from Variables to read from Excel sheet :) value from Input data (from My input data to search from:) and put them back into excel sheet which I am not able to do. can some one pls help on the same ? 
Variables to read from Excel sheet :

isim =
usim =
sim_invalid =
pcscf_discovery_flag
signaling_flag
ran_type
emergency_ind
rat
pcscf_v4
pcscf_v6
plmn
is_ems_support
ipv4
ipv6

[download]
My input data to search from:

8077  8116 D type hexa usim = 1, isim = 1
8077  8116 D type hexa usim = 1, isim = 1
pcscf_v4 : num = 2, protocol_type = 0x21, port_num = 0, addr = 10.56.5
+.85
8088 1223 D temp sim_invalid = 0
8099 1223 XX is_ems_support =   1 #88
8099 1224 XX pdn_act_ind->pcscf_discovery_flag  =  1
ind->signaling_flag            =  1
some text here plmn = 405872
DefaultStatusBarPlmnPlugin: into updateCarrierLabel
ipv6 = 36.5.2.4.25.143.200.141.0.0.0.0.32.20.232.161
get_ipv6_prefix,temp
ipv4 = 10.167.185.101
_send_ipv4_notify

[download]
MY program: 
#use strict;
#use 5.010;
use Spreadsheet::Read qw(ReadData);
my $book = ReadData ('UE_NW_Parameters.xlsx');
#my @files = grep { -f } (<*.txt>,<*main_log>,<*Project>,<*properties>
+);    
my @files = grep { -f } (<*main_log>);    
my @rows = Spreadsheet::Read::rows($book->[1]);
foreach my $i (1 .. scalar @rows) {
    foreach my $j (1 .. scalar @{$rows[$i-1]}) {
        my $temp = "\n$rows[$i-1][$j-1]";
        my @keywords = "$temp";
        print @keywords;
        }
    foreach my $file (@files){
        open(my $fh, '<', $file) or die $!;
        my @content = <$fh>;
        close($fh);
        my $l = 0;
        
        foreach my $kw (@keywords)
            {
                foreach (@content){
                    $l++;
                    my $search = $kw;
                    if ($search =~ /(\ d+)/)
                    {
                        printf 'Found keyword %s in file %s, line %d:%
+s'.$/, $search, $file, $l, $_;
                        last;
                    }
                    }
            }        
        }
            
}

[download]
  • Comment on How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column
  • Select or Download Code

Replies are listed 'Best First'.
Re: How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column
by thanos1983 (Parson) on Feb 17, 2017 at 15:01 UTC

    Hello rockyurock

    Update: After experimenting with the Spreadsheet::ParseExcel::SaveParser I noticed that it can not process xlsx file formats only xls file formats. Which in your case is not what you are looking for.

    So I checked again online and I found Excel::Writer::XLSX. The problem with this module is that only produces new xlsx files it can not read and process new same file. Taken from documentation:

    Excel::Writer::XLSX - Create a new file in the Excel 2007+ XLSX format
+.

    [download]

    So I tested it with the following example code:

    #!/usr/bin/perl
use strict;
use warnings;
use Excel::Writer::XLSX;

# Create a new workbook called simple.xls and add a worksheet
my $workbook  = Excel::Writer::XLSX->new( 'a_simple.xlsx' );
my $worksheet = $workbook->add_worksheet();

# The general syntax is write($row, $column, $token). Note that row an
+d
# column are zero indexed
#

# Write some text
$worksheet->write( 0, 0, "Hi Excel!" );


# Write some numbers
$worksheet->write( 2, 0, 3 );          # Writes 3
$worksheet->write( 3, 0, 3.00000 );    # Writes 3
$worksheet->write( 4, 0, 3.00001 );    # Writes 3.00001
$worksheet->write( 5, 0, 3.14159 );    # TeX revision no.?


# Write some formulas
$worksheet->write( 7, 0, '=A3 + A6' );
$worksheet->write( 8, 0, '=IF(A5>3,"Yes", "No")' );


# Write a hyperlink
my $hyperlink_format = $workbook->add_format(
    color     => 'blue',
    underline => 1,
);

$worksheet->write( 10, 0, 'http://www.perl.com/', $hyperlink_format );

    [download]

    On my linux operating system worked perfect produced the a_simple.xlsx and openoffice was able to open the file and read it without any problems. I do not have a Windows OS to test it but by 99.9% it will work just fine.

    So my proposed solution is to use your script that reads and parses your data and then create a new update xlsx sheet with the module.

    End of Update

    I think you are looking for this (Spreadsheet::ParseExcel::SaveParser). Taken from the documentation of the module:

    Spreadsheet::ParseExcel::SaveParser - Rewrite an existing Excel file.

    [download]

    The module that you are using can only read the excel file. Taken from (Spreadsheet::Read):

    Spreadsheet::Read - Read the data from a spreadsheet

    [download]
    Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
      So which package can help me with reading and writing both ?
[reply]

        Which Operating System ?. If windows then Win32::OLE perhaps

        poj
[reply]

Back to Seekers of Perl Wisdom