Need help with download header

radi8 · January 18, 2011

I have a small program that is to be used to export data from some MYSQL tables into an Excel spreadsheet.

It does the export successfully, the first pass, but each subsequent download selection causes the browser to display the html code as text to the browser.

If I disable the excel output, the page redraws successfully un subsequent requests.

The header definition I have is ias follows:

$FileInfo = pathinfo(filename);
            // fix for IE catching or PHP bug issue 
           
            header("Pragma: public"); 
            header("Expires: 0"); // set expiration time 
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
            // browser must download file from server instead of cache 
            // force download dialog 
            header("Content-Type: application/force-download"); 
            header("Content-Type: application/octet-stream"); 
            header("Content-type: application/x-msexcel");
            header("Content-Type: application/download"); 
            // use the Content-Disposition header to supply a recommended filename and  
            // force the browser to display the save dialog.
            if ($download_filename == "")
                $download_filename = "download.xlm";
            header("Content-Disposition: attachment; filename=".$download_filename.";"); 
            header("Content-Transfer-Encoding: binary"); 
            header("Content-Length: ".filesize($filename)); 
            @readfile($filename);

I did not setup this header, because to be honest, these things confuse the heck out of me. Can anyone see what may be wrong with this? Can you give me some suggestions?

I can post more of the code if needed, but i believe that this is the area of interest for this issue.

Thanks in advance.

radi8 · January 18, 2011

In order to give you some more information, I am attaching a word doc with some screen grabs.

Also, I am attaching the code I am using. This code is attaching to my localhost db so unless you change the sql to go to your mysql, you may have issues running it (but you already know all that, so... ok).

Anyways, here you go.

index.php

<?php
  
  include_once $_SERVER['DOCUMENT_ROOT'] .
        '\inc\magicquotes.inc.php';
  include_once $_SERVER['DOCUMENT_ROOT'] .
        '/inc/base.inc.php';
  include_once $_SERVER['DOCUMENT_ROOT'] .
        '/admin/export/excel-xml.php';
  $frmErrorLevel=0;
  $frmErrMsg='';
  
  if(isset($_POST['action']) && $_POST['action']=='submitted')
  {
      $carrierRatesdisabled=true;
        
      if(isset($_POST['ExportCarrier']))
      {
          exportTruckingCompanies();
      }
      if(isset($_POST['ExportDropFees']))
      {
          exportDropFees();
      }
      if(isset($_POST['ExportDomesticRates']))
      {
          if(isset($_POST['Carrier_Name']) && $_POST['Carrier_Name'] == "0")
          {
              $frmErrorLevel=3;
          }
          else
          {
            exportDomesticRates($_POST[Carrier_Name]);
          }
      }
      if(isset($_POST['ExportCanadianRates']))
      {
          exportCanadianRates();
      }
      if(isset($_POST['ExportFuelSurcharge']))
      {
          exportFuelCurchargeRates();
      }
  }
  else
  {
      $carrierRatesdisabled=true;
  }
  
  // get the carrier recordset for the drop list
  
   $sql = 'SELECT * FROM `Carriers` order by `CarrierName`';
   openRecordset($sql,$result, $link);
    if($result)
    {
        while ($row = mysqli_fetch_array($result))
        {
            $carrierNames[] = array('id' => $row[0], 'name' => html($row[1]));
        }
         mysqli_free_result($result);
         close_mysql($link);
    }
    else $frmErrorLevel=1;
    switch($frmErrorLevel)
    {
        case 1:
            $frmErrMsg = 'Error retrieving Carrier Data';
            break;
        case 2:
            $frmErrMsg = 'Error retrieving Drop Fee Data';
            break;
        case 3:
            $frmErrMsg = 'Error retrieving Domestic Rates by Carrier, verify that a carrier is selected';
            break;
        case 4:
            $frmErrMsg = 'Error retrieving Canadian Rate Data';
            break;
        case 5:
            $frmErrMsg = 'Error retrieving Fuel Surcharge Rate Data';
            break;
        default:
            $frmErrMsg = '';
            break;
    }
    include 'form.html.php';
     if (ob_get_length()){           
        @ob_flush();
        @flush();
        @ob_end_flush();
    } 
  exit();
?>

form.html.php


<?php   
        session_start();
        include_once $_SERVER['DOCUMENT_ROOT'] .'/inc/standardFunctions.inc.php'; 
        //phpinfo();
        $dodebug=2;
        if($dodebug > 0)
        {
            ini_set ("display_errors", "1");
            //error_reporting(E_ALL);
            error_reporting(-1);
            echo "<pre>";
            switch($dodebug)
            {
                case 1:
                    echo "--> Form data (POST) <-- <br>";
                    print_r ($_POST);
                    echo "--> Form data (GET) <-- <br>";
                    print_r ($_GET);
                    break;
                case 2:
                    echo "--> Form data (POST) <-- <br>";
                    print_r ($_POST);
                    echo "--> Form data (GET) <-- <br>";
                    print_r ($_GET);
                    echo "--> Form data (SESSION) <-- <br>";
                    print_r ($_SESSION);
                    break;
                case 3:
                    echo "--> Form data (POST) <-- <br>";
                    print_r ($_POST);
                    echo "--> Form data (GET) <-- <br>";
                    print_r ($_GET);
                    echo "--> Form data (SESSION) <-- <br>";
                    print_r ($_SESSION);
                    echo "--> Form data (SERVER) <-- <br>";
                    print_r ($_SERVER);
                    break;
                case 4:
                    echo "--> PHP Info <-- <br>";
                    phpinfo();
                default: 
                    echo "--> Form data (POST) <-- <br>";
                    print_r ($_POST);
                    break;
            }
            echo "</pre>";
        }
        else
        {
            ini_set ("display_errors", "0");
            error_reporting(0);
        }
?>
<!DOCTYPE html>
<html lang="en">
<!--
    <head>
        <meta charset="utf-8">
        <title><?php htmlout($pagetitle); ?></title>
    </head>
    <body>
        <h1><?php htmlout($pagetitle); ?></h1>
        <form action="?<?php htmlout($action); ?>" method="post">
            <table width="500" border="0">
-->
    <head>
        <meta charset="utf-8">
        <title>Export Trucking Data</title>
    </head>
    <body>
        <h1>Export Data</h1>
        <form action="?addform" method="post">
        <?php   if($frmErrorLevel>0)
                {
                    echo '<font color=#CC6600 size=+1>'.$frmErrMsg.'</font><br><br>';
                }
        ?>
        <table width="650" border="0">
            <caption>
            Data Export Options
            </caption>
            <tr>
                <td>
                    <?php 
                        if($frmErrorLevel==1) print '<img src= ..\..\images\rdx.gif>'; 
                        else echo "&nbsp"; 
                    ?>
                </td>
                <td width="275"><label>Carrier table data</label></td>
                <td width="100"><input name="ExportCarrier" type="submit" value="Submit"></td>
                <td> <div align="center">  </div></td>

            </tr>
            <tr>
                <td>
                    <?php 
                        if($frmErrorLevel==2) print '<img src= ..\..\images\rdx.gif>'; 
                        else echo "&nbsp"; 
                    ?>
                </td>
                <td><label>Drop Fees Table data</label></td>
                <td><input type="submit" name="ExportDropFees" value="Submit"></td>
                <td> <div align="center">  </div></td>

            </tr>
            <tr>
                <td>
                    <?php 
                        if($frmErrorLevel==3) print '<img src= ..\..\images\rdx.gif>'; 
                        else echo "&nbsp"; 
                    ?>
                </td>
                <td><label>Domestic Carrier Rates, by Carrier</label></td>
                <td><input type="submit" name="ExportDomesticRates" value="Submit"></td>
                <td> <div align="center">
                   <select name="Carrier Name" id="selectCarrier">
                        <option value="0">Select Carrier</option>
                            <?php foreach ($carrierNames as $cn): ?>
                                <option value="<?php htmlout($cn['id']); ?>">
                                    <?php htmlout($cn['name']); ?>
                                </option>
                        <?php 
                            endforeach; 
                            unset($carrierNames);
                        ?>
                    </select>
                </div></td>

            </tr>
            <tr>
                <td>
                    <?php 
                        if($frmErrorLevel==4) print '<img src= ..\..\images\rdx.gif>'; 
                        else echo "&nbsp"; 
                    ?>
                </td>
                <td><label>Canadian Carrier Rates</label></td>
                <td><input type="submit" name="ExportCanadianRates" value="Submit"></td>
                <td> <div align="center">  </div></td>
            </tr>
            <tr>
                <td>
                    <?php 
                        if($frmErrorLevel==5) print '<img src= ..\..\images\rdx.gif>'; 
                        else echo "&nbsp"; 
                    ?>
                </td>
                <td><label>Export Fuel Surcharge Rate table data</label></td>
                <td><input type="submit" name="ExportFuelSurcharge" value="Submit"></td>
                <td> <div align="center">  
                </div></td>

            </tr>
        </table>
        <div>
            <input type="hidden" name="action" value="submitted" />
        </div>
    </form>
    </body>
</html>

excel-xml.php

<?php

/**
* php-excel
* See bottom of this file for generic notes on usage and other important structural
* information
*/

// include the php-excel class  (eliminating this structure for more robust implentation)
    function exportData($sql, $title, $fileName)
    {
        require (dirname (__FILE__) . "\class-excel-xml.inc.php");
        // instantialte the class
        $xls = new Excel_XML;
        // set worksheet title
        $xls->setWorksheetTitle($title);
        // set output filename, by default the filename is 'DataExport_[Date]_[time].XLS'
        $xls->setWorksheetFileName($fileName);
        // by default, column labels will be created, to hide labels... labels are defined from the SQL
        $xls->hideColumnLabels();
        // or to show them again...
        $xls->showColumnLabels();
        /* generate excel file in 2 ways
        * Pass an array
        * create a dummy array ... EXAMPLE
        * $myarray = array (
        *   1 => array ("Oliver", "Peter", "Paul"),
        *   array ("Marlene", "Lucy", "Lina")
         *  );
        * $xls->addArray ( $myarray );
        *$xls->generateXML ("mytest");
        * */
        // or pass the SQL string (this example passes SQL)
        if($xls->AddRows_RS($sql))$xls->generateXML ();
        // you can also save the Excel directly to the server document root if you like
        //$xls->generateAndSaveXML($_SERVER['DOCUMENT_ROOT'] . '/excel/');
        
    }
    
     function setExcelData($sql, $title, $fileName, $idref=0)
    {
        $retVal=false;
        $fileName=$fileName.'.xml';
        //$fileName=$fileName.'.xls';
        include_once("ExcelXML.inc.php");
        include_once $_SERVER['DOCUMENT_ROOT'].'/inc/db.inc.php';
        $input = ($idref==0?"blank1.xml":"blank.xml"); 
        // create ExcelXML object
        $xml = new ExcelXML();
        // read template file
        if (!$xml->read($input))
        {
            echo "Failed to open Tempalate Excel XML file<br>";
        }
        else
        {
            // activate a worksheet
            $xml->setActiveWorksheetByIndex(0);
            $rowNum=1;
            // if the table has Carrier Name as referential information, set top warnings in spreadsheet
            if($idref==1)
            {
                $rowNum++;
                $xml->setCellValueByRowColumn(1, 1, 'Reference Name - DO NOT USE WITH IMPORT');
                $xml->setCellValueByRowColumn(1, 2, 'Exported table - BEGIN EXPORT');
            }
            $result="";
            $link=null;
            
            openRecordset($sql,$result, $link);
            // get the header row, column field names from SQL
            if($result)
            {
                $rowOffset=0;
                // set header row information
                for($i=0;$i<mysqli_num_fields($result);$i++)
                {
                    $finfo = $result->fetch_field_direct($i);
                   //if (!$xml->setCellValue("AB2", "6789"))echo "Failed to set new cell's value<br>";
                   $xml->setCellValueByRowColumn($rowNum, $i+1, $finfo->name);
                }  // end header row loop
                // set the table data
                while($row = mysqli_fetch_array($result))
                { 
                    $rowNum++;
                    for($i=0;$i<mysqli_num_fields($result);$i++)
                    {
                        if(!$xml->setCellValueByRowColumn($rowNum ,$i+1,html($row[$i])))
                            echo "Failed to set Excel value into spreadsheet object, data points: 
                                Row: $rowNum, Col: $i+1, data: $row[$i]<br>";
                    }
                }  // end db data loop
                mysqli_free_result($result);
            }
            else // no results else
            {
              $retVal = false;   
            }
            //mysql_close($connection);
            close_mysql($link);
            
            // save modified file to output file
            //if($fileName)echo "yea";
            /*if (!$xml->save($fileName))
                echo "Failed to save file<br>";
            else
                {
                 echo "Succeed to save file<br>";
                 $retVal=true;   
                }*/
            if (!$xml->save($fileName, true, $fileName))
                echo "Failed to download file<br>";
        }
        return $retVal;
    }
        
    function exportFuelCurchargeRates()
    {
       $sql = 'SELECT b . Carriername , a . `CarrierNbr` , a . `SurchargeType` , '
        . ' ifnull( `BaseSurchargeAmt` , 0 ) as `BaseSurchargeAmt` , '
        . ' ifnull( `BaseFuelPrice` , 0 ) as `BaseFuelPrice` , '
        . ' ifnull( `RateIncreaseAmt` , 0 ) as `RateIncreaseAmt` , '
        . ' ifnull( `FuelIncreaseAmt` , 0 ) as `FuelIncreaseAmount` '
        . ' FROM `Fuel_Surcharge_Rate_Tables` a '
        . ' INNER JOIN `Carriers` b ON ( a . `CarrierNbr` = b . `CarrierNbr` )';
        //exportData($sql, 'Fuel Charge Rates', 'FuelChargeRates');
        setExcelData($sql,'Fuel Charge Rates','FuelChargeRates',1);
    }
    
    function importFuelCurchargeRates()
    {
        
    }
    
    function exportDomesticRates($id=0)
    {
        if($id>0)
        {
            $sql = 'SELECT b . CarrierName , a . * '
            . ' FROM `Carrier_Rates` a inner join `Carriers` b '
            . ' on ( a . CarrierNbr = b . CarrierNbr ) '
            . ' WHERE a.CarrierNbr= '.$id.' '
            . ' order by b . CarrierName';
            //exportData($sql, 'Domestic Carrier Rates', 'DomesticCarrierRates');
            setExcelData($sql,'Domestic Carrier Rates', 'DomesticCarrierRates',1);
        }
    }

    function importDomesticRates()
    {
        
    }
    
    function exportCanadianRates()
    {
        $sql = 'SELECT * FROM `Carrier_Rates-Canadian` ';
        //exportData($sql, 'Canadian Carrier Rates', 'CanadianCarrierRates');
        setExcelData($sql, 'Canadian Carrier Rates', 'CanadianCarrierRates',0);
    }

    function importCanadianRates()
    {
        
    }
    
    function exportTruckingCompanies()
    {
        $sql = 'SELECT * FROM `Carriers`';
        //exportData($sql, 'Carriers', 'Carriers');
        setExcelData($sql, 'Carriers', 'Carriers',0);
    }

    function importTruckingCompanies()
    {
        
    }
    
    function exportDropFees()
    {
        $sql = 'SELECT b . CarrierName , a . * '
        . ' FROM `Drop_Fees` a '
        . ' INNER JOIN `Carriers` b ON ( a . CarrierNbr = b . CarrierNbr ) '
        . ' ORDER BY b . CarrierName ';
        //exportData($sql, 'Drop Fees', 'DropFees');
        setExcelData($sql,'Drop Fees','DropFees',1);
        unlink('DropFees.xml');
    }

    function importDropFees()
    {
        
    }
    
    function excelImport($fileName, $range1, $range2, $tab)
    {
        /*
          1.    With the file, do I need to write a script to upload the file to a temp directory,
                do the import, and then delete the file when done?
          2.    I need to get the dialogue for determining the sheet (tab) name and also the range (Cell reference as A1, B4, C5 format)
        
        */
        $filename = $fileName; 
        $sheet1 = 1;
        $sheet2 = $tab;
        $excel_app = new COM("Excel.application") or Die ("Did not connect");
        print "Application name: {$excel_app->Application->value}\n" ;
        print "Loaded version: {$excel_app->Application->version}\n";
        $Workbook = $excel_app->Workbooks->Open("$filename") or Die("Did not open $filename $Workbook");
        $Worksheet = $Workbook->Worksheets($sheet1);
        $Worksheet->activate;
        $excel_cell = $Worksheet->Range("C4");
        $excel_cell->activate;
        $excel_result = $excel_cell->value;
        print "$excel_result\n";
        $Worksheet = $Workbook->Worksheets($sheet2);
        $Worksheet->activate;
        $excel_cell = $Worksheet->Range("C4");
        $excel_cell->activate;
        $excel_result = $excel_cell->value;
        print "$excel_result\n";
        #To close all instances of excel:
        $Workbook->Close;
        unset($Worksheet);
        unset($Workbook);
        $excel_app->Workbooks->Close();
        $excel_app->Quit();
        unset($excel_app);
    }
  // GENERIC NOTES AND STRUCTURE INFORMATION
        /* returns multidimensional array: 
              array ->    (0) element 0 will hold the headings
                          (0+x) all other elements will have a 2 dimensional array of type
                                  value, type (int)
         Possible MYSQL type definitions are as follows:
            DECIMAL             0       
            TINY                1       
            SHORT               2       
            LONG                3       
            FLOAT               4       
            DOUBLE              5       
            NULL                6       
            TIMESTAMP           7       
            LONGLONG            8       
            INT24               9
            DATE                10
            TIME                11
            DATETIME            12
            YEAR                13
            NEWDATE             14
            ENUM                247
            SET                 248
            TINY_BLOB           249
            TINY_BLOB           249
            TINY_BLOB           249
            LONG_BLOB           251
            BLOB                252
            VAR_STRING          253
            STRING              254
            GEOMETRY            255
            
            BUT, for our purposes, the type flag will be either 0 (text) or 1 (numeric)
        */

excelXML.class.php

<?php

/**
* Class ExcelXML
* Provide functions to modify the content of file in Excel's XML format.
* 
* REQUIRED:
* - An ExcelXML file as template
* 
* FEATURES:
* - read, modify, and save Excel's XML file
* - create download stream as Excel file format (*.xls)
* 
* CHANGELOG:
* 06-08-2008
* - Update setCellValue function
* - Fix setCellValue bug
* 13-07-2008
* - First created 
* 
*  This package consist of following files:
*  1. readme.txt -> this file
*  2. ExcelXML.php -> ExcelXML class
*  3. blank.xml -> example template file
*  4. text.php -> PHP example code to use ExcelXML class
*  
*  Notes:
*  - If you want to use another template (example: preformatted Excel file), just select
*    menu "Save As" from MS Excel, choose "XML Spreadsheet (*.xml)" in "Save as Type",
*    type the filename, the click "OK".
*    Use the new template filepath as argument in read() function.
*  - Please send the bugs to [email protected]
* 
* 
* 
* @author Herry Ramli ([email protected])
* @license GPL
* @version 0.1.1
* @copyright August 06, 2008
* 
* USAGE (sample) see bottom of this file
*/
class ExcelXML 
{
    var $domXML;
    var $activeWorksheet;
    
    function ExcelXML()
    {
    }
    
    /**
     * Read ExcelXML from file
     *
     * @param String $filename
     * @return boolean true if succeed or false if failed
     */
    function read($filename)
    {
        $this->domXML = new DomDocument();//new DOMDocument();
        if (!$this->domXML->load($filename))
            return false;
        $this->activeWorksheet = null;
        return true;
    }
    
    /**
     * Save ExcelXML to a file
     *
     * @param String $filename
     * @return boolean true if succeed or false if failed
     */
    function save($filename, $download=false, $download_filename="")
    {
        if (!$download)
        {
            return $this->domXML->save($filename);
        }
        elseif ($this->domXML->save($filename))
        {
            $FileInfo = pathinfo(filename);
            // fix for IE catching or PHP bug issue 
           
            header("Pragma: public"); 
            header("Expires: 0"); // set expiration time 
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
            // browser must download file from server instead of cache 
            // force download dialog 
            header("Content-Type: application/force-download"); 
            header("Content-Type: application/octet-stream"); 
            header("Content-type: application/x-msexcel");
            header("Content-Type: application/download"); 
            // use the Content-Disposition header to supply a recommended filename and  
            // force the browser to display the save dialog.
            if ($download_filename == "")
                $download_filename = "download.xlm";
            header("Content-Disposition: attachment; filename=".$download_filename.";"); 
            header("Content-Transfer-Encoding: binary"); 
            header("Content-Length: ".filesize($filename)); 
            @readfile($filename);
           
            return true;
        }
        return false;
    }

    /**
     * Create new table node
     *
     * @param Integer $totalRow
     * @param Integer $totalCol
     * @return DOMElement
     */
    function createTableNode($totalRow=0, $totalCol=0)
    {
        // e.g.: <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1">
        $tableNode = $this->domXML->createElement("Table");
        $tableNode = $this->activeWorksheet->appendChild($tableNode);
        $this->setTableNodeAttributes($tableNode, $totalRow, $totalCol);
        
        return $tableNode;
    }
    
    /**
     * Set TableNode's attributes
     *
     * @param DOMElement $tableNode
     * @param Integer $row total row
     * @param Integer $col total column
     * @return Boolean true if succeed or false if not
     */
    function setTableNodeAttributes(&$tableNode, $row, $col)
    {
        //try {
            $tableNode->setAttribute("ss:ExpandedColumnCount", $col);
            $tableNode->setAttribute("ss:ExpandedRowCount", $row);
            $tableNode->setAttribute("x:FullColumns", 1);
            $tableNode->setAttribute("x:FullRows", 1);
            return true;
        //} catch (Exception $e) {
        //    return false;            
        //}
    }
    
    /**
     * Create DataNode and set the value
     *
     * @param Mixed $value
     * @return DOMElement new DataNode
     */
    function createDataNode($value="")
    {
        $dataNode = $this->domXML->createElement("Data");
        $dataNode->nodeValue = $value;
        if (is_numeric($value))
            $dataNode->setAttribute("ss:Type", "Number");
        else
            $dataNode->setAttribute("ss:Type", "String");
        /*try {
            if (is_numeric($value))
                $dataNode->setAttribute("ss:Type", "Number");
            else
                $dataNode->setAttribute("ss:Type", "String");
        } catch (Exception $e) {
        } */
        return $dataNode;
    }
    
    /**
     * Search and return DOMNode associate with given excel address
     * 
     * @param String $address excel address
     * @return DOMNode or null if not found
     */
    function getDOMNode($address, $is_create=false)
    {
        $col = $this->getColumnAddress($address);
        $row = $this->getRowAddress($address);
        
        return $this->getDOMNodeByRowColumn($row, $col, $is_create);
    }
    
    /**
     * Search and return DOMNode associate with given excel address
     * 
     * @param Integer $row
     * @param Integer $col
     * @return DOMNode or null if not found
     */
    function getDOMNodeByRowColumn($row, $col, $is_create=false)
    {
        if ($this->activeWorksheet == null)
            return null;
        
        $resetTableAttributes = false;
        
        $tables = $this->activeWorksheet->getElementsByTagName("Table");
        if ($tables->length > 0)
            $tableNode = $tables->item(0);
        elseif ($is_create)
            $tableNode = $this->createTableNode($row, $col);
        else
            return null;

        $rowNodes = $tableNode->getElementsByTagName("Row");
        
        if ($rowNodes->length >= $row)
        {
            $rowNode = $rowNodes->item($row-1);
        }
        elseif ($is_create)
        {
            // create additional row nodes, total new node == $row - $rowNodes->length;
            $totalNewRowNode = $row - $rowNodes->length;
            for ($i=0; $i<$totalNewRowNode; $i++)
                $rowNode = $tableNode->appendChild(new DOMElement("Row"));
            $resetTableAttributes = true;
        }
        else
        {
            return null;
        }
        $colNodes = $rowNode->getElementsByTagName("Cell");
        //echo $row . " - " . $col;
        if ($colNodes->length >= $col)
        {
            $colNode = $colNodes->item($col-1);
        }
        elseif ($is_create)
        {
            // create additional col nodes, total new node == $col - $colNodes->length;
            $totalNewColNode = $col - $colNodes->length;
            for ($i=0; $i<$totalNewColNode; $i++)
                $colNode = $rowNode->appendChild(new DOMElement("Cell"));
            $resetTableAttributes = true;
        }
        else
        {
            return null;
        }
        $this->setTableNodeAttributes($tableNode, $row, $col);
        $dataNodes = $colNode->getElementsByTagName("Data");
        
        if ($dataNodes->length > 0)
        {
            return $dataNodes->item(0);
        }
        elseif ($is_create)
        {
            return $colNode->appendChild($this->createDataNode(""));
        }
        else
        {
            return null;
        }
        
        return null;
    }
    
    /**
     * Set cell's value with given address
     *
     * @param String $address cell's address
     * @param String $value cell's value
     * @return boolean true if succeed or false if failed
     */
    function setCellValue($address, $value)
    {
        $node = $this->getDOMNode($address, true);
        if ($node == null)
            return false;
        $node->nodeValue = $value;
        return true;        
    }
    
    /**
     * Set cell's value with given address
     *
     * @param Integer $row
     * @param Integer $column
     * @return boolean true if succeed or false if failed
     */
    function setCellValueByRowColumn($row, $col, $value)
    {
        $node = $this->getDOMNodeByRowColumn($row, $col, true);
        if ($node == null)
            return false;
        $node->nodeValue = $value;
        return true;
    }

    /**
     * Get row number of given excel address
     *
     * @param String $address excel address
     * @return int row number
     */
    function getRowAddress($address)
    {
        $len = strlen($address);
        $index = 0;
        for (; $index<$len; $index++)
        {
            $chr = substr($address, $index, 1);
            if (is_numeric($chr))
                break;
        }
        $addr = substr($address, $index);
        return intval($addr);
    }
    
    /**
     * Get column number of given excel address
     *
     * @param String $address excel address
     * @return int column number
     */
    function getColumnAddress($address)
    {
        $addr = strtoupper($address);
        $len = strlen($addr);
        $index = 0;
        for (; $index<$len; $index++)
        {
            $chr = substr($addr, $index, 1);
            if (is_numeric($chr))
                break;
        }
        $colAddr = substr($addr, 0, $index);
        $col = 0;
        for ($i=0; $i<strlen($colAddr); $i++)
        {
            $c = substr($colAddr, -($i+1), 1);
            $d = ord($c) - 64;
            $v = ($i <= 0) ? $d : ($d*26);
            $col += $v;
        }
        return $col; //ord($col) - 64;
    }
    
    /**
     * Set active worksheet of given index
     *
     * @param int $index
     * @return boolena true if succeed or false if failed
     */
    function setActiveWorksheetByIndex($index)
    {
        $workbook = $this->domXML->getElementsByTagName("Workbook");
        //$workbook = $this->domXML->getElementsByTagName("Workbook");
        $workbook = $workbook->item(0);
        $worksheets = $workbook->getElementsByTagName("Worksheet");
        if ($worksheets->length <= 0)
            return false;
        $this->activeWorksheet = $worksheets->item(0);
        return true;
    }
    
    /**
     * Set active worksheet with given worksheet's name
     *
     * @param String $name
     * @return boolean true if succeed or false if failed
     */
    function setActiveWorksheet($name)
    {
        $workbook = $this->domXML->getElementsByTagName("Workbook");
        $workbook = $workbook->item(0);
        $worksheets = $workbook->getElementsByTagName("Worksheet");
        if ($worksheets->length <= 0)
            return false;
            
        foreach ($worksheets as $worksheet)
        {
            if ($worksheet->getAttribute("ss:Name") == $name)
            {
                $this->activeWorksheet = $worksheet;
                return true;
            }
        }
        return false;
    }
    
    /**
     * Return cell's value of given excel address
     *
     * @param String $address excel address
     * @return String
     */
    function getCellValue($address)
    {
        $node = $this->getDOMNode($address);
        if ($node != null)
            return $node->nodeValue;
        return null; 
    }

    /**
     * Return cell's value of given excel address
     *
     * @param Integer $row
     * @param Integer $col
     * @return String
     */
    function getCellValueByRowColumn($row, $col)
    {
        $node = $this->getDOMNodeByRowColumn($row, $col);
        if ($node != null)
            return $node->nodeValue;
        return null; 
    }
}
/* SAMPLE USAGE SCRIPT
include_once("ExcelXML.php");

// source file
$input = "blank.xml"; 
// output file
$output = "result.xml"; 

// create ExcelXML object
$xml = new ExcelXML();
// read template file
if (!$xml->read($input))
{
    echo "Failed to open file<br>";
}
else
{
    // activate a worksheet
    $xml->setActiveWorksheetByIndex(0);
    // get cell value
    echo $xml->getCellValue("A1") . "<br>";
    // set cell value
    if ($xml->setCellValue("AB2", "6789"))
        echo "Succeed to set new cell's value<br>";
    else
        echo "Failed to set new cell's value<br>";
    
    // save modified file to output file
    if (!$xml->save($output))
        echo "Failed to save file<br>";
    else
        echo "Succeed to save file<br>";

    /// uncomment following codes, so client can download Excel (*.xls) file 
    
    //if (!$xml->save($output, true, 'output.xls'))
    //    echo "Failed to download file<br>";
}
*/

?>

[attachment deleted by admin]

radi8 · January 19, 2011

I think that I am getting closer to the cause of the problem, but may need your assistance on a fix.

It would appear as though the export to excel is creating the excel file and also the download header correctly, but it is not redrawing the form.html.php page(?????).

I think that what I need is a way to force the download page to process, and then flush the header so that the form.html.php can fully reload (see the above code index.php). I did a test on the page to see if a control element is being reset changed after the file download process. I created a variable in the controller and in the form page, if the value is a 1, I display a message, otherwise I do not. Initially I set it to display the message (first load), then I processed the excel file and reset the variable, but the message persisted. So, the page is not being reprocessed after the excel process.

So, what I need to find is a way to force the page to reload completely after the Excel file has been processed. FYI: I have the PHP.INI file setting: Output_Buffering to 'ON'.

I hope this makes sense to you, if not, I can try to clarify as best I can.

radi8 · January 20, 2011

small update: I turned off the output_buffering in the PHP.INI and the unformatted HTML does not show up anymore. But, the page still fails to reload properly. In fact, it does not reload at all. I need to force the page to reload after the Excel download so that I can add status informational messages.

BlueSkyIS · January 20, 2011

To the best of my knowledge, from a PHP script you can send a file or you can send a header to reload the page. but you can't do both. in other words, you can't use PHP send a file to the browser to download and then force the browser to reload the page after the file is downloaded.

there is often a message "your download should start. if it doesn't, click here."

one option might be to have the script create the excel file, then header() to another page with a success message and "click here to download the Excel file" link.

Sign In

Need help with download header

Recommended Posts

radi8

Link to comment

Share on other sites

radi8

Link to comment

Share on other sites

radi8

Link to comment

Share on other sites

radi8

Link to comment

Share on other sites

BlueSkyIS

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information