/**
* Führt für alle Zeilen des Dokumentes processRow() aus
*/
public function process()
{
foreach ($this->sheet->getRowIterator() as $row) {
if ($row->getRowIndex() < $this->getOption('minRow')) {
$this->log .= 'Skip.minRow: ' . $row->getRowIndex() . "\n";
continue;
}
if ($row->getRowIndex() > $this->getOption('maxRow')) {
$this->log .= 'Break.maxRow: ' . $row->getRowIndex() . "\n";
break;
}
$this->log .= 'processRow(' . $row->getRowIndex() . ")\n";
$this->currentRow = $row;
$this->processRow($row);
}
}
function findGroups(PHPExcel_Worksheet $sheet)
{
$b = false;
$indexCellDAY = 0;
$rowIterator = $sheet->getRowIterator();
foreach ($rowIterator as $row) {
//поучаем итератор ячеек
$cellIterator = $row->getCellIterator();
//проходимся по всем ячейкам
foreach ($cellIterator as $cell) {
//удаляем пробелы вокруг и переводим в верхний регистр
$cell_value = mb_strtoupper(trim($cell->getFormattedValue()));
if ($b) {
echo $cell_value;
if (in_array($cell_value, $this->filter[1])) {
$this->saveColumn($cell->getColumn(), $cell->getRow(), $sheet, $indexCellDAY);
// $this->db->saveGroup($cell_value);
// $this->findSchedule($cell);
}
}
if ($cell_value == "ДЕНЬ") {
// echo $cell_value;
$b = true;
//получаем индекс строки ДЕНЬ
$indexCellDAY = $cell->getRow();
}
}
// echo $this->i++;
if ($b) {
return;
}
}
// $filter=$this->createFilter();
}
public function __construct(\PHPExcel_Worksheet $worksheet, $parent_filename)
{
$this->name = $this->genName($parent_filename, $worksheet->getTitle());
$row_it = $worksheet->getRowIterator();
$this->header = new \excel2sql\SqlHeader($row_it->current());
$this->entries = array();
for ($row_it->next(); $row_it->valid(); $row_it->next()) {
$rowindex = $row_it->current()->getRowIndex();
$this->entries[] = new \excel2sql\SqlEntry($rowindex, $this->header, $worksheet);
}
}
public function parse(\PHPExcel_Worksheet $sheet)
{
$rates = [];
foreach ($sheet->getRowIterator() as $row) {
$rowIndex = $row->getRowIndex();
$currencyCode = $sheet->getCell('B' . $rowIndex)->getCalculatedValue();
if (!$this->isCurrencyCode($currencyCode)) {
continue;
}
$date = new \DateTime();
$rate = ['type' => 'cash', 'date' => $date->format('Y-m-d'), 'curr' => $currencyCode, 'count' => (int) $sheet->getCell('C' . $rowIndex)->getValue(), 'buy' => number_format((double) $sheet->getCell('D' . $rowIndex)->getValue(), $this->getDecimals($currencyCode), '.', ''), 'sale' => number_format((double) $sheet->getCell('E' . $rowIndex)->getValue(), $this->getDecimals($currencyCode), '.', ''), 'nbu' => number_format((double) $sheet->getCell('F' . $rowIndex)->getValue(), $this->getDecimals($currencyCode, 'nbu'), '.', '')];
$rates[] = $rate;
}
return $rates;
}
/**
*
* @param PHPExcel_Worksheet $sheet
*/
private function loadSheet($sheet)
{
$name = $sheet->getTitle();
$entity_name = "Entity_{$name}";
if (!class_exists($entity_name, true)) {
print "entity class not found. skip. {$entity_name}";
return;
}
// ヘッダ
$rowIterator = $sheet->getRowIterator();
if (!$rowIterator->valid()) {
print "no data. skip. {$entity_name}";
return;
}
$col = array();
$row = $rowIterator->current();
$cellIterator = $row->getCellIterator();
while ($cellIterator->valid()) {
$cell = $cellIterator->current();
$col[] = trim($cell->getValue());
$cellIterator->next();
}
$rowIterator->next();
print "load {$entity_name} .... ";
// データを登録
while ($rowIterator->valid()) {
$row = $rowIterator->current();
$entity = $this->c->getEntity($entity_name);
$cellIterator = $row->getCellIterator();
$i = 0;
while ($cellIterator->valid()) {
$cell = $cellIterator->current();
$prop = $col[$i];
$entity->{$prop} = trim($cell->getValue());
$cellIterator->next();
$i++;
}
$entity->insert();
$rowIterator->next();
}
print $sheet->getHighestRow() - 1 . " rows loaded.\n";
return;
}
protected function readRowsWithMapping(ExcelSheet $excelSheet, array $mapping, $minRow = 1, $maxRow = NULL)
{
$nullValue = NULL;
$emptyRow = array_combine(array_values($mapping), array_fill(0, count($mapping), $nullValue));
$rows = array();
foreach ($excelSheet->getRowIterator($minRow) as $rowNum => $wsRow) {
if ($maxRow !== NULL && $rowNum > $maxRow) {
break;
}
$row = array();
foreach ($wsRow->getCellIterator() as $cell) {
$cellIndex = h::getColumnIndex($cell->getColumn());
$isEmpty = TRUE;
if (array_key_exists($cellIndex, $mapping)) {
$row[$mapping[$cellIndex]] = $this->readValue($cell, $isEmpty, $nullValue);
}
}
$rows[$rowNum] = array_replace($emptyRow, $row);
}
return $rows;
}
/**
* Retrieve row iterator
*
* @param \PHPExcel_Worksheet $sheet
* @return \PHPExcel_Worksheet_RowIterator
*/
public function rows(\PHPExcel_Worksheet $sheet)
{
$rows = $sheet->getRowIterator()->resetStart();
return $rows;
}
function findDay(PHPExcel_Worksheet $sheet)
{
$arr_temp = null;
$rowIterator = $sheet->getRowIterator();
foreach ($rowIterator as $row) {
//поучаем итератор ячеек
$cellIterator = $row->getCellIterator();
//проходимся по всем ячейкам
foreach ($cellIterator as $cell) {
//удаляем пробелы вокруг и переводим в верхний регистр
$cell_value = $this->getStrToUpper($cell);
// mb_strtoupper(trim($cell->getFormattedValue()));
//заполняем массив с названиями Day
if (in_array($cell_value, $this->filterDay)) {
foreach ($this->arr_merged_allCells as $currMergedRange) {
if ($cell->isInRange($currMergedRange)) {
//Находим позициию :,и выделяем часть от нее до конца
$temp = substr($currMergedRange, strpos($currMergedRange, ':'));
echo '$temp->' . $temp . "</br>";
$t = null;
//
for ($i = 0; $i < strlen($temp); $i++) {
echo '$i->' . $i . "</br>";
if (intval($temp[$i])) {
$t .= $temp[$i];
echo '$t->' . $t . "</br>";
}
}
}
}
$arr_temp[] = array("value" => $cell_value, "y" => $cell->getRow(), "x" => $cell->columnIndexFromString($cell->getColumn()), "z" => $cell->getColumn());
$this->arrFindDay[] = $arr_temp;
}
}
}
echo "<pre>";
echo print_r($arr_temp);
echo "</pre>";
return $arr_temp;
}
/**
* Write rows of the specified sheet
*
* @param PHPExcel_Shared_XMLWriter $objWriter
* @param PHPExcel_Worksheet $sheet
*/
private function _writeRows(PHPExcel_Shared_XMLWriter $objWriter, PHPExcel_Worksheet $sheet)
{
$number_rows_repeated = self::NUMBER_ROWS_REPEATED_MAX;
$span_row = 0;
$rows = $sheet->getRowIterator();
while ($rows->valid()) {
$number_rows_repeated--;
$row = $rows->current();
if ($row->getCellIterator()->valid()) {
if ($span_row) {
$objWriter->startElement('table:table-row');
if ($span_row > 1) {
$objWriter->writeAttribute('table:number-rows-repeated', $span_row);
}
$objWriter->startElement('table:table-cell');
$objWriter->writeAttribute('table:number-columns-repeated', self::NUMBER_COLS_REPEATED_MAX);
$objWriter->endElement();
$objWriter->endElement();
$span_row = 0;
}
$objWriter->startElement('table:table-row');
$this->_writeCells($objWriter, $row);
$objWriter->endElement();
} else {
$span_row++;
}
$rows->next();
}
}
/**
* get the data
*
* @access public
*/
public function read($valuetypes = array(), $skip = array(), $emptyvalues = FALSE)
{
/**
* @var array $array_data save parsed data from spreadsheet
*/
$array_data = array();
foreach ($this->_worksheet->getRowIterator() as $i => $row) {
$cellIterator = $row->getCellIterator();
//skip rows in array
if (!empty($skip) and in_array($i, $skip)) {
continue;
}
//if ($skip[$i] == $row->getRowIndex()) continue;
$rowIndex = $row->getRowIndex();
$values = array();
/**
* @var PHPExcel_Cell $cell
*/
foreach ($cellIterator as $cell) {
if (!empty($valuetypes) and array_key_exists($cell->getColumn(), $valuetypes)) {
$format = explode(':', $valuetypes[$cell->getColumn()]);
switch ($format[0]) {
case 'date':
$date = PHPExcel_Shared_Date::ExcelToPHPObject($cell->getValue());
$array_data[$rowIndex][$cell->getColumn()] = $date->format($format[1]);
break;
}
} else {
// check if is_null or empty
$value = $cell->getValue();
$array_data[$rowIndex][$cell->getColumn()] = (strtolower($value) == 'null' or empty($value)) ? null : $cell->getCalculatedValue();
}
// For check empty values
$values[] = $cell->getValue();
}
// Remove rows with all empty cells
if ($emptyvalues) {
$chechvalues = implode('', $values);
if (empty($chechvalues)) {
// Delete last array with empty values
array_pop($array_data);
}
}
}
return (array) $array_data;
}
