Excel Sheet Data Uploading and Handling the required fields in PHP
1) First Download PHPExcel from https://phpexcel.en.softonic.com/download or from any other resource. Extract this zip file in libraries folder of your codeigniter framework
2) Now include this in your Controller as
$this->load->library('excel');
3) Create a file with name Excel.php in your library folder and replace the following code
CODE:
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
require_once('PHPEXCEL/Classes/PHPExcel.php');
class Excel extends PHPExcel
{
public function __construct()
{
parent::__construct();
}
}
?>
4) Now in your main controller write a function to handle the excel upload
public function excel_upload
{
extract($_POST);
if (isset($_FILES['excel_file'])) {
$path = $_FILES['excel_file']['tmp_name'];
$object = PHPExcel_IOFactory::load($path);
$error_rows = []; //required to show the error rows or missed fields rows in case of required fields
foreach ($object->getWorksheetIterator() as $worksheet) {
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
for ($row = 2; $row <= $highestRow; $row++) {
$data1= $worksheet->getCell('A' . $row)->getValue();
$data2= $worksheet->getCell('B' . $row)->getValue();
$data3= $worksheet->getCell('C' . $row)->getValue();
// Check if any compulsory field is empty
if (empty($data1) || empty($data2) || empty($data3)) {
$error_rows[] = $row;
continue;
}
//Handling Entering the data in database
$data["column1"] = $data1;
$data["column2"] = $data2;
$data["column3"] = $data3;
$this->Commonmodel->insert_data('table_name', $data);
}
}
if (!$error_rows) {
alert_success("Added Successfully.");
} else {
alert_fail("Empty Data Submitted Check rows: " . implode(', ', $error_rows). " Remaining Record inserted successfully");
}
}
} catch (\Throwable $th) {
alert_fail("Adding Failed, Please try again later. ");
}
}
5)By this Logic if any required field is missing in any of the row that row will be skipped and remaining rows will be inserted as it is.
6) By following these steps you can upload your required data through excel
Note: It is important to add the data in Excel sheet according to the logic you are handling through your code or it may lead to errors and mismanagement of data upload
At the starting only Define your upload format and use this format only for every upload
Tagged: