本文目录一览：
- 1、thinkphp5.0 如何导入phpexcel插件，能详细些点吗
- 2、PHP如何导入导出Excel
- 3、thinkphp怎么用phpexcel导出数据到excel
- 4、thinkphp中phpexcel怎么实现导入导出
- 5、phpexcel导入导出excel
- 6、如何利用ThinkPHP框架实现Excel数据导入和导出
thinkphp5.0 如何导入phpexcel插件，能详细些点吗
下载phpexcel，将整个文件夹放到vendor文件夹里面，如下图
在控制器中的使用方法
vendor("PHPExcel.PHPExcel");
$objPHPExcel = new \PHPExcel();
$file_name = date('YmdHis').'.xls';
header("Content-Disposition:attachment; filename=".$file_name);
$objActSheet = $objPHPExcel-getActiveSheet()-setTitle('成绩单');
$objActSheet-SetCellValue('A1', 'ID');
$objActSheet-SetCellValue('B1', '名称');
$objActSheet-SetCellValue('C1', '分数');
$list = "";//数据列表
if($list){
foreach ($list as $k = $v){
$i = $k + 2;
$objActSheet-SetCellValue('A'.$i,''. $v['id']);
$objActSheet-SetCellValue('B'.$i,''. $v['name']);
$objActSheet-SetCellValue('C'.$i,''. $v['score']);
}
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter-save('php://output');//输出到浏览器
exit;
}
PHP如何导入导出Excel
导入导出都成，可以导出office2007格式，同时兼容2003。
下载下来的包中有文档和例子，大家可以自行研究。
抄段例子出来：
PHP代码
?php
/**
* PHPExcel
*
* Copyright (C) 2006 - 2007 PHPExcel
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel
* @copyright Copyright (c) 2006 - 2007 PHPExcel
* @license
* @version 1.5.0, 2007-10-23
*/
/** Error reporting */
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . ‘../Classes/’);
/** PHPExcel */
include ‘PHPExcel.php’;
/** PHPExcel_Writer_Excel2007 */
include ‘PHPExcel/Writer/Excel2007.php’;
// Create new PHPExcel object
echo date(’H:i:s’) . ” Create new PHPExcel object\n”;
$objPHPExcel = new PHPExcel();
// Set properties
echo date(’H:i:s’) . ” Set properties\n”;
$objPHPExcel-getProperties()-setCreator(”Maarten Balliauw”);
$objPHPExcel-getProperties()-setLastModifiedBy(”Maarten Balliauw”);
$objPHPExcel-getProperties()-setTitle(”Office 2007 XLSX Test Document”);
$objPHPExcel-getProperties()-setSubject(”Office 2007 XLSX Test Document”);
$objPHPExcel-getProperties()-setDescrīption(”Test document for Office 2007 XLSX, generated using PHP classes.”);
$objPHPExcel-getProperties()-setKeywords(”office 2007 openxml php”);
$objPHPExcel-getProperties()-setCategory(”Test result file”);
// Add some data
echo date(’H:i:s’) . ” Add some data\n”;
$objPHPExcel-setActiveSheetIndex(0);
$objPHPExcel-getActiveSheet()-setCellValue(’A1′, ‘Hello’);
$objPHPExcel-getActiveSheet()-setCellValue(’B2′, ‘world!’);
$objPHPExcel-getActiveSheet()-setCellValue(’C1′, ‘Hello’);
$objPHPExcel-getActiveSheet()-setCellValue(’D2′, ‘world!’);
// Rename sheet
echo date(’H:i:s’) . ” Rename sheet\n”;
$objPHPExcel-getActiveSheet()-setTitle(’Simple’);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel-setActiveSheetIndex(0);
// Save Excel 2007 file
echo date(’H:i:s’) . ” Write to Excel2007 format\n”;
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter-save(str_replace(’.php’, ‘.xlsx’, __FILE__));
// Echo done
echo date(’H:i:s’) . ” Done writing file.\r\n”;
thinkphp怎么用phpexcel导出数据到excel
（一）导入Excel
第一，在前台html页面进行上传文件：如：
复制代码 代码如下:
form method="post" action="php文件" enctype="multipart/form-data"
h3导入Excel表：/h3input type="file" name="file_stu" /
input type="submit" value="导入" /
/form
第二，在对应的php文件进行文件的处理
复制代码 代码如下:
if (! empty ( $_FILES ['file_stu'] ['name'] ))
{
$tmp_file = $_FILES ['file_stu'] ['tmp_name'];
$file_types = explode ( ".", $_FILES ['file_stu'] ['name'] );
$file_type = $file_types [count ( $file_types ) - 1];
/*判别是不是.xls文件，判别是不是excel文件*/
if (strtolower ( $file_type ) != "xls")
{
$this-error ( '不是Excel文件，重新上传' );
}
/*设置上传路径*/
$savePath = SITE_PATH . '/public/upfile/Excel/';
/*以时间来命名上传的文件*/
$str = date ( 'Ymdhis' );
$file_name = $str . "." . $file_type;
/*是否上传成功*/
if (! copy ( $tmp_file, $savePath . $file_name ))
{
$this-error ( '上传失败' );
}
/*
*对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中
注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入
*/
$res = Service ( 'ExcelToArray' )-read ( $savePath . $file_name );
/*
重要代码 解决Thinkphp M、D方法不能调用的问题
如果在thinkphp中遇到M 、D方法失效时就加入下面一句代码
*/
//spl_autoload_register ( array ('Think', 'autoload' ) );
/*对生成的数组进行数据库的写入*/
foreach ( $res as $k = $v )
{
if ($k != 0)
{
$data ['uid'] = $v [0];
$data ['password'] = sha1 ( '111111' );
$data ['email'] = $v [1];
$data ['uname'] = $v [3];
$data ['institute'] = $v [4];
$result = M ( 'user' )-add ( $data );
if (! $result)
{
$this-error ( '导入数据库失败' );
}
}
}
}
第三：ExcelToArrary类,用来引用phpExcel并处理Excel数据的
复制代码 代码如下:
class ExcelToArrary extends Service{
public function __construct() {
/*导入phpExcel核心类 注意 ：你的路径跟我不一样就不能直接复制*/
include_once('./Excel/PHPExcel.php');
}
/**
* 读取excel $filename 路径文件名 $encode 返回数据的编码 默认为utf8
*以下基本都不要修改
*/
public function read($filename,$encode='utf-8'){
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$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;
}
}
第四，以上就是导入的全部内容，phpExcel包附在最后。
（二）Excel的导出（相对于导入简单多了）
第一，先查出数据库里面要生成Excel的数据，如：
$data= M('User')-findAll(); //查出数据
$name='Excelfile'; //生成的Excel文件文件名
$res=service('ExcelToArrary')-push($data,$name);
第二，ExcelToArrary类,用来引用phpExcel并处理数据的
复制代码 代码如下:
class ExcelToArrary extends Service{
public function __construct() {
/*导入phpExcel核心类 注意 ：你的路径跟我不一样就不能直接复制*/
include_once('./Excel/PHPExcel.php');
}
/* 导出excel函数*/
public function push($data,$name='Excel'){
error_reporting(E_ALL);
date_default_timezone_set('Europe/London');
$objPHPExcel = new PHPExcel();
/*以下是一些设置 ，什么作者 标题啊之类的*/
$objPHPExcel-getProperties()-setCreator("转弯的阳光")
-setLastModifiedBy("转弯的阳光")
-setTitle("数据EXCEL导出")
-setSubject("数据EXCEL导出")
-setDescription("备份数据")
-setKeywords("excel")
-setCategory("result file");
/*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
foreach($data as $k = $v){
$num=$k+1;
$objPHPExcel-setActiveSheetIndex(0)
//Excel的第A列，uid是你查出数组的键值，下面以此类推
-setCellValue('A'.$num, $v['uid'])
-setCellValue('B'.$num, $v['email'])
-setCellValue('C'.$num, $v['password'])
}
$objPHPExcel-getActiveSheet()-setTitle('User');
$objPHPExcel-setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$name.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter-save('php://output');
exit;
}
第三，以上就是导出的全部内容，phpExcel包附在最后。
thinkphp中phpexcel怎么实现导入导出
首先，将PHPExcel文件放入到vendor目录下面： 备注：ThinkPHP3.1的路径是：./ThinkPHP/Extend/Vendor/；ThinkPHP3.2的路径是：./ThinkPHP/Library/Vendor/； 数据导出功能的实现： 编写导出为Excel的函数exportExcel： public function exportEx...
phpexcel导入导出excel
下载phpexcel
require '/api/PHPExcel.php';//加载phpexcel
$objPHPExcel = new PHPExcel();
$objPHPExcel-getProperties()-setCreator("Maarten Balliauw")
-setLastModifiedBy("Maarten Balliauw")
-setTitle("Office 2007 XLSX Test Document")
-setSubject("Office 2007 XLSX Test Document")
-setDescription("Document for Office 2007 XLSX, generated using PHP classes.")
-setKeywords("office 2007 openxml php")
-setCategory("Test result file");
$objPHPExcel-setActiveSheetIndex(0);
$objPHPExcel-getDefaultStyle()-getFont()-setName( '微软雅黑');
$objPHPExcel-getDefaultStyle()-getFont()-setSize(14);
$objPHPExcel-getActiveSheet()-getStyle("A1:L1")-getFont()-getColor()-setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel-getActiveSheet()-getStyle("A1:L1")-getFont()-setBold(TRUE);
$objPHPExcel-getActiveSheet()-getStyle('A1:L1')-getFill()-setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel-getActiveSheet()-getStyle('A1:L1')-getFill()-getStartColor()-setARGB('FF6FA0EC');
$ths=array('A1','B1');
$objPHPExcel-getActiveSheet()-setCellValue('A1', '用户名');
$objPHPExcel-getActiveSheet()-setCellValue('B1', '密码');
$objPHPExcel-getActiveSheet()-getColumnDimension( 'A')-setWidth(12);
$objPHPExcel-getActiveSheet()-getColumnDimension( 'B')-setWidth(12);
$size=count($passwords);
if($passwords){
for($i=0;$i$size;$i++){
$objPHPExcel-getActiveSheet()-setCellValue("A".($i+2), $lists[$i]['username']);
$objPHPExcel-getActiveSheet()-setCellValue("B".($i+2), $lists[$i]['mima']);
}
}
$objPHPExcel-setActiveSheetIndex(0);
$objPHPExcel-getActiveSheet()-setTitle('Excel表');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$outputFileName = "fileaname";//文件名字
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.iconv('UTF-8','GB18030',$outputFileName).'.xls"');
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-save(DT_ROOT.'file/cache/');
$objWriter-save('php://output');
如何利用ThinkPHP框架实现Excel数据导入和导出
实现步骤：
一：在下载最新PHPExcel放到Vendor下，注意位置：ThinkPHP\Extend\Vendor\PHPExcel\PHPExcel.php。
二：导出excel代码实现
/**方法**/
function index(){
$this-display();
}
public function exportExcel($expTitle,$expCellName,$expTableData){
$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
$fileName = $_SESSION['account'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
$cellNum = count($expCellName);
$dataNum = count($expTableData);
vendor("PHPExcel.PHPExcel");
$objPHPExcel = new PHPExcel();
$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');
$objPHPExcel-getActiveSheet(0)-mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
// $objPHPExcel-setActiveSheetIndex(0)-setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s'));
for($i=0;$i$cellNum;$i++){
$objPHPExcel-setActiveSheetIndex(0)-setCellValue($cellName[$i].'2', $expCellName[$i][1]);
}
// Miscellaneous glyphs, UTF-8
for($i=0;$i$dataNum;$i++){
for($j=0;$j$cellNum;$j++){
$objPHPExcel-getActiveSheet(0)-setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
}
}
header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter-save('php://output');
exit;
}
/**
*
* 导出Excel
*/
function expUser(){//导出Excel
$xlsName = "User";
$xlsCell = array(
array('id','账号序列'),
array('truename','名字'),
array('sex','性别'),
array('res_id','院系'),
array('sp_id','专业'),
array('class','班级'),
array('year','毕业时间'),
array('city','所在地'),
array('company','单位'),
array('zhicheng','职称'),
array('zhiwu','职务'),
array('jibie','级别'),
array('tel','电话'),
array('qq','qq'),
array('email','邮箱'),
array('honor','荣誉'),
array('remark','备注')
);
$xlsModel = M('Member');
$xlsData = $xlsModel-Field('id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark')-select();
foreach ($xlsData as $k = $v)
{
$xlsData[$k]['sex']=$v['sex']==1?'男':'女';
}
$this-exportExcel($xlsName,$xlsCell,$xlsData);
}
复制代码
第三：导入excel数据代码
function impUser(){
if (!empty($_FILES)) {
import("@.ORG.UploadFile");
$config=array(
'allowExts'=array('xlsx','xls'),
'savePath'='./Public/upload/',
'saveRule'='time',
);
$upload = new UploadFile($config);
if (!$upload-upload()) {
$this-error($upload-getErrorMsg());
} else {
$info = $upload-getUploadFileInfo();
}
vendor("PHPExcel.PHPExcel");
$file_name=$info[0]['savepath'].$info[0]['savename'];
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader-load($file_name,$encode='utf-8');
$sheet = $objPHPExcel-getSheet(0);
$highestRow = $sheet-getHighestRow(); // 取得总行数
$highestColumn = $sheet-getHighestColumn(); // 取得总列数
for($i=3;$i=$highestRow;$i++)
{
$data['account']= $data['truename'] = $objPHPExcel-getActiveSheet()-getCell("B".$i)-getValue();
$sex = $objPHPExcel-getActiveSheet()-getCell("C".$i)-getValue();
// $data['res_id'] = $objPHPExcel-getActiveSheet()-getCell("D".$i)-getValue();
$data['class'] = $objPHPExcel-getActiveSheet()-getCell("E".$i)-getValue();
$data['year'] = $objPHPExcel-getActiveSheet()-getCell("F".$i)-getValue();
$data['city']= $objPHPExcel-getActiveSheet()-getCell("G".$i)-getValue();
$data['company']= $objPHPExcel-getActiveSheet()-getCell("H".$i)-getValue();
$data['zhicheng']= $objPHPExcel-getActiveSheet()-getCell("I".$i)-getValue();
$data['zhiwu']= $objPHPExcel-getActiveSheet()-getCell("J".$i)-getValue();
$data['jibie']= $objPHPExcel-getActiveSheet()-getCell("K".$i)-getValue();
$data['honor']= $objPHPExcel-getActiveSheet()-getCell("L".$i)-getValue();
$data['tel']= $objPHPExcel-getActiveSheet()-getCell("M".$i)-getValue();
$data['qq']= $objPHPExcel-getActiveSheet()-getCell("N".$i)-getValue();
$data['email']= $objPHPExcel-getActiveSheet()-getCell("O".$i)-getValue();
$data['remark']= $objPHPExcel-getActiveSheet()-getCell("P".$i)-getValue();
$data['sex']=$sex=='男'?1:0;
$data['res_id'] =1;
$data['last_login_time']=0;
$data['create_time']=$data['last_login_ip']=$_SERVER['REMOTE_ADDR'];
$data['login_count']=0;
$data['join']=0;
$data['avatar']='';
$data['password']=md5('123456');
M('Member')-add($data);
}
$this-success('导入成功！');
}else
{
$this-error("请选择上传的文件");
}
}
复制代码
四、模板代码
html
head
/head
body
Pa href="{:U('Index/expUser')}" 导出数据并生成excel/a/Pbr/
form action="{:U('Index/impUser')}" method="post" enctype="multipart/form-data"
input type="file" name="import"/
input type="hidden" name="table" value="tablename"/
input type="submit" value="导入"/
/form
/body
/html
