Export data to spreadsheet 1

[Marine1969]

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...

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";
}

 

[feherke]

Hi

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


Feherke.
feherke.github.io

 

[Olaf Doschke]

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.

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

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

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

 

[feherke]

Hi

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


Feherke.
feherke.github.io

 

[Marine1969]

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

 

[Olaf Doschke]

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

 

[feherke]

Hi

Disadvantage you really create the file just to read it an echo it as response.

One small note : [tt]fputcsv()[/tt]'s 1[sup]st[/sup] parameter has to be resource and [tt]php://[/tt] wrappers are also fine.

See MagicalTux's comment in the PHP documentation :

[teal]<?php[/teal]
[navy]$out[/navy] [teal]=[/teal] [COLOR=orange]fopen[/color][teal]([/teal][i][green]'php://output'[/green][/i][teal],[/teal] [i][green]'w'[/green][/i][teal]);[/teal]
[COLOR=orange]fputcsv[/color][teal]([/teal][navy]$out[/navy][teal],[/teal] [b]array[/b][teal]([/teal][i][green]'this'[/green][/i][teal],[/teal] [i][green]'is some'[/green][/i][teal],[/teal] [i][green]'csv "stuff", you know.'[/green][/i][teal]));[/teal]
[COLOR=orange]fclose[/color][teal]([/teal][navy]$out[/navy][teal]);[/teal]

Feherke.
feherke.github.io

 

[Olaf Doschke]

OK, then it should be

// 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

 

[Marine1969]

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

 

[Olaf Doschke]

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

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

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

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

<?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:

<?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

 

[Marine1969]

I got it working. Thanks for all the help!!!!!