php 结合PHPExcel插件实现导入和导出功能
准备工作:
1.下载PHPExcel的SDK,下载地址: https://github.com/PHPOffice/PHPExcel
2.将SDK解压之后的Class文件拷贝到自己的项目。
1.0：Excel文件的数据读取，
function ExcelToArray($filename){
header("Content-type: text/html; charset=utf-8");
require_once APP_ROOT."/extensions/phpexcel/PHPExcel.php";
//加载excel文件
$objPHPExcelReader = PHPExcel_IOFactory::load($filename);
$sheet = $objPHPExcelReader->getSheet(0); // 读取第一个工作表(编号从 0 开始)
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$arr = 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');
// 一次读取一列
$res_arr = array();
for ($row = 2; $row <= $highestRow; $row++) {
$row_arr = array();
for ($column = 0; $arr[$column] != 'F'; $column++) {
$val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
$row_arr[] = $val;
}
$res_arr[] = $row_arr;
}
return $res_arr;
}
1.1：或者
function ExcelToArray($filename){
header("Content-type: text/html; charset=utf-8");
require_once APP_ROOT."/extensions/phpexcel/PHPExcel.php";
//加载excel文件
$objPHPExcelReader = PHPExcel_IOFactory::load($filename);
$reader = $objPHPExcelReader->getWorksheetIterator();
//循环读取sheet
foreach($reader as $sheet) {
//读取表内容
$content = $sheet->getRowIterator();
//逐行处理
$res_arr = array();
foreach($content as $key => $items) {
$rows = $items->getRowIndex(); //行
$columns = $items->getCellIterator(); //列
$row_arr = array();
//确定从哪一行开始读取
if($rows < 2){
continue;
}
//逐列读取
foreach($columns as $head => $cell) {
//获取cell中数据
$data = $cell->getValue();
$row_arr[] = $data;
}
$res_arr[] = $row_arr;
}
}
return $res_arr;
}
以上两种方法均可读取Excel文件内容保存在一个多维数组中，然后就可以进行操作了。
2.0：Excel 数据导出（无模板）。
/**
* @param $datalist
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
* @throws PHPExcel_Writer_Exception
* 数据导出（无模板）
*/
public function export($datalist,$filename){
header("Content-type: text/html; charset=utf-8");
require_once APP_ROOT."/extensions/phpexcel/PHPExcel.php";
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
//设置标题
$objPHPExcel->getActiveSheet()->setTitle($filename);
//设置表头
$key1 = 1;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$key1, 'ID')
->setCellValue('B'.$key1, '用户名')
->setCellValue('C'.$key1, 'IP')
->setCellValue('D'.$key1, '添加时间')
->setCellValue('E'.$key1, '标题')
->setCellValue('F'.$key1, '开始日期')
->setCellValue('G'.$key1, '描述')
->setCellValue('H'.$key1, '评分')
->setCellValue('I'.$key1, '完成时间');
//设置样式：
$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFont()->setBold(true); //多个单元格
// $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->getColor()->setARGB('FFFF0000'); //设置颜色
// $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); //单个单元格
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); //列宽必须单个设置
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
//写入内容
foreach($datalist as $key =>$value){
$key1=$key+2;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$key1, $value['id'])
->setCellValue('B'.$key1, $value['username'])
->setCellValue('C'.$key1, $value['ip'])
->setCellValue('D'.$key1, date("Y-m-d H:i:s",$value['inputtime']))
->setCellValue('E'.$key1, clearhtml($value['item']))
->setCellValue('F'.$key1, date("Y-m-d H:i:s",$value['inputtime']))
->setCellValue('G'.$key1, clearhtml($value['miaoshu']))
->setCellValue('H'.$key1, $value['pingfen'])
->setCellValue('I'.$key1, date("Y-m-d H:i:s",$value['wctime']));
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'. $filename .'.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); // Excel2007
$objWriter->save('php://output');
exit;
}
2.1：Excel数据导出（用模板）
/**
* @param $list
* @param $filename
* @param array $indexKey
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
* @throws PHPExcel_Writer_Exception
* 导出数据（有模板）
*/
function exportExcel($list,$filename){
header("Content-type: text/html; charset=utf-8");
require_once APP_ROOT."/extensions/phpexcel/PHPExcel.php";
//$header_arr = 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');
//$objPHPExcel = new PHPExcel(); //初始化PHPExcel(),不使用模板
$template = './temp.xls'; //使用模板
$objPHPExcel = PHPExcel_IOFactory::load($template); //加载excel文件,设置模板
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //设置保存版本格式
//接下来就是写数据到表格里面去
$objActSheet = $objPHPExcel->getActiveSheet();
//设置标题
$objPHPExcel->getActiveSheet()->setTitle($filename);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); //列宽必须单个设置
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$i = 2;
foreach ($list as $key1=>$value) {
$key=$i+$key1;
$objActSheet->setCellValue('A'.$key, $value['id'])
->setCellValue('B'.$key, $value['username'])
->setCellValue('C'.$key, $value['ip'])
->setCellValue('D'.$key, date("Y-m-d H:i:s",$value['inputtime']))
->setCellValue('E'.$key, clearhtml($value['item']))
->setCellValue('F'.$key, date("Y-m-d H:i:s",$value['inputtime']))
->setCellValue('G'.$key, clearhtml($value['miaoshu']))
->setCellValue('H'.$key, $value['pingfen'])
->setCellValue('I'.$key, date("Y-m-d H:i:s",$value['wctime']));
//这里是设置单元格的内容
// $objActSheet->setCellValue($header_arr[$key].$i,$row[$value]);
}
// 1.保存至本地Excel表格
//$objWriter->save($filename.'.xls');
// 2.接下来当然是下载这个表格了，在浏览器输出就好了
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="'.$filename.'.xlsx"');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
}
Excel模板。
导出后的数据。
3.0：Excel 文档数据导入到数据库。
/**
* @param $filename
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
* 数据导入数据库
*/
public function ExcelImport($filename){
header("Content-type: text/html; charset=utf-8");
require_once APP_ROOT."/extensions/phpexcel/PHPExcel.php";
$objPHPExcel = PHPExcel_IOFactory::load($filename);
$objWorksheet = $objPHPExcel->getSheet(0);
$highestRow = $objWorksheet->getHighestRow(); // 取得总行数
$highestColumn = $objWorksheet->getHighestColumn(); // 取得总列数
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString ( $highestColumn );
$excelData = array ();
for($row = 2 ; $row <= $highestRow; $row++) { //数据从第二行开始读取
for($col = 0; $col < $highestColumnIndex; $col++) {
$excelData[$row-2][] = $objWorksheet->getCellByColumnAndRow( $col, $row )->getValue();
}
}
//p($excelData);die;
$i=0;
foreach ($excelData as $k=>$v){
$data=array(
'username'=>$v[1],
'ip'=>$v[2],
'inputtime'=>strtotime($v[3]),
'item'=>$v[4],
'miaoshu'=>$v[6],
'pingfen'=>$v[7],
'wctime'=>strtotime($v[8]),
'cid'=>11
);
$res=$this->model("form_1_houqin")->insert($data);
if($res){
$i++;
}
}
if($i==count($excelData)){
$arr=array("code"=>200);
}else{
$arr=array("code"=>100,'allnum'=>count($excelData),'oknum'=>$i);
}
return $arr;
}
3.1：接收前端上传的Excel文件。
/**
* @return array
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
* 接收上传文件
*/
public function FileuploadAction(){
$root_url = 'uploadfiles/pexcel/';
//p($_FILES);
if (!is_uploaded_file($_FILES['file']['tmp_name']))
return array('code'=>0,'msg'=>'文件错误');
$root_url.=date('Ymd').'/';
if (!is_dir($root_url)) {
mkdir($root_url,0777, true);
}
$ext = pathinfo($_FILES['file']['name']);
$num="txt_";
$pa=file_list::get_file_list($root_url);
$na=count($pa) + 1;
if ($na<10){
$name=$num.'000'.$na;
}elseif($na<100){
$name=$num.'00'.$na;
}elseif($na<1000){
$name=$num.'0'.$na;
}else{
$name=$num.$na;
}
$n=$root_url.$name.".".$ext['extension'];
$result=move_uploaded_file($_FILES['file']['tmp_name'],$n);
if ($result){
$rs=$this->excelImport($n); //执行插入操作
if ($rs['code']==200){
exit(json_encode(array('code'=>200,'msg'=>'导入成功')));
}else{
exit(json_encode(array('code'=>0,'msg'=>'导入数据失败')));
}
}else{
exit(json_encode(array('code'=>0,'msg'=>'上次文件失败')));
}
}
3.2：前端页面
3.3：前端代码。
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, minimum-scale=1.0, maximum-scale=1.0, user-scalable=0">
<title></title>
<script src="{SITE_THEME}dan/js/jquery.min.js" type="text/javascript" charset="utf-8"></script>
<script src="{SITE_THEME}dan/layui/layui.js" type="text/javascript" charset="utf-8"></script>
<link rel="stylesheet" type="text/css" href="{SITE_THEME}dan/layui/css/layui.css" />
</head>
<body>
<blockquote class="layui-elem-quote layui-text">
附件的上传类型： xlsx,xls
</blockquote>
<form class="layui-form" action="" lay-filter="example">
<div class="layui-col-md10 layui-col-md-offset1">
<div class="layui-form-item">
<label class="layui-form-label">导入数据：</label>
<div class="layui-upload layui-input-block" >
<button type="button" class="layui-btn layui-btn-normal" id="test3">选择文件</button>
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">文件地址：</label>
<div class="layui-upload layui-input-block" >
<input type="text" name="files" id="files" autocomplete="off" class="layui-input" readonly>
</div>
</div>
<div class="layui-form-item">
<div class="layui-input-block">
<button class="layui-btn" lay-submit="" lay-filter="demo1" >立即提交</button>
</div>
</div>
</div>
</form>
<script type="text/javascript">
layui.use(['form', 'element', 'layer', 'layedit','upload'], function() {
var element = layui.element;
var layer = layui.layer;
var form = layui.form;
var jq = layui.$;
var layedit=layui.layedit;
var upload=layui.upload;
form.render();
//指定允许上传的文件类型
upload.render({
elem: '#test3'
,url: "{url('crm/fileupload')}"
,accept: 'file' //普通文件
,exts: 'xls|xlsx'
, before: function (input) {
loading = layer.load(2, {
shade: [0.2, '#000'] //0.2透明度的白色背景
});
}
,done: function(res){
layer.close(loading);
if (res.code==200) {
layer.msg(res.msg, {icon: 1, time: 1000},function () {
jq("#files").val(res.path);
/* var index = parent.layer.getFrameIndex(window.name);
parent.layer.close(index);*/
});
}else {
layer.msg(res.msg, {icon: 2, time: 1000});
}
}
, error: function () {
layer.close(loading);
layer.msg('网络错误',{icon:2,time:1500});
}
});
//监听提交
form.on('submit(demo1)', function(data) {
loading = layer.load(2, {
shade: [0.2, '#000'] //0.2透明度的白色背景
});
var param = data.field;
//console.log(param);
jq.post(
"{url('crm/import')}",
param,
function(data) {
if(data.status == 1) {
layer.close(loading);
layer.msg(data.info, {
icon: 1,
time: 1500
}, function() {
var index = parent.layer.getFrameIndex(window.name);
parent.layer.close(index);
});
} else {
layer.close(loading);
layer.msg(data.info, {
icon: 2,
anim: 6,
time: 3000
});
}
}).error(function() {
layer.close(loading);
layer.msg('服务器错误', {
icon: 2,
anim: 6,
time: 1500
});
});
return false;
});
});
</script>
</body>
</html>除此之外表格还可以做很多的属性操作，有兴趣的可以去了解。