/** * 文書のデータを配列に取得する */ public function readData_multi(Schemata $schemata) { $arrRtn = array(); //Excelファイルを開く $flg = $this->ext != self::EXT_EXCEL_BIFF; //xlsxモード or xlsモード $objBook = new ExcelBook(null, null, $flg); $objBook->setLocale('UTF-8'); $objBook->loadFile($this->file->getPathname()); //スキーマの名称でループし、 //名前付きセルを順次取得する $schemanames = $schemata->getSchemaNames(Schemata::CELL_MULTI); foreach ($schemanames as $name) { $schema = $schemata->getSchema($name, Schemata::CELL_MULTI); //対象外は処理スキップ if ($schema->require == Schema::REQUIRE_IGNORE) { continue; } $objSheet = $objBook->getSheetByName($schema->xlsheet); if (!$objSheet) { continue; } //シート取得不可ならスキップ if ($schemata->read_by == 'range') { $arrAddr = $objSheet->addrToRowCol($schema->xlrange); $arrDim = PHPExcel_Cell::rangeDimension($schema->xlrange); $arrInfo = array("row_first" => $arrAddr["row"], "col_first" => $arrAddr["column"], "row_last" => $arrAddr["row"] + $arrDim[1] - 1, "col_last" => $arrAddr["column"] + $arrDim[0] - 1); } else { $arrInfo = $objSheet->getNamedRange($name, $schema->xlscope); } $arrValid = $schema->validate($arrInfo); if ($arrValid["valid"] == false) { //エラーメッセージの取得 $msg = "[" . $name . "]" . $arrValid["msg"]; throw new \Exception($msg); continue; } $arr = array(); $cnt = 0; switch ($schema->type) { /* case Schema::TYPE_CELL: case Schema::TYPE_PID: case Schema::TYPE_ACT: // 単一セル ///////////////////// $data = $this->getOneCell($objSheet, $cnt, $arrInfo["row_first"], $arrInfo["col_first"] ); $arr = [ "type" => Schema::TYPE_CELL, "data" => $data, "rows" => 1, "cols" => 1 ]; break; */ case Schema::TYPE_ROW: // 1行 ///////////////////////// $data = $this->getOneRow($objSheet, $cnt, $arrInfo["row_first"], $arrInfo["col_first"], $arrInfo["col_last"]); $arr = ["type" => Schema::TYPE_ROW, "data" => $data, "rows" => 1, "cols" => $arrValid["cols"]]; break; case Schema::TYPE_COLUMN: // 1列 ///////////////////////// $data = $this->getOneCol($objSheet, $cnt, $arrInfo["col_first"], $arrInfo["row_first"], $arrInfo["row_last"]); $arr = ["type" => Schema::TYPE_COLUMN, "data" => $data, "rows" => $arrValid["rows"], "cols" => 1]; break; case Schema::TYPE_TABLE: // 表形式 ///////////////////////// $data = array(); $idx = 0; for ($rowIdx = $arrInfo["row_first"]; $rowIdx <= $arrInfo["row_last"]; $rowIdx++) { $data[$idx] = $this->getOneRow($objSheet, $cnt, $rowIdx, $arrInfo["col_first"], $arrInfo["col_last"]); $idx++; } $arr = ["type" => Schema::TYPE_TABLE, "data" => $data, "rows" => $arrValid["rows"], "cols" => $arrValid["cols"]]; break; case Schema::TYPE_NON: //非対応 //非対応 default: //何もしない } // 入力要求チェック[全て] //////////////////////////// if ($schema->require == Schema::REQUIRE_ALL) { if ($arrValid["rows"] * $arrValid["cols"] !== $cnt) { $msg = "[" . $name . "]に空欄があります"; throw new \Exception($msg); } } // 入力要求チェック[1つ以上] //////////////////////////// if ($schema->require == Schema::REQUIRE_NOTALL) { if ($cnt < 1) { $msg = "[" . $name . "]が空欄です"; throw new \Exception($msg); } } //正常 $arrRtn[$name] = $arr; } unset($objSheet); unset($objBook); return $arrRtn; }
/** * 文書のデータを配列に取得する */ public function readData_multi(Schemata $schemata) { $arrRtn = array(); //Excelファイルを開く $filepath = $this->file->getPathname(); $objBook = PHPExcel_IOFactory::load($filepath); //スキーマの名称でループし、 //名前付きセルを順次取得する $schemanames = $schemata->getSchemaNames(Schemata::CELL_MULTI); foreach ($schemanames as $name) { $schema = $schemata->getSchema($name, Schemata::CELL_MULTI); //対象外は処理スキップ if ($schema->require == Schema::REQUIRE_IGNORE) { continue; } $objSheet = $objBook->getSheetByName($schema->xlsheet); if (!$objSheet) { continue; } //シート取得不可ならスキップ if ($schemata->read_by == 'range') { $arrBdry = PHPExcel_Cell::rangeBoundaries($schema->xlrange); /* $arrAddr = $objSheet->addrToRowCol($schema->xlrange); $arrDim = PHPExcel_Cell::rangeDimension($schema->xlrange); $arrInfo = array( "row_first" => $arrAddr["row"], "col_first" => $arrAddr["column"], "row_last" => $arrAddr["row"] + $arrDim[1] -1, "col_last" => $arrAddr["column"] + $arrDim[0] -1 ); */ } else { $objNR = $objBook->getNamedRange($name, $objSheet); $arrBdry = PHPExcel_Cell::rangeBoundaries($objNR->getRange()); } //var_dump(PHPExcel_Cell::rangeBoundaries('B4:F10')); exit(); $arrInfo = array("row_first" => intVal($arrBdry[0][1]) - 1, "col_first" => $arrBdry[0][0] - 1, "row_last" => intVal($arrBdry[1][1]) - 1, "col_last" => $arrBdry[1][0] - 1); $arrValid = $schema->validate($arrInfo); if ($arrValid["valid"] == false) { //エラーメッセージの取得 $msg = "[" . $name . "]" . $arrValid["msg"]; throw new \Exception($msg); continue; } $arr = array(); $cnt = 0; switch ($schema->type) { /* case Schema::TYPE_CELL: case Schema::TYPE_PID: case Schema::TYPE_ACT: // 単一セル ///////////////////// $data = $this->getOneCell($objSheet, $cnt, $arrInfo["row_first"], $arrInfo["col_first"] ); $arr = [ "type" => Schema::TYPE_CELL, "data" => $data, "rows" => 1, "cols" => 1 ]; break; */ case Schema::TYPE_ROW: // 1行 ///////////////////////// $data = $this->getOneRow($objSheet, $cnt, $arrInfo["row_first"], $arrInfo["col_first"], $arrInfo["col_last"]); $arr = ["type" => Schema::TYPE_ROW, "data" => $data, "rows" => 1, "cols" => $arrValid["cols"]]; break; case Schema::TYPE_COLUMN: // 1列 ///////////////////////// $data = $this->getOneCol($objSheet, $cnt, $arrInfo["col_first"], $arrInfo["row_first"], $arrInfo["row_last"]); $arr = ["type" => Schema::TYPE_COLUMN, "data" => $data, "rows" => $arrValid["rows"], "cols" => 1]; break; case Schema::TYPE_TABLE: // 表形式 ///////////////////////// $data = array(); $idx = 0; for ($rowIdx = $arrInfo["row_first"]; $rowIdx <= $arrInfo["row_last"]; $rowIdx++) { $data[$idx] = $this->getOneRow($objSheet, $cnt, $rowIdx, $arrInfo["col_first"], $arrInfo["col_last"]); $idx++; } $arr = ["type" => Schema::TYPE_TABLE, "data" => $data, "rows" => $arrValid["rows"], "cols" => $arrValid["cols"]]; break; case Schema::TYPE_NON: //非対応 //非対応 default: //何もしない } // 入力要求チェック[全て] //////////////////////////// if ($schema->require == Schema::REQUIRE_ALL) { if ($arrValid["rows"] * $arrValid["cols"] !== $cnt) { $msg = "[" . $name . "]に空欄があります"; throw new \Exception($msg); } } // 入力要求チェック[1つ以上] //////////////////////////// if ($schema->require == Schema::REQUIRE_NOTALL) { if ($cnt < 1) { $msg = "[" . $name . "]が空欄です"; throw new \Exception($msg); } } //正常 $arrRtn[$name] = $arr; } $objBook->disconnectWorksheets(); unset($objSheet); unset($objBook); return $arrRtn; }
/** * スキーマ情報配列を統合する */ public function merge(Schemata $schemata) { if ($this->locked) { return; } //ロック済のため処理なし //スキーマのマージ(単一セル) $names_single = $schemata->getSchemaNames(self::CELL_SINGLE); foreach ($names_single as $name) { if (!isset($this->_single[$name])) { $this->addSchema($schemata->getSchema($name, self::CELL_SINGLE)); } } //スキーマのマージ(単一セル) $names_multi = $schemata->getSchemaNames(self::CELL_MULTI); foreach ($names_multi as $name) { if (!isset($this->_multi[$name])) { $this->addSchema($schemata->getSchema($name, self::CELL_MULTI)); } } }