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

This node was taken out by the NodeReaper on Feb 23, 2017 at 22:32 UTC
  • Comment on Reaped: How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column

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 23, 2017 at 10:56 UTC

    Hello rockyurock,

    Regarding this part of your question: "and put them back into excel sheet which I am not able to do. can some one pls help on the same ?"

    A few days ago you asked the same question (How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column)

    And I replied "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."

    Have you tried it?

    I mean have you tried to write your data from the original excel (e.g. read.xlsx) file to the (e.g. write.xlsx) file?

    I post also a sample of code on how to create a new file.xlsx and insert your data.

    Let me try to make it more clear, read your data from 'UE_NW_Parameters.xlsx' with Spreadsheet::Read as you are currently doing. Store the data in @rows (as you are currently do in). Then detect all the elements you want to modify (as you are currently do in) and update them in a new @rows_2 (array).

    As a final step use Excel::Writer::XLSX module to create a new.xlsx file with the updated values that you have in @rows_2.

    Reposting sample of code tested on LinuxOS on how to write the data:

    #!/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 );

    I do not have WindowsOS to test the code but 99% it should work perfectly.

    Update: I searched on the web a bit more and I found also this useful tutorial How to read an Excel file in Perl, which contains examples on how to read and write excel files in Perl and How to create an Excel file with Perl? which will guide you how to write on excel files.

    Hope now it is more clear, let me know if something is not clear.

    Seeking for Perl wisdom...on the process of learning...not there...yet!
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 poj (Abbot) on Feb 23, 2017 at 07:49 UTC
    Variables to read from Excel sheet

    Where are these variables located in the workbook. Are they always in sheet 1 column 1 for instance ?

    poj