Excel导出功能参考

@PostMapping("/export")
@ApiOperation(value = "字典导出")
public void export(@RequestBody AcceptDictVO acceptDictVO, HttpServletResponse response){
    try {
        dictService.export(acceptDictVO,response);
    }catch (Exception e){
        CommonResult.fail("导出失败！"+e);
    }
}

@Override
public void export(AcceptDictVO acceptDictVO, HttpServletResponse response) {
    List<AcceptDict> acceptDicts = new ArrayList<>();
    List<AcceptDictExportVO> exportVoList = new ArrayList<>();
    //查询结果
    if (!CollectionUtils.isEmpty(acceptDictVO.getIds())){
        //选中记录查询
        QueryWrapper<AcceptDict> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("id",acceptDictVO.getIds());
        acceptDicts = dictMapper.selectList(queryWrapper);
    }else {
        acceptDicts = findListByParams(acceptDictVO);
    }
    acceptDicts.stream().forEach(s -> {
        //导出数据处理，设置顺序及excel样式
        AcceptDictExportVO exportVo = new AcceptDictExportVO();
        BeanUtils.copyProperties(s, exportVo);
        exportVoList.add(exportVo);
    });
    SsoUserModel userInfo = TokenUtils.getUserInfo();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
    String fileName = "数据字典配置列表+" + sdf.format(new Date()) + "+" + userInfo.getName();
    String sheetName = "Sheet1";
    try {
        //easyexcel
        ExcelUtil.writeExcel(response, exportVoList, fileName, sheetName, InspectDictExportVO.class);
    } catch (Exception e) {
        log.error("export: {}", e);
    }
}

@Override
public void export(AcceptDictVO acceptDictVO, HttpServletResponse response) {
    List<AcceptDict> acceptDicts = new ArrayList<>();
    List<AcceptDictExportVO> exportVoList = new ArrayList<>();
    //查询结果
    if (!CollectionUtils.isEmpty(acceptDictVO.getIds())){
        //选中记录查询
        QueryWrapper<AcceptDict> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("id",acceptDictVO.getIds());
        acceptDicts = dictMapper.selectList(queryWrapper);
    }else {
        acceptDicts = findListByParams(acceptDictVO);
    }
    acceptDicts.stream().forEach(s -> {
        //导出数据处理，设置顺序及excel样式
        AcceptDictExportVO exportVo = new AcceptDictExportVO();
        BeanUtils.copyProperties(s, exportVo);
        exportVoList.add(exportVo);
    });
    SsoUserModel userInfo = TokenUtils.getUserInfo();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
    String fileName = "数据字典配置列表+" + sdf.format(new Date()) + "+" + userInfo.getName();
    String sheetName = "Sheet1";
    try {
        //easyexcel
        ExcelUtil.writeExcel(response, exportVoList, fileName, sheetName, InspectDictExportVO.class);
    } catch (Exception e) {
        log.error("export: {}", e);
    }
}

   

ExcelUtil 工具类：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.core.io.ClassPathResource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
​
public class ExcelUtil {
    /**
     * 导出
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
    }
    
​
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
    fileName = URLEncoder.encode(fileName, "UTF-8");
    response.setContentType("application/vnd.ms-excel; charset=utf-8");
    response.setCharacterEncoding("utf-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
    return response.getOutputStream();
}
​
/**
 * EasyExcel工具，生成Excel文件
 * @param dataUpdateNotes 要填充的数据
 * @param response
 * @throws IOException
 */
public static <T> void writerExcel(T dataUpdateNotes, String filePath, String sheetName, String fileName, HttpServletResponse response) throws IOException {
    //1，找出模板文件，并转化为输入流：templateFileInputStream；
    org.springframework.core.io.Resource resource = new ClassPathResource(filePath);
    InputStream templateFileInputStream = resource.getInputStream();
    //2，outputStream：要导出的文件的输出流；
    OutputStream outputStream = response.getOutputStream();
    //4，设置输出流格式以及文件名：
    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    response.setCharacterEncoding("utf-8");
    fileName = URLEncoder.encode(fileName, "utf-8");
    response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
    response.setHeader("content-disposition", "attachment;filename=" + fileName  + ".xlsx");
    EasyExcel.write(outputStream).withTemplate(templateFileInputStream).sheet().doFill(dataUpdateNotes);
}

}