相信大家经常会遇到需要使用PHP批量导入导出Excel表中的商品，虽说网上各种案例都有，这里我就写一下我自己的实现方法，给大家做一个参考。
1、导入Excel表中是商品
//引入相关PHPExcel处理类
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
require_once 'Classes/PHPExcel/Reader/Excel5.php';
$file_url=$_POST['file'];//获取文件路径这个必须是能够访问文件的路径
//下面那个Excel5和Excel7两种格式，请根据你的Excel的版本选择
$objReader=PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
$objPHPExcel=$objReader->load($file_url);//$file_url即Excel文件的路径
$sheet=$objPHPExcel->getSheet(0);//获取第一个工作表
$highestRow=$sheet->getHighestRow();//取得总行数
$highestColumn=$sheet->getHighestColumn(); //取得总列数
//循环读取excel文件,读取一条,插入一条
$highestColumn++;
for($j=2;$j<=$highestRow;$j++){//从第一行开始读取数据
$str='';
for($k='A';$k!=$highestColumn;$k++){
//从A列读取数据
//这种方法简单，但有不妥，以'\\'合并为数组，再分割\\为字段值插入到数据库,实测在excel中，如果某单元格的值包含了\\导入的数据会为空
$str.=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'\\';//读取单元格
}
//explode:函数把字符串分割为数组。根据自己的需求进行修改！
$strs=explode("\\",$str);
if($strs[0]!=''){
//这下面是SQL插入的语句，必须要根据你要插入的表来写
$sql="INSERT INTO `".$GLOBALS['G_DY']['db']['prefix']."article`(`title`,`tid`,`addtime`,`orders`) VALUES (
'{$strs[0]}',
'{$strs[1]}',
'{$strs[2]}',
'{$strs[3]}'
)";
syDB('article')->findSql($sql);//这里执行的是插入数据库操作
}
}
unlink($file_url); //删除上传的Excel文件2、批量导出数据库里的数据到Excel表中
//引入相关PHPExcel处理类
require_once 'Classes/PHPExcel.php';
$excel = new PHPExcel();
//Excel表格式--根据你要到处的字段来定
$letter = array('A','B','C','D','E','F','G','H','I');
//表头数组
$tableheader = array('ID','标题','缩略图','点击量','简介','关键词','是否审核','发布时间','内容');
//填充表头信息
for($i = 0;$i < count($tableheader);$i++) {
$excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");
}
//查询数据
$sql=" select id,title,litpic,hits,description,keywords,isshow,addtime,body from article a left join article_field b on(a.id=b.aid) ';
$data=syDB('article')->findSql($sql);//这个是系统的查询方法
//设置默认行宽
$excel->getActiveSheet()->getColumnDimension()->setWidth(30);
//设置单一行宽
$excel->getActiveSheet()->getColumnDimension('A')->setWidth(50);
for ($i = 2;$i <= count($data) + 1;$i++) {
$j = 0;
foreach ($data[$i - 2] as $key=>$value) {
//设置默认行高-除第一行表头
$excel->getActiveSheet()->getRowDimension($i)->setRowHeight(150);
$excel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");
$j++;
}
}
//创建Excel输入对象，这里指定生成Excel5
$write = new PHPExcel_Writer_Excel5($excel);
$excelname='Excel表名';//设置导出的Excel表名
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");;
header('Content-Disposition:attachment;filename="'.$excelname.'.xls"');
header("Content-Transfer-Encoding:binary");
$write->save('php://output');3、需要注意，如果是手机号或者银行账号，防止自动科学计数，可以使用如下方法：
假设变量$a为手机号，录入的时候两边添加\t，如："\t".$a."\t";
4、从Excel表中导入图片数据
$file = $this->frparam('file_data',1);
$filePath = substr($file,1,strlen($file)-1);
if(!file_exists($filePath)){
JsonReturn(['code'=>1,'msg'=>'文件未上传！']);
}
///Public/Home/201909031530.xlsx
//引入PHPExcel类
require_once APP_PATH.'FrPHP/Extend/phpexcel/PHPExcel.php';
require_once APP_PATH.'FrPHP/Extend/phpexcel/PHPExcel/IOFactory.php';
require_once APP_PATH.'FrPHP/Extend/phpexcel/PHPExcel/Reader/Excel2007.php';
$file_url=$filePath;
$objReader=\PHPExcel_IOFactory::createReader('Excel2007');//use excel2007 for 2007 format
$objPHPExcel=$objReader->load($file_url);//$file_url即Excel文件的路径
$sheet=$objPHPExcel->getSheet(0);//获取第一个工作表
$highestRow=$sheet->getHighestRow();//取得总行数
$highestColumn=$sheet->getHighestColumn(); //取得总列数
//$highestColumn = $sheet->getHighestColumn();
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); //总列数
/*取图片*/
$imgData=array();
$imageFilePath=APP_PATH.'Public/Home/excel/';//图片保存目录
$getDrawingCollection = $sheet->getDrawingCollection();
//获取excel中全部的图片文件，将各个单元格中的图片文件上传替换成图片路径
foreach ($sheet->getDrawingCollection() as $key =>$drawing) {
$xy = $drawing->getCoordinates(); //"B2"
$cell = $sheet->getCell($xy);//(2,2)
$path = $imageFilePath; //单元格中图片的保存路径
if ($drawing instanceof \PHPExcel_Worksheet_Drawing) { // 基于 xlsx
$filename = $drawing->getPath(); //文件
$path = $path . $drawing->getIndexedFilename(); //文件路径
copy($filename, $path); //copy(A,B) 函数拷贝文件 A->B。
//$cell->setValue($drawing->getIndexedFilename()); //将图片的单元格值替换为图片路径
$cell->setValue('/Public/Home/excel/'.$drawing->getIndexedFilename()); //将图片的单元格值替换为图片路径
} else if ($drawing instanceof \PHPExcel_Worksheet_MemoryDrawing) { //基于xls
$image = $drawing->getImageResource();
$renderingFunction = $drawing->getRenderingFunction();
switch ($renderingFunction) {
case \PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG:
$filename = $drawing->getIndexedFilename();
$path = $path . $drawing->getIndexedFilename();
imagejpeg($image, $path);
break;
case \PHPExcel_Worksheet_MemoryDrawing::RENDERING_GIF:
$filename = $drawing->getIndexedFilename();
$path = $path . $drawing->getIndexedFilename();
imagegif($image, $path);
break;
case \PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG:
$filename = $drawing->getIndexedFilename();
$path = $path . $drawing->getIndexedFilename();
imagegif($image, $path);
break;
case \PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT:
$filename = $drawing->getIndexedFilename();
$path = $path . $drawing->getIndexedFilename();
imagegif($image, $path);
break;
}
$cell->setValue($filename); //将图片的单元格值替换为图片路径
}
}
//循环读取excel文件,读取一条,插入一条
$highestColumnIndex++;
for($j=2;$j<=$highestRow;$j++){//从第一行开始读取数据
$strs=[];
// for($k='A';$k<=$highestColumn;$k++){ //从A列读取数据
for($k=0;$k<=$highestColumnIndex;$k++){ //从A列读取数据
//这种方法简单，但有不妥，以'\\'合并为数组，再分割\\为字段值插入到数据库,实测在excel中，如果某单元格的值包含了\\导入的数据会为空
// $str.=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'\\';//读取单元格
$strs[$k] = $sheet->getCellByColumnAndRow($k, $j)->getValue();
}
//explode:函数把字符串分割为数组。根据自己的需求进行修改！
// $strs=explode("\\",$str);
if($strs[0]!=''){
$w = [];
$w['title'] = trim($strs[0]);
$w['model'] = $strs[5];
$w['danwei'] = $strs[6];
$w['jixing'] = $strs[7];
$w['brand'] = $strs[8];
$w['description'] = $strs[9];
$w['tupian'] = $strs[10];
$w['litpic'] = $strs[11];
$w['num'] = $strs[12];
$w['beizhu'] = $strs[17];
$w['body'] = $strs[18];
$w['beizhu_file'] = $strs[19];
$w['jieguo'] = $strs[20];
$w['addtime'] = time();
$w['userid'] = $this->member['id'];
$w['company'] = $this->member['danwei'];
$w['code'] = strtoupper(date('YmdHis').mt_rand(1000,9999));
M('product')->add($w);
}
}
unlink($file_url); //删除excel文件5、从商品中批量导出图片数据到Excel表中
$sql = str_replace(['delete'],'',$_POST['sql']);
$lists = M('product')->findAll($sql);
if(!$lists){ Error('未找到任何数据！');}
$excelname=date('Y年m月d日-物资仓库数据表',time());
require_once APP_PATH.'FrPHP/Extend/phpexcel/PHPExcel.php';
//require_once APP_PATH.'FrPHP/Extend/phpexcel/PHPExcel/Writer/Excel2007.php';
$excel = new \PHPExcel();
$objDrawing = new \PHPExcel_Worksheet_Drawing();
//Excel表格式--根据你要到处的字段来定
$letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS');
//表头数组35
$tableheader = array('ID','二维码','随机码','物资名称','缩略图','是否新件','是否调拨','是否审批','当前状态','一级分类','二级分类','三级分类','规格型号','不含税单价/单位','数量','适用机型','初始厂牌','是否原品牌','用途描述','用途图片','生产日期','有效日期','储存(库-架-层)','调拨物资备注','合同号','供应商','发票编号','税率','合计','所属公司','库管员','采购员','部门主管','添加时间');
//填充表头信息
for($i = 0;$i < count($tableheader);$i++) {
$excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");
}
//查询数据
$data = [];
$level = M('fenlei')->findAll();
$levels = [];
foreach($level as $k=>$v){
$levels[$v['id']] = $v;
}
foreach ($lists as $k=>$v){
$data[$k]['id'] = $v['id'];
if(!file_exists('./Public/Home/'.$v['code'].'.png')){
$errorCorrectionLevel = "L";
$matrixPointSize = 6;
\QRcode::png($v['code'],'Public/Home/'.$v['code'].'.png', $errorCorrectionLevel, $matrixPointSize, 2);
}
$data[$k]['code_img'] ='/Public/Home/'.$v['code'].'.png';
$data[$k]['addtime'] = date('Y-m-d H:i:s',$v['addtime']);
}
for ($i = 2;$i <= count($data) + 1;$i++) {
$j = 0;
foreach ($data[$i - 2] as $key=>$value) {
//注意需要知道对应图片字段的字母，图片和文字要分开处理
if($letter[$j]!='E' && $letter[$j]!='T' && $letter[$j]!='B' ){
$excel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");
}else{
if(!empty($value) && file_exists('.'.$value)){
$image = '.'.$value;
$objDrawing = new \PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($image);
// 设置图片的宽度
$objDrawing->setHeight(50);
$objDrawing->setWidth(50);
$objDrawing->setCoordinates($letter[$j] . $i);
$objDrawing->setWorksheet($excel->getActiveSheet());
}
}
$excel->getActiveSheet()->getRowDimension($i)->setRowHeight(50);
$j++;
}
}
//创建Excel输入对象
$write = new \PHPExcel_Writer_Excel5($excel);
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");;
header('Content-Disposition:attachment;filename="'.$excelname.'.xls"');
header("Content-Transfer-Encoding:binary");
$write->save('php://output');
