Facing issue with PHP Excel Export/Import

Question

I am trying to export record from db to excel using PHP (Codeignitor) and then Import the same excel file again using PHP (Codignitor)

Export Code IS:

header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename=' . date('d-m-Y') . '.xls');

$export .= '<table border="1">';
$export .= '<tr>';
$export .= '<td>Gate Name</td>';
$export .= '<td>ID</td>';
$export .= '<td>Name</td>';
$export .= '<td>Rank</td>';
$export .= '<td>Time</td>';
$export .= '<td>Entry Type</td>';
$export .= '<td>Work Place</td>';
$export .= '<td>Car Type</td>';
$export .= '<td>Car Number</td>';
$export .= '<td>Inspector Name</td>';
$export .= '</tr>';
foreach ($result as $row) {
    $export .= '<tr>';
    $export .= '<td>' . $row->gate_name . '</td>';
    $export .= '<td>' . $row->soldier_id . '</td>';
    $export .= '<td>' . $row->soldier_name . '</td>';
    $export .= '<td>' . $row->soldier_rank . '</td>';
    $export .= '<td>' . $row->time . '</td>';
    $export .= '<td>' . $row->entry_type . '</td>';
    $export .= '<td>' . $row->work_place . '</td>';
    $export .= '<td>' . $row->vehicle_type . '</td>';
    $export .= '<td>' . $row->vehicle_number . '</td>';
    $export .= '<td>' . $row->inspector_name . '</td>';
    $export .= '</tr>';
}
$export .= '</table>';
echo $export;

When I try to emport the downloaded file using This PHP Library - SimpleXLSX.

$SimpleXLSX = new SimpleXLSX($filename);
$data = $SimpleXLSX->rows();

the $data is empty

this library work if I create an excel file by self using MS Excel. if I try to get file content by using

print_r(file_get_contents($filename));

It return the content with HTML code

Like

<table border="1">
            <tr>
                <td>Gate Name</td>
                <td>ID</td>
                <td>Name</td>
                <td>Rank</td>
                <td>Time</td>
                <td>Entry Type</td>
                <td>Work Place</td>
                <td>Car Type</td>
                <td>Car Number</td>
                <td>Inspector Name</td>
            </tr>
            <tr>
                <td>Gate 2</td>
                <td>1111</td>
                <td>فارس الغامدي</td>
                <td>ملازم اول</td>
                <td>28/02/1435 07:01:00</td>
                <td>دخول</td>
                <td>قطاع رفحاء</td>
                <td>جيب</td>
                <td>Tuy 124</td>
                <td>عبدالله</td>
            </tr>
        </table>

I think the problem is with header() function or encoding when Creating Excel file. Please help me. Thanks.

You don't export a Excel file, you simply export a HTML table as a file with a .xls extension. It cannot be re-imported as a Excel file. — Tchoupi
– Tchoupi, Commented Dec 31, 2013 at 16:29
@MathieuImbert So who to make it proper MS Excel file when exporting? — Kalim
– Kalim, Commented Dec 31, 2013 at 16:32
use the PHPExcel class in the Pear library. Its much more extensible. — nathan hayfield
– nathan hayfield, Commented Dec 31, 2013 at 16:40

score 1 · Accepted Answer · 2013-12-31 17:35:20Z

SimpleXLSX will read XLSX files and print HTML code, but you cannot convert HTML code to XLSX format using SimpleXLSX. Here's an example of how to do it in PHPExcel:

<?php


$date = date("m-d-Y-g-i-s");

// Include and instantiate PHPExcel object.
include '../lib/PHPExcel/PHPExcel.php';
include '../lib/PHPExcel/PHPExcel/Writer/Excel2007.php';

$objPHPExcel = new PHPExcel();

// Set metadata.
$objPHPExcel->getProperties()->setTitle("Price List $date");
$objPHPExcel->getProperties()->setSubject("$date Price List");
$objPHPExcel->getProperties()->setDescription("The active price list for $date.");

// Set active sheet index at zero.
$objPHPExcel->setActiveSheetIndex(0);

// Determine limit and offset.
if($_GET['limit'] > 0 && $_GET['offset'] > 0) {
    $limit = $_GET['limit'];
    $offset = $_GET['offset'];
} else {
    $limit = 10000;
    $offset = 0;
}

// Query the database.
$query = mysql_query("SELECT * FROM `items` LIMIT $offset, $limit") or die(mysql_error());
$c = 0; // Initialize index at zero.

// Set column dimensions.
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

// Loop through data and insert it into their respective columns.
while($row = mysql_fetch_array($query)) {
    $c++;
    $objPHPExcel->getActiveSheet()->getCell("A$c")->setValueExplicit(stripslashes($row[strStock]), PHPExcel_Cell_DataType::TYPE_STRING);
    $objPHPExcel->getActiveSheet()->SetCellValue("B$c", stripslashes(html_entity_decode($row[strDescription])));
    $objPHPExcel->getActiveSheet()->SetCellValue("C$c", stripslashes($row[curPrice]));
}

$objPHPExcel->setActiveSheetIndex(0);
// Send headers to download XLSX file.
header('Content-Type: application/vnd.ms-excel');

if(isset($_GET[part]) && isset($_GET[of])) {
    header('Content-Disposition: attachment;filename="Price List (Part '.$_GET[part].' of '.$_GET[of].') '.$date.'.xlsx"');
}

header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');exit;

?>

Edit: Sorry, this is the code for PHPExcel, not SimpleXLSX. Here is the documentation for PHPExcel.

@josh I am using Codeinitor How can be use this with Codeinitor ?

Collectives™ on Stack Overflow

Facing issue with PHP Excel Export/Import

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related