PHP 导出 和写入 Excel文件

13719654321

<?php
//  PHPExcel  需要下载  官网：http://www.codeplex.com/PHPExcel
header("Content-Type: text/html; charset=utf-8");
require_once './PHPExcel.php';
require_once './PHPExcel/IOFactory.php';
require_once './PHPExcel/Reader/Excel5.php';

/**
*读取Excel表格
*@param $filePath   Excel文件路径
*@param $field需要保存的字段array('id','username','password')
*@param $column读取Excel那列array('A','B','C')
*@examlpe
$filePath = '1.xls';
$field = array('id', 'username', 'password');
$column = array( 'A', 'B', 'C');
readExcel($filePath,$field,$column);
*/
function readExcel($filePath,$field,$column){
$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
$objPHPExcel = $objReader->load($filePath); //$filename可以是上传的文件，或者是指定的文件
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
for($j=1;$j<=$highestRow;$j++)
{
$colData = array();
$count = count($column);
for($i=0;$i<$count;$i++){
$colData[$field[$i]] = $objPHPExcel->getActiveSheet()->getCell($column[$i].$j)->getValue();//获取A列的值
}
$excelData[] = $colData;
}
return $excelData;
}


/**
*导出数据为excel表格
*@param $data    一个二维数组,结构如同从数据库查出来的数组
*@param $title   excel的第一行标题,一个数组,如果为空则没有标题
*@param $filename 下载的文件名
*@examlpe
exportexcel($arr,array('id','账户','密码','昵称'),'文件名!');
*/
function exportexcel($data=array(),$title=array(),$filename='report'){
header("Content-type:application/octet-stream");
header("Accept-Ranges:bytes");
header("Content-type:application/vnd.ms-excel");  
header("Content-Disposition:attachment;filename=".$filename.".xls");
header("Pragma: no-cache");
header("Expires: 0");
//导出xls 开始
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);
}
}
?>