本文目錄一覽：
- 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
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/231587.html
微信掃一掃 
支付寶掃一掃 