<?php/** * Created by PhpStorm. * User: admin * Date: 2017/8/15 * Time: 9:07 */class User extends CI_Controller { ????public function __construct(){ ????????parent::__construct(); ????????$this->load->database(); ????} ???public function index(){ ???????$this->load->view(‘head‘); ???????$this->load->view(‘admin/user/upload‘); ???????$this->load->view(‘footer‘); ???} ????public function export(){ ????????$this->load->library(‘PHPExcel‘); ????????$objPHPExcel=new PHPExcel(); ????????$sql="select id,username,password,email,mobile,create_time from user"; ????????$query=$this->db->query($sql); ????????$data=$query->result_array(); ????????$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007");//设置excel的属性：//合并单元格 ????????$objPHPExcel->getActiveSheet()->mergeCells(‘A1:F1‘); ????????$objPHPExcel->getActiveSheet()->mergeCells(‘A2:F2‘);//设置表头行高 ????????$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35); ????????$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(25);//设置font ????????$objPHPExcel->getActiveSheet()->getStyle(‘A:F‘)->getFont()->setName(‘Microsoft YaHei UI‘); ????????$objPHPExcel->getActiveSheet()->getStyle(‘A1‘)->getFont()->setSize(16); ????????$objPHPExcel->getActiveSheet()->getStyle(‘A2‘)->getFont()->setSize(14); ????????$objPHPExcel->getActiveSheet()->getStyle(‘A:F‘)->getFont()->setSize(12);//设置默认行高 ????????$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);//设置列宽 ????????$objPHPExcel->getActiveSheet()->getColumnDimension(‘A‘)->setWidth(8); ????????$objPHPExcel->getActiveSheet()->getColumnDimension(‘B‘)->setWidth(15); ????????$objPHPExcel->getActiveSheet()->getColumnDimension(‘C‘)->setWidth(25); ????????$objPHPExcel->getActiveSheet()->getColumnDimension(‘D‘)->setWidth(35); ????????$objPHPExcel->getActiveSheet()->getColumnDimension(‘E‘)->setWidth(25); ????????$objPHPExcel->getActiveSheet()->getColumnDimension(‘F‘)->setWidth(45);//设置表头对齐方式 ????????$objPHPExcel->getActiveSheet()->getStyle(‘A1:A2‘)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); ????????$objPHPExcel->getActiveSheet()->getStyle(‘A1:A2‘)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置水平左对齐 ????????$objPHPExcel->getActiveSheet()->getStyle(‘A:J‘)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);//所有垂直居中 ????????$objPHPExcel->getActiveSheet()->getStyle(‘A:J‘)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//填入主标题 ????????$objPHPExcel->getActiveSheet()->setCellValue(‘A1‘, ‘测试‘);//填入副标题 ????????$objPHPExcel->getActiveSheet()->setCellValue(‘A2‘, ‘测试(导出日期：‘ . date(‘Y-m-d H:i:s‘, time()) . ‘)‘);//填入表头 ????????$objPHPExcel->getActiveSheet()->setCellValue(‘A3‘, ‘序号‘); ????????$objPHPExcel->getActiveSheet()->setCellValue(‘B3‘, ‘用户名‘); ????????$objPHPExcel->getActiveSheet()->setCellValue(‘C3‘, ‘密码‘); ????????$objPHPExcel->getActiveSheet()->setCellValue(‘D3‘, ‘邮箱‘); ????????$objPHPExcel->getActiveSheet()->setCellValue(‘E3‘, ‘手机号‘); ????????$objPHPExcel->getActiveSheet()->setCellValue(‘F3‘, ‘注册时间‘);//写数据到表格里面去 ????????foreach ($data as $key => $value) { ????????????$i = $key + 1;//表格是从1开始的 ????????????$objPHPExcel->getActiveSheet()->setCellValue(‘A‘ . ($i + 3), $value[‘id‘]); ????????????$objPHPExcel->getActiveSheet()->setCellValue(‘B‘ . ($i + 3), $value[‘username‘]); ????????????$objPHPExcel->getActiveSheet()->setCellValue(‘C‘ . ($i + 3), $value[‘password‘]); ????????????$objPHPExcel->getActiveSheet()->setCellValue(‘D‘ . ($i + 3), $value[‘email‘]); ????????????$objPHPExcel->getActiveSheet()->setCellValue(‘E‘ . ($i + 3), $value[‘mobile‘]); ????????????$objPHPExcel->getActiveSheet()->setCellValue(‘F‘ . ($i + 3), $value[‘create_time‘]); ????????}//下载这个表格，在浏览器输出 ????????$filename = ‘测试‘; ????????$outputFileName = $filename . ".xls"; ????????header("Content-Type: application/force-download"); ????????header("Content-Type: application/octet-stream"); ????????header("Content-Type: application/download"); ????????header(‘Content-Disposition:attachment;filename="‘ . $outputFileName . ‘"‘); ?//到文件////header(‘Content-Disposition:inline;filename="‘.$outputFileName.‘"‘); ?//到浏览器 ????????header("Content-Transfer-Encoding: binary"); ????????header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); ????????header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); ????????header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); ????????header("Pragma: no-cache"); ????????$objWriter->save(‘php://output‘); ????} ????public function importExcel(){ ????????//判断是否符合文件格式 ????????$allowedExts = array("xls", "xlsx", "csv"); ????????$temp = explode(".", $_FILES["myfile"]["name"]); ????????$extension = end($temp); ????// 获取文件后缀名 ????????$filePath = ‘‘; ????????if(!in_array($extension,$allowedExts)){ ????????????echo "文件格式不对";exit(); ????????} ????????if(is_uploaded_file($_FILES[‘myfile‘][‘tmp_name‘])){ ????????????$savePath= ‘./asset/upload/‘; ????????????$str =date(‘Ymdhis‘); ????????????$fileName=$str.‘.‘.pathinfo($_FILES[‘myfile‘][‘name‘],PATHINFO_EXTENSION); ????????????//移动到指定的目录 ????????????if(move_uploaded_file($_FILES[‘myfile‘][‘tmp_name‘],$savePath.$fileName)){ ????????????????echo "<script>alert(‘上传成功‘)</script>"; ????????????} ?????????????$this->load->library(‘PHPExcel‘); ????????????if (!file_exists($savePath.$fileName)) { ????????????????die(‘no file!‘); ????????????} ????????????$filePath="./asset/upload/".$fileName; ????????????$PHPReader = new PHPExcel_Reader_Excel2007(); ????????????if (!$PHPReader->canRead($filePath)) { ????????????????$PHPReader = new PHPExcel_Reader_Excel5(); ????????????????if (!$PHPReader->canRead($filePath)) { ????????????????????echo ‘no Excel‘; ????????????????????return; ????????????????} ????????????} ????????????$PHPExcel = $PHPReader->load($filePath); ????????????//读取excel文件中的第一个工作表 ????????????$sheet = $PHPExcel->getSheet(0); ???????????????//取得最大的列号 ????????????$allColumn = $sheet->getHighestColumn(); ????????????//取得最大的行号 ????????????$allRow = $sheet->getHighestRow(); ????????????for($currentRow=3;$currentRow<$allRow;$currentRow++){ ????????????????$name=$PHPExcel->getActiveSheet()->getCell(‘A‘.$currentRow)->getValue(); ????????????????$password=$PHPExcel->getActiveSheet()->getCell(‘B‘.$currentRow)->getValue(); ????????????????$email=$PHPExcel->getActiveSheet()->getCell(‘C‘.$currentRow)->getValue(); ????????????????$mobile=$PHPExcel->getActiveSheet()->getCell(‘D‘.$currentRow)->getValue(); ????????????????//插入数据库 ????????????????$res=$this->db->insert(‘user‘,[‘username‘=>$name,‘password‘=>$password,‘email‘=>$email,‘mobile‘=>$mobile,‘create_time‘=>date(‘Y-m-d H:i:s‘,time())]); ????????????} ????????????if($res){ ????????????????echo "导入成功"; ????????????}else{ ????????????????echo "导出失败"; ????????????} ????????} ????}}PHPExcel集成对数据导入和导出
原文地址：https://www.cnblogs.com/thatme/p/10203061.html