PHP Excel类代码示例

 public function import($entity)
 {
     $appHelper = new libs\AppHelper();
     $className = $appHelper->getNameSpace() . $entity;
     $model = new $className();
     $table = $model->getTable();
     $columns = \Schema::getColumnListing($table);
     $key = $model->getKeyName();
     $notNullColumnNames = array();
     $notNullColumnsList = \DB::select(\DB::raw("SHOW COLUMNS FROM `" . $table . "` where `Null` = 'no'"));
     if (!empty($notNullColumnsList)) {
         foreach ($notNullColumnsList as $notNullColumn) {
             $notNullColumnNames[] = $notNullColumn->Field;
         }
     }
     $status = \Input::get('status');
     $filePath = null;
     if (\Input::hasFile('import_file') && \Input::file('import_file')->isValid()) {
         $filePath = \Input::file('import_file')->getRealPath();
     }
     if ($filePath) {
         \Excel::load($filePath, function ($reader) use($model, $columns, $key, $status, $notNullColumnNames) {
             $this->importDataToDB($reader, $model, $columns, $key, $status, $notNullColumnNames);
         });
     }
     $importMessage = $this->failed == true ? \Lang::get('panel::fields.importDataFailure') : \Lang::get('panel::fields.importDataSuccess');
     return \Redirect::to('panel/' . $entity . '/all')->with('import_message', $importMessage);
 }

 public function exportIt($return)
 {
     return parent::exportIt($return);
     $filename = $return['classname'] . "_" . date('Ymd');
     $xls = new Excel($filename);
     //$filename = $return['classname'] . "_" . date('Ymd') . ".xls";
     //header("Content-Disposition: attachment; filename=\"$filename\"");
     //header("Content-Type: application/vnd.ms-excel");
     //$flag = false;
     $xls->home();
     foreach ($return['objs'] as $key => $obj) {
         foreach ($obj as $name => $value) {
             $xls->label(Lang::t($name));
             $xls->right();
         }
         break;
     }
     $xls->down();
     //print("\n");
     foreach ($return['objs'] as $key => $obj) {
         $xls->home();
         foreach ($obj as $name => $value) {
             $xls->label($value);
             $xls->right();
         }
         $xls->down();
     }
     $xls->send();
     exit;
 }

 public function downloadExcel()
 {
     include '/Data/ClassLib/Excel.class.php';
     $a = new \Excel();
     $a->set(array('A1' => 'test data'));
     $a->download('test');
 }

 /**
  * 生成excel
  *
  * @param array $data
  */
 private function createExcel($data = array())
 {
     Language::read('export');
     import('libraries.excel');
     $excel_obj = new Excel();
     $excel_data = array();
     //设置样式
     $excel_obj->setStyle(array('id' => 's_title', 'Font' => array('FontName' => '宋体', 'Size' => '12', 'Bold' => '1')));
     //header
     $excel_data[0][] = array('styleid' => 's_title', 'data' => '订单编号');
     $excel_data[0][] = array('styleid' => 's_title', 'data' => '下单时间');
     $excel_data[0][] = array('styleid' => 's_title', 'data' => '付款时间');
     $excel_data[0][] = array('styleid' => 's_title', 'data' => '运费');
     $excel_data[0][] = array('styleid' => 's_title', 'data' => '物流单号');
     //data
     foreach ((array) $data as $k => $v) {
         $tmp = array();
         $tmp[] = array('data' => 'NC' . $v['order_sn']);
         $tmp[] = array('data' => date('Y-m-d H:i:s', $v['add_time']));
         $tmp[] = array('data' => $v['payment_time'] ? date('Y-m-d H:i:s', $v['payment_time']) : '');
         $tmp[] = array('format' => 'Number', 'data' => $v['shipping_fee']);
         $tmp[] = array('data' => $v['shipping_code']);
         $excel_data[] = $tmp;
     }
     $excel_data = $excel_obj->charset($excel_data, CHARSET);
     $excel_obj->addArray($excel_data);
     $excel_obj->addWorksheet($excel_obj->charset(L('exp_od_order'), CHARSET));
     $excel_obj->generateXML($excel_obj->charset(L('exp_od_order'), CHARSET) . $_GET['curpage'] . '-' . date('Y-m-d-H', time()));
 }

 function onlogView($msg = '', $ty = '')
 {
     $action = "index.php?admin_log/logView";
     $hasLogViewPrivilege = $_ENV['menu']->checkPermission($this->ask_login_name, $_SERVER['QUERY_STRING'], "logView");
     $hasLogViewPrivilege['url'] = "?admin_main";
     $hasLogViewPrivilege['return'] = true;
     !$hasLogViewPrivilege['return'] && __msg($hasLogViewPrivilege);
     $LogTypeList = $this->ask_config->getLogType();
     $operator_list = $_ENV['operator']->getList(0, 0);
     $ConditionList['StartDate'] = isset($this->post['StartDate']) ? $this->post['StartDate'] : (isset($this->get[2]) ? $this->get[2] : date("Y-m-d", time()));
     $ConditionList['EndDate'] = isset($this->post['EndDate']) ? $this->post['EndDate'] : (isset($this->get[3]) ? $this->get[3] : date("Y-m-d", time()));
     $ConditionList['EndDate'] = min($ConditionList['EndDate'], date("Y-m-d", time()), date("Y-m-t", strtotime($ConditionList['StartDate'])));
     $ConditionList['operatorId'] = isset($this->post['operatorId']) ? intval(urldecode($this->post['operatorId'])) : (isset($this->get[4]) ? intval(urldecode($this->get[4])) : 0);
     $ConditionList['operator'] = !in_array($ConditionList['operatorId'], array(-2, -1, 0)) ? $operator_list[$ConditionList['operatorId']]['login_name'] : $ConditionList['operatorId'];
     $ConditionList['QuestionId'] = isset($this->post['QuestionId']) ? intval($this->post['QuestionId']) : (isset($this->get[5]) ? intval($this->get[5]) : 0);
     $ConditionList['log_type_id'] = isset($this->post['log_type_id']) ? intval(urldecode($this->post['log_type_id'])) : (isset($this->get[6]) ? intval(urldecode($this->get[6])) : 0);
     $ConditionList['AuthorName'] = isset($this->post['AuthorName']) ? trim(urldecode($this->post['AuthorName'])) : (isset($this->get[7]) ? trim(urldecode($this->get[7])) : '');
     $ConditionList['log_type'] = $ConditionList['log_type_id'] ? $LogTypeList[$ConditionList['log_type_id']] : $ConditionList['log_type_id'];
     @($page = max(1, intval($this->get[8])));
     $export = trim($this->get[9]) == "export" ? 1 : 0;
     $setting = $this->setting;
     if (!$export) {
         $pagesize = $this->setting['list_default'];
         $pagesize = 20;
         $log_list = $_ENV['log']->getLogList($ConditionList, $page, $pagesize);
         $departstr = page($log_list['LogCount'], $pagesize, $page, "admin_log/logView/" . $ConditionList['StartDate'] . "/" . $ConditionList['EndDate'] . "/" . urlencode($ConditionList['operatorId']) . "/" . $ConditionList['QuestionId'] . "/" . $ConditionList['log_type_id'] . "/" . urlencode($ConditionList['AuthorName']));
         $downloadstr = page_url("<下载EXCEL表格>", "admin_log/logView/" . $ConditionList['StartDate'] . "/" . $ConditionList['EndDate'] . "/" . urlencode($ConditionList['operatorId']) . "/" . $ConditionList['QuestionId'] . "/" . $ConditionList['log_type_id'] . "/" . urlencode($ConditionList['AuthorName']) . "/" . $page . "/export");
         $msg && ($message = $msg);
         $ty && ($type = $ty);
     } else {
         set_time_limit(0);
         $page = 1;
         $pagesize = 1000;
         $num = 1;
         require TIPASK_ROOT . '/lib/Excel.php';
         $oExcel = new Excel();
         $FileName = '操作日志表';
         $oExcel->download($FileName)->addSheet('日志表');
         //标题栏
         $title = array("日志ID", "对应问题ID", "用户名", "操作人", "操作时间", "日志内容");
         $oExcel->addRows(array($title));
         while ($num > 0) {
             $log_list = $_ENV['log']->getLogList($ConditionList, $page, $pagesize);
             foreach ($log_list['LogList'] as $key => $value) {
                 $value['message'] = htmlspecialchars_decode($value['message']);
                 $log_list['LogList'][$key]['message'] = preg_replace("/<(.*?)>/", "", $value['message']);
                 $excelArr = array("id" => $log_list['LogList'][$key]['id'], "qid" => $log_list['LogList'][$key]['qid'], "AuthorName" => $log_list['LogList'][$key]['AuthorName'], "user" => $log_list['LogList'][$key]['user'], "time" => date("Y-m-d H:i:s", $log_list['LogList'][$key]['time']), "message" => $log_list['LogList'][$key]['message']);
                 $oExcel->addRows(array($excelArr));
             }
             $page++;
             $num = count($order_list['OrderList']);
         }
         $oExcel->closeSheet()->close();
     }
     include template('logview', 'admin');
 }

 /**
  * 导出excel表格
  */
 public static function exportFile($data, $type = 'xml', $fileName = 'excel-export')
 {
     if ($type == 'xml') {
         $xls = new Excel('UTF-8', false);
         $xls->addArray($data);
         $xls->generateXML($fileName);
     }
     if ($type == 'pdf') {
     }
     return;
 }

 /**
  * saveContants 
  * 存储通讯录
  * @access private
  * @return void
  */
 private function saveContants()
 {
     $ret = array("succ" => TRUE, "msg" => array());
     $excel = new Excel();
     $contantsArr = $excel->readExcel($this->_filePath, $this->_activeSheet, $this->_headers);
     foreach ($contantsArr['contents'] as $key => $contant) {
         if (FALSE === $this->contantsAddAttr($contant[0], $contant[1], $contant[2], $contant[3])) {
             $ret['msg'] = FALSE;
             array_push($ret['msg'], "{$contant[3]} 插入失败");
         }
     }
     return $ret;
 }

 public function testOutput()
 {
     $this->specifyConfig()->cloneOnly(['excel', 'path']);
     $excel = new \Excel();
     $excel->worksheet('Worksheet', ['-']);
     $path = $this->savePath;
     $this->specify('save .xls to file', function () use($excel, $path) {
         $isExported = $excel->export('export.xls', $path);
         $this->assertTrue($isExported);
         $this->assertFileExists($path . 'export.xls');
     });
     $this->specify('save .xlsx to file', function () use($excel, $path) {
         $isExported = $excel->export('export.xlsx', $path);
         $this->assertTrue($isExported);
         $this->assertFileExists($path . 'export.xlsx');
     });
     $this->specify('save .html to file', function () use($excel, $path) {
         $isExported = $excel->export('export.html', $path);
         $this->assertTrue($isExported);
         $this->assertFileExists($path . 'export.html');
     });
     $this->specify('save .csv to file', function () use($excel, $path) {
         $isExported = $excel->export('export.csv', $path);
         $this->assertTrue($isExported);
         $this->assertFileExists($path . 'export.csv');
     });
     $this->specify('save .csv to not existing dir', function () use($excel, $path) {
         $isExported = $excel->export('export.csv', '/pmt/');
         $this->assertFalse($isExported);
         $this->assertFileNotExists('/pmt/export.csv');
     });
     $isExported = $excel->export();
     $this->assertTrue($isExported);
     $this->assertNotEmpty(ob_get_contents());
 }

 public function import(Request $request, UserRepository $userRepository, Bus $bus)
 {
     $filename = $request->file;
     $programId = $request->program_id;
     \Excel::load('storage/app/' . $filename, function ($reader) use($programId, $userRepository, $bus) {
         $sheets = $reader->all();
         $sheets->each(function ($sheet) use($programId, $userRepository, $bus) {
             $sheet->each(function ($row) use($programId, $userRepository, $bus) {
                 $lecturer = Lecturer::firstOrNew(['reg_no' => $row->nidn_nup_nidk]);
                 $lecturer->local_reg_no = $row->nip;
                 $lecturer->name = $row->nama;
                 $user = $userRepository->findByUsername($lecturer->reg_no);
                 if (!$user) {
                     $role = Role::where('name', 'Lecturer')->first();
                     $roles = $role ? [$role->id] : [];
                     $desc = ['password' => bcrypt($lecturer->reg_no), 'name' => $lecturer->name, 'active' => true];
                     $bus->dispatch(new RegisterNewUser($lecturer->reg_no, $lecturer->reg_no . '@email.com', $roles, $desc));
                     $user = $userRepository->findByUsername($lecturer->reg_no);
                 }
                 $lecturer->user_id = $user->id()->value();
                 $lecturer->save();
             });
         });
     });
     return $this->formSuccess(route('admin.employee.lecturer.index'), ['message' => 'Import dosen berhasil']);
 }

 public function postshopdata()
 {
     $file = Input::file('shopdata');
     $destinationPath = 'importdata';
     // If the uploads fail due to file system, you can try doing public_path().'/uploads'
     $filename = 'imported-shoppinglistdata';
     //$filename = $file->getClientOriginalName();
     //$extension =$file->getClientOriginalExtension();
     $upload_success = Input::file('shopdata')->move($destinationPath, $filename . '.' . $file->getClientOriginalExtension());
     if ($upload_success) {
         $importedFile = public_path() . '/importdata/' . $filename . '.' . $file->getClientOriginalExtension();
         Excel::load($importedFile, function ($r) {
             $res = $r->all()->toArray();
             foreach ($res as $data) {
                 if ($data['kode'] != '' && $data['deskripsi'] != '' && $data['satuan'] != '' && $data['harga'] != '') {
                     $d = new ShoppingList();
                     $d->kode_sl = $data['kode'];
                     $d->deskripsi_pekerjaan = $data['deskripsi'];
                     $d->satuan = $data['satuan'];
                     $d->harga = $data['harga'];
                     $d->save();
                 }
             }
         });
         Session::flash('success', 'Shoppinglist Data imported to database');
         return Redirect::to('/import');
     } else {
         Session::flash('error', 'Error uploading files');
         return Redirect::to('/import');
     }
 }

 public function postPurchases()
 {
     $input_start_date = \Input::get('start_date');
     if ($input_start_date == "") {
         $input_start_date = "01/01/1900";
     }
     $start_date = \DateTime::createFromFormat('d/m/Y', $input_start_date);
     $input_end_date = \Input::get('end_date');
     if ($input_end_date == "") {
         $end_date = new \DateTime("NOW");
     } else {
         $end_date = \DateTime::createFromFormat('d/m/Y', $input_end_date);
     }
     $data = UserPricelist::where('created_at', '>=', $start_date)->where('created_at', '<=', $end_date)->orderBy('created_at', 'desc')->get();
     if (count($data) == 0) {
         $errors = new \Illuminate\Support\MessageBag();
         $errors->add('downloadError', "There's no data within the dates specified.");
         return \Redirect::to('admin/purchases')->withErrors($errors);
     }
     \Excel::create('Redooor_Purchases_Report', function ($excel) use($data) {
         $excel->sheet('Purchases Report', function ($sheet) use($data) {
             $sheet->loadView('redminportal::reports/purchases')->with('data', $data);
         });
     })->download('csv');
 }

 public function post()
 {
     $file = Input::file('grade');
     \Excel::load($file->getRealPath(), function ($reader) {
     })->get();
     return redirect()->back();
 }

 public function fire()
 {
     $dir = $_SERVER['DOCUMENT_ROOT'] . "public/toUpload/";
     try {
         Excel::filter('chunk')->load($dir . 'data.xlsx')->chunk(250, function ($reader) {
             foreach ($reader->toArray() as $data) {
                 $user = User::where('identification_card', $data['cedula'])->first();
                 if (!$user) {
                     $user = User::create(['identification_card' => $data['cedula'], 'name' => $data['nombre'], 'user_name' => str_replace(' ', '.', $data['nombre']), 'email' => $data['email'] ? $data['email'] : 'Sin registro', 'address' => $data['direccion'] ? $data['direccion'] : 'Sin registro', 'residency_city' => $data['ciudad'] ? $data['ciudad'] : 'Sin registro', 'phone' => $data['tel1'] ? $data['tel1'] : 'Sin registro', 'mobile_phone' => $data['tel2'] ? $data['tel2'] : 'Sin registro', 'document_type' => 0, 'roles_id' => 4]);
                 }
                 $credit = CreditRequest::where('user_id', intval($user->id))->first();
                 if (!$credit) {
                     $c = new CreditRequest();
                     $c->user_id = intval($user->id);
                     $c->value = intval($data['limitecredito']);
                     $c->state = 1;
                     $c->location = 3;
                     $c->responsible = 18;
                     $c->save();
                 } else {
                     $credit->value = $data['limitecredito'];
                     $credit->save();
                 }
             }
         });
         echo $message = "El archivo  se ha guardado en la base de datos.";
     } catch (Exception $e) {
         echo $message = "No se ha guardar . Intenta subirlo de nuevo.";
     }
     Mail::send('emails.excel', ['msn' => $message], function ($m) use($message) {
         $m->to('sanruiz1003@gmail.com', 'Creditos Lilipink')->subject('Archivos actualizados');
     });
     array_map('unlink', glob($_SERVER['DOCUMENT_ROOT'] . "/toUpload/*"));
 }

 /**
  * Execute the console command.
  *
  * @return mixed
  */
 public function fire()
 {
     $dir = $_SERVER['DOCUMENT_ROOT'] . "public/toUpload/";
     $doc = $this->argument('table') . '.xlsx';
     try {
         Excel::filter('chunk')->load($dir . $doc)->chunk(250, function ($reader) {
             if ($this->argument('table') == 'extracts') {
                 Extract::insert($this->validate($reader));
             } else {
                 ExcelDaily::insert($this->validate($reader));
             }
         });
         $message = "El archivo " . $doc . " se ha guardado en la base de datos.";
     } catch (Exception $e) {
         $message = "No se ha guardar " . $doc . ". Intenta subirlo de nuevo.";
     }
     /*Mail::send('emails.excel', ['msn' => $message], function ($m) use($message){
     			$m->to('sanruiz1003@gmail.com', 'Creditos Lilipink')->subject('Notificación Lilipink');
     		});*/
     Mail::send('emails.excel', ['msn' => $message], function ($m) use($message) {
         $m->to('carterainnova@innova-quality.com.co', 'Creditos Lilipink')->subject('Notificación Lilipink');
     });
     unlink($dir . $doc);
     if (!is_dir($dir)) {
         rmdir($dir);
     }
 }

 public function uploadFile()
 {
     $importfile = Input::file('upload_ex_cik');
     //$cik_no_change = array();
     Excel::load($importfile, function ($reader) {
         $result = $reader->all();
         $cik_no_change = array();
         //$i=0;
         foreach ($result as $in) {
             if ($in['cik'] != "") {
                 $ckj = $in['cik'];
                 $ckj_details = Cik::where('name', $ckj)->first();
                 if ($ckj_details) {
                     array_push($cik_no_change, $ckj);
                     //$cik_no_change[$i]=$ckj;
                     //$i=$i+1;
                 } else {
                     $ckj_details = new Cik();
                     $ckj_details->name = $ckj;
                     $ckj_details->status = 0;
                     $ckj_details->save();
                 }
             }
         }
         //print_r($cik_no_change);
     });
     //$response = Response::json($cik_no_change);
     //  return $response;
     $allUser = User::where('role_id', 1)->get();
     return View::make('admin.adminDashboard')->withUser($allUser);
 }