export to excel plugin

[eluser]Carlos Alcala[/eluser]
Hello Forum,

I have to work with fully customized spreadsheets and the PHPExcel seems to be the best fit for taht, but I was getting on trouble to use the PHPExcel class on codeigniter, helpfully some else do the trick, here is the solution:

http://techxplorer.com/2008/12/08/using-...deigniter/

I hope this would be useful,

Carlos

[eluser]moodsey211[/eluser]
does the output of PHPExcel be opened in OO spreadsheet? The drawback of writing excel in xml format is that only excel could render it. If you try to open it with OO spreadsheet it would show you the xml. T_t


Best regards,
Ismael

[eluser]Carlos Alcala[/eluser]
Hey moodsey211,

I have work today with PHPExcel porting to codeigniter and its working fine (just some issues on formating cells color, width and borders) but the rest it's fine.

About OO spreadsheets, I will test this on that soon, so I will tell you if it's working or not.

Thanks for reply,

Carlos

[eluser]moodsey211[/eluser]
Great. I really have problems about using XML. It seems that both MSO and OO have different formats. Which means if I generate xml for MS Excel I could not possibly open it in OO spreadsheet. T_t

[eluser]LiorBroshi[/eluser]
The `to_excel` plugin works great!

I did a small bugfix & changes though:

1. Heading duplications on join
When you join 2 tables (or more) and you have a duplicate-column name (e.g. `user_id`) then the entire data in the XLS file shifts 1 cell. therefore, i just added a small `storage` array to remember which headers takes place in the excel file and avoid duplications:

2. Return instead of output
Another thing I added is the return `empty` instead of echoing some text to the screen
for inside usage instead of just plain output.

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
*/

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

    $obj = &get;_instance();

    $fields = $query->field_data();
    if ($query->num_rows() == 0)
    {
        #echo '<p>The table appears to have no data.</p>';
        return 'empty';
    }
    else
    {
        # Headers array
        $headers_array = array();
        
        foreach ($fields as $field)
        {
            # Add to headers
            if (!in_array($field->name,$headers_array))
            {
                $headers .= $field->name . "\t";
                $headers_array[] = $field->name;
            }

        }

        foreach ($query->result() as $row)
        {
            $line = '';
            foreach ($row as $value)
            {
                if ((!isset($value)) or ($value == ""))
                {
                    $value = "\t";
                }
                else
                {
                    $value = str_replace('"', '""', $value);
                    $value = '"' . $value . '"' . "\t";
                }
                $line .= $value;
            }
            $data .= trim($line) . "\n";
        }

        $data = str_replace("\r", "", $data);

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

Hope this helps someone!

[eluser]moonbeetle[/eluser]
If your database stores data in UTF-8, how do you handle foreign language accents éëçà when exporting to Excel (Charset of Excel is CP1251) ?

[eluser]LiorBroshi[/eluser]
Well I'm not very sure, but isn't UTF-8 suppose to support these chars? ;/

[eluser]moonbeetle[/eluser]
Maybe I had to rephrase my question. The problem when exporting utf8 data to Excel is that Excel does NOT use utf8 but cp1251. Tried using PHP's iconv() without succes.

[eluser]LiorBroshi[/eluser]
maybe this can assist you http://www.phpfreaks.com/forums/index.ph...c=264019.0

[eluser]ccontreras[/eluser]
also you can do this for CI 2:

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
*/

if ( ! function_exists('to_excel'))
{
        function to_excel($query, $filename='exceloutput')
    {
         $ci =& get_instance();
         $ci->load->helper('download');
    
         $headers = ''; // just creating the var for field headers to append to below
         $data = ''; // just creating the var for field data to append to below
        
         $fields = $query->field_data();
         if ($query->num_rows() == 0) {
              echo '<p>The table appears to have no data.</p>';
         } else {
              foreach ($fields as $field) {
                 $headers .= $field->name . "\t";
              }
        
              foreach ($query->result() as $row) {
                   $line = '';
                   foreach($row as $value) {                                            
                        if (( ! isset($value)) OR ($value == "")) {
                             $value = "\t";
                        } else {
                             $value = str_replace('"', '""', $value);
                             $value = '"' . $value . '"' . "\t";
                        }
                        $line .= $value;
                   }
                   $data .= trim($line)."\n";
              }
              
              $data = str_replace("\r","",$data);
                            
              force_download($filename . ".xls", $headers . "\n" . $data);
         }
    }
}

/* End of file excel_helper.php */
/* Location: ./application/helpers/excel_helper.php */