jbog91 Posted August 1, 2006 Share Posted August 1, 2006 Well, what I need to do is take information from a mysql database and put it in an excel file. I have the database part down but the excel part is giving me trouble. I don't think pear is an option so anything other than it would be great. I know how to write to a file but I don't know what to write to do stuff like go to a different cell or something. I've seen where people do it using tables but that doesn't work. I need to be able to do the following things.Go to a new cell.Go to a new row.Make something bold.How can I do this? The file is an excel (.xls or .csv) but it will be opened in Works Spreadsheet by the client. Quote Link to comment https://forums.phpfreaks.com/topic/16169-creating-excel-file-xls/ Share on other sites More sharing options...
simcoweb Posted November 3, 2006 Share Posted November 3, 2006 Here's some code for creating the .xls file. Works great. Just edit the query info to match the table you want to draw the data from.[code]<?php// next we gather up the tables and find the field count$select = "SELECT * FROM Datafeed"; $export = mysql_query($select);$fields = mysql_num_fields($export); // next we fetch the data in each rowfor ($i = 0; $i < $fields; $i++) { $header .= mysql_field_name($export, $i) . "\t";}// now we extract the datawhile($row = mysql_fetch_row($export)) { $line = ''; foreach($row as $value) { if ((!isset($value)) OR ($value == "")) { $value = "\t"; } else { $value = str_replace('"', '""', $value); $value = '"' . $value . '"' . "\t"; } $line .= $value; } $data .= trim($line)."\n";}$data = str_replace("\r","",$data);// just in case there is no data in the tablesif ($data == "") { $data = "\n(0) Records Found!\n"; }// now to download the results and fileheader("Content-type: application/x-msdownload");header("Content-Disposition: attachment; filename=extraction.xls");header("Pragma: no-cache");header("Expires: 0");print "$header\n$data";echo "<center><h2>Done!</h2></center>";?>[/code] Quote Link to comment https://forums.phpfreaks.com/topic/16169-creating-excel-file-xls/#findComment-118950 Share on other sites More sharing options...
fractil Posted January 22, 2007 Share Posted January 22, 2007 This code worked great for querying a MSSQL database and writing to a .xls file. I just made the following changes to use an odbc connection. [code]<?php// Connect to database$sqlconnect=odbc_connect("<MSSQL SERVER>","<USER>","<PASSWORD>");$query="SELECT * FROM <table>;";// Get data records from table.$result=odbc_exec($sqlconnect,$query);$count = odbc_num_fields($result)+1;for ($i = 1; $i < $count; $i++){ $header .= odbc_field_name($result, $i)."\t";}$value="";$data="";$line="";while($row = odbc_fetch_array($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 screenheader("Content-type: application/octet-stream");# replace excelfile.xls with whatever you want the filename to default toheader("Content-Disposition: attachment; filename=excelfile.xls");header("Pragma: no-cache");header("Expires: 0");echo $header."\n".$data;?>[/code]Hope this helps those who are forced to deal with a MSSQL connection.Cheers! Quote Link to comment https://forums.phpfreaks.com/topic/16169-creating-excel-file-xls/#findComment-166568 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.