Import and Export using PhpSpreadsheet Library in Codeigniter
In this post, I would like to show how to data import and export from excel file using phpspreadsheet library in codeigniter.Phpspreadsheet allow you to read and write data from spreadsheet file formats.
Follow me step by step I am going to show configuration of phpspreadsheet.
1. Download and install Codeigniter
I am assuming that you already know about setup of codeigniter.If you have any issue with codeigniter setup then you can check this post Codeigniter Setup.
2. Installation of PhpSpreadsheet
Download Phpspreadsheet library.To download open command prompt and run command from your project root folder.Please find below the command.
$ composer require phpoffice/phpspreadsheet
After installing, You will get a vendor folder inside project root folder. Here is how directory structure looks.
3.Setup Composer Autoload
You need to set vendor directory path inside application/config/config.php.$config[‘composer_autoload’] = ‘vendor/autoload.php’;
4. Creating Controller:
Create a controller named PhpspreadsheetController.php and use phpspreadsheet library inside controller.Please find below the code for controller.
PhpspreadsheetController.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class PhpspreadsheetController extends CI_Controller {
public function __construct(){
parent::__construct();
}
public function index(){
$this->load->view('spreadsheet');
}
public function export(){
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');
$writer = new Xlsx($spreadsheet);
$filename = 'name-of-the-generated-file';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'. $filename .'.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output'); // download file
}
public function import(){
$file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
if(isset($_FILES['upload_file']['name']) && in_array($_FILES['upload_file']['type'], $file_mimes)) {
$arr_file = explode('.', $_FILES['upload_file']['name']);
$extension = end($arr_file);
if('csv' == $extension){
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
} else {
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
}
$spreadsheet = $reader->load($_FILES['upload_file']['tmp_name']);
$sheetData = $spreadsheet->getActiveSheet()->toArray();
echo "<pre>";
print_r($sheetData);
}
}
}
5. Creating View:
Create a view named spreadsheet.php inside application/views directory. Please find the code for view file.
spreadsheet.php
<html>
<head>
<title>Import/Export using phpspreadsheet in codeigniter</title>
</head>
<body>
<style>
h3
{
font-family: Verdana;
font-size: 14pt;
font-style: normal;
font-weight: bold;
color:red;
text-align: center;
}
table.tr{
font-family: Verdana;
color:black;
font-size: 12pt;
font-style: normal;
font-weight: bold;
text-align:left;
}
</style>
<h3><u>Import/Export using phpspreadsheet in codeigniter</u></h3>
<?php echo form_open_multipart('spreadsheet/import',array('name' => 'spreadsheet')); ?>
<table align="center" cellpadding = "5">
<tr>
<td>File :</td>
<td><input type="file" size="40px" name="upload_file" /></td>
<td class="error"><?php echo form_error('name'); ?></td>
<td colspan="5" align="center">
<input type="submit" value="Import Users"/></td>
</tr>
</table>
<?php echo form_close();?>
</body>
</html>
6. Route Configuration:
Here is the route configuration inside application/config/routes.php.
$route['spreadsheet'] = 'PhpspreadsheetController'; $route['spreadsheet/import'] = 'PhpspreadsheetController/import'; $route['spreadsheet/export'] = 'PhpspreadsheetController/export';
7. Final Words:
This example simply explains how we can import/export using Phpspreadsheet. Visit here to get all its features and functions as per your requirement.
Thank you for reading this post. we hope you like this Post, Please feel free to comment below, your suggestion. if you face any issue with this code let us know. We’d love to help!