Create XL file from mysql table


As Experts requires today i have described here XL create class. Here we will show, How to create XL file from mysql tables? Its very simple for use. Just a simple mysql query and then config your database, a XL file will be created name as today date format. Next time we will show, How to create XL from XML file. That will come very soon i think.

XL Class file name iam_xls.php

<?php
class IAM_XLS {
/**
*
* @var string $xls_data Variable that holds the XLS File
* @access private
*/
var $xls_data;
var $crow = 0; // current row number
var $ccol = 0; // current column number
var $totalcol = 0; // total number of columns
var $header = 1; // 0=no header, 1=header line for xls table

/**
*
* @var string $xlsName Output Filename. No extension should be given as the class, as the class automatically attaches the XLS extension
* @access private
*/
var $xlsName;

/**
*
* @access public
* @param String $filename Output Filename. No extension should be given as the class, as the class automatically attaches the XLS extension
*/
function IAM_XLS($filename = 'spreadsheet')
{
$this->xls_data = "";
$this->xlsName = $filename;
$this->_excelStart();
}

/**
*
* @desc Writes a value to a cell in the in-memory file
* @access public
* @param int $xls_line Spreadsheet row (zero-based)
* @param int $xls_col Spreadsheet column (zero-based)
* @param mixed $value Cell value (String or Numeric)
*/
function WriteValue($xls_row, $xls_col, $value)
{
if (is_numeric($value))
$this->WriteCellNumber($xls_row, $xls_col, $value);
else
$this->WriteCellText($xls_row, $xls_col, $value);
}

/**
*
* @desc Generates a XLS File from an SQL Query (and outputs it to the browser)
* @access public
* @param String $query Query String
* @param String $db Name of the Database
* @param String $user User to Access the Database
* @param String $pass Password to Access the Database
* @param String $host Name of the Host holding the DB
*/
function WriteSQLDump($query, $db='', $user = '', $pass = '', $host = '')
{
$xls_line = 0;
$col = 0;

$link = $this->_db_connect($db, $user, $pass, $host);
if ($link) {
$result = @mysql_query($query, $link);
if (mysql_error() != "") {
$this->WriteValue(1, 0, "Ha ocurrido un error al consultar la base de datos: " . mysql_error());
// $this->OutputFile();
$this->savexl();
exit();
}

$lines = @mysql_num_rows($result);
$colums = mysql_num_fields($result);

for($e = 0; $e < $colums; $e++)
$this->WriteValue(0, $e, trim(ucwords(str_replace("_", " ", mysql_field_name($result, $e)))));

for($col = 0; $col < $colums; $col++) {
$col_name = mysql_field_name($result, $col);

for($i = 0; $i < $lines; $i++) {
$CellValue = mysql_result($result, $i, $col_name);

$xls_line = ($i + 1);

$this->WriteValue($xls_line, $col, $CellValue);
}
}
} else {
$this->WriteValue(1, 0, "Ha ocurrido un error al conectarse a la base de datos: " . mysql_error());
}
//$this->OutputFile();
$this->savexl();
}

/**
*
* @desc Closes the XLS File and Sends it to the browser
* @access public
*/
function OutputFile()
{
$this->_excelEnd();

$now = gmdate('D, d M Y H:i:s') . ' GMT';
$USER_BROWSER_AGENT = $this->_get_browser_type();

header('Content-Type: ' . $this->_get_mime_type());
header ("Content-Description: IAM Generated Excel File");
header('Expires: ' . $now);

if ($USER_BROWSER_AGENT == 'IE') {
header('Content-Disposition: attachment; filename="' . $this->xlsName . ".xls");
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
} else {
header('Content-Disposition: attachment; filename="' . $this->xlsName . ".xls");
header('Pragma: no-cache');
}

print ($this->xls_data);
}
function savexl(){
$this->_excelEnd();
if ( !stristr( $this->xlsName, ".xls" ) ) {
$this->xlsName .= ".xls";
}
$fp = fopen( $this->xlsName, "wb" );
if(fwrite( $fp, $this->xls_data ))
echo "File written successfully";
else echo "Can't write.";

fclose( $fp );
}

/**
*
* @desc Writes The XLS Header to the in-memory file
* @access private
*/
function _excelStart()
{
$this->xls_data = pack("vvvvvv", 0x809, 0x08, 0x00, 0x10, 0x0, 0x0);
}

/**
*
* @desc Writes The XLS End-of-File sequence to the in-memory file
* @access private
*/
function _excelEnd()
{
$this->xls_data .= pack("vv", 0x0A, 0x00);
if ( $this->header ) {
$this->Header();
}
}

/**
*
* @desc Writes a numeric value to a cell in the in-memory file
* @access public
* @param int $xls_row Spreadsheet row (zero-based)
* @param int $xls_col Spreadsheet column (zero-based)
* @param float $value Cell value
*/
function WriteCellNumber($xls_row, $xls_col, $value)
{
settype($value, 'float');
settype($row, 'integer');
settype($col, 'integer');

$this->xls_data .= pack("sssss", 0x0203, 14, $xls_row, $xls_col, 0x00);
$this->xls_data .= pack("d", $value);
}

/**
*
* @desc Writes a string value to a cell in the in-memory file
* @access public
* @param int $xls_row Spreadsheet row (zero-based)
* @param int $xls_col Spreadsheet column (zero-based)
* @param float $value Cell value
*/
/**
* Error handling for long strings, added by Robin Newman
*/
function WriteCellText($xls_row, $xls_col, $value)
{
settype($value, 'string');
settype($row, 'integer');
settype($col, 'integer');

$len = strlen($value);
if ($len > 255) {
$value = "#STRING TOO LONG:" . $len;
$len = strlen($value);
}
$this->xls_data .= pack("s*", 0x0204, 8 + $len, $xls_row, $xls_col, 0x00, $len);
$this->xls_data .= $value;
}

/**
*
* @desc Connects to a MYSQL Server and select the given Database
* @access private
* @param String $dbname Name of the Database
* @param String $user User to Access the Database
* @param String $password Password to Access the Database
* @param String $host Name of the Host holding the DB
* @return resource if connection was successful | FALSE
*/
function _db_connect($dbname = "test", $user = "root", $password = "", $host = "localhost")
{
$result = @mysql_pconnect($host, $user, $password);
if (!$result) { // If no connection, return 0
return false;
}

if (!@mysql_select_db($dbname)) { // If db not set, return 0
return false;
}
return $result;
}

/**
*
* @desc Define the client's browser type
* @access private
* @return String A String containing the Browser's type or brand
*/
function _get_browser_type()
{
$USER_BROWSER_AGENT = "";

if (ereg('OPERA(/| )([0-9].[0-9]{1,2})', strtoupper($_SERVER["HTTP_USER_AGENT"]), $log_version)) {
$USER_BROWSER_AGENT = 'OPERA';
} else if (ereg('MSIE ([0-9].[0-9]{1,2})', strtoupper($_SERVER["HTTP_USER_AGENT"]), $log_version)) {
$USER_BROWSER_AGENT = 'IE';
} else if (ereg('OMNIWEB/([0-9].[0-9]{1,2})', strtoupper($_SERVER["HTTP_USER_AGENT"]), $log_version)) {
$USER_BROWSER_AGENT = 'OMNIWEB';
} else if (ereg('MOZILLA/([0-9].[0-9]{1,2})', strtoupper($_SERVER["HTTP_USER_AGENT"]), $log_version)) {
$USER_BROWSER_AGENT = 'MOZILLA';
} else if (ereg('KONQUEROR/([0-9].[0-9]{1,2})', strtoupper($_SERVER["HTTP_USER_AGENT"]), $log_version)) {
$USER_BROWSER_AGENT = 'KONQUEROR';
} else {
$USER_BROWSER_AGENT = 'OTHER';
}

return $USER_BROWSER_AGENT;
}

/**
*
* @desc Define MIME-TYPE according to target Browser
* @access private
* @return String A string containing the MIME-TYPE String corresponding to the client's browser
*/
function _get_mime_type()
{
$USER_BROWSER_AGENT = $this->_get_browser_type();

$mime_type = ($USER_BROWSER_AGENT == 'IE' || $USER_BROWSER_AGENT == 'OPERA')
? 'application/octetstream'
: 'application/octet-stream';
return $mime_type;
}

function Header( $text="" ) {
if ( $text == "" ) {
$text = "This file was generated using PSXlsGen at ".date("D, d M Y H:i:s T");
}
if ( $this->totalcol < 1 ) {
$this->totalcol = 1;
}
$this->InsertText( $text );
$this->crow += 2;
$this->ccol = 0;
}

// insert a number, increment row,col automatically
function InsertText( $value )
{
if ( $this->ccol == $this->totalcol ) {
$this->ccol = 0;
$this->crow++;
}
$this->WriteText_pos( $this->crow, $this->ccol, &$value );
$this->ccol++;
return;
}

// write a label (text) into Row, Col
function WriteText_pos( $row, $col, $value )
{
$len = strlen( $value );
$this->xls_data .= $value;
return;
}
}

?>

And here an example file name example.php

<?php
require("iam_xls.php");

$query = "SELECT * FROM tablename";

$filename = date('Y-m-d');
$mid_excel = new IAM_XLS($filename);

$mid_excel->WriteSQLDump($query, 'test', 'root', '', 'localhost'); //query,dbname,username,password,servername
?>

Finally enjoy!

Advertisements

One thought on “Create XL file from mysql table

  1. Pingback: mime type error

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s