冒险村物语
英雄无敌3塔防
驾考家园手游

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

2014-09-05

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>';         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>';        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;        }    }}?>

（免责声明：文章内容如涉及作品内容、版权和其它问题，请及时与我们联系，我们将在第一时间删除内容，文章内容仅供参考）