. * * * --Dynamic Excel or Word File from MySQL-- * php-doc-xls-gen for php/MySQL: (.doc or .xls dumper): * * This script takes the contents of a MySQL table and dumps it to * either a dynamically-generated MS Word File (file with ending '.doc') * or a dynamically-generated MS Excel File (file with ending '.xls'). * * Prerequisites: You Must have MS Word and/or MS Excel installed on * the same computer as your web browser for this to work (although * the script can be placed on any Unix/Linux server, you have to access it using * a browser on a Windows machine with either Word or Excel installed). * * How to use: * 1)edit the MySQL Connection Info below for your MySQL connection * & for the name of the MySQL table that you would like to make the dump for * 2)save this file somewhere on your server * 3)link to this file from another page: * a)for Word dumps: * link to word dump * b)for Excel dumps: * link to excel dump * --or else-- * create a Bookmark to this page (include any of the optional parameters * described below as part of the query string for the bookmarked URL) * 4)how to reuse this code to create a dump for ANY MySQL table on your server: * a)comment-out this line below under MySQL Connection Info: * //$DB_TBLName = "your_table_name"; * b)include the name of your MySQL table in links (or bookmarks) to this page * as an extra parameter: * ie: for word dump-- * link to word dump * ie: for excel dump-- * link to excel dump * c)all of the above also holds true for the name of the Database: * you could pass along the name of the Database as a parameter to this script * in order to use it on many different databases on your server: * comment out //$DB_DBName = "your database"; in this script below * and then link to this file like: * "this_file_name.php?$DB_DBName=your_database&DB_TBLName=your_table_name..." * 5)if you're resourceful, you could also pass the sql statement to be used for this * script as a parameter: "this_file_name.php?sql=..." * but you might have to URL-ENCODE your sql statement before passing it to this script, * and then URL-DECODE it in the beginning of this script for it to work. * * To change the formatting of the Word or Excel File generated: * change the respective parts of the coding for the word or the excel file that format * the database info sent to the browser. Most useful for this are the escape characters * for tabs ('\t') & line returns ('\n'). Experiment with these until you get the formatting * that you desire. * * If you're going to be using this script with SSL, please see the comments marked * 'A NOTE ABOUT USING THIS SCRIPT WITH SSL' found below! * * Comments, bugs, fixes to: * churmtom@hotmail.com * * Originally: Nov. 25th, 2001 * Updated: May 12th, 2002 * Updated: July 1st, 2002 * Updated: Jan 19th, 2003 - SSL Fix for MSIE * * Thanks to Josue & Steven d.B. for helping point out * improvements for this code! */ /* MySQL Connection Info: */ $DB_Server = "host"; /* your MySQL Server */ $DB_Username = "user"; /* your MySQL User Name */ $DB_Password = "password"; /* your MySQL Password */ $DB_DBName = "database"; /* your MySQL Database Name */ $table = "table"; /* your MySQL Table Name */ $DB_TBLName = $table; /* your MySQL Table Name */ $sql = "SELECT * from $table order by surname, given_names"; /* $DB_TBLName, $DB_DBName, may also be commented out & passed to the browser */ /* as parameters in a query string, so that this code may be easily reused for */ /* any MySQL table or any MySQL database on your server */ /* DEFINE SQL QUERY: */ /* you can use just about ANY kind of select statement you want - */ /* edit this to suit your needs! */ /* switch column name if exporting the 'pm_post' table. */ if ($table == "pm_post") { $userkey = "groupname"; } else { $userkey = "uemail"; } /* Optional: print out title to top of Excel or Word file with Timestamp */ /* for when file was generated: */ /* set $Use_Title = 1 to generate title, 0 not to use title */ $Use_Title = 1; /* define date for title: EDIT this to create the time-format you need */ $now_date = date ('m-d-Y H:i'); /* document title */ $title = "FBBG membership data, exported from www.fbbg.org.au on $now_date UTC"; /* Leave the connection info below as it is: just edit the above. (Editing of code past this point recommended only for advanced users.) */ /* create MySQL connection */ $Connect = @mysql_connect ($DB_Server, $DB_Username, $DB_Password) or die ("Couldn't connect to MySQL:
" . mysql_error () . "
" . mysql_errno ()); /* select database */ $Db = @mysql_select_db ($DB_DBName, $Connect) or die ("Couldn't select database:
" . mysql_error (). "
" . mysql_errno ()); /* execute query */ $result = @mysql_query ($sql,$Connect) or die ("Couldn't execute query:
" . mysql_error (). "
" . mysql_errno ()); /* if this parameter is included ($w=1), file returned will be in word format ('.doc') */ /* if parameter is not included, file returned will be in excel format ('.xls') */ if (isset ($w) && ($w==1)) { $file_type = "msword"; $file_ending = "doc"; } else { $file_type = "vnd.ms-excel"; $file_ending = "xls"; } /* header info for browser: determines file type ('.doc' or '.xls') */ header ("Content-Type: application/$file_type"); header ("Content-Disposition: attachment; filename=members.$file_ending"); header ("Pragma: no-cache"); header ("Expires: 0"); if (0) { /* A NOTE ABOUT USING THIS SCRIPT WITH SSL: ============================================== To get this script to work properly in MSIE, do the following: /* delete this header: */ /* and add these headers just after the "Expires: 0" header: */ header ("Keep-Alive: timeout=15, max=100"); header ("Connection: Keep-Alive"); header ("Transfer-Encoding: chunked"); } else header ("Pragma: no-cache"); /* Start of Formatting for Word or Excel */ if (isset ($w) && ($w==1)) /* check for $w again */ /* FORMATTING FOR WORD DOCUMENTS ('.doc') */ { /* create title with timestamp: */ if ($Use_Title == 1) { echo ("$title\n\n"); } /* define separator (defines columns in excel & tabs in word) */ $sep = "\n"; /* new line character */ while ($row = mysql_fetch_row ($result)) { /* set_time_limit (60); // HaRa */ $schema_insert = ""; for ($j=0; $j