export to excel plugin

[eluser]LiorBroshi[/eluser]
Heya, 3 small updates for this plugin:

1. Fixed the non-utf8/ascii problem, now you can define which encoding you need and output with it (in the example i used windows-1255 -> hebrew).

2. Added option to use `labels` for the fields. instead of just showing the sql field name, you can give it a nice label for better output and understanding what is the data we are looking at, instead of 'is_newsletter' you can write 'Wants Newsletter' etc'.

Just throw in an array that the keys represent the field_name and the value represent the label value.

3. Again, return instead of the `no data..` output.

Enjoy!

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',$headers_labels=array())
    {
         $ci =& get_instance();
         $ci->load->helper('download');
    
         $char_encoding = 'windows-1255';
    
         $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) {
              return 'empty';
         } else {
              foreach ($fields as $field) {
                  
                  if (array_key_exists($field->name,$headers_labels)) {
                        # We have a label, use it.
                        $headers .= iconv(mb_detect_encoding($headers_labels[$field->name]),$char_encoding,$headers_labels[$field->name]) . "\t";
                  }
                  else {
                        # Put field name
                        $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";
                            
                             $value = iconv(mb_detect_encoding($value),$char_encoding,$value);
                        }
                        $line .= $value;
                   }
                   $data .= trim($line)."\n";
              }
              
              $data = str_replace("\r","",$data);
              
              #doPre($data);
              #die();
                          
              force_download($filename . ".xls", $headers . "\n" . $data);
         }
    }
}

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

[eluser]Unknown[/eluser]
Hi

I have updated this plugin for Codeigniter version 2.1.3. I have also updated it for use as a library file. To use save the code below as a library file named: To_excel.php

To use the code:

Code:

// Load the library file
$this->load->library('To_excel');

// Create database query
$this->db->select('*');
$this->db->where('field', 'value');
$query = $this->db->get('table_name');

// Create Excel file
$this->to_excel->create_excel($query, 'filename'); // filename is optional, without it, the plugin will default to 'exceloutput'

Library file:

Code:

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
/*
|--------------------------------------------------------------------------
| Create Excel file - Library
|--------------------------------------------------------------------------
| Created:  24/04/2013
|
|       Excel library for Code Igniter applications
|       Author: Derek Allard, Dark Horse Consulting, www.darkhorse.to, April 2006
|       https://github.com/EllisLab/CodeIgniter/wiki/Excel-Plugin
|
|       Updated by: ccontreras: 19 May 2011 11:59 AM
|      
|       Updated for Codeigniter 2.1.3  and converted to library file
|       by Timothy Head: 11/06/2013
|
*/
class To_excel {
    
    function create_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->list_fields();
        if ($query->num_rows() == 0) {
            echo '<p>The table appears to have no data.</p>';
        } else {
            foreach ($fields as $field) {
               $headers .= $field . "\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 */
/* Location: ./application/libraries/To_excel.php */

[eluser]CroNiX[/eluser]
Thank you for your contribution.