【温馨提示】源码包解压密码:www.youhutong.com
利用PHPExcel类库,实现PHP导出导入Excel文件!(案例教程源码)
需要注意的地方就是:
1、导出文件时,如果你的字段过多,可以自己再加(outExcel方法里加)
2、导入文件时:
1):同样的文件里含有的字段多可以自己加上。
2):上传文件时如果失败,请查看你的上传目录是否存在,或目录权限。
以下是源码:(也可点击在线演示查看效果)<?php
// +----------------------------------------------------------------------------------------
// | 利用PHPExcel类库,实现PHP导出导入Excel文件! Author:xiaochuan 28126649@qq.com
// +----------------------------------------------------------------------------------------
header("Content-type:text/html;charset=utf-8");
include "PHPExcel/PHPExcel.php";
$act = empty($_POST['act'])? '' : $_POST['act'] ;
if($act == 'out'){
outExcel();
}elseif($act == 'in'){
$data = addExcel();
echo '
';
echo '
以下是文件里的数据,得到这个数组后你就可以做你接下来的事了!
';print_r($data);
echo '
';}else{
EXIT('不能直接访问.');
}
###############################################################################################
################################ 导出数据为Excel文件方法 ##############################
###############################################################################################
function Excel($title,$nav,$data){
$fileName = $title;
$cellNum = count($nav);
$dataNum = count($data);
$objPHPExcel = new PHPExcel();
$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N');
# +----------------------------------------------------------------------
# | 设置Excel的属性
# +----------------------------------------------------------------------
// 创建人
$objPHPExcel->getProperties()->setCreator("youhutong.com");
// 最后修改人
$objPHPExcel->getProperties()->setLastModifiedBy("youhutong.com");
// 标题
$objPHPExcel->getProperties()->setTitle("Excel file export case test file");
// 题目
$objPHPExcel->getProperties()->setSubject("Excel file export case test file");
// 描述
$objPHPExcel->getProperties()->setDescription("More cases, more tutorials, all in youhutong.com!");
// 关键字
$objPHPExcel->getProperties()->setKeywords("Excel PHPExcel file export file");
// 种类
$objPHPExcel->getProperties()->setCategory("PHP Excel");
// 设置宽
for($i=0;$i
$objPHPExcel->getActiveSheet()->getColumnDimension($cellName{$i})->setWidth(35);
}
for($i = 1; $i <= $dataNum+2; $i++){
// 设置居中显示
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':'.$cellName[$cellNum-1].$i)
->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 设置垂直居中显示
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':'.$cellName[$cellNum-1].$i)
->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
// 设置每行的高
$objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(30);
// 设置字体
if($i == 1){
$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getFont()->setName('黑体');
$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getFont()->setBold(true);
}
// 设置字体
if($i == 2){
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':'.$cellName[$cellNum-1].$i)
->getFont()->setName('黑体');
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':'.$cellName[$cellNum-1].$i)
->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':'.$cellName[$cellNum-1].$i)
->getFont()->setBold(true);
}
// 设置单元格边框
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':'.$cellName[$cellNum-1].$i)
->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
// 设置自动换行
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':'.$cellName[$cellNum-1].$i)
->getAlignment()->setWrapText(true);
}
# +----------------------------------------------------------------------
# |
# +----------------------------------------------------------------------
//合并单元格 (title)
$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$cellName[$cellNum-1].'1');
// 内容标题
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $fileName);
// 内容字段
for($i=0;$i
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $nav[$i][1]);
}
// 内容赋值
for($i=0;$i
for($j=0;$j
$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $data[$i][$nav[$j][0]]);
}
}
header('pragma:public');
header("Content-Disposition:attachment;filename=$fileName.xls");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
function rand_str(){
$num = str_shuffle(str_repeat('0123456789',3));
return substr($num,0,9);
}
function outExcel(){
$title = "小川编程";
$nav = array(
array('name','用户名'),
array('phone','手机号码'),
array('qq','qq号码'),
array('email','邮箱')
);
$data = [
['name'=>'小川编程01', 'phone'=>'13045009111', 'qq'=>rand_str(), 'email'=>rand_str().'@qq.com'],
['name'=>'小川编程02', 'phone'=>'13045009112', 'qq'=>rand_str(), 'email'=>rand_str().'@qq.com'],
['name'=>'小川编程03', 'phone'=>'13045009113', 'qq'=>rand_str(), 'email'=>rand_str().'@qq.com'],
['name'=>'小川编程04', 'phone'=>'13045009114', 'qq'=>rand_str(), 'email'=>rand_str().'@qq.com'],
['name'=>'小川编程05', 'phone'=>'13045009115', 'qq'=>rand_str(), 'email'=>rand_str().'@qq.com'],
['name'=>'小川编程06', 'phone'=>'13045009116', 'qq'=>rand_str(), 'email'=>rand_str().'@qq.com'],
['name'=>'小川编程07', 'phone'=>'13045009117', 'qq'=>rand_str(), 'email'=>rand_str().'@qq.com'],
['name'=>'小川编程08', 'phone'=>'13045009118', 'qq'=>rand_str(), 'email'=>rand_str().'@qq.com'],
['name'=>'小川编程09', 'phone'=>'13045009119', 'qq'=>rand_str(), 'email'=>rand_str().'@qq.com'],
['name'=>'小川编程10', 'phone'=>'13045009120', 'qq'=>rand_str(), 'email'=>rand_str().'@qq.com'],
];
Excel($title,$nav,$data);
}
###############################################################################################
############################## 导入Excel文件方法,得到数据(数组) #####################
###############################################################################################
function addExcel(){
if (!empty($_FILES)) {
$tmp_file = $_FILES['excel']['tmp_name'];
if (!copy($tmp_file , './file/demo.xls')) return '文件上传失败';
//$objReader = PHPExcel_IOFactory::createReader('Excel5');
//$objPHPExcel = $objReader->load('./file/demo.xls',$encode='utf-8');
$file_types = explode ( ".", $_FILES ['excel'] ['name'] );
$file_type = $file_types [count ( $file_types ) - 1];
$file_type = strtolower($file_type);
if($file_type=='csv'){
$objReader = PHPExcel_IOFactory::createReader('CSV');
}else{
$objReader = PHPExcel_IOFactory::createReaderForFile('./file/demo.xls');
}
$objPHPExcel = $objReader->load('./file/demo.xls',$encode='utf-8');
$sheet = $objPHPExcel->getSheet(0);
$row = $sheet->getHighestRow(); // 取得总行数
//$column = $sheet->getHighestColumn(); // 取得总列数
for($i=3;$i<=$row;$i++){
$data[$i-3]['name'] = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();
$data[$i-3]['phone'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
$data[$i-3]['qq'] = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
$data[$i-3]['email'] = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
}
return $data;
}else{
return false;
}
}
###############################################################################################
###############################################################################################
###############################################################################################
?>
浏览器启用弹出窗口过滤功能,将无法跳转到下载页。在浏览器地址栏右边符号提示处点击允许就可以了!
【温馨提示】源码包解压密码:www.youhutong.com
郑重声明:
1、本站源码仅供个人学习研究和交流使用,请于下载后二十四小时内删除
2、本站大多资源来源于互联网、用户分享,仅供学习交流使用,本站不提供任何技术支持
3、本站联系方式Email:admin@youhutong.com ,收到邮件会第一时间处理。
4、如侵犯到任何版权问题,请立即告知本站(立即在线告知),本站将及时删除并致以最深的歉意