php利用phpexcel插件实现数据的导入和导出（支持csvxlsxlsx格式和超过26个字段列）
一、导入（这其中show_msg和logFile是自定义函数）
/**
* 方法名 : excelToTable
* 作用 : 【私有】将excel数据导入数据表中
* @param1 ： file 用户上传的文件信息
* @param2 ： tableid 用来区别是哪张表，1-statistics_rawdata_pct，2-statistics_rawdata_apply，3-statistics_rawdata_auth，4-statistics_rawdata_valid
* @param3 ： month_number 导入的数据属于哪一期的，比如201510
* @param4 ： table_head 用来判断excel表格是否有表头，默认有
* @date : 2015/11/26
* @author : dingling
*/
private function excelToTable($file,$tableid,$month_number,$table_head=1){
if(!empty($file["name"])){
$file_types = explode ( ".", $file["name"] );
$excel_type = array("xls","csv","xlsx");
//判断是不是excel文件
if (!in_array(strtolower(end($file_types)),$excel_type)){
$this->show_msg("不是Excel文件，重新上传","/search/patentStatistics/uploadRawdata");
}
//设置上传路径
$savePath = _WWW_ . "www/tmp/";
//以时间来命名上传的文件
$str = date ( "Ymdhis" );
$file_name = $str.".".end($file_types);
//是否上传成功
$tmp_file = $file["tmp_name"];
if (!copy($tmp_file,$savePath.$file_name)){
$this->show_msg("上传失败","/search/patentStatistics/uploadRawdata");
}
if($tableid=="1"){
$rawdata_obj = $this->rawdata_pctmodel;
}elseif($tableid=="2"){
$rawdata_obj = $this->rawdata_applymodel;
}elseif($tableid=="3"){
$rawdata_obj = $this->rawdata_authmodel;
}elseif($tableid=="4"){
$rawdata_obj = $this->rawdata_validmodel;
}else{
$this->show_msg("您要导入的数据表不存在！","/search/patentStatistics/uploadRawdata");
}
if($rawdata_obj)
$fields = $rawdata_obj->returnFields();
else
$this->show_msg("未能指定明确的表！","/search/patentStatistics/uploadRawdata");
//定义导入失败记录的文档
$logfile = $savePath.$str.".txt";
//读取excel，存成数组，该数组的key是从1开始
$res = $this->excelToArray($savePath.$file_name,end($file_types));
//echo 12321321;exit;
//如果有表头，则过滤掉第一行
if($table_head)
unset($res[1]);
//循环写入，不一次性写入，防止有错误的记录；错误记录会记录下第一个字段到txt文档中去
foreach($res as $k =>$v){
foreach($fields as $key=>$val){
if($v[$key]===null){
$v[$key] = "null";
}
$data[$val] = $v[$key];
}
//该字段比较特殊，必须导入表中都有该字段
$data["month_number"] = $month_number;
$result = $rawdata_obj->addSave($data);
unset($data);
if(!$result){
$this ->logFile($logfile,$v[0]);
}
}
if(file_get_contents($logfile))
return $logfile;
else
return true;
}
}
/**
* 方法名 : excelToArray
* 作用 : 【私有】将excel数据转换成数组
* @param1 ： filename excel文件名
* @param2 ： filetype excel格式（xls、xlsx、csv）
* @param3 ： encode 编码格式，默认utf8
* @return ： 返回2维数组，最小的key为1
* @date : 2015/11/26
* @author : dingling
*/
private function excelToArray($filename,$filetype,$encode="utf-8"){
if(strtolower($filetype)=="xls"){
$objReader = PHPExcel_IOFactory::createReader("Excel5");
}elseif(strtolower($filetype)=="xlsx"){
$objReader = PHPExcel_IOFactory::createReader("Excel2007");
}elseif(strtolower($filetype)=="csv"){
$objReader = PHPExcel_IOFactory::createReader("CSV");
}
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($filename);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData = array();
for ($row = 1; $row <= $highestRow; $row++) {
for ($col = 0; $col < $highestColumnIndex; $col++) {
$excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $excelData;
}二、导出（该功能支持页面上html中table表格直接导出）
1、前端代码
<!doctype html>
<html>
<head>
<title>专利数据检索平台2015年08月</title>
<meta charset="utf-8" />
<meta name="keywords" content="北京市知识产权信息服务平台" />
<meta name="description" content="专利数据检索平台 北京市知识产权信息服务平台" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<meta property="wb:webmaster" content="3c67ef6a26cfe34e" />
<link rel="apple-touch-icon-precomposed" href="" />
<meta name="baidu-site-verification" content="5fNm7bQabR" />
<meta http-equiv="X-UA-Compatible" content="IE=8" />
<script type="text/javascript" src="/js/placeholder.js"></script>
<script type="text/javascript" src="/js/jquery-1.8.3.min.js"></script>
<script type="text/javascript" src="/js/admin.js"></script>
<link href="/js/dtree/dtree.css" rel="stylesheet" type="text/css">
<script type="text/javascript" src="/js/dtree/dtree.js"></script>
<link href="/css/index.css" rel="stylesheet" type="text/css">
<link href="/css/navcss.css" rel="stylesheet" type="text/css">
<link href="/css/govnet/self.css" rel="stylesheet" type="text/css">
<!-- <link href="/css/index.css" rel="stylesheet" type="text/css">-->
<link href="/css/common.css" rel="stylesheet" type="text/css">
</head>
<body class="body_index" >
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
<script type="text/javascript" src="/bootstrap/jquery.min.js"></script>
<link href="/bootstrap/css/bootstrap.min.css" rel="stylesheet">
<style>
.body_index {background:none !important;}
#mian{ width:95%; margin:20px auto;}
.table{ margin-top:20px;}
.table thead tr{text-align:center;}
.table th{ font-weight:bold; vertical-align:middle;}
.table td{ vertical-align:middle;}
.table input,.table textarea,.table select{margin-bottom:0px;}
.title h4{ border-bottom:2px solid #01AFD4; padding-bottom:8px;}
.title a{margin-top:-50px;}
.title{padding-bottom:10px;}
.handle_label{float:left;margin-top: 5px;margin-left: 15px; width:100px;text-align:right;}
.nav{width:auto !important;background:none !important;}
.table2{ margin-top:0px !important; margin-bottom:0px !important;}
.hide{display:none;}
</style><style>
.mainlevel DIV {width:1050px;}
#mian{ width:95%; margin:20px auto;}
.table{ margin-top:20px;}
.table thead tr{text-align:center;}
.table th{ font-weight:bold; vertical-align:middle;}
.table td{text-align:center;}
.table input,.table textarea,.table select{margin-bottom:0px;}
.red{color:red;}
</style>
<div id="mian">
<span id="export_data" style="float:right;cursor:pointer;">导出</span>
<table class="table table-bordered">
<tr><th colspan="12"><center>2015年08月北京地区各区县专利授权情况</center></th></tr>
<tr>
<th rowspan="2"><center>排序</center></th>
<th rowspan="2"><center>区县</center></th>
<th rowspan="2"><center>当月累计</center></th>
<th rowspan="2"><center>发明</center></th>
<th rowspan="2"><center>实用新型</center></th>
<th rowspan="2"><center>外观设计</center></th>
<th colspan="6"><center>三种专利申请中</center></th>
</tr>
<tr>
<th><center>个人</center></th>
<th><center>职务小计</center></th>
<th><center>大专院校</center></th>
<th><center>科研单位</center></th>
<th><center>企业</center></th>
<th><center>机关团体</center></th>
</tr>
<tr>
<td><center><b></b></center></td>
<td><center><b>总计</b></center></td>
<td>6814</td>
<td>2088</td>
<td>3763</td>
<td>963</td>
<td>828</td>
<td>5986</td>
<td>412</td>
<td>536</td>
<td>4920</td>
<td>118</td>
</tr>
<tr>
<td><center><b>1</b></center></td>
<td><center><b>海淀区</b></center></td>
<td>2157</td>
<td>823</td>
<td>1033</td>
<td>301</td>
<td>246</td>
<td>1911</td>
<td>264</td>
<td>337</td>
<td>1229</td>
<td>81</td>
</tr>
<tr>
<td><center><b>2</b></center></td>
<td><center><b>朝阳区</b></center></td>
<td>1181</td>
<td>569</td>
<td>407</td>
<td>205</td>
<td>192</td>
<td>989</td>
<td>64</td>
<td>92</td>
<td>825</td>
<td>8</td>
</tr>
<tr>
<td><center><b>3</b></center></td>
<td><center><b>西城区</b></center></td>
<td>1095</td>
<td>259</td>
<td>758</td>
<td>78</td>
<td>51</td>
<td>1044</td>
<td>10</td>
<td>19</td>
<td>1001</td>
<td>14</td>
</tr>
<tr>
<td><center><b>4</b></center></td>
<td><center><b>昌平区</b></center></td>
<td>416</td>
<td>78</td>
<td>225</td>
<td>113</td>
<td>55</td>
<td>361</td>
<td>34</td>
<td>5</td>
<td>318</td>
<td>4</td>
</tr>
<tr>
<td><center><b>5</b></center></td>
<td><center><b>东城区</b></center></td>
<td>393</td>
<td>100</td>
<td>262</td>
<td>31</td>
<td>37</td>
<td>356</td>
<td>0</td>
<td>9</td>
<td>340</td>
<td>7</td>
</tr>
<tr>
<td><center><b>6</b></center></td>
<td><center><b>丰台区</b></center></td>
<td>388</td>
<td>84</td>
<td>264</td>
<td>40</td>
<td>99</td>
<td>289</td>
<td>16</td>
<td>62</td>
<td>209</td>
<td>2</td>
</tr>
<tr>
<td><center><b>7</b></center></td>
<td><center><b>大兴区</b></center></td>
<td>387</td>
<td>84</td>
<td>254</td>
<td>49</td>
<td>55</td>
<td>332</td>
<td>16</td>
<td>1</td>
<td>314</td>
<td>1</td>
</tr>
<tr>
<td><center><b>8</b></center></td>
<td><center><b>顺义区</b></center></td>
<td>304</td>
<td>21</td>
<td>228</td>
<td>55</td>
<td>19</td>
<td>285</td>
<td>0</td>
<td>0</td>
<td>285</td>
<td>0</td>
</tr>
<tr>
<td><center><b>9</b></center></td>
<td><center><b>通州区</b></center></td>
<td>164</td>
<td>18</td>
<td>109</td>
<td>37</td>
<td>29</td>
<td>135</td>
<td>6</td>
<td>2</td>
<td>127</td>
<td>0</td>
</tr>
<tr>
<td><center><b>10</b></center></td>
<td><center><b>石景山区</b></center></td>
<td>147</td>
<td>33</td>
<td>82</td>
<td>32</td>
<td>10</td>
<td>137</td>
<td>0</td>
<td>8</td>
<td>128</td>
<td>1</td>
</tr>
<tr>
<td><center><b>11</b></center></td>
<td><center><b>房山区</b></center></td>
<td>66</td>
<td>7</td>
<td>52</td>
<td>7</td>
<td>14</td>
<td>52</td>
<td>2</td>
<td>1</td>
<td>49</td>
<td>0</td>
</tr>
<tr>
<td><center><b>12</b></center></td>
<td><center><b>怀柔区</b></center></td>
<td>58</td>
<td>7</td>
<td>44</td>
<td>7</td>
<td>9</td>
<td>49</td>
<td>0</td>
<td>0</td>
<td>49</td>
<td>0</td>
</tr>
<tr>
<td><center><b>13</b></center></td>
<td><center><b>密云区</b></center></td>
<td>23</td>
<td>1</td>
<td>18</td>
<td>4</td>
<td>6</td>
<td>17</td>
<td>0</td>
<td>0</td>
<td>17</td>
<td>0</td>
</tr>
<tr>
<td><center><b>14</b></center></td>
<td><center><b>平谷区</b></center></td>
<td>21</td>
<td>1</td>
<td>17</td>
<td>3</td>
<td>2</td>
<td>19</td>
<td>0</td>
<td>0</td>
<td>19</td>
<td>0</td>
</tr>
<tr>
<td><center><b>15</b></center></td>
<td><center><b>门头沟区</b></center></td>
<td>10</td>
<td>2</td>
<td>7</td>
<td>1</td>
<td>4</td>
<td>6</td>
<td>0</td>
<td>0</td>
<td>6</td>
<td>0</td>
</tr>
<tr>
<td><center><b>16</b></center></td>
<td><center><b>延庆区</b></center></td>
<td>3</td>
<td>0</td>
<td>3</td>
<td>0</td>
<td>0</td>
<td>3</td>
<td>0</td>
<td>0</td>
<td>3</td>
<td>0</td>
</tr>
<tr>
<td><center><b>17</b></center></td>
<td><center><b>其他</b></center></td>
<td>1</td>
<td>1</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>1</td>
<td>0</td>
<td>0</td>
<td>1</td>
<td>0</td>
</tr>
<tr>
<td class="red"><center><b>18</b></center></td>
<td class="red"><center><b>北京经济技术开发区</b></center></td>
<td class="red">191</td>
<td class="red">67</td>
<td class="red">112</td>
<td class="red">12</td>
<td class="red">14</td>
<td class="red">177</td>
<td class="red">0</td>
<td class="red">1</td>
<td class="red">176</td>
<td class="red">0</td>
</tr>
<tr><td colspan="12"><span style="text-align:right;">注：开发区数据不计算在合计总数中<span></td></tr>
</table>
</div>
<script>
window.onload=function(){
$("#export_data").click(function(){
//获取标题
var title = $(".table").find("tr").eq(0).find("th").html();
//获取总行数
var line_num = $(".table tr").length;
//获取最大列数
var max_col_num = $(".table").find("tr").eq(0).find("th").attr("colspan");
//获取各行列数 数组col_num_arr 字符串col_num_str
var col_num_arr = new Array();
//获取各行td或者th的个数
var total_td_num = 0;
for(var i=0;i<line_num;i++){
col_num_arr[i] = $(".table").find("tr").eq(i).find("td").length;
if(col_num_arr[i]=="")
col_num_arr[i] = $(".table").find("tr").eq(i).find("th").length;
total_td_num = total_td_num + col_num_arr[i];
}
var col_num_str = col_num_arr.join(",");//alert(col_num_str);
//获取各行th的个数
var th_col_num_arr = [];
for(var i=0;i<line_num;i++){
var j = $(".table").find("tr").eq(i).find("th").length;
if(j != 0)
th_col_num_arr[i] = j;
}
var th_col_num_str = th_col_num_arr.join(",");//alert(th_col_num_str);
//获取所有th值
var th_data_arr = [];
var th_num = $(".table").find("th").length;
for(var i=0;i<th_num;i++){
var val = $(".table").find("th").eq(i).html();
if(typeof(val)!="undefined"&&typeof(val)!="")
th_data_arr[i] = val;
}
var th_data_str = th_data_arr.join("@@@");//alert(th_data_arr[0]);alert(th_data_str);
//获取所有td值
var td_data_arr = [];
var td_num = $(".table").find("td").length;
for(var i=0;i<td_num;i++){
var val = $(".table").find("td").eq(i).html();
if(typeof(val)!="undefined")
td_data_arr[i] = val;
}
var td_data_str = td_data_arr.join("@@@");
$.ajax({
url:"/search/patentStatistics/exportData",
type:"POST",
data:{title:title,max_col_num:max_col_num,th_col_num_str:th_col_num_str,th_data_str:th_data_str,td_data_str:td_data_str},
dataType:"json",
success:function(data){
//console.log(data.href);
window.open(data.href);
}
})
});
}
</script>
<script src="/bootstrap/js/bootstrap.min.js"></script>
</body>
</html>
</body>
</html>
2、后端代码
/**
* 方法名 : exportData
* 作用 : 导出数据
* @date 2015/03/26
* @author dingling
* @return excel文件路径
*/
public function exportDataAction(){
$title = strip_tags($_POST["title"]);//excel第一行标题
$max_col_num = $_POST["max_col_num"];//最大列数
$th_num_arr = explode(",",trim($_POST["th_col_num_str"]));//取th各行的列数
array_shift($th_num_arr);//删除首行th
$head_line = count($th_num_arr);//列标题的th行数
$th_data = explode("@@@",trim($_POST["th_data_str"]));
array_shift($th_data);//删除首行th(就是第一行标题)
$th_data2 = array();
foreach($th_data as $k=>$v){
$th_data2[] = strip_tags($v);
}
//将一维数组（值）按照另一个数组（个数）拆分成二维数组
foreach($th_num_arr as $k=>$v){
foreach($th_data2 as $key=>$val){
if($key<$v)
$temp[] = $val;
}
$th_data2 = array_values(array_diff($th_data2,$temp));
$head[] = $temp;
unset($temp);
}
//补空
foreach($head as $k=>$v){
if(count($head[$k])<$max_col_num){
for($i=0;$i<$max_col_num-count($head[$k]);$i++){
$temp[] = "";
}
if($k==0)
$head2[] = array_merge($head[$k],$temp);
else
$head2[] = array_merge($temp,$head[$k]);
}
unset($temp);
}
//获取所有td的值
$td_data = explode("@@@",trim($_POST["td_data_str"]));
$data = array();
foreach($td_data as $k=>$v){
$data[$k/$max_col_num][$k%$max_col_num] = strip_tags($v);
}
$path = $this -> getExcel($title,$title,$head2,$data);
echo json_encode(array("href"=>$path)) ;
}
/**
* 方法名: getExcel
* 作用 : 将数据转换为Excel格式
* @date 2015/03/26
* @author dingling
* @param1 文件名
* @param2 sheet名称
* @param3 字段名(必须二维数组)
* @param4 数据
* @return excel文件
*/
private function getExcel($fileName,$fileName2,$headArr,$data){
//对数据进行检验
if(empty($data) || !is_array($data)){
die("数据必须为数组");
}
//检查文件名
if(empty($fileName)){
exit;
}
//组装文件名
$date = date("Y_m_d",time());
$fileName .= "_{$date}.xls";
error_reporting(E_ALL);
ini_set("display_errors", TRUE);
ini_set("display_startup_errors", TRUE);
date_default_timezone_set("PRC");
if (PHP_SAPI == "cli")
die("只能通过浏览器运行");
//创建PHPExcel对象
$objPHPExcel = new PHPExcel();
$objProps = $objPHPExcel->getProperties();
//设置表名称
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue("A1", $fileName2);
//设置表头
for($i=0;$i<count($headArr);$i++){
$line_num = 2;
$line_num += $i;
$key = ord("A");//A--65
$key2 = ord("@");//@--64
foreach($headArr[$i] as $v){
if($key>ord("Z")){
$key2 += 1;
$key = ord("A");
$colum = chr($key2).chr($key);//超过26个字母时才会启用 dingling 20150626
}else{
if($key2>=ord("A")){
$colum = chr($key2).chr($key);
}else{
$colum = chr($key);
}
}
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.$line_num,$v);
$key += 1;
}
}
$column = count($headArr)+2;
$objActSheet = $objPHPExcel->getActiveSheet();
foreach($data as $v){ //行写入
$span = ord("A");
$span2 = ord("@");
foreach($headArr[0] as $key=>$val){
if($span>ord("Z")){
$span2 += 1;
$span = ord("A");
$j = chr($span2).chr($span);//超过26个字母时才会启用 dingling 20150626
}else{
if($span2>=ord("A")){
$j = chr($span2).chr($span);
}else{
$j = chr($span);
}
}
$objActSheet->setCellValue($j.$column, strip_tags($v[$key]));
$span++;
}
$column++;
}
$fileName = iconv("utf-8", "gb2312", $fileName);
$objPHPExcel->getActiveSheet()->setTitle($fileName2);// 重命名表
$objPHPExcel->setActiveSheetIndex(0);// 设置活动单指数到第一个表,所以Excel打开这是第一个表
ob_end_clean();//清除缓冲区,避免乱码
// Redirect output to a client’s web browser (Excel5)
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment;filename="$fileName"");
header("Cache-Control: max-age=0");
// 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
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
//$objWriter->save("php://output"); //文件通过浏览器下载
//指定存放路径
$savePath = _WWW_ . "www/tmp/";
$file = time().".xls";
$objWriter->save($savePath.$file); //将文件存放到指定目录
return "/tmp/".$file;
}声明：该文观点仅代表作者本人，牛骨文系教育信息发布平台，牛骨文仅提供信息存储空间服务。
- 上一篇： csv格式导出，身份证号码显示不全
- 下一篇： laravel excel循环导出
