Export Excel file with PHP - Format error when opening file

Question

Export Excel file with PHP - Format error when opening file

Navigation

#1 by (1 votes)
#2 by (0 votes)

3

When exporting an excel file with php, the file can not be opened because it sends me a format error.

It should be noted that I am trying to open it with Excel 2013

This is the error:

EXCEL CAN NOT OPEN THIS FILE.

"The format or extension of the file may not be valid Check that the file has not been damaged and that the file extension matches the file format"

This is my code:

<?php

include('C:/xampp/htdocs/LOGEC/core/conexion/conexion.php');
if($_GET['fecha_inicio'] and $_GET ['fecha_termino']){

$fecha_inicio = $_GET['fecha_inicio'];
$fecha_termino = $_GET['fecha_termino'];


$consulta = pg_query($conn, "SELECT a.name ... FROM ...");

$output = '';


if(pg_num_rows($consulta)>0){
  $output.= '<table border="1">
            <tr>
              <th>Nº Orden</th>
              <th>Cliente O.S</th> ...
            </tr>
  ';
  while($avance = pg_fetch_array($consulta)){
    $output.= '
              <tr>
                   <td>' . $avance['n_orden']. '</td>
                   <td>' . $avance['cliente_os']. '</td> ....
              </tr>
    ';
  }
  $output.= '</table>';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="S.O desde '.$fecha_inicio.' hasta '.$fecha_termino.' (con inventario).xls"');
    header('Cache-Control: max-age=0');
    echo $output;
  }
}

?>

Finally, if there is the possibility of omitting this message when opening, because the contents of the document do not affect in the least.

php excel

asked by Carlo Echeverría 05.12.2016 в 14:12

source

2 answers

SSL Certificate for websites How to pass javascript variables to ruby on rails?

score 1 · Answer 1

Although Excel correctly interprets an HTML table as if it were an Excel file, it does not stop displaying this warning. If in the file that you are exporting you do not need to use excel functions, the most convenient thing is that you export it as CSV.

What is CSV?

Comma separated values (CSV) is a text file format that can be used to exchange data from a spreadsheet between applications. Each line of a text CSV file represents a row of a spreadsheet. Each cell in a spreadsheet row is usually separated by a comma (,) or semicolon (;).

Try the following example, if it gives you good results you can adapt the same logic to your case.

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=file2.csv");
header("Pragma: no-cache");
header("Expires: 0");

echo "N de orden; Cliente\n";
echo "0000001; Test 01 \n";
echo "0000002; Test 02 \n";
echo "0000003; Test 03 \n";

In case your records can contain the character that is used as a separator (semicolons in this example) you should include the text in quotes.

score 0 · Answer 2

I agree with Max Dominguez

But if you really want to export an excel file I recommend using PHPExcel , this adds a bit of difficulty to your code but it does more robust.

To make my work a bit easier, develop a helper, which, using anonymous functions, minimizes the work of creating worksheets. It also helps you with the download, you can download it as xls or xlsx , this helper can find it here

This would be your code using PHPExcel and my helper

<?php
include('C:/xampp/htdocs/LOGEC/core/conexion/conexion.php');
include('PHPExcel/Classes/PHPExcel.php');
include('class/Excel.php');

if ($_GET['fecha_inicio'] and $_GET ['fecha_termino'])
{
    $fecha_inicio = $_GET['fecha_inicio'];
    $fecha_termino = $_GET['fecha_termino'];

    $consulta = pg_query($conn, "SELECT a.name ... FROM ...");

    if (pg_num_rows($consulta) > 0)
    {
        $excel = new Excel("S.O_desde_{$fecha_inicio}_hasta_{$fecha_termino} (con inventario)");
        $excel->sheet('Worksheet', function (PHPExcel_Worksheet $sheet) use ($consulta)
        {
            $sheet->setCellValue('A1', 'Nº Orden');
            $sheet->setCellValue('B1', 'Cliente O.S');
            $row = 2;
            while ($avance = pg_fetch_array($consulta))
            {
                $sheet->setCellValueByColumnAndRow(0, $row, $avance['n_orden']);
                $sheet->setCellValueByColumnAndRow(1, $row, $avance['cliente_os']);
                $row++;
            }
        });
        //aquí defines la extensión de descarga
        //Soporta xls y xlsx          
        $excel->download('xlsx');
    }
}