PHP:使用PHPExcel完成电子表格文件的导出下载和导入操作_PHP教程
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了PHP:使用PHPExcel完成电子表格文件的导出下载和导入操作_PHP教程,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9705字,纯文字阅读大概需要14分钟。
内容图文
![PHP:使用PHPExcel完成电子表格文件的导出下载和导入操作_PHP教程](/upload/InfoBanner/zyjiaocheng/175/519239387728407693f649904e57b477.jpg)
后台逻辑处理文件:
*/ $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; } } } ?>
http://www.bkjia.com/PHPjc/477177.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/477177.htmlTechArticleview页面: 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=../../s...
内容总结
以上是互联网集市为您收集整理的PHP:使用PHPExcel完成电子表格文件的导出下载和导入操作_PHP教程全部内容,希望文章能够帮你解决PHP:使用PHPExcel完成电子表格文件的导出下载和导入操作_PHP教程所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。