/**
* @param \PHPExcel_Worksheet_Row $row
* @return boolean
*/
public static function isRowEmpty($row)
{
foreach ($row->getCellIterator() as $cell) {
if ($cell->getValue()) {
return false;
}
}
return true;
}
public function valid()
{
$currentItem = current($this->rowResult);
if (!empty($currentItem)) {
return true;
} else {
$this->chunkFilter->setRows($this->position, 2000);
$objPHPExcel = $this->objReader->load($this->fileName);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$result = array();
for ($row = $this->position; $row <= $highestRow; $row++) {
$rowObj = new PHPExcel_Worksheet_Row($objWorksheet, $row);
$cellIterator = $rowObj->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$arrayValues = array();
foreach ($cellIterator as $cell) {
$arrayValues[] = str_replace('_x000D_', '', $cell->getValue());
}
$result[$row] = $arrayValues;
}
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
$this->rowResult = $result;
return !empty($result);
}
}
/**
* @param \PHPExcel_Worksheet_Row $row
*/
public function setDefaultAttributes($row)
{
$sheet = $row->getCellIterator()->current()->getWorksheet();
foreach ($this->_standardAttributes as $standardAttribute) {
if ($standardAttribute->column) {
$this->_defaultAttributes[] = new Attribute(['cell' => $sheet->getCell($standardAttribute->column . $row->getRowIndex()), 'standardAttribute' => $standardAttribute]);
}
}
}
/**
* Current PHPExcel_Worksheet_Row
*
* @return PHPExcel_Worksheet_Row
*/
public function current()
{
$row = new PHPExcel_Worksheet_Row($this->_subject, $this->_position);
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$arr = array();
foreach ($cellIterator as $cell) {
$arr[] = $cell->getValue();
}
return $arr;
}
/**
* @param \PHPExcel_Worksheet_Row $row
* @return EntryExcel
*/
public function createFromRow(\PHPExcel_Worksheet_Row $row)
{
$entryExcel = new EntryExcel();
foreach ($row->getCellIterator('A', 'F') as $cell) {
$setter = self::mapping($cell->getColumn());
if ($setter) {
$value = $cell->getValue();
$entryExcel->{$setter}($value);
}
}
return $entryExcel;
}
/**
* Process sheet row data
* @param \PHPExcel_Worksheet_Row $row
* @return array
*/
protected function processRowData(\PHPExcel_Worksheet_Row $row)
{
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
// This loops all cells,
$rowIndex = $row->getRowIndex();
$rowData = [];
foreach ($cellIterator as $cell) {
$cellData = $this->processCellData($cell);
$rowData[$cellData['column']] = $cellData['value'];
}
return ['rowIndex' => $rowIndex, 'data' => $rowData];
}
public function __construct(\PHPExcel_Worksheet_Row $row)
{
$cell_it = $row->getCellIterator();
$cell_it->setIterateOnlyExistingCells(false);
$this->fields = array();
for ($cell_it; $cell_it->valid(); $cell_it->next()) {
$val = $cell_it->current()->getValue();
if (empty($val)) {
continue;
}
$this->fields[] = new \excel2sql\SqlField($cell_it->current());
}
}
/**
* Parse the cells of the given row
* @return CellCollection
*/
protected function parseCells()
{
$i = 0;
$parsedCells = array();
try {
// Set the cell iterator
$cellIterator = $this->row->getCellIterator();
// Ignore empty cells if needed
$cellIterator->setIterateOnlyExistingCells($this->reader->needsIgnoreEmpty());
// Foreach cells
foreach ($cellIterator as $this->cell) {
// Check how we need to save the parsed array
$index = $this->reader->hasHeading() && isset($this->indices[$i]) ? $this->indices[$i] : $this->getIndexFromColumn();
// Check if we want to select this column
if ($this->cellNeedsParsing($index)) {
// Set the value
$parsedCells[(string) $index] = $this->parseCell($index);
}
$i++;
}
} catch (PHPExcel_Exception $e) {
// silently ignore the 'No cells exist within the specified range' error, but rethrow any others
if ($e->getMessage() != 'No cells exist within the specified range') {
throw $e;
}
// make sure that we return an empty CellCollection
$parsedCells = array();
}
// Return array with parsed cells
return new CellCollection($parsedCells);
}
/**
* Parse the cells of the given row
* @return CellCollection
*/
protected function parseCells()
{
$i = 0;
$parsedCells = array();
// Set the cell iterator
$cellIterator = $this->row->getCellIterator();
// Ignore empty cells if needed
$cellIterator->setIterateOnlyExistingCells($this->reader->needsIgnoreEmpty());
// Foreach cells
foreach ($cellIterator as $this->cell) {
// Check how we need to save the parsed array
$index = $this->reader->hasHeading() && isset($this->indices[$i]) ? $this->indices[$i] : $this->getIndexFromColumn();
// Check if we want to select this column
if ($this->cellNeedsParsing($index)) {
// Set the value
$parsedCells[$index] = $this->parseCell($index);
}
$i++;
}
// Return array with parsed cells
return new CellCollection($parsedCells);
}
/**
* Write cells of the specified row
*
* @param PHPExcel_Shared_XMLWriter $objWriter
* @param PHPExcel_Worksheet_Row $row
* @throws PHPExcel_Writer_Exception
*/
private function _writeCells(PHPExcel_Shared_XMLWriter $objWriter, PHPExcel_Worksheet_Row $row)
{
$number_cols_repeated = self::NUMBER_COLS_REPEATED_MAX;
$prev_column = -1;
$cells = $row->getCellIterator();
while ($cells->valid()) {
$cell = $cells->current();
$column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
$this->_writeCellSpan($objWriter, $column, $prev_column);
$objWriter->startElement('table:table-cell');
switch ($cell->getDataType()) {
case PHPExcel_Cell_DataType::TYPE_BOOL:
$objWriter->writeAttribute('office:value-type', 'boolean');
$objWriter->writeAttribute('office:value', $cell->getValue());
$objWriter->writeElement('text:p', $cell->getValue());
break;
case PHPExcel_Cell_DataType::TYPE_ERROR:
throw new PHPExcel_Writer_Exception('Writing of error not implemented yet.');
break;
case PHPExcel_Cell_DataType::TYPE_FORMULA:
try {
$formula_value = $cell->getCalculatedValue();
} catch (Exception $e) {
$formula_value = $cell->getValue();
}
$objWriter->writeAttribute('table:formula', 'of:' . $cell->getValue());
if (is_numeric($formula_value)) {
$objWriter->writeAttribute('office:value-type', 'float');
} else {
$objWriter->writeAttribute('office:value-type', 'string');
}
$objWriter->writeAttribute('office:value', $formula_value);
$objWriter->writeElement('text:p', $formula_value);
break;
case PHPExcel_Cell_DataType::TYPE_INLINE:
throw new PHPExcel_Writer_Exception('Writing of inline not implemented yet.');
break;
case PHPExcel_Cell_DataType::TYPE_NUMERIC:
$objWriter->writeAttribute('office:value-type', 'float');
$objWriter->writeAttribute('office:value', $cell->getValue());
$objWriter->writeElement('text:p', $cell->getValue());
break;
case PHPExcel_Cell_DataType::TYPE_STRING:
$objWriter->writeAttribute('office:value-type', 'string');
$objWriter->writeElement('text:p', $cell->getValue());
break;
}
PHPExcel_Writer_OpenDocument_Cell_Comment::write($objWriter, $cell);
$objWriter->endElement();
$prev_column = $column;
$cells->next();
}
$number_cols_repeated = $number_cols_repeated - $prev_column - 1;
if ($number_cols_repeated > 0) {
if ($number_cols_repeated > 1) {
$objWriter->startElement('table:table-cell');
$objWriter->writeAttribute('table:number-columns-repeated', $number_cols_repeated);
$objWriter->endElement();
} else {
$objWriter->writeElement('table:table-cell');
}
}
}
/**
* @param PHPExcel_Worksheet_Row $row
* @return array
*/
private function getRowCellValues(PHPExcel_Worksheet_Row $row)
{
$cells = array();
for ($i = 0; $i < 26; $i++) {
$cells[$i] = '';
}
$rowIterator = $row->getCellIterator();
foreach ($rowIterator as $cell) {
if ($cell === NULL) {
continue;
}
$cells[$this->getIndexForColumn($cell->getColumn())] = $cell->getValue();
}
$rowIterator->__destruct();
return $cells;
}
public function testGetCellIterator()
{
$row = new PHPExcel_Worksheet_Row($this->mockWorksheet);
$cellIterator = $row->getCellIterator();
$this->assertInstanceOf('PHPExcel_Worksheet_RowCellIterator', $cellIterator);
}
/**
* @param \PHPExcel_Worksheet_Row $row
* @param int $start_cell
* @return array
*/
private static function readRow(\PHPExcel_Worksheet_Row $row, $start_cell = 0)
{
$result = array();
$cell_iterator = $row->getCellIterator();
$cell_iterator->setIterateOnlyExistingCells(false);
$cell_index = -1;
//actually the first is 0, but we increment it in the beginning of the loop
$not_null_right_index = -1;
foreach ($cell_iterator as $cell) {
/* @var \PHPExcel_Cell $cell */
$cell_index++;
if ($cell_index < $start_cell) {
continue;
}
$result[] = $cell->getValue();
//remember position of the rightmost not null element, in order to remove null values later without additional loop
if ($result[count($result) - 1] !== null) {
$not_null_right_index = count($result) - 1;
}
}
//delete all rightmost null elements (Excel issue)
$result = array_slice($result, 0, $not_null_right_index + 1);
return $result;
}
