PHP table to Excel file

Question

I can't figure out how to export a php table to an existing Excel file.
I'm new in php and until now I didn't understand how can I do that.

From my search I saw that it's needed a while and foreach statement.

<?php
$sql_data = date('d.m.Y', strtotime('-1days'));

require(realpath(dirname(__FILE__)."/PHPExcel-1.8/Classes/PHPExcel.php"));

    $conn = oci_connect('USER', 'PASS', 'dark:1521/DAR');
    if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }
    Else {echo 'Connection successfully !';
    }

$parametri = oci_parse($conn, "SELECT * FROM TABLE_1 WHERE TO_DATE('${sql_data}','DD.MM.YYYY') BETWEEN T_FROM AND T_TILL");

oci_execute($parametri);

echo "<table border='1'>\n";
while ($row = oci_fetch_array($parametri, OCI_ASSOC+OCI_RETURN_NULLS)) {

echo "<tr>\n";
foreach ($row as $item) {
    echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
}
    echo "</tr>\n";
}
echo "</table>\n";

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'UD_NAME');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'UD_CODE');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'TECH_TYPE');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'PPE_NAME');

$H_col = $objPHPExcel -> setActiveSheetIndex(0)->getHighestColumn();

$nr_col = PHPExcel_Cell::columnIndexFromString($H_col);
$nr_row = $objPHPExcel -> setActiveSheetIndex(0)->getHighestRow();

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));






?>

Asylzat · Accepted Answer · 2017-04-24 12:03:17Z

1

  function saveExcelFile($objPHPExcel, $file){
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: ' . $file . ';filename="01simple.xls"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0

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

try this instead of

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

try to run phpexcel, "header was sent error" you can't use "echo" before headers

require(realpath(dirname(__FILE__)."/PHPExcel-1.8/Classes/PHPExcel.php"));

function saveExcelFile($objPHPExcel, $file){
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: ' . $file . ';filename="01simple.xls"');
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

    // If you're serving to IE over SSL, then the following may be needed
    header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
    header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header ('Pragma: public'); // HTTP/1.0

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

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();


$objWorksheet->SetCellValue('A1', 'UD_NAME');
$objWorksheet->SetCellValue('B1', 'UD_CODE');
$objWorksheet->SetCellValue('C1', 'TECH_TYPE');
$objWorksheet->SetCellValue('D1', 'PPE_NAME');

saveExcelFile($objPHPExcel, 'attachment');

i don't know how this code below works, but test it separate

$sql_data = date('d.m.Y', strtotime('-1days'));

$conn = oci_connect('USER', 'PASS', 'dark:1521/DAR');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
Else {echo 'Connection successfully !';
}

$parametri = oci_parse($conn, "SELECT * FROM TABLE_1 WHERE TO_DATE('${sql_data}','DD.MM.YYYY') BETWEEN T_FROM AND T_TILL"

oci_execute($parametri);

echo "<table border='1'>\n";
while ($row = oci_fetch_array($parametri, OCI_ASSOC+OCI_RETURN_NULLS)) {

echo "<tr>\n";
foreach ($row as $item) {
    echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
}
    echo "</tr>\n";
}
echo "</table>\n";

edited Apr 24, 2017 at 12:03

answered Apr 24, 2017 at 10:56

Asylzat

2297 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BOB Over a year ago

I have the error " Cannot modify header information - headers already sent by(output started....) "for all the lines at functionsaveExcelFile "

Asylzat Over a year ago

tested all works fine, you can remove slesh "\" from $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); as $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); And check for oci_connect it seems that you didn't closed the brackets php.net/manual/en/function.oci-connect.php

BOB Over a year ago

That code extract data from oracle database, data which I want it into my existing Excel file.

Asylzat Over a year ago

Test them separate, and if all fine so then combine. $parametri = oci_parse($conn, "SELECT * FROM TABLE_1 WHERE TO_DATE('${sql_data}','DD.MM.YYYY') BETWEEN T_FROM AND T_TILL"); and delete all echo "<table border='1'>\n"; and use inside loop - $i++; $objWorksheet->SetCellValue('A' . $i, ($item !== null ? htmlentities($item, ENT_QUOTES) : " ") );

Collectives™ on Stack Overflow

PHP table to Excel file

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related