Perl Reading Excel sheet isssue

Shell Programming and Scripting
1

There is a perl scriptwhich will read Excel sheet and create one file(.v) .

Excel sheet::: 
     A              B   C   D
1  cpu_dailog   2   3   4

Perl will create the file(.v) like thsi :::

assert (cpu_dailog_iso ==2) ; 
assert (cpu_dailog_reset ==3);
assert (cpu_dailog_idle ==4);

My Requirement:::
If Row1+ColumnC (Value is 3 there) is having coloured background "Blue" , then i want to add comment . like this in generated file

assert (cpu_dailog_iso ==2) ; 
//assert (cpu_dailog_reset ==3);
assert (cpu_dailog_idle ==4);

Plz guys can any body help?
There are lot of signals. Its very pain to do it manually .

2

Well, it might be easier to use Excel or LibreOffice to globally replace the blue background with a nice selective key prefix in the cell, and save it as tab separated text. Is this one of several xls format or xlsx ?

3

My sample Excel spreadsheet, called "test_file.xlsx" looks like this -

  | A             | B   |   C   |   D   |
--+---------------+-----+-------+-------+
1 | cpu_dialog    | 2   |   3   |   4   |
--+---------------+-----+-------+-------+
2 | something_else| 20  |   30  |   40  |
--+---------------+-----+-------+-------+

and the background color of Cell C1 is blue.

Here's some Perl code:

c:\>
c:\>
c:\>REM Display the Perl program to process MS Excel workbook "test_file.xlsx"

c:\>
c:\>type read_excel.pl
#!perl -w
use strict;
use Win32::OLE;

$Win32::OLE::Warn = 3;              # die on errors
my $excelfile = 'test_file.xlsx';   # file to be read

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
            || Win32::OLE->new('Excel.Application', 'Quit');

my $Book = $Excel->Workbooks->Open($excelfile);
my $Sheet = $Book->Worksheets("Sheet1");

my $comment = "";
my @row_values;
my %suffix = qw (1 _iso 2 _reset 3 _idle);
foreach my $row (1..2) {
  foreach my $col ("a".."d") {
    my $range = $col.$row;
    push @row_values, $Sheet->Range($range)->{Value};
    if ($range eq "c1" and $Sheet->Range($range)->Interior->{ColorIndex} == 23) { # ColorIndex = 23 for Blue
      $comment = "//";
    }
  }
  foreach my $i (1..3) {
    printf ("%sassert (%s%s == %s);\n", $row==1 && $i==2 ? $comment : "", $row_values[0], $suffix{$i}, $row_values[$i]);
  }
  @row_values = ();
}

c:\>
c:\>
c:\>
c:\>REM Now run the Perl program

c:\>
c:\>perl read_excel.pl
assert (cpu_dialog_iso == 2);
//assert (cpu_dialog_reset == 3);
assert (cpu_dialog_idle == 4);
assert (something_else_iso == 20);
assert (something_else_reset == 30);
assert (something_else_idle == 40);

c:\>
c:\>
c:\>

tyler_durden

1 Like
4

OK, xlsx,very interesting, a zip file full of xml files. I suppose the right late model perl classes can make it simple, but maybe not that simple. It looks like color can be by index or rgb.

Have it print state change and value discovery messages as it goes. Did it find the cell, find the color, and what colors did it find?