Community Learn Tools Library Leisure
search
English
Home > Backend Development > PHP Tutorial > body text

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

WBOY
Release： 2016-07-15 13:21:33
Original
1107 people have browsed it

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>
Copy after login

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;
        }
    }
}
?>
Copy after login

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/477177.htmlTechArticleview 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=../../s...
Related labels：
head html php phpexcel view use Finish import Export operate document electronic of sheet page
source：php.cn
Previous article：ThinkPHP integrated Alipay ILLEGAL_SIGN error_PHP tutorial Next article：Convert array to XML format in PHP_PHP tutorial
Statement of this Website
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
Latest Articles by Author
Latest Issues
PHP arrays obtained from URL parameters do not behave as expected I have a URL parameter that contains the category ID and I want to treat it as an array li...
From 2024-04-06 22:09:02
0
1
1428
Where should I place CustomLog directive in apache I'm using php:7.2-apachedocker. I need to disable health check url login access log. Based...
From 2024-04-06 22:03:59
0
1
990
What is the format of the variables in the return value? I am a new learner of php. I found a piece of code: if($x<time()){return[false,'error']...
From 2024-04-06 21:55:20
0
1
778
Problems encountered when using opentbs to generate odt files: values ​​of the same key are displayed in the same row instead of separate columns. I'm using a library called OpenTbs to create odt using PHP, I'm using it because columns a...
From 2024-04-06 20:18:18
0
1
483
Group MySQL results by ID for looping over I have a table with flight data in mysql. I'm writing a php code that will group and displ...
From 2024-04-06 17:27:56
0
1
406
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template