php download into excel format

[xushi]

in myPhPAdmin, there is this nice functionality to dump DB-entries into an excel file. Can someone point me to any scripts, which can do this?

thanks

[mitchind]

I've had great success with the PEAR Spreadsheet/Excel/Writer.php module.

You have to create your own PEAR folder and download the OLE and Spreadsheet modules, then include them in your code and change the ini path to include the PEAR directory.

I build a php file on the fly to create the spreadsheet with data from query and then send it to the browser.

I can post some of my code if you're interested - but you should download the PEAR modules first.

[foofoo]

Could you post some code any way mitch? I'd like to see how you do this as PEAR was the route I was going to go with .xls file writing.

[mitchind]

Will do - I don't know if my example will help or not. I use PHP5 and sqlite database connection, but methods should be basically the same for writing to the file - just change the query calls as needed.

What I do is build a temporary php file that creates the excel file - then show users the link to that php file when completed.

Feel free to modify for your use, suggest changes or ask questions. It does demonstrate the use of several useful methods in the Spreadsheet class.


<?php
/************************************************** ********

File: make_excel_file.php5

Description:
Creates a php file that can be called as a link
to display an excel file based on a SQLite query.

Input Parameters:
$db = already opened SQLite db connection
$sql_select = SELECT query to get results from database
$output_php_file = location of output php file - absolute path
(e.g. "/www/Y/O/YOurdomain.com/htdocs/excel/build_excel_file.php")
************************************************** ********/

// Build a php file to send Excel file directly to browser
$root_dir = '/www/Y/O/YOurdomain.com';
$PEAR_dir = "$root_dir/PEAR";
$temp_dir = "$root_dir/tmp";
$logo = "$root_dir/htdocs/images/insert-logo.bmp";
$logo_ht = 42; // height of row (not pixels)

$fh_xl = fopen($output_php_file,"w");

if ($fh_xl) {
fwrite($fh_xl, '<?php '."\n");

// Set PEAR directory to be in include path
fwrite($fh_xl, "ini_set('include_path', ini_get(include_path) . ':$PEAR_dir');\n");
fwrite($fh_xl, "require_once('Spreadsheet/Excel/Writer.php');\n");

fwrite($fh_xl, '$workbook = new Spreadsheet_Excel_Writer();' . "\n");
fwrite($fh_xl, '$workbook->setTempDir(' . "'$temp_dir');\n");

fwrite($fh_xl, '$workbook->send("AlbertaLandmanExtract.xls");' . "\n");
fwrite($fh_xl, '$worksheet =& $workbook->addWorksheet("Title Data");' . "\n");

fwrite($fh_xl, '$format_header =& $workbook->addFormat();' . "\n");
fwrite($fh_xl, '$format_header->setFontFamily(' . "'Verdana');\n");
fwrite($fh_xl, '$format_header->setSize(8);' . "\n");
fwrite($fh_xl, '$format_header->setBold();' . "\n");
fwrite($fh_xl, '$format_header->setColor(' . "'white');\n");
fwrite($fh_xl, '$format_header->setFgColor(' . "'blue-gray');\n");
fwrite($fh_xl, '$format_header->setAlign(' . "'center');\n");

fwrite($fh_xl, '$format_num =& $workbook->addFormat();' . "\n");
fwrite($fh_xl, '$format_num->setFontFamily(' . "'Arial');\n");
fwrite($fh_xl, '$format_num->setSize(8);' . "\n");
fwrite($fh_xl, '$format_num->setColor(' . "'black');\n");
fwrite($fh_xl, '$format_num->setAlign(' . "'center');\n");
fwrite($fh_xl, '$format_num->setNumFormat(1);' . "\n");

fwrite($fh_xl, '$format_txt =& $workbook->addFormat();' . "\n");
fwrite($fh_xl, '$format_txt->setFontFamily(' . "'Arial');\n");
fwrite($fh_xl, '$format_txt->setSize(8);' . "\n");
fwrite($fh_xl, '$format_txt->setColor(' . "'black');\n");
fwrite($fh_xl, '$format_txt->setAlign(' . "'center');\n");

fwrite($fh_xl, '$format_std =& $workbook->addFormat();' . "\n");
fwrite($fh_xl, '$format_std->setFontFamily(' . "'Arial');\n");
fwrite($fh_xl, '$format_std->setSize(8);' . "\n");
fwrite($fh_xl, '$format_std->setColor(' . "'black');\n");
fwrite($fh_xl, '$format_std->setAlign(' . "'left');\n");

//add new picture format to spreadsheet but can't combine it with font formats

// add picture format for LINC IDs
fwrite($fh_xl, '$custom_fmt =& $workbook->addFormat();' . "\n");
fwrite($fh_xl, '$custom_fmt->setNumFormat('. "'0000 000 000');\n");

fwrite($fh_xl, '$format_linc =& $workbook->addFormat();' . "\n");
fwrite($fh_xl, '$format_linc->setFontFamily(' . "'Arial');\n");
fwrite($fh_xl, '$format_linc->setSize(8);' . "\n");
fwrite($fh_xl, '$format_linc->setColor(' . "'black');\n");
fwrite($fh_xl, '$format_linc->setAlign(' . "'center');\n");

// will it identify and combine the new format now???
fwrite($fh_xl, '$format_linc->setNumFormat('. "'0000 000 000');\n");

$result = $db->query($sql_select);

// Write Header Line
$n_fields = $result->numFields();
$r = 1; // starting row number
$c = 0; // starting column number
while ($c < $n_fields) {

// store column widths to process at end
switch ($result->fieldName($c)) {

case "LincID":
$col_width[$c] = 12;
break;

case "Sec":
case "Twp":
case "Rge":
case "Mer":
$col_width[$c] = 6;
break;

case "TotalInstruments":
$col_width[$c] = 14;
break;

case "Parcel":
case "Quarter":
$col_width[$c] = 8;
break;

case "SurfaceMineral":
case "PostalCode":
case "TitleNumber":
case "Estate":
$col_width[$c] = 12;
break;

case "City":
case "Province":
$col_width[$c] = 12;
break;

default:
$col_width[$c] = 20;

}

// Write header row in bold
fwrite($fh_xl, '$worksheet->writeString(' . "$r, $c, '" . $result->fieldName($c++) . "'," . ' $format_header);' . "\n");
}


// Write Data
$r++; // set first row of data
foreach ($result as $row) {
$c = 0;
while ($c < $n_fields) {
$data = "$row[$c]";
switch ($result->fieldName($c)) {

case "LincID":
fwrite($fh_xl, '$worksheet->writeNumber(' . "$r, $c, $data," . ' $custom_fmt);' . "\n");
fwrite($fh_xl, '$worksheet->writeNumber(' . "$r, " . $c++ . ", $data," . ' $format_linc);' . "\n");
break;

case "Sec":
case "Twp":
case "Rge":
case "Mer":
fwrite($fh_xl, '$worksheet->writeNumber(' . "$r, " . $c++ . ", $data," . ' $format_num);' . "\n");
break;

case "TotalInstruments":
fwrite($fh_xl, '$worksheet->writeNumber(' . "$r, " . $c++ . ", $data," . ' $format_num);' . "\n");
break;

case "Parcel":
case "Quarter":
fwrite($fh_xl, '$worksheet->writeString(' . "$r, " . $c++ . ", '$data'," . ' $format_txt);' . "\n");
break;

case "SurfaceMineral":
case "PostalCode":
case "TitleNumber":
case "Estate":
// align center
fwrite($fh_xl, '$worksheet->writeString(' . "$r, " . $c++ . ", '$data'," . ' $format_txt);' . "\n");
break;

case "City":
case "Province":
// align left - 12 wide
fwrite($fh_xl, '$worksheet->writeString(' . "$r, " . $c++ . ", '$data'," . ' $format_std);' . "\n");
break;

default:
fwrite($fh_xl, '$worksheet->writeString(' . "$r, " . $c++ . ", '$data'," . ' $format_std);' . "\n");
}
}
$r++;
}

// Set column widths
$c=0;
while ($c < $n_fields) {
fwrite($fh_xl, '$worksheet->setColumn(' . "$c, $c, " . $col_width[$c++] . ");\n");
}

// Merge top row cells for clean look at logo
$last_col = ($n_fields - 1);
fwrite($fh_xl, '$worksheet->mergeCells(0, 0, 0, ' . "$last_col);\n");


/* ************** Important - do this AFTER all column widths and merges! ************/
// Set logo row height
fwrite($fh_xl, '$worksheet->setRow(0,' . "$logo_ht);\n");

// Insert Logo into Top Left corner of worksheet
fwrite($fh_xl, '$bitmap = ' . "'$logo';\n");
fwrite($fh_xl, '$worksheet->insertBitmap(0, 0, $bitmap);' . "\n");
/************************************************** **************************/


// Freeze top row and logo
$rows_to_freeze = 2;
$cols_to_freeze = 0;
fwrite($fh_xl, '$freeze_panes = array(' . "$rows_to_freeze, $cols_to_freeze);\n");
fwrite($fh_xl, '$worksheet->freezePanes($freeze_panes);' . "\n");


// Send direct to browser when they open file
fwrite($fh_xl, '$workbook->close();' . "\n");
fwrite($fh_xl, "flush();\n");
fwrite($fh_xl, '?> '."\n");

flush();
fclose($fh_xl);

sleep(1);
clearstatcache();
unset($fh_xl);
unset($result);
}

?>

[WATRD]

This is what I use. Change then entries designated by <xxxxxx> to suit;

<?php
define(db_host, "<mysqlXX.powweb.com>");
define(db_user, "<USERNAME>");
define(db_pass, "<PASSWORD>");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "<DBNAME>");
mysql_select_db(db_name);

$select = "SELECT * FROM <TABLENAME>";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

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

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);

if ($data == "") {
$data = "\n(0) Records Found!\n";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=<NAMEFORSPREADSHEETOUTPUT>.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>

[CHIS]

So did you install PEAR to your website or just the module? If you installed it could you please point me to the installation. In advance thanks.

[mitchind]

I didn't install PEAR - just the modules I needed.

At least from the directory I'm looking at - I'll check again.