前段时间刚好有个项目需要用到，所以在这里记录一下，话不多说直接上代码
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\cell\Coordinate;
/**
* 提交EXECL表格带图片导入导入数据
*/
public function upload_field_excel_post()
{
$file_url = $this->request->param('file_url'); //文件路径
if (!empty($file_url)) {
$filePath = $_SERVER['DOCUMENT_ROOT'] . '/upload/' . $file_url;
//如果文件存在
if (file_exists($filePath)) { //检查文件是否存在
$exts = pathinfo($filePath, PATHINFO_EXTENSION); //文件类型
// 有Xls和Xlsx格式两种
if ($exts == 'xlsx') {
$objReader = IOFactory::createReader('Xlsx');
} else {
//$objReader = IOFactory::createReader('Xls');
$info = "请使用'Xlsx'格式文件进行带图片上传,否则图片上传失败！";
$this->error(['code' => 0, 'msg' => $info]);
}
$imageFilePath1 = root_path() . '/public/upload/';//图片保存目录
$imageFilePath3 = 'admin/images/' . date("Ymd") . '/';
$imageFilePath = $imageFilePath1 . $imageFilePath3;
if (!file_exists($imageFilePath)) {
mkdir("$imageFilePath", 0777, true);
}
$objSpreadsheet = $objReader->load($filePath);
$objWorksheet = $objSpreadsheet->getActiveSheet(0); //getSheet(0)
$highestRow = $objWorksheet->getHighestRow(); // 取得总行数
$data = $objWorksheet->toArray();
//$drawing 为 PhpOffice\PhpSpreadsheet\Worksheet\Drawing类的实例; 仅仅支持xlsx格式文件
foreach ($objWorksheet->getDrawingCollection() as $drawing) {
list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());
$imageFileName = $drawing->getIndexedFilename(); //获取文件名称
switch ($drawing->getExtension()) {
case 'jpg':
case 'jpeg':
// $imageFileName .= '.jpg';
$source = imagecreatefromjpeg($drawing->getPath());
imagejpeg($source, $imageFilePath . $imageFileName);
break;
case 'gif':
$imageFileName .= '.gif';
$source = imagecreatefromgif($drawing->getPath());
imagegif($source, $imageFilePath . $imageFileName);
break;
case 'png':
$imageFileName .= '.png';
$source = imagecreatefrompng($drawing->getPath());
imagepng($source, $imageFilePath . $imageFileName);
break;
}
$startColumn = $this->ABC2decimal($startColumn);
$data[$startRow - 1][$startColumn] = $imageFilePath3 . $imageFileName;
}
//从第二行开始
for ($i = 1; $i <= $highestRow - 1; $i++) {
$add_data[$i]['name'] = $data[$i][0];
$add_data[$i]['image'] = $data[$i][1];
}
$success_count = Db::name('test')->insertAll($add_data);
//unlink($filePath); //删除文件
//判断导入成功数量
if ($success_count == $highestRow - 1) {
$info = '导入成功！本次成功导入数量：' . $success_count . '条';
$this->success($info);
} else {
$info = '导入成功！本次成功导入字段数量：' . $success_count . '条,无效数据或已重复上传' . ($highestRow - 1) - $success_count . '条,与目标数不符！';
$this->success($info);
}
} else {
$info = "文件不存在,文件上传失败！";
$this->error(['code' => 0, 'msg' => $info]);
}
} else {
$info = "请上传文件！";
$this->error(['code' => 0, 'msg' => $info]);
}
}
/**
* 字母序列化为数字
*/
public function ABC2decimal($abc)
{
$ten = 0;
$len = strlen($abc);
for ($i = 1; $i <= $len; $i++) {
$char = substr($abc, 0 - $i, 1);//反向获取单个字符
$int = ord($char);
$ten += ($int - 65) * pow(26, $i - 1);
}
return $ten;
}
/**
* 下载数据EXECL表格导出
*/
public function export()
{
$newExcel = new Spreadsheet(); //创建一个新的excel文档
$objSheet = $newExcel->getActiveSheet(); //获取当前操作sheet的对象
//设置宽度为true,不然太窄了
$newExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);
$newExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
//获取数据库的数据。
$List = Db::name('user')->select();
$objSheet->setTitle('班级表'); //设置当前sheet的标题
//设置第一栏的标题
$objSheet->setCellValue('A1', 'ID')
->setCellValue('B1', '图片');
foreach ($List as $k => $val) {
$k = $k + 2;
$objSheet->setCellValue('A' . $k, $val['id']); //数据行
// 获取本地文件夹路径
$dir = $_SERVER['DOCUMENT_ROOT'] . '/upload/';
$file_info = pathinfo($val['avatar']);
// 过滤非文件类型
if (!empty($file_info['basename'])) {
$basename = $file_info['basename'];
// 进行检测文件是否存在
is_dir($dir) OR mkdir($dir, 0777, true);
file_put_contents($dir . $basename, $val['avatar']);
// 引入操作图片类
$drawing[$k] = new Drawing();
$drawing[$k]->setName('GoodsCode');
$drawing[$k]->setDescription('GoodsCode');
$drawing[$k]->setPath($dir . $val['avatar']);
$drawing[$k]->setWidth(80);
$drawing[$k]->setHeight(80);
$drawing[$k]->setCoordinates('B' . $k);
$drawing[$k]->setOffsetX(10);
$drawing[$k]->setOffsetY(10);
$drawing[$k]->setWorksheet($newExcel->getActiveSheet());
}
// 每行高度设置
$objSheet->getRowDimension($k)->setRowHeight(80);
}
$this->downloadExcel($newExcel, '表1', 'Xls');
}
/**
* 下载数据EXECL表格导出
* 'newExcel' => 'EXECL表格',
* 'filename' => '文件名称',
* 'format'=> 文件后缀,
*/
function downloadExcel($newExcel, $filename, $format)
{
// $format只能为 Xlsx 或 Xls
if ($format == 'Xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
} elseif ($format == 'Xls') {
header('Content-Type: application/vnd.ms-excel');
}
header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format));
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($newExcel, $format);
$objWriter->save('php://output');
//通过php保存在本地的时候需要用到
//$objWriter->save($dir.'/demo.xlsx');
//以下为需要用到IE时候设置
// If you're serving to IE 9, then the following may be needed
//header('Cache-Control: max-age=1');
//If you're serving to IE over SSL, then the following may be needed
//header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
//header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
//header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
//header('Pragma: public'); // HTTP/1.0
exit;
}
