use strict;
use warnings;
use Win32::OLE;
my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add;
for my $nSht (2..$wb->Sheets->{Count}) {
$wb->Sheets(2)->Delete;
}
my $sht = $wb->Sheets(1);
$sht->Cells(1, 1)->{Value}="Filesystem 1024-blocks Used A
+vailable Capacity Mounted on";
$sht->Cells(2, 1)->{Value}="/abc/defghi/dskvg00-root 1015704 5465
+16 416760 57% /";
$sht->Cells(3, 1)->{Value}="/abc/defghi/dskvg00-var 2539312 105462
+0 1353636 44% /var";
$sht->Cells(4, 1)->{Value}="/abc/defghi/dskvg00-tmp 1552232 4139
+2 1432208 3% /tmp";
$sht->Cells(5, 1)->{Value}="/abc/defghi/dskvg00-home 126931 56
+93 114685 5% /home";
$sht->Cells(6, 1)->{Value}="/abc/defghi/dskvg00-usr 2539312 124096
+0 1167280 52% /usr";
$sht->Cells(7, 1)->{Value}="/abc/defghi/dskvg00-usrlocal 126931
+ 7146 113232 6% /usr/local";
$sht->Range("A1:A" . $sht->UsedRange->Rows->{Count})->TextToColumns({
Destination => $sht->Range("A1"),
Space => 1,
ConsecutiveDelimiter => 1,
});
$sht->UsedRange->Columns->AutoFit;
Obviously this isn't the way to import the data, but you seem to have solved that already. There are three main changes:
Line 22 calculates the number of rows automatically. There are more complicated ways. Avoid them.
The ConsecutiveDelimiter line treats all spaces as a single delimiter.
The AutoFit line is purely cosmetic.
I can't work out quickly how to stop "Mounted on" being split into 2 columns. If there's anything that can be done about that at generation time, it might help. Or you might insert some code to remove & replace the space. Or live with it. Or something else.
Regards,
John Davies
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.