Perl script to fill the entire row of Excel file with color based on pattern match Post: 303022436

Sponsored Content

Top Forums Shell Programming and Scripting Perl script to fill the entire row of Excel file with color based on pattern match Post 303022436 by durden_tyler on Friday 31st of August 2018 05:54:00 PM

08-31-2018

Registered User

Quote:

Originally Posted by kshitij

...
I am not sure how to extract the rows of the excel based on the pattern match and then fill the color of the designated rows and write to another excel.

Code:

#!/usr/bin/perl -w
 
use strict;
use Spreadsheet::ParseExcel;
 use Spreadsheet::WriteExcel;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('status.xls');
 
if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}
 
my $workbook1 = Spreadsheet::WriteExcel->new('Report.xls');
# Add a worksheet
my $worksheet1 = $workbook1->add_worksheet();
#  Add and define a format
my $format = $workbook1->add_format(bg_color => 'green');

for my $worksheet ( $workbook->worksheets() ) {
 
    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();
 
    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {
 
            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell;

    my $string = $cell->value();
      if ($string  =~ m/Completed/i)    { 

$worksheet1->write($row, $col, $cell_value,$format);

   }
else {

 $worksheet1->write($row, $col, $cell->value());
}
}
                 }
    }

This will write the content to another Excel but only the cells having "Completed" word is colored green but not the entire row having the cell "Completed".
Could you help me out in completing this script ?
...

As per the documentation of this module at: https://metacpan.org/pod/Spreadsheet::WriteExcel#set_row($row,-$height,-$format,-$hidden,-$level,-$collapsed)
I guess you could probably do something like this:

Code:

...
            my $string = $cell->value();
            if ($string  =~ m/Completed/i) {
                $worksheet1->set_row($row, undef, $format);
                $worksheet1->write($row, $col, $cell->value(), $format);
            } else {
                $worksheet1->write($row, $col, $cell->value());
            }
...

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Perl script to match a pattern and print lines

Hi I have a file (say 'file1')and I want to search for a first occurence of pattern (say 'ERROR') and print ten lines in the file below pattern. I have to code it in PERL and I am using Solaris 5.9. I appreciate any help with code Thanks Ammu

2. Shell Programming and Scripting

Merge two file data together based on specific pattern match

My input: File_1: 2000_t g1110.b1 abb.1 2001_t g1111.b1 abb.2 abb.2 g1112.b1 abb.3 2002_t . . File_2: 2000_t Ali england 135 abb.1 Zoe british 150 2001_t Ali england 305 g1111.b1 Lucy russia 126

3. UNIX for Dummies Questions & Answers

Match pattern in a field, print pattern only instead of the entire field

Hi ! I have a tab-delimited file, file.tab: Column1 Column2 Column3 aaaaaaaaaa bbtomatoesbbbbbb cccccccccc ddddddddd eeeeappleseeeeeeeee ffffffffffffff ggggggggg hhhhhhtomatoeshhh iiiiiiiiiiiiiiii ...

4. Shell Programming and Scripting

Help needed - Split large file into smaller files based on pattern match

Help needed urgently please. I have a large file - a few hundred thousand lines. Sample CP START ACCOUNT 1234556 name 1 CP END ACCOUNT CP START ACCOUNT 2224444 name 1 CP END ACCOUNT CP START ACCOUNT 333344444 name 1 CP END ACCOUNT I need to split this file each time "CP START...

5. Shell Programming and Scripting

Help with ksh-to read ip file & append lines to another file based on pattern match

Hi, I need help with this- input.txt : L B white X Y white A B brown M Y black Read this input file and if 3rd column is "white", then add specific lines to another file insert.txt. If 3rd column is brown, add different set of lines to insert.txt, and so on. For example, the given...

6. Shell Programming and Scripting

Perl script to Merge contents of 2 different excel files in a single excel file

All, I have an excel sheet Excel1.xls that has some entries. I have one more excel sheet Excel2.xls that has entries only in those cells which are blank in Excel1.xls These may be in different workbooks. They are totally independent made by 2 different users. I have placed them in a...

7. UNIX for Dummies Questions & Answers

Fill csv entire column with content from another text file

I have a csv that looks like this: ,yude-to-nap2,0,0,0,0,0 ,2twis-yude-to-nap2,0,0,0,0,0 ,2tiws-yude-to-nap2,0,0,0,0,0 ,2arcos-yude-to-nap2,0,0,0,0,0 and another file named m1 that has a single line of text as content: Feb 1 15:30:20 How can I fill the whole the empty column of the...

8. Shell Programming and Scripting

awk to update value based on pattern match in another file

In the awk, thanks you @RavinderSingh13, for the help in below, hopefully it is close as I am trying to update the value in $12 of the tab-delimeted file2 with the matching value in $1 of the space delimeted file1. I have added comments for each line as well. Thank you :). awk awk '$12 ==...

9. UNIX for Beginners Questions & Answers

Match pattern only between certain lines in entire file

Hello, I have input that looks like this: * 0 -1 103 0 0 m. 7 LineNr 23 ClauseNr 1: 1: 1: 304: 0 0 SentenceNr 13 TxtType: Q Pargr: 2.1 ClType:MSyn PS004,006 ZBX= 0 1 1 0 7 -1 -1 3 2 3 2 -1 1 1 -1 -1 -1 -1 0 501 0 PS004,006 ZBX ...

LEARN ABOUT DEBIAN

excel::template::element::cell

Excel::Template::Element::Cell(3pm)			User Contributed Perl Documentation		       Excel::Template::Element::Cell(3pm)

NAME

       Excel::Template::Element::Cell - Excel::Template::Element::Cell

PURPOSE

       To actually write stuff to the worksheet

NODE NAME

       CELL

INHERITANCE

       ELEMENT

ATTRIBUTES

       o   TEXT

	   This is the text to write to the cell. This can either be text or a parameter with a dollar-sign in front of the parameter name.

       o   COL

	   Optionally, you can specify which column you want this cell to be in. It can be either a number (zero-based) or an offset. See
	   Excel::Template for more info on offset-based numbering.

       o   REF

	   Adds the current cell to the a list of cells that can be backreferenced.  This is useful when the current cell needs to be referenced
	   by a formula. See BACKREF and RANGE.

       o   WIDTH

	   Sets the width of the column the cell is in. The last setting for a given column will win out.

       o   TYPE

	   This allows you to specify what write_*() method will be used. The default is to call write() and let Spreadsheet::WriteExcel make the
	   right call. However, you may wish to override it. Excel::Template will not do any form of validation on what you provide. You are
	   assumed to know what you're doing.

	   The legal types (taken from Spreadsheet::WriteExcel) are:

       o   COMMENT

	   Add a comment to the cell

	   o   blank

	   o   formula

	   o   number

	   o   string

	   o   url

	   o   date_time

	   other write_* methods as defined defined Spreadsheet::WriteExcel would be integrated by request

CHILDREN

       FORMULA

EFFECTS

       This will consume one column in the current row.

DEPENDENCIES

       None

USAGE

	 <cell text="Some Text Here"/>
	 <cell>Some other text here</cell>

	 <cell text="$Param2"/>
	 <cell>Some <var name="Param"> text here</cell>

       In the above example, four cells are written out. The first two have text hard-coded. The second two have variables. The third and fourth
       items have another thing that should be noted. If you have text where you want a variable in the middle, you have to use the latter form.
       Variables within parameters are the entire parameter's value.

       Please see Spreadsheet::WriteExcel for what constitutes a legal formula.

AUTHOR

       Rob Kinyon (rob.kinyon@gmail.com)

SEE ALSO

       ROW, VAR, FORMULA

perl v5.14.2							    2012-04-29				       Excel::Template::Element::Cell(3pm)