<?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 "导出失败";

             }

         }

     }

}

最新文章

1. LogStash配置、使用（三）
2. IOS 设置导航栏全局样式
3. LoRaWAN协议(六)--OTAA KEY生成过程
4. 编译QtAV工程库
5. ShareSDKForANE 打包笔记
6. c#.net 访问SQL SERVER 时提示：尝试读取或写入受保护的内存。这通常指示其他内存已损坏
7. 基于adt-bundle的Android开发环境搭建
8. ListView的优化
9. lombk在IDEA中报ClassNotFoundException错误
10. ARKit 初体验
11. python appium 操作app
12. Ajax之路
13. 2015-2016机器人操作系统（ROS）及其应用暑期学校资料汇总 ROS Summer School 持续更新
14. MT4 取K线
15. Confluence 6 指定日志选项和已知问题
16. 迭代器模块 itertools
17. [leetcode]8. String to Integer (atoi)字符串转整数
18. Java中输出正则表达式匹配到的内容
19. Theano笔记
20. 中文字符utf-8编码原则

热门文章

1. RabbitMQ高级之如何保证消息可靠性？
2. 学习seo技术要不断地扩大思维和思路
3. 焦大：seo思维进化论（下）
4. 【Pod Terminating原因追踪系列之一】containerd中被漏掉的runc错误信息
5. Python 编程开发 实用经验和技巧
6. Activiti7 提交任务
7. SpringBoot整合MinIO
8. 部署zabbix监控服务器，部署主动监控
9. PicGo软件搭配gitee实现图床
10. 解Bug之路-串包Bug