【PHP】导入、导出Excel表格（有使用PHPExcel和不使用的两个版本）

if (isset ( $export_out )) {
$wh = " 1=1 "; $filename = "报名支付成功.xls";
$j_status = 'ok';
$wh .=" and j_status = '".$j_status."'";
$wh .=" order by on_time desc ";
$export_website_out_arr = db_factory::query(sprintf("select * from %switkey_xian_join where ".$wh,TABLEPRE));
$bank_arr['alipayjs'] = "随便写什么";
$contents = "<table width=\"100%\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\" >
<caption height='25'> <b>".$filename."</b> </caption>
<tr>
<th width=\"60\" align=\"left\">编号</th>
<th width=\"60\" align=\"left\">订单号</th>
<th width=\"60\" align=\"left\">姓名</th>
<th width=\"60\" align=\"left\">电话</th>
<th width=\"100\" >金额 </th>
<th witdh=\"\">单位</th>
<th witdh=\"\">税号 </th>
<th witdh=\"\" >地址</th>
<th witdh=\"\" >时间</th>
</tr>
";
$k = 1;
foreach ( $export_website_out_arr as $key => $value ) {
$contents .= "<tr>
<td align=left>" . ($k ++) . "</td>
<td align=left>" . 'Activity-xian'.$value[j_id] . "</td>
<td align=left>" . $value[j_name] . "</td>
<td align=left>'" . $value[j_phone]. "</td>
<td align=left>￥ " . $value [j_amount] . "</td>
<td align=left>" . $value [j_company] . "</td>
<td align=left>'" . $value [j_number] . "</td>
<td align=left>'" . $value [j_address] . "</td>
<td align=left>" . date ( "Y-m-d", $value [on_time] ) . "</td>
</tr>";
}
$contents .= "</table>";
header ( 'Content-type: application/vnd.ms-execl' );
header ( 'Content-Disposition: attachment; filename=' . $filename );
echo $contents;
die ();
}

///////////////////////////////////////////////////////////////////////////////////
/*
* 导出excel，报名乡贤的表单 -xzz1130
*/
$filename = '第三届***报名名单';
// $indexKey = array('编号','用户名','性别','年龄');
// $list = array(array('编号'=>1,'用户名'=>'YQJ','性别'=>'男','年龄'=>24));
$indexKey = array('id','username','sex','age');
$list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>'22564351324564432333333'),array('id'=>2,'username'=>'KKL','sex'=>'女','age'=>'252342341312300123123'));
$indexKey2 = array('id'=>'编号','username'=>'用户名','sex'=>'性别','age'=>'年龄'); //标题栏
array_unshift($list, $indexKey2); //合并标题栏和数据，并将标题栏放在数据的最前面(这样导出的Excel才有标题)
exportExcel($list, $filename, $indexKey);///////////////////////////////////////////////////////////////
// // //给导出的数据强行添加序号1、2、3...100000 // // // //
// $filename = '第三届乡贤论坛暨第五届全国爱故乡大会报名名单';
// $indexKey = array('xuhao','id','username','sex','age','amount');
// $list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>'22564351324564432333333','amount'=>'232323222.65'),array('id'=>5,'username'=>'KKL','sex'=>'女','age'=>'252342341312300123123','amount'=>'1599222.68'));
// //给导出的数据强行添加序号1、2、3...100000
// foreach($list as $k=>$v){
// $list[$k]['xuhao'] = $k+1;
// }
// $indexKey2 = array('xuhao'=>'序号','id'=>'编号','username'=>'用户名','sex'=>'性别','age'=>'年龄','amount'=>'金额'); //标题栏
// array_unshift($list, $indexKey2); //合并标题栏和数据，并将标题栏放在数据的最前面(这样导出的Excel才有标题)
// exportExcel($list, $filename, $indexKey);
///////////////////////////////////////////////////////////////////封装的导出Excel的方法
/**
* 创建(导出)Excel数据表格
* @param array $list 要导出的数组格式的数据
* @param string $filename 导出的Excel表格数据表的文件名
* @param array $indexKey $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值)
* @param array $startRow 第一条数据在Excel表格中起始行
* @param [bool] $excel2007 是否生成Excel2007(.xlsx)以上兼容的数据表
* 比如: $indexKey与$list数组对应关系如下:
* $indexKey = array('id','username','sex','age');
* $list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24));
*/
function exportExcel($list,$filename,$indexKey,$startRow=1,$excel2007=false){
//文件引入
header('Content-type: text/html; charset=utf-8');
require_once './lib/excel/PHPExcel.php';
require_once './lib/excel/PHPExcel/Writer/Excel2007.php'; //TP3.2.X系统，使用vendor引入
//vendor('PHPExcel.PHPExcel');
//vendor('PHPExcel.PHPExcel.Writer.Excel2007'); if(empty($filename)) $filename = time();
if( !is_array($indexKey)) return false; $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');
//初始化PHPExcel()
$objPHPExcel = new PHPExcel(); //设置保存版本格式
if($excel2007){
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$filename = $filename.'.xlsx';
}else{
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$filename = $filename.'.xls';
} //接下来就是写数据到表格里面去
$objActSheet = $objPHPExcel->getActiveSheet();
//$startRow = 1;
foreach ($list as $row) {
foreach ($indexKey as $key => $value){
//这里是设置单元格的内容
$objActSheet->setCellValue($header_arr[$key].$startRow,$row[$value]);
}
$startRow++;
} // 下载这个表格，在浏览器输出
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.'');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
}------------ 再次，导入excel （需要使用phpexcel插件，同样适用于oneThink） 2018/05/17 新增 ----------------转载至 ： https://blog.csdn.net/yssong1028/article/details/75913317强烈建议 去上面那个链接 查看，超级详细，超级简单的示例，广大PHPer的福音 ！！！核心：<?php
namespace Home\Controller;
use Think\Controller;
use PHPExcel_IOFactory;
use PHPExcel;
use Behavior;
class IndexController extends Controller {
public function index(){
$this->display();
}
public function upload() {
ini_set('memory_limit','1024M');
if (!empty($_FILES)) {
$config = array(
'exts' => array('xlsx','xls'),
'maxSize' => 3145728000,
'rootPath' =>"./Public/",
'savePath' => 'Uploads/',
'subName' => array('date','Ymd'),
);
$upload = new \Think\Upload($config);
if (!$info = $upload->upload()) {
$this->error($upload->getError());
}
vendor("PHPExcel.PHPExcel");
$file_name=$upload->rootPath.$info['photo']['savepath'].$info['photo']['savename'];
$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式
if ($extension == 'xlsx') {
$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
} else if ($extension == 'xls'){
$objReader =\PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
}
$sheet =$objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();//取得总行数
$highestColumn =$sheet->getHighestColumn(); //取得总列数
D('pro_info')->execute('truncate table pro_info');
for ($i = 2; $i <= $highestRow; $i++) {
//看这里看这里,前面小写的a是表中的字段名，后面的大写A是excel中位置
$data['pId'] =$objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
$data['pName'] =$objPHPExcel->getActiveSheet()->getCell("B" .$i)->getValue();
$data['pPrice'] =$objPHPExcel->getActiveSheet()->getCell("C" .$i)->getValue();
$data['pCount'] = $objPHPExcel->getActiveSheet()->getCell("D". $i)->getValue();
//看这里看这里,这个位置写数据库中的表名D('pro_info')->add($data);
}
$this->success('导入成功!');
} else {
$this->error("请选择上传的文件");
}
}
}