PHP Export to multiple excel file

[shades]

Hi,

 

Firstly the below loop is not working as expected, I am not able to export to multiple excel files. It exports and writes to the same file.

 

Secondly after exporting I got below errors inside the file. My requirement is to export data to a separate excel file on each iteration of the loop.  So, could someone help me with these issue I am not able figure out where is the problem.

if(isset($_GET['groupname'], $_GET['decks'], $_GET['rows']))
   {

    $groupname = $_GET['groupname'];
    $decks = $_GET['decks'];
    $rows = $_GET['rows'];    
    $pickdeckrows = $rows/$decks;
    
    for($i=1; $i<=$decks; $i++){

      $stmt = $dbconnect->prepare("SELECT v.decknumber, v.vtext  FROM decks v WHERE  v.groupname =:groupname LIMIT :pickdeckrows");
      $stmt -> bindValue(':groupname', $groupname);
      $stmt -> bindValue(':pickdeckrows', $pickdeckrows);
      $stmt -> execute();

      ob_end_clean();

      $output = '
       <table class="table" bordered="1">  
        <tr>  
             <th>GroupId</th>  
             <th>VignetteText</th>
        </tr>
      ';
      while($row = $stmt -> fetch())
      {
       $output .= '
        <tr>  
             <td>'.$row["decknumber"].'</td>  
             <td>'.$row["vtext"].'</td>
        </tr>
       ';
      }
      $output .= '</table>';
      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment; filename='.$groupname.'deck'.$i.'.xls');
      echo $output;

    }    
  }

  else {
    echo "Not set!!!";
  }

Errors:

 

1. Notice: ob_end_clean(): failed to delete buffer. No buffer to delete 

2. Warning: Cannot modify header information - headers already sent by (first header)

3. Warning: Cannot modify header information - headers already sent by (second header)

 

Thanks.

 

 

[Jacques1]

You don't seem to understand how HTTP works.

 

You can send a single response with a single file*. If you want the user to download multiple files, you have to either put them into an archive or display a page where the files can be downloaded one by one.

 

 

 

* In theory, there are multipart messages, but I would stay away from those experiments.

[Jacques1]

Frankly, I have no idea what you're doing there. You run the exact same query over and over again, each time you generate the same HTML table, and then you try to pretend that this HTML table is an Excel spreadsheet.

 

What do you expect from this other than a collection of identical files all containing gibberish?

[shades]

Frankly, I have no idea what you're doing there. You run the exact same query over and over again, each time you generate the same HTML table, and then you try to pretend that this HTML table is an Excel spreadsheet.

 

What do you expect from this other than a collection of identical files all containing gibberish?

 

Actually i did not include the code to delete the the rows which are exported. Below is the code which i tried and its working fine for exporting once. But I would like to have it export until the loop ends.

if(isset($_GET['groupname'], $_GET['decks'], $_GET['rows']))

   {

 

    $groupname = $_GET['groupname'];

    $decks = $_GET['decks'];

    $rows = $_GET['rows'];    

    $pickdeckrows = $rows/$decks;

 

  //  for($i=1; $i<=$decks; $i++){

 

      $stmt = $dbconnect->prepare("SELECT v.decknumber, v.vtext  FROM decks v WHERE  v.groupname =:groupname LIMIT :pickdeckrows");

      $stmt -> bindValue(':groupname', $groupname);

      $stmt -> bindValue(':pickdeckrows', $pickdeckrows);

      $stmt -> execute();

 

      ob_end_clean();

 

      $output = '

       <table class="table" bordered="1">  

        <tr>  

             <th>GroupId</th>  

             <th>Text</th>

        </tr>

      ';

      while($row = $stmt -> fetch())

      {

       $output .= '

        <tr>  

             <td>'.$row["decknumber"].'</td>  

             <td>'.$row["vtext"].'</td>

        </tr>

       ';

      }

      $output .= '</table>';

      header('Content-Type: application/vnd.ms-excel');

      header('Content-Disposition: attachment; filename='.$groupname.'deck.xls');

      echo $output;

 

      $stmtdelete = $dbconnect->prepare("DELETE  FROM decks WHERE  groupname =:groupname LIMIT :pickdeckrows");

      $stmtdelete -> bindValue(':groupname', $groupname);

      $stmtdelete -> bindValue(':pickdeckrows', $pickdeckrows);

      $stmtdelete -> execute();

 

   // }    

  }

[Jacques1]

Read the reply. And, no, this is not fine. Some applications may be clever enough to recognize that your HTML table pretending to be a spreadsheet is actually an HTML table which should be imported, but that doesn't change the fact that the type declaration is nonsense and lead to errors at any time.

 

If you want a spreadsheet, then serve a spreadsheet. There are several libraries which can generate a valid file. But then again: Read the reply.

Edited September 3, 2017 by Jacques1

[shades]

Read the reply. And, no, this is not fine. Some applications may be clever enough to recognize that your HTML table pretending to be a spreadsheet is actually an HTML table which should be imported, but that doesn't change the fact that the type declaration is nonsense and lead to errors at any time.

 

If you want a spreadsheet, then serve a spreadsheet. There are several libraries which can generate a valid file. But then again: Read the reply.

 

Okay I got what you meant. I see my excel file has data but it's just dumping the HTML Table and not in the format of a spreadsheet. I see there are PHP Excel Libraries. I will use it but again my requirement is to generate multiple excel files and user cannot do it manually as there will be like more than 10 excel files and they want it to be generated automatically.

[Jacques1]

I said there are two options. Two. If you don't want the user to download the files separately, then use an archive (.zip, .tar.gz, whatever).

[kicken]

As far as generating the "excel" files, if you don't need any of the fancy features like multiple-sheets, formatting, etc then I generally find it to be easiest to just generate a CSV file. The file type is generally mapped to a user's spreadsheet program (be that excel, open office, whatever) and is easy to generate/inspect.

 

<?php
if(isset($_GET['groupname'], $_GET['decks'], $_GET['rows'])){
    $groupname = $_GET['groupname'];
    $decks = $_GET['decks'];
    $rows = $_GET['rows'];    
    $pickdeckrows = $rows/$decks;

    //for($i=1; $i<=$decks; $i++){
        $stmt = $dbconnect->prepare("SELECT v.decknumber, v.vtext  FROM decks v WHERE  v.groupname =:groupname LIMIT :pickdeckrows");
        $stmt->bindValue(':groupname', $groupname);
        $stmt->bindValue(':pickdeckrows', $pickdeckrows);
        $stmt->execute();

        ob_end_clean();

        $output = fopen('php://memory', 'w+');
        $length = fputcsv($output, [
            'GroupId'
            , 'Text'
        ]);
        while($row = $stmt -> fetch()){
            $length += fputcsv($output, [
                $row["decknumber"]
                , $row["vtext"]
            ]);
        }

        header('Content-Type: text/csv');
        header('Content-length: '.$length);
        header('Content-Disposition: attachment; filename='.$groupname.'deck.csv');
        rewind($output);
        fpassthru($output);
        fclose($output);

        $stmtdelete = $dbconnect->prepare("DELETE  FROM decks WHERE  groupname =:groupname LIMIT :pickdeckrows");
        $stmtdelete -> bindValue(':groupname', $groupname);
        $stmtdelete -> bindValue(':pickdeckrows', $pickdeckrows);
        $stmtdelete -> execute();
    //}
}

And as mentioned, if you need to provide multiple files in a single request, package them up into an archive. then serve that file.

[shades]

As far as generating the "excel" files, if you don't need any of the fancy features like multiple-sheets, formatting, etc then I generally find it to be easiest to just generate a CSV file. The file type is generally mapped to a user's spreadsheet program (be that excel, open office, whatever) and is easy to generate/inspect.

 

<?php
if(isset($_GET['groupname'], $_GET['decks'], $_GET['rows'])){
    $groupname = $_GET['groupname'];
    $decks = $_GET['decks'];
    $rows = $_GET['rows'];    
    $pickdeckrows = $rows/$decks;

    //for($i=1; $i<=$decks; $i++){
        $stmt = $dbconnect->prepare("SELECT v.decknumber, v.vtext  FROM decks v WHERE  v.groupname =:groupname LIMIT :pickdeckrows");
        $stmt->bindValue(':groupname', $groupname);
        $stmt->bindValue(':pickdeckrows', $pickdeckrows);
        $stmt->execute();

        ob_end_clean();

        $output = fopen('php://memory', 'w+');
        $length = fputcsv($output, [
            'GroupId'
            , 'Text'
        ]);
        while($row = $stmt -> fetch()){
            $length += fputcsv($output, [
                $row["decknumber"]
                , $row["vtext"]
            ]);
        }

        header('Content-Type: text/csv');
        header('Content-length: '.$length);
        header('Content-Disposition: attachment; filename='.$groupname.'deck.csv');
        rewind($output);
        fpassthru($output);
        fclose($output);

        $stmtdelete = $dbconnect->prepare("DELETE  FROM decks WHERE  groupname =:groupname LIMIT :pickdeckrows");
        $stmtdelete -> bindValue(':groupname', $groupname);
        $stmtdelete -> bindValue(':pickdeckrows', $pickdeckrows);
        $stmtdelete -> execute();
    //}
}

And as mentioned, if you need to provide multiple files in a single request, package them up into an archive. then serve that file.

 

 

Thanks for information. Yes, I actually need no formatting and multiple sheets. I found out that the application can only supports CSV file format.

 

The main requirement is not just exporting into multiple files. User later selects one of the generated files into their application. So, if i generate it into an archive then again i have to unpack and provide it to them for selection. So, could you please tell me if this is the only way I could do it ? Or is their any other way to do it ?

 

Thanks.

[shades]

As far as generating the "excel" files, if you don't need any of the fancy features like multiple-sheets, formatting, etc then I generally find it to be easiest to just generate a CSV file. The file type is generally mapped to a user's spreadsheet program (be that excel, open office, whatever) and is easy to generate/inspect.

 

<?php
if(isset($_GET['groupname'], $_GET['decks'], $_GET['rows'])){
    $groupname = $_GET['groupname'];
    $decks = $_GET['decks'];
    $rows = $_GET['rows'];    
    $pickdeckrows = $rows/$decks;

    //for($i=1; $i<=$decks; $i++){
        $stmt = $dbconnect->prepare("SELECT v.decknumber, v.vtext  FROM decks v WHERE  v.groupname =:groupname LIMIT :pickdeckrows");
        $stmt->bindValue(':groupname', $groupname);
        $stmt->bindValue(':pickdeckrows', $pickdeckrows);
        $stmt->execute();

        ob_end_clean();

        $output = fopen('php://memory', 'w+');
        $length = fputcsv($output, [
            'GroupId'
            , 'Text'
        ]);
        while($row = $stmt -> fetch()){
            $length += fputcsv($output, [
                $row["decknumber"]
                , $row["vtext"]
            ]);
        }

        header('Content-Type: text/csv');
        header('Content-length: '.$length);
        header('Content-Disposition: attachment; filename='.$groupname.'deck.csv');
        rewind($output);
        fpassthru($output);
        fclose($output);

        $stmtdelete = $dbconnect->prepare("DELETE  FROM decks WHERE  groupname =:groupname LIMIT :pickdeckrows");
        $stmtdelete -> bindValue(':groupname', $groupname);
        $stmtdelete -> bindValue(':pickdeckrows', $pickdeckrows);
        $stmtdelete -> execute();
    //}
}

And as mentioned, if you need to provide multiple files in a single request, package them up into an archive. then serve that file.

 

 

Using your solution, when i export, it is exporting as index.php and not as csv file.. Why is that ??

[shades]

Okay. Understood as my export page is part of my Index page, the contents of index page were getting dumped. So to resolve this i pointed my export to a standalone page and now the download to csv is working perfect.

 

I also read from another forum that "I cannot spit out multiple files on the fly for download. That's a limitation of the http protocol and PHP can not work around that unless I save multiple files to the disk and then either zip them and offer the zip file OR use a series of header redirects to grab the files in sequential order."

 

So, I guess I got no other option than to zip all the files and later maybe unzip and provide it to the user.