Upload XLS sheet and Update Column name "Smart ID" and download the Updated XLS sheet

As you know the CodeIgniter framework follows the MVC(Model/View/Controller) architecture and we explain all the three section along with PHPExcel library which will help to read/write XLS sheet.

To start the development you need to first download the PHPExcel library ZIP files from <a href="">HERE</a>. After the download and extract the ZIP files put into the library directory which is already created by the "CodeIgniter" Framework.   
We are here to explain how to update a particular column in the uploaded XLS sheet and provided the updated sheet to download. This script is written in CodeIgniter framework and explain every thing how to create & manage the script.

Below is the Controller methods create a file (pimsheet.php) into the Controller Directory copy and paste all the code into the files and put into the "Controller" directory.

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

/*
* @Date      : March 28, 2017
* @Class     : Admin
* Created By : Vishal Nigam
* @Purpose   : This class is used for XLS related functionalities
*/

class Pimsheet extends CI_Controller {
  
  /*
    In This function __construct() function, you can load library/model/language/settings things over the entire class. if you described everything inside the function __construct() you don't have to load in every method.
  */

  public function __construct()
  {
    parent::__construct();  
    $this->load->library('session');           
    $this->load->library('PHPExcel'); 
    $this->load->model('pim_model');
    $this->load->helper(array('form', 'url'));
    $this->load->library('form_validation');
    $config['upload_path']   = 'uploads/PIMSheet/';
    $config['max_size']    = '15000'; 
    $config['allowed_types'] = 'xlsx|csv|xls|xlsm';
    $this->load->library('upload', $config);        
    $this->upload->initialize($config);
  }


   /*
  * @Date      : March 28, 2017
  * @Method    : index
  * Created By : Vishal Nigam
  * @Purpose   : This function is used for getting infomation on the Index Page .
  * @Param     : none
  * @Return    : none 
  */
    
  public function index()
  { 
    if($this->session->userdata('id'))
    {

        $this->data['title']          = 'Manage PIM Sheet';
        $this->data['countryLists']   = $this->common_model->get_countryList();
        $this->data['downloadlink']   = "";
        if ($this->input->server('REQUEST_METHOD') == 'POST'){
        
        $this->form_validation->set_rules('country_id', 'Country', 'required'); 
        $this->form_validation->set_rules('check_brand', 'Brand', 'required'); 
        $this->form_validation->set_rules('title_index', 'Title Index', 'required|numeric'); 
        $this->form_validation->set_rules('smartid_index', 'SmartID Index', 'numeric'); 
        $this->form_validation->set_rules('upload_sheet', 'PIM Sheet', 'callback_handle_upload');

        if ($this->form_validation->run() == TRUE) {          
          $brandID      = $this->input->post('check_brand');
          $ProductIndex   = $this->input->post('title_index');
          $SmartIdIndex   = $this->input->post('smartid_index'); 
          $sheetData = $this->generateSheet($brandID ,$_POST['upload_sheet_url'],$ProductIndex,$SmartIdIndex);    
          
          if(array_intersect(array("productName","title"),$sheetData[1])){
            unlink($_POST['upload_sheet_url']);
            $objjPHPExcel = new PHPExcel();                       
            $objjPHPExcel->getActiveSheet()->setTitle('Product List');
            $objjPHPExcel->setActiveSheetIndex(0);                    
            for($i=1;$i < count($sheetData);$i++){
              $r=1;
              foreach($sheetData[$i] as $key => $value){
                if($i==1 && $r==count($sheetData[$i])){
                  $objjPHPExcel->getActiveSheet()->getStyle('A1:'.$key)->applyFromArray(
                  array(
                      'font'    => array(
                              'bold'      => true
                          ),
                          'alignment' => array(
                              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT
                          ),
                          'borders' => array(
                              'top'     => array(
                                  'style' => PHPExcel_Style_Border::BORDER_THIN
                              )
                          ),
                          'fill' => array(
                              'type'       => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                              'rotation'   => 90,
                              'startcolor' => array(
                                  'argb' => 'FFA0A0A0'
                              ),
                              'endcolor'   => array(
                                  'argb' => 'FFFFFFFF'
                              )
                          )
                      )
                  );
                }
                  
                $objjPHPExcel->getActiveSheet()->setCellValue($key, $value);
                $r++;
              }
            }

            $filename=time().'.xls'; //save our workbook as this file name                  
            $objWriter = PHPExcel_IOFactory::createWriter($objjPHPExcel, 'Excel5');  
            $objWriter->save('./uploads/'.$filename);
            $this->data['downloadlink'] = base_url('uploads/'.$filename);           
          }else{
            unlink($_POST['upload_sheet_url']);
            $this->data['error'] = 'Please upload valid PIM Sheet'; 
          }                   
        }
              
      }
        
        $this->load->view('admin/pimsheet.php',$this->data);  
    }
    else
    {
      $this->data['title']  = 'Login';
      $this->load->view('admin/login.php',$this->data);   
    
    }
  }

 /*
  * @Date      : March 28, 2017
  * @Method    : handle_upload
  * Created By : Vishal Nigam
  * @Purpose   : This function is used to upload the XLS sheet on the server  
  * @Param     : none
  * @Return    : none 
  */

  function handle_upload(){

    if (isset($_FILES['upload_sheet']) && !empty($_FILES['upload_sheet']['name'])) {  

      if($this->upload->do_upload('upload_sheet')){       
        $upload_data    = $this->upload->data();
        $_POST['upload_sheet_url'] = $upload_data['full_path'];
        return true;
      }else{              
        $this->form_validation->set_message('handle_upload', $this->upload->display_errors());
        return false;
      }
    }else{
      return false;
    }
    
   }
  
    /*
  * @Created Date  : March 28, 2017
  * @Method        : generateSheet
  * Created By     : Vishal Nigam
  * @Purpose       : This function is used to generate the new XLS sheet after the updation.
  * @Param         : none
  * @Return        : none 
  */

  public function generateSheet($barndID,$sheetUrl,$ProductIndex = NULL,$SmartIdIndex = NULL ){
    
    $objPHPExcel = PHPExcel_IOFactory::load($sheetUrl);
    $dataTable   = array(); 
    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {    
      $i = 0;
      foreach ($worksheet->getRowIterator() as $row) {      
        $i++;
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
        $j=0;
        foreach ($cellIterator as $cell) {
          if (!is_null($cell)) {
            $j++;
            if(!is_null($cell->getCalculatedValue())){
              $dataTable[$i][$cell->getCoordinate()] = $cell->getCalculatedValue();           

              if($j==$ProductIndex && $i!=1){
                $hashKey = $this->pim_model->getHashByProductName($barndID,$cell->getCalculatedValue()); 
              }
              
              if($j==$SmartIdIndex && $i!=1){
                $dataTable[$i][$cell->getCoordinate()] = $hashKey;                 
              } 
            }
          }
        }
      }
    }
    return $dataTable;
  }
} 
?>

Below is the Model methods create a file (pim_model.php) into the Model Directory copy and paste all the code into the files and put into the "Model" directory.

<?php

if (!defined('BASEPATH')) exit('No direct script access allowed');

class Pim_model extends CI_Model
{
  function __construct()
  {
      parent::__construct();
      $this->load->library('session');  
  } 

  /*
  * @Created Date  : March 28, 2017
  * @Method :  getHashByProductName
  * @Created By: Vishal Nigam
  * @Purpose: This function is used to get the "Smart ID/Hash Code" form the DB
  * @Param: none
  * @Return: none 
  */

  public function getHashByProductName($brandId,$ProductName ){

    $ProductName = trim($ProductName);
    if(!empty($ProductName)){

      $this->db->select('products.title,widget_master.hash_key');
      $this->db->from('widget_master'); 
      $this->db->join('widget_products', 'widget_products.widget_id = widget_master.id');       
      $this->db->join('products', 'products.id = widget_products.product_id');    
      $this->db->where('products.product_website_id',$brandId);
      $this->db->where('widget_products.user_id','26');
      $this->db->like('products.title',trim($ProductName)); 
      $this->db->group_by('widget_products.product_id');  
      $this->db->limit(1, 0);       
      $query = $this->db->get()->result_array();
      if($query)
        return $query[0]['hash_key'];
      else    
        return "";
    }
    else    
      return "";  
  }
}

Below is the View create a file (pimsheet2.php) into the View Directory copy and paste all the code into the files and put into the "View" directory.

<form action="pimsheet/index" method="POST" class=""  enctype="multipart/form-data" id="generateSheet">
  <div>
    <div class="adminFldRw posit_err">
      <div class="admin_Labl">Upload PIM Sheet </div>
        <div class="fldWrap ">
      <input type="file" name="upload_sheet" id="upload_sheet" maxlength="90" accept=".csv,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"  />
            <span id="upload_sheet_err" class="error"></span>
    </div>
    </div>

    <div class="adminFldRw posit_err">
      <div class="admin_Labl">Cell Index of Product Title <span style="color:#FF0000"> * </span></div>
        <div class="adminInptTxt ">
      <input type="text" name="title_index" id="title_index" maxlength="40"/>
            <span id="title_index_err" class="error"></span>
    </div>
    </div>

  <div class="adminFldRw posit_err">
      <div class="admin_Labl">Cell Index of SmartID <span style="color:#FF0000"> * </span></div>
        <div class="adminInptTxt ">
      <input type="text" name="smartid_index" id="smartid_index" maxlength="40" value="" />
            <span id="smartid_index_err" class="error"></span>
    </div>
    </div>

   <div class="adminFldRw pTop10">
    <div class="admin_Labl">&nbsp;</div>
    <div class="left"><div class="left"><span class="franklin-g"><input type="image"  src= "images/btn_submit.png"/>&nbsp;</span><a href="<?php echo base_url();?>pimsheet/index" onclick="history.go(-1)"><img  src= "images/btn_cancel.png" border="0" /></a></div></div>  
    <?php if(!empty($downloadlink)){ ?>
    <div class="right"><a href="<?php echo $downloadlink; ?>" class="btn"> Download </a></div>
    <?php } ?>  
  </div>
</form>

Comments

Post a Comment

Popular posts from this blog

5 Simple steps to improve the performance of a CodeIgniter Site

5 Simple steps to improve the performance of a CodeIgniter applications. Speed Test, Identify error on the result, HTML output, Page Caching, and Gzip.  1.   Speed Test: First to test the speed of your website to using the tool like Pingdom. it is a free tool that helps to test your website performance from different locations. 2.   Identify error on the result: This Tool displayed all the result and list of files how is taking more time to load and the user can see the result over there check the files and found the root cause of the error and resolve. 3.   HTML output: if any white space exists in the HTML output, Hooks will perform this kind of job to remove white space. Go to "config" directory to open the "config.php" file and enable hooks: $config['enable_hooks'] = TRUE; Hooks provides the provision for extending the core of CodeIgniter, Hooks are defined in config path: application/config/hooks.php file. Each hook is specif...
Read more

Bigbasket - Redirect to the Bigbasket Product Detail Page without choosing the location

Please Use this URL for redirection from widget to Bigbasket: https://www.bigbasket.com/skip_explore/?c=1&amp;l=0&amp;s=0&amp;n=http://www.bigbasket.com/pd/285704/comfort-after-wash-anti-bacterial-fabric-conditioner-200-ml/ Here are the details of the variable which use in the query string: "c" stands for "City Name" "l" stands for "Location" "s" stands for "Street" "n" stands for "Buy Now URL of the PDP Page" Please have a look to this line of code which we implement for Bigbasket redirection without the use of an Iframe in the back-end. Bigbasket - Redirect to the Bigbasket Product Detail Page without choosing the location
Read more

Jquery Form Validation and Submission

We have created a simple script to validate the form using jQuery. jQuery provides the client-side validation of the form. For the jQuery validation we used to add the two library files: 1. jquery-1.10.2.js 2. jquery.validate.js Here are an example to display how to implement jQuery on the page, we have three fields which we have validated here: 1. fullname 2. email 3. mobile We have also checked the "fullname" and "email" uniqness with using the "remote" method. This validation is implemented on the "CodeIgniter" Framework here: <script id="demo" type="text/javascript"> $(document).ready(function() {     // validate signup form on keyup and submit for company profile     var validator = $("#cadminForm").validate({         rules: {             fullname: {                 ...
Read more