Liquid_Inc
asked on
Problem with Export to Excel in PHP
I am having trouble with how the excel file looks once it gets exported. The exported excel file displays all the content in a single cell. In other words, all of the columns and rows in the MySQL table I am exporting just get dumped into a single excel cell. It seems that the "\t" and "\n" characters are simply being ignored during export. I am attaching the code I am using to generate the export. What am I doing wrong?
<?php
// Start output buffering.
ob_start();
// Initialize a session.
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Transitions Survey Database Export Page</title>
</head>
<body>
<?php
if (isset($_SESSION['adminid'])) {
// Connect to the database.
$select = "SELECT * FROM reginfo";
$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
$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 ) ) || ( $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=survey_results.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
} else {
echo "You are not authorized to view this page.";
}
?>
</body>
</html>
<?php // Flush the buffered output.
ob_flush();
?>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I will find it...
damn...i had done that before...OK...I am doing it with PEAR!
Yes, i did it with PEAR...
google for PEAR classes to include if you don't have one...
If you can't find, i will send it to you!
google for PEAR classes to include if you don't have one...
If you can't find, i will send it to you!
dummy.php:
<?
echo "<p><a href='./sql2excel.php'>Generate XLS file</a></p>";
?>
*************************
<?php
// Start output buffering.
include_once "./Spreadsheet/Excel/Writer.php";
// Initialize a session.
session_start();
?>
<html>
<head> Yeah! </head>
<body>
<?php
$_SESSION['adminid'] = "baran";
if (isset($_SESSION['adminid'])) {
// Connect to the database.
$dbConnLink = mysql_connect("localhost", "root", "root");
mysql_select_db(alumni, $dbConnLink);
$select = "SELECT * FROM alumni_year";
$export = mysql_query ($select);
/*
$xls =& new Spreadsheet_Excel_Writer("aaa.xls");
$xls->send("test.xls");
$format =& $xls->addFormat();
$format->setBold();
$format->setColor("blue");
$sheet =& $xls->addWorksheet('Test XLS');
$sheet->write(0, 0, 1, 0);
$sheet->write(0, 1, 2, 0);
$sheet->writeString(1, 0, "XAMPP:", 0);
$sheet->writeString(1, 1, 12, $format);
$xls->close();
echo "ok";
*/
$rowCount = mysql_num_rows($export);
$xls =& new Spreadsheet_Excel_Writer("fuck.xls");
//$xls->send("fucking.xls");
$format =& $xls->addFormat();
$format->setBold();
$format->setColor("blue");
$sheet =& $xls->addWorksheet('SQL to Fucking XLS');
$i=0;
while ($row = mysql_fetch_assoc($export)){
$sheet->write(0, 0, "ID", 0);
$sheet->write(0, 1, "Year", 0);
$sheet->writeString($i, 0, $row['ID'] , 0);
$sheet->writeString($i, 1, $row['year'], $format);
$i++;
}
$xls->close();
}
else {
echo "You are not authorized to view this page.";
}
?>
</body>
</html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I scanned through your code once. I don't know what went wrong, but try to look at my working code below. This should give you some idea what to modify in order to make it working.
$csv_output = 'Last Name, First Name, User Name, Professor, Score, Submitted, ';
$num_questions = getTotalQuestions();
for ($i = 0; $i < $num_questions; $i++)
{
$csv_output .= ($i+1);
if ($i != $num_questions - 1)
{
$csv_output .= ',';
}
}
$csv_output .= "\015\012";
$result = mysql_query("select * from students");
while($row = mysql_fetch_array($result)) {
$csv_output .= "$row[last_name],$row[first_name],$row[user_name],".getProfLastName($row[prof_id]).",$row[score],$row[last_submitted],$row[answers]";
$csv_output .="\015\012";
}
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv; filename= Student_Data_". date("Y-m-d") . ".csv");
print $csv_output;
exit;
ASKER
Thank you! I finally have a working export to excel page :)
Open in new window