PHP Excel::selectSheetsByIndex方法代码示例

 protected function upload()
 {
     $validator = $this->fileValidator(Input::all());
     if ($validator->passes()) {
         if ($this->_user->download_count) {
             \Excel::selectSheetsByIndex(0)->load(Input::get('file'), function ($reader) {
                 $finalHtml = '';
                 $currentTime = date('d-m-Y_His');
                 mkdir($currentTime);
                 foreach ($reader->toArray() as $row) {
                     $html = "\n\t\t\t<style>\n\t\t\t\t.page-break {\n\t    \t\t\tpage-break-after: always;\n\t\t\t\t}\n\t\t\t\t.outer-container {\n\t\t\t\t\tmargin: 0% auto;\n\t\t\t\t\tborder: 1px solid black;\n\t\t\t\t\ttext-align: center;\n\t\t\t\t\theight: 99%;\n\t\t\t\t}\n\t\t\t\t.subject-container {\n\t\t\t\t\tfont-weight: bold;\n\t\t\t\t\tmargin-top: 30px;\n\t\t\t\t}\n\t\t\t\t.content-container {\n\t\t\t\t\ttext-align: left;\n\t\t\t\t\tpadding: 10px;\n\t\t\t\t\tmargin-top: 50px;\n\t\t\t\t}\n\t\t\t\tol {\n\t\t\t\t\ttext-align: left;\n\t\t\t\t}\n\t\t\t\tol li{\n\t\t\t\t\tpadding-bottom: 40px;\n\t\t\t\t}\n\t\t\t</style>\n\t\t\t<div class='outer-container'>\n\t\t\t\t\n\t\t\t \t\t<p class='subject-container'>Subject: NOTICE UNDER SECTION 138 OF NEGOTIABLE INSTRUMENT ACT READ WITH SECTION 420 OF INDIAN PENAL CODE</p>\n\n\t\t\t\t\t<p class='content-container'>\n\t\t\t\t\t\tOn behalf of and under instructions of my client <u>{$row['name']}</u> S/o__________ R/o __________ (hereinafter referred to as &quot;my client&quot;). I do hereby serve you with the following legal notice:\n\n\t\t\t\t\t\t<ol>\n\t\t\t\t\t\t\t<li>That my client, an engineering student, while looking for job paid Rs {$row['amount']} to you for assured placement in an MNC, last year.</li>\n\t\t\t\t\t\t\t<li>That thereafter my client issued a number of reminders to you for placement, but still no opportunity was provided to him, i.e. as you were unable to fulfill the promise as to placement of my client. Therefore, it was decided between you and my client that the amount of Rs {$row['amount']} should be refunded and as a result you issued him a cheque no {$row['cheque_number']} dated {$row['cheque_date']}.</li>\n\t\t\t\t\t\t\t<li>That the said cheque was presented by my client to State Bank of India, Noida for credit in his account in the month of December 2011 itself, but it bounced due to insufficient funds. And my client contacted you and was assured of cash in lieu of bounced cheque, therefore, my client did not take legal action earlier. My client thereafter again requested many a time to you for the payment of the said cheque amount by telephone and/or through personal visit of his representative, but in vain.</li>\n\t\t\t\t\t\t\t<li>That in April 2012, my client again tried depositing the cheque with State Bank of India, Mysore but it was again returned as unpaid with remarks &#45; Funds Insufficient, vide Syndicate Bank memo dated 19 April 2012.</li>\n\t\t\t\t\t\t\t<li>That in the facts and circumstances created by you my above said client left with no alternative except to serve you the present notice and calling upon all of you to make the payment of the above mentioned cheque amount totaling Rs {$row['amount']}/- (Rupees Ten Thousand only) including bouncing charges in cash with interest @ 24% per annum within 15 days of the receipt of this notice failing which my client shall be constrained to institute against you a criminal complaint under section 138 of the Negotiable Instrument Act read with section 420 of IPC where under you could be sentenced to undergo imprisonment of the two years and also pay the fine equivalent of the double amount of the above mentioned cheque as well as legal charges of this notice of Rs 2100/-</li>\n\t\t\t\t\t\t\t<li>That a copy of this notice retained in my office for further reference /record and legal action.</li>\n\t\t\t\t\t\t</ol>\n\t\t\t\t\t</p>\n\t\t\t</div>";
                     $finalHtml .= $html . "<div class='page-break'></div>";
                     \PDF::loadHTML($html)->setPaper('a4')->setOrientation('portrait')->setWarnings(false)->save($currentTime . '/' . $row["name"] . '_' . $row['cheque_number'] . '.pdf');
                 }
                 \PDF::loadHTML($finalHtml)->setPaper('a4')->setOrientation('portrait')->setWarnings(false)->save($currentTime . '/' . $currentTime . '.pdf');
                 // Here we choose the folder which will be used.
                 $dirName = public_path() . '/' . $currentTime;
                 // Choose a name for the archive.
                 $zipFileName = $this->_user->email . '_' . $currentTime . '.zip';
                 // Create ".zip" file in public directory of project.
                 $zip = new ZipArchive();
                 if ($zip->open(public_path() . '/' . $zipFileName, ZipArchive::CREATE) === TRUE) {
                     // Copy all the files from the folder and place them in the archive.
                     foreach (glob($dirName . '/*') as $fileName) {
                         $file = basename($fileName);
                         $zip->addFile($fileName, $file);
                     }
                     $zip->close();
                     $headers = array('Content-Type' => 'application/octet-stream');
                 } else {
                     echo 'failed';
                 }
                 $filename = $this->_user->email . '_' . $currentTime . '.zip';
                 $filepath = $_SERVER["DOCUMENT_ROOT"];
                 ob_start();
                 // http headers for zip downloads
                 header("Pragma: public");
                 header("Expires: 0");
                 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
                 header("Cache-Control: public");
                 header("Content-Description: File Transfer");
                 header("Content-type: application/octet-stream");
                 header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
                 header("Content-Transfer-Encoding: binary");
                 header("Content-Length: " . filesize($filepath . "/" . $filename));
                 @readfile($filepath . "/" . $filename);
                 ob_end_flush();
                 \File::deleteDirectory($currentTime);
                 \File::delete($this->_user->email . '_' . $currentTime . '.zip');
                 // reader methods
                 $this->_user->download_count = $this->_user->download_count - 1;
                 $this->_user->save();
             });
         } else {
             return Redirect::to("home")->with('message', 'Your maximum download limit 3, exceeded in beta version.  Please subscribe to use this feature.');
         }
     }
     return Redirect::to("home")->withErrors($validator->messages());
 }

 public function storeList($touch_id, Request $request)
 {
     $date = \Carbon\Carbon::now()->toDateTimeString();
     $file = $request->file('file');
     $touch = Touch::find($touch_id);
     $results = \Excel::selectSheetsByIndex(0)->load($file, function ($reader) use($touch) {
         $reader->ignoreEmpty();
         $reader->each(function ($row) use($touch) {
             $email = $row['email'];
             $email = trim($email);
             if (!$this->checkEmail($email)) {
                 return false;
             }
             if (isset($row['email']) && $row['email']) {
                 $contact = Contact::firstOrCreate(['email' => $email, 'client_id' => $touch->campaign->client->id]);
                 if (isset($row['first_name'])) {
                     $contact->first_name = $row['first_name'];
                 }
                 if (isset($row['last_name'])) {
                     $contact->last_name = $row['last_name'];
                 }
                 if (isset($row['company'])) {
                     $contact->company = $row['company'];
                 }
                 if (isset($row['title'])) {
                     $contact->title = $row['title'];
                 }
                 if (isset($row['address'])) {
                     $contact->address = trim(str_replace("\n", ' ', $row['address']));
                 }
                 if (isset($row['city'])) {
                     $contact->city = $row['city'];
                 }
                 if (isset($row['state'])) {
                     $contact->state = $row['state'];
                 }
                 if (isset($row['zip'])) {
                     $contact->zip = $row['zip'];
                 }
                 $contact->save();
                 array_push($this->contacts, $contact);
             }
         });
     });
     $queuedEmails = 0;
     foreach ($this->contacts as $contact) {
         if (!$contact->unsubscribe && !$contact->bounced) {
             $email = Email::create(['subject' => $touch->subject, 'reply_to' => $touch->campaign->client->reply_to, 'from' => $touch->campaign->client->reply_to, 'send_on' => $date, 'template' => "emails.{$touch->template}", 'draft' => false, 'trackable' => false, 'campaign_id' => $touch->campaign->id, 'contact_id' => $contact->id, 'touch_id' => $touch->id]);
             $email->salted_id = bcrypt($email->id);
             $email->save();
             $queuedEmails++;
         }
     }
     return redirect()->route('admin.touches.show', $touch->id)->with('message', "{$queuedEmails} test emails sent");
 }

 public function upload($file)
 {
     if ($file->isValid()) {
         // Se o arquivo for válido
         $extension = $file->getClientOriginalExtension();
         // Guarda a extensão do arquivo
         if ($extension == 'xls' || $extension == 'xlsx') {
             // Verifica se é um arquivo Excel
             $name = $file->getClientOriginalName();
             // Guarda o nome do arquivo
             $path = storage_path() . '/import';
             // Determina onde o arquivo será salvo
             $file->move($path, $name);
             // Salva o arquivo em disco
             // Adiciona a tarefa de leitura do arquivo e criação dos registros no banco de dados à fila
             Queue::push(function ($job) use($path, $name) {
                 Product::truncate();
                 // Limpa a tabela, evitando assim itens duplicados
                 $reader = Excel::selectSheetsByIndex(0)->load($path . '/' . $name);
                 // Le o arquivo Excel
                 $reader->each(function ($sheet) {
                     // Faz o loop nas linhas que contém conteúdo
                     $product = array();
                     $product['lm'] = $sheet->lm;
                     $product['name'] = $sheet->name;
                     $product['free_shipping'] = $sheet->free_shipping;
                     $product['description'] = $sheet->description;
                     $product['price'] = number_format((double) $sheet->price, 2, ',', '.');
                     $product['category'] = $sheet->category;
                     // Cria um novo produto baseado nas informações obtidas no arquivo Excel
                     Product::create($product);
                 });
                 $job->delete();
                 // Remove job from Queue
             });
             return 0;
             // Sucesso
         } else {
             return 1;
             // Arquivo diferente de Excel
         }
     } else {
         return 2;
         // Falha no envio do arquivo
     }
 }

 public function file_move()
 {
     if (Input::hasFile('image')) {
         $vote_id_temp = Session::get('vote_id_insert', '');
         $this->clean($vote_id_temp);
         $file = Input::file('image');
         //
         $fileName = "test222";
         $destinationPath = storage_path() . '/file_import/';
         $file = $file->move($destinationPath, $fileName);
         Excel::selectSheetsByIndex(0)->load($file, function ($reader) use($vote_id_temp) {
             //以第一個資料表中的資料為主
             //提醒使用ooo的使用者，不要存成 Microsoft Excel 95
             //需存成 Microsoft Excel 97/2000/xp  ***.xls
             $value = $reader->get()->toArray();
             //object -> array
             foreach ($value as $data_array1) {
                 //$vote_id_temp = Session::get('vote_id_insert', '');
                 $data_array1['vote_id'] = $vote_id_temp;
                 //var_dump($data_array1);
                 $candidate = Candidate::create($data_array1);
                 // $candidate = new Candidate;
                 // $candidate->cname=$data_array1[0];
                 // $candidate->job_title=$data_array1[1];
                 // $candidate->sex=$data_array1[2];
                 // $candidate->vote_id=42;
                 // $candidate->total_count=0;
                 // $candidate->save();
             }
         });
         // echo $file;
         //File::delete($file);
     }
     return Redirect::route('votes_not_yet');
 }

 public function postSave()
 {
     $result = new AutomateModel();
     $result->info = Input::get('info');
     $result->save();
     $named = NULL;
     $result->file_result = $named;
     $result->save();
     $res = $result->id;
     $file_id = Input::get('files');
     $plot = Input::get('id_plot');
     $charts_data = array();
     $index = 0;
     foreach ($file_id as $file) {
         $myfile = DB::table('his_file')->where('id', '=', $file)->first();
         $filename = $myfile->filename;
         $path = public_path('uploads') . '/' . $filename;
         Excel::selectSheetsByIndex(1)->load($path, function ($reader) {
             //                 echo json_encode($reader->toArray());
             //                 exit();
             $this->value = $reader->toArray()[1];
             $this->frekuensi = $reader->toArray()[0];
             //                var_dump($reader)->toArray();exit();
             //                }
         });
         //            print_r($this->value);
         //            print_r($this->frekuensi);
         //            exit();
         $selected_plot = $plot[$index];
         $range = DB::table('his_plot_data')->where('id_plot', '=', $selected_plot)->get();
         $plot_name = DB::table('his_plot')->join('his_technology', 'his_technology.id', '=', 'his_plot.id_tech')->where('his_plot.id', '=', $selected_plot)->select('his_plot.name as name', 'his_plot.id as id', 'his_technology.name as technology')->first();
         //            print_r($plot_name);exit();
         $xchart = array();
         $ychart = array();
         $z = 0;
         $new_x_chart = array();
         $new_y_chart = array();
         $new_yy_chart = array();
         $title = "";
         $flagin = 0;
         //            print_r($range); exit();
         foreach ($range as $key => $val) {
             if ($val->start == -999) {
                 $xchart[$z] = ' <span style="text-decoration:underline;">&lt;</span> ' . $val->end;
             } else {
                 if ($val->end == 999) {
                     $xchart[$z] = $val->start . ' &lt; ';
                 } else {
                     $xchart[$z] = $val->start . ' &lt; ' . $plot_name->name . ' <span style="text-decoration:underline;">&lt;</span> ' . $val->end;
                 }
             }
             $ychart[$z] = 0;
             $j = 0;
             foreach ($this->value as $roy) {
                 if ($roy > $val->start && $roy <= $val->end) {
                     $ychart[$z] = $ychart[$z] + $this->frekuensi[$j];
                 } else {
                     if (!is_numeric($this->frekuensi[$j]) && strlen($this->frekuensi[$j]) > 5 && $flagin == 0) {
                         $title = $title . ' ' . $this->frekuensi[$j];
                         $flagin = 1;
                     }
                 }
                 $j++;
             }
             $z++;
         }
         $sum = array_sum($ychart);
         $series1Data = array();
         $ind = 0;
         $yy = 0;
         for ($st = 0; $st < $z; $st++) {
             if ($ychart[$st] > 0) {
                 $yy = $yy + $ychart[$st];
                 $series1Data[$xchart[$st]] = $ychart[$st];
                 $new_x_chart[$ind] = $xchart[$st];
                 $new_y_chart[$ind] = $ychart[$st] / $sum * 100;
                 $new_yy_chart[$ind] = $yy / $sum * 100;
                 $ind++;
             }
         }
         $tech = PlotModel::join('his_technology', 'his_technology.id', '=', 'his_plot.id_tech')->select('his_technology.name')->where('his_plot.id', $selected_plot)->first();
         $charts_data['title'][$index] = $title;
         $charts_data['tech'][$index] = $tech->name;
         $charts_data['plot'][$index] = $selected_plot;
         $charts_data['x'][$index] = $new_x_chart;
         $charts_data['y'][$index] = $new_y_chart;
         $charts_data['yy'][$index] = $new_yy_chart;
         $index++;
     }
     $json = json_encode($charts_data['x'][0]);
     //        print_r($json);
     //        exit();
     //        $this->createPresentation();
     $data['charts_data'] = $charts_data;
     $data['res'] = $res;
     return View::make('automate.result', $data);
     //        return Redirect::to('automate/result')
     //                ->with('charts_data', $charts_data);
 }

 private function processPhoneNumbersUpdate($file)
 {
     if (Session::has('username') && (Session::get('user_type') == "Root" || Session::get('user_type') == "Admin")) {
         if (!in_array($file->getClientOriginalExtension(), array("xls", "xlsx", "csv"))) {
             Input::flash();
             return Redirect::to('settings/employees/updatephonenumbers')->with('message', "Invalid file selected.");
         } else {
             $filename = "upload-" . str_random(9) . "." . $file->getClientOriginalExtension();
             $file->move("uploads", $filename);
             $readFile = "uploads/" . $filename;
             $reader = Excel::selectSheetsByIndex(0)->load($readFile, function ($reader) {
             })->get();
             $excelChecker = Excel::selectSheetsByIndex(0)->load($readFile, function ($reader) {
             })->get()->toArray();
             $excelIsValid = false;
             foreach ($excelChecker as $ex) {
                 if (isset($ex["employeenumber"])) {
                     $excelIsValid = true;
                 }
             }
             if (!$excelIsValid) {
                 Input::flash();
                 File::delete($readFile);
                 return Redirect::to('settings/employees/updatephonenumbers')->with('message', "Excel file has invalid attributes. Please download the form.");
             }
             $hasError = false;
             //Detects if there are any errors.
             $hasCorrectRows = false;
             $rowIndex = 1;
             //Indexes which row the reader is reading.
             $rowsWithErrors = array();
             //This is used to contain in an array the row numbers of the rows with error.
             $error = array();
             //Error details collector.
             foreach ($reader as $r) {
                 $rowIndex += 1;
                 $errorCount = 0;
                 //Counts the number of errors for the currect row.
                 $rowHasError = false;
                 //Check if this row has error. I will use this before the reading of the row ends.
                 //					If $rowHasError is still false by end of the reading, then I will write it in the database.
                 $validator = Validator::make(array("employee number" => trim($r->employeenumber)), array("employee number" => "required|exists:tbl_employees,employee_number"));
                 if ($validator->fails()) {
                     $hasError = true;
                     $rowHasError = true;
                     $rowsWithErrors[$rowIndex] = $rowIndex;
                     if ($validator->messages()->get("employee number")) {
                         foreach ($validator->messages()->get("employee number") as $e) {
                             $errorCount += 1;
                             $error[$rowIndex][$errorCount] = $errorCount . ". " . $e . "<br/>";
                         }
                     }
                 }
                 if (!$rowHasError) {
                     $hasCorrectRows = true;
                     //Save Updates
                     //Find the employee by employee number.
                     $employee = Employee::where("employee_number", "=", trim($r->employeenumber))->first();
                     $employee->cellphone_number = trim($r->phonenumber) != null ? trim($r->phonenumber) : null;
                     $employee->save();
                 }
             }
             File::delete($readFile);
             if ($hasCorrectRows) {
                 //Log the changes made
                 $desc = "(" . Session::get("user_type") . ") <b>" . Session::get("username") . "</b> has updated employees phone numbers. ";
                 $newLog = new UserLog();
                 $newLog->description = $desc;
                 $newLog->user_id = Session::get('user_id');
                 $newLog->save();
             }
             return $this->phoneNumbersUpdateResult($hasError, $hasCorrectRows, $rowsWithErrors, $error);
         }
     } else {
         return Redirect::to("/");
     }
 }

 private function processImport($file)
 {
     if (Session::has('username') && (Session::get('user_type') == "Root" || Session::get('user_type') == "Admin")) {
         if (!in_array($file->getClientOriginalExtension(), array("xls", "xlsx", "csv"))) {
             Input::flash();
             return Redirect::to('assets/software/import')->with('message', "Invalid file selected.");
         } else {
             $filename = "upload-" . str_random(9) . "." . $file->getClientOriginalExtension();
             $file->move("uploads", $filename);
             $readFile = "uploads/" . $filename;
             $reader = Excel::selectSheetsByIndex(0)->load($readFile, function ($reader) {
             })->get();
             /*
              * 				Before validating each rows of the file uploaded, the file itself is checked if it has the valid attributes (columns)
              * 				using the algorithm found below.
              *
              * 				1. File is read.
              * 				2. Boolean variable $excelIsValid to check if the file is valid. Set to false by default.
              *
              */
             $excelChecker = Excel::selectSheetsByIndex(0)->load($readFile, function ($reader) {
             })->get()->toArray();
             $excelIsValid = false;
             /*
              * 				3. Loop through the excel file and check if at least once all the columns have been present.
              * 				4. If it does, $excelIsValid is set to true.
              * 				5. If $excelIsValid is still false by end of the loop, it is automatically assumed that the file is:
              * 					A.) Empty
              * 					B.) Does not have the right attributes.
              * 					C.) Has valid columns, but does not have any valid entry.
              *
              */
             foreach ($excelChecker as $ex) {
                 if (isset($ex["productkey"]) && isset($ex["softwaretype"]) && isset($ex["status"])) {
                     $excelIsValid = true;
                 }
             }
             /*				6. If file is invalid, redirect to import form and return an error. */
             if (!$excelIsValid) {
                 Input::flash();
                 File::delete($readFile);
                 return Redirect::to('assets/software/import')->with('message', "Excel file has invalid attributes. Please download the form.");
             }
             /*
              * 				CHECKING EXCEL FILE FOR ERRORS WHILE READING THE ROWS
              *
              * 				1. $hasError is a Boolean variable that is simply used to tell if any error has been found.
              * 					This variable is, by default, set to false. If any error has been detected, it is set to true,
              * 					regardless of how many errors has been detected.
              *
              * 				2. $rowIndex indexes which row the reader is currently reading. Default value set to 1 because
              * 					the first row of excel files is automatically set as the attribute row. When the reader reads each row,
              * 					$rowIndex is incremented. For example, reader is currently reading row 2, $rowIndex will then be incremented,
              * 					setting its value to 2, thus the row number.
              *
              * 				3. $rowsWithErrors is the array of the rows with errors. To explain further, let's say excel file has 10 readable (non-attrib)
              * 					rows. No errors were found from rows number 2-8, but errors were found in rows 9, 10, and 11. These 9, 10, and 11
              * 					will then be in the $rowsWithError.
              *
              * 				4. $error array is the variable that will be used to collect all errors found from the excel file.
              * 					This is a two-dimensional array.
              *
              *
              */
             $hasError = false;
             //Detects if there are any errors.
             $hasCorrectRows = false;
             $rowIndex = 1;
             //Indexes which row the reader is reading.
             $rowsWithErrors = array();
             //This is used to contain in an array the row numbers of the rows with error.
             $error = array();
             //Error details collector.
             foreach ($reader as $r) {
                 /*
                  * 				5. Here, we immediately increment the value of $rowIndex, since the variable will be used in the core logic of this method.
                  *
                  * 				6. $errorCount variable is a variable used in every loop. Set to 0 when the loop begins, so it always comes back to 0 for every loop.
                  * 					$errorCount is used to track the number of errors for the current row. This variable goes hand in hand with the
                  * 					$rowsWithError array when publishing the rows with errors, and the error details for each row with error.
                  *
                  * 					This is how $rowsWithError and $rowCount will be used:
                  *
                  * 					for each $rowWithErrors:
                  * 						Row $rowWithErrors Index:
                  * 							Errors Found :
                  * 							$rowCount 1. Foo bar
                  * 							$rowCount 2. Jane Doe etc..
                  *
                  *
                  */
                 $rowIndex += 1;
                 $errorCount = 0;
                 //Counts the number of errors for the currect row.
                 $rowHasError = false;
                 //Check if this row has error. I will use this before the reading of the row ends.
                 //					If $rowHasError is still false by end of the reading, then I will write it in the database.
                 $warranty_start = !empty(trim($r->warrantystart)) ? trim($r->warrantystart) : "1994-04-16";
                 $validator = Validator::make(array("software asset tag" => trim($r->assettag), "product key" => trim($r->productkey), "employee number" => trim($r->employeenumber), "laptop serial number" => trim($r->laptopsn), "software type" => trim($r->softwaretype), "warranty start date" => trim($r->warrantystart), "warranty end date" => trim($r->warrantyend), "status" => trim($r->status)), array("software asset tag" => "required|unique:tbl_software_assets,asset_tag|unique:tbl_assets,asset_tag", "product key" => "required", "employee number" => "exists:tbl_employees,employee_number", "laptop serial number" => "exists:tbl_assets,serial_number", "software type" => "required|exists:tbl_software_types,software_type", "warranty start date" => "required_with:warranty end date|date:Y-m-d", "warranty end date" => "date:Y-m-d|after:" . $warranty_start, "status" => "required|in:Available,PWU,Retired,Test Case,Lost"), array("after" => "The :attribute must be after the warranty start date."));
                 if ($validator->fails()) {
//.........这里部分代码省略.........

 private function processImport($file)
 {
     if (Session::has('username') && (Session::get('user_type') == "Root" || Session::get('user_type') == "Admin")) {
         if (!in_array($file->getClientOriginalExtension(), array("xls", "xlsx", "csv"))) {
             Input::flash();
             return Redirect::to('employees/import')->with('message', "Invalid file selected.");
         } else {
             $filename = "upload-" . str_random(9) . "." . $file->getClientOriginalExtension();
             $file->move("uploads", $filename);
             $readFile = "uploads/" . $filename;
             $reader = Excel::selectSheetsByIndex(0)->load($readFile, function ($reader) {
             })->get();
             /*
              * 				Before validating each rows of the file uploaded, the file itself is checked if it has the valid attributes (columns)
              * 				using the algorithm found below.
              * 
              * 				1. File is read.
              * 				2. Boolean variable $excelIsValid to check if the file is valid. Set to false by default.
              * 	
              */
             $excelChecker = Excel::selectSheetsByIndex(0)->load($readFile, function ($reader) {
             })->get()->toArray();
             $excelIsValid = false;
             /*
              * 				3. Loop through the excel file and check if at least once all the columns have been present.
              * 				4. If it does, $excelIsValid is set to true.
              * 				5. If $excelIsValid is still false by end of the loop, it is automatically assumed that the file is:
              * 					A.) Empty
              * 					B.) Does not have the right attributes.
              * 					C.) Has valid columns, but does not have any valid entry.
              * 					
              */
             foreach ($excelChecker as $ex) {
                 if (isset($ex["employeenumber"]) && isset($ex["lastname"]) && isset($ex["firstname"]) && isset($ex["startdate"]) && isset($ex["status"])) {
                     $excelIsValid = true;
                 }
             }
             /*				6. If file is invalid, redirect to import form and return an error. */
             if (!$excelIsValid) {
                 Input::flash();
                 File::delete($readFile);
                 return Redirect::to('employees/import')->with('message', "Excel file has invalid attributes. Please download the form.");
             }
             /*				
              * 				CHECKING EXCEL FILE FOR ERRORS WHILE READING THE ROWS
              * 
              * 				1. $hasError is a Boolean variable that is simply used to tell if any error has been found.
              * 					This variable is, by default, set to false. If any error has been detected, it is set to true,
              * 					regardless of how many errors has been detected.
              * 
              * 				2. $rowIndex indexes which row the reader is currently reading. Default value set to 1 because
              * 					the first row of excel files is automatically set as the attribute row. When the reader reads each row,
              * 					$rowIndex is incremented. For example, reader is currently reading row 2, $rowIndex will then be incremented,
              * 					setting its value to 2, thus the row number.
              * 
              * 				3. $rowsWithErrors is the array of the rows with errors. To explain further, let's say excel file has 10 readable (non-attrib)
              * 					rows. No errors were found from rows number 2-8, but errors were found in rows 9, 10, and 11. These 9, 10, and 11
              * 					will then be in the $rowsWithError.
              * 
              * 				4. $error array is the variable that will be used to collect all errors found from the excel file.
              * 					This is a two-dimensional array.
              * 
              * 
              */
             $hasError = false;
             //Detects if there are any errors.
             $hasCorrectRows = false;
             $rowIndex = 1;
             //Indexes which row the reader is reading.
             $rowsWithErrors = array();
             //This is used to contain in an array the row numbers of the rows with error.
             $error = array();
             //Error details collector.
             foreach ($reader as $r) {
                 /*
                  * 				5. Here, we immediately increment the value of $rowIndex, since the variable will be used in the core logic of this method.
                  * 				
                  * 				6. $errorCount variable is a variable used in every loop. Set to 0 when the loop begins, so it always comes back to 0 for every loop.
                  * 					$errorCount is used to track the number of errors for the current row. This variable goes hand in hand with the
                  * 					$rowsWithError array when publishing the rows with errors, and the error details for each row with error.
                  * 
                  * 					This is how $rowsWithError and $rowCount will be used:
                  * 
                  * 					for each $rowWithErrors:
                  * 						Row $rowWithErrors Index:
                  * 							Errors Found :
                  * 							$rowCount 1. Foo bar
                  * 							$rowCount 2. Jane Doe etc..
                  * 
                  * 
                  */
                 $rowIndex += 1;
                 $errorCount = 0;
                 //Counts the number of errors for the currect row.
                 $rowHasError = false;
                 //Check if this row has error. I will use this before the reading of the row ends.
                 //					If $rowHasError is still false by end of the reading, then I will write it in the database.
                 /*
                  * 
                  */
//.........这里部分代码省略.........

 public function excel2()
 {
     $dir = "files/excel/";
     $fecha = base64_encode("excel" . date('d/m/Y-H:m:i'));
     $file_name = $fecha;
     $file = Input::file('excel');
     $date = Input::get('date');
     $destinationPath = 'files/excel/';
     // If the uploads fail due to file system, you can try doing public_path().'/uploads'
     $filename = $file_name;
     //str_random(12);
     $upload_success = Input::file('excel')->move($destinationPath, $filename);
     $results = Excel::selectSheetsByIndex(0)->load($dir . $file_name)->get();
     $factura = array();
     $groups = array();
     //shattering file gotten
     $nit = "";
     $error = "";
     $e = 1;
     foreach ($results as $key => $res) {
         $dato = [];
         //$nit = "";
         foreach ($res as $r) {
             if ($r != "") {
                 array_push($dato, $r);
             }
         }
         //$nit = $dato[0];
         $bbr['name'] = $dato[0];
         if ($bbr['name'] == "") {
             $error .= "Registro " . $e . ": el cliente debe tener nombre<br>";
         }
         $bbr['nit'] = $dato[1];
         if (ctype_digit($bbr['nit'])) {
             $error .= "Registro " . $e . ": nit ser numérico<br>";
         }
         $bbr['code'] = $dato[2];
         $actual_product = Product::where('account_id', Auth::user()->account_id)->where('product_key', $bbr['code'])->first();
         if (!$actual_product) {
             $error .= "Registro " . $e . ": producto no encontrado<br>";
         }
         $bbr['razon'] = $dato[3];
         if ($bbr['razon'] == "") {
             $error .= "Registro " . $e . ": el cliente debe tener razón social<br>";
         }
         $bbr['total'] = $dato[4];
         if (ctype_digit($bbr['total'])) {
             $error .= "Registro " . $e . ": precio incorrecto<br>";
         }
         $bbr['qty'] = $dato[5];
         if (ctype_digit($bbr['qty'])) {
             $error .= "Registro " . $e . ": cantidad incorrecta<br>";
         }
         array_push($factura, $bbr);
         $e++;
     }
     if ($error != "") {
         Session::flash('error', $error);
         return Redirect::to('importar2');
     }
     /*        print_r($bbr);
             return 0;
     
             $returnable = $this->validateShatterExcel($factura);
             if($returnable!=""){
                 Session::flash('error',$returnable);
                 return View::make('factura.import');
             }
             */
     $cont = 0;
     foreach ($factura as $fac) {
         $this->saveLote2($fac, $date);
         $cont++;
     }
     //return 0;
     Session::flash('message', 'Se importaron ' . $cont . ' facturas exitósamente');
     $invoices = Invoice::where('account_id', Auth::user()->account_id)->where('branch_id', Session::get('branch_id'))->orderBy('public_id', 'DESC')->get();
     //return View::make('factura.index', array('invoices' => $invoices));
     //return View::make('factura.import2',$data);
     Session::flash('message', 'Se importaron exitósamente ' . $cont . " facturas");
     return Redirect::to('factura');
     return $cont;
 }

 public function storeContacts($campaign_id, Request $request)
 {
     set_time_limit(0);
     $date = \Carbon\Carbon::parse($request->input('dateTime'))->toDateTimeString();
     $file = $request->file('file');
     $campaign = Campaign::find($campaign_id);
     $contacts = [];
     $results = \Excel::selectSheetsByIndex(0)->load($file, function ($reader) use($campaign) {
         $reader->ignoreEmpty();
         $reader->each(function ($row) use($campaign) {
             if (isset($row['email']) && $row['email']) {
                 $email = $row['email'];
                 $email = trim($email);
                 if (!$this->checkEmail($email)) {
                     return false;
                 }
                 $contact = Contact::firstOrCreate(['email' => $email, 'client_id' => $campaign->client->id]);
                 if (isset($row['first_name'])) {
                     $contact->first_name = $row['first_name'];
                 }
                 if (isset($row['last_name'])) {
                     $contact->last_name = $row['last_name'];
                 }
                 if (isset($row['company'])) {
                     $contact->company = $row['company'];
                 }
                 if (isset($row['title'])) {
                     $contact->title = $row['title'];
                 }
                 if (isset($row['address'])) {
                     $contact->address = trim(str_replace("\n", ' ', $row['address']));
                 }
                 if (isset($row['city'])) {
                     $contact->city = $row['city'];
                 }
                 if (isset($row['state'])) {
                     $contact->state = $row['state'];
                 }
                 if (isset($row['zip'])) {
                     $contact->zip = $row['zip'];
                 }
                 $contact->save();
                 if (!$contact->bounced && !$contact->unsubscribe) {
                     array_push($this->contacts, $contact->id);
                 }
             }
         });
     });
     $contactCount = 0;
     foreach ($this->contacts as $contact) {
         if (!$campaign->contacts->contains($contact)) {
             $campaign->contacts()->attach($contact);
             $contactCount++;
         }
     }
     return redirect()->route('admin.campaigns.show', $campaign->id)->with('message', "{$contactCount} contacts added to Campaign");
 }

 public function proses_import()
 {
     $input = Input::all();
     $rules = ['excel' => 'required'];
     $pesan = ['excel.required' => 'File excel harus diisi'];
     $validasi = Validator::make($input, $rules, $pesan);
     if ($validasi->fails()) {
         Session::flash('pesan', "<div class='alert alert-danger'>Pilih File yang akan diimport</div>");
         return Redirect::back()->withInput();
     } else {
         $excel = Input::file('excel');
         //ambil sheet pertama
         $excels = Excel::selectSheetsByIndex(0)->load($excel, function ($reader) {
             //options jika ada
         })->get();
         //digunakan untuk menghitung total siswa yang masuk
         $counter = 0;
         $rowRules = ['id' => 'required', 'nm_guru' => 'required'];
         foreach ($excels as $row) {
             //membuat validasi untuk row di excel
             //jangan lupa mengubah $row menjadi array
             $validasi = Validator::make($row->toArray(), $rowRules);
             //skip baris ini jika tidak valid, langsung ke baris berikutnya
             if ($validasi->fails()) {
                 continue;
             }
             $cek = DB::table('guru')->where('id', '=', $row['id'])->count();
             if ($cek > 0) {
                 continue;
             }
             $data = array('id' => $row['id'], 'nip' => $row['nip'], 'nuptk' => $row['nuptk'], 'nm_guru' => $row['nm_guru']);
             DB::table('guru')->insert($data);
             //membuat guru baru
             $guru = Sentry::register(array('password' => $row['id'], 'username' => $row['id']), true);
             //cari group berdasarkan nama=guru
             $guruGroup = Sentry::findGroupByName('guru');
             //masukan user ke group guru
             $guru->addGroup($guruGroup);
             $counter++;
         }
         Session::flash('pesan', "<hr><div class='alert alert-info'>\n\t\t\t\tBerhasil mengimport " . $counter . " guru</div>");
         return Redirect::to('admin/guru');
     }
 }

 public function importExcel()
 {
     ini_set('max_execution_time', 300);
     $rules = ['excel' => 'required'];
     $validasi = Validator::make(Input::all(), $rules);
     if ($validasi->fails()) {
         return Redirect::back()->withErrors($validasi)->withInput();
     }
     $excel = Input::file('excel');
     //ambil sheet pertama
     $excels = Excel::selectSheetsByIndex(0)->load($excel, function ($reader) {
         //options jika ada
     })->get();
     //digunakan untuk menghitung total siswa yang masuk
     $counter = 0;
     $rowRules = ['nis' => 'required', 'nm_siswa' => 'required', 'jk' => 'required'];
     foreach ($excels as $row) {
         //membuat validasi untuk row di excel
         //jangan lupa mengubah $row menjadi array
         $validasi = Validator::make($row->toArray(), $rowRules);
         //skip baris ini jika tidak valid, langsung ke baris berikutnya
         if ($validasi->fails()) {
             continue;
         }
         $cek = DB::table('siswa')->where('nis', '=', $row['nis'])->count();
         if ($cek > 0) {
             continue;
         }
         $data = array('nis' => $row['nis'], 'nm_siswa' => $row['nm_siswa'], 'jk' => $row['jk']);
         DB::table('siswa')->insert($data);
         //membuat siswa baru
         $cekEmail = DB::table('users')->where('username', '=', $row['nis'])->count();
         if ($cekEmail > 0) {
             continue;
         }
         $siswa = Sentry::register(array('password' => $row['nis'], 'username' => $row['nis']), true);
         //cari group berdasarkan nama=siswa
         $siswaGroup = Sentry::findGroupByName('siswa');
         $siswa->addGroup($siswaGroup);
         $data = array('kd_rombel' => $row['thn_ajaran'] . '-' . $row['kelas'], 'nis' => $row['nis']);
         DB::table('siswa_rombel')->insert($data);
         $counter++;
     }
     Session::flash('pesan', "<hr><div class='alert alert-info'>\n\t\t\tBerhasil mengimport " . $counter . " siswa</div>");
     return Redirect::to('admin/siswa');
 }

 public function import()
 {
     ini_set('max_execution_time', 300);
     $input = Input::all();
     $rules = ['excel' => 'required'];
     $pesan = ['excel.required' => 'File excel harus diisi'];
     $validasi = Validator::make($input, $rules, $pesan);
     if ($validasi->fails()) {
         Session::flash('pesan', "<div class='alert alert-danger'>Pilih File yang akan diimport</div>");
         return Redirect::back()->withInput();
     } else {
         $excel = Input::file('excel');
         //ambil sheet pertama
         $excels = Excel::selectSheetsByIndex(0)->load($excel, function ($reader) {
             //options jika ada
         })->get();
         //digunakan untuk menghitung total siswa yang masuk
         $counter = 0;
         $rowRules = ['kd_rombel' => 'required', 'nis' => 'required'];
         foreach ($excels as $row) {
             //membuat validasi untuk row di excel
             //jangan lupa mengubah $row menjadi array
             $validasi = Validator::make($row->toArray(), $rowRules);
             //skip baris ini jika tidak valid, langsung ke baris berikutnya
             if ($validasi->fails()) {
                 continue;
             }
             if ($row['kd_rombel'] != Input::get('rombel')) {
                 continue;
             }
             $rombel = explode("-", $row['kd_rombel']);
             //cek siswa apakah sudah ada di rombel tertentu pada tahun ajaran sekian
             $siswarombel = DB::table('detail_rombel')->where('kd_rombel', 'like', $rombel[0] . "%")->where('nis', '=', $row['nis'])->count();
             //jika sudah ada maka proses dilanjutkan tanpa save data ke detail rombel
             if ($siswarombel > 0) {
                 continue;
             }
             $cek = DB::table('detail_rombel')->where('kd_rombel', '=', $row['kd_rombel'])->where('nis', '=', $row['nis'])->count();
             if ($cek > 0) {
                 continue;
             }
             $data = array('kd_rombel' => $row['kd_rombel'], 'nis' => $row['nis']);
             DB::table('detail_rombel')->insert($data);
             $counter++;
         }
         Session::flash('pesan', "<hr><div class='alert alert-info'>\n\t\t\t\t\tBerhasil mengimport " . $counter . " siswa</div>");
         return Redirect::back();
     }
 }

 public function excel()
 {
     //print_r(Input::get('excel'));
     //return 0;
     //return View::make('factura.import');
     $dir = "files/excel/";
     $fecha = base64_encode("excel" . date('d/m/Y-H:m:i'));
     $file_name = $fecha;
     //return $file_name;
     $file = Input::file('excel');
     $destinationPath = 'files/excel/';
     // If the uploads fail due to file system, you can try doing public_path().'/uploads'
     $filename = $file_name;
     //str_random(12);
     //$filename = $file->getClientOriginalName();
     //$extension =$file->getClientOriginalExtension();
     $upload_success = Input::file('excel')->move($destinationPath, $filename);
     //        if( $upload_success ) {
     //           return Response::json('success', 200);
     //        } else {
     //           return Response::json('error', 400);
     //        }
     //        return 0;
     $results = Excel::selectSheetsByIndex(0)->load($dir . $file_name)->get();
     $factura = array();
     $groups = array();
     //shattering file gotten
     $nit = "";
     foreach ($results as $key => $res) {
         $dato = [];
         //$nit = "";
         foreach ($res as $r) {
             if ($r != "") {
                 array_push($dato, $r);
             }
         }
         //$nit = $dato[0];
         if ($dato) {
             if ($nit == $dato[0]) {
                 array_push($groups, $dato);
             } else {
                 if ($groups) {
                     $bbr = ['id' => $groups[0][0], 'nit' => $groups[0][1], 'nota' => $groups[0][5]];
                     $products = array();
                     foreach ($groups as $gru) {
                         $pro['product_key'] = $gru[2];
                         $pro['description'] = $gru[3];
                         $pro['cost'] = $gru[4];
                         array_push($products, $pro);
                     }
                     $bbr['products'] = $products;
                     array_push($factura, $bbr);
                 }
                 $groups = array();
                 $nit = $dato[0];
                 array_push($groups, $dato);
             }
         }
     }
     if ($groups) {
         $bbr = ['id' => $groups[0][0], 'nit' => $groups[0][1], 'nota' => $groups[0][5]];
         $products = array();
         foreach ($groups as $gru) {
             $pro['product_key'] = $gru[2];
             $pro['description'] = $gru[3];
             $pro['cost'] = $gru[4];
             array_push($products, $pro);
         }
         $bbr['products'] = $products;
         array_push($factura, $bbr);
     }
     $returnable = $this->validateShatterExcel($factura);
     if ($returnable != "") {
         Session::flash('error', $returnable);
         return View::make('factura.import');
     }
     //echo $returnable;
     //return  0;
     $cont = 0;
     foreach ($factura as $fac) {
         $this->saveLote($fac);
         $cont++;
     }
     Session::flash('message', 'Se importaron ' . $cont . ' facturas exitósamente');
     $invoices = Invoice::where('account_id', Auth::user()->account_id)->where('branch_id', Session::get('branch_id'))->orderBy('public_id', 'DESC')->get();
     return View::make('factura.index', array('invoices' => $invoices));
     return 0;
 }

 public function import()
 {
     $rules = ['excel' => 'required'];
     $pesan = ['excel.required' => 'Pilih file terlebih dahulu'];
     $validasi = Validator::make(Input::all(), $rules, $pesan);
     if ($validasi->fails()) {
         echo "gagal";
     } else {
         $excel = Input::file('excel');
         //ambil sheet pertama
         $excels = Excel::selectSheetsByIndex(0)->load($excel, function ($reader) {
             //options jika ada
         })->get();
         $counter = 0;
         $rowRules = ['nip' => 'required', 'rombel' => 'required', 'mapel' => 'required', 'kkm' => 'required'];
         foreach ($excels as $row) {
             //membuat validasi untuk row di excel
             //jangan lupa mengubah $row menjadi array
             $validasi = Validator::make($row->toArray(), $rowRules);
             //skip baris ini jika tidak valid, langsung ke baris berikutnya
             if ($validasi->fails()) {
                 continue;
             }
             $cek = DB::table('mengajar')->where('id_mengajar', '=', $row['rombel'] . "-" . $row['nip'] . "-" . $row['mapel'])->count();
             if ($cek > 0) {
                 continue;
             }
             $data = array('id_mengajar' => $row['rombel'] . "-" . $row['nip'] . "-" . $row['mapel'], 'kd_rombel' => $row['rombel'], 'nip' => $row['nip'], 'kd_mapel' => $row['mapel'], 'nilai_kkm' => $row['kkm']);
             DB::table('mengajar')->insert($data);
             $counter++;
         }
         Session::flash('pesan', "<hr><div class='alert alert-info'>\n\t\t\t\tBerhasil mengimport " . $counter . " data mengajar</div>");
         return Redirect::to('admin/mengajar');
     }
 }