Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
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)
Replies are listed 'Best First'.
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.
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 roboticus (Chancellor) 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.

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 avoiding work at the Monastery: (12)
As of 2015-09-02 18:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred temperature scale is:










    Results (86 votes), past polls