Welcome Guest, Not a member yet? Register   Sign In
[PLUGIN] Excel plugin extended to arrays.
#1

[eluser]Morty[/eluser]
Please refer to http://codeigniter.com/wiki/Excel_Plugin/ for original plugin.

Following my last needs (see older threads), I took my own hands to modify a little this plugin. It now should accept three different data sources :

- Queries : default type and therefore backward compatible ;
- Associative array :
=> table[0]['Number'] => 1 ;
=> table[0]['Information'] => 'Blabla1' ;
=> table[1]['Number'] => 3 ;
=> table[1]['Information'] => 'Blabla2' ;
- Arrays :
=> $array['headers'] containing headers ;
=> $array['data'] containing data (nice titling isn't it?) ;

It can be summoned by using to_excel($source, $filename, $type = 'query' or 'assoc_array' or 'array').

Now for the code :

Code:
<?php if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/*
* Excel library for Code Igniter applications
* Author: Derek Allard, Dark Horse Consulting, www.darkhorse.to, April 2006
* Modification: Hadrien Debris, Archos S.A., July 2007
*/

function is_obj(&$object, $check = null, $strict = true)
{
    if (is_object($object))
    {
        if ($check == null)
        {
            return true;
        }
        else
        {
            $object_name = get_class($object);
            return ($strict === true) ? ($object_name == $check):(strtolower($object_name) ==
                strtolower($check));
        }
    }
    else
    {
        return false;
    }
}

function row_record($row)
{
    $line = '';
    foreach ($row as $value)
    {
        if ((!isset($value)) || ($value == ""))
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    return ($line);
}

function to_excel($source, $filename = 'exceloutput', $type = 'query')
{
    $headers = '';// just creating the var for field headers to append to below
    $data = '';// just creating the var for field data to append to below
    $test_passed = false;// validating datasource

    $obj = &get;_instance();

    if (($type == 'query') && (is_obj($source, "CI_DB_mysql_result")))
        // we're passing a normal query
    {
        $fields = $source->field_data();
        $test_passed = true;
    }
    elseif (($type == 'assoc_array') && (is_array($source)))
        // information is held in an associative array
    {
        $fields = array_keys($source[0]);// We take our first record's headers
        $test_passed = true;
    }
    elseif (($type == 'array') && (isset($source['headers'])) && (is_array($source)))
        // headers and data are separated
    {
        $fields = $source['headers'];
        $test_passed = true;
    }
    else
        echo '<p>Incorrect <b>$type</b> parameter.</p>';

    if ($test_passed)
    {
        $result_check_query = (($type == 'query') && ($source->num_rows() == 0));
        $result_check_array = (is_array($source)) && (((count($source) == 0)) || (!isset($source['data'])));

        if ($result_check_query || $result_check_array)
        {
            echo '<p>The table appears to have no data.</p>';
        }
        else
        {
            if ($type == 'query')
            {
                foreach ($fields as $field)
                {
                    $headers .= $field->name . "\t";
                }

                foreach ($source->result() as $row)
                {
                    $data .= trim(row_record($row)) . "\n";
                }
            }
            elseif ($type == 'assoc_array')
            {
                foreach ($fields as $field)
                {
                    $headers .= $field . "\t";
                }
                foreach ($source as $header => $row)
                {
                    $data .= trim(row_record($row)) . "\n";
                }
            }
            else
            {
                foreach ($fields as $field)
                {
                    $headers .= $field . "\t";
                }
                foreach ($source['data'] as $row)
                {
                    $data .= trim(row_record($row)) . "\n";
                }
            }

            if ($headers != '') // Allow results with no headers
                $headers .= "\n";
            $data = str_replace("\r", "", $data);

            header("Content-type: application/x-msdownload");
            header("Content-Disposition: attachment; filename=$filename.xls");
            echo $headers;
            echo $data;
        }
    }
    else
    {
        echo ('Error when submitting your datasource : ');
        echo (var_dump($source));
        return false;
    }
}
?&gt;

I did not have time to test it completely, so please report bugs and features you might want added. I am not a PHP-maniac so there could be novice errors in there.

Thanks in advance for your feedback.

Edit/P.S. : I was thinking about allowing headers with "colspan" or extraheadcontent but I don't know if it is either useful or if it can easily be added.
#2

[eluser]Morty[/eluser]
For those of you who needed an extra title line, here is the modified code.

Example : $extrahead = array("Large title" => 4, "Small title" => 1, "Not so small title" => 2);

New code is as follows :

Code:
&lt;?php if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/*
* Excel library for Code Igniter applications
* Author: Derek Allard, Dark Horse Consulting, www.darkhorse.to, April 2006
* Modification: Hadrien Debris, Archos S.A., July 2007
*/

function is_obj(&$object, $check = null, $strict = true)
{
    if (is_object($object))
    {
        if ($check == null)
        {
            return true;
        }
        else
        {
            $object_name = get_class($object);
            return ($strict === true) ? ($object_name == $check):(strtolower($object_name) ==
                strtolower($check));
        }
    }
    else
    {
        return false;
    }
}

function row_record($row)
{
    $line = '';
    foreach ($row as $value)
    {
        if ((!isset($value)) || ($value == ""))
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    return ($line);
}

function to_excel($source, $filename = 'exceloutput', $type = 'query', $extrahead = array
    ())
{
    $headers = '';// just creating the var for field headers to append to below
    $data = '';// just creating the var for field data to append to below
    $test_passed = false;// validating datasource

    $obj = &get;_instance();

    if (($type == 'query') && (is_obj($source, "CI_DB_mysql_result")))
        // we're passing a normal query
    {
        $fields = $source->field_data();
        $test_passed = true;
    }
    elseif (($type == 'assoc_array') && (is_array($source)))
        // information is held in an associative array
    {
        $fields = array_keys($source[0]);// We take our first record's headers
        $test_passed = true;
    }
    elseif (($type == 'array') && (isset($source['headers'])) && (is_array($source)))
        // headers and data are separated
    {
        $fields = $source['headers'];
        $test_passed = true;
    }
    else
        echo '<p>Incorrect <b>$type</b> parameter.</p>';

    if ($test_passed)
    {
        $result_check_query = (($type == 'query') && ($source->num_rows() == 0));
        $result_check_array = (is_array($source)) && (((count($source) == 0)) || (!isset($source['data'])));

        if ($result_check_query || $result_check_array)
        {
            echo '<p>The table appears to have no data.</p>';
        }
        else
        {
            if ($type == 'query')
            {
                foreach ($fields as $field)
                {
                    $headers .= $field->name . "\t";
                }

                foreach ($source->result() as $row)
                {
                    $data .= trim(row_record($row)) . "\n";
                }
            }
            elseif ($type == 'assoc_array')
            {
                foreach ($fields as $field)
                {
                    $headers .= $field . "\t";
                }
                foreach ($source as $header => $row)
                {
                    $data .= trim(row_record($row)) . "\n";
                }
            }
            else
            {
                foreach ($fields as $field)
                {
                    $headers .= $field . "\t";
                }
                foreach ($source['data'] as $row)
                {
                    $data .= trim(row_record($row)) . "\n";
                }
            }

            if ($headers != '') // Allow results with no headers
                $headers .= "\n";
            $data = str_replace("\r", "", $data);

            header("Content-type: application/x-msdownload");
            header("Content-Disposition: attachment; filename=\"$filename.xls\"");

            if (count($extrahead) > 0)
            {
                $extra = '';
                foreach ($extrahead as $title => $room)
                {
                    if ($room < 1)
                        $room = 1;
                    $extra .= $title;
                    for ($i = 0; $i < $room; $i++)
                    {
                        $extra .= "\t";
                    }
                }
                $extra .= "\n";
                echo $extra;
            }

            echo $headers;
            echo $data;
        }
    }
    else
    {
        echo ('Error when submitting your datasource : ');
        echo (var_dump($source));
        return false;
    }
}
?&gt;




Theme © iAndrew 2016 - Forum software by © MyBB