Excel导入导出,源码直接奉上!
数据导入
/**
* @导入试题
*/
public function import(){
//引入EXCEL操作库
require_once( WEB_ROOT . '/Application/Common/excel/reader.php' );
//
$this->bcItemPush('导入问题');
//
//$this->assign('questionnaire', M('Questionnaires')->field('id,type,name,description')->find(I('get.questionnaire_id/d')));
$qid = I('get.questionnaire_id/d');
if (empty ($qid)) {
$this->error('问卷ID不存在,请重新导入!');
}
$tmp = $_FILES['upload_file']['tmp_name'];
//
if (empty ($tmp)) {
$this->error('请选择要导入的EXCEL(.xls)文件!');
}
switch ($_FILES['upload_file']['type']){
case "application/kset" :
break;
case "application/excel" :
break;
case "application/vnd.ms-excel" :
break;
case "application/msexcel" :
break;
case "application/octet-stream" :
break;
default:
$flag = 1;
}
if($flag ==1){
$this->error('目前只支持EXCEL(.xls)格式文件!!!');
}
$file_name = WEB_ROOT . "/Public/Upload/Qid_{$qid}_".time().".xls";
if (move_uploaded_file($tmp, $file_name)) {
$xls = new \Spreadsheet_Excel_Reader();
$xls->setOutputEncoding('utf-8');
$xls->read($file_name);
$i=2;$j=0;$k=0;
$len = $xls->sheets[0]['numRows'];
while( $i <= $len){
$name = trim($xls->sheets[0]['cells'][$i][2]);
$type = intval($xls->sheets[0]['cells'][$i][3]);
$right = trim($xls->sheets[0]['cells'][$i][4]);
$type_str = $type == 1 ? 'radio' : 'checkbox';
$options = [];
if( trim($xls->sheets[0]['cells'][$i][5]) ){
$options[0] = [
'type' => $type_str,
'text' => trim($xls->sheets[0]['cells'][$i][5])
];
}
if( trim($xls->sheets[0]['cells'][$i][6]) ){
$options[1] = [
'type' => $type_str,
'text' => trim($xls->sheets[0]['cells'][$i][6])
];
}
if( trim($xls->sheets[0]['cells'][$i][7]) ){
$options[2] = [
'type' => $type_str,
'text' => trim($xls->sheets[0]['cells'][$i][7])
];
}
if( trim($xls->sheets[0]['cells'][$i][8]) ){
$options[3] = [
'type' => $type_str,
'text' => trim($xls->sheets[0]['cells'][$i][8])
];
}
if( trim($xls->sheets[0]['cells'][$i][9]) ){
$options[4] = [
'type' => $type_str,
'text' => trim($xls->sheets[0]['cells'][$i][9])
];
}
if( trim($xls->sheets[0]['cells'][$i][10]) ){
$options[5] = [
'type' => $type_str,
'text' => trim($xls->sheets[0]['cells'][$i][10])
];
}
if( M('Questions')->where(['questionnaire_id' => $qid,'name' => $name])->find() ){
$k ++;
}else{
$data['name'] = $name;
$data['options'] = json_encode($options);
$data['standard'] = str_ireplace(['A','B','C','D','E','F'],[0,1,2,3,4,5],str_replace(',',',',$right));
$data['score'] = 1;
$data['questionnaire_id'] = $qid;
$data['sort'] = $i;
M('Questions')->data( $data )->add();
$j ++;
}
$i++;
}
$this->success('成功导入数据'.$j.'条!剔除重复数据'.$k.'条!');
}
}
数据导出
//导出题目
public function export(){
//引入EXCEL操作库
require_once( WEB_ROOT . '/Application/Common/excel/reader.php' );
//
$this->bcItemPush('导出问题');
//
//$this->assign('questionnaire', M('Questionnaires')->field('id,type,name,description')->find(I('get.questionnaire_id/d')));
$qid = I('get.questionnaire_id/d');
if (empty ($qid)) {
$this->error('问卷ID不存在,请重新导出!');
}
$list = M('Questions')->where(['questionnaire_id' => $qid])->order('sort')->select();
//
$max_choice = 0;
foreach ($list as $k => $v){
$options = json_decode($v['options'],true);
$type = isset( $options[0]['type'] ) && $options[0]['type'] == 'radio' ? '单选题' : '多选题';
$data[$k] = [
'id' => $v['id'],
'name' => $v['name'],
'type' => $type,
'standard' => str_ireplace([0,1,2,3,4,5],['A','B','C','D','E','F'],$v['standard'])
];
//
$choice = 'A';
foreach ($options as $key => $val){
$max_choice = $key > $max_choice ? $key : $max_choice;
$data[$k][$choice] = $val['text'];
$choice ++;
}
}
$head = array("ID","题目","类型","正确答案");
$hChoice = 'A';
for($i = 0;$i<=$max_choice;$i++){
$head[] = '选项' . $hChoice;
$hChoice ++;
}
$this->export_excel($data,$head,time());
exit();
}
//导出EXCEL
private function export_excel($data=array(),$title=array(),$filename='report'){
header("Content-Type: text/csv");
header("Content-Disposition:attachment;filename=".$filename.".csv");
if (!empty($title)){
foreach ($title as $k => $v) {
$title[$k]=iconv("UTF-8", "GBK",$v);
}
$title= implode(",", $title);
echo "$title\n";
}
if (!empty($data)){
foreach($data as $key=>$val){
foreach ($val as $ck => $cv) {
$data[$key][$ck]=iconv("UTF-8", "GBK", $cv);
}
$data[$key]=implode(",", $data[$key]);
}
echo implode("\n",$data);
}
}