php excell search for string and return cell value = a12

[Paulqvz]

Hi there

 

I have started using php excell and getting most of it right. 

But i am struggling to search active sheet and return what cell the string is in. below is my code.

 

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
set_time_limit(0);



$directory = "/var/www/html/csvex/xls/";
$formatdirectory = "/var/www/html/csvex/toformat/";
$finishedformatdirectory = "/var/www/html/csvex/formatted/";
$savanadirectory = "/var/www/html/iretire/files/upload/1/";
$main = "/var/www/html/csvex/";


$files = scandir($directory, SCANDIR_SORT_DESCENDING);

$newest_file = $files[0];
echo $newest_file;
echo "<br>";
$oldfile = $directory.$newest_file;
$newfile = $formatdirectory.$newest_file;
$changedfile = $finishedformatdirectory.$newest_file;
$crmfile = $savanadirectory.$newest_file;
echo $oldfile;
echo "<br>";
echo $newfile;

echo "<br>";
if( !rename($oldfile, $newfile) ) {  
    echo "File can't be moved!";  
}  
else {  
    echo "File has been moved!";  
} 
//format the file


date_default_timezone_set('Europe/London');
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');

include 'PHPExcel/IOFactory.php';

$fileType = 'Excel5';
$fileName = $newfile;

// Read the file
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objPHPExcel = $objReader->load($fileName);
//change font
$objPHPExcel->getActiveSheet()->getStyle('a1:a6')->applyFromArray(
$styleArray = array(
'font' => array(
'bold' => true
)
)
);
$objPHPExcel->getActiveSheet()->getStyle('a6:aa6')->applyFromArray(
$styleArray = array(
'font' => array(
'bold' => true
)
)
);
$objPHPExcel->getActiveSheet()->getStyle('n16:aa16')->applyFromArray(
$styleArray = array(
'font' => array(
'bold' => true
)
)
);
//get INACTIVE MEMBERS
$foundInCells = array();
$searchValue = 'INACTIVE MEMBERS';
foreach ($objPHPExcel->getActiveSheet() as $worksheet) {
    $ws = $worksheet->getTitle();
    foreach ($worksheet->getRowIterator() as $row) {
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(true);
        foreach ($cellIterator as $cell) {
            if ($cell->getValue() == $searchValue) {
                $foundInCells[] = $ws . '!' . $cell->getCoordinate();
            }
        }
    }
}
echo "<br>";
echo "<br>";
echo "this is where - - ";
var_dump($foundInCells);
echo "<br>";
echo "<br>";
//
$objPHPExcel->getActiveSheet()->getStyle('a20')->applyFromArray(
$styleArray = array(
'font' => array(
'bold' => true
)
)
);

its located at //Get inactive members.

another one i am struggling with is to see if file exists if not then do nothing if it does run the code.

[requinix]

foreach ($objPHPExcel->getActiveSheet() as $worksheet) {

foreach is only for multiple values. Does getActiveSheet() return multiple values?

 

1 hour ago, Paulqvz said:

another one i am struggling with is to see if file exists if not then do nothing if it does run the code.

I don't see anything in your code that tries to do this. If you need help making code work then you're going to have to post the code.

[Paulqvz]

this pasrt of the code is where i am trying to search for the string

 

//get INACTIVE MEMBERS
$foundInCells = array();
$searchValue = 'INACTIVE MEMBERS';
foreach ($objPHPExcel->getActiveSheet() as $worksheet) {
    $ws = $worksheet->getTitle();
    foreach ($worksheet->getRowIterator() as $row) {
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(true);
        foreach ($cellIterator as $cell) {
            if ($cell->getValue() == $searchValue) {
                $foundInCells[] = $ws . '!' . $cell->getCoordinate();
            }
        }
    }
}
echo "<br>";
echo "<br>";
echo "this is where - - ";
var_dump($foundInCells);
echo "<br>";
echo "<br>";

 

[Paulqvz]

24 minutes ago, requinix said:

foreach ($objPHPExcel->getActiveSheet() as $worksheet) {

foreach is only for multiple values. Does getActiveSheet() return multiple values?

 

I don't see anything in your code that tries to do this. If you need help making code work then you're going to have to post the code.

got this right with 

 $q = count(glob("$directory/*")) == 0;

    if ($q) {
        echo "the folder is empty"; 
    } else {

 

[requinix]

You're correct, that code does try to search the active worksheet for a cell containing a value.

[Paulqvz]

Just now, Paulqvz said:

got this right with 

 $q = count(glob("$directory/*")) == 0;

    if ($q) {
        echo "the folder is empty"; 
    } else {

 

oreach is only for multiple values. Does getActiveSheet() return multiple values?   how else will i search the excell for the string - 'INACTIVE MEMBERS' and return in what cell it is

[Paulqvz]

1 minute ago, requinix said:

You're correct, that code does try to search the active worksheet for a cell containing a value.

but it does not return anything - says there is nothing although it is in a20?

 

[requinix]

3 hours ago, Paulqvz said:

oreach is only for multiple values. Does getActiveSheet() return multiple values?   how else will i search the excell for the string - 'INACTIVE MEMBERS' and return in what cell it is

Pay very close attention to the question and try to answer it directly:

Does getActiveSheet() return multiple values?

[Paulqvz]

does getActiveSheet() return multiple values? - yes - not to be forward - but you keep asking questions but never give answers or point in a direction, are there any other people on this forum that actually code in php?

[requinix]

I keep asking that question because you don't know the answer.

19 minutes ago, Paulqvz said:

does getActiveSheet() return multiple values? - yes

No, actually, it does not. getActiveSheet() returns a single PHPExcel_Worksheet object - as seen in the other places in your code where you call that method.

Which means this

foreach ($objPHPExcel->getActiveSheet() as $worksheet) {

does not make sense.

[Paulqvz]

foreach ($objPHPExcel->getActiveSheet() as $worksheet) { exactly why i need guidiance . it one sheet with lotsa data all rows filled int till g500

 

but i know that word is somewhere in column A so i want to search Column A for that word.

[requinix]

Right now, with the foreach loop problem dealt with, the code will search the entire sheet for the cell. Have you confirmed that part works? If so then you can make the change to the code so it only needs to search the first column.