[PHP] MySQL/PHP to Excel

Hey guys,

I've found a nice script that pulls mysql data and prints it on a spreadsheet, but im working on a project at work tht lists domain names that are currently pending to be listed - i get it working fine but can i use a preformated spreadsheet to print the mysql results on to instead of a blank?

Other wise i have to customise it manually and i havent got the time as im so busy - if u dont understand wht im askin for please feel free to post below and ill do my best to re-explain

here is the code

PHP Code:

---

<?php

$time = time();

// DB Connect
define(db_host, "lolirl");
define(db_user, "usernameirl");
define(db_pass, "passwordirl");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, dbnameirl");
mysql_select_db(db_name);

// Query DB
$select = "SELECT domain_name, company_name, notes, exp_date FROM domains WHERE company_name = 'pending'";
$export = mysql_query($select);
$row_export = mysql_fetch_assoc($export);
$fields = mysql_num_fields($export);

// Is there any data or should we call it quits?
$num_rows = mysql_num_rows($export);
if (!$num_rows){
echo "<h1 style=\"font-family: Arial, Helvetica, sans-serif\">Sorry, no data available for export.</h1>";
exit();
}

// Set headers
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=spreadsheetnameirl_". date("mdy_Gis", $time) .".xls; size=$size_in_bytes");
header("Pragma: no-cache");
header("Expires: 0");

// Process rows
$count = mysql_num_fields($export);
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}

if ($num_rows){
    mysql_data_seek($export,0);
}

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);
echo $header."\n".$data;
?>

---

Preformatted? You mean how values are positioned, how wide columns are, how many number behind the dot? Can't you set that with your library?

Quote:

---

Originally Posted by Daevius

Preformatted? You mean how values are positioned, how wide columns are, how many number behind the dot? Can't you set that with your library?

---

Yeah thats right, but i've got the faintess idea on going arround it :x

Just a bump - Can someone point me in the right direction?

many thanks

Your "library" does not, in fact, do little more than produce a CSV file (well, tab seperated but same difference). This isn't an excel file and it can't, by definition, contain the excel formatting you require.

Your "solution" would be to scrap this and get a real Excel export library - mind that they do tend to cost money though.

you could use openoffice libs as free solution

I've done a lot of digging and worked it out, not worked out the formatting as of yet but i will look into at work tommorow - currently i can postion the data from mysql into any cell i choose which was something i wanted to achieve

Here is the code im using atm

PHP Code:

---

<?


mysql_connect("loclahost","user","pass");
mysql_select_db("databaseirl");
 
$result=mysql_query("SELECT * FROM title WHERE account = 'suspended' ORDER BY username ASC");


function xlsBOF() { 
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); 
return; 
} 
function xlsEOF() { 
echo pack("ss", 0x0A, 0x00); 
return; 
} 
function xlsWriteNumber($Row, $Col, $Value) { 
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0); 
echo pack("d", $Value); 
return; 
} 
function xlsWriteLabel($Row, $Col, $Value ) { 
$L = strlen($Value); 
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L); 
echo $Value; 
return; 
} 
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=spreadsheetnameirl.xls "); 
header("Content-Transfer-Encoding: binary ");

xlsBOF();

xlsWriteLabel(0,0,"A bit of text didnt do no harm :)");

xlsWriteLabel(2,0,"ID");
xlsWriteLabel(2,1,"Title");
xlsWriteLabel(2,3,"Link");

$xlsRow = 3;

while($row=mysql_fetch_array($result)){

xlsWriteNumber($xlsRow,0,$row['sqlvalue1']);
xlsWriteLabel($xlsRow,1,$row['sqlvalue2']);
xlsWriteLabel($xlsRow,3,$row['sqlvalue3']);

$xlsRow++;
} 
xlsEOF();
exit();
?>

---

Just looking into

PHP Code:

---

xlsWrite 


---

atm - will keep this post updated