程式師世界 >> 編程語言 >> 網頁編程 >> PHP編程 >> 關於PHP編程 >> PHP：使用PHPExcel完成電子表格文件的導出下載和導入操作

PHP：使用PHPExcel完成電子表格文件的導出下載和導入操作

編輯：關於PHP編程

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 <[email protected]>
 */ 
$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 <[email protected]>
 */
$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;
        }
    }
}
?>