最近用thinkphp5做excel导入导出，在网上发现代码各不相同，废了一些时间整合了一下，和网上大同小异，下面代码只要按照步骤粘贴复制，就能用，其余的数据库名和数据库中的字段自行更改。
1、https://github.com/PHPOffice/PHPExcel 请自行下载
2、下载好了把classes文件改名为PHPExcel
3、Talk is cheap, show me your code：
废话不多说，直接粘贴复制
use think\Controller;
use think\Loader;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Cell;
use PHPExcel_Writer_Excel5;
use PHPExcel_Writer_Excel2007;
核心操作方法：
public function excel(){
if (request()->isPost()) {Loader::import(‘PHPExcel.PHPExcel’);
Loader::import(‘PHPExcel.PHPExcel.PHPExcel_IOFactory’);
Loader::import(‘PHPExcel.PHPExcel.PHPExcel_Cell’);
//实例化PHPExcel
$objPHPExcel = new PHPExcel();
$file = request()->file(‘excel’);
if ($file) {
$file_types = explode(“.”, $_FILES [‘excel’] [‘name’]); // [“name”] => string(25) “excel文件名.xls”
$file_type = $file_types [count($file_types) – 1];//xls后缀
dump($file_type);
die;
$file_name = $file_types [count($file_types) – 2];//xls去后缀的文件名
/*判别是不是.xls文件，判别是不是excel文件*/
if (strtolower($file_type) != “xls” && strtolower($file_type) != “xlsx”) {
echo ‘不是Excel文件，重新上传’;
die;
}
$info = $file->rule(‘uniqid’)->move(ROOT_PATH . ‘public’ . DS . ‘excel’);//上传位置
$path = ROOT_PATH . ‘public’ . DS . ‘excel’ . DS;
$file_path = $path . $info->getSaveName();//上传后的EXCEL路径
//获取上传的excel表格的数据，形成数组
$re = $this->actionRead($file_path, ‘utf-8’);
array_splice($re, 1, 0);
unset($re[0]);
/*将数组的键改为自定义名称*/
$keys = array(‘id’, ‘username’, ‘nickname’, ‘password’, ‘salt’, ‘avatar’, ’email’, ‘loginfailure’, ‘logintime’, ‘createtime’, ‘updatetime’,’token’,’status’);
foreach ($re as $i => $vals) {
$re[$i] = array_combine($keys, $vals);
}
//遍历数组写入数据库
for ($i = 1; $i < count($re); $i++) {
$data = $re[$i];
$res = db(‘admin’)->insert($data);
}
}
}
}
读取excel表数据，转为数组结果：
public function actionRead($filename, $encode = ‘utf-8’)
{
$objReader = PHPExcel_IOFactory::createReader(‘Excel5’);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($filename);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData = array();
for($row = 1; $row <= $highestRow; $row++)
{
for ($col = 0; $col < $highestColumnIndex; $col++)
{
$excelData[$row][]=(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $excelData;
}
做一个上传表单：
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<title>Title</title>
</head>
<body>
<form action=”{:url(‘admin/index/excel’)}” enctype=”multipart/form-data” method=”post”>
<input type=”file” name=”excel” /> <br>
<input type=”submit” value=”上传” />
</form>
</body>
</html>
使用PHPExcel类库导出.xls数据表：
public function out(){
$path = dirname(__FILE__); //找到当前脚本所在路径
Loader::import(“PHPExcel.PHPExcel”);
Loader::import(“PHPExcel.PHPExcel.Writer.IWriter”);
Loader::import(“PHPExcel.PHPExcel.Writer.Abstract”);
Loader::import(“PHPExcel.PHPExcel.Writer.Excel5”);
Loader::import(“PHPExcel.PHPExcel.Writer.Excel2007”);
Loader::import(“PHPExcel.PHPExcel.IOFactory”);
$objPHPExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
// 实例化完了之后就先把数据库里面的数据查出来
$sql = db(‘admin’)->select();
// 设置表头信息
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue(‘A1’, ‘id’)
->setCellValue(‘B1’, ‘username’)
->setCellValue(‘C1’, ‘nickname’)
->setCellValue(‘D1’, ‘password’)
->setCellValue(‘E1’, ‘salt’)
->setCellValue(‘F1’, ‘avatar’)
->setCellValue(‘G1′, ’email’)
->setCellValue(‘H1’, ‘loginfailure’)
->setCellValue(‘I1’, ‘logintime’)
->setCellValue(‘J1’, ‘createtime’)
->setCellValue(‘K1’, ‘updatetime’)
->setCellValue(‘L1’, ‘token’)
->setCellValue(‘M1’, ‘status’);
$i=2; //定义一个i变量，目的是在循环输出数据是控制行数
$count = count($sql); //计算有多少条数据
for ($i = 2; $i <= $count+1; $i++) {
$objPHPExcel->getActiveSheet()->setCellValue(‘A’ . $i, $sql[$i-2][‘id’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘B’ . $i, $sql[$i-2][‘username’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘C’ . $i, $sql[$i-2][‘nickname’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘D’ . $i, $sql[$i-2][‘password’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘E’ . $i, $sql[$i-2][‘salt’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘F’ . $i, $sql[$i-2][‘avatar’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘G’ . $i, $sql[$i-2][’email’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘H’ . $i, $sql[$i-2][‘loginfailure’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘I’ . $i, $sql[$i-2][‘logintime’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘J’ . $i, $sql[$i-2][‘createtime’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘K’ . $i, $sql[$i-2][‘updatetime’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘L’ . $i, $sql[$i-2][‘token’]);
$objPHPExcel->getActiveSheet()->setCellValue(‘M’ . $i, $sql[$i-2][‘status’]);
}
$objPHPExcel->getActiveSheet()->setTitle(‘admin’); //设置sheet的名称
$objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’); //通过PHPExcel_IOFactory的写函数将上面数据写出来
$PHPWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel,”Excel2007″);
header(‘Content-Disposition: attachment;filename=”admin.xlsx”‘);
header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
$PHPWriter->save(“php://output”); //表示在$path路径下面生成demo.xlsx文件}
更多PHP导入导出Excel方法请参考这篇文章：
评论