Jump to content

Archived

This topic is now archived and is closed to further replies.

jbog91

Creating Excel file (xls)

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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 row
for ($i = 0; $i < $fields; $i++) {
    $header .= mysql_field_name($export, $i) . "\t";
}
// now we extract the data
while($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 tables
if ($data == "") {
    $data = "\n(0) Records Found!\n";                       
}
// now to download the results and file
header("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]

Share this post


Link to post
Share on other sites
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 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;
?>[/code]

Hope this helps those who are forced to deal with a MSSQL connection.

Cheers!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.