Introduction
Importing data from an Excel file into a CodeIgniter application is a common requirement for applications that need to process bulk records. Instead of entering records manually, users can upload an Excel file and import multiple rows of data into the application.
In this guide, we will explain how to implement Excel file importing in CodeIgniter using PHPExcel. The example demonstrates how to load an Excel file, read the spreadsheet data, and access individual column values.
Prerequisites
Before proceeding, make sure you have:
- A working CodeIgniter application.
- PHP installed and configured.
- Access to the CodeIgniter application directory.
- PHPExcel library.
- An Excel file containing the data to be imported.
Implementation:
Step 1: Download the PHPExcel Library
Download the PHPExcel library from its GitHub repository or the official PHPExcel website.
You can download the PHPExcel library from the following repository:
After downloading, extract the PHPExcel archive on your local system.
Step 2: Copy PHPExcel to CodeIgniter
After extracting the PHPExcel library, you will find directories such as:
Classes Documentation Examples
Navigate to the Classes directory and copy the following into your CodeIgniter application’s application/third_party/ directory:
Step 3: Create the Excel Import Library
Navigate to:
application/libraries/
Create a new file named:
ExcelImport_lib.php
Add the following code to the file:
<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
ini_set('error_reporting', E_STRICT);
require_once APPPATH."/third_party/PHPExcel.php";
class ExcelImport_lib extends PHPExcel
{ public function __construct()
{ parent::__construct(); }
} ?>
Step4:
Navigate to application/controllers and create new file name as ImportExcel.php,place following code here
<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
* summary
*/
class ImportExcel extends CI_Controller
{
public function index()
{
$this->load->view('excelimport');
}
public function Import($value='')
{
$this->load->library('ExcelImport_lib');
try {
/// it will be your file name that you are posting with a form or can pass static name $_FILES["file"]["name"];
// $objPHPExcel = PHPExcel_IOFactory::load('uploads/'.$_FILES["file"]["name"]);
$objPHPExcel = PHPExcel_IOFactory::load('uploads/'.'yourTableName.xls');
}
catch(Exception $e)
{
$this->resp->success = FALSE;
$this->resp->msg = 'Error Uploading file';
echo json_encode($this->resp);
exit;
}
$allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
print_r($allDataInSheet);
foreach($allDataInSheet as $import)
{
echo $import['A']; /// will return values of Col A
echo $import['B']; /// will return values of Col B
echo $import['C']; /// will return values of Col C
echo $import['D']; /// will return values of Col D
}
}
}
?>
Step5:
Navigate to application/view and create new file name as excelimport.php,place following code here
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?><!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Welcome to CodeIgniter</title>
<style type="text/css">
::selection { background-color: #E13300; color: white; }
::-moz-selection { background-color: #E13300; color: white; }
body {
background-color: #fff;
margin: 40px;
font: 13px/20px normal Helvetica, Arial, sans-serif;
color: #4F5155;
}
a {
color: #003399;
background-color: transparent;
font-weight: normal;
}
h1 {
color: #444;
background-color: transparent;
border-bottom: 1px solid #D0D0D0;
font-size: 19px;
font-weight: normal;
margin: 0 0 14px 0;
padding: 14px 15px 10px 15px;
}
code {
font-family: Consolas, Monaco, Courier New, Courier, monospace;
font-size: 12px;
background-color: #f9f9f9;
border: 1px solid #D0D0D0;
color: #002166;
display: block;
margin: 14px 0 14px 0;
padding: 12px 10px 12px 10px;
}
#body {
margin: 0 15px 0 15px;
}
p.footer {
text-align: right;
font-size: 11px;
border-top: 1px solid #D0D0D0;
line-height: 32px;
padding: 0 10px 0 10px;
margin: 20px 0 0 0;
}
#container {
margin: 10px;
border: 1px solid #D0D0D0;
box-shadow: 0 0 8px #D0D0D0;
}
</style>
</head>
<body>
Welcome to CodeIgniter!" enctype="multipart/form-data" method="POST" role="form">
Form title
Click to Read
</body>
</html>
Conclusion
Excel importing makes it much easier to handle bulk data in a CodeIgniter application. Using PHPExcel, we can load an Excel file, convert the spreadsheet into an array, read individual columns, and insert the data into a database.
The basic workflow is:
Upload Excel File
↓
Read Excel File
↓
Convert Spreadsheet to Array
↓
Validate Data
↓
Insert/Update Database
↓
Display Import Result
FAQs
1. Can I import both XLS and XLSX files?
Yes. PHPExcel can work with both older .xls files and .xlsx files, provided the appropriate reader is available.
2. Can I upload the Excel file instead of specifying a fixed filename?
Yes. You can use PHP’s $_FILES to process the file uploaded through an HTML form.
3. Where should I place PHPExcel in CodeIgniter?
For the approach shown in this tutorial, place the required PHPExcel files inside:
application/third_party/
4. How can I access Excel columns?
PHPExcel returns columns using Excel-style column names:
$row['A'];
$row['B'];
$row['C'];
$row['D'];
5. Can I insert the imported data into MySQL?
Yes. After reading and validating each row, you can use CodeIgniter’s database methods such as:
$this->db->insert('users', $data);