首先:下载好PHPExcel类库文件
视图层:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"> <head> <meta http-equiv="Content-Type" content="text/html;charset=UTF-8" /> <title></title> </head> <body> <form action="<?php echo site_url('admin/excel/import_pro') ?>" method='post' enctype="multipart/form-data" > <input type="file" name='file_stu' /> <input type="submit" value='导入' /> </form> </body> </html>
控制器:
<?php defined('BASEPATH') OR exit('No direct script access allowed'); class Excel extends CI_Controller{ function __construct() { parent::__construct(); //$this->load->library('PHPExcel'); //$this ->load ->library('PHPExcel/IOFactory'); } //从数据表导出到excel public function export($table_name){ $query = $this -> db -> get($table_name); //print_r($query); if(!$query)return false; // StartingthePHPExcellibrary //加载PHPExcel类 $this->load->library('PHPExcel'); $this ->load ->library('PHPExcel/IOFactory'); $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()-> setTitle("export") -> setDescription("none"); $objPHPExcel -> setActiveSheetIndex(0); // Fieldnamesinthefirstrow $fields = $query -> list_fields(); $col = 0; foreach($fields as $field){ $objPHPExcel -> getActiveSheet() -> setCellValueByColumnAndRow($col, 1,$field); $col++; } // Fetchingthetabledata $row = 2; foreach($query->result() as $data) { $col = 0; foreach($fields as $field) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$data->$field); $col++; } $row++; } $objPHPExcel -> setActiveSheetIndex(0); $objWriter = IOFactory :: createWriter($objPHPExcel, 'Excel5'); // Sendingheaderstoforcetheusertodownloadthefile header('Content-Type:application/vnd.ms-excel'); //header('Content-Disposition:attachment;filename="Products_' . date('dMy') . '.xls"'); header('Content-Disposition:attachment;filename="Brand_' . date('Y-m-d') . '.xls"'); header('Cache-Control:max-age=0'); $objWriter -> save('php://output'); } //从excel导入到数据表 function import(){ $this->load->view('excel_import.html'); } //从excel导入到数据表 function import_pro(){ //要处理的excel文件 //$filename = './sampleData/example2.xls';//指定文件 //用用选择excel文件 //print_r($_FILES);exit; $tmp_file = $_FILES ['file_stu'] ['tmp_name']; $file_types = explode ( ".", $_FILES ['file_stu'] ['name'] ); $file_type = $file_types [count ( $file_types ) - 1]; /*判别是不是.xls文件,判别是不是excel文件*/ if (strtolower ( $file_type ) != "xls"){ $this->error ( '不是Excel文件,重新上传' ); } $savePath = "Public/uploads/excel/"; /*以时间来命名上传的文件*/ $str = date ( 'Ymdhis' ); $file_name = $str . "." . $file_type; /*是否上传成功*/ if(!copy($tmp_file,$savePath.$file_name)){ $this->error ( '上传失败' ); } //要获得新的文件路径+名字 $fullpath = $savePath.$file_name; //echo $fullpath; $re = $this->read($fullpath,'utf-8'); //var_dump($re); return $re; }
public function read($filename,$encode='utf-8'){ $this ->load ->library('PHPExcel/IOFactory'); $objReader = IOFactory::createReader('Excel5'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($filename); $objWorksheet = $objPHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); //echo $highestRow; $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $excelData = array(); for($row = 1; $row <= $highestRow; $row++) { for ($col = 0; $col < $highestColumnIndex; $col++) { $excelData[$row][]=(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); } } return $excelData; } }
具体内容,自己操作便知!
|
请发表评论