php读取和导出Excel文件

最新推荐文章于 2025-09-09 18:53:59 发布

weixin_34090643

最新推荐文章于 2025-09-09 18:53:59 发布

阅读量63

点赞数

CC 4.0 BY-SA版权

文章标签： php

原文链接：http://www.cnblogs.com/jiangzuo/p/7987312.html

本文介绍了一种使用PHPExcel库从Excel文件中读取数据的方法，并通过PHP脚本实现了对Excel文件中特定列数据的处理及新链接的生成。此外，还展示了如何将处理后的数据导出到新的Excel文件中。

require 'vendor/PHPExcel/PHPExcel.php';
require 'vendor/PHPExcel/PHPExcel/IOFactory.php';

public function dull_url(){
        
        $reader = \PHPExcel_IOFactory::createReader('Excel2007'); //设置以Excel5格式(Excel97-2003工作簿)  
        $PHPExcel = $reader->load("AD.xlsx"); // 载入excel文件          
        $sheet = $PHPExcel->getSheet(0); // 读取第一個工作表  
        $highestRow = $sheet->getHighestRow(); // 取得总行数  
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数  
        
        /** 循环读取每个单元格的数据 */  
        $data = array();
        for ($row = 2; $row <= $highestRow; $row++){//行数是以第1行开始  
            $dataset = array();
            for ($column = 'A'; $column <= $highestColumm; $column++) {//列数是以A列开始  
                if($column == 'A'){
                    $url = 'http://www.XXX.com/s/?k='. urlencode(trim($sheet->getCell($column.$row)->getValue()));
                }
                if($column == 'C'){
                    $dataset[] = $url;
                }else{
                    $dataset[] = trim($sheet->getCell($column.$row)->getValue());  
                }
                //echo $column.$row.":".$sheet->getCell($column.$row)->getValue()."<br />";  
                //echo $sheet->getCell($column.$row)->getValue()."  ";                  
            }
            $data[] = $dataset;
        } 
        $this->dull_url_data($data);
    }
    function dull_url_data($data){
        header("Content-Type:text/html;charset=utf-8");  
        //include_once('../../ClassLibrary/PHPExcel-1.8/Classes/PHPExcel.php');  

        error_reporting(E_ALL);  
        ini_set('display_errors', TRUE);  
        ini_set('display_startup_errors', TRUE); 
                //创建对象  
        $excel = new \PHPExcel();  
        //Excel表格式,这里简略写了8列  
        $letter = array('A','B','C','D','E','F','F','G');  
        //表头数组  
        $tableheader = array('元件型号','制造商','搜索结果页链接');  
        //填充表头信息  
        for($i = 0;$i < count($tableheader);$i++) {  
            $excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");  
        }  

        //填充表格信息  
        for ($i = 2;$i <= count($data) + 1;$i++) {  
            $j = 0;  
            foreach ($data[$i - 2] as $key=>$value) {  
                $excel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");  
                $j++;  
            }  
        }  

        //创建Excel输入对象  
        $write = new \PHPExcel_Writer_Excel5($excel);  
        header("Pragma: public");  
        header("Expires: 0");  
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");  
        header("Content-Type:application/force-download");  
        header("Content-Type:application/vnd.ms-execl");  
        header("Content-Type:application/octet-stream");  
        header("Content-Type:application/download");;  
        header('Content-Disposition:attachment;filename="url_data.xls"');  
        header("Content-Transfer-Encoding:binary");  
        $write->save('php://output');  exit;
    }