fastadmin 数据导出，设置excel行高和限制图片大小

起因是一个项目的图片导出到excel之后太大了，需要调整一下大小，这个fastadmin自带的前端导出就可以实现，但是也是比较复杂的，需要搞清楚图片的代码

在require-table.js这个文件里面找到exportOptions，添加一下代码

htmlContent: true,
// 处理导出图片
onCellHtmlData: function ($cell, row, col, htmlData) {
    var html = $.parseHTML(htmlData);
    var inputidx = 0;
    var selectidx = 0;

    var result = '';
    $.each(html, function () {
        if ($(this).is("input")) {
            result += $cell.find('input').eq(inputidx++).val();
        }else if ($(this).is("select")) {
            result += $cell.find('select option:selected').eq(selectidx++).text();
        }else if ($(this).is("a")) {
            // 这里就是设置图片大小的代码
            if($(this).context.childNodes[0].tagName=='IMG'){
                let str = $(this).context.childNodes[0]
                str.width = "60";
                str.height = "60";    
            }
            result += $(this).html();
        } else {
            if (typeof $(this).html() === 'undefined') {
                result += $(this).text();
            }else if (jQuery().bootstrapTable === undefined || ($(this).hasClass('filterControl') !== true && $cell.parents('.detail-view').length === 0)) {
                result += $(this).html();
            }
        }
    });
    return result;
},

---

但是如果想要设置excel的行高是不够的，需要自定义服务端导出才可以

首先，设置界面导出按钮样式

在相关控制器的index.html添加，记得改控制器的名称

<a href="javascript:;" class="btn btn-success btn-export {:$auth->check('question/export')?'':'hide'}" title="{:__('Export')}" id="btn-export-file"><i class="fa fa-download"></i> {:__('Export')}</a>

然后在该js文件里，在var table = $("#table");后面添加，这里也要记得修改控制器的名称

$(document).on("click", ".btn-export", function () {
    var ids = Table.api.selectedids(table);
    var page = table.bootstrapTable('getData');
    var all = table.bootstrapTable('getOptions').totalRows;
    console.log(ids, page, all);
    Layer.confirm("请选择导出的选项<form action='" + Fast.api.fixurl("question/export") + "' method='post' target='_blank'><input type='hidden' name='ids' value='' /><input type='hidden' name='filter' ><input type='hidden' name='op'><input type='hidden' name='search'><input type='hidden' name='columns'></form>", {
        title: '导出数据',
        btn: ["选中项(" + ids.length + "条)", "本页(" + page.length + "条)", "全部(" + all + "条)"],
        success: function (layero, index) {
            $(".layui-layer-btn a", layero).addClass("layui-layer-btn0");
        }
        , yes: function (index, layero) {
            submitForm(ids.join(","), layero);
            return false;
        }
        ,
        btn2: function (index, layero) {
            var ids = [];
            $.each(page, function (i, j) {
                ids.push(j.id);
            });
            submitForm(ids.join(","), layero);
            return false;
        }
        ,
        btn3: function (index, layero) {
            submitForm("all", layero);
            return false;
        }
    })
});
var submitForm = function (ids, layero) {
    var options = table.bootstrapTable('getOptions');
    console.log(options);
    var columns = [];
    $.each(options.columns[0], function (i, j) {
        if (j.field && !j.checkbox && j.visible && j.field != 'operate') {
            columns.push(j.field);
        }
    });
    var search = options.queryParams({});
    $("input[name=search]", layero).val(options.searchText);
    $("input[name=ids]", layero).val(ids);
    $("input[name=filter]", layero).val(search.filter);
    $("input[name=op]", layero).val(search.op);
    $("input[name=columns]", layero).val(columns.join(','));
    $("form", layero).submit();
};

最后在php文件里，添加导出方法

public function export(){
    if ($this->request->isPost()) {
        set_time_limit(0);
        $search = $this->request->post('search');
        $ids = $this->request->post('ids');
        $filter = $this->request->post('filter');
        $op = $this->request->post('op');
        $columns = $this->request->post('columns');

        $spreadsheet = new Spreadsheet();

        $spreadsheet->getProperties()
            ->setCreator("FastAdmin")
            ->setLastModifiedBy("FastAdmin")
            ->setTitle("标题")
            ->setSubject("Subject");
        $spreadsheet->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);

        $worksheet = $spreadsheet->setActiveSheetIndex(0);
        $whereIds = $ids == 'all' ? '1=1' : ['id' => ['in', explode(',', $ids)]];
        $this->request->get(['search' => $search, 'ids' => $ids, 'filter' => $filter, 'op' => $op]);
        list($where, $sort, $order, $offset, $limit) = $this->buildparams();
        
        $line = 1;

        //设置过滤方法
        $this->request->filter(['strip_tags']);

        //如果发送的来源是Selectpage，则转发到Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        list($where, $sort, $order, $offset, $limit) = $this->buildparams();
        $total = $this->model
            ->where($whereIds)
            ->order($sort, $order)
            ->count();

       $list = $this->model
            ->where($whereIds)
            ->order($sort, $order)
            ->limit($offset, $limit)
            ->select(); 

        $list = collection($list)->toArray();
        $result = array("total" => $total, "rows" => $list);
        
        $first = array_keys($list[0]);
        foreach ($first as $index => $item) {
            $worksheet->setCellValueByColumnAndRow($index, 1, __($item));
        }
        
        $worksheet = $spreadsheet->getActiveSheet();     //指向激活的工作表
        $worksheet->getColumnDimension('B')->setWidth(50);
        $worksheet->getColumnDimension('H')->setWidth(15);
        $worksheet->getColumnDimension('J')->setAutoSize(true);
        $worksheet->getColumnDimension('K')->setWidth(50);
        $worksheet->getColumnDimension('L')->setWidth(15);
        $worksheet->getColumnDimension('N')->setWidth(20);
        $worksheet->getColumnDimension('O')->setWidth(20);
         //设置表头
        $worksheet->setCellValue('A1', '序号');
        $worksheet->setCellValue('B1', '问题图片');
        $worksheet->setCellValue('C1', '所属村(社区)');
        $worksheet->setCellValue('D1', '区域分类');
        $worksheet->setCellValue('E1', '问题分类');
        $worksheet->setCellValue('F1', '角色');
        $worksheet->setCellValue('G1', '发现人');
        $worksheet->setCellValue('H1', '发现人手机号');
        $worksheet->setCellValue('I1', '地点');
        $worksheet->setCellValue('J1', '描述');
        $worksheet->setCellValue('K1', '整改图片');
        $worksheet->setCellValue('L1', '整改人手机号');
        
        
        $worksheet->setTitle('数据表');

        
        foreach ($list as $key => &$val) {
            $i=$key+2;//表格是从2开始的
            $worksheet->getRowDimension($i)->setRowHeight(100);
            
            //多个图片需用遍历new
			// 多图导出
        	$num1 = 10;
	        if (!empty($val['images'])) {
	            $images = explode(",", $val['images']);
	            foreach ($images as $k => $v){
	                // 获取本地文件夹路径
	                $str = explode('uploads',$v);
	                $str2 = explode('/',$str[1]);
	                $dir = ROOT_PATH . 'public/uploads/' .$str2[0];
	                $image = ROOT_PATH . 'public/uploads' .$str[1];
	
	                // 过滤非文件类型
	                if (!empty($file_info['basename'])) {
	                    $basename = $file_info['basename'];
	                    // 进行检测文件是否存在
	                    is_dir($dir) OR mkdir($dir, 0777, true);
	                    $res = file_put_contents($dir . $basename, file_get_contents($image));
						// 引入操作图片类
	                    $drawings[$i] = new Drawing();
	                    $drawings[$i]->setResizeProportional(false); // TODO 此处顺序不可调，因为导出默认是按原图像缩放的，设置成false才可以设置成可控制的宽度，要注意哦！
	                    $drawings[$i]->setName('图片');
	                    $drawings[$i]->setDescription('图片');
	                    $drawings[$i]->setPath($image);
	                    $drawings[$i]->setWidth(60);
	                    $drawings[$i]->setHeight(60);
	                    $drawings[$i]->setOffsetX($num1);
	                    $drawings[$i]->setOffsetY(10);
	                    $drawings[$i]->setCoordinates('B' . $i);
	                    $drawings[$i]->setWorksheet($worksheet);
	                }
	
	                $num1 = $num1 + 70; // 增加每张图之间的间距
	            }
	        }else{
	            $worksheet->setCellValue('B' . $i, '');
	        }
	        
            //向模板表中写入数据
            $worksheet->setCellValue('A'.$i,$key+1);
            // $worksheet->setCellValue('B'.$i,$val['images']);
            $worksheet->setCellValue('C'.$i,$val['village_name']);
            $worksheet->setCellValue('D'.$i,$val['type_area_name']);
            $worksheet->setCellValue('E'.$i,$val['type_question_name']);
            $worksheet->setCellValue('F'.$i,$val['group_name']);
            $worksheet->setCellValue('G'.$i,$val['personnel_user']['username']);
            $worksheet->setCellValue('H'.$i,$val['personnel_user']['mobile']);
            $worksheet->setCellValue('I'.$i,$val['address']);
            $worksheet->setCellValue('J'.$i,$val['description']);
            // $worksheet->setCellValue('K'.$i,$val['corrective_images']);
            $worksheet->setCellValue('N'.$i,$i,$val['personnel_user_two']['username']);
            $worksheet->setCellValue('L'.$i,$val['personnel_user_two']['mobile']);
            $worksheet->setCellValue('M'.$i,$val['status_name']);
            $worksheet->setCellValue('N'.$i,$val['create_date']);
            $worksheet->setCellValue('O'.$i,$val['correctivetime']);
        }   
            
            
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        //下载文档
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'. date('Y-m-d') .'_test'.'.xls"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');     

        return;
    }
}

批量修改行高加上这句代码

$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(100);

如果是想要修改除了第一行的行高，就在循环里面添加

$spreadsheet->getActiveSheet()->getDefaultRowDimension($i)->setRowHeight(100);

修改单元格宽度

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(15);

单元格宽度自适应

$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);