封装读取excel内容方法

    /**

     * 获取Excel内容

     * @param type $filename

     * @return type

     */

    public function getExcelContent($filename) {

        //读取内容

        $objPHPExcel = \PHPExcel_IOFactory::load($filename);

        $objPHPExcel->setActiveSheetIndex(0);

        $sheet1 = $objPHPExcel->getSheet(0);

        //获取行数，并把数据读取出来$data数组

        $rowCount = $sheet1->getHighestRow(); //excel行数

        $data = array();

        $this->column = 100; //最大列数

        for ($i = 1; $i <= $rowCount; $i++) {

            for ($j = 0; $j < $this->column; $j++) {

                $value = (string)$sheet1->getCellByColumnAndRow($j, $i)->getValue();

                if ($i == 1 && empty($value)) {

                    $this->column = $j; //当第一列遇到空的时候替换最大列数

                } else {

                    $data[$i - 1][$j] = $value; //获取每一列的值

                }

            }

        }

        return $data;

    }

封装excel导出方法

<?php

/*

 * To change this license header, choose License Headers in Project Properties.

 * To change this template file, choose Tools | Templates

 * and open the template in the editor.

 */

namespace app\common\model;

use think\Model;

use think\Db;

/**

 * Description of OrderSourceController

 *

 * @author zhangjx <zhangjx882@sina.com>

 * @date 2017-9-8

 */

class Excel extends Model {

    /**

     * Excel列数getExcelContent()专用

     */

    protected $column;

    private function checkExcel($fileName, $fileAddress, $loadFileAddress){

        if (empty($fileName))

            return false;

        set_time_limit(0);

        ini_set('memory_limit', '512M');

        // 检测文件夹如果不存在就生成

        Directory($fileAddress);

        vendor('excel.PHPExcel');

//        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory;

//        $cacheSettings = array('memoryCacheSize'=>'50MB');

//        \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        if ($loadFileAddress == null) {

            $objPHPExcel = new \PHPExcel();

        } else {

            $objReader = \PHPExcel_IOFactory::createReader("Excel2007");

            $objReader->setIncludeCharts(TRUE);

            $objPHPExcel = $objReader->load($loadFileAddress);

        }

        return $objPHPExcel;

    }

    /**

     * 导出excel 公共方法 若表头一致可采用

     * @param array $xlsCellName        excel表列名

     * @param array $xlsCellValue       excel表列值

     * @param string $fileName          文件名称

     * @param string $fileAddress       文件保存地址

     * @param integer $beginNum         开始写数据的行数

     * @param null $loadFileAddress     是否读取其他文件作为模板配置

     * @return string

     */

    public function exportExcel($xlsCellName, $xlsCellValue, $fileName, $fileAddress, $beginNum = 1, $loadFileAddress = null) {

        $objPHPExcel = $this->checkExcel($fileName, $fileAddress, $loadFileAddress);

        // 合并数据

        $xlsData = array_merge(array($xlsCellName), $xlsCellValue);

        foreach ($xlsData as $num => $data) {

            $row = $num + $beginNum;

            $chr = 0;

            foreach ($data as $val) {

                $col = self::IntToChr($chr);

                $chr++;

                $objPHPExcel->getActiveSheet(0)->setCellValue($col . $row, $val);

            }

        }

        header('pragma:public');

        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $fileName . '.xls"');

        header("Content-Disposition:attachment;filename=$fileName.xls"); //attachment新窗口打印inline本窗口打印

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

//        $objWriter->save('php://output');

        $objWriter->save($fileAddress . $fileName . '.xls');

        return $_SERVER['REQUEST_SCHEME'] . "://" . $_SERVER['HTTP_HOST'] . ltrim($fileAddress, '.') . $fileName . '.xls'; //增加后缀

    }

    /**

     * 数字转字母 （类似于Excel列标）

     * @param integer $index 索引值

     * @param integer $start 字母起始值

     * @return String 返回字母

     */

    public static function IntToChr($index, $start = 65) {

        $str = '';

        if (floor($index / 26) > 0) {

            $str .= self::IntToChr(floor($index / 26) - 1);

        }

        return $str . chr($index % 26 + $start);

    }