Excel导出
//功能：导出题库模板
public function get_contract_ex() { ob_get_clean(); header("Content-Typ:text/html;charset=utf-8"); vendor('Excel.PHPExcel'); vendor('Excel.PHPExcel.IOFactory'); //设定缓存模式） $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized; $cacheSettings = array(); \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); $objPHPExcel = new \PHPExcel(); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '题干');//设置列的值 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', '选项A');//设置列的值 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '选项B');//设置列的值 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '选项C');//设置列的值 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', '选项D');//设置列的值 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', '答案');//设置列的值 //设置居中 $objPHPExcel->getDefaultStyle() ->getAlignment() ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER) ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); /*设置字段宽度*/ $objPHPExcel->getActiveSheet(0)->getColumnDimension('A')->setWidth(18); $objPHPExcel->getActiveSheet(0)->getColumnDimension('B')->setWidth(18); $objPHPExcel->getActiveSheet(0)->getColumnDimension('C')->setWidth(18); $objPHPExcel->getActiveSheet(0)->getColumnDimension('D')->setWidth(18); $objPHPExcel->getActiveSheet(0)->getColumnDimension('E')->setWidth(18); $objPHPExcel->getActiveSheet(0)->getColumnDimension('F')->setWidth(18); /*行高*/ $objPHPExcel->getActiveSheet(0)->getRowDimension(1)->setRowHeight(30); $objPHPExcel->getActiveSheet(0)->getDefaultStyle()->getFont()->setName("微软雅黑");//设置默认字体大小和格式 $objPHPExcel->getActiveSheet(0)->getStyle('A1:F1')->getFont()->setBold(true); //字体加粗 //导出多条此处可加foreach循环--开始 $objPHPExcel->setActiveSheetIndex(0) //选择 //Excel的第A列，uid是你查出数组的键值，下面以此类推 ->setCellValue('A2', "中国的首都是") ->setCellValue('B2', "北京") ->setCellValue('C2', "上海") ->setCellValue('D2', "广州") ->setCellValue('E2', "深圳") ->setCellValue('F2', "A"); //导出多条此处可加foreach循环--结尾
$objPHPExcel->getActiveSheet(0)->setTitle("题库模板"); /*浏览器输出*/ // $filename = "题库导入模板"; $filename = iconv("utf-8", "gb2312", "题库导入模板"); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $filename . ".xlsx"); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); } Excel导入
html代码：
jquery代码：
$('#upload').click(function() { ajaxFileUpload(); }); function ajaxFileUpload() { $.ajaxFileUpload({ url: "<{:U('ServiceQuestions/upload_file')}>", //用于文件上传的服务器端请求地址 secureuri: false, //是否需要安全协议，一般设置为false fileElementId: 'file2', //文件上传域的ID dataType: 'text', //返回值类型 一般设置为json success: function(data, status) //服务器成功响应处理函数 { var str = data; var data_array = data.split(","); if (data_array[0] != 0) { layer.msg(data_array[1], { time: 1000 }); } else if (data_array[0] == 0) { //上传成功 layer.msg(data_array[1], { time: 1000 }); } }, error: function(data, status, e) //服务器响应失败处理函数 { alert("网络错误"); } }) return false; } 后端php代码： public function upload_file() { $excel_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'; $tmp_file = $_FILES ['file-2'] ['tmp_name']; $file_types = explode(".", $_FILES ['file-2'] ['name']); $upload_type = $_FILES['file-2']['type']; $file_type = $file_types [count($file_types) - 1]; if ($file_type != 'xlsx' and $upload_type != $excel_type) { $res['error'] = '1'; $res['msg'] = '不是Excel文件，请重新上传'; echo implode($res, ','); exit; } else { //上传文件 $upload_info = $this->uploadOne('tesques'); if (!$upload_info) { $res['error'] = '1'; $res['msg'] = '上传失败，请刷新重试!'; echo implode($res, ','); exit; } else { //导入题库 $result = $this->import_contract($upload_info); if ($result) { unlink('./Upload/' . $upload_info); $res['error'] = '0'; $res['msg'] = '上传成功！'; } else { $res['error'] = '1'; $res['msg'] = '上传失败，请确认数据格式是否正确!'; } echo implode($res, ','); exit; } } } /** * 单文件上传 * @return [type] [description] */ public function uploadOne($dir) { $upload = new \Think\Upload();// 实例化上传类 $upload->maxSize = 3145728;// 设置附件上传大小 $upload->exts = array('xlsx');// 设置附件上传类型 $upload->rootPath = './Upload/'; // 设置附件上传根目录 $upload->savePath = $dir . '/'; // 设置附件上传根目录 $upload->autoSub = TRUE; //是否自动生成二级子目录 $upload->subName = array('date', 'Ymd'); //子目录规则 // 上传单个文件 $info = $upload->uploadOne($_FILES['file-2']); if (!$info) {// 上传错误提示错误信息 return false; } else {// 上传成功 获取上传文件信息 return $info['savepath'] . $info['savename']; } } /** * Excel导入数据库表 * @return [type] [description] */ public function import_contract($filedir) { ob_get_clean(); vendor('Excel.PHPExcel'); vendor('Excel.PHPExcel.IOFactory'); vendor('Excel.PHPExcel.IReader'); $objReader = \PHPExcel_IOFactory::createReader('Excel2007');//use excel2007 for 2007 format $objPHPExcel = $objReader->load('./Upload/' . $filedir); //$filename指定的文件 $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得总行数 $highestColumn = $sheet->getHighestColumn(); // 取得总列数 // $sheetCount = $objPHPExcel->getSheetCount();//获取总的sheet数 $data = array(); $now_time = time(); //获取文件中的数据 // $datas = $objPHPExcel->getSheet(0)->toArray();//以数组的形式获取第一个sheet页的数据。与两个for循环的功能相同。
for ($rowIndex = 2; $rowIndex <= $highestRow; $rowIndex++) { //循环行 for ($colIndex = 'A'; $colIndex <= $highestColumn; $colIndex++) { //循环列 $addr = $colIndex . $rowIndex; $cell = $objPHPExcel->getActiveSheet()->getCell($addr)->getValue();//获取某行某列的值 if ($cell instanceof \PHPExcel_RichText) { //富文本转换字符串 $cell = $cell->__toString(); } $data[$rowIndex][] = $cell; } $data[$rowIndex][] = $now_time; $datas[] = $data[$rowIndex]; } // array_shift($datas); //删除第一个元素
if ($datas) { foreach ($datas as $key => $value) { $add_data['qustion_title'] = $value[0]; //题目内容 $add_data['state'] = $value[5]; // 正确答案 $add_data['create_time'] = $value[6]; //添加时间 $filter_datas = array_filter($add_data, array($this, 'filter_recontreact')); //判断数据是否有null值 if (count($filter_datas)) { $test = M('test_cont'); $test->startTrans(); $res = $test->add($add_data); if ($res) { $test->commit(); //选项A $add_data2['0']['test_id'] = $res; //所属题目ID $add_data2['0']['answer_name'] = $value[1]; // 答案内容 $add_data2['0']['create_time'] = $value[6]; //添加时间 $add_data2['0']['state'] = "A"; // //选项B $add_data2['1']['test_id'] = $res; //所属题目ID $add_data2['1']['answer_name'] = $value[2]; // 答案内容 $add_data2['1']['create_time'] = $value[6]; //添加时间 $add_data2['1']['state'] = "B"; //ABCD //选项C $add_data2['2']['test_id'] = $res; //所属题目ID $add_data2['2']['answer_name'] = $value[3]; // 答案内容 $add_data2['2']['create_time'] = $value[6]; //添加时间 $add_data2['2']['state'] = "C"; //ABCD //选项D $add_data2['3']['test_id'] = $res; //所属题目ID $add_data2['3']['answer_name'] = $value[4]; // 答案内容 $add_data2['3']['create_time'] = $value[6]; //添加时间 $add_data2['3']['state'] = "D"; //ABCD $filter_datas2 = array_filter($add_data2, array($this, 'filter_recontreact')); //判断数据是否有null值 if (count($filter_datas2)) { $res2 = M('test_answer')->addAll($add_data2); if (!$res2) { $test->rollback(); return false; } } else { return false; } } else { $test->rollback(); return false; } } else { return false; } } return 1; } else { return false; } }