我正在尝试获取excel工作簿中包含数据的最后一行,问题是在某些工作表中我获得了正确的数据,但在其他工作表中,我没有,我试图通过使用getHighestDataRow()
而不是getHighestRow()
来更改代码,但我一直得到相同的结果,所以我将我的PHPExcel库从PHPExcel_1.7.9_doc更新为PHPExcel_1.8.0_doc,但结果也相同。有什么想法吗?
我想在循环中生成一个if语句,测试每一行是否为空。
$lastRowt = $excel2->getActiveSheet()->getHighestDataRow();
echo" $lastRowt ";
$lastRowt
一直是34,但现在只有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>
我以前在获取最高列时遇到过这个问题,原因是单元格中的格式和公式。即使单元格没有显示值,也不意味着该单元格为空。PHPExcel将读取看似空的单元格,但其中实际上包含一些格式或公式。
如果是这种情况,您可以清除内容下面的所有格式/公式。如果文件是在其他地方生成的,有时这是不可行的,因此我建议在创建读取器之后和加载文件之前添加以下代码行:$excel2->setReadDataOnly(true);
因此,该代码块应读作:
$excel2 = PHPExcel_IOFactory::createReader('Excel2007');
$excel2->setReadDataOnly(true);
$excel2 = $excel2->load('try.xlsx');