PHP PHPExcel_Shared_Date::ExcelToPHP方法代码示例

 public function ProcessFileContent()
 {
     $objPHPExcel = PHPExcel_IOFactory::load($this->file);
     // Format is as follows:
     // (gray bg)    [ <description of data> ], <relation1>,    <relationN>
     //              <srcConcept>,              <tgtConcept1>,  <tgtConceptN>
     //              <srcAtomA>,                <tgtAtom1A>,    <tgtAtomNA>
     //              <srcAtomB>,                <tgtAtom1B>,    <tgtAtomNB>
     //              <srcAtomC>,                <tgtAtom1C>,    <tgtAtomNC>
     // Output is function call:
     // InsPair($relation,$srcConcept,$srcAtom,$tgtConcept,$tgtAtom)
     // Loop over all worksheets
     foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
         // Loop through all rows
         $highestrow = $worksheet->getHighestRow();
         $highestcolumn = $worksheet->getHighestColumn();
         $highestcolumnnr = PHPExcel_Cell::columnIndexFromString($highestcolumn);
         $row = 1;
         // Go to the first row where a table starts.
         for ($i = $row; $i <= $highestrow; $i++) {
             $row = $i;
             if (substr($worksheet->getCell('A' . $row)->getValue(), 0, 1) === '[') {
                 break;
             }
         }
         // We are now at the beginning of a table or at the end of the file.
         $line = array();
         // Line is a buffer of one or more related (subsequent) excel rows
         while ($row <= $highestrow) {
             // Read this line as an array of values
             $values = array();
             // values is a buffer containing the cells in a single excel row
             for ($columnnr = 0; $columnnr < $highestcolumnnr; $columnnr++) {
                 $columnletter = PHPExcel_Cell::stringFromColumnIndex($columnnr);
                 $cell = $worksheet->getCell($columnletter . $row);
                 $cellvalue = (string) $cell->getCalculatedValue();
                 // overwrite $cellvalue in case of datetime
                 // the @ is a php indicator for a unix timestamp (http://php.net/manual/en/datetime.formats.compound.php), later used for typeConversion
                 if (PHPExcel_Shared_Date::isDateTime($cell) && !empty($cellvalue)) {
                     $cellvalue = '@' . (string) PHPExcel_Shared_Date::ExcelToPHP($cellvalue);
                 }
                 $values[] = $cellvalue;
             }
             $line[] = $values;
             // add line (array of values) to the line buffer
             $row++;
             // Is this relation table done? Then we parse the current values into function calls and reset it
             $firstCellInRow = (string) $worksheet->getCell('A' . $row)->getCalculatedValue();
             if (substr($firstCellInRow, 0, 1) === '[') {
                 // Relation table is complete, so it can be processed.
                 $this->ParseLines($line);
                 $line = array();
             }
         }
         // Last relation table remains to be processed.
         $this->ParseLines($line);
         $line = array();
     }
 }

 function insertarCargarExcepciones($array)
 {
     $uploadOk = 1;
     $time = time();
     $fecha = date("Y-m-d", $time);
     $target_dir = "../documents/";
     $target_file = $target_dir . basename($_FILES["archivoExcepcion"]["name"]);
     move_uploaded_file($array["archivoExcepcion"]["tmp_name"], $target_file);
     set_include_path(get_include_path() . PATH_SEPARATOR . '../complements/PHPExcel-1.8/Classes/');
     $inputFileType = 'Excel2007';
     include 'PHPExcel/IOFactory.php';
     $inputFileName = $target_file;
     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($inputFileName);
     $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     require_once "../db/conexiones.php";
     $consulta = new Conexion();
     foreach ($sheetData as $key => $datos) {
         $nombreSinAcentos = sanear_string($datos['B']);
         $nombre = strtoupper(trim($nombreSinAcentos));
         $datosEmpleado = $consulta->Conectar("postgres", "SELECT * FROM userinfo WHERE UPPER(name)='" . $nombre . "'");
         if ($datosEmpleado) {
             $sqlInsert = $this->invoco->Conectar("postgres", "INSERT INTO horario_excepcion (user_id, desde, hasta, banda_id) VALUES (" . $datosEmpleado[0]['userid'] . ",'" . date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell('D' . $key)->getvalue())) . "', '" . date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell('E' . $key)->getvalue())) . "'," . $datos['C'] . ")");
         }
     }
     return "Se insertaron los datos Exitosamente!";
 }

 /**
  * Format cell data type
  * @param  \PHPExcel_Cell $cell 
  * @return string               
  */
 protected function reformatCellDataType(\PHPExcel_Cell $cell)
 {
     $value = $cell->getValue();
     //datetime
     if (\PHPExcel_Shared_Date::isDateTime($cell)) {
         $format = $this->cellFormat['dateTime'];
         return date($format, \PHPExcel_Shared_Date::ExcelToPHP($value));
     }
     return $value;
 }

function excel_to_date($id)
{
    $value = $id->getValue();
    if ($value != '') {
        if (PHPExcel_Shared_Date::isDateTime($id)) {
            $data = PHPExcel_Shared_Date::ExcelToPHP($value);
            $date = date('Y-m-d', $data);
        } else {
            if (date_create($value)) {
                $date = date_format(date_create($value), 'Y-m-d');
            } else {
                $date = '0000-00-00';
            }
        }
    } else {
        $date = '0000-00-00';
    }
    return $date;
}

 public function getAllData()
 {
     $this->excel_data = array();
     $objWorksheet = $this->excel->getActiveSheet();
     // Get the highest row number and column letter referenced in the worksheet
     $highestRow = $objWorksheet->getHighestRow();
     // e.g. 10
     $highestColumn = $objWorksheet->getHighestColumn();
     // e.g 'F'
     if ($this->maxColumn != null) {
         $highestColumn = $this->maxColumn;
     }
     // Increment the highest column letter
     $highestColumn++;
     for ($row = 1; $row <= $highestRow; ++$row) {
         $line = array();
         for ($col = 'A'; $col != $highestColumn; ++$col) {
             //   	var_dump($objWorksheet->getCell($col . $row)
             // ->getValue());
             // print "<br/>\r\n";
             // continue;
             $cell = $objWorksheet->getCell($col . $row);
             $val = "" . $cell->getValue();
             // var_dump($col, $row,$val);
             if ($row == $this->titleLine && $val == "") {
                 $highestColumn = $col++;
                 break;
             }
             if (PHPExcel_Shared_Date::isDateTime($cell)) {
                 $val = PHPExcel_Shared_Date::ExcelToPHP($val);
             } else {
                 $val = "" . $val;
             }
             $line[] = $val;
         }
         $this->excel_data[] = $line;
     }
     return $this->excel_data;
 }

 /**
  * Convert a value in a pre-defined format to a PHP string
  *
  * @param mixed 	$value		Value to format
  * @param string 	$format		Format code
  * @return string	Formatted string
  */
 public static function toFormattedString($value = '', $format = '')
 {
     if (!is_numeric($value)) {
         return $value;
     }
     if (preg_match("/^[hmsdy]/i", $format)) {
         // custom datetime format
         // dvc: convert Excel formats to PHP date formats
         // first remove escapes related to non-format characters
         // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
         $format = strtolower($format);
         $format = str_replace('\\', '', $format);
         // 4-digit year
         $format = str_replace('yyyy', 'Y', $format);
         // 2-digit year
         $format = str_replace('yy', 'y', $format);
         // first letter of month - no php equivalent
         $format = str_replace('mmmmm', 'M', $format);
         // full month name
         $format = str_replace('mmmm', 'F', $format);
         // short month name
         $format = str_replace('mmm', 'M', $format);
         // mm is minutes if time or month w/leading zero
         $format = str_replace(':mm', ':i', $format);
         // tmp place holder
         $format = str_replace('mm', 'x', $format);
         // month no leading zero
         $format = str_replace('m', 'n', $format);
         // month leading zero
         $format = str_replace('x', 'm', $format);
         // 12-hour suffix
         $format = str_replace('am/pm', 'A', $format);
         // tmp place holder
         $format = str_replace('dd', 'x', $format);
         // days no leading zero
         $format = str_replace('d', 'j', $format);
         // days leading zero
         $format = str_replace('x', 'd', $format);
         // seconds
         $format = str_replace('ss', 's', $format);
         // fractional seconds - no php equivalent
         $format = str_replace('.s', '', $format);
         if (!strpos($format, 'A')) {
             // 24-hour format
             $format = str_replace('h', 'H', $format);
         }
         // user defined flag symbol????
         $format = str_replace(';@', '', $format);
         return date($format, PHPExcel_Shared_Date::ExcelToPHP($value));
     } else {
         if (preg_match('/%$/', $format)) {
             // % number format
             if (preg_match('/\\.[#0]+/i', $format, $m)) {
                 $s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
                 $format = str_replace($m[0], $s, $format);
             }
             if (preg_match('/^[#0]+/', $format, $m)) {
                 $format = str_replace($m[0], strlen($m[0]), $format);
             }
             $format = '%' . str_replace('%', "f%%", $format);
             return sprintf($format, 100 * $value);
         } else {
             if (preg_match("/^([0-9.,-]+)\$/", $value)) {
                 if ($format === self::FORMAT_NUMBER) {
                     return sprintf('%1.0f', $value);
                 } else {
                     if ($format === self::FORMAT_NUMBER_00) {
                         return sprintf('%1.2f', $value);
                     } else {
                         if ($format === self::FORMAT_NUMBER_COMMA_SEPARATED1 || $format === self::FORMAT_NUMBER_COMMA_SEPARATED2) {
                             return number_format($value, 2, ',', '.');
                         } else {
                             if ($format === self::FORMAT_PERCENTAGE) {
                                 return round(100 * $value, 0) . '%';
                             } else {
                                 if ($format === self::FORMAT_PERCENTAGE_00) {
                                     return round(100 * $value, 2) . '%';
                                 } else {
                                     if ($format === self::FORMAT_DATE_YYYYMMDD || $format === self::FORMAT_DATE_YYYYMMDD2) {
                                         return date('Y-m-d', 1 * $value);
                                     } else {
                                         if ($format === self::FORMAT_DATE_DDMMYYYY) {
                                             return date('d/m/Y', 1 * $value);
                                         } else {
                                             if ($format === 'yyyy/mm/dd;@') {
                                                 return date('Y/m/d', 1 * $value);
                                             } else {
                                                 if ($format === self::FORMAT_CURRENCY_USD_SIMPLE) {
                                                     return '$' . number_format($value, 2);
                                                 } else {
                                                     if ($format === self::FORMAT_CURRENCY_USD) {
                                                         return '$' . number_format($value);
                                                     } else {
//.........这里部分代码省略.........

 private function _importADH(&$excelFile)
 {
     $worksheet = $excelFile->getSheetByName('ADH');
     $worksheetTitle = $worksheet->getTitle();
     $highestRow = $worksheet->getHighestRow();
     $highestColumn = 'O';
     $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
     //echo '<br>'. $worksheetTitle .'<table border="1"><tr>';
     ini_set('max_execution_time', '500');
     //SLW 15-06: initializer of the GN- (codes)
     $GNTable = array();
     $CountryTable = array();
     $dateVerdict;
     $dateIN;
     $dateOUT;
     $description;
     $status;
     $regulationCode;
     $regulationURL;
     $statusTime = 0;
     $commentTime = 0;
     $linkTime = 0;
     $dataSource = $this->CustomsDuty->getDataSource();
     $dataSource2 = $this->Code->getDataSource();
     $dataSource->begin();
     $dataSource2->begin();
     $timepre = microtime(true);
     for ($row = 2; $row <= $highestRow; ++$row) {
         $dateIN = date('d-m-Y', PHPExcel_Shared_Date::ExcelToPHP($worksheet->getCellByColumnAndRow(ADH::$IN_DATUM, $row)->getValue()));
         $dateOUT = date('d-m-Y', PHPExcel_Shared_Date::ExcelToPHP($worksheet->getCellByColumnAndRow(ADH::$EIND_DATUM, $row)->getValue()));
         array_push($GNTable, $worksheet->getCellByColumnAndRow(ADH::$GS_POST_1, $row)->getValue());
         array_push($GNTable, $worksheet->getCellByColumnAndRow(ADH::$GS_POST_2, $row)->getValue());
         array_push($GNTable, $worksheet->getCellByColumnAndRow(ADH::$GN_CODE_1, $row)->getValue());
         array_push($GNTable, $worksheet->getCellByColumnAndRow(ADH::$GN_CODE_2, $row)->getValue());
         array_push($CountryTable, $worksheet->getCellByColumnAndRow(ADH::$LAND_OORSPR, $row)->getValue());
         $CountryTable = $this->_tableCheck($CountryTable, false);
         array_push($GNTable, $worksheet->getCellByColumnAndRow(ADH::$UITGEBR_GCODE, $row)->getValue());
         $GNTable = $this->_tableCheck($GNTable, true);
         if ($worksheet->getCellByColumnAndRow(ADH::$BRON, $row)->hasHyperlink()) {
             $regulationURL = $worksheet->getCellByColumnAndRow(ADH::$BRON, $row)->getHyperlink()->getUrl();
         }
         if (PHPExcel_Shared_Date::isDateTime($worksheet->getCellByColumnAndRow(ADH::$DATUM_INBRENG, $row))) {
             $dateVerdict = trim($worksheet->getCellByColumnAndRow(ADH::$DATUM_INBRENG, $row)->getValue());
         }
         if ($dateVerdict !== NUll && $dateVerdict !== '') {
             $dateVerdict = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($worksheet->getCellByColumnAndRow(ADH::$DATUM_INBRENG, $row)->getValue()));
         }
         $dateIN = $worksheet->getCellByColumnAndRow(ADH::$IN_DATUM, $row)->getValue();
         if ($dateIN !== NULL) {
             $dateIN = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($dateIN));
         }
         $dateOUT = $worksheet->getCellByColumnAndRow(ADH::$EIND_DATUM, $row)->getValue();
         if ($dateOUT !== NULL) {
             $dateOUT = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($dateOUT));
         }
         $status = $worksheet->getCellByColumnAndRow(ADH::$STATUS, $row)->getValue();
         $time_pre = microtime(true);
         $this->_saveStatuses($status);
         $time_post = microtime(true);
         $statusTime += $time_post - $time_pre;
         // }
         $regulationCode = $worksheet->getCellByColumnAndRow(ADH::$VERORD_CODE, $row)->getValue();
         $this->_importRegulations($regulationURL, $regulationCode, $dateVerdict, $dateIN, $dateOUT, $status);
         $time_pre = microtime(true);
         $this->_saveComment($GNTable, $worksheet->getCellByColumnAndRow(ADH::$UITGEBR_OMSCHR, $row)->getValue());
         $time_post = microtime(true);
         $commentTime += $time_post - $time_pre;
         $time_pre = microtime(true);
         $this->_createLink($GNTable, $CountryTable, $regulationCode);
         $time_post = microtime(true);
         $linkTime += $time_post - $time_pre;
         $GNTable = array();
     }
     $timepro = microtime(true);
     echo "<br>time before commit: " . ($timepro - $timepre) . "<br>";
     $dataSource->commit();
     $dataSource2->commit();
     echo "status time: " . $statusTime . "<br>comment time: " . $commentTime . "<br>link time: " . $linkTime;
     $this->Controller->autoRender = false;
 }

 /**
  * Функция осуществляет преобразование в DateTime object
  * @param type $string строка дата/время полученная из Excel
  * @return \DateTime object
  * @throws \Exception
  */
 public function renderDateString($string)
 {
     if (!strlen($string)) {
         return null;
     }
     $date = \PHPExcel_Shared_Date::ExcelToPHP(trim($string));
     return new \DateTime(gmdate('d-m-Y H:i:s', $date));
 }

 /**
  * Read FORMULA record
  * This record contains the token array and the result of a
  * formula cell.
  *
  * --	"OpenOffice.org's Documentation of the Microsoft
  * 		Excel File Format"
  */
 private function _readFormula()
 {
     $pos = $this->_pos;
     $length = $this->_GetInt2d($this->_data, $pos + 2);
     $recordData = substr($this->_data, $pos + 4, $length);
     $pos += 4;
     // offset: 0; size: 2; row index
     $row = $this->_GetInt2d($this->_data, $pos);
     // offset: 2; size: 2; col index
     $column = $this->_GetInt2d($this->_data, $pos + 2);
     $columnString = PHPExcel_Cell::stringFromColumnIndex($column);
     // Read cell?
     if (!is_null($this->getReadFilter()) && $this->getReadFilter()->readCell($columnString, $row + 1, $this->_phpSheet->getTitle())) {
         // offset: 4; size: 2; XF index
         $xfindex = $this->_GetInt2d($this->_data, $pos + 4);
         // offset: 6; size: 8; result of the formula
         if (ord($this->_data[$pos + 6]) == 0 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
             //String formula. Result follows in appended STRING record
             $dataType = PHPExcel_Cell_DataType::TYPE_STRING;
             $soff = $pos + $length;
             $scode = $this->_GetInt2d($this->_data, $soff);
             $slength = $this->_GetInt2d($this->_data, $soff + 2);
             $sdata = substr($this->_data, $soff + 4, $slength);
             if ($this->_version == self::XLS_BIFF8) {
                 $string = $this->_readUnicodeStringLong($sdata);
                 $value = $string['value'];
             } else {
                 $string = $this->_readByteStringLong($sdata);
                 $value = $string['value'];
             }
         } elseif (ord($this->_data[$pos + 6]) == 1 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
             //Boolean formula. Result is in +2; 0=false,1=true
             $dataType = PHPExcel_Cell_DataType::TYPE_BOOL;
             $value = (bool) ord($this->_data[$pos + 8]);
         } elseif (ord($this->_data[$pos + 6]) == 2 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
             //Error formula. Error code is in +2
             $dataType = PHPExcel_Cell_DataType::TYPE_ERROR;
             $value = $this->_mapErrorCode(ord($this->_data[$pos + 8]));
         } elseif (ord($this->_data[$pos + 6]) == 3 && ord($this->_data[$pos + 12]) == 255 && ord($this->_data[$pos + 13]) == 255) {
             //Formula result is a null string
             $dataType = PHPExcel_Cell_DataType::TYPE_NULL;
             $value = '';
         } else {
             // forumla result is a number, first 14 bytes like _NUMBER record
             $dataType = PHPExcel_Cell_DataType::TYPE_NUMERIC;
             $value = $this->_createNumber($pos);
         }
         // add cell style
         if (!$this->_readDataOnly) {
             $this->_phpSheet->getStyle($columnString . ($row + 1))->applyFromArray($this->_xf[$xfindex]);
             if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
                 $value = PHPExcel_Shared_Date::ExcelToPHP($value);
             }
         }
         // offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
         // offset: 16: size: 4; not used
         // offset: 20: size: variable; formula structure
         $formulaStructure = substr($recordData, 20);
         // add cell value
         try {
             if ($this->_version != self::XLS_BIFF8) {
                 throw new Exception('Not BIFF8. Can only read BIFF8 formulas');
             }
             $formula = $this->_getFormulaFromStructure($formulaStructure);
             // get human language
             $this->_phpSheet->getCell($columnString . ($row + 1))->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
         } catch (Exception $e) {
             $this->_phpSheet->setCellValueExplicit($columnString . ($row + 1), $value, $dataType);
         }
     }
     // move stream pointer to next record
     $this->_pos += 4 + $length;
 }

 /**
  * EOMONTH
  *
  * Returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
  * Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
  *
  * @param	long	$dateValue			Excel date serial value or a standard date string
  * @param	int		$adjustmentMonths	Number of months to adjust by
  * @return	long	Excel date serial value
  */
 public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0)
 {
     $dateValue = self::flattenSingleValue($dateValue);
     $adjustmentMonths = floor(self::flattenSingleValue($adjustmentMonths));
     if (!is_numeric($adjustmentMonths)) {
         return self::$_errorCodes['value'];
     }
     if (is_string($dateValue = self::_getDateValue($dateValue))) {
         return self::$_errorCodes['value'];
     }
     // Execute function
     $PHPDateObject = self::_adjustDateByMonths($dateValue, $adjustmentMonths + 1);
     $adjustDays = (int) $PHPDateObject->format('d');
     $adjustDaysString = '-' . $adjustDays . ' days';
     $PHPDateObject->modify($adjustDaysString);
     switch (self::getReturnDateType()) {
         case self::RETURNDATE_EXCEL:
             return (double) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject);
             break;
         case self::RETURNDATE_PHP_NUMERIC:
             return (int) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject));
             break;
         case self::RETURNDATE_PHP_OBJECT:
             return $PHPDateObject;
             break;
     }
 }

 /**
  * @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);
 }

         $F = $objPHPExcel->getActiveSheet()->getCell('F' . $i)->getCalculatedValue();
         $G = $objPHPExcel->getActiveSheet()->getCell('G' . $i)->getCalculatedValue();
         $H = $objPHPExcel->getActiveSheet()->getCell('H' . $i)->getCalculatedValue();
         $I = $objPHPExcel->getActiveSheet()->getCell('I' . $i)->getCalculatedValue();
         $J = $objPHPExcel->getActiveSheet()->getCell('J' . $i)->getCalculatedValue();
         $K = $objPHPExcel->getActiveSheet()->getCell('K' . $i)->getCalculatedValue();
         $L = $objPHPExcel->getActiveSheet()->getCell('L' . $i)->getCalculatedValue();
         $M = $objPHPExcel->getActiveSheet()->getCell('M' . $i)->getCalculatedValue();
         $N = $objPHPExcel->getActiveSheet()->getCell('N' . $i)->getCalculatedValue();
         $fechaA = PHPExcel_Shared_Date::ExcelToPHP($A);
         $dateA = date('Y-m-d', $fechaA);
         $fechaB = PHPExcel_Shared_Date::ExcelToPHP($B);
         $dateB = date('H:i:s', $fechaB);
         $fechaC = PHPExcel_Shared_Date::ExcelToPHP($C);
         $dateC = date('Y-m-d', $fechaC);
         $fechaD = PHPExcel_Shared_Date::ExcelToPHP($D);
         $dateD = date('H:i:s', $fechaD);
         $H = utf8_decode($H);
         $L = utf8_decode($L);
         $N = utf8_decode($N);
         mysqli_query($cn, "INSERT INTO despacho_solicitud_temp (desde_fecha,desde_hora,hasta_fecha,hasta_hora,\n\t\t\t\t\t\t\t\t\t\t\t\tblock,tipo,circulacion_trenes,vias,desde_sector,hasta_sector,empresa,encargados,telefonos,descripcion) VALUES ('{$dateA}','{$dateB}','{$dateC}','{$dateD}','{$E}',\n\t\t\t\t\t\t\t\t\t\t\t\t'{$F}','{$G}','{$H}','{$I}','{$J}','{$K}','{$L}','{$M}','{$N}')");
         $query2 = "delete from despacho_solicitud_temp where block=''";
         mysqli_query($link, $query2);
         if ($objPHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue() == NULL) {
             $param = 1;
         }
         $i++;
         $contador = $contador + 1;
     }
     $totalingresos = $contador - 1;
 } else {

/**
 * Get date from Excel sheet for given column and row. Convert Excel date to format acceptable by TimeExpressionParser if necessary.
 * @param PHPExcel_Worksheet $po_sheet The work sheet
 * @param int $pn_row_num row number (zero indexed)
 * @param string|int $pm_col either column number (zero indexed) or column letter ('A', 'BC')
 * @param int $pn_offset Offset to adf to the timestamp (can be used to fix timezone issues or simple to move dates around a little bit)
 * @return string|null the date, if a value exists
 */
function caPhpExcelGetDateCellContent($po_sheet, $pn_row_num, $pm_col, $pn_offset = 0)
{
    if (!is_int($pn_offset)) {
        $pn_offset = 0;
    }
    if (!is_numeric($pm_col)) {
        $pm_col = PHPExcel_Cell::columnIndexFromString($pm_col) - 1;
    }
    $o_val = $po_sheet->getCellByColumnAndRow($pm_col, $pn_row_num);
    $vs_val = trim((string) $o_val);
    if (strlen($vs_val) > 0) {
        $vn_timestamp = PHPExcel_Shared_Date::ExcelToPHP(trim((string) $o_val->getValue())) + $pn_offset;
        if (!($vs_return = caGetLocalizedDate($vn_timestamp, array('dateFormat' => 'iso8601', 'timeOmit' => false)))) {
            $vs_return = $vs_val;
        }
    } else {
        $vs_return = null;
    }
    return $vs_return;
}

 public function update_stock_via_excel()
 {
     if (isset($_FILES['file']) && $_FILES['file']['size'] > 0) {
         $excel2 = PHPExcel_IOFactory::createReader('Excel2007');
         $excel2 = $objPHPExcel = $excel2->load($_FILES["file"]["tmp_name"]);
         // Empty Sheet
         $sheet = $objPHPExcel->getSheet(0);
         $highestRow = $sheet->getHighestRow();
         $highestColumn = $sheet->getHighestColumn();
         $temp = array();
         //  Loop through each row of the worksheet in turn
         for ($row = 2; $row <= $highestRow; $row++) {
             //  Read a row of data into an array
             $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
             if ($rowData[0][11] == 0) {
                 $unit_of_issue = "Pack_Size";
                 $total_units = $rowData[0][11];
                 $stock_level = $rowData[0][10];
             } elseif ($rowData[0][10] >= 1) {
                 $unit_of_issue = "Unit_Size";
                 $total_units = $rowData[0][10] * $rowData[0][6];
                 $stock_level = $rowData[0][10];
             }
             $InvDate = date('t M Y', PHPExcel_Shared_Date::ExcelToPHP($rowData[0][9]));
             array_push($temp, array('commodity_id' => $rowData[0][0], 'unit_size' => $rowData[0][5], 'batch_no' => $rowData[0][7], 'manu' => $rowData[0][8], 'expiry_date' => $InvDate, 'stock_level' => $stock_level, 'total_unit_count' => $rowData[0][6], 'unit_issue' => $unit_of_issue, 'total_units' => $total_units, 'source_of_item' => $rowData[0][3], 'supplier' => $rowData[0][2]));
         }
         unset($objPHPExcel);
         $this->autosave_update_stock($temp, $this->session->userdata('facility_id'));
     }
 }

//.........这里部分代码省略.........
                     /**
                      * RK
                      *
                      * This record represents a cell that contains an RK value
                      * (encoded integer or floating-point value). If a
                      * floating-point value cannot be encoded to an RK value,
                      * a NUMBER record will be written. This record replaces the
                      * record INTEGER written in BIFF2.
                      *
                      * --	"OpenOffice.org's Documentation of the Microsoft
                      * 		Excel File Format"
                      */
                     $row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
                     $column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
                     $rknum = $this->_GetInt4d($this->_data, $spos + 6);
                     $numValue = $this->_GetIEEE754($rknum);
                     /*
                     if ($this->_isDate($spos)) {
                     	list($string, $raw) = $this->_createDate($numValue);
                     } else {
                     	$raw = $numValue;
                     	if (isset($this->_columnsFormat[$column + 1])){
                     		$this->_curformat = $this->_columnsFormat[$column + 1];
                     	}
                     	$string = sprintf($this->_curformat,$numValue*$this->_multiplier);
                     }
                     */
                     // offset 4; size: 2; index to XF record
                     $xfindex = $this->_getInt2d($recordData, 4);
                     // add BIFF8 style information
                     if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
                         $sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
                         if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
                             $numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
                         }
                     }
                     //$this->_addcell($row, $column, $string, $raw);
                     //$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
                     $sheet->setCellValueByColumnAndRow($column, $row + 1, $numValue);
                     break;
                 case self::XLS_Type_LABELSST:
                     /**
                      * LABELSST
                      *
                      * This record represents a cell that contains a string. It
                      * replaces the LABEL record and RSTRING record used in
                      * BIFF2-BIFF5.
                      *
                      * --	"OpenOffice.org's Documentation of the Microsoft
                      * 		Excel File Format"
                      */
                     $row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
                     $column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
                     $xfindex = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
                     $index = $this->_GetInt4d($this->_data, $spos + 6);
                     //$this->_addcell($row, $column, $this->_sst[$index]);
                     if ($fmtRuns = $this->_sst[$index]['fmtRuns']) {
                         // then we have rich text
                         $richText = new PHPExcel_RichText($sheet->getCellByColumnAndRow($column, $row + 1));
                         $charPos = 0;
                         for ($i = 0; $i <= count($this->_sst[$index]['fmtRuns']); $i++) {
                             if (isset($fmtRuns[$i])) {
                                 $text = mb_substr($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos, 'UTF-8');
                                 $charPos = $fmtRuns[$i]['charPos'];
                             } else {
                                 $text = mb_substr($this->_sst[$index]['value'], $charPos, mb_strlen($this->_sst[$index]['value']), 'UTF-8');