Read a table (defined by name)

Topics: User Forum
Jul 16, 2012 at 3:48 PM

Read a table (defined by name)
Friends, I have a table that has been defined by name, I need to read this and their properties (eg size)
I just read a cell is defined by name ...

$business_name = $objPHPExcel->getActiveSheet()->getCell('business_name')->getCalculatedValue();

Thanks for your feedback


Leer una tabla (definida por nombre)
Amigos, yo tengo una tabla que ha sido definida por nombre, yo necesito leer esta y sus propiedades (tamaño por ejemplo)
Yo solo se leer una celda definida por nombre con ...

$business_name = $objPHPExcel->getActiveSheet()->getCell('business_name')->getCalculatedValue();

Gracias por su colaboración

Jul 17, 2012 at 2:21 PM

Dear, please I need help, I'm working a new project

Coordinator
Jul 17, 2012 at 4:38 PM

You can access named ranges using a variety of methods:

For the PHPExcel_Worksheet object:

getCell('NAMED_RANGE') should work

namedRangeToArray('NAMED_RANGE')

For the PHPExcel object:

getNamedRanges() returns an array listing named ranges in the workbook

getNamedRange('NAMED_RANGE'[, PHPExcel_Worksheet])

 

If any of these are not working, then please indicate what

Jul 17, 2012 at 5:24 PM

Dear, MarkBaker

I tested your suggestions, but...

$value = $objPHPExcel->getActiveSheet()->getCell('file_type')->getCalculatedValue(); // Value of cell, but with table is fail
$ArrayValue = $objPHPExcel->getActiveSheet()->namedRangeToArray('file_type');        // Array of cell, but with table is fail
$objPHPExcel->getActiveSheet()->getNamedRanges('file_type');  // This are not working
$objPHPExcel->getActiveSheet()->getNamedRanges();  // This are not working

I need select a table (by range), but this only works for cell 

Coordinator
Jul 17, 2012 at 8:54 PM
Edited Jul 17, 2012 at 8:59 PM

If they aren't working, then the named range file_type doesn't exist in the workbook.

getNamedRanges() is a PHPExcel object method, not a PHPExcel_Worksheet method... named ranges are defined at the workbook-level, although they may reference a specific worksheet.

$objPHPExcel->getNamedRanges('file_type'); 
$objPHPExcel->getNamedRanges(); 

Please upload a sample workbook if you can so that I can take a look and see what is happening.

 

Jul 17, 2012 at 9:50 PM

Dear, MarkBaker

My code for test

<?php

	set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
	require_once('PHPExcel.php');
	require_once('PHPExcel/Reader/Excel2007.php');


	$objReader = new PHPExcel_Reader_Excel2007();
		$objReader->setReadDataOnly(true);
		$objReader->setLoadAllSheets();
	$objPHPExcel = $objReader->load("our_opportunities_v3_test.xlsx");	
		$objPHPExcel->setActiveSheetIndex(2);


	$loadedSheetNames = $objPHPExcel->getSheetNames();
	if (in_array('core_assets', $loadedSheetNames)):
		$file_type = $objPHPExcel->getActiveSheet()->getCell('file_type')->getCalculatedValue();
		// Our Opportunities
		if (!strcmp('gemi-xls01-03', $file_type)):
			// Connected
			$objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($objPHPExcel->getSheetByName('Connected')));
			$rows = $objPHPExcel->getActiveSheet()->getHighestRow();
			for( $row=8 ; $row<=$rows ; $row++ ):
				$cod = 							$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(2,$row)->getValue();
				$expProposalDate = 				$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(3,$row)->getValue();
				$expAwardDate =					$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(4,$row)->getValue();
				$country = 			utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(5,$row)->getValue());
				$endUser = 			utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(6,$row)->getValue()); 
				$industry = 		utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(7,$row)->getValue()); 
				$market = 			utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(8,$row)->getValue()); 
				$customer = 		utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(9,$row)->getValue()); 
				$GEPL1 = 						$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(10,$row)->getValue(); 
				$GEPL2 = 						$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(11,$row)->getValue(); 
				$leader = 			utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(12,$row)->getValue()); 
				$GEOffer = 						$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(13,$row)->getValue(); 
				$projectName = 		utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(14,$row)->getValue()); 
				$GEOpportunity = 				$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(15,$row)->getValue(); 
				$stage = 			utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(16,$row)->getValue()); 
				$issuesRoadblocks = utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(17,$row)->getValue()); 				
				$nextSteps =		utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(18,$row)->getValue()); 
				$YGRStatus = 					$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(19,$row)->getValue();
				echo $cod.' '.$expProposalDate.' '.$expAwardDate.' '.$country.' '.$endUser.' '.$industry.' '.$market.' '.$customer.' '.$GEPL1.' '.$GEPL2.
					'<br/>';					
			endfor;
			// Prospects
			$objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($objPHPExcel->getSheetByName('Prospects')));
			$rows = $objPHPExcel->getActiveSheet()->getHighestRow();
		else:
			echo 'This version file is cannot read in GEMI';
		endif;
	else:
		echo 'This file is not template of GEMI';
	endif;

?>

Please friend, I need help because it's only a demo, but it's an opportunities for get down a long time without job 

Coordinator
Jul 17, 2012 at 10:43 PM

Your problem is here:

$file_type = $objPHPExcel->getActiveSheet()->getCell('file_type')->getCalculatedValue();

The file_type named range is on the 'core_assets' sheet; but your currently active sheet at this point is the 'Connected' worksheet.

Either set the active sheet to 'core_assets' or use:

$file_type = $objPHPExcel->getSheetByName('core_assets')->getCell('file_type')->getCalculatedValue();

to retrieve the 'file_type' named range value directly from the 'core_assets' sheet.

Jul 18, 2012 at 1:50 AM
Edited Jul 19, 2012 at 2:20 PM

-> friend MarkBaker

but...
I need link the 'tabla2' object (inside 'Connected' sheet), because,
I need export to MySQL DB but I not have the rows and cols

please,  MarkBaker