CODEWITHSUNDEEP
phpexcelphpexcelcells
Kartik Shenoy
Kartik Shenoy

Reputation: 328

Read excel sheet containing merged cells using PHPExcel

I want to read an excel sheet completely and using AJAX send each row to another page for processing. So I have used the following code for converting the excel sheet data into JSON array(Reference PHPExcel example provided in Library):

<?php
error_reporting(E_ALL);
set_time_limit(0);

date_default_timezone_set('Asia/Kolkata');
set_include_path(get_include_path() . PATH_SEPARATOR . 'PHPExcel-1.8/Classes/');
require_once 'PHPExcel/IOFactory.php';

$inputFileType = PHPExcel_IOFactory::identify($fileLocation);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly("SHEETNAME");
$objPHPExcel = $objReader->load($fileLocation);

$data = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
?>

Here $filelocation is the location of the uploaded file which is to be read for sending the rows individually using AJAX to another page. I am using $data in javascript as

DataToBeUploaded=<?php echo json_encode($data);?>;

But the excel sheet contains some merged cells so PHPExcel is not able to read the values in these merged cells. Hence values in these cells are read as NULL.

Is there a way where I can use the merged cells' upper left cell value for all of the subsequent cells? (Actually in my case cells are merged vertically only)

Eg. I have (Assume rows are numbered from 1 and columns from A)

merged cells excel sheet example

Here PHPExcel reads this as:

data[1][A]='abc'
$data[1][B]='123'

$data[2][A]=''
$data[2][B]='456'

$data[3][A]=''
$data[3][B]='789'

I want the snippet to result in these values:

data[1][A]='abc'
$data[1][B]='123'

$data[2][A]='abc'
$data[2][B]='456'

$data[3][A]='abc'
$data[3][B]='789'

Upvotes: 2

Views: 5830

Answers (1)

Kartik Shenoy
Kartik Shenoy

Reputation: 328

Referring to https://github.com/PHPOffice/PHPExcel/issues/643

I have written the following snippet:

$referenceRow=array();
for ( $row = 2; $row <= $noOfBooks; $row++ ){
     for ( $col = 0; $col < 7; $col++ ){
         if (!$objPHPExcel->getActiveSheet()->getCellByColumnAndRow( $col, $row )->isInMergeRange() || $objPHPExcel->getActiveSheet()->getCellByColumnAndRow( $col, $row )->isMergeRangeValueCell()) {
             // Cell is not merged cell
             $data[$row][$col] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow( $col, $row )->getCalculatedValue();

             $referenceRow[$col]=$data[$row][$col];  
             //This will store the value of cell in $referenceRow so that if the next row is merged then it will use this value for the attribute
          } else {
             // Cell is part of a merge-range
             $data[$row][$col]=$referenceRow[$col];  
             //The value stored for this column in $referenceRow in one of the previous iterations is the value of the merged cell
          }

      }
 }

This will give the result exactly as required

Upvotes: 7

Related Questions