PHP PHPExcel_Shared_Date::ExcelToPHPObject方法代码示例

 public function convert($input)
 {
     if (!$input) {
         return;
     }
     if (is_numeric($input) && $input < 100000) {
         //Date may be 42338 (=> 30.11.2015
         $date = \PHPExcel_Shared_Date::ExcelToPHPObject($input);
         return $this->formatOutput($date);
     }
     return parent::convert($input);
 }

 private static function _coupFirstPeriodDate($settlement, $maturity, $frequency, $next)
 {
     $months = 12 / $frequency;
     $result = PHPExcel_Shared_Date::ExcelToPHPObject($maturity);
     $eom = self::_lastDayOfMonth($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);
 }

 /**
  * YEAR
  *
  * @param	long	$dateValue		Excel date serial value or a standard date string
  * @return	int		Year
  */
 public static function YEAR($dateValue = 1)
 {
     $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue);
     if (is_string($dateValue = self::_getDateValue($dateValue))) {
         return PHPExcel_Calculation_Functions::VALUE();
     } elseif ($dateValue < 0.0) {
         return PHPExcel_Calculation_Functions::NaN();
     }
     // Execute function
     $PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($dateValue);
     return (int) $PHPDateObject->format('Y');
 }

 /**
  * Convert a value in a pre-defined format to a PHP string
  *
  * @param mixed	$value		Value to format
  * @param string	$format		Format code
  * @param array		$callBack	Callback function for additional formatting of string
  * @return string	Formatted string
  */
 public static function toFormattedString($value = '', $format = '', $callBack = null)
 {
     // For now we do not treat strings although section 4 of a format code affects strings
     if (!is_numeric($value)) {
         return $value;
     }
     // For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
     // it seems to round numbers to a total of 10 digits.
     if ($format === PHPExcel_Style_NumberFormat::FORMAT_GENERAL || $format === PHPExcel_Style_NumberFormat::FORMAT_TEXT) {
         return $value;
     }
     // Get the sections, there can be up to four sections
     $sections = explode(';', $format);
     // Fetch the relevant section depending on whether number is positive, negative, or zero?
     // Text not supported yet.
     // Here is how the sections apply to various values in Excel:
     //   1 section:   [POSITIVE/NEGATIVE/ZERO/TEXT]
     //   2 sections:  [POSITIVE/ZERO/TEXT] [NEGATIVE]
     //   3 sections:  [POSITIVE/TEXT] [NEGATIVE] [ZERO]
     //   4 sections:  [POSITIVE] [NEGATIVE] [ZERO] [TEXT]
     switch (count($sections)) {
         case 1:
             $format = $sections[0];
             break;
         case 2:
             $format = $value >= 0 ? $sections[0] : $sections[1];
             $value = abs($value);
             // Use the absolute value
             break;
         case 3:
             $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
             $value = abs($value);
             // Use the absolute value
             break;
         case 4:
             $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
             $value = abs($value);
             // Use the absolute value
             break;
         default:
             // something is wrong, just use first section
             $format = $sections[0];
             break;
     }
     // Save format with color information for later use below
     $formatColor = $format;
     // Strip color information
     $color_regex = '/^\\[[a-zA-Z]+\\]/';
     $format = preg_replace($color_regex, '', $format);
     // Let's begin inspecting the format and converting the value to a formatted string
     if (preg_match('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])*[hmsdy]/i', $format)) {
         // datetime format
         // dvc: convert Excel formats to PHP date formats
         // strip off first part containing e.g. [$-F800] or [$USD-409]
         // general syntax: [$<Currency string>-<language info>]
         // language info is in hexadecimal
         $format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format);
         // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
         $format = strtolower($format);
         $format = strtr($format, self::$_dateFormatReplacements);
         if (!strpos($format, 'A')) {
             // 24-hour time format
             $format = strtr($format, self::$_dateFormatReplacements24);
         } else {
             // 12-hour time format
             $format = strtr($format, self::$_dateFormatReplacements12);
         }
         $dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($value);
         $value = $dateObj->format($format);
     } else {
         if (preg_match('/%$/', $format)) {
             // % number format
             if ($format === self::FORMAT_PERCENTAGE) {
                 $value = round(100 * $value, 0) . '%';
             } else {
                 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);
                 $value = sprintf($format, 100 * $value);
             }
         } else {
             if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
                 $value = 'EUR ' . sprintf('%1.2f', $value);
             } else {
                 // In Excel formats, "_" is used to add spacing, which we can't do in HTML
                 $format = preg_replace('/_./', '', $format);
                 // Some non-number characters are escaped with \, which we don't need
//.........这里部分代码省略.........

 private static function _formatAsDate(&$value, &$format)
 {
     // dvc: convert Excel formats to PHP date formats
     // strip off first part containing e.g. [$-F800] or [$USD-409]
     // general syntax: [$<Currency string>-<language info>]
     // language info is in hexadecimal
     $format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format);
     // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
     $format = strtolower($format);
     $format = strtr($format, self::$_dateFormatReplacements);
     if (!strpos($format, 'A')) {
         // 24-hour time format
         $format = strtr($format, self::$_dateFormatReplacements24);
     } else {
         // 12-hour time format
         $format = strtr($format, self::$_dateFormatReplacements12);
     }
     $dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($value);
     $value = $dateObj->format($format);
 }

 protected function convertCellDate($cellValue)
 {
     if ($cellValue == '') {
         return null;
     }
     if (is_numeric($cellValue)) {
         $d = PHPExcel_Shared_Date::ExcelToPHPObject($cellValue);
     } else {
         $cellValue = str_replace(array('/', '.'), '-', $cellValue);
         $a = explode('-', $cellValue);
         if (count($a) == 3 && is_numeric($a[0]) && is_numeric($a[1]) && is_numeric($a[2])) {
             $d = DateTime::createFromFormat('Y-m-d', "{$a[2]}-{$a[1]}-{$a[0]}");
         } else {
             $this->logger->warning(_("Data non valida \"{$cellValue}\""));
             return null;
         }
     }
     return $d->format('Y-m-d');
 }

 /**
  *
  * @param string $uid
  *
  * @return Response
  */
 public function editPostAction($uid)
 {
     $urlFrom = $this->getReferer();
     if (null == $urlFrom || trim($urlFrom) == '') {
         $urlFrom = $this->generateUrl('_admin_company_list');
     }
     $em = $this->getEntityManager();
     try {
         $mbpurchase = $em->getRepository('AcfDataBundle:MBPurchase')->find($uid);
         if (null == $mbpurchase) {
             $this->flashMsgSession('warning', $this->translate('MBPurchase.edit.notfound'));
         } else {
             $traces = $em->getRepository('AcfDataBundle:Trace')->getAllByEntityId($mbpurchase->getId(), Trace::AE_MBPURCHASE);
             $this->gvars['traces'] = array_reverse($traces);
             $mbpurchaseUpdateCountForm = $this->createForm(MBPurchaseUpdateCountTForm::class, $mbpurchase);
             $buy = new Buy();
             $buy->setMonthlyBalance($mbpurchase);
             $buyNewForm = $this->createForm(BuyNewTForm::class, $buy, array('monthlybalance' => $mbpurchase));
             $buyImportForm = $this->createForm(BuyImportTForm::class);
             $mbpurchaseUpdateDocsForm = $this->createForm(MBPurchaseUpdateDocsTForm::class, $mbpurchase, array('company' => $mbpurchase->getCompany()));
             $doc = new Doc();
             $doc->setCompany($mbpurchase->getCompany());
             $docNewForm = $this->createForm(DocNewTForm::class, $doc, array('company' => $mbpurchase->getCompany()));
             $this->gvars['tabActive'] = $this->getSession()->get('tabActive', 2);
             $this->getSession()->remove('tabActive');
             $this->gvars['stabActive'] = $this->getSession()->get('stabActive', 1);
             $this->getSession()->remove('stabActive');
             $request = $this->getRequest();
             $reqData = $request->request->all();
             $cloneMBPurchase = clone $mbpurchase;
             if (isset($reqData['BuyImportForm'])) {
                 $this->gvars['tabActive'] = 2;
                 $this->getSession()->set('tabActive', 2);
                 $this->gvars['stabActive'] = 1;
                 $this->getSession()->set('stabActive', 1);
                 $buyImportForm->handleRequest($request);
                 if ($buyImportForm->isValid()) {
                     ini_set('memory_limit', '4096M');
                     ini_set('max_execution_time', '0');
                     $extension = $buyImportForm['excel']->getData()->guessExtension();
                     if ($extension == 'zip') {
                         $extension = 'xlsx';
                     }
                     $filename = uniqid() . '.' . $extension;
                     $buyImportForm['excel']->getData()->move($this->getParameter('adapter_files'), $filename);
                     $fullfilename = $this->getParameter('adapter_files');
                     $fullfilename .= '/' . $filename;
                     $excelObj = $this->get('phpexcel')->createPHPExcelObject($fullfilename);
                     $log = '';
                     $iterator = $excelObj->getWorksheetIterator();
                     $activeSheetIndex = -1;
                     $i = 0;
                     foreach ($iterator as $worksheet) {
                         $worksheetTitle = $worksheet->getTitle();
                         $highestRow = $worksheet->getHighestRow();
                         // e.g. 10
                         $highestColumn = $worksheet->getHighestColumn();
                         // e.g 'F'
                         $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
                         $log .= "Feuille : '" . $worksheetTitle . "' trouvée contenant " . $highestRow . ' lignes et ' . $highestColumnIndex . ' colonnes avec comme plus grand index ' . $highestColumn . ' <br>';
                         if (\trim($worksheetTitle) == 'Sage') {
                             $activeSheetIndex = $i;
                         }
                         $i++;
                     }
                     if ($activeSheetIndex == -1) {
                         $log .= "Aucune Feuille de Titre 'Sage' trouvée tentative d'import depuis le première Feuille<br>";
                         $activeSheetIndex = 0;
                     }
                     $excelObj->setActiveSheetIndex($activeSheetIndex);
                     $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();
                     $suppliersPrefixNum = \intval($suppliersPrefix) * 1000000000;
                     $worksheet = $excelObj->getActiveSheet();
                     $highestRow = $worksheet->getHighestRow();
                     $lineRead = 0;
                     $buysNew = 0;
                     $lineUnprocessed = 0;
                     $lineError = 0;
                     $company = $mbpurchase->getCompany();
                     $accounts = $em->getRepository('AcfDataBundle:Account')->getAllByCompany($company);
                     $suppliers = $em->getRepository('AcfDataBundle:Supplier')->getAllByCompany($company);
                     $companyNatures = $em->getRepository('AcfDataBundle:CompanyNature')->getAllByCompany($company);
                     $withholdings = $em->getRepository('AcfDataBundle:Withholding')->getAllByCompany($company);
                     for ($row = 1; $row <= $highestRow; $row++) {
                         $lineRead++;
                         $dtActivation = \PHPExcel_Shared_Date::ExcelToPHPObject($worksheet->getCellByColumnAndRow(1, $row)->getValue());
//.........这里部分代码省略.........

 /**
  *
  * @return \Symfony\Component\HttpFoundation\RedirectResponse
  */
 public function importPostAction()
 {
     if (!$this->hasRole('ROLE_SUPERADMIN')) {
         return $this->redirect($this->generateUrl('_admin_homepage'));
     }
     $urlFrom = $this->getReferer();
     if (null == $urlFrom || trim($urlFrom) == '') {
         $urlFrom = $this->generateUrl('_admin_bulletinInfo_list');
     }
     $bulletinInfoImportForm = $this->createForm(BulletinInfoImportTForm::class);
     $request = $this->getRequest();
     $reqData = $request->request->all();
     if (isset($reqData['BulletinInfoImportForm'])) {
         $bulletinInfoImportForm->handleRequest($request);
         if ($bulletinInfoImportForm->isValid()) {
             ini_set('memory_limit', '4096M');
             ini_set('max_execution_time', '0');
             $extension = $bulletinInfoImportForm['excel']->getData()->guessExtension();
             if ($extension == 'zip') {
                 $extension = 'xlsx';
             }
             $filename = uniqid() . '.' . $extension;
             $bulletinInfoImportForm['excel']->getData()->move($this->getParameter('adapter_files'), $filename);
             $fullfilename = $this->getParameter('adapter_files');
             $fullfilename .= '/' . $filename;
             $excelObj = $this->get('phpexcel')->createPHPExcelObject($fullfilename);
             $log = '';
             $iterator = $excelObj->getWorksheetIterator();
             $activeSheetIndex = -1;
             $i = 0;
             $bulletinInfo = new BulletinInfo();
             foreach ($iterator as $worksheet) {
                 $worksheetTitle = $worksheet->getTitle();
                 $highestRow = $worksheet->getHighestRow();
                 // e.g. 10
                 $highestColumn = $worksheet->getHighestColumn();
                 // e.g 'F'
                 $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
                 $log .= "Feuille : '" . $worksheetTitle . "' trouvée contenant " . $highestRow . ' lignes et ' . $highestColumnIndex . ' colonnes avec comme plus grand index ' . $highestColumn . ' <br>';
                 if (\trim($worksheetTitle) == 'BulletinInfo') {
                     $activeSheetIndex = $i;
                 }
                 $i++;
             }
             if ($activeSheetIndex == -1) {
                 $log .= "Aucune Feuille de Titre 'BulletinInfo' trouvée tentative d'import depuis le première Feuille<br>";
                 $activeSheetIndex = 0;
             }
             $excelObj->setActiveSheetIndex($activeSheetIndex);
             $worksheet = $excelObj->getActiveSheet();
             $highestRow = $worksheet->getHighestRow();
             $lineRead = 0;
             $lineUnprocessed = 0;
             $bulletinInfoTitleNew = 0;
             $bulletinInfoContentNew = 0;
             $lineError = 0;
             $haserror = false;
             if ($highestRow < 3) {
                 $log .= 'Fichier Excel Invalide<br>';
                 $haserror = true;
             } else {
                 $lineRead++;
                 $num = \trim(\intval($worksheet->getCellByColumnAndRow(8, 3)->getValue()));
                 $dtStart = \PHPExcel_Shared_Date::ExcelToPHPObject($worksheet->getCellByColumnAndRow(10, 3)->getValue());
                 if ($num <= 0) {
                     $log .= 'Numéro de bulletin illisible (' . $num . ')<br>';
                     $haserror = true;
                     $lineError++;
                 }
                 if (!$dtStart instanceof \DateTime) {
                     $log .= 'Date du bulletin illisible (' . $dtStart . ')<br>';
                     $haserror = true;
                     $lineError++;
                 }
             }
             if (!$haserror) {
                 $em = $this->getEntityManager();
                 $bulletinInfoTest = $em->getRepository('AcfDataBundle:BulletinInfo')->findOneBy(array('num' => $num));
                 if (null != $bulletinInfoTest) {
                     $log .= 'Numéro de bulletin déjà existant<br>';
                     $haserror = true;
                 }
             }
             if (!$haserror) {
                 $bulletinInfo->setNum($num);
                 $bulletinInfo->setDtStart($dtStart);
                 $em->persist($bulletinInfo);
                 $lineRead = 5;
                 $isTitle = false;
                 $isContent = false;
                 $titles = array();
                 // $logger = $this->getLogger();
                 for ($row = $lineRead - 1; $row <= $highestRow; $row++) {
                     $isTitle = false;
                     $isContent = false;
                     $canBeTitle = false;
//.........这里部分代码省略.........

 public function exceltravel_upload()
 {
     if (Input::file('file') == '') {
         return View::make('admin.upexceltravel', array('status' => 'ไม่สามารถอัพข้อมูลได้'));
     }
     $destinationPath = 'upload_excel';
     $filename = Input::file('file')->getClientOriginalName();
     $uploadSuccess = Input::file('file')->move($destinationPath, $filename);
     $objPHPExcel = PHPExcel_IOFactory::load("upload_excel/" . $filename);
     $isheet = 0;
     $isheetrow = 0;
     foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
         $isheet++;
         $worksheetTitle = $worksheet->getTitle();
         $highestRow = $worksheet->getHighestRow();
         // e.g. 10
         $highestColumn = $worksheet->getHighestColumn();
         // e.g 'F'
         $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
         $nrColumns = ord($highestColumn) - 64;
         for ($row = 3; $row <= $highestRow; ++$row) {
             $isheetrow++;
             $val = array();
             for ($col = 0; $col < $highestColumnIndex; ++$col) {
                 $cell = $worksheet->getCellByColumnAndRow($col, $row);
                 $val[] = $cell->getValue();
             }
             $dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($val[0]);
             $datetravel = $dateObj->format('Y') - 543 . '-' . $dateObj->format('m-d');
             $cid = $val[1];
             $money = $val[5];
             $Data = array('cid' => $cid, 'money' => $money, 'datetravel' => $datetravel);
             if ($Data['cid'] != null || $Data['cid'] != '') {
                 $result = DB::insert('insert into s_travel_temp ( cid, money, datetravel ) values ( ? , ?, ? )', array($Data['cid'], $Data['money'], $Data['datetravel']));
             }
             //if check cid null
         }
     }
     //end foreach
     $dataall = DB::table('s_travel_temp')->select('cid', DB::raw('month(datetravel) as travel_m'), DB::raw('year(datetravel) as travel_y'), DB::raw('sum(money) as total_money'))->groupby('cid')->groupby(DB::raw('month(datetravel)'))->orderby(DB::raw('year(datetravel)'), 'asc')->orderby(DB::raw('month(datetravel)'), 'asc')->get();
     foreach ($dataall as $key => $value) {
         $Data_update = array('u_travel' => $value->total_money);
         $p = DB::table('n_position_salary')->where('cid', '=', $value->cid)->select('level')->orderBy('salaryID', 'desc')->limit(1)->first();
         if ($p->level == 'ข้าราชการ' || $p->level == 'ลูกจ้างประจำ') {
             $result = DB::table('s_salary_ocsc_detail')->where('cid', '=', $value->cid)->where(DB::raw('year(order_date)'), $value->travel_y)->where(DB::raw('month(order_date)'), $value->travel_m)->update($Data_update);
         } else {
             $result = DB::table('s_salary_detail')->where('cid', '=', $value->cid)->where(DB::raw('year(order_date)'), $value->travel_y)->where(DB::raw('month(order_date)'), $value->travel_m)->update($Data_update);
         }
     }
     // end foreach update u_travel 2 table
     //clear table s_travel_temp
     //DB::table('s_travel_temp')->delete();
     DB::table('s_travel_temp')->truncate();
     return View::make('admin.upexceltravel', array('status' => 'อัพโหลดข้อมูลเรียบร้อย'));
 }

//.........这里部分代码省略.........
             $recordNumber = trim($currentSheet->getCell($aa)->getValue());
             //订单号
             if (empty($recordNumber)) {
                 break;
             }
             /***************判断订单是否已存在***************/
             if (M('OrderAdd')->checkIsExists(array('recordNumber' => $recordNumber, 'accountId' => $account))) {
                 self::$errMsg[] = get_promptmsg(10043, $recordNumber);
                 //"该recordNumber已经存在<br/>";
                 continue;
             }
             /**************/
             $is_order = intval($currentSheet->getCell($bb)->getValue());
             //1代表为订单，0代表订单明细
             if ($is_order != 0) {
                 //为订单
                 //这个验证可以不用
                 //if($cndlAccounts[$account]=="dresslink.com"){
                 //					   $str = substr($recordNumber,0,2);
                 //					   if($str!=="DL"){
                 //						  $message .= "<font color=red> {$recordNumber}不在账号{$cndlAccounts[$account]}中！</font><br>";
                 //						  continue;
                 //					   }
                 //					}elseif($cndlAccounts[$account]=="cndirect.com"){
                 //					   $str = substr($recordNumber,0,2);
                 //					   if($str!=="CN"){
                 //						  $message .= "<font color=red> {$recordNumber}不在账号{$cndlAccounts[$account]}中！</font><br>";
                 //						  continue;
                 //					   }
                 //					}
                 $platformUsername = mysql_real_escape_string(trim($currentSheet->getCell($cc)->getValue()));
                 $email = mysql_real_escape_string(trim($currentSheet->getCell($dd)->getValue()));
                 $transId = mysql_real_escape_string(trim($currentSheet->getCell($ee)->getValue()));
                 $ordersTime = (array) PHPExcel_Shared_Date::ExcelToPHPObject(trim($currentSheet->getCell($ll)->getValue()));
                 $paymentTime = (array) PHPExcel_Shared_Date::ExcelToPHPObject(trim($currentSheet->getCell($mm)->getValue()));
                 $shippingFee = round_num(trim($currentSheet->getCell($oo)->getValue()), 2);
                 $calcWeight = round_num(trim($currentSheet->getCell($ahh)->getValue()), 3);
                 $actualTotal = round_num(trim($currentSheet->getCell($pp)->getValue()), 2);
                 $onlineTotal = round_num(trim($currentSheet->getCell($aff)->getValue()), 2);
                 $currency = mysql_real_escape_string(trim($currentSheet->getCell($qq)->getValue()));
                 //$orders['ebay_orderqk'] = round_num(trim($currentSheet->getCell($rr)->getValue()), 2);
                 $note = mysql_real_escape_string(trim($currentSheet->getCell($ss)->getValue()));
                 $username = mysql_real_escape_string(trim($currentSheet->getCell($tt)->getValue()));
                 $countryName = mysql_real_escape_string(trim($currentSheet->getCell($uu)->getValue()));
                 $state = mysql_real_escape_string(trim($currentSheet->getCell($vv)->getValue()));
                 $city = mysql_real_escape_string(trim($currentSheet->getCell($ww)->getValue()));
                 $street = mysql_real_escape_string(trim($currentSheet->getCell($xx)->getValue()));
                 $address2 = mysql_real_escape_string(trim($currentSheet->getCell($yy)->getValue()));
                 $zipCode = mysql_real_escape_string(trim($currentSheet->getCell($zz)->getValue()));
                 $phone = mysql_real_escape_string(trim($currentSheet->getCell($abb)->getValue()));
                 $landline = mysql_real_escape_string(trim($currentSheet->getCell($aaa)->getValue()));
                 //					if($account == 400){    //dresslink.com
                 //						$feedback 				= mysql_real_escape_string(trim($currentSheet->getCell($ann)->getValue()));
                 //					}elseif($account == 410){   //cndirect.com
                 //						$feedback 				= mysql_real_escape_string(trim($currentSheet->getCell($akk)->getValue()));
                 //					}
                 $carrierNameCn = strtolower(mysql_real_escape_string(trim($currentSheet->getCell($kk)->getValue())));
                 $payment_method = mysql_real_escape_string(trim($currentSheet->getCell($ff)->getValue()));
                 $payment_module = mysql_real_escape_string(trim($currentSheet->getCell($gg)->getValue()));
                 $bank_account = mysql_real_escape_string(trim($currentSheet->getCell($hh)->getValue()));
                 $bank_country = mysql_real_escape_string(trim($currentSheet->getCell($ii)->getValue()));
                 $shipping_method = mysql_real_escape_string(trim($currentSheet->getCell($jj)->getValue()));
                 $shipping_module = mysql_real_escape_string(trim($currentSheet->getCell($kk)->getValue()));
                 //这个dresslinks_info表在新系统已经废除了
                 //$dresslinks['payment_method'] = $payment_method;
                 //					$dresslinks['payment_module'] = $payment_module;

 /**
  *
  * @param mixed $value
  * @param string $originalFieldName
  * @return \DateTime|null
  * @throws PhpExcelException
  */
 private function getValueAsDate($value, $originalFieldName)
 {
     if (strlen($value) > 0 && !is_numeric($value)) {
         throw new PhpExcelException("Invalid date value '{$value}'. " . 'Make sure the cell is formatted as date. ' . "Field '{$originalFieldName}', " . "Row '" . $this->currentRow . "'");
     }
     return strlen($value) > 0 ? PhpOffice_PHPExcel_Shared_Date::ExcelToPHPObject($value) : null;
 }

//.........这里部分代码省略.........
             $is_order = intval($currentSheet->getCell($bb)->getValue());
             //1代表为订单，0代表订单明细
             if (empty($recordnumber)) {
                 $message .= "<font color=red> 第{$c}行recordnumber为空！</font><br>";
                 break;
             }
             /***************判断订单是否已存在***************/
             $where = "where recordnumber='{$recordnumber}'";
             $orderinfo = cndlModel::selectOrder($where);
             if ($orderinfo) {
                 if ($is_order != 0) {
                     $message .= "<font color='blue'>订单 {$recordnumber}已存在！</font><br>";
                 }
                 continue;
             }
             /**************/
             if ($is_order != 0) {
                 if ($cndlAccounts[$account] == "dresslink.com") {
                     $str = substr($recordnumber, 0, 2);
                     if ($str !== "DL") {
                         $message .= "<font color=red> {$recordnumber}不在账号{$cndlAccounts[$account]}中！</font><br>";
                         continue;
                     }
                 } elseif ($cndlAccounts[$account] == "cndirect.com") {
                     $str = substr($recordnumber, 0, 2);
                     if ($str !== "CN") {
                         $message .= "<font color=red> {$recordnumber}不在账号{$cndlAccounts[$account]}中！</font><br>";
                         continue;
                     }
                 }
                 $platformUsername = mysql_real_escape_string(trim($currentSheet->getCell($cc)->getValue()));
                 $email = mysql_real_escape_string(trim($currentSheet->getCell($dd)->getValue()));
                 $transId = mysql_real_escape_string(trim($currentSheet->getCell($ee)->getValue()));
                 $ordersTime = (array) PHPExcel_Shared_Date::ExcelToPHPObject(trim($currentSheet->getCell($ll)->getValue()));
                 $paymentTime = (array) PHPExcel_Shared_Date::ExcelToPHPObject(trim($currentSheet->getCell($mm)->getValue()));
                 $shippingFee = round_num(trim($currentSheet->getCell($oo)->getValue()), 2);
                 $calcWeight = round_num(trim($currentSheet->getCell($ahh)->getValue()), 3);
                 $actualTotal = round_num(trim($currentSheet->getCell($pp)->getValue()), 2);
                 $onlineTotal = round_num(trim($currentSheet->getCell($aff)->getValue()), 2);
                 $currency = mysql_real_escape_string(trim($currentSheet->getCell($qq)->getValue()));
                 //$orders['ebay_orderqk'] = round_num(trim($currentSheet->getCell($rr)->getValue()), 2);
                 $note = mysql_real_escape_string(trim($currentSheet->getCell($ss)->getValue()));
                 $username = mysql_real_escape_string(trim($currentSheet->getCell($tt)->getValue()));
                 $countryName = mysql_real_escape_string(trim($currentSheet->getCell($uu)->getValue()));
                 $state = mysql_real_escape_string(trim($currentSheet->getCell($vv)->getValue()));
                 $city = mysql_real_escape_string(trim($currentSheet->getCell($ww)->getValue()));
                 $street = mysql_real_escape_string(trim($currentSheet->getCell($xx)->getValue()));
                 $address2 = mysql_real_escape_string(trim($currentSheet->getCell($yy)->getValue()));
                 $zipCode = mysql_real_escape_string(trim($currentSheet->getCell($zz)->getValue()));
                 $phone = mysql_real_escape_string(trim($currentSheet->getCell($abb)->getValue()));
                 $landline = mysql_real_escape_string(trim($currentSheet->getCell($aaa)->getValue()));
                 if ($account == "dresslink.com") {
                     $feedback = mysql_real_escape_string(trim($currentSheet->getCell($ann)->getValue()));
                 } elseif ($account == "cndirect.com") {
                     $feedback = mysql_real_escape_string(trim($currentSheet->getCell($akk)->getValue()));
                 }
                 $carrierNameCn = strtolower(mysql_real_escape_string(trim($currentSheet->getCell($kk)->getValue())));
                 $carrierNameCn = cndlModel::carrier($carrierNameCn);
                 $payment_method = mysql_real_escape_string(trim($currentSheet->getCell($ff)->getValue()));
                 $payment_module = mysql_real_escape_string(trim($currentSheet->getCell($gg)->getValue()));
                 $bank_account = mysql_real_escape_string(trim($currentSheet->getCell($hh)->getValue()));
                 $bank_country = mysql_real_escape_string(trim($currentSheet->getCell($ii)->getValue()));
                 $shipping_method = mysql_real_escape_string(trim($currentSheet->getCell($jj)->getValue()));
                 $shipping_module = mysql_real_escape_string(trim($currentSheet->getCell($kk)->getValue()));
                 $dresslinks['payment_method'] = $payment_method;
                 $dresslinks['payment_module'] = $payment_module;

	$value = $sheet->getCell("E$i")->getValue();	
	if ($value!="m" && $value!="f") error ("Invalid gender ('$value') at cell E$i");
	//echo "<td>$value</td>";
	$gender = $value;

	$value = $sheet->getCell("F$i")->getValue();
	if (!$value) error ("Birth date can't be blank at cell F$i");
	if (!is_numeric($value)) 
	{
		$birthday = $value;
		if (!checkdate((int)substr($birthday,5,2),(int)substr($birthday,8,2),(int)substr($birthday,0,4)))
			error ("Invalid birth date format ('$value') at cell F$i");
	}
	else
	{
		$date = PHPExcel_Shared_Date::ExcelToPHPObject($value);
		//echo "<td>".$date->format("Y-m-d")."</td>";
		$birthday = $date->format("Y-m-d");
	}

	$r = addCom($wcaid,$name,$birthday,$countryid,$gender,true);
	if (!is_numeric($r)) error ("Error inserting in database: $r");

	//echo "</tr>";
	$i++;
}
//echo "</table>";
echo " Done! <b>".($i-4)." competitors</b> imported.<p>";

$lastabbr = "";
$round = 0;

 /**
  * @param Schema $schema
  */
 public function up(Schema $schema)
 {
     /**
      * @var EntityManager $em
      */
     $em = $this->container->get('doctrine')->getManager();
     $decType = $em->getRepository('CoreBundle:DocumentType')->findOneBy(['code' => 'DeclOfConf']);
     if (!$decType) {
         $decType = new DocumentType();
         $decType->setName('Declaration of Conformity')->setCode('DeclOfConf');
         $em->persist($decType);
     }
     $certType = $em->getRepository('CoreBundle:DocumentType')->findOneBy(['code' => 'CertOfConf']);
     if (!$certType) {
         $certType = new DocumentType();
         $certType->setName('Certificate of Conformity')->setCode('CertOfConf');
         $em->persist($certType);
     }
     $customer = new Customer();
     $customer->setName('LPP S.A.')->setAbbr('lpp')->setIsActive(false)->setAddress('');
     $em->persist($customer);
     $excel = \PHPExcel_IOFactory::load(__DIR__ . '/files/documents.xlsx');
     $sheets = $excel->getAllSheets();
     foreach ($sheets as $sheet) {
         $title = $sheet->getTitle();
         $documentType = preg_match('/ДС/ui', $title) ? $decType : $certType;
         preg_match('/\\((?P<tag>.+?)\\)/ui', $title, $matches);
         $tag = null;
         //add tag
         if (isset($matches['tag']) && $matches['tag']) {
             $tag = $em->getRepository('CoreBundle:Tag')->findOneBy(['name' => $matches['tag']]);
             if (!$tag) {
                 $tag = new Tag();
                 $tag->setName($matches['tag']);
                 $em->persist($tag);
                 $em->flush();
             }
         }
         $i = 0;
         $field = null;
         $mapping = [];
         while (($field = $sheet->getCellByColumnAndRow($i, 1)->getValue()) == true) {
             $mapping[$field] = $i;
             $i++;
         }
         $lastColumn = $i - 1;
         $readSheet = true;
         $r = 3;
         while ($readSheet) {
             $document = new Document();
             $document->setCustomer($customer)->setType($documentType);
             if (isset($tag)) {
                 $document->addTag($tag);
             }
             $isEmpty = false;
             foreach ($mapping as $field => $col) {
                 $value = trim($sheet->getCellByColumnAndRow($col, $r)->getValue());
                 if (!$value && $field == 'name') {
                     $isEmpty = true;
                     break;
                 }
                 if (\PHPExcel_Shared_Date::isDateTime($sheet->getCellByColumnAndRow($col, $r))) {
                     $value = \PHPExcel_Shared_Date::ExcelToPHPObject($value);
                 }
                 $method = 'set' . ucfirst($field);
                 if ($value) {
                     $document->{$method}($value);
                 }
             }
             if ($isEmpty) {
                 $readSheet = false;
             } else {
                 $em->persist($document);
             }
             $r++;
         }
     }
     $em->flush();
 }

 /**
  * get the data
  * 
  * @access public
  */
 public function read($valuetypes = array(), $skip = array(), $emptyvalues = FALSE)
 {
     /**
      * @var array $array_data save parsed data from spreadsheet
      */
     $array_data = array();
     foreach ($this->_worksheet->getRowIterator() as $i => $row) {
         $cellIterator = $row->getCellIterator();
         //skip rows in array
         if (!empty($skip) and in_array($i, $skip)) {
             continue;
         }
         //if ($skip[$i] == $row->getRowIndex()) continue;
         $rowIndex = $row->getRowIndex();
         $values = array();
         /**
          * @var PHPExcel_Cell $cell
          */
         foreach ($cellIterator as $cell) {
             if (!empty($valuetypes) and array_key_exists($cell->getColumn(), $valuetypes)) {
                 $format = explode(':', $valuetypes[$cell->getColumn()]);
                 switch ($format[0]) {
                     case 'date':
                         $date = PHPExcel_Shared_Date::ExcelToPHPObject($cell->getValue());
                         $array_data[$rowIndex][$cell->getColumn()] = $date->format($format[1]);
                         break;
                 }
             } else {
                 // check if is_null or empty
                 $value = $cell->getValue();
                 $array_data[$rowIndex][$cell->getColumn()] = (strtolower($value) == 'null' or empty($value)) ? null : $cell->getCalculatedValue();
             }
             // For check empty values
             $values[] = $cell->getValue();
         }
         // Remove rows with all empty cells
         if ($emptyvalues) {
             $chechvalues = implode('', $values);
             if (empty($chechvalues)) {
                 // Delete last array with empty values
                 array_pop($array_data);
             }
         }
     }
     return (array) $array_data;
 }