网站首页php
PHP导出导入excel类
发布时间：2016-03-24 01:47:22编辑：阅读（5999）
下载PHPExcel, From链接： http://phpexcel.codeplex.com/releases/view/119187
Excel导出数据组合函数：
/**
*生成execl内容
*@param $expTitle 标题
*@param $expCellName 列名
*@param $expTableData 数据
*@param $objExcel PHPExcel 对象
*
*Return $objExcel PHPExcel 对象
**/
function makeExcel($expTitle,$expCellName,$expTableData,$objExcel){
$objProps = $objExcel->getProperties();
$objProps->setCreator($expTitle);
$objProps->setTitle($expTitle);
$objProps->setSubject($expTitle);
$objExcel->setActiveSheetIndex(0);
$objActSheet = $objExcel->getActiveSheet();
$objActSheet->setTitle($expTitle);
$cellNum = count($expCellName);
$dataNum = count($expTableData);
$cellName = 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','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT',
'AU','AV','AW','AX','AY','AZ');
$objExcel->getActiveSheet()->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
$objExcel->getActiveSheet()->setCellValue('A1', $expTitle.' 生成时间:'.date('Y-m-d H:i:s'));//合并单元格生成标题
for($i=0;$i<$cellNum;$i++){
$objExcel->getActiveSheet()->setCellValueExplicit($cellName[$i].'2', $expCellName[$i][1],
PHPExcel_Cell_DataType::TYPE_STRING);
}
for($i=0;$i<$dataNum;$i++){
for($j=0;$j<$cellNum;$j++){
$objExcel->getActiveSheet()->setCellValueExplicit($cellName[$j].($i+3),
$expTableData[$i][$expCellName[$j][0]], PHPExcel_Cell_DataType::TYPE_STRING);
}
}
return $objExcel;
}3.在程序中调用导出excel：
public function actionexportExcel(){
require_once('Excel/PHPExcel.php');
require_once('Excel/PHPExcel/Writer/Excel5.php');
//require('Excel/PHPExcel/Writer/Excel2007.php'); //使用Excel2007导出.xlsx文件
$objExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel5($objExcel);
//$objWriter =new PHPExcel_Writer_Excel2007($objExcel);
$cellname = array(
array('id','ID'),
array('name','用户名'),
array('phone','手机'),
array('email','邮箱'),
array('addtime','注册时间'),
);
$dbo = new Db();
$dataset= $dbo->getAll("SELECT id,name,phone,email,additm from FROM w_user LIMIT 0,100");
$objExcel = makeExcel('导出人员', $cellname, $dataset, $objExcel);
$outputFilePath = "Upload/";
$outputFileName = "Members" . date('YmdHis') . ".xls";
//$outputFileName = "Members" . date('YmdHis') . ".xlsx"; //使用Excel2007导出.xlsx文件
$objWriter->save($outputFilePath . $outputFileName);
/**下载文件**/
Header("Content-type: application/octet-stream");
Header("Accept-Ranges: bytes");
Header("Accept-Length: ".filesize($outputFilePath . $outputFileName));
Header("Content-Disposition: attachment; filename=" . $outputFileName);
$file = fopen($outputFilePath . $outputFileName,"r");
echo fread($file,filesize($outputFilePath . $outputFileName));
fclose($file);
}4.excel导入数据组合函数
function excelToArray($filename, $encode='utf-8'){
require_once('Excel/PHPExcel.php');
$objExcel = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader('Excel5'); //or Excel2007
$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 = 2;$row<=$highestRow;$row++) {
for($col=0; $col<$highestColumnIndex; $col++) {
$excelData[$row][] = strval($objWorksheet->getCellByColumnAndRow($col, $row)->getValue());
}
}
return $excelData;
}
或者：
function excelToArray($path) {
$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;
}5.在程序中处理上传excel文件，导入数据。
public function doimportjiaolianAction(){
if (! empty( $_FILES ['file_excel'] ['name'] ))
{
$tmp_file = $_FILES ['file_stu']['tmp_name'];
$file_types = explode ( ".", $_FILES ['file_stu']['name'] );
$file_type = $file_types [count ( $file_types ) - 1];
/*判别是不是.xls或者.xlsx文件，判别是不是excel文件*/
if (strtolower ( $file_type ) != "xls" && strtolower ( $file_type ) != "xlsx")
{
js_msg( '不是Excel文件，重新上传', 'history.back()' );
}
/*设置上传路径*/
$inputFilePath = $config['uploadpath'] . '/Excel/';
$inputFileName = "Order" . date('YmdHis') . $file_type;
/*是否上传成功*/
if (! copy ($tmp_file, $inputFilePath . $inputFileName ))
{
js_msg( '上传失败', 'history.back()' );
}
/*
* 对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中
* 注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入
*/
$res = $this->excelToArray( $inputFilePath . $inputFileName );
if(empty($res)){
js_msg('未找到数据', 'history.back()');
}
/*对生成的数组进行数据库的写入*/
foreach ( $res as $k => $v )
{
$name= $v[1];
$phone= $v[2];
$email= $v[3];
$addtime= $v[4];
$rows=array(
'name'=>$name,
'phone'=>$phone,
'email'=>$email,
'addtime'=>$addtime,
);
if (!(new Table('user'))->add($rows)) {
js_msg( '导入数据库失败' );
}
}
js_msg( '导入数据成功', '', '/user.html' );
}else{
js_msg( '上传未成功.', 'history.back()' );
}
}处理可以加上数据校验,及校验结果预览，确定无误后再入库。
评论