How to Export/Download data in Excel using Codeigniter 2020

Sometimes we work on data-driven projects in which most of the time we have to deal with exporting data in Excel file. In this article, we will see how to Export/Download data in Excel using Codeigniter.

So let’s get started, before we hit to the code part lets analyze what we need to start writing any code. We need the two most important tools database server and composer. For the database purpose, I am using XAMPP which comes with MySQL and PHPMyAdmin.

if you don’t have these two things please install it before we go further. To install the composer you can follow these links according to your Operating system. Ubuntu , Windows

To install XAMPP – Windows , Ubuntu , MacOS

I am assuming you have already have a setup for s codeigniter project with database and all. All you need to export the table data.

The Setup / Installation

To accomplish our article goal we will be using the PhpSpreadsheet library. Now Open your terminal in your project directory and enter the following command.

composer require phpoffice/phpspreadsheet

add the following line in application/config/config.php if it is not exists.

$config['composer_autoload'] = 'vendor/autoload.php';

Code part

Now let’s suppose I have the following UI with the export button. You can see the export highlighted button, on click of this button we will add all below table structured data in Excel file and Download.

Code of Export button.

				
<a class="btn btn-outline-secondary btn-lg btn-block" href="Excel_export/action"><span class="fa fa-download"></span>Export</a>
         		
         	

I am using <a> tag with href Excel_export/action .

Excel_export – name of controller and action is function name.

So now let’s see the action funtion part which gets excuted on click of export button.

<?php
defined('BASEPATH') or exit('No direct script access allowed');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Excel_export extends CI_Controller
{

    function action()
    {
        $this->load->model("Bill_model");
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        /*set column names*/
        $table_columns = array("Company Name", "Invoice Number", "Bill Date",  "Taxable total", "CGST", "SGST", "Advance", "Total");
        $column = 1;
        foreach ($table_columns as $field) {
            $sheet->setCellValueByColumnAndRow($column, 1, $field);
            $column++;
        }
        /*end set column names*/
        $bill_data = $this->Bill_model->get_List(); //get your data from model

        $excel_row = 2; //now from row 2

        foreach ($bill_data as $row) {
            $sheet->setCellValueByColumnAndRow(1, $excel_row, $row['customer']);
            $sheet->setCellValueByColumnAndRow(2, $excel_row, $row['invoice_number']);
            $sheet->setCellValueByColumnAndRow(3, $excel_row, $row['bill_date']);
            $sheet->setCellValueByColumnAndRow(4, $excel_row, $row['taxable_total']);
            $sheet->setCellValueByColumnAndRow(5, $excel_row, $row['cgst']);
            $sheet->setCellValueByColumnAndRow(6, $excel_row, $row['sgst']);
            $sheet->setCellValueByColumnAndRow(7, $excel_row, $row['advance']);
            $sheet->setCellValueByColumnAndRow(8, $excel_row, $row['total']);
            $excel_row++;
        }
        $object_writer = new Xlsx($spreadsheet);
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Invoce_list.xls"');
        $object_writer->save('php://output');
    }
}

Explanation

The above code will download a file named Invoce_list.xls on every click of the export button. Let’s have short explaination of what’s happening in above code.

So in very few starting lines we have imported library in out file.

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

Next, we have an action function which doing our main part to convert our data in excel format and get downloaded immediately.

So in action function we are loading our model which helps to fetch the data from database.

$this->load->model("Bill_model");

In the next line, we are instantiating PhpSpreadsheet library with new keyword. and the next setting column name that will be visible in the Excel file at the top.

And In the end, we are fetching data from our model and looping through that data and placing all data in columns using the setCellValueByColumnAndRow() function.

At last, we are setting up some headers and filename of the created excel file and executing it to download the file.

header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Invoce_list.xls"');
        $object_writer->save('php://output');

So thats all about how to Export/Download data in Excel using Codeigniter. I hope this article has helped you. In case of any doubts and queries leave the comment in the below comments section. We will surely help you out.

Thank you for your time.

See also

Leave a Reply

Your email address will not be published. Required fields are marked *