[SOLVED] Iteration through an Excel file using COM

[suttercain]

Hi Guys,

 

I was able to succesfully connect to an excel spread sheet, each of the worksheets and sotre the information in an array. The problem I am having is this. The way code is currentley set up, I have to specifiy a range of cells that I would like to collect, exp. A4, D10. While the columns will always be the same, the amount of records won't be. Does anyone know how I can loop through the rows (records) in the excel spreadsheet until it finds null values. I am thinking a for loop, but am not 100% sure.

 

<?php
$filename = "C:\wamp\www\Excel\\".$_POST['file']."";
$sheet1 = "engine";

$excel_app = new COM("Excel.application") or Die ("Did not connect");

$Workbook = $excel_app->Workbooks->Open("$filename") or Die("Did not open $filename $Workbook");

$Worksheet = $Workbook->Worksheets($sheet1);
$Worksheet->activate;
$excel_cell = $Worksheet->Range("A4", "I28");//A way to get this automatically detected?
$excel_cell->activate;
$excel_result = $excel_cell->value;
foreach ($excel_cell as $value) { //loop through the array
    echo "$value<br />"; //display the cells based on the range() perimeters
}
?>

 

Any suggestions would be great appreciated.

 

Thanks.

 

SC

[Psycho]

Just create a while loop to grab one row at a time and exit the loop if a row is empty. BY the way I noticed that you have:

 

$excel_result = $excel_cell->value;

foreach ($excel_cell as $value)

 

Shouldn't you do a foreach on $excel_result?

 

Anyway, try this (not tested):

<?php

$filename = "C:\wamp\www\Excel\\".$_POST['file']."";
$sheet1 = "engine";

$excel_app = new COM("Excel.application") or Die ("Did not connect");

$Workbook = $excel_app->Workbooks->Open("$filename") or Die("Did not open $filename $Workbook");

$Worksheet = $Workbook->Worksheets($sheet1);
$Worksheet->activate;

$row = 4;

while ($row !== false) {

  $range1 = 'A' . $row;
  $range2 = 'I' . $row;

  $excel_cell = $Worksheet->Range($range1, $range2);
  $excel_cell->activate;
  $excel_result = $excel_cell->value;

  //Verify the first column has a value (can check the add'l fields if needed)
  if ($excel_result[0]) {

    //Display or process the data
    foreach ($excel_result as $value) { //loop through the array
      echo "$value<br />"; //display the cells based on the range() perimeters
    }

  } else { //Exit the loop
    $row = false;
  }

?>

[suttercain]

I Tried the above code, adding a closing bracket. I got this error:

 

Warning: main() [function.main]: Can only handle single dimension variant arrays (this array has 2) in C:\wamp\www\Excel\search.php on line 28

 

Fatal error: Uncaught exception 'Exception' with message 'Object of type variant did not create an Iterator' in C:\wamp\www\Excel\search.php:28 Stack trace: #0 C:\wamp\www\Excel\search.php(28): unknown() #1 {main} thrown in C:\wamp\www\Excel\search.php on line 28

[Psycho]

Well, my logic was sound, but the implementation was not. This is tested.

 

The way I have written it, if the first value in a row is empty, then it is assumed that the previous row was the last row of data and it does not proces any more rows. You can change that logic as you see fit.

 

<?php

$filename = "C:\\wamp\\www\\Excel\\".$_POST['file']."";
$sheet1 = "engine";

$excel_app = new COM("Excel.application") or Die ("Did not connect");

$Workbook = $excel_app->Workbooks->Open($filename) or Die("Did not open $filename $Workbook");

$Worksheet = $Workbook->Worksheets($sheet1);
$Worksheet->activate;


echo '<table border="1">';

$Worksheet = $Workbook->Worksheets($sheet1);
$Worksheet->activate;

$row = 4;  //Starting row

while ($row!==false) {

    //Create start and end points for the row
    $range1 = 'A' . $row;
    $range2 = 'I' . $row;

    //Get data for the current row
    $excel_cell = $Worksheet->Range($range1, $range2);
    $excel_cell->activate;

    echo "<tr>";

    //Process the row
    $first_col = true;
    foreach ($excel_cell as $value) {

        //test if the first column has a value
        if ($first_col) {
            //If the first value is not null, increase row by 1, else set to false
            $row = ($value)? $row + 1 : false;
            $first_row = false;
        }

        if ($row!==fale) {
            echo "<td>$value</td>";
        }
    }
    echo '</tr>';
}

echo '</table>';

?>

[suttercain]

Hi mjdamato,

 

Thanks for the help. I tried running the above script on the excel file and it echoed the first assigned row (Row 4) perfectly. But when it got time to echo the second row, row 5, it echoed row one and then quit executing.

 

I tried playing with it but can't seem to fix it.

 

SC

[Psycho]

Well it worked for me. Do you have anything entered in cell A5? If not, it will stop at the record on row 4 for the reasons I stated above. If you want, upload your excel file and I'll see if I can find the problem.

[suttercain]

Here's a snap shot.

 

So what it is doing, is echoing all of row 4, and then row 2, then nothing else.

 

[Psycho]

Um, yeah. I don't feel like entering all of that into a spreadsheet. Would you please upload the file to the forum if you would like some assistance?

[Psycho]

It appears I did have a last minute edit to my code before posting that may have fouoled things up. These two lines are repeated:

$Worksheet = $Workbook->Worksheets($sheet1);
$Worksheet->activate;

 

Try removing one set.

[suttercain]

Hi mjdamato,

 

Okay it's monday and I am back to work on this. I tried removing the "$Worksheet = $Workbook->Worksheets($sheet1);

$Worksheet->activate;" and I am still getting the same error.

 

What I did though is make a basic 5 column excel file with data filled in A through E then down to row 15.

 

 

But when I run the code from let's say row 4, I get this:

 

Here is the basic excel file with a single worksheet:

http://www.shannoncronin.com/Excel/single.xls

 

 

Oh yeah, and the code:

<?php
//print_r($_POST);
$filename = "C:\wamp\www\Excel\\single.xls";
$sheet1 = "Sheet1";

$excel_app = new COM("Excel.application") or die("Did not connect");

$Workbook = $excel_app->Workbooks->Open($filename) or die("Did not open $filename $Workbook");



echo '<table border="1">';

$Worksheet = $Workbook->Worksheets($sheet1);
$Worksheet->activate;

$row = 4;  //Starting row

while ($row!==false) {

    //Create start and end points for the row
    $range1 = 'A' . $row;
    $range2 = 'E' . $row;

    //Get data for the current row
    $excel_cell = $Worksheet->Range($range1, $range2);
    $excel_cell->activate;

    echo "<tr>";

    //Process the row
    $first_col = true;
    foreach ($excel_cell as $value) {

        //test if the first column has a value
        if ($first_col) {
            //If the first value is not null, increase row by 1, else set to false
            $row = ($value)? $row + 1 : false;
            $first_row = false;
        }

        if ($row!==false) {
            echo "<td>$value</td>";
        }
    }
    echo '</tr>';
}

echo '</table>';

//REPEAT FOR ADDITIONAL WORKSHEETS
/*$Worksheet = $Workbook->Worksheets($sheet2);
$Worksheet->activate;
$excel_cell = $Worksheet->Range("C40");
$excel_cell->activate;
$excel_result = $excel_cell->value;
if (is_null($excel_result)) echo "The field is NULL.";
if (isset($excel_result)) echo "The field IS SET.";*/

#To close all instances of excel:
$Workbook->Close;
unset($Worksheet);
unset($Workbook);
$excel_app->Workbooks->Close();
$excel_app->Quit();
unset($excel_app);
?>

[suttercain]

Alright, so check it out.

 

I renamed the data in the cells to corrospond to their row/cloumn. a1 is a1, c2 is c2, etc.

 

I started set the $row to 2 and this is what it outputted. It is skipping 4 rows of data....

 

 

If I set the $row at 1, it'll echo row 6 and 11.

[suttercain]

Alright, so trying to trouble shoot I echeod range1 and range2 to see if they were increment.... they are, kind of... Additionally, Row 1, 6, 11 are being echoed into the table.... but why is it going up every fourth row?

 

A1 and E1

A1 and E1

A1 and E1

A1 and E1

A1 and E1

A6 and E6

A6 and E6

A6 and E6

A6 and E6

A6 and E6

A11 and E11

A11 and E11

A11 and E11

A11 and E11

A11 and E11

A16 and E16

A16 and E16

A16 and E16

A16 and E16

A16 and E16

A21 and E21

A21 and E21

A21 and E21

A21 and E21

A21 and E21

[Psycho]

Ok, I got it to work using the excel file you linked to above. The problem I had at first was that the test excel file you provided had a sheet name of "Sheet1" and the script is using a sheet name of "engine". I am getting exactly the same number fo rows in the output as exist in the excel file. I don't see anything in the code you posted to explain why rows wiould be getting skipped.

 

Anyway here is the EXACT code I used on the test excel file you provided (with the file in the root of C, modify the path accordingly):

<?php

$filename = "c:\\single.xls";
$sheet1 = "Sheet1";

$excel_app = new COM("Excel.application") or Die ("Did not connect");

$Workbook = $excel_app->Workbooks->Open($filename) or Die("Did not open $filename $Workbook");
$Worksheet = $Workbook->Worksheets($sheet1);
$Worksheet->activate;

echo '<table border="1">';

$row = 2;

while ($row!==false) {

    $range1 = 'A' . $row;
    $range2 = 'E' . $row;

    $excel_cell = $Worksheet->Range($range1, $range2);
    $excel_cell->activate;

    echo "<tr>";

    //Process the row
    $first_row = true;
    foreach ($excel_cell as $value) {

        //test if the first column has a value
        if ($first_row) {
            $row = ($value)? $row + 1 : false;
            $first_row = false;
        }

        if ($row!==fale) {
            echo "<td>$value</td>";
        }
    }

    echo '</tr>';

}

echo '</table>';

$Workbook->Close(false);
unset($Worksheet);
unset($Workbook);
$excel_app->Workbooks->Close();
$excel_app->Quit();
unset($excel_app);

?>

 

Here is the output I received (note that I modified the cell content to indicate the column/row values):

data 1-1    data 2-1    data 3-1    data 4-1    data 5-1 
data 1-2    data 2-2    data 3-2    data 4-2    data 5-2 
data 1-3    data 2-3    data 3-3    data 4-3    data 5-3 
data 1-4    data 2-4    data 3-4    data 4-4    data 5-4 
data 1-5    data 2-5    data 3-5    data 4-5    data 5-5 
data 1-6    data 2-6    data 3-6    data 4-6    data 5-6 
data 1-7    data 2-7    data 3-7    data 4-7    data 5-7 
data 1-8    data 2-8    data 3-8    data 4-8    data 5-8 
data 1-9    data 2-9    data 3-9    data 4-9    data 5-9 
data 1-10   data 2-10   data 3-10   data 4-10   data 5-10 
data 1-11   data 2-11   data 3-11   data 4-11   data 5-11 
data 1-12   data 2-12   data 3-12   data 4-12   data 5-12 
data 1-13   data 2-13   data 3-13   data 4-13   data 5-13 
data 1-14   data 2-14   data 3-14   data 4-14   data 5-14 

[suttercain]

NICE, it's working! Thank you very much!

[Psycho]

Don't forget to mark the thread as solved. So, did you figure out why it was skipping rows?

[suttercain]

No it was weird. I did have the worksheet set to "Sheet1" but it was still doing it. After your last post though, that fixed what ever was causing it.