| View previous topic :: View next topic |
| Author |
Message |
brent_weaver
Guru
Joined: 01 Jul 2004
Posts: 510
Location: Burlington, VT
|
 Posted: Thu Mar 07, 2013 10:55 am Post subject: Perl Syntax help [SOLVED] |
 |
|
I am using a sample script from cpan utilizing Spreadsheet::WriteExcel. There is a part of the script that does this:
| Code: |
my $headings = [ 'Category', 'Values 1', 'Values 2' ];
my $data = [
[ 2, 3, 4, 5, 6, 7 ],
[ 1, 4, 5, 2, 1, 5 ],
[ 3, 6, 7, 5, 4, 3 ],
];
|
What is this syntax? How is it accessed? Here is the whole script context:
| Code: |
#!/usr/bin/perl -w
###############################################################################
#
# A simple demo of Line charts in Spreadsheet::WriteExcel.
#
# reverse('©'), December 2009, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new( 'chart_line.xls' );
my $worksheet = $workbook->add_worksheet();
my $bold = $workbook->add_format( bold => 1 );
# Add the worksheet data that the charts will refer to.
my $headings = [ 'Category', 'Values 1', 'Values 2' ];
my $headings = qw( Category Values 1 Values 2 );
my $data = [
[ 2, 3, 4, 5, 6, 7 ],
[ 1, 4, 5, 2, 1, 5 ],
[ 3, 6, 7, 5, 4, 3 ],
];
$worksheet->write( 'A1', $headings, $bold );
$worksheet->write( 'A2', $data );
###############################################################################
#
# Example 1. A minimal chart.
#
my $chart1 = $workbook->add_chart( type => 'line' );
# Add values only. Use the default categories.
$chart1->add_series( values => '=Sheet1!$B$2:$B$7' );
$chart1->add_series( values => '=Sheet1!$C$2:$C$7' );
###############################################################################
#
# Example 2. A minimal chart with user specified categories (X axis)
# and a series name.
#
my $chart2 = $workbook->add_chart( type => 'line' );
# Configure the series.
$chart2->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
name => 'Test data series 1',
);
###############################################################################
#
# Example 3. Same as previous chart but with added title and axes labels.
#
my $chart3 = $workbook->add_chart( type => 'line' );
# Configure the series.
$chart3->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
name => 'Test data series 1',
);
# Add some labels.
$chart3->set_title( name => 'Results of sample analysis' );
$chart3->set_x_axis( name => 'Sample number' );
$chart3->set_y_axis( name => 'Sample length (cm)' );
###############################################################################
#
# Example 4. Same as previous chart but with an added series and with a
# user specified chart sheet name.
#
my $chart4 = $workbook->add_chart( name => 'Results Chart', type => 'line' );
# Configure the series.
$chart4->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
name => 'Test data series 1',
);
# Add another series.
$chart4->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$C$2:$C$7',
name => 'Test data series 2',
);
# Add some labels.
$chart4->set_title( name => 'Results of sample analysis' );
$chart4->set_x_axis( name => 'Sample number' );
$chart4->set_y_axis( name => 'Sample length (cm)' );
###############################################################################
#
# Example 5. Same as Example 3 but as an embedded chart.
#
my $chart5 = $workbook->add_chart( type => 'line', embedded => 1 );
# Configure the series.
$chart5->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
name => 'Test data series 1',
);
# Add some labels.
$chart5->set_title( name => 'Results of sample analysis' );
$chart5->set_x_axis( name => 'Sample number' );
$chart5->set_y_axis( name => 'Sample length (cm)' );
# Insert the chart into the main worksheet.
$worksheet->insert_chart( 'E2', $chart5 );
__END__
|
Ultimately I am trying to figure out a way to take a csv file and create a spreadsheet and graph in excel. If I would only figure out how to use the values read in from a flat .csv file??!?!?
Thanks in advance for even looking at this!
_________________
Brent Weaver
Last edited by brent_weaver on Fri Mar 22, 2013 1:29 pm; edited 1 time in total |
|
| Back to top |
|
 |
massimo
Veteran
Joined: 22 Jun 2003
Posts: 1226
|
| Posted: Thu Mar 07, 2013 12:29 pm Post subject: |
|
|
I usually use Text::CSV_XS to process files in CSV format.
_________________
Hello 911? How are you? |
|
| Back to top |
|
|
genterminl
Guru
Joined: 12 Feb 2005
Posts: 523
Location: Connecticut, USA
|
| Posted: Thu Mar 07, 2013 3:55 pm Post subject: |
|
|
I've long since forgotten the issues with lists vs arrays (that's parens vs square brackets) but I don't think it's important here. (I also don't know why $headings gets assigned twice - the second just overrides the first - so it may just be showing two ways to do it.
Do you have a specific question, or just need a general explanation?
However, it looks like $headings is just an arrays of values to go into the first row of the worksheet, and $data is an array of arrays where each array is a column of values. You should just try running it and play with the values until you are comfortable with how it works. That's what I like about Perl - it's easy to just keep trying different things until you get what you want. |
|
| Back to top |
|
|
brent_weaver
Guru
Joined: 01 Jul 2004
Posts: 510
Location: Burlington, VT
|
| Posted: Thu Mar 07, 2013 4:30 pm Post subject: |
|
|
Thanks for the response. I need to know how to store the following in the same way:
| Code: |
Date, License 1, License 2, License 3
2/4/13,3,4,5
2/5/13,5,4,3
...
|
_________________
Brent Weaver |
|
| Back to top |
|
|
genterminl
Guru
Joined: 12 Feb 2005
Posts: 523
Location: Connecticut, USA
|
| Posted: Thu Mar 07, 2013 4:39 pm Post subject: |
|
|
They are just text strings, I'm pretty sure (but admit I'm guessing). That's what I was referring to in the other post - you need to be sure things like dates and numbers are passed in in a format Excel will interpret correctly and not mess up, and then also check if you can set the format on the cells. If you want to be sure Excel takes something as text and does not even try to interpret as a number or date, prefix it with a single quote.
"3.4" will be seen as a number
"'3.4" will be seen as the text value "3.4"
"1/1/12" will (probably) be seen as the date value for 1 January 2012
"'1/1/12" will be seen as the text value
Out of curiosity - why are you going through this effort instead of just opening the csv with Excel (or LibreOffice) ?
NOTE: I have not used this perl module - I would check it's manual and examples to be sure it wants all values as text, or if it actually does something special with numbers or dates. The issue is that Excel does not always do what you want or expect, for example trimming leading 0's even if you consider a string of digits as an ID and not really a number. With dates - you also have to be careful of date/month vs month/date. |
|
| Back to top |
|
|
dataking
Apprentice
Joined: 20 Apr 2005
Posts: 251
|
| Posted: Thu Mar 07, 2013 10:34 pm Post subject: Re: Perl Syntax help |
|
|
| brent_weaver wrote: |
| Code: |
my $headings = [ 'Category', 'Values 1', 'Values 2' ];
my $data = [
[ 2, 3, 4, 5, 6, 7 ],
[ 1, 4, 5, 2, 1, 5 ],
[ 3, 6, 7, 5, 4, 3 ],
];
|
What is this syntax? How is it accessed? Here is the whole script context:
|
Here, $headings is a reference to an array and (IIRC) can be accessed like:
| Code: |
# get the value
print $headings->[0]."\n";
# output: Category
# set/change the value
$headings->[0] = 'Categories';
|
And $data is a reference to an array of arrays and can be accessed/set similarly to $headings, except that you need the double index.
| Code: |
# gets the value
print $data->[0][0];
# output: 2
|
| brent_weaver wrote: |
Thanks for the response. I need to know how to store the following in the same way:
Code:
| Code: |
Date, License 1, License 2, License 3
2/4/13,3,4,5
2/5/13,5,4,3 |
! |
You would set this data the same way they have in the example:
| Code: |
$myheaders = [ 'Date', 'License_1', 'License 2', 'License 3'];
$mydata = [
['2/4/13', '2/5/13'],
['3', '5'],
['4', '4'],
['5', '3']
]; |
_________________
-= the D@7@k|n& =- |
|
| Back to top |
|
|
brent_weaver
Guru
Joined: 01 Jul 2004
Posts: 510
Location: Burlington, VT
|
| Posted: Fri Mar 08, 2013 11:42 am Post subject: |
|
|
This is EXACTLY what I am looking for, thank you so much. One more question is how do I take the csv file and store it in arrays like that? So how do I read in each line and store the data. I know that line 1 will be the headings, and 2+ will be data. I have no issue with reading in the line, it is how do I store it using this method.
Someone asked me why I am am doing this, it is because I need to process hundreds of files each monday, and I will not do this manually.
Again thank you all SOO MUCH for the reposes, Gentoo forums has some GREAT people helping!
_________________
Brent Weaver |
|
| Back to top |
|
|
massimo
Veteran
Joined: 22 Jun 2003
Posts: 1226
|
|
| Back to top |
|
|
dataking
Apprentice
Joined: 20 Apr 2005
Posts: 251
|
| Posted: Fri Mar 08, 2013 4:13 pm Post subject: |
|
|
I'm not too familiar with Text::CSV_XS, but if my assumptions are correct, the example given by massimo points out one important thing.
The secondary arrays in the (referenced) array of arrays ($data) are columns (not rows).
So if you were to parse this line-by-line, based on your example here:
| Code: |
Date, License 1, License 2, License 3
2/4/13,3,4,5
2/5/13,5,4,3
...
|
... you would have to do something like:
| Code: |
my $i = 0;
open IN, $my_csv_file or die "Couldn't open input file for reading: $! \n";
while (my $line = <IN>) {
chomp $line; # remove ending whitespace
if ($i == 0) {
@myheaders = split(/\,/, $line);
$i++;
} else {
my @fields = split(/\,/, $line);
push @dates, $fields[0];
push @licenses_1, $fields[1];
push @licenses_2, $fields[2];
push @licenses_3, $fields[3];
}
}
close IN;
|
Then to populate the spreadsheet, you would do something like:
| Code: |
$headings = \@myheaders;
$data = [
\@dates,
\@licenses_1,
\@licenses_2,
\@licenses_3
];
|
If Text::CSV_XS parses data into columns (as the example implies), then that is definitely the way to go, because I'm betting it can also tell if/when there is a comma (,) in a field, rather than being used as a delimiter. (And that is a very handy feature, if it exists.)
It's also possible that you may not need to dereference the arrays when populating the spreadsheet data. If that's the case, the code would look like this:
| Code: |
$headings = @myheaders;
$data = [
@dates,
@licenses_1,
@licenses_2,
@licenses_3
];
|
It's been a while since I've used Spreadsheet::WriteExcel, though I used to use it extensively. $headings may need a derferenced array (\@myheaders) while $data may not. You'll just have to play with it.
HTH
_________________
-= the D@7@k|n& =- |
|
| Back to top |
|
|
brent_weaver
Guru
Joined: 01 Jul 2004
Posts: 510
Location: Burlington, VT
|
| Posted: Wed Mar 13, 2013 8:26 pm Post subject: |
|
|
Again thank you VERY much for the info. How do I deal with a variable line csv file?
_________________
Brent Weaver |
|
| Back to top |
|
|
dataking
Apprentice
Joined: 20 Apr 2005
Posts: 251
|
| Posted: Thu Mar 14, 2013 2:29 am Post subject: |
|
|
| brent_weaver wrote: | | Again thank you VERY much for the info. How do I deal with a variable line csv file? |
Need more information.
- Are you using Text::CSV_XS?
- What do you mean by "variable line csv file"? Is it a variable number of rows or a variable number of columns?
- Are you encountering a specific problem that may better be solved directly?
If a) I'm not really sure, simply because I'm not familiar with that module. If b) then a simple while loop should suffice (as demonstrated above). If you're processing multiple CSV files, the number of lines should be invisible to the while loop processing the file(s), as long as the arrays (@dates, @licenses_1, @licenses_2, @licenses_3) are global, or at least within scope of the loop handling the list of files and the writing of the spreadsheet. If you mean b) and you have a variable number of columns, you're kinda screwed. Now you get into regex matching data to figure out which column is which, etc., which is a total PITA (been there, done that).
If c), I would need more details about the specific problem to assist.
_________________
-= the D@7@k|n& =- |
|
| Back to top |
|
|
sundialsvc4
Guru
Joined: 10 Nov 2005
Posts: 436
|
| Posted: Thu Mar 14, 2013 3:24 am Post subject: |
|
|
| Suggest that you transport this question to http://www.perlmonks.org. (See you there.) |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
Portage & Programming
