×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Export data to spreadsheet

Export data to spreadsheet

Export data to spreadsheet

(OP)
I found a script online to export my data to excel but when it opens the spreadsheet is blank. I have modified the top of the script to get the data that I need and the array is ok. This is the first time I am exporting data to a ss and am having a little trouble finding the issue. Is there an error in the script? Below is the script that is supposed to export data...

CODE --> php

function filterData(&$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 = "invoices.xls";
    
// headers for download
header("Content-Disposition: attachment; filename=\"$fileName\"");
header("Content-Type: application/vnd.ms-excel");
    
$flag = false;
foreach($data as $row) {
   if(!$flag) {
      // display column names as first row
      echo implode("\t", array_keys($row)) . "\n";
      $flag = true;
   }
      
   // filter data
   array_walk($row, 'filterData');
   echo implode("\t", array_values($row)) . "\n";
} 

RE: Export data to spreadsheet

Hi

Works for me. How is your $data looking ? How gets that code run ?

Feherke.
feherke.github.io

RE: Export data to spreadsheet

This is just generating a tab-delimited file, not XLS, it relies on Excel detecting that and importing it, which can work in some systems, not in others. That can depend on regional settings, for example, which define a list separator character (that can be comma or semicolon or tab) and Excel settings (depending on Excel version).

It would be more to the point to give such output the CSV extension and generate comma separated values (CSV) files, at least CSV extension typiclly will be associated with Excel, too and you're giving the file an extnsion truly reflecting its nature.

CODE

function filterData(&$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 = "invoices.csv";
    
// headers for download
header("Content-Disposition: attachment; filename=\"$fileName\"");
header("Content-Type: plain/text");
    
$flag = false;
foreach($data as $row) {
   if(!$flag) {
      // display column names as first row
      echo implode(",", array_keys($row)) . "\n";
      $flag = true;
   }
      
   // filter data
   array_walk($row, 'filterData');
   echo implode(",", array_values($row)) . "\n";
} 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Export data to spreadsheet

By the way, to me it does not work out, too indeed. Just using Excel 2007. Relying on Excel to pick up tab delimited value and create a sheet from that is not a good idea, even if it works for some users.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Export data to spreadsheet

Hi

One small note : if you change the separator in implode(), you should update filterData() accordingly. Though using PHP's own fputcsv() may be a better choice.

Feherke.
feherke.github.io

RE: Export data to spreadsheet

(OP)
I took Olaf's advice and made it a .csv with the code changes. The data is there with interesting results. And I should mention that I am running Linux Mint 17 which might explain why the SS was blank. Now I am getting css code in the csv file and I have customers with a comma in the name that then moves the rest of the data over 1 cell. In the below example the '7' is in its own column.

What it should be...
A client = MyCompany
B invoice = 1807121
C customer = MyCustomer, LLC
D addr = 18-20 E Lakeview Lane
E city = Chicago
F items = 7

What I get is ...
A client = MyCompany
B invoice = 1807121
C customer = MyCustomer,
D addr = LLC
E city = 18-20 E Lakeview Lane
F items = Chicago
G 7


<style type="text/css">
table {border-collapse: collapse }
th td {padding:3px }
</style>client invoice customer addr city items
MyCompany 1807121 MyCustomer, LLC 18-20 E Lakeview Lane Chicago 7

RE: Export data to spreadsheet

CSS doesn't come from that script, so look what you include and don't do that.
Besides, comma separated values have that disadvantage, but the standard is to enclose any textual values with double quotes, then commas within data don't mean a separation and you don't have that shift.

Using fputcsv, as feherke suggests, will do that. actuall will only enclose text values containing commas. Disadvantage you really create the file just to read it an echo it as response. You could also do a header redirect, but then have no trigger when to remove the file again.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Export data to spreadsheet

Hi

Quote (Olaf)

Disadvantage you really create the file just to read it an echo it as response.
One small note : fputcsv()'s 1st parameter has to be resource and php:// wrappers are also fine.

See MagicalTux's comment in the PHP documentation :

CODE --> PHP

<?php
$out = fopen('php://output', 'w');
fputcsv($out, array('this', 'is some', 'csv "stuff", you know.'));
fclose($out); 

Feherke.
feherke.github.io

RE: Export data to spreadsheet

OK, then it should be

CODE --> PHP

// create $data (array of row arrays) from sql query

// file name for download
$fileName = "invoices.csv";
    
// headers for download
header("Content-Disposition: attachment; filename=\"$fileName\"");
header("Content-Type: plain/text");

$h = fopen('php//output','w');
foreach($data as $row) fputcsv($h,$row);
fclose($h); 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Export data to spreadsheet

(OP)
I used the code Olaf posted above and there is no data that is opening in the file.

RE: Export data to spreadsheet

The code doesn't provide data, that's your job:

CODE

// create $data (array of row arrays) from sql query 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Export data to spreadsheet

To give you a closed example, this producs a header + 1 row of data csv:

CODE

<?php 
$data[] = Array('Salutation','Addressee');
$data[] = Array('Hello', 'world');
    
// file name for download
$fileName = "helloworld.csv";
    
// headers for download
header("Content-Disposition: attachment; filename=\"$fileName\"");
header("Content-Type: plain/text");

$h = fopen('php://output','w');
foreach($data as $row) fputcsv($h,$row);
fclose($h);

?> 

If you want to output your query result fetched as an associative array (keys=column names/headers) you could use this to make column names the CSV headers:

CODE

<?php 
$db = 'yourdbname';
$user = 'yourdbuser';
$pass =	'yourpassword';
$host = 'localhost';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$dbh = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass, $options);
$sth = $dbh->query("SELECT * FROM yourtable"); && maybe also a prepared statement with parameters...
$data = $sth->fetchAll();
  
// file name for download
$fileName = "data.csv";
    
// headers for download
header("Content-Disposition: attachment; filename=\"$fileName\"");
header("Content-Type: plain/text");

$h = fopen('php://output','w');
fputcsv($h,array_keys($data[0])); && headers
foreach($data as $row) fputcsv($h,$row); && data
fclose($h);
?> 

This is tested (of course you need to put in your database credentials and query), the code posted earlier had the : in "php://output" missing, but you could have fixed that reading what Feherke posted.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Export data to spreadsheet

(OP)
I got it working. Thanks for all the help!!!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close