SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    outputting to spreadsheet - how to format cell?

    I use OpenOffice but I suppose similar formatting rules apply to MS Excel. For example, to format a cell for two digit numbering, the format code is:

    #,##0.00

    But I also want to control font size and background color.

    I can use formulas (=F$t + 5) and there's likely a way to write formatting strings in the output code, I just need a little help. Any ideas?

    My code is fairly typical to export to spreadsheet:
    PHP Code:
    //get data from database
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"my-data.xls\"");
    $d "";
    $t 0;
    foreach(
    $r as $row) {
     
    $t++;
     
    $d .= $row['data1'].",".$row['some_number'].",=F$t + 5\r\n";
    };
    echo 
    $d

  2. #2
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can use number_format to control the decimal places/rounding, thousand seperators and full stop seperator

    I don't know about formatting the text straight into excel though...

    PHP Code:
    $num 10.785;
    echo 
    number_format($num,1); // rounds to 10.8 
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The Pear library contains an excel writer package.
    http://pear.php.net/package/Spreadsh...el_Writer/docs
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  4. #4
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried number format in php but the spreadsheet defaults to how every many non-zero digits. For example 10.80 displays on the default as 10.8, even with the number formatting in php.

    I can not seem to install the pear spreadsheet writer. I have other pear packages installed but it's not allowing my to install it without OLE and will not allow install of OLE - wierd.

  5. #5
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You do not need OLE, just make sure the Path to the binaries are in your system path.
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  6. #6
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by WebDevGuy View Post
    I tried number format in php but the spreadsheet defaults to how every many non-zero digits. For example 10.80 displays on the default as 10.8, even with the number formatting in php.
    In that case maybe you can tinker with the options in your spreadsheet program?
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  7. #7
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    my goal is for anyone using downloading the spreadsheet will be able to open it as a spreadsheet and not have to go through the process of defining the delimiters. My users all use IE 6

    I'm not going to go to each user and make sure they have an environmental var set up. If you mean for the server, that's a different matter.

    Thanks guys

  8. #8
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know you do not want to hear it, but your answer is simple.
    Install the PEAR Spreadsheet_Excel_writer and use the Format::setNumFormat.
    http://pear.php.net/manual/en/packag...tnumformat.php
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  9. #9
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by byron3@earthlink View Post
    You do not need OLE, just make sure the Path to the binaries are in your system path.
    Byron - when you say the "make sure the Path to the binaries are in your system path", are you referring to the server where pear is installed? It has nothing to do with the user machine, right?

    I have used pear spreadsheet write before on another machine and it worked fine. I liked it and would prefer to use it now.

    It's wierd. Whenever I do anything with pear now, a popup appears first "pop-up appears with "PHP.exe - ordinal not found. The ordinal 968 coul dnot be located in the dll LIBEAY32.dll". Then it tires to do the command. If I upgrade an existing package, it displays that popup then executes that command.

    Can you give specifics about making sure the path on windows? The reason I ask this is that all my other pear packages I have installed are working fine.

  10. #10
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, the server where pear is installed. The user does not need the pear package on their machine.

    To set the path :
    Control Panel => System => Advanced => Environment Variables => System Variables : Path.

    My binaries were located in C:\Pear\bin. You will need a semi-colon between the last entry and the Pear path you will append.
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •