php下载excel表格,PHP：使用PHPExcel完成电子表格文件的导出下载和导入操作

view页面：

后台逻辑处理文件：

*/

$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 '';

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;

}

}

}

?>

*/

$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 '';

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;

}

}

}

?>