[SOLVED] Export Data into Excel Spreadsheet

dennismonsewicz · February 6, 2008

Sorry about not getting back with everyone late yesterday. I left work and then a bad storm came through the area so no internet. I am at work now working on the script now. I will be in contact today. Thanks again for everyone's help!

dennismonsewicz · February 6, 2008

ok...

so I commented out the first thing, to just get the headers to come through and it did not work.

So something is wrong somewhere, obviously

everything is spelled correctly. I am running this script inside of a switch with a case name of export.

rhodesa · February 6, 2008

Is anything else getting echoed/printed? Because the excel can be the only thing sending stuff. Can you post some of your code?

dennismonsewicz · February 6, 2008

Sure here is the code I am using:

The following is my code in my switch

case "export":	

			 include "../includes/db_login.php";
			  include "excelWriter.inc.php";

			  $excel=new ExcelWriter();

			  $myArr=array("Id","Product","Company","Description","Web Address","Last Used","Where product was last used","Is product active or inactive?");
			  $excel->writeLine($myArr);

			   $query = "SELECT * FROM table_name";
			  $result = mysql_query($query);
			  if(!$result){
				die("Could not query the database: <br/>" . mysql_error());
			  }
			  while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
				$id = $row['rowid'];
				$company = $row['company'];
				$product = $row['product'];
				$description = $row['description'];
				$web = $row['web'];
				$last = $row['last'];
				$used = $row['used'];
				$active = $row['active'];
				$myArr = array($id,$product,$company,$description,$web,$last,$used,$active);

				$excel->writeLine($myArr);
			  }
			  $excel->send('hr');
			  exit;
		} 
		/* END SWITCH */

Here is the code I am using for my include:

<?php
  class ExcelWriter{
    var $data = null;
    var $filename = null;
    var $newRow = false;

    function ExcelWriter(){
      
    }
    
    function writeLine($line_arr){
      if(!is_array($line_arr)){
        trigger_error("Error : Argument is not valid. Supply an valid Array.",E_USER_WARNING);
        return false;
      }
      $this->data .= "  <tr>\n";
      foreach($line_arr as $value)
        $this->data .= "    <td class=xl24 width=64 >{$value}</td>\n";
      $this->data .= "  </tr>\n";
    }

    function send($filename = null){
      if($filename){
        header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" );
        header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" );
        header ( "Pragma: no-cache" );
        header ( "Content-type: application/x-msexcel" );
        header ( "Content-Disposition: attachment; filename={$filename}.xls" );
      }
      print $this->GetHeader();
      print $this->data;
      print $this->GetFooter();
      if($filename)
        exit;
    }

    function GetHeader(){
      ob_start();
?>
        <html xmlns:o="urn:schemas-microsoft-com:office:office"
        xmlns:x="urn:schemas-microsoft-com:office:excel"
        xmlns="http://www.w3.org/TR/REC-html40">

        <head>
        <meta http-equiv=Content-Type content="text/html; charset=us-ascii">
        <meta name=ProgId content=Excel.Sheet>
        <!--[if gte mso 9]><xml>
         <o:DocumentProperties>
          <o:LastAuthor>Sriram</o:LastAuthor>
          <o:LastSaved>2005-01-02T07:46:23Z</o:LastSaved>
          <o:Version>10.2625</o:Version>
         </o:DocumentProperties>
         <o:OfficeDocumentSettings>
          <o:DownloadComponents/>
         </o:OfficeDocumentSettings>
        </xml><![endif]-->
        <style>
        <!--table
          {mso-displayed-decimal-separator:"\.";
          mso-displayed-thousand-separator:"\,";}
        @page
          {margin:1.0in .75in 1.0in .75in;
          mso-header-margin:.5in;
          mso-footer-margin:.5in;}
        tr
          {mso-height-source:auto;}
        col
          {mso-width-source:auto;}
        br
          {mso-data-placement:same-cell;}
        .style0
          {mso-number-format:General;
          text-align:general;
          vertical-align:bottom;
          white-space:nowrap;
          mso-rotate:0;
          mso-background-source:auto;
          mso-pattern:auto;
          color:windowtext;
          font-size:10.0pt;
          font-weight:400;
          font-style:normal;
          text-decoration:none;
          font-family:Arial;
          mso-generic-font-family:auto;
          mso-font-charset:0;
          border:none;
          mso-protection:locked visible;
          mso-style-name:Normal;
          mso-style-id:0;}
        td
          {mso-style-parent:style0;
          padding-top:1px;
          padding-right:1px;
          padding-left:1px;
          mso-ignore:padding;
          color:windowtext;
          font-size:10.0pt;
          font-weight:400;
          font-style:normal;
          text-decoration:none;
          font-family:Arial;
          mso-generic-font-family:auto;
          mso-font-charset:0;
          mso-number-format:General;
          text-align:general;
          vertical-align:bottom;
          border:none;
          mso-background-source:auto;
          mso-pattern:auto;
          mso-protection:locked visible;
          white-space:nowrap;
          mso-rotate:0;}
        .xl24
          {mso-style-parent:style0;
          white-space:normal;}
        -->
        </style>
        <!--[if gte mso 9]><xml>
         <x:ExcelWorkbook>
          <x:ExcelWorksheets>
           <x:ExcelWorksheet>
          <x:Name>srirmam</x:Name>
          <x:WorksheetOptions>
           <x:Selected/>
           <x:ProtectContents>False</x:ProtectContents>
           <x:ProtectObjects>False</x:ProtectObjects>
           <x:ProtectScenarios>False</x:ProtectScenarios>
          </x:WorksheetOptions>
           </x:ExcelWorksheet>
          </x:ExcelWorksheets>
          <x:WindowHeight>10005</x:WindowHeight>
          <x:WindowWidth>10005</x:WindowWidth>
          <x:WindowTopX>120</x:WindowTopX>
          <x:WindowTopY>135</x:WindowTopY>
          <x:ProtectStructure>False</x:ProtectStructure>
          <x:ProtectWindows>False</x:ProtectWindows>
         </x:ExcelWorkbook>
        </xml><![endif]-->
        </head>

        <body link=blue vlink=purple>
        <table x:str border=0 cellpadding=0 cellspacing=0 style='border-collapse: collapse;table-layout:fixed;'>
<?php
      return ob_get_clean();
    }

    function GetFooter(){
      return "</table></body></html>";
    }
  }
?>

rhodesa · February 6, 2008

But my concern is that something is being printed before this code.

In the ExcelWriter class, update these lines:

    function send($filename = null){
      if(headers_sent()) die("Headers already sent"); //Add this line
      if($filename){
        header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" );

rhodesa · February 6, 2008

Also, just to make sure the problem is in your code and not your computer, click this link and tell me if it opens ok:

http://vectorloft.com/aaron/phpfreaks/test.php

dennismonsewicz · February 6, 2008

Ok...

I clicked your link and was able to open it.

I updated the PHP script, and still the same problem.

I did notice this though:

When I click on my link and try to open it, Excel displays this message:

But when I click on your link it works and does not display the above message

dennismonsewicz · February 6, 2008

I even had a coworker go to the exact link and on a windows machine using Office 03 and still is having the same problem

rhodesa · February 6, 2008

Well...the code you post looks fine, so it has to be something in the rest of your code. You'll need to post more of the code if you want me to try and find the problem.

dennismonsewicz · February 6, 2008

RHODESA YOUR A GENIOUS!

I took the code out of the switch and placed in another file called excel.php and then linked over to it and it worked! There was something happening in the switch

Everything works like a charm now! You ROCK!

vallabh · April 30, 2015

Im using excelwriter.inc.php library to write data in XLS. I have a system where I select column names like "id" , "name", "description" etc. This columns can be chosen from list. The column count can vary from choosing 1 or 2 or 40 columns to appear in XLS.

As I chose more columns to appear in XLS. The data is not shown in proper format. Some data goes missing in between or with large number of chosen column the count is reduced to 8000-9000 rows.

Suppose if column count is around at max 2 . Suppose id , name , the total rows would be perfectly shown having 10k records. But as I increase the column say adding a description . The row count would reduce to suppose 8-9k. Now If I keep increasing more column than The record count keep on decreasing and also some columns data goes missing.

There is some problem with the excelwriter library to write data in XLS.

LOGIC : One sheet will have maximum 10k rows. If count exceeds 10k . It will generate a new sheet.

Sign In

[SOLVED] Export Data into Excel Spreadsheet

Recommended Posts

dennismonsewicz

Link to comment

Share on other sites

dennismonsewicz

Link to comment

Share on other sites

rhodesa

Link to comment

Share on other sites

dennismonsewicz

Link to comment

Share on other sites

rhodesa

Link to comment

Share on other sites

rhodesa

Link to comment

Share on other sites

dennismonsewicz

Link to comment

Share on other sites

dennismonsewicz

Link to comment

Share on other sites

rhodesa

Link to comment

Share on other sites

dennismonsewicz

Link to comment

Share on other sites

vallabh

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information