追加方法:
/**
* [readExcel 往Excel表插入数据]
* @param [array] $data [数据]
* @param [string] $filePath [文件路径]
* @param [array] $column [表格列]
* @return [type] [description]
*/
public function readExcel($data=array(),$filePath,$column){
//获取后缀名
$extension = strtolower( pathinfo($filePath, PATHINFO_EXTENSION) );
//判断
if ($extension =='xlsx') {
$objReader = PHPExcel_IOFactory::createReader('Excel2007');//use excel2007 for 2007 format
} else if ($extension =='xls') {
//$objReader = new PHPExcel_Reader_Excel5();//use excel2007 for 2007 format
$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
} else if ($extension=='csv') {
//还没有测试过
$objReader = new PHPExcel_Reader_CSV();
//默认输入字符集
$objReader->setInputEncoding('GBK');
//默认的分隔符
$objReader->setDelimiter(',');
}
//加载新文件
$objPHPExcel = $objReader->load($filePath);
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
//从第几行追加
$h = 2;
//循环数据
for($j = 0; $j <= count($data); $j++){
//循环数据内有多少个
for ($i=0; $i < count($data[$j]); $i++) {
//按照表格第几列追加内容
$objPHPExcel->getActiveSheet()->setCellValue("$column[$i]"."$h",$data[$j][$i]); //填充文字
}
//表格行+1
$h++;
}
$obj_writer = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007'); //生成文件
$obj_writer->save($filePath); //保存文件
//返回文件路径
return $filePath;
}
使用方法:
/**
* [packExcels 追加数据到Excel表格]
* @param array $data [数据]
* @param string $fileName [名称]
* @return [type] [description]
*/
public function packExcels($data=array(),$fileName='report'){
//引入PHPExcel累
require_once ROOT_PATH.'plugins/PHPExcel/Classes/PHPExcel.php';
require_once ROOT_PATH.'plugins/PHPExcel/Classes/PHPExcel/IOFactory.php';
require_once ROOT_PATH.'plugins/PHPExcel/Classes/PHPExcel/Reader/Excel5.php';
//定义一个文件模板
$path = ROOT_PATH . 'public/user.xlsx';
//存储文件新地址
$fileWjj = ROOT_PATH . 'public/user_excel/'.date('Ymd').'/';
//文件名称拼接后缀
$fileName = $fileName.'.xlsx';
//判断文件夹是否存在,没有则创建
if(!is_dir($fileWjj)){
//去除左右的斜杆 / ,再分割出数组
$arr = explode('/',trim($fileWjj,"/"));
//定义空变量
$a = '';
//循环数组
foreach ($arr as $k => $v) {
//拼接路径
$a .= $v.'/';
//判断如果当前的路径不存在,则创建
if(!is_dir($a)){
@mkdir($a);
}
}
}
//拼接路径
$file = $fileWjj.$fileName;
//将文件模板复制到新地址
copy($path,$file);
//标题
//$title = array('编号','姓名','性别','年龄');
//数据
//$data = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24));
//表格列
$column = array('A','B','C');
return $this->readExcel($data,$file,$column);
}
下载方法:
/**
* [exportexcel 下载Excel]
* @param array $data [description]
* @param array $title [description]
* @param string $filename [description]
* @return [type] [description]
*/
public function exportexcel($data=array(),$title=array(),$filename="report.xlsx"){
header("Content-type:application/octet-stream");
header("Accept-Ranges:bytes");
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=".$filename);
header("Pragma:no-cache");
header("Expires:0");
if(!empty($title)){
foreach ($title as $k => $v) {
$title[$k] = iconv("UTF-8","GB2312",$v);
}
$title = implode("\t",$title);
echo "$title\n";
}
if(!empty($data)){
foreach ($data as $key => $val) {
foreach ($val as $ck => $cv) {
$data[$key][$ck] = iconv("UTF-8","GB2312",$cv);
}
$data[$key] = implode("\t",$data[$key]);
}
echo implode("\n",$data);
}
}
注意:
1、引入的PHPExcel文件路径必须改成你自己的,否则报错;
2、注意文件后缀名,xls / xlsx 这两个是有区别的,上面使用的是xlsx,如果使用xls的需要改正;