I'm using PHPExcel to load xls files into a mysql database. Everything is working fine except for one thing: the PHPExcel reads ALL rows in the worksheet, even the blank ones and if, for example, my xls has 100 rows, the reader loops all the way down to row #3359 (which I suppose is the default number of rows for a blank worksheet).
What makes the reader count even the blank rows? This is my code:
$objPHPExcel = PHPExcel_IOFactory::load($path);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
$rowsadded=0;
$begin_row=5; // 1st line of data in excel file
for ($row = $begin_row; $row <= $highestRow; ++ $row) {
$val=array();
for ($col=0; $col < $highestColumnIndex; $col++) {
$cell = $objWorksheet->getCellByColumnAndRow($col, $row);
$val[] = $cell->getValue();
}
if ($val[0]<>'' && $val[1]<>'') { //check that row contains data before inserting
$rowsadded++;
$sql1 = sprintf("INSERT INTO aitisi (name_u, onoma_u, asf_gr, code, compid, im_egr, im_exp, symb, programa, cost, showcost, omadiko, ar_tayt, afm, dieyth, poli, til_st, til_kin, ep_te1, on_te1, ep_te2, on_te2, ep_te3, on_te3, ep_te4, on_te4, name_us, onoma_us) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($val[0], "text"),
GetSQLValueString($val[1], "text"),
...
GetSQLValueString($val[11], "text"),
GetSQLValueString($val[12], "text"),
GetSQLValueString($val[13], "text"),
GetSQLValueString($val[14], "text"),
GetSQLValueString($val[15], "text"),
GetSQLValueString($val[16], "text"),
GetSQLValueString($val[17], "text")).';';
$result = mysql_query($sql1) or die(mysql_error());
$field_id = mysql_insert_id();
}
I used this to filter out empty rows. Although i print them on my screen to be edited maybe it'll be of some use to you:
foreach($worksheet->getRowIterator() as $row) {
$range = 'A'.$row->getRowIndex().':'.$highestColumn.$row->getRowIndex();
$rowData = $worksheet->rangeToArray( $range, NULL,TRUE,TRUE,TRUE);
$rowData = $rowData[$row->getRowIndex()];
if(implode("",$rowData) != "") {
foreach($rowData as $column => $value){}
}
}
3359 is not the default number of rows for a blank worksheet.... it means that whoever created the worksheet actually created it with those blank lines (probably inadvertently). The reader doesn't care whether a row is blank or not, you've told it to read the worksheet and it does exactly that.
Unless you know exactly how many rows you want to read before loading the workbook (in which case, you can apply a read filter to read just those rows), you'll need to test each row to see if it's blank or not within your for loop.
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