CodeIgniter Spreadsheet Read and Write Excel file

PHP SpreadSheet library allows you to read and write to different spreadsheet file formats, like Excel and LibreOffice Calc. It is a great library which you may need to read and write data in your application. You can read and write XLSXLSX, XML, ODS, HTML different format files.

I am going to tell you how can you read and write data in CodeIgniter using the SpreadSheet library. You have to follow the steps.

Requirements

Before starting, you must have the following:

  • PHP version 5.6 or newer
  • PHP extension php_zip enabled
  • PHP extension php_xml enabled
  • PHP extension php_gd2 enabled (if not compiled in)

Composer

Use composer to install this library.

composer require phpoffice/phpspreadsheet

This will install the spreadsheet library in the current directory. You have to copy this library and paste into your CodeIgniter application/third_party folder.

CodeIgniter Read File

Once you copied the library into the third_party folder, open the controller and write the code below in a function.

<?php

 require_once APPPATH . '/third_party/phpexcel/vendor/autoload.php';
 $inputFileName = APPPATH . '/path/to/file.xls';

 try {

     $inputFileType = 'Xls'; // input file type Csv, Xls, Xlsx, Xml, Ods, Html
     $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
     $spreadsheet = $reader->load($inputFileName);
     $data = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

     if( count( $data ) > 0 ) { ?>
     <table>
         <thead>
             <tr>
                 <th>Name</th>
                 <th>Email</th>
                 <th>Phone</th>
             </tr>
         </thead>
         <tbody>
         <?php for ( $i = 1; $i < count($data); $i++ ) { ?>

            <tr>
                <td><?php echo $data[$i]['A']; ?></td>
                <td><?php echo $data[$i]['B']; ?></td>
                <td><?php echo $data[$i]['C']; ?></td>
            </tr>

         <?php } ?>
         </tbody>
     </table>

     <?php
     }

 } catch(Exception $e) {

    echo $e->getMessage();
    exit();
 }
?>

CodeIgniter Write File

You can write the data to the file using the Spreadsheet class. The setCellValue function of this class used to set the value to the cell. Here is the following code to write the data, copy and paste this code in your function.

<?php

     require_once APPPATH . '/third_party/phpexcel/vendor/autoload.php';
 
     $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
     $sheet = $spreadsheet->getActiveSheet();
     $sheet->setCellValue('A1', 'Name');
     $sheet->setCellValue('B1', 'Email');

     $array = array(
                 array(
                     'name' => 'Arun',
                     'email' => '[email protected]',
                     'phone' => '9288483494'
                 ),
                 array(
                     'name' => 'Amit',
                     'email' => '[email protected]',
                     'phone' => '9288483494'
                 )
            );

     $i = 2;
     foreach( $array as $row ) {
         $sheet->setCellValue( 'A' . $i, $row['name'] );
         $sheet->setCellValue( 'B' . $i, $row['email'] );
         $i++;
     }

     $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xls"); // you can create xlsx file as well
     $writer->save( APPPATH . '/data.xls');

?>

If you have any issue you can drop a comment.

 

Comments

Be the first to comment