Menu
i am trying to get the last row that contains data in an excel workbook the problem is in some sheets i get the correct data but in other sheets i don't , i tried to change my code by using getHighestDataRow() instead of getHighestRow() but i keep getting the same results so i updated my PHPExcel library from PHPExcel_1.7.9_doc to PHPExcel_1.8.0_doc but the same result too .
any ideas ?
i am thinking to make an if statement in a loop to test each row if it is empty or not .
$lastRowt = $excel2->getActiveSheet()->getHighestDataRow();
echo" $lastRowt ";
$lastRowt keep getting 34 but it is only 30 .
<html>
<?php
ini_set('max_execution_time', 300);
ini_set("memory_limit",-1);
require_once 'PHPExcel_1.7.9_doc/Classes/PHPExcel/IOFactory.php';
require_once 'include/adb.php';
$excel2 = PHPExcel_IOFactory::createReader('Excel2007');
$excel2 = $excel2->load('try.xlsx');
$sheetCount = $excel2->getSheetCount();
for ($x=0; $x<$sheetCount; $x++) {
echo"now $x <br>";
$excel2->setActiveSheetIndex($x);
$objWorksheet = $excel2->getActiveSheet();
$po = $objWorksheet->getCellByColumnAndRow(1, 7)->getValue();
$in = $objWorksheet->getCellByColumnAndRow(6, 7)->getValue();
$id = date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($objWorksheet->getCellByColumnAndRow(6, 8)->getValue()));
$esv = $objWorksheet->getCellByColumnAndRow(4, 19)->getValue();
$won = $objWorksheet->getCellByColumnAndRow(0, 21)->getValue();
$DTH = $objWorksheet->getCellByColumnAndRow(1, 21)->getValue();
$wn = $objWorksheet->getCellByColumnAndRow(3, 21)->getValue();
$rco = $objWorksheet->getCellByColumnAndRow(4, 21)->getValue();
$wc = $objWorksheet->getCellByColumnAndRow(5, 21)->getValue();
$rch = $objWorksheet->getCellByColumnAndRow(6, 21)->getValue();
$wd = $objWorksheet->getCellByColumnAndRow(0, 22)->getValue();
$cs = 1 ;
$query="INSERT INTO invoice
(saponum, invoicenum, invoicedate, esvnum,workordernum, dth, wellnum, regcode, wellcharge, rigcharge, workdescription, idc)VALUES
( '$po', '$in', '$id', '$esv', '$won','$DTH', '$wn', '$rco', '$wc', '$rch', '$wd', '$cs' )";
echo" query <br> $query <br>";
mysql_query($query);
$queryi="select idv from invoice where invoicenum = $in ";
echo" query <br> $in <br>haya de el qwery bta3et el idv $queryi ";
$idnt = mysql_query($queryi);
$outputidernt=mysql_fetch_array($idnt);
extract($outputidernt);
$lastRowt = $excel2->getActiveSheet()->getHighestDataRow();
echo"<br> $lastRowt <br>";
$rn = 25 ;
for($i=$lastRowt ; $i>=28 ; $i--){
$qty = $objWorksheet->getCellByColumnAndRow(0, $rn)->getValue();
echo "qty = $qty";
$item = $objWorksheet->getCellByColumnAndRow(1, $rn)->getValue();
echo "item = $item";
$describtion = $objWorksheet->getCellByColumnAndRow(2, $rn)->getValue();
echo "describtion = $describtion";
$unit = $objWorksheet->getCellByColumnAndRow(4, $rn)->getValue();
echo "unit = $unit";
$pric = $objWorksheet->getCellByColumnAndRow(5, $rn)->getValue();
echo "pric = $pric";
$rn++ ;
$queryit=" SELECT ids FROM pricelist WHERE itemnum = '$item' AND saponum = '$po' ";
echo" queryit <br> $queryit <br>";
$idniter = mysql_query($queryit);
$outputider=mysql_fetch_array($idniter);
extract($outputider);
$querys="INSERT INTO invoicestep ( ids , quantity , idv) VALUES ( '$ids','$qty','$idv' )";
echo" querys <br> $querys <br>";
mysql_query($querys);
}
}
?>
</html>
760
I have ran into this issue before when getting the highest column and was due to formatting and formulas in cells. Even if the cell doesn't display a value, that doesn't mean the cell is empty. PHPExcel will read cells that appear empty, but actually have some formatting or formula within it.
If this is the case for you, you could clear out all formatting/formulas below your content. Sometimes this isn't feasible if the file is generated else where, so therefore I recommend adding the following line of code after you create the reader and before you load the file: $excel2->setReadDataOnly(true);
So that block of code should read as:
$excel2 = PHPExcel_IOFactory::createReader('Excel2007');
$excel2->setReadDataOnly(true);
$excel2 = $excel2->load('try.xlsx');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
