홈페이지 제작을 하다보면 ,php에서 엑셀파일인 xsl 로 다운받는 기능이 필요할때가 많다. 특히 관공서홈페이지를 제작하다보면 , 필수 항목이다. 기존에 하던 방식은 해더를 붙인후에 , table 태그를 이용하는 방식이었다. 이 방식이 잘 먹히긴 했는데, 익스플로가 7로 업그래이드 되면서 먹히지 않는 경우가 발생했다. 난감해 하며 여기저기 방법을 찾다가 아래 CLASS를 발견했다.

<<MS-Excel Stream Handler (http://www.phpclasses.org/browse/package/1919.html) >>라는 이름을 가진 멋진 클래스다.

아래는 간단한 사용예제다. 원하는 내용을 배열로 만들기만 하면 , 엑셀파일로 다운로드가 된다. 따로 저장할수도 있고, 바로 다운로드도 가능하다.

<?php

/**

* MS-Excel stream handler

* Excel export example

* @author      Ignatius Teo            <ignatius@act28.com>

* @copyright   (C)2004 act28.com       <http://act28.com>

* @date        21 Oct 2004

*/

require_once "excel.php";

$export_file = "xlsfile://example.xls";//저장하고싶은 파일이름, 앞에 꼭 xlsfile:// 를 붙여야한다.

$fp = fopen($export_file, "wb");

if (!is_resource($fp))

{

    die("Cannot open $export_file");

}

// 엑셀에 들어갈 내용을 배열로 만든다.

$assoc = array(

    array("Sales Person" => "엑셀된다 만만세", "Q1" => "$3255", "Q2" => "$3167", "Q3" => 3245, "Q4" => 3943),

    array("Sales Person" => "Jim Brown", "Q1" => "$2580", "Q2" => "$2677", "Q3" => 3225, "Q4" => 3410),

    array("Sales Person" => "John Hancock", "Q1" => "$9367", "Q2" => "$9875", "Q3" => 9544, "Q4" => 10255),

);

fwrite($fp, serialize($assoc));

fclose($fp);

//엑셀파일로 다운로드

$export_file = "xlsfile://example.xls";//다운로드할 파일 이름

header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");

header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");

header ("Cache-Control: no-cache, must-revalidate");

header ("Pragma: no-cache");

header ("Content-type: application/x-msexcel");

header ("Content-Disposition: attachment; filename=\"" . basename($export_file) . "\"" );

header ("Content-Description: PHP/INTERBASE Generated Data" );

readfile($export_file);

exit;

?>

엑셀 클래스 파일 

아래내용을 excel.php 라는 이름으로 저장하면 된다.

<?php
/**
* MS-Excel stream handler
* This class read/writes a data stream directly
* from/to a Microsoft Excel spreadsheet
* opened with the xlsfile:// protocol
* This is used to export associative array data directly to MS-Excel
* @requires    PHP 4 >= 4.3.2
* @author      Ignatius Teo            <ignatius@act28.com>
* @copyright   (C)2004 act28.com       <http://act28.com>
* @version     0.3
* @date        20 Jan 2005
* $Id: excel.php,v 1.3 2005/01/20 09:58:58 Owner Exp $
*/
class xlsStream
{
    /* private */
    var $position = 0;          // stream pointer
    var $mode = "rb";           // default stream open mode
    var $xlsfilename = null;    // stream name
    var $fp = null;             // internal stream pointer to physical file
    var $buffer = null;         // internal write buffer
    var $endian = "unknown";    // little | unknown | big endian mode
    var $bin = array(
        "big" => "v",
        "little" => "s",
        "unknown" => "s",
    );

    /**
     * detect server endian mode
     * thanks to Charles Turner for picking this one up
     * @access    private
     * @params    void
     * @returns    void
     * @see        http://www.phpdig.net/ref/rn45re877.html
     */
    function _detect()
    {
        // A hex number that may represent 'abyz'
        $abyz = 0x6162797A;

        // Convert $abyz to a binary string containing 32 bits
        // Do the conversion the way that the system architecture wants to
        switch (pack ('L', $abyz))
        {
            // Compare the value to the same value converted in a Little-Endian fashion
            case pack ('V', $abyz):
                $this->endian = "little";
                break;

            // Compare the value to the same value converted in a Big-Endian fashion
            case pack ('N', $abyz):
                $this->endian = "big";
                break;

            default:
                $this->endian = "unknown";
                break;
        }
    }

    /**
     * called by fopen() to the stream
     * @param   (string)    $path           file path
     * @param   (string)    $mode           stream open mode
     * @param   (int)       $options        stream options (STREAM_USE_PATH |
     *                                      STREAM_REPORT_ERRORS)
     * @param   (string)    $opened_path    stream opened path
     */
    function stream_open($path, $mode, $options, &$opened_path)
    {
        $url = parse_url($path);
        $this->xlsfilename = '/' . $url['host'] . $url['path'];
        $this->position = 0;
        $this->mode = $mode;

        $this->_detect();    // detect endian mode

        //@TODO: test for invalid mode and trigger error if required

        // open underlying resource
        $this->fp = @fopen($this->xlsfilename, $this->mode);
        if (is_resource($this->fp))
        {
            // empty the buffer
            $this->buffer = "";

            if (preg_match("/^w|x/", $this->mode))
            {
                // write an Excel stream header
                $str = pack(str_repeat($this->bin[$this->endian], 6), 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
                fwrite($this->fp, $str);
                $opened_path = $this->xlsfilename;
                $this->position = strlen($str);
            }
        }
        return is_resource($this->fp);
    }

    /**
     * read the underlying stream resource (automatically called by fread/fgets)
     * @todo    modify this to convert an excel stream to an array
     * @param   (int)       $byte_count     number of bytes to read (in 8192 byte blocks)
     */
    function stream_read($byte_count)
    {
        if (is_resource($this->fp) && !feof($this->fp))
        {
            $data .= fread($this->fp, $byte_count);
            $this->position = strlen($data);
        }
        return $data;
    }

    /**
     * called automatically by an fwrite() to the stream
     * @param   (string)    $data           serialized array data string
     *                                      representing a tabular worksheet
     */
    function stream_write($data)
    {
        // buffer the data
        $this->buffer .= $data;
        $bufsize = strlen($data);
        return $bufsize;
    }

    /**
     * pseudo write function to manipulate the data
     * stream before writing it
     * modify this to suit your data array
     * @access  private
     * @param   (array)     $data           associative array representing
     *                                      a tabular worksheet
     */
    function _xls_stream_write($data)
    {
        if (is_array($data) && !empty($data))
        {
            $row = 0;
            foreach (array_values($data) as $_data)
            {
                if (is_array($_data) && !empty($_data))
                {
                    if ($row == 0)
                    {
                        // write the column headers
                        foreach (array_keys($_data) as $col => $val)
                        {
                            // next line intentionally commented out
                            // since we don't want a warning about the
                            // extra bytes written
                            // $size += $this->write($row, $col, $val);
                            $this->_xlsWriteCell($row, $col, $val);
                        }
                        $row++;
                    }

                    foreach (array_values($_data) as $col => $val)
                    {
                        $size += $this->_xlsWriteCell($row, $col, $val);
                    }
                    $row++;
                }
            }
        }
        return $size;
    }

    /**
     * Excel worksheet cell insertion
     * (single-worksheet supported only)
     * @access  private
     * @param   (int)       $row            worksheet row number (0...65536)
     * @param   (int)       $col            worksheet column number (0..255)
     * @param   (mixed)     $val            worksheet row number
     */
    function _xlsWriteCell($row, $col, $val)
    {
        if (is_float($val) || is_int($val))
        {
            // doubles, floats, integers
            $str  = pack(str_repeat($this->bin[$this->endian], 5), 0x203, 14, $row, $col, 0x0);
            $str .= pack("d", $val);
        }
        else
        {
            // everything else is treated as a string
            $l    = strlen($val);
            $str  = pack(str_repeat($this->bin[$this->endian], 6), 0x204, 8 + $l, $row, $col, 0x0, $l);
            $str .= $val;
        }
        fwrite($this->fp, $str);
        $this->position += strlen($str);
        return strlen($str);
    }

    /**
     * called by an fclose() on the stream
     */
    function stream_close()
    {
        if (preg_match("/^w|x/", $this->mode))
        {
            // flush the buffer
            $bufsize = $this->_xls_stream_write(unserialize($this->buffer));

            // ...and empty it
            $this->buffer = null;

            // write the xls EOF
            $str = pack(str_repeat($this->bin[$this->endian], 2), 0x0A, 0x00);
            $this->position += strlen($str);
            fwrite($this->fp, $str);
        }

        // ...and close the internal stream
        return fclose($this->fp);
    }

    function stream_eof()
    {
        $eof = true;
        if (is_resource($this->fp))
        {
            $eof = feof($this->fp);
        }
        return $eof;
    }
}

stream_wrapper_register("xlsfile", "xlsStream")
    or die("Failed to register protocol: xlsfile");
?>