PHP的Excel导出与导入

编写背景（TP框架+数据库 dyg表 id user pwd ）

从数据库导出到Excel表格：

public function add()
{
$objExcel = new PHPExcel;
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objActSheet = $objExcel->getActiveSheet(0);
$objActSheet->setTitle('测试');
$objActSheet->setCellValue('A1', 'id');
$objActSheet->setCellValue('B1', '账号');
$objActSheet->setCellValue('C1', '密码');

$baseRow = 2; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
$explame_data = Db::name('dyg')->select();
// var_dump($explame_data);die();
foreach ($explame_data as & $lottery) {

}
// halt($explame_data);

foreach ($explame_data as $key => $value){
$i = $baseRow + $key;


// var_dump($value['user']);die();

$objExcel->getActiveSheet()->setCellValue('A' . $i, $value['id']);
$objExcel->getActiveSheet()->setCellValue('B' . $i, $value['user']);
$objExcel->getActiveSheet()->setCellValue('C' . $i, $value['pwd']);

}
$objExcel->setActiveSheetIndex(0);
//4、输出
$objExcel->setActiveSheetIndex();
header('Content-Type: applicationnd.ms-excel');
// $filename = date('YmdHis');$filename
header("Content-Disposition: attachment;filename=".'1'.".xlsx");
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}

从Excel表格导入到数据库：

前端：

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title></title>
<link rel="stylesheet" href="">
</head>
<body>
<br>
<!--
上传文件三要素
post请求
enctype
文件最大 2M
-->
<form action="" method="post" enctype="multipart/form-data">
点击上传:<input type="file" name="excel">
<br/>
<br/>
<br/>
<button>提交</button>
</form>
</body>
</html>

后端：

public function index()
{
// return '<a href="' . u('add') . '">导出数据</a>';
if ( request()->isPost() ) {
// $res = upload()->save('file');
$file = request()->file('excel');
$filename = $file->getRealpath();
// $filename = $res['data']['full_path'];
//设置excel格式
$reader = PHPExcel_IOFactory::createReader('Excel5');
//载入excel文件
$excel = $reader->load($filename);
// halt($data);
// var_dump($excel);die;
//读取第一张表
$sheet = $excel->getSheet(0);
//获取总行数
$row_num = $sheet->getHighestRow();
//获取总列数
$col_num = $sheet->getHighestColumn();
$data = []; //数组形式获取表格数据
for ($col = 'A'; $col <= $col_num; $col++) {
for ($row = 2; $row <= $row_num; $row++) {
$data[$row - 2][$sheet->getCell($col . 1)->getValue()] = $sheet->getCell($col . $row)->getValue();
}
}
// var_dump($data);die;
foreach($data as $v){
// Db::name('cs001')->where('id',$v['id'])
// ->where('user',$v['user'])
// ->setInc('pwd',$v['pwd']);
// echo Db::getLastSql();die;
dump(Db::name('cs001')->insert($v));
}
}