PHP PHPExcel_Shared_Date::PHPToExcel方法代码示例

 public static function exportXlsx($data, $keys)
 {
     // Create new PHPExcel object
     $objPHPExcel = new \PHPExcel();
     // Set document properties
     $objPHPExcel->getProperties()->setCreator("Roadiz CMS")->setLastModifiedBy("Roadiz CMS")->setCategory("");
     $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     $cacheSettings = ['memoryCacheSize' => '8MB'];
     \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
     $objPHPExcel->setActiveSheetIndex(0);
     foreach ($keys as $key => $value) {
         $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value);
     }
     foreach ($data as $key => $answer) {
         foreach ($answer as $k => $value) {
             $columnAlpha = \PHPExcel_Cell::stringFromColumnIndex($k);
             if ($value instanceof \DateTime) {
                 $value = \PHPExcel_Shared_Date::PHPToExcel($value);
                 $objPHPExcel->getActiveSheet()->getStyle($columnAlpha . (2 + $key))->getNumberFormat()->setFormatCode('dd.mm.yyyy hh:MM:ss');
             }
             $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($k, 2 + $key, $value);
         }
     }
     // Set active sheet index to the first sheet, so Excel opens this as the first sheet
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     ob_start();
     $objWriter->save('php://output');
     $return = ob_get_clean();
     return $return;
 }

 protected function writeCell($value, $column, $row, $config)
 {
     // auto type
     if (!isset($config['type']) || $config['type'] === null) {
         $this->sheet->setCellValueByColumnAndRow($column, $row, $value);
     } elseif ($config['type'] === 'date') {
         if (!is_int($value)) {
             $timestamp = strtotime($value);
         }
         $this->sheet->SetCellValueByColumnAndRow($column, $row, \PHPExcel_Shared_Date::PHPToExcel($timestamp));
         if (!isset($config['styles']['numberformat']['code'])) {
             $config['styles']['numberformat']['code'] = $this->defaultDateFormat;
         }
     } elseif ($config['type'] === 'url') {
         if (isset($config['label'])) {
             if ($config['label'] instanceof \Closure) {
                 // NOTE: calculate label on top level
                 $label = call_user_func($config['label']);
             } else {
                 $label = $config['label'];
             }
         } else {
             $label = $value;
         }
         $urlValid = filter_var($value, FILTER_VALIDATE_URL) !== false;
         if (!$urlValid) {
             $label = '';
         }
         $this->sheet->setCellValueByColumnAndRow($column, $row, $label);
         if ($urlValid) {
             $this->sheet->getCellByColumnAndRow($column, $row)->getHyperlink()->setUrl($value);
         }
     } else {
         $this->sheet->setCellValueExplicitByColumnAndRow($column, $row, $value, $config['type']);
     }
     if (isset($config['styles'])) {
         $this->sheet->getStyleByColumnAndRow($column, $row)->applyFromArray($config['styles']);
     }
 }

 /**
  *  Output record line into file
  *
  *  @param      array		$array_selected_sorted      Array with list of field to export
  *  @param      resource	$objp                       A record from a fetch with all fields from select
  *  @param      Translate	$outputlangs                Object lang to translate values
  *  @param		array		$array_types				Array with types of fields
  * 	@return		int										<0 if KO, >0 if OK
  */
 function write_record($array_selected_sorted, $objp, $outputlangs, $array_types)
 {
     global $conf;
     // Create a format for the column headings
     if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
         $outputlangs->charset_output = 'ISO-8859-1';
         // Because Excel 5 format is ISO
     }
     // Define first row
     $this->col = 0;
     foreach ($array_selected_sorted as $code => $value) {
         if (strpos($code, ' as ') == 0) {
             $alias = str_replace(array('.', '-'), '_', $code);
         } else {
             $alias = substr($code, strpos($code, ' as ') + 4);
         }
         if (empty($alias)) {
             dol_print_error('', 'Bad value for field with code=' . $code . '. Try to redefine export.');
         }
         $newvalue = $objp->{$alias};
         $newvalue = $this->excel_clean($newvalue);
         $typefield = isset($array_types[$code]) ? $array_types[$code] : '';
         // Traduction newvalue
         if (preg_match('/^\\((.*)\\)$/i', $newvalue, $reg)) {
             $newvalue = $outputlangs->transnoentities($reg[1]);
         } else {
             $newvalue = $outputlangs->convToOutputCharset($newvalue);
         }
         if (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $newvalue)) {
             if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
                 $formatdate = $this->workbook->addformat();
                 $formatdate->set_num_format('yyyy-mm-dd');
                 //$formatdate->set_num_format(0x0f);
                 $arrayvalue = preg_split('/[.,]/', xl_parse_date($newvalue));
                 //print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
                 $newvalue = strval($arrayvalue[0]) . '.' . strval($arrayvalue[1]);
                 // $newvalue=strval(36892.521); directly does not work because . will be convert into , later
                 $this->worksheet->write($this->row, $this->col, $newvalue, PHPExcel_Shared_Date::PHPToExcel($formatdate));
             } else {
                 $newvalue = dol_stringtotime($newvalue);
                 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, PHPExcel_Shared_Date::PHPToExcel($newvalue));
                 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
                 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd');
             }
         } elseif (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]$/i', $newvalue)) {
             if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
                 $formatdatehour = $this->workbook->addformat();
                 $formatdatehour->set_num_format('yyyy-mm-dd hh:mm:ss');
                 //$formatdatehour->set_num_format(0x0f);
                 $arrayvalue = preg_split('/[.,]/', xl_parse_date($newvalue));
                 //print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
                 $newvalue = strval($arrayvalue[0]) . '.' . strval($arrayvalue[1]);
                 // $newvalue=strval(36892.521); directly does not work because . will be convert into , later
                 $this->worksheet->write($this->row, $this->col, $newvalue, $formatdatehour);
             } else {
                 $newvalue = dol_stringtotime($newvalue);
                 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, PHPExcel_Shared_Date::PHPToExcel($newvalue));
                 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
                 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
             }
         } else {
             if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
                 $this->worksheet->write($this->row, $this->col, $newvalue);
             } else {
                 if ($typefield == 'Text' || $typefield == 'TextAuto') {
                     //$this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row+1)->setValueExplicit($newvalue, PHPExcel_Cell_DataType::TYPE_STRING);
                     $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, (string) $newvalue);
                     $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
                     $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('@');
                     $this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                 } else {
                     $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
                 }
             }
         }
         $this->col++;
     }
     $this->row++;
     return 0;
 }

 public function execute()
 {
     $this->form_validation->set_rules('event', 'Event', 'trim');
     $this->form_validation->set_rules('date_from', 'Date From', 'trim');
     $this->form_validation->set_rules('date_to', 'Date To', 'trim');
     if ($this->form_validation->run() === false) {
         $this->session->set_flashdata('alert', '<div class="alert alert-danger">' . validation_errors() . '</div>');
         $data['content'] = $this->load->view('export', '', true);
         $this->load->view('template', $data);
     } else {
         ini_set('memory_limit', '-1');
         require_once "../assets/phpexcel/PHPExcel.php";
         $excel = new PHPExcel();
         $excel->setActiveSheetIndex(0);
         $active_sheet = $excel->getActiveSheet();
         $active_sheet->setTitle('Candidate');
         //style
         $active_sheet->getStyle("A1:AC1")->getFont()->setBold(true);
         //header
         $active_sheet->setCellValue('A1', 'No');
         $active_sheet->setCellValue('B1', 'Event');
         $active_sheet->setCellValue('C1', 'Serial Number');
         $active_sheet->setCellValue('D1', 'Name of Contacts');
         $active_sheet->setCellValue('E1', 'Job Title');
         $active_sheet->setCellValue('F1', 'Departement');
         $active_sheet->setCellValue('G1', 'Company');
         $active_sheet->setCellValue('H1', 'Telephone');
         $active_sheet->setCellValue('I1', 'Mobile');
         $active_sheet->setCellValue('J1', 'Actcode');
         $active_sheet->setCellValue('K1', 'New Name');
         $active_sheet->setCellValue('L1', 'New Title');
         $active_sheet->setCellValue('M1', 'New Telephone');
         $active_sheet->setCellValue('N1', 'New Mobile');
         $active_sheet->setCellValue('O1', 'Email');
         $active_sheet->setCellValue('P1', 'Mobile Sms');
         $active_sheet->setCellValue('Q1', 'Distribution Date');
         $active_sheet->setCellValue('R1', 'Status');
         $active_sheet->setCellValue('S1', 'Call History');
         $event = $this->input->post('event');
         $date_from = format_ymd($this->input->post('date_from'));
         $date_to = format_ymd($this->input->post('date_to'));
         $result = $this->export_model->export($event, $date_from, $date_to)->result();
         $i = 2;
         foreach ($result as $r) {
             $active_sheet->setCellValue('A' . $i, $i - 1);
             $active_sheet->setCellValue('B' . $i, $r->event_name);
             $active_sheet->setCellValueExplicit('C' . $i, $r->sn);
             $active_sheet->setCellValue('D' . $i, $r->name);
             $active_sheet->setCellValue('E' . $i, $r->title);
             $active_sheet->setCellValue('F' . $i, $r->dept);
             $active_sheet->setCellValue('G' . $i, $r->company);
             $active_sheet->setCellValueExplicit('H' . $i, $r->tlp);
             $active_sheet->setCellValueExplicit('I' . $i, $r->mobile);
             $active_sheet->setCellValue('J' . $i, $r->actcode);
             $active_sheet->setCellValue('K' . $i, $r->name_new);
             $active_sheet->setCellValue('L' . $i, $r->title_new);
             $active_sheet->setCellValueExplicit('M' . $i, $r->tlp_new);
             $active_sheet->setCellValueExplicit('N' . $i, $r->mobile_new);
             $active_sheet->setCellValue('O' . $i, $r->email);
             $active_sheet->setCellValueExplicit('P' . $i, $r->mobile_sms);
             $active_sheet->setCellValue('Q' . $i, PHPExcel_Shared_Date::PHPToExcel(date_to_excel($r->dist_date)));
             $active_sheet->getStyle('Q' . $i)->getNumberFormat()->setFormatCode('dd/mm/yyyy');
             $active_sheet->setCellValue('R' . $i, $r->status_name);
             $active_sheet->setCellValue('S' . $i, $this->callhis_model->get_note($r->id));
             $i++;
         }
         $filename = 'Candidate_' . date('Ymd_His') . '.xlsx';
         header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
         header('Content-Disposition: attachment;filename="' . $filename . '"');
         header('Cache-Control: max-age=0');
         $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
         $objWriter->save('php://output');
     }
 }

//.........这里部分代码省略.........
             // Map type
             $mappedType = $pCell->getDataType();
             // Write data type depending on its type
             switch (strtolower($mappedType)) {
                 case 'inlinestr':
                     // Inline string
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 's':
                     // String
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 'b':
                     // Boolean
                     $objWriter->writeAttribute('t', $mappedType);
                     break;
                 case 'f':
                     // Formula
                     $calculatedValue = null;
                     if ($this->getParentWriter()->getPreCalculateFormulas()) {
                         $calculatedValue = $pCell->getCalculatedValue();
                     } else {
                         $calculatedValue = $pCell->getValue();
                     }
                     if (is_string($calculatedValue)) {
                         $objWriter->writeAttribute('t', 'str');
                     }
                     break;
             }
             // Write data depending on its type
             switch (strtolower($mappedType)) {
                 case 'inlinestr':
                     // Inline string
                     if (!$pCell->getValue() instanceof PHPExcel_RichText) {
                         $objWriter->writeElement('t', PHPExcel_Shared_String::ControlCharacterPHP2OOXML($pCell->getValue()));
                     } else {
                         if ($pCell->getValue() instanceof PHPExcel_RichText) {
                             $objWriter->startElement('is');
                             $this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $pCell->getValue());
                             $objWriter->endElement();
                         }
                     }
                     break;
                 case 's':
                     // String
                     if (!$pCell->getValue() instanceof PHPExcel_RichText) {
                         if (isset($pFlippedStringTable[$pCell->getValue()])) {
                             $objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()]);
                         }
                     } else {
                         if ($pCell->getValue() instanceof PHPExcel_RichText) {
                             $objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()->getHashCode()]);
                         }
                     }
                     break;
                 case 'f':
                     // Formula
                     $objWriter->writeElement('f', substr($pCell->getValue(), 1));
                     if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
                         if ($this->getParentWriter()->getPreCalculateFormulas()) {
                             $calculatedValue = $pCell->getCalculatedValue();
                             if (substr($calculatedValue, 0, 1) != '#') {
                                 $objWriter->writeElement('v', $calculatedValue);
                             } else {
                                 $objWriter->writeElement('v', '0');
                             }
                         } else {
                             $objWriter->writeElement('v', '0');
                         }
                     }
                     break;
                 case 'n':
                     // Numeric
                     if (PHPExcel_Shared_Date::isDateTime($pCell)) {
                         $dateValue = $pCell->getValue();
                         if (is_string($dateValue)) {
                             //	Error string
                             $objWriter->writeElement('v', $pFlippedStringTable[$dateValue]);
                         } elseif (!is_float($dateValue)) {
                             //	PHP serialized date/time or date/time object
                             $objWriter->writeElement('v', PHPExcel_Shared_Date::PHPToExcel($dateValue));
                         } else {
                             //	Excel serialized date/time
                             $objWriter->writeElement('v', $dateValue);
                         }
                     } else {
                         $objWriter->writeElement('v', $pCell->getValue());
                     }
                     break;
                 case 'b':
                     // Boolean
                     $objWriter->writeElement('v', $pCell->getValue() ? '1' : '0');
                     break;
             }
         }
         $objWriter->endElement();
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }

 /**
  * Bind value to a cell
  *
  * @param PHPExcel_Cell $cell	Cell to bind value to
  * @param mixed $value			Value to bind in cell
  * @return boolean
  */
 public function bindValue(PHPExcel_Cell $cell, $value = null)
 {
     // Find out data type
     $dataType = parent::dataTypeForValue($value);
     // Style logic - strings
     if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
         // Check for percentage
         if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
             // Convert value to number
             $cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
             return true;
         }
         // Check for time e.g. '9:45', '09:45'
         if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) {
             list($h, $m) = explode(':', $value);
             $days = $h / 24 + $m / 1440;
             // Convert value to number
             $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);
             return true;
         }
         // Check for date
         if (strtotime($value) !== false) {
             // make sure we have UTC for the sake of strtotime
             $saveTimeZone = date_default_timezone_get();
             date_default_timezone_set('UTC');
             // Convert value to Excel date
             $cell->setValueExplicit(PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
             // restore original value for timezone
             date_default_timezone_set($saveTimeZone);
             return true;
         }
     }
     // Style logic - Numbers
     if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
         // Leading zeroes?
         if (preg_match('/^\\-?[0]+[0-9]*\\.?[0-9]*$/', $value)) {
             // Convert value to string
             $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
             return true;
         }
     }
     // Not bound yet? Use parent...
     return parent::bindValue($cell, $value);
 }

 private static function couponFirstPeriodDate($settlement, $maturity, $frequency, $next)
 {
     $months = 12 / $frequency;
     $result = PHPExcel_Shared_Date::ExcelToPHPObject($maturity);
     $eom = self::isLastDayOfMonth($result);
     while ($settlement < PHPExcel_Shared_Date::PHPToExcel($result)) {
         $result->modify('-' . $months . ' months');
     }
     if ($next) {
         $result->modify('+' . $months . ' months');
     }
     if ($eom) {
         $result->modify('-1 day');
     }
     return PHPExcel_Shared_Date::PHPToExcel($result);
 }

 /**
  * Fill worksheet from values in array
  *
  * @param array $source Source array
  * @param mixed $nullValue Value in source array that stands for blank cell
  * @param string $startCell Insert array starting from this cell address as the top left coordinate
  * @param bool $strictNullComparison Apply strict comparison when testing for null values in the array
  * @throws PhpExcelException
  * @return Worksheet
  */
 public function fromArray(array $source = null, $nullValue = null, $startCell = 'A1', $strictNullComparison = false)
 {
     foreach ($source as &$row) {
         if (is_array($row)) {
             foreach ($row as $key => $value) {
                 if ($value instanceof DateTime) {
                     $row[$key] = PhpOffice_PHPExcel_Shared_Date::PHPToExcel($value);
                 }
             }
         }
     }
     try {
         $this->sheet->fromArray($source, $nullValue, $startCell, $strictNullComparison);
     } catch (PhpOffice_PHPExcel_Exception $ex) {
         throw new PhpExcelException('Unable to paste the array to worksheet', $ex->getCode(), $ex);
     }
     return $this;
 }

 protected function renderFields($key, $value)
 {
     $renderTimeArray = array('p_loadTimeSlot', 'p_unloadTimeSlot', 'p_shipperEta', 'p_consigneeEta', 'p_shipperAta', 'p_shipperAtd', 'p_consigneeAta', 'p_consigneeAtd', 'to_shipperEta');
     $securityArray = array('p_securityFlag', 'p_stackFlag');
     if (in_array($key, $renderTimeArray)) {
         if (is_object($value)) {
             return \PHPExcel_Shared_Date::PHPToExcel($value);
         }
     } else {
         if (in_array($key, $securityArray)) {
             if ($value == true) {
                 return 'ДА';
             } else {
                 return 'НЕТ';
             }
         }
     }
     return $value;
 }

 /**
  * @return array
  */
 protected function makeExportData()
 {
     $rows = array();
     $rows[] = $this->excelHeader();
     /** @var $em \Doctrine\ORM\EntityManager */
     $em = \AppKernel::getStaticContainer()->get('doctrine');
     /** @var $query \Doctrine\ORM\QueryBuilder */
     $object = $em->getRepository('ApplicationSonataClientBundle:Garantie')->createQueryBuilder('g')->orderBy('g.date_decheance', 'ASC')->getQuery()->execute();
     $nom_de_la_banques = Garantie::getNomDeLaBanques();
     foreach ($object as $key => $row) {
         $date_decheance = $row->getDateDecheance() ? \PHPExcel_Shared_Date::PHPToExcel($row->getDateDecheance()) : '';
         /** @var $row Garantie */
         $cell = array('client' => (string) $row->getClient(), 'date_decheance' => $date_decheance, 'montant' => $row->getMontant(), 'nom_de_la_banques_id' => isset($nom_de_la_banques[$row->getNomDeLaBanquesId()]) ? $nom_de_la_banques[$row->getNomDeLaBanquesId()] : '', 'num_de_ganrantie' => $row->getNumDeGanrantie(), 'note' => $row->getNote(), 'client_date_fin_mission' => $row->getClient()->getDateFinMission() ? \PHPExcel_Shared_Date::PHPToExcel($row->getClient()->getDateFinMission()) : '', 'calc_day_date_decheance' => $date_decheance ? '=B' . ($key + 2) . '-TODAY()' : '');
         //format
         $this->excelCellFormat($key + 2, $row);
         $rows[] = $cell;
     }
     return $rows;
 }

 /**
  * Bind value to a cell
  *
  * @param PHPExcel_Cell $cell	Cell to bind value to
  * @param mixed $value			Value to bind in cell
  * @return boolean
  */
 public function bindValue(PHPExcel_Cell $cell, $value = null)
 {
     // Find out data type
     $dataType = parent::dataTypeForValue($value);
     // Style logic - strings
     if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
         // Check for percentage
         if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
             // Convert value to number
             $cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
             return true;
         }
         // Check for date
         if (strtotime($value) !== false) {
             // Convert value to Excel date
             $cell->setValueExplicit(PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
             return true;
         }
     }
     // Style logic - Numbers
     if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
         // Leading zeroes?
         if (preg_match('/^\\-?[0]+[0-9]*\\.?[0-9]*$/', $value)) {
             // Convert value to string
             $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
             return true;
         }
     }
     // Not bound yet? Use parent...
     return parent::bindValue($cell, $value);
 }

 /**
  * Функция проверяет корректность данных в конкретной строке импорта
  * @param integer $index числовой индекс проверяемой строки
  * @return array массив ошибок, или массив корректных данных
  */
 public function isImportRowCorrect($index)
 {
     $error = [];
     $result = [];
     //если у нас есть назаполненые поля, то прерываем загрузку
     if ($this->isNotEmptyFields($index) !== true) {
         return false;
     }
     $result['CUSTOMER'] = $this->getCustomerById($this->getCellValue('A', $index));
     if (!is_object($result['CUSTOMER'])) {
         $error[] = 'This customer not found in DB';
     }
     $result['LOADING_ITEM_TYPE'] = trim($this->getCellValue('B', $index));
     if (!$this->isFieldHasCorrectValue($result['LOADING_ITEM_TYPE'], ['Pallet', 'Container', 'Carton box', 'Crate'])) {
         $error[] = 'Field "Loading item type" has incorrect value';
     } else {
         $result['PACKAGE_TYPE'] = $this->getPackageTypeByName($result['LOADING_ITEM_TYPE']);
         if ($result['PACKAGE_TYPE'] == false) {
             $error[] = 'This "Loading item type" not found in DB';
         }
     }
     $result['CARGO_TYPE'] = trim($this->getCellValue('C', $index));
     if (!$this->isFieldHasCorrectValue($result['CARGO_TYPE'], ['General', 'HVC', 'ADR', 'Temperature controlled', 'Oversized', 'Other', 'LTL'])) {
         $error[] = 'Field "Cargo type" has incorrect value';
     } else {
         $result['TRUCKING_CARGO_TYPE'] = $this->getCargoTypeByName($result['CARGO_TYPE']);
         if ($result['TRUCKING_CARGO_TYPE'] == false || !is_object($result['TRUCKING_CARGO_TYPE'])) {
             $error[] = 'This "Cargo type" not found in DB';
         }
     }
     $result['SHIPPER'] = $this->getOrganizationById($this->getCellValue('L', $index));
     if (false == $result['SHIPPER']) {
         $error[] = 'This "Shipper" not found in DB';
     }
     $result['SHIPPER_ADDRESS'] = $this->getOrganizationLocationById($this->getCellValue('M', $index));
     if (false == $result['SHIPPER_ADDRESS']) {
         $error[] = 'This "Shipper address" not found in DB';
     }
     $result['CONSIGNEE'] = $this->getOrganizationById($this->getCellValue('O', $index));
     if (false == $result['CONSIGNEE']) {
         $error[] = 'This "Consignee" not found in DB';
     }
     $result['CONSIGNEE_ADDRESS'] = $this->getOrganizationLocationById($this->getCellValue('P', $index));
     if (false == $result['CONSIGNEE_ADDRESS']) {
         $error[] = 'This "Consignee address" not found in DB';
     }
     $result['LOADING_TIME_SLOT'] = $this->getCellValue('N', $index);
     $result['UNLOADING_TIME_SLOT'] = $this->getCellValue('Q', $index);
     if (empty($result['LOADING_TIME_SLOT']) && empty($result['UNLOADING_TIME_SLOT'])) {
         $error[] = 'One fields of "Loading time slot" or "Unload time slot" must not be empty';
     }
     //заполним Loading Time Slot
     if (empty($result['LOADING_TIME_SLOT'])) {
         $result['LOADING_TIME_SLOT'] = \PHPExcel_Shared_Date::PHPToExcel(new \DateTime());
     }
     if (is_object($result['TRUCKING_CARGO_TYPE']) && $result['TRUCKING_CARGO_TYPE']->getName() == 'LTL') {
         $result['TOTAL_WEIGHT'] = $this->getCellValue('J', $index);
         $result['TOTAL_VOLUME'] = $this->getCellValue('H', $index);
         if (empty($result['TOTAL_WEIGHT']) && empty($result['TOTAL_VOLUME'])) {
             $error[] = 'One fields of "Total weight" or "Total value" must not be empty';
         }
     } else {
         $result['TOTAL_WEIGHT'] = $this->getCellValue('J', $index);
         $result['ITEM_QUANTITY'] = $this->getCellValue('I', $index);
         if (empty($result['TOTAL_WEIGHT']) || empty($result['ITEM_QUANTITY'])) {
             $error[] = 'Fields "Total weight" and "Item quantity" must not be empty';
         }
     }
     $result['STACKABILITY'] = $this->getCellValue('K', $index);
     if (strlen($result['STACKABILITY']) && !$this->isFieldHasCorrectValue($result['STACKABILITY'], ['YES', 'NO'])) {
         $error[] = 'Field "Stackability" has incorrect value';
     } else {
         $result['STACKABILITY'] = strtoupper($result['STACKABILITY']) == 'YES' ? 1 : 0;
     }
     $result['SECURITY'] = $this->getCellValue('R', $index);
     if (strlen($result['SECURITY']) && !$this->isFieldHasCorrectValue($result['SECURITY'], ['YES', 'NO'])) {
         $error[] = 'Field "Security" has incorrect value';
     } else {
         $result['SECURITY'] = strtoupper($result['SECURITY']) == 'YES' ? 1 : 0;
     }
     if ($result['SECURITY'] == 1) {
         $result['SECURITY_TYPE'] = trim($this->getCellValue('S', $index));
         if (!$this->isFieldHasCorrectValue($result['SECURITY_TYPE'], ['guard', 'convoy'])) {
             $error[] = 'Field "Security type" has incorrect value';
         } else {
             $result['SECURITY_TYPE'] = $this->getSecurityTypeByName($result['SECURITY_TYPE']);
             if ($result['SECURITY_TYPE'] == false) {
                 $error[] = 'This "Security type" not found in DB';
             }
         }
     } else {
         $result['SECURITY_TYPE'] = null;
     }
     if (!count($error)) {
         return ['SUCCESS' => true, 'RESULT' => $result];
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                                         break;
                                     case 'percentage':
                                         $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
                                         $dataValue = (double) $cellDataOfficeAttributes['value'];
                                         if (floor($dataValue) == $dataValue) {
                                             $dataValue = (int) $dataValue;
                                         }
                                         $formatting = PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00;
                                         break;
                                     case 'currency':
                                         $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
                                         $dataValue = (double) $cellDataOfficeAttributes['value'];
                                         if (floor($dataValue) == $dataValue) {
                                             $dataValue = (int) $dataValue;
                                         }
                                         $formatting = PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE;
                                         break;
                                     case 'float':
                                         $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
                                         $dataValue = (double) $cellDataOfficeAttributes['value'];
                                         if (floor($dataValue) == $dataValue) {
                                             if ($dataValue == (int) $dataValue) {
                                                 $dataValue = (int) $dataValue;
                                             } else {
                                                 $dataValue = (double) $dataValue;
                                             }
                                         }
                                         break;
                                     case 'date':
                                         $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
                                         $dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT);
                                         $dateObj->setTimeZone($timezoneObj);
                                         list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s'));
                                         $dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second);
                                         if ($dataValue != floor($dataValue)) {
                                             $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15 . ' ' . PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
                                         } else {
                                             $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15;
                                         }
                                         break;
                                     case 'time':
                                         $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
                                         $dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS'))));
                                         $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
                                         break;
                                 }
                                 //									echo 'Data value is '.$dataValue.'<br />';
                                 //									if ($hyperlink !== NULL) {
                                 //										echo 'Hyperlink is '.$hyperlink.'<br />';
                                 //									}
                             } else {
                                 $type = PHPExcel_Cell_DataType::TYPE_NULL;
                                 $dataValue = NULL;
                             }
                             if ($hasCalculatedValue) {
                                 $type = PHPExcel_Cell_DataType::TYPE_FORMULA;
                                 //									echo 'Formula: ', $cellDataFormula, PHP_EOL;
                                 $cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1);
                                 $temp = explode('"', $cellDataFormula);
                                 $tKey = false;
                                 foreach ($temp as &$value) {
                                     //	Only replace in alternate array entries (i.e. non-quoted blocks)
                                     if ($tKey = !$tKey) {
                                         $value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+):\\.([^\\.]+)\\]/Ui', '$1!$2:$3', $value);
                                         //  Cell range reference in another sheet
                                         $value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+)\\]/Ui', '$1!$2', $value);

 */
/** Error reporting */
error_reporting(E_ALL);
/** Include PHPExcel */
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
// Create new PHPExcel object
echo date('H:i:s'), " Create new PHPExcel object", EOL;
$objPHPExcel = new PHPExcel();
// Set document properties
echo date('H:i:s'), " Set document properties", EOL;
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
// Create a first sheet, representing sales data
echo date('H:i:s'), " Add some data", EOL;
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Invoice');
$objPHPExcel->getActiveSheet()->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, date('m'), date('d'), date('Y'))));
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);
$objPHPExcel->getActiveSheet()->setCellValue('E1', '#12566');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Product Id');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Description');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Price');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Amount');
$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Total');
$objPHPExcel->getActiveSheet()->setCellValue('A4', '1001');
$objPHPExcel->getActiveSheet()->setCellValue('B4', 'PHP for dummies');
$objPHPExcel->getActiveSheet()->setCellValue('C4', '20');
$objPHPExcel->getActiveSheet()->setCellValue('D4', '1');
$objPHPExcel->getActiveSheet()->setCellValue('E4', '=IF(D4<>"",C4*D4,"")');
$objPHPExcel->getActiveSheet()->setCellValue('A5', '1012');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'OpenXML for dummies');
$objPHPExcel->getActiveSheet()->setCellValue('C5', '22');

 /**
  *
  * @param integer $year
  * @param string $uid
  *
  * @return unknown|\Symfony\Component\HttpFoundation\RedirectResponse
  */
 public function excelYearAction($year, $uid)
 {
     $urlFrom = $this->getReferer();
     if (null == $urlFrom || trim($urlFrom) == '') {
         $urlFrom = $this->generateUrl('_admin_company_list');
     }
     $em = $this->getEntityManager();
     try {
         $company = $em->getRepository('AcfDataBundle:Company')->find($uid);
         if (null == $company) {
             $this->flashMsgSession('warning', $this->translate('Company.edit.notfound'));
         } else {
             $mbpurchases = $em->getRepository('AcfDataBundle:MBPurchase')->getAllByYearCompany($year, $company);
             $buys = array();
             foreach ($mbpurchases as $mbpurchase) {
                 $buys = array_merge($buys, $mbpurchase->getTransactions()->toArray());
             }
             $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();
             $phpExcelObject->getProperties()->setCreator('Salah Abdelkader Seif Eddine')->setLastModifiedBy($this->getSecurityTokenStorage()->getToken()->getUser()->getFullname())->setTitle($this->translate('pagetitle.buy.list'))->setSubject($this->translate('pagetitle.buy.list'))->setDescription($this->translate('pagetitle.buy.list'))->setKeywords($this->translate('pagetitle.buy.list'))->setCategory('ACEF buy');
             $phpExcelObject->setActiveSheetIndex(0);
             $workSheet = $phpExcelObject->getActiveSheet();
             $workSheet->setTitle($this->translate('pagetitle.buy.listExcel', array('%mbpurchase%' => $year)));
             $workSheet->setCellValue('A1', $this->translate('Buy.number.label'));
             $workSheet->getStyle('A1')->getFont()->setBold(true);
             $workSheet->setCellValue('B1', $this->translate('Buy.dtActivation.label'));
             $workSheet->getStyle('B1')->getFont()->setBold(true);
             $workSheet->setCellValue('C1', $this->translate('Buy.bill.label'));
             $workSheet->getStyle('C1')->getFont()->setBold(true);
             $workSheet->setCellValue('D1', $this->translate('Buy.relation.label'));
             $workSheet->getStyle('D1')->getFont()->setBold(true);
             $workSheet->setCellValue('E1', $this->translate('Buy.relation.number'));
             $workSheet->getStyle('E1')->getFont()->setBold(true);
             $workSheet->setCellValue('F1', $this->translate('Buy.label.label'));
             $workSheet->getStyle('F1')->getFont()->setBold(true);
             $workSheet->setCellValue('G1', $this->translate('Buy.balanceHt.label'));
             $workSheet->getStyle('G1')->getFont()->setBold(true);
             $workSheet->setCellValue('H1', $this->translate('Buy.vat.label'));
             $workSheet->getStyle('H1')->getFont()->setBold(true);
             $workSheet->setCellValue('I1', $this->translate('Buy.stamp.label'));
             $workSheet->getStyle('I1')->getFont()->setBold(true);
             $workSheet->setCellValue('J1', $this->translate('Buy.balanceTtc.label'));
             $workSheet->getStyle('J1')->getFont()->setBold(true);
             $workSheet->setCellValue('K1', $this->translate('Buy.regime.label'));
             $workSheet->getStyle('K1')->getFont()->setBold(true);
             $workSheet->setCellValue('L1', $this->translate('Buy.withholding.label'));
             $workSheet->getStyle('L1')->getFont()->setBold(true);
             $workSheet->setCellValue('M1', $this->translate('Buy.withholding.value.label'));
             $workSheet->getStyle('M1')->getFont()->setBold(true);
             $workSheet->setCellValue('N1', $this->translate('Buy.balanceNet.label'));
             $workSheet->getStyle('N1')->getFont()->setBold(true);
             $workSheet->setCellValue('O1', $this->translate('Buy.paymentType.label'));
             $workSheet->getStyle('O1')->getFont()->setBold(true);
             $workSheet->setCellValue('P1', $this->translate('Buy.dtPayment.label'));
             $workSheet->getStyle('P1')->getFont()->setBold(true);
             $workSheet->setCellValue('Q1', $this->translate('Buy.account.label'));
             $workSheet->getStyle('Q1')->getFont()->setBold(true);
             $workSheet->setCellValue('R1', $this->translate('Buy.nature.label'));
             $workSheet->getStyle('R1')->getFont()->setBold(true);
             $workSheet->setCellValue('S1', $this->translate('Buy.transactionStatus.label'));
             $workSheet->getStyle('S1')->getFont()->setBold(true);
             $workSheet->setCellValue('T1', $this->translate('Buy.otherInfos.label'));
             $workSheet->getStyle('T1')->getFont()->setBold(true);
             $workSheet->getStyle('A1:T1')->applyFromArray(array('fill' => array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '94ccdf'))));
             $suppliersConstStr = $em->getRepository('AcfDataBundle:ConstantStr')->findOneBy(array('name' => 'suppliersPrefix'));
             if (null == $suppliersConstStr) {
                 $suppliersConstStr = new ConstantStr();
                 $suppliersConstStr->setName('suppliersPrefix');
                 $suppliersConstStr->setValue('401');
                 $em->persist($suppliersConstStr);
                 $em->flush();
             }
             $suppliersPrefix = $suppliersConstStr->getValue();
             $this->gvars['suppliersPrefix'] = $suppliersPrefix;
             $i = 1;
             // $currencyFormatter = new \NumberFormatter($this->getRequest()->getLocale(), \NumberFormatter::CURRENCY);
             // $balance = $currencyFormatter->formatCurrency($balance, 'TND');
             foreach ($buys as $buy) {
                 $i++;
                 $workSheet->setCellValue('A' . $i, $buy->getNumber(), \PHPExcel_Cell_DataType::TYPE_STRING2);
                 $workSheet->setCellValue('B' . $i, \PHPExcel_Shared_Date::PHPToExcel($buy->getDtActivation()), \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                 $workSheet->getStyle('B' . $i)->getNumberFormat()->setFormatCode('dd/mm/yyyy');
                 $workSheet->setCellValue('C' . $i, $buy->getBill(), \PHPExcel_Cell_DataType::TYPE_STRING2);
                 $workSheet->setCellValue('D' . $i, $buy->getRelation()->getLabel(), \PHPExcel_Cell_DataType::TYPE_STRING2);
                 $numb = $suppliersPrefix . $buy->getRelation()->getNumberFormated();
                 $workSheet->setCellValueExplicit('E' . $i, $numb, \PHPExcel_Cell_DataType::TYPE_STRING2);
                 $workSheet->setCellValue('F' . $i, $buy->getLabel(), \PHPExcel_Cell_DataType::TYPE_STRING2);
                 $balanceHt = $buy->getBalanceTtc() - $buy->getStamp() - $buy->getVat();
                 // $balanceHt = $currencyFormatter->formatCurrency($balanceHt, 'TND');
                 $workSheet->setCellValue('G' . $i, $balanceHt);
                 $workSheet->getStyle('G' . $i)->getNumberFormat()->setFormatCode('#,##0.000');
                 $workSheet->setCellValue('H' . $i, $buy->getVat());
                 $workSheet->getStyle('H' . $i)->getNumberFormat()->setFormatCode('#,##0.000');
                 $workSheet->setCellValue('I' . $i, $buy->getStamp());
//.........这里部分代码省略.........