php 读取，生成excel文件

首先下载插件PHPExcel （PHPExcel-1.8），以tp5框架为例，将该文件放在verdor文件夹下。然后引入IOFactory文件。

1.读取文件的部分内容（用于固定格式）

public function readExcel($filename){
            try {
                Vendor('PHPExcel/PHPExcel/IOFactory');
                $reader = \PHPExcel_IOFactory::createReader('Excel2007');
                $PHPExcel = $reader->load($filename); // 文档名称
                $objWorksheet = $PHPExcel->getActiveSheet();   #获取当前sheet
                $highestRow = $objWorksheet->getHighestRow(); // 取得总行数
                $highestColumn = $objWorksheet->getHighestColumn(); // 取得总列数
                $data = array();
                for ($row = 1; $row <= $highestRow; $row++) {
                    #从第二行开始，依次获取多少列的内容 因为第一行一般是表头 也可再加一个for循环 获取每一列的内容
                    $A = $objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
                    $B = $objWorksheet->getCellByColumnAndRow(1, $row)->getValue();
                    array_push($data,$A,$B);
                }
                return $data;
            } catch (Exception $e) {
                $this->ajaxReturn(array(
                    'code' => -1,
                    'msg' => $e->getMessage()
                ));
            }
        }

 

注：默认从第一行开始，读取A列和B列，以数组的形式返回，但是可以自定义。传入文件路径即可

 

2.读取文件的全部内容（用于多变，未知格式）

public function excelread($path){
        try {
            Vendor('PHPExcel/PHPExcel/IOFactory');
            $reader = \PHPExcel_IOFactory::createReader('Excel5');
            $PHPExcel = $reader->load($path); // 文档名称
            $objWorksheet = $PHPExcel->getSheet(0);  #获取当前sheet
            $highestRow = $objWorksheet->getHighestRow(); // 取得总行数
            $highestColumn = $objWorksheet->getHighestColumn(); // 取得总列数
            $data =array();
            for ($row = 1; $row <= $highestRow; $row++) {
                for ($col = 'A'; $col != $highestColumn; $col ++) {
                    #从第二行开始，依次获取多少列的内容 因为第一行一般是表头 也可再加一个for循环 获取每一列的内容
                    $res = $objWorksheet->getCell($col.$row)->getValue();
                    $res = mb_convert_encoding($res, "UTF-8", "GBK");
                    $data[$col][$row] = $res;
                }
            }
            return $data;
        } catch (Exception $e) {
            $this->ajaxReturn(array(
                'code' => -1,
                'msg' => $e->getMessage()
            ));
        }
    }

 

3.生成excel文件在线下载 

 #添加内容
    #$data=array(
    #    array(),
    #    array("","name","greate"),
    #    array("","xuzhan","12"),
    #    array("","paopao","24")
    #);
    public function excel_export($data)
    {
        try {
            Vendor('PHPExcel/PHPExcel/IOFactory');
            $obj = new \PHPExcel();
            $objsheet=$obj->getActiveSheet();
#设置sheet的名称
            #$objsheet->setTitle("demo");
            $objsheet->fromArray($data);
#按照指定格式保存文件，保存文件
            header('Content-Type: application/vnd.ms-excel');
//下载的excel文件名称，为Excel5，后缀为xls，不过影响似乎不大
            header('Content-Disposition: attachment;filename="' . time() . '.xlsx"');
            header('Cache-Control: max-age=0');
            $objWriter=\PHPExcel_IOFactory::createWriter($obj,"Excel2007"); //通过PHPExcel_IOFactory的写函数将上面数据写出来
            $objWriter->save('php://output');
        } catch (Exception $e) {
            $this->ajaxReturn(array(
                'code' => -1,
                'msg' => $e->getMessage()
            ));
        }
    }

 4. 另外一种生成方式

public function excel_export($data)
    {
        try {
            Vendor('PHPExcel/PHPExcel/IOFactory');
            $obj = new \PHPExcel();
            $objsheet=$obj->getActiveSheet();
            #设置sheet的名称
            #$objsheet->setTitle("demo");
            $objsheet->fromArray($data);
            #按照指定格式保存文件，保存文件
            header('Content-Type: application/vnd.ms-excel');
            //下载的excel文件名称，为Excel5，后缀为xls，不过影响似乎不大
            header('Content-Disposition: attachment;filename="' . time() . '.xlsx"');
            header('Cache-Control: max-age=0');
            $objWriter=\PHPExcel_IOFactory::createWriter($obj,"Excel2007"); //通过PHPExcel_IOFactory的写函数将上面数据写出来
            $objWriter->save('php://output');
        } catch (Exception $e) {
            $this->ajaxReturn(array(
                'code' => -1,
                'msg' => $e->getMessage()
            ));
        }
    }