PDA

View Full Version : Export MySQL data to Excel in PHP



netfrugal
01-29-2006, 06:59 PM
I've been able to export any data from MySQL to a csv file. But I've been having problems with stripping out all html code, and whenever there is a comma in the data, it sends all data to the next cell in excel. I included a str_replace function, but that is still not entirely working.

Let me show you what I've already done.



<?php if (!$HTTP_GET_VARS['submit']) { ?>

<?php
echo "Export and Save Customer Data onto your Local Machine";
echo '<form action="'. $phpself.'">';
echo '<input type="submit" value="Export" name="submit"></form>';
?>

<?php
}
else
{
$contents="Board Category,Questions,Answers\n";
$user_query = mysql_query('select
f.faqdesk_id
, c.categories_id
, f.faqdesk_question
, c.categories_name
, f.faqdesk_answer_short
from faqdesk_description as f
join faqdesk_to_categories as f2c
on f.faqdesk_id = f2c.faqdesk_id
join faqdesk_categories_description as c
on f2c.categories_id = c.categories_id');
while($row = mysql_fetch_array($user_query))
{
$contents.=$row[categories_name].",";
$contents.=$row[faqdesk_question].",";
$answer = str_replace(',', '\,', $row[faqdesk_answer_short]); // escape internalt commas
$contents.=$answer."\n";
}
$contents = strip_tags($contents); // remove html and php tags etc.
Header("Content-Disposition: attachment; filename=export.csv");
print $contents;

?>
It stipped out html tags, but it is still leaving in &nbsp;

I'm sure there is a fix or replacement function for that. However, the commas are still giving separation problems, even with the str_replace function.

You can see the excel results here: www.mastertheboard.com/board_topic_export3.php

It is a list of questions and answers for the military soldiers in my units.
There is also some other formmating issues having to do with numbered bullets. But I'm not sure how that is fixed.
Any ideas?

IeD3vil
02-09-2006, 08:00 PM
$text = str_replace("&nbsp;"," ",$text);

that will replace all &nbsp; in $text to space.

codeexploiter
03-06-2007, 06:10 AM
<?php
class database
{
private $db_handle;
private $user_name;
private $password;
private $data_base;
private $host_name;
private $sql;
private $results;

function __construct($host="localhost",$user,$passwd)
{
$this->db_handle = mysql_connect($host,$user,$passwd);
}

function dbSelect($db)
{
$this->data_base = $db;
if(!mysql_select_db($this->data_base, $this->db_handle))
{
error_log(mysql_error(), 3, "/phplog.err");
die("Error connecting to Database");
}
}

function executeSql($sql_stmt)
{
$this->sql = $sql_stmt;
$this->result = mysql_query($this->sql);
}
function returnResults()
{
return $this->result;
}
}

$user = "root";
$passwd = "";
$db = "newTest";
$sql = "SELECT * FROM EMP ORDER BY empname";

$dbObject = new database($host,$user,$passwd);
$dbObject->dbSelect($db);
$dbObject->executeSql($sql);


$res = $dbObject->returnResults();

$newFileName = "emp_names.csv";

$fpWrite = fopen("C:\\$newFileName", "w");

$nameStr = "";

while($record = mysql_fetch_object($res))
{
$name = $record->empname;

$nameArray = explode(",",$name);

if(count($nameArray) > 1)
{
$nameTemp = "";
for($i=0;$i < count($nameArray); $i++)
{
$nameTemp = $nameTemp . $nameArray[$i];

if($i != (count($nameArray) - 1))
$nameTemp = $nameTemp . "&sbquo;";
}
$name = $nameTemp;
}

$nameStr = $nameStr.$name.",";
}


fwrite($fpWrite,$nameStr);

echo "File operation has been completed successfully!<br><br>";
?>


Try the above code it needs optimization but the core thing works that mean it retrieves database fields from the DB and insert (in this case as only one record you can change it accoridng to your needs I just want to show how we can keep , within a db field)

alansam007
12-24-2008, 05:00 AM
I want to design a site with form being send to my e-mail account and i want to be creating a profile for the visitor that send me an e-mail through the form....I was think of using MS Access as a Database since is just for 10-15 profile.

I want the MS Access to stand as the Database the will be check their username and password whether is correct...before they can log in into there profiles.

I was also looking for a script that i can use as a progress report which will look likes a plug in when they click an Hyper link in there profile(The script should display in percentage).

Thanks

huzefa
05-10-2009, 03:35 PM
Hiiii,

I want to export mysql data to excel file with php

There are the things i want :
1. It should display the table on a page and we will be able to select the field with a radio button.
2. Then the selected fields should get exported to a excel file when we click on the submit button.
3. On the top of the excel file, it should display the field names and below it should show all the records present in the table.

Does anybody know how to do this ........i will be thankful if anybody will put the source code for this ...i need it ...its urgent.

itw
07-30-2010, 02:37 PM
i have one drop down list which is dynamically generate reports.can someone help me out to generate excel report based on that drop down list?Thanks in advance.

muagibando.com
11-15-2010, 11:58 AM
This is class export very good UTF-8 encoding. I know it when i working
1. File load data


<?php

// load library
require 'php-excel.class.php';

// create a simple 2-dimensional array
$data = array(
1 => array ('Name', 'Surname'),
array('Schwarz', 'Oliver'),
array('Test', 'Peter')
);

// generate file (constructor parameters are optional)
$xls = new Excel_XML('UTF-8', false, 'Workflow Management');
$xls->addArray($data);
$xls->generateXML('Output_Report_WFM');

?>

2.File Library. Create name of it is : php-excel.class.php


<?php

/**
* Simple excel generating from PHP5
*
* @package Utilities
* @license http://www.opensource.org/licenses/mit-license.php
* @author Oliver Schwarz <oliver.schwarz@gmail.com>
* @version 1.0
*/

/**
* Generating excel documents on-the-fly from PHP5
*
* Uses the excel XML-specification to generate a native
* XML document, readable/processable by excel.
*
* @package Utilities
* @subpackage Excel
* @author Oliver Schwarz <oliver.schwarz@vaicon.de>
* @version 1.1
*
* @todo Issue #4: Internet Explorer 7 does not work well with the given header
* @todo Add option to give out first line as header (bold text)
* @todo Add option to give out last line as footer (bold text)
* @todo Add option to write to file
*/
class Excel_XML
{

/**
* Header (of document)
* @var string
*/
private $header = "<?xml version=\"1.0\" encoding=\"%s\"?\>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">";

/**
* Footer (of document)
* @var string
*/
private $footer = "</Workbook>";

/**
* Lines to output in the excel document
* @var array
*/
private $lines = array();

/**
* Used encoding
* @var string
*/
private $sEncoding;

/**
* Convert variable types
* @var boolean
*/
private $bConvertTypes;

/**
* Worksheet title
* @var string
*/
private $sWorksheetTitle;

/**
* Constructor
*
* The constructor allows the setting of some additional
* parameters so that the library may be configured to
* one's needs.
*
* On converting types:
* When set to true, the library tries to identify the type of
* the variable value and set the field specification for Excel
* accordingly. Be careful with article numbers or postcodes
* starting with a '0' (zero)!
*
* @param string $sEncoding Encoding to be used (defaults to UTF-8)
* @param boolean $bConvertTypes Convert variables to field specification
* @param string $sWorksheetTitle Title for the worksheet
*/
public function __construct($sEncoding = 'UTF-8', $bConvertTypes = false, $sWorksheetTitle = 'Table1')
{
$this->bConvertTypes = $bConvertTypes;
$this->setEncoding($sEncoding);
$this->setWorksheetTitle($sWorksheetTitle);
}

/**
* Set encoding
* @param string Encoding type to set
*/
public function setEncoding($sEncoding)
{
$this->sEncoding = $sEncoding;
}

/**
* Set worksheet title
*
* Strips out not allowed characters and trims the
* title to a maximum length of 31.
*
* @param string $title Title for worksheet
*/
public function setWorksheetTitle ($title)
{
$title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
$title = substr ($title, 0, 31);
$this->sWorksheetTitle = $title;
}

/**
* Add row
*
* Adds a single row to the document. If set to true, self::bConvertTypes
* checks the type of variable and returns the specific field settings
* for the cell.
*
* @param array $array One-dimensional array with row content
*/
private function addRow ($array)
{
$cells = "";
foreach ($array as $k => $v):
$type = 'String';
if ($this->bConvertTypes === true && is_numeric($v)):
$type = 'Number';
endif;
$v = htmlentities($v, ENT_COMPAT, $this->sEncoding);
$cells .= "<Cell><Data ss:Type=\"$type\">" . $v . "</Data></Cell>\n";
endforeach;
$this->lines[] = "<Row>\n" . $cells . "</Row>\n";
}

/**
* Add an array to the document
* @param array 2-dimensional array
*/
public function addArray ($array)
{
foreach ($array as $k => $v)
$this->addRow ($v);
}


/**
* Generate the excel file
* @param string $filename Name of excel file to generate (...xls)
*/
public function generateXML ($filename = 'excel-export')
{
// correct/validate filename
$filename = preg_replace('/[^aA-zZ0-9\_\-]/', '', $filename);

// deliver header (as recommended in php manual)
header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");

// print out document to the browser
// need to use stripslashes for the damn ">"
echo stripslashes (sprintf($this->header, $this->sEncoding));
echo "\n<Worksheet ss:Name=\"" . $this->sWorksheetTitle . "\">\n<Table>\n";
foreach ($this->lines as $line)
echo $line;

echo "</Table>\n</Worksheet>\n";
echo $this->footer;
}

}

?>


[B]- Keep copyright when using it.

atheequ
02-11-2011, 05:00 AM
Same thing how can i do value from database(mysql).plz reply me.

Djordje
04-26-2011, 05:51 PM
HI. I know this thread is 1 year old.

I have a problem on this issue. I have a table with 20 columns and how do I put in array all these values​​? I try like this but don't work. help me? :(

$xls = new Excel_XML('UTF-8', false, 'Workflow Management');
while($samthing=DBarray($select))
{
$data=array($samthing['name'],$samthing['phon',....);
$xls->addArray($data);

}
$xls->generateXML('Output_Report_WFM');

acopetric
05-29-2012, 10:35 PM
The class is extra! I tested class and it's work fine.
If you want to export more columns from table in excel here is example:

<?php
require 'php-excel.class.php';
require 'konekcija.php';

$data[] = array('Oblast', 'Oznaka','Duzina');
$sql = mysql_query("SELECT OBLAST,OZNAKA,DUZINA FROM pravci") or die("died");
while($row = mysql_fetch_array($sql)){
$data[] = array($row['OBLAST'],$row['OZNAKA'],$row['DUZINA']);
}

// generate file (constructor parameters are optional)
$xls = new Excel_XML('UTF-8', false, 'Workflow Management');
$xls->addArray($data);
$xls->generateXML('Eksport_pravci');
?>