Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question converting PHP records to excel file..

    I have preview page that shows simple details of properties. you can see address, price etc.. There is a sort option on this page where you for example select records 1,3,12,10 and click a button and all of records that are not selected disappear. If you click details link in depth details are listed.

    I need to find a way to convert these records from the PHP DB to an excel file.

    I found a script that will convert the entire table to a excel file but I need to find a method that will only convert certain records (the ones that are selected)

    here is that code, I figure it might be a good place to start:

    PHP Code:
    <?php
    //Written by Dan Zarrella. Some additional tweaks provided by JP Honeywell
    //pear excel package has support for fonts and formulas etc.. more complicated
    //this is good for quick table dumps (deliverables)

    include('DB_connection.php');
    $result mysql_query('select * from excel_test'$linkID);
    $count mysql_num_fields($result);

    for (
    $i 0$i $count$i++){
        
    $header .= mysql_field_name($result$i)."\t";
    }

    while(
    $row mysql_fetch_row($result)){
      
    $line '';
      foreach(
    $row as $value){
        if(!isset(
    $value) || $value == ""){
          
    $value "\t";
        }else{
    # important to escape any quotes to preserve them in the data.
          
    $value str_replace('"''""'$value);
    # needed to encapsulate data in quotes because some data might be multi line.
    # the good news is that numbers remain numbers in Excel even though quoted.
          
    $value '"' $value '"' "\t";
        }
        
    $line .= $value;
      }
      
    $data .= trim($line)."\n";
    }
    # this line is needed because returns embedded in the data have "\r"
    # and this looks like a "box character" in Excel
      
    $data str_replace("\r"""$data);


    # Nice to let someone know that the search came up empty.
    # Otherwise only the column name headers will be output to Excel.
    if ($data == "") {
      
    $data "\nno matching records found\n";
    }

    # This line will stream the file to the user rather than spray it across the screen
    header("Content-type: application/octet-stream");

    # replace excelfile.xls with whatever you want the filename to default to
    header("Content-Disposition: attachment; filename=excelfile.xls");
    header("Pragma: no-cache");
    header("Expires: 0");

    echo 
    $header."\n".$data;
    ?>

    any ideas?

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,280
    Thanks
    4
    Thanked 83 Times in 82 Posts
    If you only want certain records exported then redefine the SQL statement to restrict the number of records based on the conditions you want

    PHP Code:
    query "SELECT * from excel_test WHERE column1value = 'foo' AND  column2value LIKE 'bar'"

    $result mysql_query($query$linkID); 
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,845
    Thanks
    21
    Thanked 157 Times in 148 Posts
    My initial idea is to take all of your selected records, create a new (possibly temporary) table, insert the values from the selected records into your new table, and convert the table to an Excel file (using your script)...then you can either choose to keep the new table, truncate it, or drop it, depending on what you're trying to do....

    In other words, you can still use your script the way it is ---assuming that it works.....And I'm looking at the script: Does this script take your information and attach or embed the info as an Excel spreadsheet in an e-mail message? If it does, that's pretty cool...Also, is this the entire script? ....And, you say this actually works?....just curious....

    Edit: Modify the query like Spookster said....but I'd still like to know exactly what this script does when it's run..
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :-)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!
    ♪♪ …Need Web Hosting For My YouTube-To-Mp3 Conversion Software? Check Here !!… ♪♪

  • #4
    New Coder
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The page I have DOES work currently, but it does not do the excel stuff yet.

    you can see the page the way it is now @ http://www.lenderrecovery.com/list.php?contract_num=2

    the code for that page is below:
    PHP Code:
    <?php

    // session check
    //session_start();
    //if (!session_is_registered("SESSION")) {
        // if session check fails, invoke error handler
        //header("Location: error.php?e=2");
        //exit();
    //}

    // search.phtml
    include ("common5.php");
    include (
    "functions.php");
    include (
    "foreclosure_listing_header.php");

    //$stmt = "SELECT * FROM fuser WHERE email = '".$_SESSION['SESSION_UNAME']."' AND selected_counties like '%$county%'";

    if (!($dblink mysql_pconnect($host$user$pass)))
    {
      print(
    "Error connecting to host<br>");
      print(
    "<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
      exit();
    }

    if (!
    mysql_select_db($db$dblink))
    {
      print(
    "Error selecting database<br>");
      print(
    "<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
      exit();
    }

    // execute the statement


    //if (!$result = mysql_query($stmt, $dblink))
    //{
      //print("Error verifying your identity<br>");
      //print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
    // exit();
    //}

    //if (mysql_num_rows($result) < 1)
    //{
    // print("You aren't allowed to view this county.<br>");
    // print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
    //    exit();
    //}

    $stmt "SELECT ID,address,city,state,county,units,bedrooms,baths,assessed_value,contract_num FROM portfolio WHERE contract_num = '".$contract_num."'";

    $list '';
    if(!empty(
    $_POST['kc']) && is_array($_POST['kc'])) {
      foreach(
    $_POST['kc'] as $kc) {
        
    $kc = (int)$kc;
        if(
    $kc 0) {
          
    $list .= "'$kc','$kc'";
        }
      }
      
    $stmt .= " AND ID IN ($list)";
    }

    if (
    $sortby==1)
    $stmt .= " ORDER BY address";

    elseif (
    $sortby==2)

    $stmt .= " ORDER BY city";

    elseif (
    $sortby==3)

    $stmt .= " ORDER BY state";

    elseif (
    $sortby==4)

    $stmt .= " ORDER BY county";

    elseif (
    $sortby==5)

    $stmt .= " ORDER BY units";

    elseif (
    $sortby==6)

    $stmt .= " ORDER BY bedrooms";

    elseif (
    $sortby==7)

    $stmt .= " ORDER BY baths";

    elseif (
    $sortby==8)

    $stmt .= " ORDER BY assessed_value";

    else

    $stmt .= " ORDER BY address";

    if (!(
    $dblink mysql_pconnect($host$user$pass))) {
            print(
    "Error connecting to host ");
            print(
    "<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
            exit();
    }

    if (!
    mysql_select_db($db$dblink)) {
            print(
    "Error selecting database ");
            print(
    "<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
            exit();
    }

    ?><title>Portfolio Listings</title>
        
    <style type="text/css">
    <!--
    .style1 {
        font-family: Verdana, Arial, Helvetica, sans-serif;
        font-size: 12px;
    }
    -->
    </style>
    <table width="690" cellspacing="0" cellpadding="3" border="0" style="font-family: sans-serif; font-size: 9pt" align="center">
      <!--DWLayoutTable-->
      <tr>
        <td width="31" height="21" valign="top"><div align="center"><strong>View</strong></div>
        </td>
        <td width="139" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=1" class="content">Address</a></div>
        </td>
        <td width="111" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=2" class="content">City</a></div>
        </td>
        <td width="28" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=3" class="content">State</a></div>
        </td>
        <td width="100" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=4" class="content">County</a></div>
        </td>
        <td width="35" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=5" class="content">Units</a></div>
        </td>
        <td width="58" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=6" class="content">Bedrooms</a></div>
        </td>
        <td width="32" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=7" class="content">Baths</a></div>
        </td>
      <td width="102" valign="top"><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=8" class="content">Assessed
            Value</a></div>
      </td>
      <td width="102" valign="top">
      </form>
      <form name='filter' action='' method='post'>
        <input type='submit' name='submit' value='filter' ></td> </tr>





      <?// execute the statement

    if (!$result mysql_query($stmt$dblink)) {
            print(
    "There was and error in the query ".mysql_error());
            print(
    "<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
            exit();
    } else {
    while (
    $row mysql_fetch_array($result)) {
                    
    $row_color altBgColor();
                    print (
    "<tr bgcolor=\"".$row_color."\">\n");
                    print 
    "\n<td align=\"left\" nowrap>
                    <a href=\"show_property.php?id=
                    "
    .$row["ID"]."\">Details</a></td>";
                    print 
    "\n<td align=\"left\">".$row["address"]."</td>";
                    print 
    "\n<td align=\"left\">".$row["city"]."</td>";
                    print 
    "\n<td align=\"left\">".$row["state"]."</td>";
                    print 
    "\n<td align=\"center\">".$row["county"]."</td>";
                    print 
    "\n<td align=\"left\">".$row["units"]."</td>";
                    print 
    "\n<td align=\"left\">".$row["bedrooms"]."</td>";
                    print 
    "\n<td align=\"left\"nowrap>".$row["baths"]."</td>";
                    print 
    "\n<td align=\"left\"nowrap>".$row["assessed_value"]."</td>";
                    print 
    "\n<td align=\"left\"nowrap><input type='checkbox' name='kc[]' value='{$row['ID']}' /></td>";
                    print 
    "</tr>";
            }
            }
    ?>
    </table>

    The code has been medified a bit but I still am having problems getting it working and I am not quite sure where to put it in my working page code.

    the modifications are below: (this does not work yet, thats where I need help)
    PHP Code:
    function xls_format_row$field ) {
        
    //-- $field is array containing columns values of single row of xls document --//
        //-- the code below was wrriten by Dan --//
        
    $line '';
        foreach(
    $field as $value){
            if(!isset(
    $value) || $value == ""$value "\t";
             else{
                
    # important to escape any quotes to preserve them in the data.
                
    $value str_replace('"''""'$value);
                
    # needed to encapsulate data in quotes because some data might be multi line.
                # the good news is that numbers remain numbers in Excel even though quoted.
                 
    $value '"' $value '"' "\t";
            }
        
    $line .= $value;
        }
        return 
    trim($line)."\n";
    }


    function 
    xls_send$headers$rows ) {
        
    //-- http headers were implemented by Dan--//
        # This line will stream the file to the user rather than spray it across the screen
        
    header("Content-type: application/octet-stream");
        
        
    # replace excelfile.xls with whatever you want the filename to default to
        
    header("Content-Disposition: attachment; filename=excelfile.xls");
        
    header("Pragma: no-cache");    
        
    header("Expires: 0");
        
        echo 
    $headers."\n".$rows;
    }
        

    if(! isset( 
    $_POST'selections' ])) {
        include(
    'DB_connection.php');
        
        
    //-- retrive records
        
    $result mysql_query('select * from excel_test'$linkID);
        
    $count mysql_num_fields($result);
        
        
    //-- retrive column names
        
    $headers'';
        for (
    $i 0$i $count$i++)
            
    $headers .= mysql_field_name($result$i)."\t";
        
        if( isset( 
    $_POST['cache'] )) echo 'You\'ll need to select something...';
        
        
    $i 0;
        echo 
    '<form method="post">';
        while(
    $row mysql_fetch_row($result)) {
            echo 
    '<input type="Checkbox" name="selections[]" value="'$i++ .'" >' implode ' '$row ) . '<br>';
            
            
    //-- my idea is also to cache cache the retrived data so that we will not have
            //-- to connect to db again... it should speed up the script when few checkboxes
            //-- of large collection will be selected
            //-- one can also use mysql_query once again after posting the form
            
            //-- i need to use htmlspecialchars because quotation marks are treated as
            //-- html code that ends attibute "value" of input
            
    echo '<input type="Hidden" name="cache[]" value="'htmlspecialcharsserialize($row) ) .'">';
        }
        
    //-- we can also cache columns' headers
        
    echo '<input type="Hidden" name="headers" value="'htmlspecialcharsserialize($headers) ) .'">';
        echo 
    '<input type="submit" value="Produce xls format"  >';
        echo 
    '</form>';
    } else {
        
    $cache $_POST['cache'];
        
    $selections $_POST['selections'];
        
    $selected_from_cache = array();
        
        
    //-- retrive values from cache --//
        
    foreach( $selections as $row_id $selected_from_cache[] = unserialize$cache$row_id ] );
        
        
    //-- retrive xls columns'headers from cache --//
        
    $headers unserialize$_POST['headers'] );
        
        
    //-- format data xls style --//
        
    $xls_rows '';
        foreach( 
    $selected_from_cache as $row )
            
    $xls_rows .= xls_format_row$row );
        
        
    //-- send prepared data to client --//
        
    xls_send$headers$xls_rows );

    Any Ideas?

  • #5
    New Coder
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    anyone have any ideas?

  • #6
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,280
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Well like I said previously if you want only certain records then modify your query. Currently your query is hardcoded to this:

    PHP Code:
        $result mysql_query('select * from excel_test'$linkID); 
    That is going to select all records from your database. If you only want certain records then define the SQL statement more.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #7
    New Coder
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    well my problem right now is I cannot get the data to actually go in the excel file, it just says "no matches found", first I'd like to get the data to show up then I will refine it.

    I guess I am stuck on what I need to change for the generic code to fit my needs, I am not very versed in PHP so I am not sure what parts of that code are variables that need to be changed to my data.. obviously I know I need to change the dbconnection file and I think I need to change the from_excel_test to my table name which is portfolio but what else has to be changed?

    And in this line of code you posted from the script, what is $linkID and do I need to change this to a variable of my own?

  • #8
    New Coder
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Also-- I found that if I remove $linkID, the data WILL work with excel, what is $linkID for?

    also any one have a clue how to only show certain fields?

  • #9
    New to the CF scene
    Join Date
    Jul 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i have this script:
    PHP Code:
    <?php

    $timestart 
    microtime();
    set_time_limit(900);

    $server "127.0.0.1"
    $username "usern"
    $password "pass"
    $dbname "Intranet"

    $db mssql_connect($server,$username,$password); 
    mssql_select_db($dbname,$db); 

    # begin of script
    $query "SELECT * FROM opzoek_list WHERE cust_number LIKE '100001%'"
    $result mssql_query($query,$db); 

    $count mssql_num_fields($result);
    echo 
    "Downloading $count fields";

    for (
    $i 0$i $count$i++){
        
    $header .= mssql_field_name($result$i)."\t";
    }

    while(
    $row mssql_fetch_row($result)){
      
    $line '';
      foreach(
    $row as $value){
        if(!isset(
    $value) || $value == ""){
          
    $value "\t";
        }else{
    # important to escape any quotes to preserve them in the data.
          
    $value str_replace('"''""'$value);
    # needed to encapsulate data in quotes because some data might be multi line.
    # the good news is that numbers remain numbers in Excel even though quoted.
          
    $value '"' $value '"' "\t";
        }
        
    $line .= $value;
      }
      
    $data .= trim($line)."\n";
    }
    # this line is needed because returns embedded in the data have "\r"
    # and this looks like a "box character" in Excel
      
    $data str_replace("\r"""$data);


    # Nice to let someone know that the search came up empty.
    # Otherwise only the column name headers will be output to Excel.
    if ($data == "") {
      
    $data "\nno matching records found\n";
    }

    # This line will stream the file to the user rather than spray it across the screen
    header("Content-type: application/octet-stream");

    # replace excelfile.xls with whatever you want the filename to default to
    header("Content-Disposition: attachment; filename=excelfile.xls");
    header("Pragma: no-cache");
    header("Expires: 0");

    echo 
    $header."\n".$data;

    # end of script
    When i execute i get this error:
    Notice: Undefined variable: header in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 21

    Notice: Undefined variable: data in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 38

    Warning: Cannot modify header information - headers already sent by (output started at c:\inetpub\wwwroot\sqltoexcel\index2.php:18) in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 52

    Warning: Cannot modify header information - headers already sent by (output started at c:\inetpub\wwwroot\sqltoexcel\index2.php:18) in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 55

    Warning: Cannot modify header information - headers already sent by (output started at c:\inetpub\wwwroot\sqltoexcel\index2.php:18) in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 56

    Warning: Cannot modify header information - headers already sent by (output started at c:\inetpub\wwwroot\sqltoexcel\index2.php:18) in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 57

    what to do?

  • #10
    New Coder
    Join Date
    Jun 2005
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Do I need to have excel install on server before I'm able to generate spreadsheet

    Hi,

    http://www.sitepoint.com/article/php...rksheet-server

    I'm able to run the above on XP, my machine,

    so my question is must I install openoffice or any other stuff on my server besides all the necessary php class.

    thks

    rgds


  •  

    Posting Permissions

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