PHP: Use PHPExcel to complete the export, download and import operations of spreadsheet files

Home

Backend Development

PHP Tutorial

PHP: Use PHPExcel to complete the export, download and import operations of spreadsheet files_PHP Tutorial

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jul 15, 2016 pm 01:21 PM

headhtmlphpphpexcelviewuseFinishimportExportoperatedocumentelectronicofsheetpage

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[&#39;type&#39;]; 
switch ( $operation ) { 
    case &#39;report&#39;: 
        //路径按自己项目实际路径修改，文件请到PHPExcel官网下载  
        include_once &#39;../../plugin/PHPExcel/PHPExcel.php&#39;; 
        include_once &#39;../../plugin/PHPExcel/PHPExcel/Writer/Excel2007.php&#39;; 
        //或者include &#39;PHPExcel/Writer/Excel5.php&#39;; 用于输出.xls的  
        //创建一个excel  
        $objPHPExcel = new PHPExcel(); 
        //保存excel&mdash;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( &#39;导出表测试&#39; ); 
        //设置单元格的值  
        $subTitle = array( &#39;账号&#39;, &#39;姓名&#39;, &#39;性别&#39;, &#39;地址&#39;, &#39;电话&#39;, &#39;事由&#39;, &#39;复读&#39; ); 
        $datas = array( 
            0 => array( &#39;ZhangSan&#39;, &#39;张三&#39;, &#39;男&#39;, &#39;广东&#39;, &#39;1232323443&#39;, &#39;实得分&#39;, 1 ), 
            1 => array( &#39;ZhangSan2&#39;, &#39;张三2&#39;, &#39;男&#39;, &#39;广东2&#39;, &#39;13454444433&#39;, &#39;实得分2&#39;, 2 ), 
        ); 
        $colspan = range( &#39;A&#39;, &#39;G&#39; ); 
        $count = count( $subTitle ); 
        // 标题输出  
        for ( $index = 0; $index < $count; $index++ ) { 
            $col = $colspan[$index]; 
            $objPHPExcel->getActiveSheet()->setCellValue( $col . &#39;1&#39;, $subTitle[$index] ); 
            //设置font  
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFont()->setName( &#39;Candara&#39; ); 
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFont()->setSize( 15 ); 
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFont()->setBold( true ); 
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFont()->getColor() 
                    ->setARGB( PHPExcel_Style_Color::COLOR_WHITE ); 
 
            //设置填充色彩    
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFill() 
                    ->setFillType( PHPExcel_Style_Fill::FILL_SOLID ); 
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFill()->getStartColor()->setARGB( &#39;FF808080&#39; ); 
            // align 设置居中  
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getAlignment() 
                    ->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER ); 
            if ( $subTitle[$index] == &#39;电话&#39; ) { 
                // 设置宽度  
                $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 &#39;import&#39;: 
        //路径按自己项目实际路径修改，文件请到PHPExcel官网下载  
        include_once &#39;../../plugin/PHPExcel/PHPExcel.php&#39;; 
        include_once &#39;../../plugin/PHPExcel/PHPExcel/IOFactory.php&#39;; 
        include_once &#39;../../plugin/PHPExcel/PHPExcel/Reader/Excel5.php&#39;; 
 
        $fileName = $_FILES[&#39;inputExcel&#39;][&#39;name&#39;]; 
        $fileTmpAddr = $_FILES[&#39;inputExcel&#39;][&#39;tmp_name&#39;]; 
        //获取上传文件的扩展名  
        $extend = strrchr( $fileName, &#39;.&#39; ); 
        //上传后的文件名  
        $fileDesAddr = &#39;../../upload/&#39; . 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( &#39;A&#39;, $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 &#39;<pre class="brush:php;toolbar:false">&#39;; 
        print_r( $datas ); 
        exit; 
        break; 
} 
 
//==============================================================================================  
function download( $fileName, $delDesFile = false, $isExit = true ) { 
    if ( file_exists( $fileName ) ) { 
        header( &#39;Content-Description: File Transfer&#39; ); 
        header( &#39;Content-Type: application/octet-stream&#39; ); 
        header( &#39;Content-Disposition: attachment;filename = &#39; . basename( $fileName ) ); 
        header( &#39;Content-Transfer-Encoding: binary&#39; ); 
        header( &#39;Expires: 0&#39; ); 
        header( &#39;Cache-Control: must-revalidate, post-check = 0, pre-check = 0&#39; ); 
        header( &#39;Pragma: public&#39; ); 
        header( &#39;Content-Length: &#39; . 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[&#39;type&#39;];
switch ( $operation ) {
    case &#39;report&#39;:
        //路径按自己项目实际路径修改，文件请到PHPExcel官网下载
        include_once &#39;../../plugin/PHPExcel/PHPExcel.php&#39;;
        include_once &#39;../../plugin/PHPExcel/PHPExcel/Writer/Excel2007.php&#39;;
        //或者include &#39;PHPExcel/Writer/Excel5.php&#39;; 用于输出.xls的
        //创建一个excel
        $objPHPExcel = new PHPExcel();
        //保存excel&mdash;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( &#39;导出表测试&#39; );
        //设置单元格的值
        $subTitle = array( &#39;账号&#39;, &#39;姓名&#39;, &#39;性别&#39;, &#39;地址&#39;, &#39;电话&#39;, &#39;事由&#39;, &#39;复读&#39; );
        $datas = array(
            0 => array( &#39;ZhangSan&#39;, &#39;张三&#39;, &#39;男&#39;, &#39;广东&#39;, &#39;1232323443&#39;, &#39;实得分&#39;, 1 ),
            1 => array( &#39;ZhangSan2&#39;, &#39;张三2&#39;, &#39;男&#39;, &#39;广东2&#39;, &#39;13454444433&#39;, &#39;实得分2&#39;, 2 ),
        );
        $colspan = range( &#39;A&#39;, &#39;G&#39; );
        $count = count( $subTitle );
        // 标题输出
        for ( $index = 0; $index < $count; $index++ ) {
            $col = $colspan[$index];
            $objPHPExcel->getActiveSheet()->setCellValue( $col . &#39;1&#39;, $subTitle[$index] );
            //设置font
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFont()->setName( &#39;Candara&#39; );
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFont()->setSize( 15 );
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFont()->setBold( true );
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFont()->getColor()
                    ->setARGB( PHPExcel_Style_Color::COLOR_WHITE );

            //设置填充色彩 
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFill()
                    ->setFillType( PHPExcel_Style_Fill::FILL_SOLID );
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getFill()->getStartColor()->setARGB( &#39;FF808080&#39; );
            // align 设置居中
            $objPHPExcel->getActiveSheet()->getStyle( $col . &#39;1&#39; )->getAlignment()
                    ->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER );
            if ( $subTitle[$index] == &#39;电话&#39; ) {
                // 设置宽度
                $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 &#39;import&#39;:
        //路径按自己项目实际路径修改，文件请到PHPExcel官网下载
        include_once &#39;../../plugin/PHPExcel/PHPExcel.php&#39;;
        include_once &#39;../../plugin/PHPExcel/PHPExcel/IOFactory.php&#39;;
        include_once &#39;../../plugin/PHPExcel/PHPExcel/Reader/Excel5.php&#39;;

        $fileName = $_FILES[&#39;inputExcel&#39;][&#39;name&#39;];
        $fileTmpAddr = $_FILES[&#39;inputExcel&#39;][&#39;tmp_name&#39;];
        //获取上传文件的扩展名
        $extend = strrchr( $fileName, &#39;.&#39; );
        //上传后的文件名
        $fileDesAddr = &#39;../../upload/&#39; . 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( &#39;A&#39;, $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 &#39;<pre class="brush:php;toolbar:false">&#39;;
        print_r( $datas );
        exit;
        break;
}

//==============================================================================================
function download( $fileName, $delDesFile = false, $isExit = true ) {
    if ( file_exists( $fileName ) ) {
        header( &#39;Content-Description: File Transfer&#39; );
        header( &#39;Content-Type: application/octet-stream&#39; );
        header( &#39;Content-Disposition: attachment;filename = &#39; . basename( $fileName ) );
        header( &#39;Content-Transfer-Encoding: binary&#39; );
        header( &#39;Expires: 0&#39; );
        header( &#39;Cache-Control: must-revalidate, post-check = 0, pre-check = 0&#39; );
        header( &#39;Pragma: public&#39; );
        header( &#39;Content-Length: &#39; . 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

How does PHP identify a user's session?May 01, 2025 am 12:23 AM

PHPidentifiesauser'ssessionusingsessioncookiesandsessionIDs.1)Whensession_start()iscalled,PHPgeneratesauniquesessionIDstoredinacookienamedPHPSESSIDontheuser'sbrowser.2)ThisIDallowsPHPtoretrievesessiondatafromtheserver.

What are some best practices for securing PHP sessions?May 01, 2025 am 12:22 AM

The security of PHP sessions can be achieved through the following measures: 1. Use session_regenerate_id() to regenerate the session ID when the user logs in or is an important operation. 2. Encrypt the transmission session ID through the HTTPS protocol. 3. Use session_save_path() to specify the secure directory to store session data and set permissions correctly.

Where are PHP session files stored by default?May 01, 2025 am 12:15 AM

PHPsessionfilesarestoredinthedirectoryspecifiedbysession.save_path,typically/tmponUnix-likesystemsorC:\Windows\TemponWindows.Tocustomizethis:1)Usesession_save_path()tosetacustomdirectory,ensuringit'swritable;2)Verifythecustomdirectoryexistsandiswrita

How do you retrieve data from a PHP session?May 01, 2025 am 12:11 AM

ToretrievedatafromaPHPsession,startthesessionwithsession_start()andaccessvariablesinthe$_SESSIONarray.Forexample:1)Startthesession:session_start().2)Retrievedata:$username=$_SESSION['username'];echo"Welcome,".$username;.Sessionsareserver-si

How can you use sessions to implement a shopping cart?May 01, 2025 am 12:10 AM

The steps to build an efficient shopping cart system using sessions include: 1) Understand the definition and function of the session. The session is a server-side storage mechanism used to maintain user status across requests; 2) Implement basic session management, such as adding products to the shopping cart; 3) Expand to advanced usage, supporting product quantity management and deletion; 4) Optimize performance and security, by persisting session data and using secure session identifiers.

How do you create and use an interface in PHP?Apr 30, 2025 pm 03:40 PM

The article explains how to create, implement, and use interfaces in PHP, focusing on their benefits for code organization and maintainability.

What is the difference between crypt() and password_hash()?Apr 30, 2025 pm 03:39 PM

The article discusses the differences between crypt() and password_hash() in PHP for password hashing, focusing on their implementation, security, and suitability for modern web applications.

How can you prevent Cross-Site Scripting (XSS) in PHP?Apr 30, 2025 pm 03:38 PM

Article discusses preventing Cross-Site Scripting (XSS) in PHP through input validation, output encoding, and using tools like OWASP ESAPI and HTML Purifier.

See all articles