PHP: Exporting Data to Excel - CSS-Tricks

This topic is empty.

Viewing 4 posts - 1 through 4 (of 4 total)

Author

Posts
June 12, 2014 at 12:58 am #172508
dnyanesh
Participant
Hello
i want to export data in to excel. i have the code but it is downloading empty file.
please help me to down load with data from database
here is the code
```
&lt;?php
    $con = mysql_connect("localhost","root","");
    mysql_select_db("audit_db");


    $sql = "select * from issues";
    $qur = mysql_query($sql);

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


    $filename = "AuditReport.xls"; 
    header("Content-Type: text/plain");
    header("Content-Disposition: attachment; filename=\"$filename\""); 
    header("Content-Type: application/vnd.ms-excel;charset=UTF-16LE");

$flag = false;
while($row = mysql_fetch_assoc($qur)) 
{   
    echo "Hello";
    if(!$flag)
    {
        echo implode("\t", array_keys($row)) . "\r\n";
        $flag = true;
    }
        array_walk($row, 'cleanData');
        echo implode("\t", array_values($row)) . "\r\n";
}
        exit;
?&gt;
```
June 12, 2014 at 9:02 am #172613

__
Participant

@chriscoyier, your “Securi Website Firewall” is misinterpreting my post as an SQL injection attack and blocking it. I think it’s because I used the words “select”, “into”, and “outfile” consecutively.

If the output is empty, the two most likely reasons are (1) the query returned no results, or (2) there was an error (e.g., if mysql_query failed, then $qur would be false and trying to fetch from it would cause a fatal error; your browser would have already received the download headers, and, if errors were configured not to display, you would end up with a “blank” file).

Before you spend too much time on this, however, you should consider using a select ... into out file query.*

* “out file” should be one word, i.e., “outfile.” the css-tricks website is giving me a hard time about this. go read the mysql manual page for more info.

Before doing that, if you are doing this on a local machine (i.e., you don’t need to do it over a web server), you should use mysqldump from the cli.

June 14, 2014 at 11:15 am #172784

Chris Coyier
Keymaster

your “Securi Website Firewall” is misinterpreting my post as an SQL injection attack and blocking it.

That’s a bummer that it blocked you, but it’s been a good thing to have in place for the most part. If it happens again, maybe just dump it in a Gist and paste link to it.

June 16, 2014 at 9:03 pm #172913
dnyanesh
Participant
Good morning,

i could complete my task.
thanks for help.

I have saved the data from database in to excel using PHP code. now i want to send that file from the specific folder location which i am giving in code.
i have code. code get the same file from location even send a mail as well but file dont have content. however the file resides in audit folder have the data.

here is the code.

please help me out.

<html>
<head>
<title>Sending attachment using PHP</title>
</head>
<body>
<?php
$to = “[email protected]”;
$subject = “Audit_report”;
$message = “Hello All, PFA audit report .”;
# Open a file
$file = fopen( “G:/Smart solution/Audit/Audit_report.csv”, “r” );
if( $file == false )
{
echo “Error in opening file”;
exit();
}
# Read the file into a variable
$size = filesize(“G:/Smart solution/Audit/Audit_report.csv”);
$content = fread( $file, $size);

# encode the data for safe transit
# and insert \r\n after every 76 chars.
$encoded_content = chunk_split( base64_encode($content));

# Get a random 32 bit number using time() as seed.
$num = md5( time() );

# Define the main headers.
$header = “From:[email protected]\r\n”;
$header .= “MIME-Version: 1.0\r\n”;
$header .= “Content-Type: multipart/mixed; “;
$header .= “boundary=$num\r\n”;
$header .= “–$num\r\n”;

# Define the message section
$header .= “Content-Type: text/plain\r\n”;
$header .= “Content-Transfer-Encoding:8bit\r\n\n”;
$header .= “$message\r\n”;
$header .= “–$num\r\n”;
```
ini_set("SMTP","localhost");
ini_set("smtp_port","25");
ini_set("sendmail_from","[email protected]");
ini_set("sendmail_path", "C:\wamp\bin\sendmail.exe -t");    
```
# Define the attachment section
$header .= “Content-Type: multipart/mixed; “;
$header .= “name=\”test.csv\”\r\n”;
$header .= “Content-Transfer-Encoding:base64\r\n”;
$header .= “Content-Disposition:attachment; “;
$header .= “filename=\”Audit_report.csv\”\r\n\n”;
$header .= “$encoded_content\r\n”;
$header .= “–$num–“;

# Send email now
$retval = mail ( $to,$subject,””,$header);
if( $retval == true )
{
echo “Message sent successfully…”;
}
else
{
echo “Message could not be sent…”;
}
?>
</body>
</html>
<?php header(‘location:issues.php’);
?>

Thanks in advance
Author

Posts

Viewing 4 posts - 1 through 4 (of 4 total)

The forum ‘Back End’ is closed to new topics and replies.