How do I use Spreadsheet::WriteExcel to create a chart from numeric log data?

Question

I used csv2xls.pl to convert a text log into .xls format, and then I create a chart as in the following:

my $chart3 = $workbook->add_chart( type => 'line' , embedded => 1);

# Configure the series.
$chart3->add_series(
    categories => '=Sheet1!$B$2:$B$64',
    values     => '=Sheet1!$C$2:$C$64',
    name       => 'Test data series 1',
);

# Add some labels.
$chart3->set_title( name => 'Bridge Rate Analysis' );
$chart3->set_x_axis( name => 'Packet Size ' );
$chart3->set_y_axis( name => 'BVI Rate' );

# Insert the chart into the main worksheet.
$worksheet->insert_chart( 'G2', $chart3 );

I can see the chart in the .xls file. However, all the data are in text format, not numeric, so the chart looks wrong.

How do I convert text into number before applying this create-chart function?

Also, how do I sort the .xls file before creating the chart?

Answer 1

The way to do it in excel is to plug in this in a different cell:

=value(trim(clean(a1)))

where a1 is the cell which contains the original data which is coming up as text. Then use this cell reference for your charting.

HTH

Answer 2

If Excel is displaying the data as text then you have created the cells as text.

You omitted the code from csv2xls.pl so it is hard to guess what the exact problem is. Possibly you are using the Spreadsheet::WriteExcel method write_string() instead of write_number() or the generic write().

Also, how do I sort the .xls file before creating the chart?

The Excel file format doesn't have a sort option. Sorting is carried out by Excel after the file is loaded. A solution would be to sort the input CSV file before using Sreadsheet::WriteExcel to convert it.