Jump to content
Sign in to follow this  
shades

PHP Export to multiple excel file

Recommended Posts

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.

 

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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();
 
   // }    
  }

Share this post


Link to post
Share on other sites

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 by Jacques1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 ??

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.