list类型文件下载 下载excel表格 List＜List＜string＞＞类型 设置excel格式为文本(数据和每列都可设为文本)

@ApiOperation(value = "下载excel模板", notes = "下载特性excel模板")
    @PostMapping(value = "/downloadTemp")
    public ResultEntity<Object> downloadExcel(HttpServletResponse response) {

        attributeService.exportExcel(response);

        return ResultEntity.success();
    }


 public void exportExcel(HttpServletResponse response) {

        AttributeQueryDTO a = new AttributeQueryDTO();
        a.setPageNum(1);
        a.setPageSize(10000);
        AttributeDTO attributeList = getAttributeList(a);

        List<AttributeModelDTO> headerList = attributeList.getHeaderList();
        List<String> collect = headerList.stream().map(h -> h.getAttributeName()).collect(Collectors.toList());
        String[] objects = collect.toArray(new String[collect.size()]);
        AttributeExcelUtils.noModleWrite("CustomerAttribute",objects,attributeList.getPagedata().getList(),response);

    }



public AttributeDTO getAttributeList(AttributeQueryDTO attributeQueryDTO) {

        AttributeDTO attributeDTO = new AttributeDTO();
        //返回结果
        PageData<List<String>> poPageData = new PageData<>();
        List<AttributeModelDTO> dtoList = new ArrayList<>();
        List<List<String>> list3 = new ArrayList<>();

        // TODO: 2022/7/18 0018 查询用户权限可看到的字段headerList
        List<AttributeModelPO> list = attributeDao.getHeaderList();

        if (CollectionUtils.isNotEmpty(list)) {
            for (AttributeModelPO modelPO : list) {
                AttributeModelDTO attributeModelDTO = JSONUtil.obj2Bean(modelPO, AttributeModelDTO.class);
                dtoList.add(attributeModelDTO);
            }
        }

        //查询数据
        AttributePO queryPO = JSONUtil.obj2Bean(attributeQueryDTO, AttributePO.class);
        PageInfo<AttributeValueAggPO> poPageInfo = PageHelper.startPage
                (queryPO.getPageNum(), queryPO.getPageSize(), queryPO.getSort()).
                doSelectPageInfo(() -> attributeDao.getAttributeList(queryPO));

        List<AttributeValueAggPO> list1 = poPageInfo.getList();
        //数据存在 返回列头和数据
        if (CollectionUtils.isNotEmpty(list1)) {
            for (AttributeValueAggPO attributeValueAggPO : list1) {
                String attributeValueAgg = attributeValueAggPO.getAttributeValueAggregation();
                Map<String, String> map = JSONUtil.fromJson(attributeValueAgg, Map.class);
                List<String> datalist = new ArrayList<>();
                for (AttributeModelPO modelPO : list) {
                    String attributeCode = modelPO.getAttributeCode();
                    String data = map.get(attributeCode);
                    datalist.add(data);
                }
                list3.add(datalist);
            }
        }

        poPageData.setList(list3);
        poPageData.setTotal(poPageInfo.getTotal());
        poPageData.setPageNum(poPageInfo.getPageNum());
        poPageData.setPageSize(poPageInfo.getPageSize());

        attributeDTO.setHeaderList(dtoList);
        attributeDTO.setPagedata(poPageData);

        return attributeDTO;
    }



 public static void noModleWrite(String name,String[] header,List<List<String>> dataList,HttpServletResponse response) {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(name, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(
                    response.getOutputStream()).
                    head(head(header)).
                    registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)).
                    registerWriteHandler(new AttributeColumnConfig()).
                    sheet(name).
                    doWrite(dataList);
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
        }
    }

//设置表头
    private  static List<List<String>> head(String[] headMap) {
        List<List<String>> list = new ArrayList<List<String>>();

        for (String head : headMap) {
            List<String> headList = new ArrayList<String>();
            headList.add(head);
            list.add(headList);
        }
        return list;
    }

    @Override
    public int getColumn(){
        int size = attributeDao.getHeaderList().size();
        return size;
    }







import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class AttributeColumnConfig implements CellWriteHandler {

    private static AttributeService attributeService;

    @Autowired
    public void setAttributeService(AttributeService attributeService) {
        AttributeColumnConfig.attributeService = attributeService;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

        Workbook workBook = writeSheetHolder.getSheet().getWorkbook();
        Sheet sheet = workBook.getSheetAt(0);
        CellStyle textStyle = workBook.createCellStyle();
        DataFormat format = workBook.createDataFormat();
        //设置已存在数据为文本格式
        textStyle.setDataFormat(format.getFormat("@"));
        //设置每一列style  每一列非文本数据也都为文本格式
        //for (int i =0;i<head.getHeadNameList().size();i++){  //旧版本 headernameList长度会有问题
        //for (int i =0;i<attributeService.getColumn();i++){ //新版本 长度正常
            //sheet.setDefaultColumnStyle(i, textStyle);
        //}
//最新版本: 2023-5-11 通过调试发现每次head只会一个格子一个格子传值，所以这块不用循环只需要用下标进行赋值文本格式
        sheet.setDefaultColumnStyle(head.getColumnIndex()，textstyle):
        cell.setCellStyle(textStyle);

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

}



// excel工具包
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
            <exclusions>
                <exclusion>
                    <groupId>javax.servlet</groupId>
                    <artifactId>servlet-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
        </dependency>