PHP PHPExcel_Style_NumberFormat::toFormattedString方法代码示例

 public function ZoekTijd()
 {
     require_once "/Classes/databaseHandler.class.php";
     $Database = new Database();
     require_once "/Classes/calculatetime.php";
     $calculatetime = new calculatetime();
     $Database->query('SELECT fileName, name FROM wedstrijden WHERE name=?');
     $Database->bind(1, $_GET['wedstrijd']);
     $resultSet = $Database->single();
     if (isset($_GET['startnummer']) || isset($_GET['naam'])) {
         include 'Classes/PHPExcel/IOFactory.php';
         $inputFileName = 'excel/' . $resultSet['fileName'];
         try {
             $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
             $objReader = PHPExcel_IOFactory::createReader($inputFileType);
             $objPHPExcel = $objReader->load($inputFileName);
         } catch (Exception $e) {
             die('Er is een fout opgetreden met bestand  "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
         }
         $sheet = $objPHPExcel->getSheet(0);
         $highestRow = $sheet->getHighestRow();
         $highestColumn = $sheet->getHighestColumn();
         for ($row = 1; $row <= $highestRow; $row++) {
             $rowData[] = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE)[0];
         }
         if (isset($_GET['startnummer']) && $_GET['startnummer'] == !null) {
             $Startnummer = $_GET['startnummer'];
             $key = array_search($Startnummer, array_column($rowData, 0));
         }
         if (isset($_GET['naam']) && $_GET['naam'] == !null) {
             $Naam = $_GET['naam'];
             $key = array_search($Naam, array_column($rowData, 1));
         }
         $this->Startnummer = $rowData[$key][0];
         $this->Naam = $rowData[$key][1];
         $this->Woonplaats = $rowData[$key][2];
         $this->Geboortedatum = $rowData[$key][3];
         $this->Geslacht = $rowData[$key][4];
         $this->Wedstrijdnaam = $resultSet['name'];
         $cell = $sheet->getCellByColumnAndRow(5, $key + 1);
         $cell_value = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'hh:mm:ss');
         $this->VideoTijd = $calculatetime->GetPlayerTime($cell_value);
         $_GET['startnummer'] = $this->Startnummer;
     }
 }

 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFileName
  * @throws 	Exception
  */
 public function save($pFilename = null)
 {
     // Fetch sheet
     $sheet = $this->_phpExcel->getSheet($this->_sheetIndex);
     // Open file
     $fileHandle = fopen($pFilename, 'w');
     if ($fileHandle === false) {
         throw new Exception("Could not open file {$pFilename} for writing.");
     }
     // Get cell collection
     $cellCollection = $sheet->getCellCollection();
     // Get column count
     $colCount = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
     // Loop trough cells
     $currentRow = -1;
     $rowData = array();
     foreach ($cellCollection as $cell) {
         if ($currentRow != $cell->getRow()) {
             // End previous row?
             if ($currentRow != -1) {
                 $this->_writeLine($fileHandle, $rowData);
             }
             // Set current row
             $currentRow = $cell->getRow();
             // Start a new row
             $rowData = array();
             for ($i = 0; $i < $colCount; $i++) {
                 $rowData[$i] = '';
             }
         }
         // Copy cell
         $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
         if ($cell->getValue() instanceof PHPExcel_RichText) {
             $rowData[$column] = $cell->getValue()->getPlainText();
         } else {
             if ($this->_preCalculateFormulas) {
                 $rowData[$column] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $sheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
             } else {
                 $rowData[$column] = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $sheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
             }
         }
     }
     // End last row?
     if ($currentRow != -1) {
         $this->_writeLine($fileHandle, $rowData);
     }
     // Close file
     fclose($fileHandle);
 }

 public static function convertCellData(\PHPExcel_Cell $cell)
 {
     $ret = array();
     $datatype = $cell->getDataType();
     if ($datatype === \excel2sql\Type::EXCEL_NUMERIC) {
         $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
         if (array_key_exists($format, \excel2sql\Type::$numeric_convert_map)) {
             $format = \excel2sql\Type::$numeric_convert_map[$format];
         }
         $ret['type'] = \excel2sql\Type::$numeric_to_sql_map[$format];
         $ret['value'] = \PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), $format);
         if ($format === \excel2sql\Type::EXCEL_DATE || $format === \excel2sql\Type::EXCEL_DATETIME || $format === \excel2sql\Type::EXCEL_TIME) {
             $ret['value'] = "'{$ret['value']}'";
         }
     } else {
         $ret['type'] = \excel2sql\Type::$excel_to_sql_map[$datatype];
         if ($ret['value'] = $cell->getFormattedValue()) {
             if ($datatype === \excel2sql\Type::EXCEL_STRING || $datatype === \excel2sql\Type::EXCEL_STRING2) {
                 $ret['value'] = "\"" . preg_replace("[\"]", "''", $ret['value']) . "\"";
             }
         } else {
             $ret['value'] = "null";
         }
     }
     return $ret;
 }

 private function formatDataSetLabels($groupID, $datasetLabels, $labelCount, $rotation = '')
 {
     $datasetLabelFormatCode = $this->chart->getPlotArea()->getPlotGroupByIndex($groupID)->getPlotCategoryByIndex(0)->getFormatCode();
     if (!is_null($datasetLabelFormatCode)) {
         //    Retrieve any label formatting code
         $datasetLabelFormatCode = stripslashes($datasetLabelFormatCode);
     }
     $testCurrentIndex = 0;
     foreach ($datasetLabels as $i => $datasetLabel) {
         if (is_array($datasetLabel)) {
             if ($rotation == 'bar') {
                 $datasetLabels[$i] = implode(" ", $datasetLabel);
             } else {
                 $datasetLabel = array_reverse($datasetLabel);
                 $datasetLabels[$i] = implode("\n", $datasetLabel);
             }
         } else {
             //    Format labels according to any formatting code
             if (!is_null($datasetLabelFormatCode)) {
                 $datasetLabels[$i] = PHPExcel_Style_NumberFormat::toFormattedString($datasetLabel, $datasetLabelFormatCode);
             }
         }
         ++$testCurrentIndex;
     }
     return $datasetLabels;
 }

 /**
  * PROCESA EL ARCHIVO EXCEL Y LO METE EN LA BASE DE DATOS
  */
 public function ProcesaArchivo()
 {
     $archivo = $_FILES['archivo'];
     //COGE EL ARCHIVO
     $objPHPExcel = PHPExcel_IOFactory::load($archivo['tmp_name']);
     //NOMBRE DEL ARCHIVO TEMPORAL EN EL SISTEMA
     $objPHPExcel->setActiveSheetIndex(0);
     //COGE EL PANEL 1 DEL EXEL
     $numRows = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
     //CUENTA LAS FILAS QUE TIENE EL EXCEL
     $datos = array();
     //ARRAY PARA GUARDAR LOS DATOS
     $tipo = "";
     //TIPO PARA VER SI ES CATEGORÍA O PRODUCTO
     $idCat = 0;
     //ID DE CATEGORÍA PARA EL PRODUCTO
     for ($i = 1; $i <= $numRows; $i++) {
         //POR CADA FILA
         $celdaB = $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue();
         //CALCULO LA CELDA A$i
         $celdaA = $objPHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue();
         //CALCULO LA CELDA B$i
         if ($celdaB == "") {
             //SI LA CELDA B ESTÁ VACÍA ES QUE ES UN TIPO
             $tipo = $celdaA;
             //SE LE ASIGNA EL TIPO A LA VARIABLE
         } else {
             //SI TIENE CONTENIDO
             if ($celdaA == 'fec_ini' || $celdaA == 'fec_fin') {
                 //SI ES UNA FECHA, LA CONVIERTO A FORMATO YYYY/MM/DD Y LO METO EN EL ARRAY
                 $datos[$tipo][$celdaA] = PHPExcel_Style_NumberFormat::toFormattedString($celdaB, 'YYYY/MM/DD');
             } else {
                 // SI NO LO METO EN EL ARRAY SIN MÁS
                 $datos[$tipo][$celdaA] = $celdaB;
             }
         }
         if ($celdaA == "se_muestra" && $tipo == 'CATEGORIA') {
             //SI LA ÚLTIMA CELDA ES se_muestra Y EL TIPO ES LA CATEGORÍA
             $idCat = $this->xml->mas_categoria($datos['CATEGORIA']);
             //AÑADE LA CATEGORÍA A LA BASE DE DATOS Y DEVUELVE LA ID
         }
         if ($celdaA == "se_muestra" && $tipo == 'PRODUCTOS') {
             //SI LA ÚLTIMA CELDA ES se_muestra Y EL TIPO ES PRODUCTOS
             $datos['PRODUCTOS']['Categoria_idCat'] = $idCat;
             //AÑADE LA ID DE CATEGORÍA AL ARRAY
             $this->xml->mas_productos($datos['PRODUCTOS']);
             //METE EL PRODUCTO EN LA BASE DE DATOS
         }
     }
     redirect('/Welcome/index', 'location', 301);
     //MUESTRA INICIO
 }

 public function insertData($data)
 {
     // EVENT_INFO_ID serial NOT NULL,
     // STATUS CHAR(1) DEFAULT '0' NOT NULL,
     // EVENT_DATE TIMESTAMP,
     // MEDICAL_INSTITION_NO CHAR(3),
     // PARTICIPANT_NO NUMERIC(2,0) NOT NULL,
     // BARCODE NUMERIC(9,0) NOT NULL,
     // MEDICAL_INSTITION VARCHAR(100),
     // DEPARTMENT VARCHAR(100),
     // POST VARCHAR(100),
     // NAME VARCHAR(100),
     // TEL_NO1 VARCHAR(20),
     // TEL_NO2 VARCHAR(20),
     // FAX VARCHAR(20),
     // MAIL_ADDRESS VARCHAR(256),
     // POSTAL_CODE VARCHAR(10),
     // ADDRESS VARCHAR(100),
     // REMARKS VARCHAR(100)
     $columns = array('event_info_id', 'event_date', 'medical_instition_no', 'medical_instition', 'participant_no', 'department', 'post', 'name', 'tel_no1', 'tel_no2', 'mail_address', 'postal_code', 'address', 'remarks');
     foreach ($data as $k1 => $v1) {
         // 先頭行はヘッダのため除く
         if ($k1 < 4) {
             continue;
         }
         $newData = null;
         $this->create();
         if (!isset($v1[0]) || $v1[0] == '') {
             break;
         }
         foreach ($v1 as $k2 => $v2) {
             if ($k2 >= 14) {
                 continue;
             }
             var_dump($v2);
             if ('event_info_id' === $columns[$k2]) {
                 $newData[$columns[$k2]] = intval($v2);
             } else {
                 if ('event_date' === $columns[$k2]) {
                     $display_date = PHPExcel_Style_NumberFormat::toFormattedString($v2, PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
                     // $newData[$columns[$k2]] = date('Y-M-D', $display_date);
                     $newData[$columns[$k2]] = $display_date;
                 } else {
                     // $newData[$columns[$k2]] = mb_convert_encoding($v2, "UTF-8", "SJIS");
                     $newData[$columns[$k2]] = $v2;
                 }
             }
         }
         $this->save($newData);
     }
 }

    $objPHPExcel = $objReader->load($inputFileName);
    $rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
    $sheet = $objPHPExcel->getActiveSheet();
    $_SESSION['array_data'] = array();
    $count = 0;
    foreach ($rowIterator as $row) {
        $rowIndex = $row->getRowIndex();
        $_SESSION['array_data'][$rowIndex] = array('A' => '', 'B' => '', 'C' => '', 'D' => '', 'E' => '', 'F' => '', 'G' => '', 'H' => '', 'I' => '', 'J' => '', 'K' => '', 'L' => '', 'M' => '', 'N' => '', 'O' => '', 'P' => '');
        $startA = 'A';
        $endP = 'P';
        for ($startA; $startA <= $endP; $startA++) {
            $cell = $sheet->getCell($startA . $rowIndex);
            $_SESSION['array_data'][$rowIndex][$startA] = $cell->getValue();
            if ($startA == 'G' || $startA == 'O' || $startA == 'P') {
                $cell = $sheet->getCell($startA . $rowIndex);
                $_SESSION['array_data'][$rowIndex][$startA] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), 'M-DD-Y');
            }
        }
    }
    //print_r($_SESSION['array_data']);
}
?>

<html>
	<head>
		<title>Philhealth Membership Verification</title>
		<style>
			#tw-form-outer {
			
			}
			#tw-form{

 /**
  * @param 文件 $file
  * @return Exel 内容 array
  * @throws PHPExcel_Exception
  * @throws PHPExcel_Reader_Exception
  */
 public function importExcel($file)
 {
     if (!file_exists($file)) {
         return array("error" => 0, 'message' => 'file not found!');
     }
     chmod($file, 0777);
     if (!is_readable($file)) {
         return array("error" => 0, 'message' => 'file is not readable');
     }
     Vendor("PHPExcel.IOFactory");
     //兼容多种版本的Excel
     $objReader = PHPExcel_IOFactory::createReader('Excel5');
     $PHPReader = $objReader->load($file);
     /* if(!$PHPReader){
            $objReader = PHPExcel_IOFactory::createReader('Excel2007');
            $PHPReader = $objReader->load($file);
            if(!$PHPReader){
                $objReader = PHPExcel_IOFactory::createReader('Excel5');
                $PHPReader = $objReader->load($file);
                if(!$PHPReader){
                    return array("error"=>0,'message'=>'文件格式错误');
                }
            }
        } */
     if (!isset($PHPReader)) {
         return array("error" => 0, 'message' => 'read error!');
     }
     $allWorksheets = $PHPReader->getAllSheets();
     $i = 0;
     foreach ($allWorksheets as $objWorksheet) {
         $sheetname = $objWorksheet->getTitle();
         $allRow = $objWorksheet->getHighestRow();
         //how many rows
         $highestColumn = $objWorksheet->getHighestColumn();
         //how many columns
         $allColumn = PHPExcel_Cell::columnIndexFromString($highestColumn);
         $array[$i]["Title"] = $sheetname;
         $array[$i]["Cols"] = $allColumn;
         $array[$i]["Rows"] = $allRow;
         $arr = array();
         $isMergeCell = array();
         foreach ($objWorksheet->getMergeCells() as $cells) {
             //merge cells
             foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                 $isMergeCell[$cellReference] = true;
             }
         }
         for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
             $row = array();
             for ($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++) {
                 $cell = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
                 $afCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn + 1);
                 $bfCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn - 1);
                 $col = PHPExcel_Cell::stringFromColumnIndex($currentColumn);
                 $address = $col . $currentRow;
                 $value = $objWorksheet->getCell($address)->getValue();
                 if (substr($value, 0, 1) == '=') {
                     return array("error" => 0, 'message' => 'can not use the formula!');
                     exit;
                 }
                 if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_NUMERIC) {
                     $cellstyleformat = $cell->getStyle($cell->getCoordinate())->getNumberFormat();
                     $formatcode = $cellstyleformat->getFormatCode();
                     if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $formatcode)) {
                         $value = gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));
                     } else {
                         $value = PHPExcel_Style_NumberFormat::toFormattedString($value, $formatcode);
                     }
                 }
                 if ($isMergeCell[$col . $currentRow] && $isMergeCell[$afCol . $currentRow] && !empty($value)) {
                     $temp = $value;
                 } elseif ($isMergeCell[$col . $currentRow] && $isMergeCell[$col . ($currentRow - 1)] && empty($value)) {
                     $value = $arr[$currentRow - 1][$currentColumn];
                 } elseif ($isMergeCell[$col . $currentRow] && $isMergeCell[$bfCol . $currentRow] && empty($value)) {
                     $value = $temp;
                 }
                 //$value = iconv( "UTF-8","gb2312", $content);
                 $row[$currentColumn] = $value;
             }
             $arr[$currentRow] = $row;
         }
         $array[$i]["Content"] = $arr;
         $i++;
     }
     spl_autoload_register(array('Think', 'autoload'));
     //must, resolve ThinkPHP and PHPExcel conflicts
     unset($objWorksheet);
     unset($PHPReader);
     unset($PHPExcel);
     unlink($file);
     return array("error" => 1, "data" => $array);
 }

 /**
  * Create array from worksheet
  *
  * @param	mixed	$nullValue				Value returned in the array entry if a cell doesn't exist
  * @param	boolean	$calculateFormulas		Should formulas be calculated?
  * @param	boolean	$formatData				Should formatting be applied to cell values?
  * @param	boolean	$returnCellRef			False - Return a simple array of rows and columns indexed by number counting from zero
  *											True - Return rows and columns indexed by their actual row and column IDs
  * @return array
  */
 public function toArray($nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false)
 {
     // Returnvalue
     $returnValue = array();
     // Garbage collect...
     $this->garbageCollect();
     // Loop through rows
     $r = -1;
     $rowIterator = $this->getRowIterator();
     foreach ($rowIterator as $row) {
         ++$r;
         $cellIterator = $row->getCellIterator();
         $cellIterator->setIterateOnlyExistingCells(true);
         // Loop through each cell in the current row
         $c = -1;
         foreach ($cellIterator as $cell) {
             ++$c;
             $rRef = $returnCellRef ? $cell->getRow() : $r;
             $cRef = $returnCellRef ? $cell->getColumn() : $c;
             if (!is_null($cell)) {
                 // Cell exists?
                 if ($cell->getValue() instanceof PHPExcel_RichText) {
                     $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
                 } else {
                     if ($calculateFormulas) {
                         $returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
                     } else {
                         $returnValue[$rRef][$cRef] = $cell->getValue();
                     }
                 }
                 if ($formatData) {
                     $style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
                     $returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style->getNumberFormat()->getFormatCode());
                 }
             } else {
                 $returnValue[$rRef][$cRef] = $nullValue;
             }
         }
     }
     // Return
     return $returnValue;
 }

 function excel_yukle()
 {
     $this->load->model('servis_model');
     $yeniDosyaAdi = $this->servis_model->dosyaAdiOlustur($ozNetlik = FALSE, $_FILES['excel_dosyasi']['name']);
     $ayar = array('upload_path' => 'dosyalar/upload', 'allowed_types' => 'xls|xlsx', 'file_name' => $yeniDosyaAdi);
     $this->load->library('upload', $ayar);
     if (!$this->upload->do_upload('excel_dosyasi')) {
         $veri = array('formHatasi' => $this->upload->display_errors('<div class="formHatasi">', '</div>'), 'gosterilecekSayfa' => 'excel_oku');
         $bilgi = array_merge($veri, $this->sistemSabit);
         $this->load->view('taslak', $bilgi);
     } else {
         $this->load->library('Excel');
         $this->excel = PHPExcel_IOFactory::load('dosyalar/upload/' . $yeniDosyaAdi);
         $sayfa = $this->excel->getSheet(0);
         $satirSayisi = $this->excel->setActiveSheetIndex(0)->getHighestRow();
         $kolonAdi = $sayfa->getHighestColumn();
         echo $this->servis_model->tarih2unix('20.02.2015');
         $veri = array();
         for ($satir = 2; $satir <= $satirSayisi; $satir++) {
             $kolonVeri = array();
             $satirData = $sayfa->rangeToArray('B' . $satir . ':' . $kolonAdi . $satir, NULL, TRUE, FALSE);
             foreach ($satirData[0] as $kolonNo => $kolonBilgi) {
                 $kolon = $kolonBilgi;
                 array_push($kolonVeri, $kolon);
             }
             $yazVeri = array('is_numarasi' => $this->servis_model->yeniFisNoOlustur(), 'gelis_tarihi' => $this->servis_model->tarih2unix(PHPExcel_Style_NumberFormat::toFormattedString($kolonVeri[0], 'YYYY-MM-DD hh:mm:ss')), 'musteri_adi' => $kolonVeri[1], 'musteri_adresi' => $kolonVeri[2], 'posta_kodu' => $this->servis_model->postaKoduGetir($kolonVeri[4]), 'sehir' => $this->servis_model->plakaGetir($kolonVeri[4]), 'tel' => $kolonVeri[5], 'email' => $kolonVeri[6], 'durum' => 1, 'marka' => 2, 'cihaz_tur' => 11, 'urun_kodu' => strtoupper($kolonVeri[7]), 'urun_adi' => strtoupper($kolonVeri[8]), 'seri_no' => strtoupper($kolonVeri[9]), 'garanti_baslangic' => $this->servis_model->tarih2unix(PHPExcel_Style_NumberFormat::toFormattedString($kolonVeri[10], 'YYYY-MM-DD hh:mm:ss')), 'garanti_belge_turu' => $kolonVeri[11], 'bildirilen_ariza' => $kolonVeri[12], 'ariza_tanimi' => $kolonVeri[13], 'yapilan_islem' => $kolonVeri[14], 'sayac_durumu' => $kolonVeri[15], 'teslim_tarihi' => $this->servis_model->tarih2unix(PHPExcel_Style_NumberFormat::toFormattedString($kolonVeri[16], 'YYYY-MM-DD hh:mm:ss')), 'servis_sekli' => $kolonVeri[17], 'gonderi_sekli' => $kolonVeri[18], 'gonderi_dokuman' => $kolonVeri[19], 'gonderi_ucreti' => 0, 'servis_ucreti' => 0, 'pesinat' => '0.00', 'islemler' => NULL, 'sil' => 0);
             $this->servis_model->exceldenVeriKaydet($yazVeri);
         }
         unlink('./dosyalar/upload/' . $yeni_dosya_adi);
         redirect('servis/listele');
     }
 }

 /**
  * 
  * 
  * @param string $ps_source
  * @param array $pa_options
  * @return bool
  */
 public function nextRow()
 {
     if (!$this->opo_rows) {
         return false;
     }
     while (true) {
         if ($this->opn_current_row > 0) {
             $this->opo_rows->next();
         }
         $this->opn_current_row++;
         if (!$this->opo_rows->valid()) {
             return false;
         }
         if ($o_row = $this->opo_rows->current()) {
             $this->opa_row_buf = array(null);
             $o_cells = $o_row->getCellIterator();
             $o_cells->setIterateOnlyExistingCells(false);
             $va_row = array();
             $vb_val_was_set = false;
             $vn_col = 0;
             $vn_last_col_set = null;
             foreach ($o_cells as $o_cell) {
                 if (PHPExcel_Shared_Date::isDateTime($o_cell)) {
                     if (!($vs_val = caGetLocalizedDate(PHPExcel_Shared_Date::ExcelToPHP(trim((string) $o_cell->getValue()))))) {
                         if (!($vs_val = trim(PHPExcel_Style_NumberFormat::toFormattedString((string) $o_cell->getValue(), 'YYYY-MM-DD')))) {
                             $vs_val = trim((string) $o_cell->getValue());
                         }
                     }
                     $this->opa_row_buf[] = $vs_val;
                 } else {
                     $this->opa_row_buf[] = $vs_val = trim((string) $o_cell->getValue());
                 }
                 if (strlen($vs_val) > 0) {
                     $vb_val_was_set = true;
                     $vn_last_col_set = $vn_col;
                 }
                 $vn_col++;
                 if ($vn_col > 255) {
                     break;
                 }
                 // max 255 columns; some Excel files have *thousands* of "phantom" columns
             }
             //if (!$vb_val_was_set) {
             //return $this->nextRow();
             //	continue;
             //}	// skip completely blank rows
             return $o_row;
         }
     }
     return false;
 }

function oadueslookup_options()
{
    global $wpdb;
    $dbprefix = $wpdb->prefix . "oalm_";
    $hidden_field_name = 'oalm_submit_hidden';
    if (!current_user_can('manage_options')) {
        wp_die(__('You do not have sufficient permissions to access this page.'));
    }
    // =========================
    // form processing code here
    // =========================
    if (isset($_FILES['oalm_file'])) {
        #echo "<h3>Processing file upload</h3>";
        #echo "<strong>Processing File:</strong> " . esc_html($_FILES['oalm_file']['name']) . "<br>";
        #echo "<strong>Type:</strong> " . esc_html($_FILES['oalm_file']['type']) . "<br>";
        if (preg_match('/\\.xlsx$/', $_FILES['oalm_file']['name'])) {
            /** PHPExcel */
            include plugin_dir_path(__FILE__) . 'PHPExcel-1.8.0/Classes/PHPExcel.php';
            /** PHPExcel_Writer_Excel2007 */
            include plugin_dir_path(__FILE__) . 'PHPExcel-1.8.0/Classes/PHPExcel/Writer/Excel2007.php';
            $objReader = new PHPExcel_Reader_Excel2007();
            $objReader->setReadDataOnly(true);
            $objReader->setLoadSheetsOnly(array("All"));
            $objPHPExcel = $objReader->load($_FILES["oalm_file"]["tmp_name"]);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $columnMap = array('BSA ID' => 'bsaid', 'Dues Yr.' => 'max_dues_year', 'Dues Pd. Dt.' => 'dues_paid_date', 'Level' => 'level', 'Reg. Audit Date' => 'reg_audit_date', 'Reg. Audit Result' => 'reg_audit_result');
            $complete = 0;
            $recordcount = 0;
            $error_output = "";
            foreach ($objWorksheet->getRowIterator() as $row) {
                $rowData = array();
                if ($row->getRowIndex() == 1) {
                    # this is the header row, grab the headings
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(FALSE);
                    foreach ($cellIterator as $cell) {
                        $cellValue = $cell->getValue();
                        if (isset($columnMap[$cellValue])) {
                            $rowData[$columnMap[$cellValue]] = 1;
                            #echo "Found column " . htmlspecialchars($cell->getColumn()) . " with title '" . htmlspecialchars($cellValue) . "'<br>" . PHP_EOL;
                        } else {
                            #echo "Discarding unknown column " . htmlspecialchars($cell->getColumn()) . " with title '" . htmlspecialchars($cellValue) . "'<br>" . PHP_EOL;
                        }
                    }
                    $missingColumns = array();
                    foreach ($columnMap as $key => $value) {
                        if (!isset($rowData[$value])) {
                            $missingColumns[] = $key;
                        }
                    }
                    if ($missingColumns) {
                        ?>
<div class="error"><p><strong>Import failed.</strong></p><p>Missing required columns: <?php 
                        esc_html_e(implode(", ", $missingColumns));
                        ?>
</div><?php 
                        $complete = 1;
                        # Don't show "may have failed" box at the bottom
                        break;
                    } else {
                        #echo "<strong>Data format validated:</strong> Importing new data...<br>" . PHP_EOL;
                        # we just validated that we have a good data file, nuke the existing data
                        $wpdb->show_errors();
                        ob_start();
                        $wpdb->query("TRUNCATE TABLE {$dbprefix}dues_data");
                        update_option('oadueslookup_last_import', $wpdb->get_var("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')"));
                        # re-insert the test data
                        oadueslookup_insert_sample_data();
                        # now we're ready for the incoming from the rest of the file.
                    }
                } else {
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(FALSE);
                    foreach ($cellIterator as $cell) {
                        $columnName = $objWorksheet->getCell($cell->getColumn() . "1")->getValue();
                        $value = "";
                        if ($columnName == "Dues Pd. Dt.") {
                            # this is a date field, and we have to work miracles to turn it into a mysql-compatible date
                            $date = $cell->getValue();
                            $dateint = intval($date);
                            $dateintVal = (int) $dateint;
                            $value = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY-MM-DD");
                        } else {
                            if ($columnName == "Reg. Audit Date") {
                                # this is also a date field, but can be empty
                                $date = $cell->getValue();
                                if (!$date) {
                                    $value = get_option('oadueslookup_last_import');
                                } else {
                                    $dateint = intval($date);
                                    $dateintVal = (int) $dateint;
                                    $value = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY-MM-DD");
                                }
                            } else {
                                $value = $cell->getValue();
                            }
                        }
                        if (isset($columnMap[$columnName])) {
                            $rowData[$columnMap[$columnName]] = $value;
                        }
//.........这里部分代码省略.........

		}
		$col++;
	}
	$top_col = $col - 1;
	//
	$row = 4;
	while ($name = $sheet->getCellByColumnAndRow(1,$row)->getValue())
	{
		$ncomp++;
		//
		$country = strict_query("SELECT id FROM countries WHERE name=?", array(($sheet->getCellByColumnAndRow(2,$row)->getValue())));
		if (!sql_num_rows($country))
			$errors .= "Country name \"".$line[$headers["country"]]."\" not found in database<br>";
		else
		{
			$birthday = PHPExcel_Style_NumberFormat::toFormattedString (floor($sheet->getCellByColumnAndRow(5,$row)->getValue()), "YYYY-MM-DD");
			//
			$err = addCom(
				$sheet->getCellByColumnAndRow(3,$row)->getValue(), 
				$name, 
				$birthday, 
				cased_mysql_result($country,0,"id"), 
				$sheet->getCellByColumnAndRow(4,$row)->getValue(), 
				true);
			if (is_int($err))
			{
				$nimp++;
				//
				for ($col=9;$col<=$top_col;$col++)
					if (isset($cats[$col]) && $sheet->getCellByColumnAndRow($col,$row)->getValue().""=="1")
						toggleReg($err,$cats[$col]);		

 /**
  * xlsxからサイトマップCSVを出力する。
  */
 public function import($path_xlsx, $path_csv)
 {
     $this->path_xlsx = $path_xlsx;
     $this->path_csv = $path_csv;
     $path_toppage = '/';
     if (strlen($this->px->conf()->path_top)) {
         $path_toppage = $this->px->conf()->path_top;
     }
     $path_toppage = $this->regulize_path($path_toppage);
     // サイトマップCSVの定義を取得
     $sitemap_definition = $this->get_sitemap_definition();
     $phpExcelHelper = $this->plugin->factory_PHPExcelHelper();
     if (!$phpExcelHelper) {
         return false;
     }
     set_time_limit(0);
     $objPHPExcel = $phpExcelHelper->load($path_xlsx);
     $table_definition = $this->parse_definition($objPHPExcel, 0);
     //xlsxの構造定義を読み解く
     $col_title = array();
     foreach ($table_definition['col_define'] as $tmp_col_define) {
         if (isset($col_title['start'])) {
             $col_title['end'] = @$tmp_col_define['col'];
             break;
         }
         if ($tmp_col_define['key'] == 'title') {
             $col_title['start'] = @$tmp_col_define['col'];
         }
     }
     unset($tmp_col_define);
     $objPHPExcel->setActiveSheetIndex(0);
     $objSheet = $objPHPExcel->getActiveSheet();
     // xlsxにあってサイトマップ定義にないカスタムカラムを定義に反映
     $xls_custom_column_definition = $table_definition['col_define'];
     $tmp_last_elm_info = array();
     foreach ($sitemap_definition as $tmp_row) {
         unset($xls_custom_column_definition[$tmp_row['key']]);
         $tmp_last_elm_info = $tmp_row;
     }
     foreach ($xls_custom_column_definition as $tmp_key => $tmp_row) {
         @$tmp_last_elm_info['num']++;
         @$tmp_last_elm_info['col']++;
         $tmp_last_elm_info['key'] = $tmp_row['key'];
         $tmp_last_elm_info['name'] = $tmp_row['key'];
         $sitemap_definition[$tmp_last_elm_info['key']] = $tmp_last_elm_info;
     }
     $sitemap = array();
     $page_info = array();
     foreach ($sitemap_definition as $row) {
         $page_info[$row['key']] = '* ' . $row['key'];
     }
     array_push($sitemap, $page_info);
     $last_breadcrumb = array();
     $last_page_id = null;
     $logical_path_last_depth = 0;
     $xlsx_row = $table_definition['row_data_start'];
     $xlsx_row--;
     while (1) {
         set_time_limit(30);
         $xlsx_row++;
         if ($xlsx_row > $table_definition['tbl_highest_row']) {
             // エクセルの最終行に達していたら、終了。
             break;
         }
         if ($objSheet->getCell('A' . $xlsx_row)->getCalculatedValue() == 'EndOfData') {
             // A列が 'EndOfData' だったら、終了。
             break;
         }
         $page_info = array();
         $tmp_page_info = array();
         foreach ($sitemap_definition as $key => $row) {
             $tmp_col_name = @$table_definition['col_define'][$row['key']]['col'];
             if (strlen($tmp_col_name)) {
                 $tmp_page_info[$row['key']] = $objSheet->getCell($tmp_col_name . $xlsx_row)->getCalculatedValue();
                 // ユーザーが設定したセルフォーマットに従って文字列を復元する
                 $cell_format = $objSheet->getStyle($tmp_col_name . $xlsx_row)->getNumberFormat()->getFormatCode();
                 if ($cell_format !== 'General') {
                     $tmp_cell_value = \PHPExcel_Style_NumberFormat::toFormattedString($tmp_page_info[$row['key']], $cell_format);
                     if (!is_null($tmp_cell_value)) {
                         $tmp_page_info[$row['key']] = $tmp_cell_value;
                     }
                 }
                 unset($cell_format, $tmp_cell_value);
             } else {
                 $tmp_page_info[$row['key']] = '';
             }
         }
         if (@$tmp_page_info['**delete_flg']) {
             // 削除フラグ
             continue;
         }
         // タイトルだけ特別
         $col_title_col = @$col_title['start'];
         $tmp_page_info['title'] = '';
         $logical_path_depth = 0;
         $alias_title_list = array();
         while (@strcmp($col_title_col, $col_title['end'])) {
//.........这里部分代码省略.........

 /**
  * Deprecated
  *
  * @throws PHPExcel_Exception
  */
 public function warehouse_unused()
 {
     $auth = new Auth();
     if ($auth->getPermission("warehouse")) {
         ini_set("max_execution_time", 900);
         ini_set("max_input_time", 60);
         ini_set("memory_limit", "256M");
         $this->goCake = true;
         ob_implicit_flush(true);
         @ob_end_flush();
         $i = 0;
         App::import('Vendor', 'PHPExcel/PHPExcel');
         if (!empty($this->request->data)) {
             $file_name = $this->request->data['shipping_upload_file']['tmp_name'];
             $excelFileObj = PHPExcel_IOFactory::load($file_name);
             $worksheet = $excelFileObj->getSheet(0);
             $highestRow = $worksheet->getHighestRow();
             $highestColumn = $worksheet->getHighestColumn();
             $validated = array();
             $returnErrors = array();
             $i = 0;
             $pure_endicia = false;
             for ($row = 1; $row <= $highestRow; $row++) {
                 $errors = array();
                 if ($row != 1) {
                     $rowData = $worksheet->rangeToArray("A{$row}" . ':' . "{$highestColumn}{$row}", NULL, true, false);
                     //make array easier to use
                     if ($row == 2 && $rowData[0][0] == 'Account Number') {
                         $pure_endicia = true;
                         continue;
                     }
                     if ($pure_endicia === true) {
                         $data = array('order_id' => preg_replace('/[^0-9]/', '', $rowData[0][14]), 'order_shipment_id' => 'LOOKUP', 'tracking_number' => $rowData[0][8], 'carrier' => 'USPS', 'date_created' => null, 'date_shipped' => $rowData[0][3], 'items_not_included' => null);
                         try {
                             $order_shipment_id = YouniqueAPI::call("/orders/getOrderShipmentId/" . $rowData[0][14]);
                             if ($order_shipment_id) {
                                 $data['order_shipment_id'] = $order_shipment_id;
                             }
                         } catch (Exception $e) {
                             error_log('API: failed to get order_shipment_id');
                         }
                     } else {
                         $data = array('order_id' => $rowData[0][0], 'order_shipment_id' => $rowData[0][1], 'tracking_number' => $rowData[0][2], 'carrier' => $rowData[0][3], 'date_created' => $rowData[0][4], 'date_shipped' => $rowData[0][5], 'items_not_included' => $rowData[0][6]);
                     }
                     $split = array();
                     //validation rules
                     //need all fields
                     if (empty($data['order_id']) || empty($data['order_shipment_id']) || empty($data['tracking_number']) || empty($data['carrier']) || empty($data['date_shipped'])) {
                         $data['error'] = "Row {$row} is missing data";
                         $errors[] = $data;
                     }
                     //order id must be int or - separated ints or int-REP
                     if (!is_numeric($data['order_id'])) {
                         //it may be a list
                         $order_ids = explode('-', $data['order_id']);
                         foreach ($order_ids as $value) {
                             if (!is_numeric($value)) {
                                 if (strtoupper($value) != "REP" || strtoupper($value) != "BO") {
                                     $data['error'] = "Row {$row} has invalid order id(s)";
                                     $errors[] = $data;
                                 }
                             } else {
                                 //split it
                                 $split[] = $value;
                             }
                         }
                     }
                     //order shipment id must be int
                     if (!is_numeric($data['order_shipment_id'])) {
                         $data['error'] = "Row {$row} has invalid order shipment id";
                         $errors[] = $data;
                     }
                     //tracking number must be int or 'int'
                     if (!ctype_alnum($data['tracking_number'])) {
                         if (!ctype_alnum(str_replace("'", '', $data['tracking_number']))) {
                             $data['error'] = "Row {$row} has invalid tracking number";
                             $errors[] = $data;
                         } else {
                             $data['tracking_number'] = str_replace("'", '', $data['tracking_number']);
                         }
                     }
                     //carrier must be "USPS" or "Landmark Global"
                     if ($data['carrier'] != "USPS" && $data['carrier'] != "Landmark Global") {
                         $data['error'] = "Row {$row} has invalid carrier";
                         $errors[] = $data;
                     }
                     //date created is optional but must be a valid date
                     //in the excel format or in valid php date format
                     if (is_numeric($data['date_created'])) {
                         //excel format
                         $date = PHPExcel_Style_NumberFormat::toFormattedString($data['date_created'], "YYYY-MM-DD h:mm:ss");
                         if (!DateTime::createFromFormat('G-m-d H:i:s', $date)) {
                             $date_created = DateTime::createFromFormat('Y-m-d H:i:s', $date);
                             $data['date_created'] = $date_created->format("Y-m-d H:i:s");
                         } else {
//.........这里部分代码省略.........