phpexcel操作

读取EXCEL内容并替换内容

if(strrpos($jijiaurl,'xlsx')){
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        }else{
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }
        // 读取文件
        try{
            $PHPReader = $objReader->load(CMF_ROOT.$jijiaurl);
        }catch(Exception $e){
            dump($e);
            exit;
        }
        if(!isset($PHPReader)){
            return ['status'=>0, '读取错误'];
        }
        $page = $PHPReader->getSheet(0); //读取第一个工作表

        $objTestSheet=$PHPReader->setActiveSheetIndex(0);
        //写入值  不用考虑合并单元格问题  直接写入就行了
        $objTestSheet->setCellValue('B3', $house['name']);
        //导出
        $objWriter = \PHPExcel_IOFactory::createWriter($PHPReader, 'Excel2007');
        header('Content-Disposition: attachment;filename="'计价单'.date('YmdHis',time()).'.xlsx"');//下载下来的表格名
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $objWriter->save("php://output"); 
}

分享EXCEL导入导出类

<?php
// +----------------------------------------------------------------------
// | Excel导入导出模型
// +----------------------------------------------------------------------
namespace app\common\model;
use think\Exception;
use think\Model;

class ExcelModel extends Model
{
    /**
     * 导出Excel
     * @param $file_name 文件名
     * @param $head_arr 表头
     * @param $data 导出的数据
     */
    public function exportExcel($fileName, $headArr, $data)
    {
        if(empty($data) || !is_array($data)){
            die('数据不能为空');
        }
        $date = date("Y_m_d", time());
        $fileName .= "_{$date}.xls";

        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
        $cacheSettings = array('memoryCacheSize'  => '8MB');
        \PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings);
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        // 设置表头
        $key = 0;
        foreach($headArr as $v){
            $colum = \PHPExcel_Cell::stringFromColumnIndex($key);
            $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
            $key += 1;
        }
        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();
        // 行写入
        foreach($data as $key => $rows){
            $span = 0;
            // 列写入
            foreach($rows as $keyName=>$value){
                $j = \PHPExcel_Cell::stringFromColumnIndex($span);
                $objActSheet->setCellValue($j.$column, $value);
                if(strlen($value)>10){
                    $objActSheet->setCellValueExplicit($j.$column, $value,\PHPExcel_Cell_DataType::TYPE_STRING);
                }
                $span++;
            }
            $column++;
        }

        $fileName = iconv("utf-8", "gb2312", $fileName);
        // 重命名表
        $objPHPExcel->getActiveSheet()->setTitle($date);
        // 设置字体大小
        $objPHPExcel->getDefaultStyle()->getFont()->setSize(14);
        // 设置单元格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(15);
        // 设置默认行高
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(23);
        // 设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //dump($objWriter);exit;
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 导入Excel
     * @param array $file 文件
     * @return Excel数据
     */
    public function importExcel($file)
    {
        // 判断读取的Excel版本
        if(strrpos($file,'xlsx')){
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        }else{
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }
        // 读取文件
        try{
            $PHPReader = $objReader->load($file);
        }catch(Exception $e){
            dump($e);
            exit;
        }
        if(!isset($PHPReader)){
            return ['status'=>0, '读取错误'];
        }
        $currentSheet = $PHPReader->getSheet(0); //读取第一个工作表
        $allColumn = $currentSheet->getHighestColumn(); //总列
        $allRow = $currentSheet->getHighestRow(); //总行
        // 从第二行开始输出
        for($currentRow = 2;$currentRow <= $allRow;$currentRow++) {
            // 从第A列开始输出
            for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
                //数据坐标
                $address = $currentColumn . $currentRow;
                //读取到的数据，保存到数组$arr中
                $data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
            }
        }
        return $data;
    }
}