Heim > Artikel > Backend-Entwicklung > PHP：使用PHPExcel完成电子表格文件的导出下载和导入操作_PHP教程
PHP：使用PHPExcel完成电子表格文件的导出下载和导入操作_PHP教程
- WBOYOriginal
- 2016-07-15 13:21:331041Durchsuche
view页面：
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script src="../../js/lib/jquery/jquery-1.7.2.min.js"></script>
</head>
<body>
<div>
<form action="../../src/controller/PHPExcel.php?type=report" method="post">
<input type="submit" id="excel_report" value="导出"/>
</form>
<hr/>
<form action="../../src/controller/PHPExcel.php?type=import" method="post" enctype="multipart/form-data">
<input type="file" name="inputExcel">
<input type="submit" value="导入数据">
</form>
</div>
<script>
(function() {
})();
</script>
</body>
</html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script src="../../js/lib/jquery/jquery-1.7.2.min.js"></script>
</head>
<body>
<div>
<form action="../../src/controller/PHPExcel.php?type=report" method="post">
<input type="submit" id="excel_report" value="导出"/>
</form>
<hr/>
<form action="../../src/controller/PHPExcel.php?type=import" method="post" enctype="multipart/form-data">
<input type="file" name="inputExcel">
<input type="submit" value="导入数据">
</form>
</div>
<script>
(function() {
})();
</script>
</body>
</html>
后台逻辑处理文件：
<?php
/*
* PHPExcel.php 使用PHPExcel完成文件的导出下载和导入操作
* @author zyb_icanplay7 <zyb_icanplay@163.com>
*/
$operation = $_GET['type'];
switch ( $operation ) {
case 'report':
//路径按自己项目实际路径修改，文件请到PHPExcel官网下载
include_once '../../plugin/PHPExcel/PHPExcel.php';
include_once '../../plugin/PHPExcel/PHPExcel/Writer/Excel2007.php';
//或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
//创建一个excel
$objPHPExcel = new PHPExcel();
//保存excel—2007格式
$objWriter = new PHPExcel_Writer_Excel2007( $objPHPExcel );
//或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
//
//设置excel的属性：
//创建人
$objPHPExcel->getProperties()->setCreator( "ZYB" );
//最后修改人
$objPHPExcel->getProperties()->setLastModifiedBy( "ZYB" );
//标题
$objPHPExcel->getProperties()->setTitle( "Office 2007 XLSX Test Document" );
//题目
$objPHPExcel->getProperties()->setSubject( "Office 2007 XLSX Test Document" );
//描述
$objPHPExcel->getProperties()->setDescription( "Test document for Office 2007 XLSX, generated using PHP classes." );
//关键字
$objPHPExcel->getProperties()->setKeywords( "office 2007 openxml php" );
//种类
$objPHPExcel->getProperties()->setCategory( "Test result file" );
//
//设置当前的sheet
$objPHPExcel->setActiveSheetIndex( 0 );
//设置sheet的name
$objPHPExcel->getActiveSheet()->setTitle( '导出表测试' );
//设置单元格的值
$subTitle = array( '账号', '姓名', '性别', '地址', '电话', '事由', '复读' );
$datas = array(
0 => array( 'ZhangSan', '张三', '男', '广东', '1232323443', '实得分', 1 ),
1 => array( 'ZhangSan2', '张三2', '男', '广东2', '13454444433', '实得分2', 2 ),
);
$colspan = range( 'A', 'G' );
$count = count( $subTitle );
// 标题输出
for ( $index = 0; $index < $count; $index++ ) {
$col = $colspan[$index];
$objPHPExcel->getActiveSheet()->setCellValue( $col . '1', $subTitle[$index] );
//设置font
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFont()->setName( 'Candara' );
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFont()->setSize( 15 );
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFont()->setBold( true );
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFont()->getColor()
->setARGB( PHPExcel_Style_Color::COLOR_WHITE );
//设置填充色彩
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFill()
->setFillType( PHPExcel_Style_Fill::FILL_SOLID );
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFill()->getStartColor()->setARGB( 'FF808080' );
// align 设置居中
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getAlignment()
->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER );
if ( $subTitle[$index] == '电话' ) {
// 设置宽度
$objPHPExcel->getActiveSheet()->getColumnDimension( $col )->setWidth( 40 );
}
}
// 内容输出
foreach ( $datas as $key => $value ) {
$colNumber = $key + 2; //第二行开始才是内容
foreach ( $colspan as $colKey => $col ) {
$objPHPExcel->getActiveSheet()->setCellValue( $col . $colNumber, $value[$colKey] );
}
}
//
//在默认sheet后，创建一个worksheet
$objPHPExcel->createSheet();
$fileName = "xxx.xlsx";
$objWriter->save( $fileName );
download( $fileName, true );
break;
case 'import':
//路径按自己项目实际路径修改，文件请到PHPExcel官网下载
include_once '../../plugin/PHPExcel/PHPExcel.php';
include_once '../../plugin/PHPExcel/PHPExcel/IOFactory.php';
include_once '../../plugin/PHPExcel/PHPExcel/Reader/Excel5.php';
$fileName = $_FILES['inputExcel']['name'];
$fileTmpAddr = $_FILES['inputExcel']['tmp_name'];
//获取上传文件的扩展名
$extend = strrchr( $fileName, '.' );
//上传后的文件名
$fileDesAddr = '../../upload/' . date( "Y-m-d-H-i-s" ) . $extend; //上传后的文件名地址
$result = move_uploaded_file( $fileTmpAddr, $fileDesAddr );
if ( $result ) {
$readerType = ($extend == ".xlsx") ? "Excel2007" : "Excel5";
$objPHPExcel = PHPExcel_IOFactory::createReader( $readerType )->load( $fileDesAddr );
$sheet = $objPHPExcel->getSheet( 0 );
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$colspan = range( 'A', $highestColumn );
$datas = array( );
//循环读取excel文件
for ( $j = 2; $j <= $highestRow; $j++ ) {
$array = array( );
foreach ( $colspan as $value ) {
$array[] = $objPHPExcel->getActiveSheet()->getCell( $value . $j )->getValue();
}
$datas[] = $array;
}
//读取完成，最后删除文件
unlink( $fileDesAddr );
}
echo '<pre class="brush:php;toolbar:false">';
print_r( $datas );
exit;
break;
}
//==============================================================================================
function download( $fileName, $delDesFile = false, $isExit = true ) {
if ( file_exists( $fileName ) ) {
header( 'Content-Description: File Transfer' );
header( 'Content-Type: application/octet-stream' );
header( 'Content-Disposition: attachment;filename = ' . basename( $fileName ) );
header( 'Content-Transfer-Encoding: binary' );
header( 'Expires: 0' );
header( 'Cache-Control: must-revalidate, post-check = 0, pre-check = 0' );
header( 'Pragma: public' );
header( 'Content-Length: ' . filesize( $fileName ) );
ob_clean();
flush();
readfile( $fileName );
if ( $delDesFile ) {
unlink( $fileName );
}
if ( $isExit ) {
exit;
}
}
}
?>
<?php
/*
* PHPExcel.php 使用PHPExcel完成文件的导出下载和导入操作
* @author zyb_icanplay7 <zyb_icanplay@163.com>
*/
$operation = $_GET['type'];
switch ( $operation ) {
case 'report':
//路径按自己项目实际路径修改，文件请到PHPExcel官网下载
include_once '../../plugin/PHPExcel/PHPExcel.php';
include_once '../../plugin/PHPExcel/PHPExcel/Writer/Excel2007.php';
//或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
//创建一个excel
$objPHPExcel = new PHPExcel();
//保存excel—2007格式
$objWriter = new PHPExcel_Writer_Excel2007( $objPHPExcel );
//或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
//
//设置excel的属性：
//创建人
$objPHPExcel->getProperties()->setCreator( "ZYB" );
//最后修改人
$objPHPExcel->getProperties()->setLastModifiedBy( "ZYB" );
//标题
$objPHPExcel->getProperties()->setTitle( "Office 2007 XLSX Test Document" );
//题目
$objPHPExcel->getProperties()->setSubject( "Office 2007 XLSX Test Document" );
//描述
$objPHPExcel->getProperties()->setDescription( "Test document for Office 2007 XLSX, generated using PHP classes." );
//关键字
$objPHPExcel->getProperties()->setKeywords( "office 2007 openxml php" );
//种类
$objPHPExcel->getProperties()->setCategory( "Test result file" );
//
//设置当前的sheet
$objPHPExcel->setActiveSheetIndex( 0 );
//设置sheet的name
$objPHPExcel->getActiveSheet()->setTitle( '导出表测试' );
//设置单元格的值
$subTitle = array( '账号', '姓名', '性别', '地址', '电话', '事由', '复读' );
$datas = array(
0 => array( 'ZhangSan', '张三', '男', '广东', '1232323443', '实得分', 1 ),
1 => array( 'ZhangSan2', '张三2', '男', '广东2', '13454444433', '实得分2', 2 ),
);
$colspan = range( 'A', 'G' );
$count = count( $subTitle );
// 标题输出
for ( $index = 0; $index < $count; $index++ ) {
$col = $colspan[$index];
$objPHPExcel->getActiveSheet()->setCellValue( $col . '1', $subTitle[$index] );
//设置font
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFont()->setName( 'Candara' );
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFont()->setSize( 15 );
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFont()->setBold( true );
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFont()->getColor()
->setARGB( PHPExcel_Style_Color::COLOR_WHITE );
//设置填充色彩
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFill()
->setFillType( PHPExcel_Style_Fill::FILL_SOLID );
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getFill()->getStartColor()->setARGB( 'FF808080' );
// align 设置居中
$objPHPExcel->getActiveSheet()->getStyle( $col . '1' )->getAlignment()
->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER );
if ( $subTitle[$index] == '电话' ) {
// 设置宽度
$objPHPExcel->getActiveSheet()->getColumnDimension( $col )->setWidth( 40 );
}
}
// 内容输出
foreach ( $datas as $key => $value ) {
$colNumber = $key + 2; //第二行开始才是内容
foreach ( $colspan as $colKey => $col ) {
$objPHPExcel->getActiveSheet()->setCellValue( $col . $colNumber, $value[$colKey] );
}
}
//
//在默认sheet后，创建一个worksheet
$objPHPExcel->createSheet();
$fileName = "xxx.xlsx";
$objWriter->save( $fileName );
download( $fileName, true );
break;
case 'import':
//路径按自己项目实际路径修改，文件请到PHPExcel官网下载
include_once '../../plugin/PHPExcel/PHPExcel.php';
include_once '../../plugin/PHPExcel/PHPExcel/IOFactory.php';
include_once '../../plugin/PHPExcel/PHPExcel/Reader/Excel5.php';
$fileName = $_FILES['inputExcel']['name'];
$fileTmpAddr = $_FILES['inputExcel']['tmp_name'];
//获取上传文件的扩展名
$extend = strrchr( $fileName, '.' );
//上传后的文件名
$fileDesAddr = '../../upload/' . date( "Y-m-d-H-i-s" ) . $extend; //上传后的文件名地址
$result = move_uploaded_file( $fileTmpAddr, $fileDesAddr );
if ( $result ) {
$readerType = ($extend == ".xlsx") ? "Excel2007" : "Excel5";
$objPHPExcel = PHPExcel_IOFactory::createReader( $readerType )->load( $fileDesAddr );
$sheet = $objPHPExcel->getSheet( 0 );
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$colspan = range( 'A', $highestColumn );
$datas = array( );
//循环读取excel文件
for ( $j = 2; $j <= $highestRow; $j++ ) {
$array = array( );
foreach ( $colspan as $value ) {
$array[] = $objPHPExcel->getActiveSheet()->getCell( $value . $j )->getValue();
}
$datas[] = $array;
}
//读取完成，最后删除文件
unlink( $fileDesAddr );
}
echo '<pre class="brush:php;toolbar:false">';
print_r( $datas );
exit;
break;
}
//==============================================================================================
function download( $fileName, $delDesFile = false, $isExit = true ) {
if ( file_exists( $fileName ) ) {
header( 'Content-Description: File Transfer' );
header( 'Content-Type: application/octet-stream' );
header( 'Content-Disposition: attachment;filename = ' . basename( $fileName ) );
header( 'Content-Transfer-Encoding: binary' );
header( 'Expires: 0' );
header( 'Cache-Control: must-revalidate, post-check = 0, pre-check = 0' );
header( 'Pragma: public' );
header( 'Content-Length: ' . filesize( $fileName ) );
ob_clean();
flush();
readfile( $fileName );
if ( $delDesFile ) {
unlink( $fileName );
}
if ( $isExit ) {
exit;
}
}
}
?>
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：ThinkPHP集成支付宝ILLEGAL_SIGN错误_PHP教程Nächster Artikel：PHP中数组转换为XML格式_PHP教程
In Verbindung stehende Artikel
Mehr sehen- So verwenden Sie cURL zum Implementieren von Get- und Post-Anfragen in PHP
- So verwenden Sie cURL zum Implementieren von Get- und Post-Anfragen in PHP
- So verwenden Sie cURL zum Implementieren von Get- und Post-Anfragen in PHP
- So verwenden Sie cURL zum Implementieren von Get- und Post-Anfragen in PHP
- Alle Ausdruckssymbole in regulären Ausdrücken (Zusammenfassung)