Jump to content

php excell search for string and return cell value = a12


Paulqvz

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>";

 

Link to comment
Share on other sites

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 {

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.