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

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 303022015 by kshitij on Wednesday 22nd of August 2018 11:24:42 PM

08-23-2018

Registered User

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

Hi All ,

I have to write one Perl script in which I need to read one pre-existing xls and based on pattern match for one word in some cells of the XLS , I need to fill the entire row with one color of that matched cell and write the content to another excel

Please find the below stated script which I have written , Unfortunately it is just coloring the particular cell which is being matched but not the entire row.
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 ?

Thanks and Regards
Kshitij Kulshreshtha

kshitij

View Public Profile for kshitij

Find all posts by kshitij

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::container::format

Excel::Template::Container::Format(3pm) 		User Contributed Perl Documentation		   Excel::Template::Container::Format(3pm)

NAME

       Excel::Template::Container::Format - Excel::Template::Container::Format

PURPOSE

       To format all children according to the parameters

NODE NAME

       FORMAT

INHERITANCE

       Excel::Template::Container

ATTRIBUTES

       Boolean attributes should be set to 1, 0, true, or false.

       Color values can be the color name or the color index. See "COLOURS IN EXCEL" in Spreadsheet::WriteExcel

       o   align

	   Set to either left, center, right, fill, or justify. Default is left.  See also valign.

       o   bg_color

	   Set to a color value. Default is none.

       o   bold

	   This will set bold to on or off, depending on the boolean value.

       o   border

	   Set the border for all for edges of a cell. Also see bottom, top, left, and right.  Valid values are 0 - 7.

	   See "set_border()" in Spreadsheet::WriteExcel

       o   border_color

	   Sets the color value for the border. See also border, top_color, bottom_color, left_color and right_color.

       o   bottom

	   See border.

       o   bottom_color

	   See border_color

       o   color

	   This will set the color of the text, depending on color value. Default is black.

       o   fg_color

	   Set to a color value. This color will be used in foreground of some patterns. See color to change the color of text. Also see bg_color
	   and pattern.

       o   font

	   This will sent the font face. Default is Arial.

       o   font_outline

	   This will set font_outline to on or off, depending on the boolean value. (q.v.  OUTLINE tag)

       o   font_shadow

	   This will set font_shadow to on or off, depending on the boolean value. (q.v.  SHADOW tag). This only applies to Excel for Macintosh.

       o   font_strikeout

	   This will set font_strikeout to on or off, depending on the boolean value. (q.v.  STRIKEOUT tag)

       o   hidden

	   This will set whether the cell is hidden to on or off, depending on the boolean value.

       o   indent

	   Set the indentation level for a cell. Positive integers are allowed.

       o   italic

	   This will set italic to on or off, depending on the boolean value. (q.v. ITALIC tag)

       o   left

	   See border.

       o   left_color

	   See border_color.

       o   num_format

	   Set to the index of one of Excel's built-in number formats. See "set_num_format()" in Spreadsheet::WriteExcel

       o   pattern

	   Set to an integer, 0 - 18. Sets the background fill pattern of a ell. Default is 1, solid.

       o   right

	   See border.

       o   right_color

	   See border color.

       o   rotation

	   Set the rotation of the text in a cell. The rotation can be any angle in the range -90 to 90 degrees.  The angle 270 is also supported.
	   This indicates text where the letters run from top to bottom.

       o   shrink

	   A boolean value. If true, text will shrink to fit a cell.

       o   size

	   This will set the size of the font. Default is 10. Unless a row height is specifically set, the row will grow taller as necessary.

       o   text_justlast

	   A boolean value to justify the last line. Only applies to Far Eastern versions of Excel.

       o   text_wrap

	   A boolean value. When set to true, text will wrap in a cell instead of crossing over into empty cells. If the row height is not set,
	   the row will grow taller to accommodate the wrapping text.

       o   top

	   See border.

       o   top_color

	   See border_color

       o   valign

	   Set to top, vcenter, bottom, or vjustify. Default is vcenter. See also align.

CHILDREN

       None

EFFECTS

       None

DEPENDENCIES

       None

USAGE

	 <format bold="1">
	   ... Children here
	 </format>

       In the above example, the children will be displayed (if they are displaying elements) in a bold format. All other formatting will remain
       the same and the "bold"-ness will end at the end tag.

AUTHOR

       Rob Kinyon (rob.kinyon@gmail.com)

SEE ALSO

       BOLD, HIDDEN, ITALIC, OUTLINE, SHADOW, STRIKEOUT

perl v5.14.2							    2012-04-29				   Excel::Template::Container::Format(3pm)