How to check if cell is merged?

Topics: Developer Forum, Project Management Forum, User Forum
Nov 3, 2009 at 8:57 AM

Hello all,

I have a coding problem.

I have to save contents from excel5 file to db.

I have no problem with reading contents from excel.

But when for example 3 cells are merged i would like to duplicate content from first to another two.

Here's example:

merged (A1:A3) = some data

after reading unmarged cells looks like:

A1 = some data

A2 = ' '

A3 = ' '

I wuld like to have something like this after reading:

A1 = some data

A2 = some data

A3 = some data

How to chek if cell is merged to copy data to all merged cells?

Is there any answer

Thank you

jaskooo

 

 

 

Coordinator
Nov 3, 2009 at 9:28 AM
Edited Nov 3, 2009 at 9:33 AM

A call to $objPHPExcel->getActiveSheet()->getMergeCells() will return an array of all the merged ranges in a worksheet, so a block of code like the following (untested)

$cellAddress = 'A2';
$foundInRange = false;
foreach($objPHPExcel->getActiveSheet()->getMergeCells() as $range) {
   if ($objPHPExcel->getActiveSheet()->getCell($cellAddress)->isInRange($range)) {
      $rangeDetails = PHPExcel_Cell::splitRange($range);
      $result = $objPHPExcel->getActiveSheet()->getCell($rangeDetails[0][0])->getValue();
      $foundInRange = true;
      break;
   }
}
if (!$foundInRange) {
   $result = $objPHPExcel->getActiveSheet()->getCell($cellAddress)->getValue();
}

should allow you to search through all merged ranges for a given cell, returning the correct value whether it's in a merged range or not

Nov 3, 2009 at 10:42 AM

Ok I'm stuck again :)

Here's my code

$file = $_FILES['file_loaded']['tmp_name'];
			$objReader = PHPExcel_IOFactory::createReader('Excel5');
			$objPHPExcel = $objReader->load($file);
			$objWorksheet = $objPHPExcel->getActiveSheet();
			$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
			$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
			$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g.
			
			$data = Array();
			$start = 9;
			for ($row = $start; $row <= $highestRow; ++$row) {
			 				
				 $no = $objWorksheet->getCellByColumnAndRow(0, $row)->getCalculatedValue();
	
				 $div = $objWorksheet->getCellByColumnAndRow(1, $row)->getCalculatedValue();
	
				 $page = $objWorksheet->getCellByColumnAndRow(2, $row)->getValue();
				
				 $dept = $objWorksheet->getCellByColumnAndRow(3, $row)->getCalculatedValue();
				
				 $buyer_id = $objWorksheet->getCellByColumnAndRow(4, $row)->getCalculatedValue();
				
				 $upc = $objWorksheet->getCellByColumnAndRow(5, $row)->getCalculatedValue();
				
				 $item_name = $objWorksheet->getCellByColumnAndRow(6, $row)->getCalculatedValue();
				
				 $pack_size = $objWorksheet->getCellByColumnAndRow(7, $row)->getCalculatedValue();
				
				 $function_description = $objWorksheet->getCellByColumnAndRow(8, $row)->getCalculatedValue();
				
				 $gift_info = $objWorksheet->getCellByColumnAndRow(9, $row)->getCalculatedValue();
				
				 $special_price = $objWorksheet->getCellByColumnAndRow(10, $row)->getCalculatedValue();
				
				 $member_price = $objWorksheet->getCellByColumnAndRow(11, $row)->getCalculatedValue();
				
				 $effective_date_exception = $objWorksheet->getCellByColumnAndRow(13, $row)->getCalculatedValue();
				
				 $sample_pic_no = $objWorksheet->getCellByColumnAndRow(14, $row)->getCalculatedValue();
				
				 $special_photo_shooting_request = $objWorksheet->getCellByColumnAndRow(15, $row)->getCalculatedValue();
				
				 $effective_store = $objWorksheet->getCellByColumnAndRow(16, $row)->getCalculatedValue();
				
				 $change_sheet_submit_date = $objWorksheet->getCellByColumnAndRow(17	, $row)->getCalculatedValue();
				
				$dane[$row-$start] = array (
				"no"  => $no,
				"div" => $div,
				"page" => (string) $page,
				"dept"=> $dept,
				"buyer_id" => $buyer_id,
				"upc"=>$upc,
				"item_name" => $item_name,
				"pack_size" => $pack_size,
				"function_description" => $function_description,
				"gift_info" => $gift_info,
				"special_price" => $special_price,
				"member_price" => $member_price,
				"effective_date_exception" => $effective_date_exception,
				"sample_pic_no" => $sample_pic_no,
				"special_foto_shooting_request" => $special_photo_shooting_request,
				"effective_store" => $effective_store,
				"change_sheet_submit_date" => $change_sheet_submit_date
				);

After your answer i've made a functon:

public static function getCFM ($cellAddress)
    {
        $foundInRange = false;
    foreach($objPHPExcel->getActiveSheet()->getMergeCells() as $range) {
           if ($objPHPExcel->getActiveSheet()->getCell($cellAddress)->isInRange($range)) {
              $rangeDetails = PHPExcel_Cell::splitRange($range);
              $result = $objPHPExcel->getActiveSheet()->getCell($rangeDetails[0][0])->getValue();
              $foundInRange = true;
              break;
               }
        }
        if (!$foundInRange) {
           $result = $objPHPExcel->getActiveSheet()->getCell($cellAddress)->getValue();
        }
        return $result;
    }

How do i get cell address to use it as a param in getCFM function?


I think i shoul use something like this i nevery cell call:

$no = getCFM($objWorksheet->getCellByColumnAndRow(0, $row)->getCellAddress());

But function getCellAddress doesn't exists

Please help

 

Coordinator
Nov 3, 2009 at 11:24 AM

A getCellAddress() method doesn't exist, but getCoordinate() does

And you'd need to pass $objPHPExcel through to your function as well, otherwise it isn't in scope for your function

 

Also, the function only returns the cell value, not necessarily the calculated value, so you'd probably need an equivalent function (or an additional argument for your current function) to call getCalculatedValue() instead of getValue()

Nov 3, 2009 at 11:58 AM

It finally works thanks