正文
thinkphp-PHP实现Excel导入 导出功能
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
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代码：
<button id="upload" type="button"> 导入数据</button> 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;
}
}
