PHP Export to excel from MYSQL

Question

I'm trying to get my MySQL data to Excel file, but I'm having problems with Excel cells. All my text goes to one cell, I would like to have each row value in separate Excel cell. Here is my code:

    <html xmlns:o="urn:schemas-microsoft-com:office:office"xmlns:x="urn:schemas-microsoft-com:office:excel"xmlns="http://www.w3.org/TR/REC-html40">
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html>
    <head>
        <meta charset="UTF-8">
        <title>Daily Log</title>
    </head>
    <body>
    <?php
    ini_set('display_errors', 1);
    error_reporting(~0);
    $strKeyword = null;

    if (isset($_POST["txtKeyword"])) {
        $strKeyword = $_POST["txtKeyword"];
    }
    ?>
    <form name="frmSearch" method="post" action="<?php echo $_SERVER['SCRIPT_NAME'];?>">
        <table>
            <tr>
                <th>Date
                    <input name="txtKeyword" type="month" id="txtKeyword" value="<?php echo $strKeyword;?>">
                    <input type="submit" value="Search"></th>
                    <input type="submit" value="Export"></th>
            </tr>
            <tr>
                <th><label>Date</label></th>
                <th><label>Requested</label></th>
                <th><label>Requested Time</label></th>
                <th><label>Location</label></th>
                <th><label>Description</label></th>
                <th><label>Order By</label></th>
                <th><label>Completed by</label></th>
                <th><label>Completed Time</label></th>
            </tr>
            <tr><!--/*DESC ASP*/-->
                <?php include('config.php');
                $filename = "excelfilename";
                $strsql = "select * from log WHERE dateorder LIKE '%".$strKeyword."%'";
                $result = mysqli_query($objConnect, $strsql);
                $strExcelFileName="Member-All.xls";
                header("Content-Type: application/x-msexcel; name=\"$strExcelFileName\"");
                header("Content-Disposition: inline; filename=\"$strExcelFileName\"");
                header("Pragma:no-cache");
                while ($rs = mysqli_fetch_array($result,MYSQLI_ASSOC))
                {
                ?>
                <td><?php echo $rs['dateorder'] ?></td>
                <td><?php echo $rs['request'] ?></td>
                <td><?php echo date("H:i", strtotime($rs['requesttime'])) ?></td>
                <td><?php echo $rs['location'] ?></td>
                <td><?php echo $rs['description'] ?></td>
                <td><?php echo $rs['orderby'] ?></td>
                <td><?php echo $rs['completed'] ?></td>
                <td><?php echo date("H:i", strtotime($rs['completedtime'])) ?></td>
            </tr>
            <?php } ?>
        </table>
    </form>
    <script>
        window.onbeforeunload = function(){return false;};
        setTimeout(function(){window.close();}, 10000);
    </script>
    </body>
    </html>

I need Step 1 search button step 2 export button

Parfait · Accepted Answer · 2017-01-29 14:23:12Z

Consider separating the HTML output and Excel export using the same PHP array fetched from MySQL query. Integrate below pieces into your code.

Specifically, Excel requires tab-delimited text to map to cells with carriage and line breaks.

Database Fetch

...same db config...

$data = []
while ($rs = mysqli_fetch_array($result,MYSQLI_ASSOC)) {
    $data[] = $row; 
}

HTML Output

foreach ($data as $row) { ?>
 <tr>
     <td><?php echo $row['dateorder'] ?></td>
     <td><?php echo $row['request'] ?></td>
     <td><?php echo date("H:i", strtotime($row['requesttime'])) ?></td>
     <td><?php echo $row['location'] ?></td>
     <td><?php echo $row['description'] ?></td>
     <td><?php echo $row['orderby'] ?></td>
     <td><?php echo $row['completed'] ?></td>
     <td><?php echo date("H:i", strtotime($row['completedtime'])) ?></td>
 </tr>
<?php } ?>

Excel Export

$strExcelFileName="Member-All.xls";
header("Content-Type: application/x-msexcel; name=\"$strExcelFileName\"");
header("Content-Disposition: attachment; filename=\"$strExcelFileName\"");
header("Pragma:no-cache");

$i = 1;
foreach ($data as $row) {
   if ($i == 1) {
      // COLUMN HEADERS
      echo implode("\t", array_keys($row)) . "\r\n";
   }
   // DATA ROWS
   echo implode("\t", array_values($row)) . "\r\n";
   $i++;
}

Collectives™ on Stack Overflow

PHP Export to excel from MYSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related