thinkphp5整合excel导入导出
<?php
namespace app\index\controller;
use think\Controller;
use PHPExcel_IOFactory;
use PHPExcel;
use think\Db;
class Index extends Controller
{
public function index(){
return view();
}
public function out(){
$path = dirname(__FILE__);
vendor("PHPExcel.PHPExcel");
vendor("PHPExcel.PHPExcel.Writer.Excel5");
vendor("PHPExcel.PHPExcel.Writer.Excel2007");
vendor("PHPExcel.PHPExcel.IOFactory");
$objPHPExcel = new \PHPExcel();
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
$sql = db('info')->select();
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'ID编号')
->setCellValue('B1', '用户名')
->setCellValue('C1', '性别')
->setCellValue('D1', '地址');
/*--------------开始从数据库提取信息插入Excel表中------------------*/
//$i=2; //定义一个i变量，目的是在循环输出数据是控制行数
$count = count($sql); //计算有多少条数据
//echo $count;
//die;
for ($i = 2; $i <= $count+1; $i++) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $sql[$i-2]['id']);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $sql[$i-2]['name']);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sql[$i-2]['gender']);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $sql[$i-2]['address']);
}
/*--------------下面是设置其他信息------------------*/
$objPHPExcel->getActiveSheet()->setTitle('信息'); //设置sheet的名称
$objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
$PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
header('Content-Disposition: attachment;filename="用户信息.xlsx"');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
}
public function in(){
vendor("PHPExcel.PHPExcel"); //方法一
$objPHPExcel = new \PHPExcel();
//获取表单上传文件
$file = request()->file('excel');
$info = $file->validate(['size'=>156780,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel');
if($info){
//获取文件名
$exclePath = $info->getSaveName();
//上传文件的地址
$file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $exclePath;
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
//加载文件内容,编码utf-8
$obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8');
echo "<pre>";
$excel_array = $obj_PHPExcel->getsheet(0)->toArray(); //转换为数组格式
array_shift($excel_array); //删除第一个数组(标题);
$data = [];
foreach ($excel_array as $k => $v) {
$data[$k]['name'] = $v['0'];
$data[$k]['gender'] = $v['1'];
$data[$k]['address'] = $v['2'];
}
//批量插入数据
$success = Db::name('info')->insertAll($data);
echo '数据添加成功';
}else{
// 上传失败获取错误信息
echo $file->getError();
}
}
}https://pan.baidu.com/s/1qXK6riGt0Q12U3fgXPbmLA[/url]
