CODEWITHSUNDEEP
phpmysqlexcel
TheTub
TheTub

Reputation: 195

Export mySQL to excel or csv

I'm no php expert (a mere beginner) but need some help!

After hours searching Google and trying out about 100 different scripts, I finally found one that does what I need - almost.

Basically, my site has a button marked 'Export to Excel'. Visitor to site clicks button and a download begins containing all data from a specified table.

I found this on here - PHP code to convert a MySQL query to CSV

which does exactly what I want except the user sees the following error when trying to open the file:

Error - 'The file you are trying to open, 'export.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Wo you want to open the file now?'

User clicks 'Yes' and file opens with all data! Brilliant! Except users will not open the file with this error.

I would be very grateful if someone knows a way to fix this.

Many thanks

TT

Upvotes: 2

Views: 6275

Answers (6)

Islam El-Ghazali
Islam El-Ghazali

Reputation: 11

I had the same problem so I looked at the following link: PHP code to convert a MySQL query to CSV

I modified one of the answers to get the headers to work.

include('DBFILE.PHP'); 
$select="SELECT * FROM SOMETable";


$result = mysqli_query($conn, $select);
if (!$result) die('Couldn\'t fetch records');
$num_fields = mysql_num_fields($result);

//This is what I changed...
$headers ="";
while ($property = mysqli_fetch_field($result)) {
    $headers.= $property->name.",";
}
$headers.="\n";
//////

$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}

I Tested this and it works like a charm, you just need to add your db connection or include the db.php file that you have.

you can change the name of the file if you edit the following line

header('Content-Disposition: attachment; filename="export.csv"');

Change export to what ever name you like.

Upvotes: 0

kjdion84
kjdion84

Reputation: 10044

if you make the first letters “ID” of a text file Excel incorrectly assumes you are trying to open an SYLK file.

Meaning if the first row & column value is "ID", Excel will throw this warning. Just change it from "ID" to anything else.

Credit: http://alunr.com/excel-csv-import-returns-an-sylk-file-format-error/

Upvotes: 1

Sooraj Bathery
Sooraj Bathery

Reputation: 1

functions sendFile($filename,$content_type="application/ms-excel") { 
  header('Content-type: '.$content_type);  
  header('Content-disposition: Attachment; filename=' . $filename);  
  readfile($filename);   
}

Upvotes: 0

Sooraj Bathery
Sooraj Bathery

Reputation: 1

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Set objXL = New Excel.Application
'turn off excel warnings
objXL.DisplayAlerts = False
'Open the Workbook
Set objWkb = objXL.Workbooks.Open(fpath)

Upvotes: 0

JeffP
JeffP

Reputation: 1086

Or, you could just change the script in the above solution to return a file with the .csv extension. .csv files are associated with Excel, so they should open directly.

Upvotes: 5

Matthew Jones
Matthew Jones

Reputation: 26190

Ok, this results from a feature specified by Excel 2007 called Extension Hardening. You can turn it off, but that can only be done client-side. If you click "OK" or "Yes" the file should open anyway. Check this blog post for more info.

EDIT: What this means is that Excel is finding that the file is of a different type (say HTML or CSV) that what is specified by the file extension. Therefore Excel wants to warn you that this file is not what it says it is. Unless you are going to create native Excel files on the server then prompt the user to download them, there is no getting around this error except for each user to turn off Extension Hardening on their own computer.

Upvotes: 2

Related Questions