<?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 "导出失败";
}
}
}
}
手机扫一扫
移动阅读更方便
你可能感兴趣的文章