PHP Spreadsheet_Excel_Reader::dumptoarray方法代码示例

 public function insertDataIntoRateChart()
 {
     if (empty($this->request->data['file_path']) || empty($this->request->data['broker']) || empty($this->request->data['gas_product'])) {
         return new CakeResponse(array('body' => json_encode(0)));
     }
     $data = new Spreadsheet_Excel_Reader("plupload/" . $this->request->data['file_path'], true);
     $temp = $data->dumptoarray();
     $rowCount = count($temp);
     $head = $temp[1];
     $finalData = array();
     for ($i = 2; $i <= $rowCount; $i++) {
         $row = $temp[$i];
         $data = array();
         for ($j = 1; $j <= count($head); $j++) {
             $data[$head[$j]] = $row[$j];
         }
         $finalData[] = $data;
     }
     $results = array();
     foreach ($finalData as $r) {
         $r['broker_info_id'] = $this->request->data['broker'];
         $r['price_code'] = $r['PRICE CODE'];
         $consumption_range = $r['Consumption'];
         $c_array = explode("-", $consumption_range);
         $start_range = $this->tofloat($c_array[0]);
         $r['consumption_range_start'] = $start_range;
         if (isset($c_array[1])) {
             $end_range = $this->tofloat($c_array[1]);
             $r['consumption_range_end'] = $end_range;
         } else {
             $r['consumption_range_end'] = 10000000;
         }
         $r['region_code'] = $r['PES Region'];
         $r['standing_charge_per_year'] = $r['STANDING CHARGE p/year'];
         $r['standing_charge_per_day'] = $r['STANDING CHARGE p/day'];
         $r['unit_rate_per_kwh'] = $r['Unit Rate p/kwh'];
         $r['quote_gas_product_id'] = $this->request->data['gas_product'];
         $results[] = $r;
     }
     $flag = 0;
     $rateCharts = $this->QuoteGasRateChart->find('all', array('conditions' => array('quote_gas_product_id' => $this->request->data['gas_product'], 'broker_info_id' => $this->request->data['broker'])));
     foreach ($rateCharts as $chart) {
         $this->QuoteGasRateChart->id = $chart['QuoteGasRateChart']['id'];
         $this->QuoteGasRateChart->delete();
     }
     if ($this->QuoteGasRateChart->saveAll($results)) {
         $flag = 1;
         $this->Session->setFlash('Uploaded Successfully', 'flash_success');
     } else {
         $this->Session->setFlash('Error Occurred', 'flash_error');
     }
     return new CakeResponse(array('body' => json_encode($flag)));
 }

 public function recepcion()
 {
     if ($this->request->is('post')) {
         $data = new Spreadsheet_Excel_Reader($this->data['Data']['archivo']['tmp_name'], true);
         $temp = $data->dumptoarray(0);
         $datas = array();
         $tlfs = array();
         for ($i = 2; $i < count($temp); $i++) {
             $CedulaOrif = $temp[$i][2] . $temp[$i][3];
             $Nombre = $temp[$i][4] . " " . $temp[$i][5] . " " . $temp[$i][6] . " " . $temp[$i][7];
             $Sexo = $temp[$i][8];
             $FechaRaw = strtotime($temp[$i][9]);
             $FechaNac = date('Y-m-d', $FechaRaw);
             $GrupoSocial = $temp[$i][10];
             $Estado = $temp[$i][11];
             $Direccion = $temp[$i][12];
             $Email = $temp[$i][13];
             $Fecha = date('d/m/Y');
             //Fecha
             $j = 14;
             while ($temp[$i][$j] != ' ' && $j < 42) {
                 $tlf = array("CedulaOrif" => $CedulaOrif, "Telefono" => $temp[$i][$j] . $temp[$i][$j + 1]);
                 if (!empty($CedulaOrif)) {
                     array_push($tlfs, $tlf);
                 }
                 $j += 2;
             }
             $data = array("CedulaOrif" => $CedulaOrif, "Nombre" => $Nombre, "Sexo" => $Sexo, "FechaNac" => $FechaNac, "GrupoSocial" => $GrupoSocial, "Estado" => $Estado, "Direccion" => $Direccion, "Email" => $Email, "Fecha" => $Fecha);
             if (!empty($CedulaOrif)) {
                 array_push($datas, $data);
             }
         }
         unlink($this->data['Data']['archivo']['tmp_name']);
         $this->Data->create();
         $this->Data->saveMany($datas);
         $this->DataTel->create();
         $this->DataTel->saveMany($tlfs);
         // debug($tlfs);
         debug("Fin de la guardada");
     }
 }

 public function excelUploadStates()
 {
     //$this->layout = "defaultSearch";
     if ($this->request->is('post')) {
         /* echo "<pre>";
         			 print_r($this->request->data);
         			exit; */
         $excelData = $this->request->data['State'];
         if (!$excelData['excelFile']['error']) {
             $data = new Spreadsheet_Excel_Reader($excelData['excelFile']['tmp_name'], true);
             $temp = $data->dumptoarray();
             //$this->log($temp, 'debug');
             /* echo "<pre>";
             			print_r($temp);
             			exit; */
             foreach ($temp as $row) {
                 if ($row['1'] == 'id') {
                     continue;
                 }
                 /* echo "<pre>";
                 			 print_r($row);
                 			exit; */
                 $row['id'] = $row['1'];
                 $row['state'] = $row['2'];
                 $rowId = $this->State->field('id', array('State.state' => $row['state']));
                 if ($rowId) {
                     $this->State->id = $rowId;
                     $this->State->save($row);
                 } else {
                     $this->State->create();
                     $this->State->save($row);
                 }
             }
             $this->Session->setFlash("Excel file uploaded Successfully");
             $this->redirect($this->referer());
         }
     }
 }

}
if ($_POST) {
    $msg = "";
    if ($_FILES["file"]["name"] == "") {
        $msg = "Please enter file Excel";
    } else {
        $infofile = pathinfo($_FILES["file"]["name"]);
        if (strtolower($infofile["extension"]) != "xls" && strtolower($infofile["extension"]) != "xlsx") {
            $msg = 'Not allow extentions file, you can only upload file xlsx, xls';
        }
    }
    if ($msg == "") {
        require_once _ROOT . 'libraries/excel/excel_reader2.php';
        //$data = new Spreadsheet_Excel_Reader(_UPLOAD."excel/TPMG Inventory.xls");
        $data = new Spreadsheet_Excel_Reader($_FILES["file"]["tmp_name"]);
        $arrData = $data->dumptoarray(0);
        if (!empty($arrData) && is_array($arrData)) {
            unset($arrData[1], $arrData[2], $arrData[3]);
            if (count($arrData) > 0) {
                foreach ($arrData as $val) {
                    switch (trim($val[7])) {
                        case "Giải nhất":
                            $val[7] = 1;
                            break;
                        case "Giải II":
                            $val[7] = 2;
                            break;
                        case "Giải III":
                            $val[7] = 3;
                            break;
                        default:

 function import_partner()
 {
     if (!empty($this->data)) {
         if ($this->{$this->modelClass}->ExcelValidate()) {
             $data = $this->data;
             $user_id = $this->Auth->user('id');
             $excel_data = new Spreadsheet_Excel_Reader($data[$this->modelClass]['user_image']['tmp_name'], true);
             $this->data = array();
             $result = array();
             $partners = $excel_data->dumptoarray();
             $partner_data_array = array('name', 'email', 'password', 'country', 'zipcode', 'county', 'district', 'city', 'telephone', 'mobile', 'address', 'observations', 'site_url', 'nif_nipc', 'facebook_link', 'google_link', 'twitter', 'linkedIn', 'legal_form', 'social_capital', 'activities_description', 'social_designation', 'creation_date');
             array_shift($partners);
             $this->Partner->data[$this->modelClass] = array();
             foreach ($partners as $user_detials) {
                 $check_data = array_filter($user_detials);
                 if (!empty($check_data)) {
                     $this->loadModel('County');
                     $this->loadModel('District');
                     $partner_details[$this->modelClass] = array_combine($partner_data_array, $user_detials);
                     $this->{$this->modelClass}->set($partner_details);
                     $userName = $partner_details['Partner']['name'];
                     $this->{$this->modelClass}->set($partner_details);
                     if ($this->{$this->modelClass}->PotentialValidate()) {
                         // pr($partner_details);
                         $partner_details['Partner']['address'] = html_entity_decode(utf8_decode($partner_details['Partner']['address']));
                         // pr($partner_details);die;
                         $finddata = $this->{$this->modelClass}->find('first', array('conditions' => array('email' => $partner_details['Partner']['email'])));
                         $county_id = $this->County->find('first', array('conditions' => array('name' => trim($partner_details['Partner']['county']))));
                         $district_id = $this->District->find('first', array('conditions' => array('name' => trim($partner_details['Partner']['district']))));
                         if (!empty($county_id)) {
                             $partner_details['Partner']['county'] = $county_id['County']['id'];
                         } else {
                             $partner_details['Partner']['county'] = 1;
                         }
                         if (!empty($district_id)) {
                             $partner_details['Partner']['district'] = $district_id['District']['id'];
                         } else {
                             $partner_details['Partner']['district'] = 1;
                         }
                         // pr($partner_details); die;
                         if (empty($finddata)) {
                             $data = $partner_details;
                             $data[$this->modelClass]['user_role_id'] = Configure::read('user_roles.partner');
                             $data[$this->modelClass]['is_approved'] = 1;
                             $data[$this->modelClass]['is_potential'] = 1;
                             $data[$this->modelClass]['verified_potential'] = 1;
                             $data[$this->modelClass]['promoter_id'] = $user_id;
                             $data[$this->modelClass]['username'] = $partner_details[$this->modelClass]['name'];
                             $data[$this->modelClass]['password'] = $this->Auth->password($partner_details[$this->modelClass]['password']);
                             $partner_details = $data;
                             $this->{$this->modelClass}->create();
                             if ($this->{$this->modelClass}->save($partner_details, array('validate' => false))) {
                                 //pr($this->data);
                                 //pr($partner_details); die;
                                 $userId = $this->{$this->modelClass}->id;
                                 $partnerdata['Comercial'] = $partner_details['Partner'];
                                 //pr($partnerdata); die;
                                 $this->{$this->modelClass}->UserDetail->savePotentialPartnerAdmin($userId, $partnerdata);
                                 $this->Session->setFlash(__('Potential Partner record has been added.'), 'success');
                                 $this->render('import_partners_from_excel');
                             }
                         } else {
                             $validationserrors = array('0' => 'This Email already exist');
                             if ($userName != '') {
                                 $result['notadded'][$userName] = $validationserrors;
                             }
                         }
                     } else {
                         $validationserrors = array_values($this->{$this->modelClass}->validationErrors);
                         $validationserrora = $validationserrors;
                         $result['notadded'][$userName] = $validationserrora;
                     }
                 }
             }
             // pr($result);	 die;
             $this->set('result', $result);
             $this->set('pageHeading', "Partner Import Messages");
             $this->render('import_partners_from_excel');
         }
     }
 }

         $extra = 1;
         if (($handle = fopen($filename, "r")) !== FALSE) {
             while (($line = fgetcsv($handle)) !== FALSE) {
                 $arr[] = $line;
             }
             fclose($handle);
         }
         break;
     case "xls":
         $start = 1;
         $extra = 0;
         require_once 'excel_reader2.php';
         /* conserve memory for large worksheets by not storing the extended */
         /* information about cells like fonts, colors, etc.                 */
         $data = new Spreadsheet_Excel_Reader($filename, false);
         $arr = $data->dumptoarray(0);
         break;
 }
 //$max = sizeof($arr);
 $max = 5;
 $result = mysqli_query($connection, "SHOW COLUMNS FROM customers");
 $fields_to_ignore[] = "id";
 $fields_to_ignore[] = "new";
 $fields_to_ignore[] = "last_updated";
 $fields_to_ignore[] = "locked_by";
 $fields_to_ignore[] = "datetime_locked";
 $fields_to_ignore[] = "cleaned_number";
 $fields_to_ignore[] = "list_id";
 if (mysqli_num_rows($result) > 0) {
     while ($row = mysqli_fetch_assoc($result)) {
         if (!in_array($row['Field'], $fields_to_ignore)) {

 function parse_er($file)
 {
     $er = Common::inc_module_factory('ExcelReader', true);
     $data = new Spreadsheet_Excel_Reader($file);
     return $data->dumptoarray();
 }

 public function recepcion()
 {
     $bancosRaw = $this->Cliente->find('all');
     $bancos = array();
     //Solo para que se vea mas pretty
     $bancos = $this->Cliente->find('list', array('fields' => array('rif', 'nombre')));
     $this->set('bancos', $bancos);
     if (!empty($this->data)) {
         $empresaId = $this->data['Pago']['banco'];
         if ($this->data['Pago']['banco'] == 7) {
             $nombrearchivo = WWW_ROOT . str_replace("/", DS, 'files/') . $this->data['Pagos']['archivo']['name'];
             /* copiamos el archivo*/
             if (move_uploaded_file($this->data['Pagos']['archivo']['tmp_name'], $nombrearchivo)) {
                 $data = new Spreadsheet_Excel_Reader($nombrearchivo, true);
                 $temp = $data->dumptoarray(1);
                 $pagos = array();
                 for ($i = 2; $i < count($temp); $i++) {
                     $fechaRaw = strtotime($temp[$i][11]);
                     $FechaPago = date('Y-m-d', $fechaRaw);
                     $CedulaOrif = $temp[$i][1];
                     $Producto = $temp[$i][8];
                     //Para que se vea del tipo TdC -
                     $MontoPago = str_replace(',', '', $temp[$i][12]);
                     //Para retirar las comas de los numero 1,000.00
                     $status = 'no-confirmado';
                     //Se guardara asi siempre y cuano no se consida el registro en clien_pagos
                     $pagoPrevio = $this->ClienPago->find('first', array('conditions' => array('ClienPago.FECH_PAGO' => $FechaPago, 'ClienPago.COD_PROD LIKE' => $Producto . '%', 'ClienPago.TOTAL_PAGO' => $MontoPago)));
                     if (count($pagoPrevio) > 0) {
                         $status = 'confirmado';
                         $DiasMora = $temp[$i][20];
                         $update = array('ClienPago' => array('unique_id' => $pagoPrevio['ClienPago']['unique_id'], 'diasMora' => $DiasMora, 'EST_PAGO' => 'confirmado'));
                         $this->ClienPago->save($update);
                     }
                     $pago = array('RIF_EMP' => $empresaId, 'Fecha' => date('Y/m/d'), 'CedulaOrif' => $CedulaOrif, 'Nombre' => $temp[$i][2], 'Contrato' => $temp[$i][3], 'Cuenta' => $temp[$i][34], 'Producto' => $temp[$i][9], 'FechaPago' => $FechaPago, 'MontoPago' => $MontoPago, 'DiasMora' => $temp[$i][20], 'status' => $status);
                     array_push($pagos, $pago);
                 }
                 $this->Pago->create();
                 $this->Pago->saveMany($pagos);
             }
         } else {
             //Banco Bicentenario
             $pagos = array();
             $nombrearchivo = WWW_ROOT . str_replace("/", DS, 'files/') . $this->data['Pagos']['archivo']['name'];
             /* copiamos el archivo*/
             if (move_uploaded_file($this->data['Pagos']['archivo']['tmp_name'], $nombrearchivo)) {
                 $hoy = date('Y-m-d');
                 $objPHPExcel = PHPExcel_IOFactory::load($nombrearchivo);
                 $count = 0;
                 foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                     if ($count == 0 || $count == 1) {
                         //:a pestaña que tiene los datos que me interesan
                         foreach ($worksheet->getRowIterator() as $row) {
                             $cellIterator = $row->getCellIterator();
                             $cellIterator->setIterateOnlyExistingCells(FALSE);
                             // Loop all cells, even if it is not set
                             if ($row->getRowIndex() > 1) {
                                 $i = 0;
                                 foreach ($cellIterator as $cell) {
                                     if ($i == 8) {
                                         $fechaRaw = $cell->getCalculatedValue();
                                         $timestamp = PHPExcel_Shared_Date::ExcelToPHP($fechaRaw);
                                         $FechaPago = date("Y-m-d", $timestamp);
                                     } elseif ($i == 1) {
                                         $CedulaOrif = $cell->getCalculatedValue();
                                     } elseif ($i == 5) {
                                         $Producto = $cell->getCalculatedValue();
                                     } elseif ($i == 9) {
                                         $MontoPago = $cell->getCalculatedValue();
                                     } elseif ($i == 3) {
                                         $nombre = $cell->getCalculatedValue();
                                     } elseif ($i == 4) {
                                         $contrato = $cell->getCalculatedValue();
                                         $cuenta = $cell->getCalculatedValue();
                                     } elseif ($i == 14) {
                                         $DiasMora = $cell->getCalculatedValue();
                                     }
                                     $i++;
                                 }
                                 $status = 'no-confirmado';
                                 $pagoPrevio = $this->ClienPago->find('first', array('conditions' => array('ClienPago.FECH_PAGO' => $FechaPago, 'ClienPago.PRODUCTO LIKE' => '%' . $Producto . '%', 'ClienPago.TOTAL_PAGO' => $MontoPago)));
                                 if (count($pagoPrevio) > 0) {
                                     $status = 'confirmado';
                                     if (empty($DiasMora)) {
                                         $DiasMora = '2500';
                                     }
                                     $update = array('ClienPago' => array('unique_id' => $pagoPrevio['ClienPago']['unique_id'], 'diasMora' => $DiasMora, 'EST_PAGO' => 'confirmado'));
                                     $this->ClienPago->save($update);
                                 }
                                 if (!empty($CedulaOrif)) {
                                     $pago = array('RIF_EMP' => $empresaId, 'Fecha' => date('Y-m-d'), 'CedulaOrif' => $CedulaOrif, 'Nombre' => $nombre, 'Contrato' => $contrato, 'Cuenta' => $cuenta, 'Producto' => $Producto, 'FechaPago' => $FechaPago, 'MontoPago' => $MontoPago, 'DiasMora' => 2500, 'status' => $status);
                                     array_push($pagos, $pago);
                                 }
                             }
                         }
                         $this->Pago->create();
                         $this->Pago->saveMany($pagos);
                     }
                     $count++;
                 }
             }
//.........这里部分代码省略.........