PHPexcel导入、导出
程序员文章站
2022-05-17 14:39:16
...
//需要下载PHPExcel类
下载地址：http://www.onlinedown.net/soft/988679.htm
前端代码：
<html>
<head>
<title></title>
</head>
<meta charset="utf-8">
<body>
<form enctype="multipart/form-data" action="./go.php" method="post">
<table>
<td>请选择你要上传的文件</td>
<td><input type="file" name="excel"></td>
</tr>
<tr><td><input type="submit" value="上传文件" /></td></tr>
</table>
</form>
</body>
</html>
后端代码：
<?php
function addExcel()
{
//接收前台文件
$excel= $_FILES['excel'];
//重设置文件名
$filename = time().substr($excel['name'],stripos($excel['name'],'.'));
$path = './you/'.$filename;//设置移动路径
move_uploaded_file($excel['tmp_name'],$path);
//表用函数方法 返回数组
$arr= _readExcel($path);
return $arr;
}
//创建一个读取excel数据，可用于入库
function _readExcel($path)
{
//引用PHPexcel 类
include_once('./Classes/PHPExcel.php');
include_once('./Classes/PHPExcel/IOFactory.php');//静态类
$type = 'Excel2007';//设置为Excel5代表支持2003或以下版本，Excel2007代表2007版
$xlsReader = PHPExcel_IOFactory::createReader($type);
$xlsReader->setReadDataOnly(true);
$xlsReader->setLoadSheetsOnly(true);
$Sheets = $xlsReader->load($path);
//开始读取上传到服务器中的Excel文件，返回一个二维数组
$dataArray = $Sheets->getSheet(0)->toArray();
return $dataArray;
}
//调用
if($_FILES) {
var_dump(addExcel());
unset($_FILES);
unset($_POST);
}
?>//Excel表格
//结果：
array(5) { [0]=> array(4) { [0]=> string(9) "用户名" [1]=> string(6) "密码" [2]=> string(6) "手机" [3]=> string(6) "地址" } [1]=> array(4) { [0]=> string(3) "you" [1]=> float(1234) [2]=> float(1234) [3]=> string(6) "重庆" } [2]=> array(4) { [0]=> string(5) "canye" [1]=> float(5678) [2]=> float(5678) [3]=> string(6) "广东" } [3]=> array(4) { [0]=> string(7) "yueyaer" [1]=> float(9101) [2]=> float(9101) [3]=> string(6) "辽宁" } [4]=> array(4) { [0]=> string(5) "fuxia" [1]=> float(1213) [2]=> float(1213) [3]=> string(6) "贵州" } }
//导出
后端代码：
function excel(){
$arr=pdo_fetchall('select id,openid from '.tablename('member').' limit 1,30000');
$array=array('id','openid');
$this->toExcel($arr,'会员',$array);
}
//$list 要导出的数组
//$filename 导出文件名
//$indexKey 导出的值，数组形式
function toExcel($list,$filename,$indexKey,$startRow=1,$excel2007=true){
//文件引入
require_once IA_ROOT . '/framework/library/phpexcel/PHPExcel.php';
require_once IA_ROOT .'/framework/library/phpexcel/PHPExcel/Writer/Excel2007.php';
ob_end_clean();
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');
}//结果