Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Convert file to excel

Reply
Thread Tools

Convert file to excel

 
 
Deepu
Guest
Posts: n/a
 
      09-22-2006
Hi All,

I am trying to create a table with some data and store it in a file and
then it should be used in excel to create 3D bar chart. Can some one
provide me with some ideas on how it can be done.

Thanks for your time.

Code:

I have file which contains so many other details and have a line in the

file which start with:

#RESULT: 30 => YES: 10 NO: 10 UNKNOWN: 10

I have several files with each file have a line with RESULT.

##### Code starts here ####

#!/usr/bin/perl

@dirList= qw(FILE1 FILE2 FILE3 FILE4 FILE5);

foreach $dir (@dirList) {
open (PH, "$dir") || die "Can not open:$dir";

while (<PH>) {
if (/#RESULT: (\d+) => YES: (\d+) NO: (\d+) UNKNOWN: (\d+)/) {
push (@yesArray, $2);
push (@noArray, $3);
push (@unknownArray, $4);
}
}

close (PH);

}

## -- help from Mumia W ---
my $fmt = "-%10s %5s %5s %5s\n";
printf ($fmt, '', qw(YES NO UNKNOWN));

foreach $dir(@dirList) {
printf $fmt, $dir, $yesArray[$_], $noArray[$_], $unknownArray[$_];
$_++;
}

The output generated is:

YES NO UNKNOWN
FILE1 10 8 14
FILE2 6 7 20
FILE3 18 10 10
FILE4 20 12 10
FILE5 10 10 10

Now i am trying to save this table in a file so that it can be read by
Excel and create bar chart. Is there any way possible to automate this
flow.

Thanks for the help.

 
Reply With Quote
 
 
 
 
Brian McCauley
Guest
Posts: n/a
 
      09-22-2006

Deepu wrote:

> The output generated is:
>
> YES NO UNKNOWN
> FILE1 10 8 14
> FILE2 6 7 20
> FILE3 18 10 10
> FILE4 20 12 10
> FILE5 10 10 10
>
> Now i am trying to save this table in a file so that it can be read by
> Excel and create bar chart.


Excel can read CSV files.

There's also a module on CPAN to write Excel files but it can't do
fancy stuff like include macros.

> Is there any way possible to automate this flow.


You can drive Excel via OLE. (f you know the OLE commands you want
then Win32::OLE will let you do them).

Alternatively you could write an Excel macro that runs your Perl script.

 
Reply With Quote
 
 
 
 
Tad McClellan
Guest
Posts: n/a
 
      09-22-2006
Deepu <(E-Mail Removed)> wrote:

> #!/usr/bin/perl


use warnings;
use strict;


> @dirList= qw(FILE1 FILE2 FILE3 FILE4 FILE5);
> foreach $dir (@dirList) {



Calling something that is not a directory "dir" is likely to lead
to easily avoided confusion.


> open (PH, "$dir") || die "Can not open:$dir";



perldoc -q vars

What's wrong with always quoting "$vars"?


--
Tad McClellan SGML consulting
http://www.velocityreviews.com/forums/(E-Mail Removed) Perl programming
Fort Worth, Texas
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
How i convert a excel file to csv file ? Santana Perl Misc 1 08-07-2007 12:46 PM
ExtenXLS loads data into Excel 2002 file but damages the Excel file. kp2900@gmail.com Java 1 11-21-2006 05:48 PM
exporting an excel file from database; making changes to excel file and updating the database by importing it back Luis Esteban Valencia ASP .Net 1 01-12-2005 12:28 AM
how to convert excel file to text file in asp.net using VB rama ASP .Net 1 11-12-2003 05:24 PM



Advertisments