Search and replace

Topics: Developer Forum, User Forum
Oct 16, 2011 at 5:52 PM

How can i perform search and replace action?

I need to fill in xls file from template. Template can be modified completely, so cols and rows numbers are changeable.

So i have cells filled with tags. For example   #1# or #2# or #10# etc. So that tags must be replaced with info.

 

Thanks in advance

Oct 16, 2011 at 5:55 PM

So i need some search function to find cell number with specified text or substring.

Oct 17, 2011 at 9:45 AM

Well, you can make it yourself, perhaps this little snippet can help you. It basically builds a list of cell values on the first sheet, and provides a search function that returns the coordinates of a cell matching a search query. Don't know is this is the best way, but it suits my needs ;)

 

	/**
	 * build nice list of cell values, to be able to map headers to them
	*/
	public function getCellValues($force = false){
		if ( !is_null($this->cellValues) && $force === false ){
			return $this->cellValues;
		}
		$currentIndex = $this->objPHPExcel->getActiveSheetIndex();
		$this->objPHPExcel->setActiveSheetIndex(0);


		$sheet = $this->objPHPExcel->getActiveSheet();
		$highestColumn = $sheet->getHighestColumn(); //e.g., 'G'
		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //e.g., 6
		$highestRow= $sheet->getHighestRow();

		$this->cellValues = array();
		for ( $i =0 ; $i < $highestColumnIndex; $i++ ){
			$column = PHPExcel_Cell::stringFromColumnIndex($i);
			for ( $j = 1; $j <= $highestRow; $j++ ){
				$this->cellValues[$column . $j] = $sheet->getCellByColumnAndRow($i, $j)->getValue();
			}
		}
		$this->objPHPExcel->setActiveSheetIndex($currentIndex);
		return $this->cellValues;
	}

	/**
	 * returns cell by value. Be carefull, could be ambigous, only use
	 * if you really know what you are doing
	 */
	public function getCellByValue($search) {
		$nonPrintableChars = array("\n", "\r", "\t", "\s");
		$search = str_replace($nonPrintableChars, '', $search);
		foreach ( $this->getCellValues() as $cell => $value ){
			if ( strcasecmp(str_replace($nonPrintableChars, '', $value), $search) == 0  ){
				return $cell;
			}
		}
		return false;
	}

Oct 18, 2011 at 10:46 AM

Could you post some FAQ where to add this code and how to use it? :-)

Oct 18, 2011 at 10:57 AM
Edited Oct 18, 2011 at 10:58 AM

I have a big class that generates my excel sheets. But basically you need a class that instatiates a PHPExcel object, and stores it in $this->objPHPExcel

forinstance:

 

/* load excel library */
require('PHPExcel.php');
require('PHPExcel/IOFactory.php');

class bla { protected $objPHPExcel;
protected $cellValues;

 

 

	function __construct($filename){
		$this->objPHPExcel = PHPExcel_IOFactory::load($filename);
	}
}

 

you could add these functions to the class.  Then you might do the following:

 

$bla = new bla('somefile.xlsx');
$cell = $bla->getCellByValue('some search term');

var_dump($cell);

hope this help. Please note, my example function only searches the worksheet at index 0. That was enough for my application. But you could always modify the function to iterate through the worksheets, and include the worksheet id in the array key for $this->cellValues.

Oct 18, 2011 at 3:25 PM

Search works, but i can't open exported file, excel says that file corrupted.

Used code:

Changed 

protected $objPHPExcel; to public.

 

$template = $dir.'gen/xls/'.$type.'.xlsx';
$bla = new bla($template);
$objWorksheet = $bla->objPHPExcel->getActiveSheet();
foreach($r as $k => $v) {
$cell = $bla->getCellByValue('#'.$k.'#');
  if($cel!='') $objWorksheet->setCellValue($cell, $v);
}

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($bla->objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

Oct 18, 2011 at 3:37 PM

hmm interesting. Can you try saving the output file and opening it in a text editor? perhaps php is throwing in some free warnings or notices. ;)

Dec 11, 2013 at 7:43 AM
i am unable to use this function searches for the worksheet other than index 0.for all sheets
pls hlp
May 30, 2014 at 1:02 AM
thanks all you genius.
i make some changes to return multi searched cells and to trace all my worksheets.
here is my whole example:
    require_once dirname ( __FILE__ ) . "/customclasses/PHPExcel.php";

// https://phpexcel.codeplex.com/discussions/276067
class myExcelClass {
    public $objPHPExcel;
    public $cellValues;
    function __construct($filename) {
        $this->objPHPExcel = PHPExcel_IOFactory::load ( $filename );
    }
    
    public function getCellValues($currSheet) {
        $sheet = $currSheet;
        
        // echo "current sheet:".$sheet->getTitle()."<br />";
        
        $highestColumn = $sheet->getHighestColumn (); // e.g., 'G'
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString ( $highestColumn ); // e.g., 6
        $highestRow = $sheet->getHighestRow ();
        
        $this->cellValues = array ();
        for($i = 0; $i < $highestColumnIndex; $i ++) {
            $column = PHPExcel_Cell::stringFromColumnIndex ( $i );
            for($j = 1; $j <= $highestRow; $j ++) {
                $this->cellValues [$column . $j] = $sheet->getCellByColumnAndRow ( $i, $j )->getValue ();
            }
        }
        return $this->cellValues;
    }
    

    public function getCellByValue($sheet, $search) {
        $nonPrintableChars = array (
                "\n",
                "\r",
                "\t",
                "\s" 
        );
        
        // return multi cells
        $retCells = array ();
        
        $search = str_replace ( $nonPrintableChars, '', $search );
        foreach ( $this->getCellValues ( $sheet ) as $cell => $value ) {
            if (strcasecmp ( str_replace ( $nonPrintableChars, '', $value ), $search ) == 0) {  
                array_push ( $retCells, $cell );
            }
        }
        return $retCells;
    }
}

$tempfile = "tmp/tempReport.xls";
$outputfile = "tmp/outputReport" . uniqid () . ".xls";

$myexcel = new myExcelClass ( $tempfile );

// replace cwinl, badguy with  888, 999
$r = array (
        "cwinl" => "888",
        "badguy" => "999" 
);


foreach ( $myexcel->objPHPExcel->getWorksheetIterator () as $sheet ) {
    /*
     * $tmp = $myexcel->getCellValues($sheet); 
     * var_dump($tmp);
     */
    
    foreach ( $r as $k => $v ) {
        $cells = $myexcel->getCellByValue ( $sheet, '#' . $k . '#' );
        
        foreach($cells as $cell) {
            $sheet->setCellValue ( $cell, $v );
        }
    }
}



// save
$objWriter = PHPExcel_IOFactory::createWriter ( $myexcel->objPHPExcel, 'Excel5' );
$objWriter->save ( $outputfile );

if (file_exists ( $outputfile )) {
    header ( 'Pragma: public' );
    header ( 'Expires: 0' );
    header ( 'Content-Encoding: none' );
    header ( 'Cache-Control: must-revalidate, post-check=0, pre-check=0' );
    header ( 'Cache-Control: public' );
    header ( 'Content-Type: application/vnd.ms-excel; charset=utf-8' );
    header ( 'Content-Disposition: attachment; filename=' . pathinfo ( $outputfile, PATHINFO_BASENAME ) );
    header ( 'Content-Transfer-Encoding: binary' );
    header ( 'Content-Length: ' . filesize ( $outputfile ) );
    readfile ( $outputfile );
    
    unlink ( $outputfile );
} else {
    echo '<script>alert(\'export error, file not exists!\')</script>';
}