current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1

Hey guys I have this php application that i developing and i would like it to export the data correctly to the excel document such that each column header is exported with all the data in that column. This is what i have so far but its not working correctly as all the columns are shown in one row. How can i be able to achieve what i would like? Someone please assist me. This is the current implementation:

<?php require_once('connections/pgconn.php');


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

// filename for download
$filename = "website_data_" . date('Ymd') . ".xls";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");

$flag = false;
$result = pg_query("SELECT to_char (a.CALLDATE,'yyyymm') as month,min(a.calldate) as      start_time,max(a.calldate) as end_time,
     ceil(SUM (a.CALLDURATION::integer) / 60) AS minutes,
     COUNT (DISTINCT a.IDENTIFIANT) AS distinct_callers,
a.zoneiddest as country_code,b.country
FROM cdr_data a,COUNTRY_CODES b
WHERE  a.CALLSUBCLASS = '002'
     AND  a.CALLCLASS = '008'
and a.zoneiddest::integer > 0
AND SUBSTR (a.CALLEDNUMBER, 1, 2) NOT IN
('77', '78', '75', '70', '71', '41', '31', '39', '76','79')

and not substr(a.zoneiddest , 1 ,3) in ('254','255','256','211','257','250','256')
and trim(a.zoneiddest)  = trim(b.country_code)
GROUP BY to_char (a.CALLDATE,'yyyymm') ,a.zoneiddest,b.country
ORDER BY 1") or die('Query failed!');

while(false !== ($row = pg_fetch_assoc($result))) {
if(!$flag) {
  // display field/column names as first row
  echo implode("\t", array_keys($row)) . "\r\n";
  $flag = true;
}
array_walk($row, 'cleanData');
echo implode("\t", array_values($row)) . "\r\n";
}
exit;
?>
share|improve this question
    
Do you actually want an Excel file, or a simple CSV file? –  Mark Baker Jan 15 '13 at 13:34
    
I want an excel file strictly –  roykasa Jan 15 '13 at 13:53
1  
Take a look at some of the libraries for writing Excel files such as PHPExcel (phpexcel.codeplex.com) or those listed in stackoverflow.com/questions/3930975/alternative-for-php-excel to see what they can do –  Mark Baker Jan 15 '13 at 14:02
    
Let me check them out. I didnt want to go so off truck though from what i have been doing with the code up there. –  roykasa Jan 15 '13 at 14:25
    
Your existing code will remain largely unchanged - but rather than echoing with implode, you set cell values instead –  Mark Baker Jan 15 '13 at 14:36

1 Answer 1

up vote 1 down vote

What I would suggest is to export your query as CSV telling PHP to create a HTTP response as "application/ms-excel" content type so IE will automatically launch Excel that will import CSV in columns with column names:

$query = "COPY (SELECT .... ) TO '/path/reachable/with/php/file.csv' CSV;"

This way, you save the CSV as a file on the server and retrieve it as many time as you want in PHP.

$file = file_get_contents('/path/reachable/with/php/file.csv');
header('Content-Type: application/ms-excel');
echo $file;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.