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

I was wondering how one would go about adding borders to blocks of data within an excel column that have space above and below. I am able to output Excel workbook with data placed in columns without any problem. I am using Writer::Excel and can format the data using calls such as: 
      my $format_section = $workbook->add_format(
       
        font  => 'Arial',
        bold          => 1,
        size          => 8,
        pattern       => 1,
        border        => 0,
        color         => 'black',
        fg_color      => 'white',
        valign        =>  'top'
        
    );

[download]

If I turn on the border option, this will add the borders along all sides row-per-row as illustrated below:

 
data
data
 
data
data
 

What I need is to add borders around all sides excluding rows that do not have data. Here is an example:

data
data

data
data

Is there another module other than Writer::Excel that would help me to easily parse the contents of each column picking out the empty rows above and below each data set? Many thanks.

Replies are listed 'Best First'.
Re: Adding Borders to Excel Data (Excel::Writer)
by AppleFritter (Vicar) on Jul 04, 2014 at 16:21 UTC

    Is there another module other than Writer::Excel that would help me to easily parse the contents of each column picking out the empty rows above and below each data set?

    Off the top of my head, Spreadsheet::ParseExcel for older files, or Spreadsheet::XLSX for newer ones. I've never used them, though. CPAN's down at the moment, but have you checked meta::cpan?

    That's assuming you want to read an existing Excel file, of course.

[reply]
[reply]
Re: Adding Borders to Excel Data (Excel::Writer)
by ww (Archbishop) on Jul 04, 2014 at 17:57 UTC

    If you have the prerequisite VB fu, create an Excel macro and convert that to Perl. Not guaranteed to be easy if starting from ground zero, but if you have some relevant experience with VB, conversion should be almost painless (... or so say some :-) experts ).


    check Ln42!

[reply]
      I would really appreciate an example as to how the above can be accomplished with one of the suggested modules if not otherwise.
[reply]

        Take a look at Using Win32::OLE and Excel - Tips and Tricks.

        This code demo shows how to remove the borders around a cell. You could adapt this to scan down the columns and use some logic around previous cell,next cell to remove the borders you don't want.

        #!perl
use strict;
use Win32::OLE::Const 'Microsoft Excel';
Win32::OLE->Option(Warn => 3);

my $ex = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');
        
# change filename to suit
my $wb = $ex->Workbooks->Open('c:\\temp\\border.xls') ; 
my $ws = $wb->sheets(1);
my $cell = $ws->Cells(7,2); # b7
$cell->Borders(7)->{LineStyle}  =  xlNone; #left
$cell->Borders(8)->{LineStyle}  =  xlNone; #top
$cell->Borders(9)->{LineStyle}  =  xlNone; #bottom
$cell->Borders(10)->{LineStyle} =  xlNone; #right

# save and exit
$wb->SaveAs( 'c:\\temp\\changedborder.xls' );

        [download]
        poj
[reply]
[d/l]

Back to Seekers of Perl Wisdom