Home > Article > Backend Development > PHP: Use PHPExcel to complete the export, download and import operations of spreadsheet files_PHP Tutorial
PHP: Use PHPExcel to complete the export, download and import operations of spreadsheet files_PHP Tutorial
- WBOYOriginal
- 2016-07-15 13:21:331041browse
view page:
<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>
Backend logic processing file:
<?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;
}
}
}
?>
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：ThinkPHP integrated Alipay ILLEGAL_SIGN error_PHP tutorialNext article：ThinkPHP integrated Alipay ILLEGAL_SIGN error_PHP tutorial