Beefy Boxes and Bandwidth Generously Provided by pair Networks vroom
Perl: the Markov chain saw
 
PerlMonks  

How can I use Spreadsheet::WriteExcel to write a url label without formatting it (turning ints to floats)

by uG (Scribe)
on May 25, 2011 at 19:13 UTC ( #906696=perlquestion: print w/ replies, xml ) Need Help??
uG has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to show an eBay item number (example: 270751343164) and have it be a hyperlink. I believe WriteExcel is setting the format to what it thinks is right, but in this case it is not (example: 2.70751E+11). I've looked and fiddled with the various formaters, but i'm not having any luck. Can someone point me in the right direction?

Comment on How can I use Spreadsheet::WriteExcel to write a url label without formatting it (turning ints to floats)
Re: How can I use Spreadsheet::WriteExcel to write a url label without formatting it (turning ints to floats)
by roboticus (Canon) on May 25, 2011 at 19:39 UTC

    uG:

    Do you mean that the write_url() method isn't working for you? If not, what's wrong?

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

Re: How can I use Spreadsheet::WriteExcel to write a url label without formatting it (turning ints to floats)
by chilledham (Pilgrim) on May 25, 2011 at 21:35 UTC

    Are you sure Spreadsheet::WriteExcel is the culprit? I imagine you see the formatted version of your number in the cell, but what do you see in the function bar when you click on the cell?

    I pasted 270751343164 into an Excel sheet and through the magic of changing the cell's width saw the value change between: ###, 3E+11, 2.71E+11 and 2.70751E+11. The function bar always displayed the correct value, however.

    Try the write_string method and see if your numbers look better.

Re: How can I use Spreadsheet::WriteExcel to write a url label without formatting it (turning ints to floats)
by jmcnamara (Monsignor) on May 25, 2011 at 22:35 UTC

    As chilledham points out it is Excel that is converting the number to a floating point format.

    The way to avoid this (in Excel and Spreadsheet::WriteExcel) is to specify a cell format to ensure that the number is displayed in the way you want. You will also need to add some link style formatting to ensure it also looks like a link. Here is a working example:

    #!/usr/bin/perl use warnings; use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new( 'example.xls' ); my $worksheet = $workbook->add_worksheet(); my $link_format = $workbook->add_format( color => 'blue', underline => 1, num_format => 1, ); $worksheet->set_column( 'B:B', 20 ); $worksheet->write_url( 'A1', 'www.perl.com', 270751343164, $link_forma +t ); $worksheet->write_url( 'B1', 'www.perl.com', 270751343164, $link_forma +t ); __END__

    The set_column() method call is there to show how to display the number since it is now larger than the cell.

    --
    John.
      Thanks everyone. I've always been able to avoid Excel for a regular ol' CSV, so this was my first experience. I assumed it was WriteExcel because in the docs it mentioned that it will format things how it thinks they should be formatted, in addition to my inexperience with Excel.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://906696]
Approved by planetscape
Front-paged by toolic
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (11)
As of 2014-04-17 19:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (454 votes), past polls