Question

  • Creator
    Topic
  • #2150294

    PHP -> Excel

    Locked

    by emily_thess ·

    Hello everyone!

    Thats my first post 🙂 I am sending this because I have a problem with transfering data from MySQL to Excel by using php. I have found many open source classes in the web and I tried some of them. Althought the .xsl file is created, when I open the fileinexcell, I see that all the data are thrown in the first cell instead of having the table format ofspreadsheets that we all know.

    Please if help me if you know what is going wrong.
    Thank you 🙂

    I have Windows XP Professionall
    and using PHP Version 5.2.6 and Microsoft Office 2003.

All Answers

  • Author
    Replies
    • #2908038

      Clarifications

      by emily_thess ·

      In reply to PHP -> Excel

      Clarifications

    • #2908027

      Get Data From MySQL Database

      by Anonymous ·

      In reply to PHP -> Excel

      Using PHP you can run a MySQL SELECT query to fetch the data out of the database. You have several options in fetching information from MySQL. PHP provide several functions for this. The first one is mysql_fetch_array()which fetch a result row as an associative array, a numeric array, or both.
      Below is an example of fetching data from MySQL, the table contact have three columns, name, subject and message.
      Example : select.php
      Source code : select.phps, contact.txt

      ” .
      “Subject : {$row[‘subject’]}
      ” .
      “Message : {$row[‘message’]}

      “;
      }

      include ‘closedb.php’;
      ?>

      The while() loop will keep fetching new rows until mysql_fetch_array() returns FALSE, which means there are no more rows to fetch. The content of the rows are assigned to the variable $row and the values in row are then printed. Always remember to put curly brackets when you want to insert an array value directly into a string.
      In above example I use the constant MYSQL_ASSOC as the second argument to mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index . Personally I think it’s more informative to use $row[‘subject’] instead of $row[1].
      PHP also provide a function called mysql_fetch_assoc() which also return the row as an associative array.
      ” .
      “Subject : {$row[‘subject’]}
      ” .
      “Message : {$row[‘message’]}

      “;
      }

      include ‘closedb.php’;
      ?>

      You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.

      ” .
      “Subject : {$row[0]}
      ” .
      “Message : {$row[0]}

      “;
      }

      include ‘closedb.php’;
      ?>

      Using the constant MYSQL_NUM with mysql_fetch_array() gives the same result as the function mysql_fetch_row().
      There is another method for you to get the values from a row. You can use list(), to assign a list of variables in one operation.

      ” .
      “Subject : $subject
      ” .
      “Message : $row

      “;
      }

      include ‘closedb.php’;
      ?>

      In above example, list() assign the values in the array returned by mysql_fetch_row() into the variable $name, $subject and $message.
      Of course you can also do it like this
      ” .
      “Subject : $subject
      ” .
      “Message : $row

      “;
      }

      include ‘closedb.php’;
      ?>

      So you see you have lots of choices in fetching information from a database. Just choose the one appropriate for your program

      Freeing the memory ?
      In some cases a query can return large result sets. As this results are stored in memory there’s a concern about memory usage. However you do not need to worry that you will have to call this function in all your script to prevent memory congestion. In PHP all results memory is automatically freed at the end of the script’s execution.
      But you are really concerned about how much memory is being used for queries that return large result sets you can use mysql_free_result(). Calling this function will free all memory associated with the result identifier ( $result ).
      Using the above example you can call mysql_free_result() like this :

      Convert MySQL Query Result To Excel
      Using PHP to convert MySQL query result to Excel format is also common especially in web based finance applications. The finance data stored in database are downloaded as Excel file for easy viewing. There is no special functions in PHP to do the job. But you can do it easily by formatting the query result as tab separated values or put the value in an HTML table. After that set the content type to application/vnd.ms-excel
      Example : convert.php
      Source : convert.php, students.txt
      ” .implode(“ “, $row) . “

      “;
      }
      $tsv = implode(“\r\n”, $tsv);
      $html = “

      ” . implode(“\r\n”, $html) . “

      “;
      $fileName = ‘mysql-to-excel.xls’;
      header(“Content-type: application/vnd.ms-excel”);
      header(“Content-Disposition: attachment; filename=$fileName”);
      echo $tsv;
      //echo $html;
      include ‘library/closedb.php’;
      ?>

      In the above example $tsv is a string containing tab separated values and $html contain an HTML table. I use implode() to join the values of $row with tab to create a tab separated string.
      After the while loop implode() is used once again to join the rows using newline characters. The headers are set and the value of $tsv is then printed. This will force the browser to save the file as mysql-to-excel.xsl
      Try running the script in your own computer then try commenting echo $tsv and uncomment echo $html to see the difference.

      Thanks to:
      http://www.php-mysql-tutorial.com/php-mysql-select.php
      For the answer to this problem.

      Please post back if you have any more problems or questions.

Viewing 1 reply thread