Welcome to WebmasterWorld Guest from 54.144.107.83

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

PHP with Excel

     
7:48 am on Apr 29, 2011 (gmt 0)

Full Member

5+ Year Member

joined:Dec 12, 2007
posts:233
votes: 0


Hi all,

I am using the next code to change database data into excel sheet.


<?php
function cleanData(&$str) {
$str = preg_replace("/\t/", "\\t", $str); $str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}


// file name for download
$filename = "OBR_Recettes" . time() . ".xls";
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");


$flag = false;


$result = mysqli_query($conn, $sql ) or die('Query failed !: '.mysqli_error($conn));



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

if(!$flag) {
// display field/column names as first row
echo implode("\t", array_keys($row)) . "\n";
$flag = true;
}
array_walk($row, 'cleanData');
echo implode("\t", array_values($row)) . "\n";
}
?>

$result is computed from $sql query.

Problem of this, it is giving a warning when file opens with Excel 2007 and i cannot customize it.

Now, i saw a very powerful Excel Class, called PHPExcel from www.codeplex.com. This one is really
very powerful. But the major drawback, i saw no usage example or tutorial for this. Does someone know how to start off in using this PHPExcel class? (Example or tuto)

Thanks in advance.
2:57 pm on Apr 29, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 12, 2005
posts:5966
votes: 0


Try to make is just a regular ASCII text file, and open it in excel when you download it. I believe this should prompt you to specify a separator character for the import. a CSV file is not in excel format by itself.
3:30 pm on Apr 29, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 30, 2006
posts:1377
votes: 51


x2 to eelixduppy

you can print plain text and html code (tables) to excel, just print out the right headers first and then go. The file will be downloaded and identified as excel, it will open the app and it will recognize the format and show it to you as excel file. I've done this with both ms office on windows and open office on linux with no problem.
3:36 pm on Apr 29, 2011 (gmt 0)

Full Member

5+ Year Member

joined:Dec 12, 2007
posts:233
votes: 0


So no one here has already used the class i mentionned?
3:12 pm on May 2, 2011 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


Not me, but I tend to write all my own code and classes ... which I think you will find more often than not with others in this forum.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members