How to write an PHP array into xls file?

Question

My data looks like this (After clicking View Page Source),

date \t new_users \t cpm \t earnings \t total_pushes \t avail_users \t application \t 
 2011-03-31  \t  336933  \t  $5.27  \t  $10151.2  \t  1925244  \t  1905744  \t  Play Movies  \t 
 2011-03-31  \t  205070  \t  $5.12  \t  $5810.26  \t  1134946  \t  1118354  \t  Watch Movies or TV Shows  \t

I imploding given array with '\t' and then dynamically writing into xls file .Problem with this is all of the contents are present only in the first column of the xls file .

What am i missing in the code,does xls file requires data format ?

\t should not be visble, you should see a [tab]. Also, this wont be valid XLS, but a TSV file. But maybe if you show some more code we could help more. — Derk Arts
– Derk Arts, Commented Dec 21, 2011 at 19:11
@saratis is right. Check your code to see if you use double quotes or single quotes around the \t. It should be "double quotes". — Brigand
– Brigand, Commented Dec 21, 2011 at 19:24
$glue = '\t'; $h = array( 'date' , 'news' , 'cpm' , 'earnings' , 'pushes' , 'count' , 'app'); $top = implode("$glue",$h)."\n"; $d = array( '0'=> array('2011','3' ,'5.2','10151.2', '1925244','1905744','Play Movies'), '1'=> array('2011','2 ' ,'35.2', '10151.2', '1134946 ','1118354 ','Watch Movies or TV Shows') ); $f_c= ''; foreach ($d as $ed){ $cs = null; $cs = implode("$glue" ,array_values($ed)); $f_conts .= $s. "\n"; } $f_cs =$top.$f_c; $f = 's.xls'; header("Content-type: application/vnd.ms-excel"); header("Content-disposition: filename=".$f); print_r( $fn ) — caspersky 48
– caspersky 48, Commented Dec 21, 2011 at 19:51

Evilnode · Accepted Answer · 2011-12-21 19:25:48Z

3

You should probably use fputcsv to build your file. You dont need to worry about setting the delimiter. Then you should rename the file to something.xls.

If you're processing this from a web request, add the following header: application/vnd.ms-excel

<?php
// create header array here...$myHeaders
// create data array here... $myData

$csvName = 'temp.csv';

$fp = fopen($csvName, 'w');
fputcsv($fp, $myHeaders);
foreach ($myData as $line) {
    fputcsv($fp, $line);
}
fclose($fp);

// now send to browser if this is a web request
$csvData = file_get_contents($csvName);
header('Content-Type: application/vnd.ms-excel');
header('Content-Length: ' . strlen($csvData));
echo $csvData;
exit;

answered Dec 21, 2011 at 19:25

Evilnode

5234 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PazsitZ · Accepted Answer · 2011-12-21 19:23:00Z

2

For generating valid xml you can use f.e. the PHPExcel

If you statisfied with simplier format, you can just generate csv file format. This is good enough for open or MS office. For csv put the built content simply into a file, or use the fputcsv function

answered Dec 21, 2011 at 19:23

PazsitZ

1313 bronze badges

Comments

Wilbo Baggins · Accepted Answer · 2011-12-21 19:22:13Z

It looks like you are not trying to write XLS but CSV (comma-separated value) format. If that's the case, replace \t with , (or ;) and wrap all value fields (not the column titles) in double quotes (").

Excel should handle CSV files formatted like that without a problem.

If you really want to generate an XLS file, try generating an HTML table with your data in it. Put the HTML code for that table into a single variable and use the following code to download it as an XLS-file automatically:

// Just some example data in a HTML table
$htmlcontent = "<table><tr><th>col1</th><th>col2</th></tr><tr><td>val1-1</td><td>val1-2</td></tr></table>";

// Create as XLS file and send to browser for download
$filename ="data.xls";
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
echo $htmlcontent;

That way, your users can download the content as an XLS file that Excel should be able to handle just fine.

(PS; you can try using the \t-separated data as file contents instead of an HTML table, that may work, but I'm not sure so give it a go.)

Collectives™ on Stack Overflow

How to write an PHP array into xls file?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related