控制器文件:
- class ExcelAction extends Action {
- public function __construct()
- {
- import('ORG.Util.ExcelToArrary');//导入excelToArray类
- }
- public function index()
- {
- $this->display();
- }
- public function add()
- {
- $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 ) != "xlsx" && strtolower ( $file_type ) != "xls")
- {
- $this->error ( '不是Excel文件,重新上传' );
- }
- /*设置上传路径*/
- $savePath = C('UPLOAD_DIR');
- /*以时间来命名上传的文件*/
- $str = date ( 'Ymdhis' );
- $file_name = $str . "." . $file_type;
- /*是否上传成功*/
- if (! copy ( $tmp_file, $savePath . $file_name ))
- {
- $this->error ( '上传失败' );
- }
- $ExcelToArrary=new ExcelToArrary();//实例化
- $res=$ExcelToArrary->read(C('UPLOAD_DIR').$file_name,"UTF-8",$file_type);//传参,判断office2007还是office2003
- foreach ( $res as $k => $v ) //循环excel表
- {
- $k=$k-1;//addAll方法要求数组必须有0索引
- $data[$k]['name1'] = $v [0];//创建二维数组
- $data[$k]['name2'] = $v [1];
- $data[$k]['name3'] = $v [2];
- $data[$k]['name4'] = $v [3];
- }
- $kucun=M('kucun');//M方法
- $result=$kucun->addAll($data);
- if(! $result)
- {
- $this->error('导入数据库失败');
- exit();
- }
- else
- {
- $this->success ( '导入成功' );
- }
- }
定义Excel转换数组类
- class ExcelToArrary {
- public function __construct() {
- Vendor("Excel.PHPExcel");//引入phpexcel类(注意你自己的路径)
- Vendor("Excel.PHPExcel.IOFactory");
- }
- public function read($filename,$encode,$file_type){
- if(strtolower ( $file_type )=='xls')//判断excel表类型为2003还是2007
- {
- Vendor("Excel.PHPExcel.Reader.Excel5");
- $objReader = PHPExcel_IOFactory::createReader('Excel5');
- }elseif(strtolower ( $file_type )=='xlsx')
- {
- Vendor("Excel.PHPExcel.Reader.Excel2007");
- $objReader = PHPExcel_IOFactory::createReader('Excel2007');
- }
- $objReader->setReadDataOnly(true);
- $objPHPExcel = $objReader->load($filename);
- $objWorksheet = $objPHPExcel->getActiveSheet();
- $highestRow = $objWorksheet->getHighestRow();
- $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;
- }
前台导入页面
- <form method="post" action="Excel/add" enctype="multipart/form-data">
- <h3>导入Excel表:</h3><input type="file" name="file_stu" />
- <input type="submit" value="导入" />
- </form>
注意:在php.ini里面把;extension=php_zip.dll前面的分号去掉