CodeIgniter Forums

Full Version: Writing excel 2003 xml files
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]Kamarg[/eluser]
I recently found myself needing to create Excel files that couldn't be accomplished with a csv file. Rather than figuring out the Excel BIFF or OOXML formats, I went and wrote a simple CodeIgniter library that can write out Excel 2003 xml files. The library also needed to be able to run on PHP4 (with the domxml extension enabled) and PHP5. Attached is a zip file containing my solution.

Below is an example controller for a very simple spreadsheet that adds two cells together and formats the result as a currency then forces a download of the Excel file.

As I have little time to add to the functionality, I thought I'd release it to the community to do with as they please.

Code:
<?php
class ExcelTest extends Controller {
    function ExcelTest() {
        parent::Controller();
        
        // Load the library.  This will automatically load the dom helper.
        $this->load->library('excel');
    }
    
    function index() {
        // Create a new style that can be applied to cells.  This particular style will be in the form of $x.xx or ($x.xx) for positive and negative numbers.
        $currency_opts = array('NumberFormat' => array('ss:Format' => '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)'));
        $currency = $this->excel->create_style($currency_opts);
        
        // Create a new worksheet named WSName as the first worksheet.  Set it to be the active worksheet.
        $this->excel->worksheet("WSName", 1, TRUE);
        
        // Set cell A1 to be equal to B2 + C3
        $this->excel->formula('A1', 'R[1]C[1]+R[2]C[2]');
        
        // Make A1 a currency using our style
        $this->excel->style('A1', $currency);
        
        // Set B2 to 2 and make Excel display it as a number
        $this->excel->cell('B2', 2);
        $this->excel->type('B2', 'Number');
        
        // From now own, don't make us call the type function, just pick the appropriate type for us
        $this->excel->smart_typing(true);
        
        // Set C3 to 3
        $this->excel->cell('C3', 3);
        
        // Force the workbook to be downloaded.  Alternatly, pass 'S' to get the workbook back as an xml string.
        $this->excel->output();
    }
}

El Forum

[eluser]Bui Duc Long[/eluser]
thanks for sharing Wink

El Forum

[eluser]Kamarg[/eluser]
Updated to add worksheet protection. You can't password protect an xml file (this is a MS restriction) and being plaintext it would be rather pointless anyway. Not that it isn't easy to remove the protection anyway but there's not anything that can be done about that.

El Forum

[eluser]Unknown[/eluser]
Thank u for sharing,
it's very2 helping me.

El Forum

[eluser]Unknown[/eluser]
I can't download this zip