springboot项目excel按照模板导出：Jxls导出excel

上弦暮光

已于 2023-09-19 16:36:40 修改

阅读量1.1k

点赞数

于 2023-09-19 15:56:41 首次发布

本文链接：https://blog.csdn.net/qq_44769705/article/details/133027221

版权

step1：定义excel导出模板

创建.xls文件，对应数据库中的列表构建模板，注意一定要加批注

模板中的语法参考

使用xls或xlsx模板(jxls语法)导出Excel_jxls1.0.6对应文档_justry_deng的博客-CSDN博客

创建名为：project_template的xls文件

放在项目目录下面：

在两个红色小三角的单元格里面增加：批注

增加的两个内容如下图所示：

Date类型的传递：

这里面的utils和dateFmt会在下面的代码中看到

step2：项目中导入依赖

        <!--jxls-->
		<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls</artifactId>
			<version>[2.6.0-SNAPSHOT,)</version>
		</dependency>
		<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls-poi</artifactId>
			<version>[1.2.0-SNAPSHOT,)</version>
		</dependency>
		<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls-jexcel</artifactId>
			<version>[1.0.8,)</version>
		</dependency>
		<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls-reader</artifactId>
			<version>[2.0.5,)</version>
		</dependency>

step3：项目中的代码

3.1 JxlsUtils：JXLS的工具类，包括导出等功能


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.jexl3.JexlBuilder;
import org.apache.commons.jexl3.JexlEngine;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;


public class JxlsUtils {
    private static final String TEMPLATE_PATH = "jxls-template";// 在target/classes目录下面的目录，专门用于存放模板

    /**
     * 导出excel函数
     * @param is
     * @param os
     * @param model
     * @throws IOException
     */
    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
        Context context = new Context();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer = jxlsHelper.createTransformer(is, os);
        JexlExpressionEvaluator evaluator =
            (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
        Map<String, Object> funcs = new HashMap<String, Object>();
        funcs.put("utils", new JxlsUtils()); // 添加自定义功能，对应着excel模板中的utils
        // evaluator.getJexlEngine().setFunctions(funcs);这个函数在新的版本里已经不适用了，删掉就行

        /**
         * 这里需要注意，只加在map里面是不生效的，一定要跟后面的代码，才正确
         */
        JexlBuilder jb = new JexlBuilder();
        jb.namespaces(funcs);
        JexlEngine je = jb.create();
        evaluator.setJexlEngine(je);

        jxlsHelper.processTemplate(context, transformer);
    }

    /**
     * 本地的excel导出
     * @param xls
     * @param out
     * @param model
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static void exportExcel(File xls, File out, Map<String, Object> model)
        throws FileNotFoundException, IOException {
        exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
    }

    /**
     * 适用于response的excel导出
     * @param templateName
     * @param os
     * @param model
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static void exportExcel(String templateName, OutputStream os, Map<String, Object> model)
        throws FileNotFoundException, IOException {
        File template = getTemplate(templateName);
        if (template != null) {
            exportExcel(new FileInputStream(template), os, model);
        }
    }
    
    /**
     * 获取jxls模版文件
     * @param name
     * @return
     */
    public static File getTemplate(String name) {
        // String templatePath = JxlsUtils.class.getClassLoader().getResource(TEMPLATE_PATH).getPath();
        URL resource = JxlsUtils.class.getClassLoader().getResource(TEMPLATE_PATH);
        String templatePath = resource.getPath();
        File template = new File(templatePath, name);
        if (template.exists()) {
            return template;
        }
        return null;
    }

    /**
     * 日期格式化函数，对应着excel模板中的dateFmt
     * （自定义功能）
     * @param date
     * @param fmt
     * @return
     */
    public String dateFmt(Date date, String fmt) {
        if (date == null) {
            return "";
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    // if判断
    public Object ifelse(boolean b, Object o1, Object o2) {
        return b ? o1 : o2;
    }
}

3.2 ProjectController

主要就是分为两步：

第一步：通过查询数据库返回一个List作为结果，用于传入到excel中去

查询的单个结果为ProjectVo类，所以总体查询结果为一个List<ProjectVo>

@Autowired
    private ProjectAdminService projectService;

@ApiOperation(value = "34、export导出")
    @GetMapping(value = "/exportExcelJxls", produces = "application/octet-stream")
    public void exportExcelJxls(ProjectQuery query, HttpServletResponse response) throws IOException {

        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");

        String fileName = URLEncoder.encode("项目管理", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

        List<ProjectVo> projectVoList = this.projectService.listForExport(query);
        //按照id进行排序，否则查询到的是逆序的
        projectVoList.sort(Comparator.comparing(ProjectVo::getId));
        Map<String, Object> model = new HashMap<String, Object>();
        model.put("projectVoList", projectVoList);
        model.put("nowdate", new Date());
        JxlsUtils.exportExcel("project_template.xls", response.getOutputStream(), model);
    }

3.3 ProjectAdminService

@Slf4j
@Service
public class ProjectAdminService extends MpServiceImpl<ProjectMapper, Project> {

    @Autowired
    private VoDataKitService voDataKitService;

    @Resource
    private RedisCacheClient redisCacheClient;

    public PageInfo<ProjectVo> listPage(ProjectQuery query) {
        log.info("ProjectAdminService->listPage,query={}", query);
        List<Project> modelList = this.doPage(query);
        PageInfo<Project> page = new PageInfo<>(modelList);
        List<ProjectVo> voList = BeanKit.buildList(modelList, ProjectVoFunction.one());
        return PageKit.buildPage(page, voList);
    }

    private List<Project> doPage(ProjectQuery query) {
        LambdaQueryWrapper<Project> queryWrapper = this.buildQueryWrapper(query);
        CsPermissionKit.projectPermission(queryWrapper);
        PageKit.startPage(query);
        List<Project> modelList = super.list(queryWrapper);
        return modelList;
    }

    private LambdaQueryWrapper<Project> buildQueryWrapper(ProjectQuery query) {
        Project queryModel = new Project();
        queryModel.setIsDeleted(YnEnum.NO.getCode());
        LambdaQueryWrapper<Project> queryWrapper = Wrappers.lambdaQuery(queryModel);
        if (StringUtils.isNotEmpty(query.getKeyword())) {
            String keywordTrim = query.getKeyword().trim();
            Consumer<LambdaQueryWrapper<Project>> consumer = (LambdaQueryWrapper<Project> wrapper) -> {
                wrapper.like(Project::getCode, keywordTrim).or().like(Project::getName, keywordTrim);
            };
            queryWrapper.and(consumer);
        }
        queryWrapper.orderByDesc(Project::getId);
        return queryWrapper;
    }

    public ProjectVo getDetail(Integer id) {
        Project entity = super.getById(id);
        ProjectVo vo = ProjectVoFunction.one().apply(entity);
        return vo;
    }

    

    public boolean updateIsValid(Integer id, Integer isValid) {
        Project model = new Project();
        model.setId(id);
        model.setIsValid(isValid);
        model.setUpdateTime(DateKit.now());
        boolean succeed = super.updateById(model);
        this.refreshCache(id);
        return succeed;
    }


    /**
     * 缓存变化的时候，刷新
     */
    private void refreshCache(Integer id) {
        ProjectVo vo = this.getDetail(id);
        if (vo == null) {
            return;
        }
        String key = vo.getCode();
        if (StringUtils.isNotEmpty(key)) {
            this.redisCacheClient.delete(CsCacheKit.projectCacheKey(key));
        }

    }

    public List<ProjectVo> listForExport(ProjectQuery query) {
        // 导出设置最大数量
        query.setPageNo(1);
        query.setPageSize(100);

        List<Project> modelList = this.doPage(query);
        List<ProjectVo> voList = BeanKit.buildList(modelList, ProjectVoFunction.one());

        return voList;
    }
}

3.4 ProjectVoFunction

public class ProjectVoFunction implements Function<Project,ProjectVo> {

	@Override
	public ProjectVo apply(Project model) {
		ProjectVo vo = new ProjectVo();
		BeanKit.copyProperties(model, vo);
		return vo;
	}
	
	public static ProjectVoFunction one(){
		return new ProjectVoFunction();
	}
}

3.4.1 BeanKit的copyProperties函数、buildList函数

public class BeanKit extends BeanUtil {
    private static final Logger log = LoggerFactory.getLogger(BeanKit.class);
    private static boolean init = false;

    static {
        if (!init) {
            initApacheCommonsConvertUtils();
        }

    }

    public BeanKit() {
    }

    public static void initApacheCommonsConvertUtils() {
        ConvertUtils.register(new DateConverter((Object)null), Date.class);
        ConvertUtils.register(new LongConverter((Object)null), Long.class);
        ConvertUtils.register(new ShortConverter((Object)null), Short.class);
        ConvertUtils.register(new IntegerConverter((Object)null), Integer.class);
        ConvertUtils.register(new DoubleConverter((Object)null), Double.class);
        ConvertUtils.register(new BigDecimalConverter((Object)null), BigDecimal.class);
    }

    public static <T> T build(Object source, Class<T> destClazz) {
        if (source == null) {
            return null;
        } else {
            try {
                T bean = destClazz.newInstance();
                copyProperties(source, bean);
                return bean;
            } catch (Exception var3) {
                var3.printStackTrace();
                return null;
            }
        }
    }

    public static <S, T> T build(S source, Function<S, T> function) {
        return function.apply(source);
    }

    public static <S, T> T build(S source, Function<S, T> function, boolean copyProperties) {
        T target = function.apply(source);
        if (copyProperties) {
            copyProperties(source, target);
            target = function.apply(source);
        }

        return target;
    }

    public static <T> List<T> buildList(List<?> sourceList, Class<T> destClazz) {
        if (CollectionUtils.isEmpty(sourceList)) {
            return Lists.newArrayList();
        } else {
            List<T> destList = Lists.newArrayList();
            Iterator var4 = sourceList.iterator();

            while(var4.hasNext()) {
                Object source = var4.next();
                T bean = build(source, destClazz);
                destList.add(bean);
            }

            return destList;
        }
    }

    public static <S, T> List<T> buildList(List<S> sourceList, Function<S, T> function) {
        return buildList(sourceList, function, false);
    }

    public static <S, T> List<T> buildList(List<S> sourceList, Function<S, T> function, boolean copyProperties) {
        if (CollectionUtils.isEmpty(sourceList)) {
            return Lists.newArrayList();
        } else {
            List<T> destList = Lists.newArrayList();
            Iterator var5 = sourceList.iterator();

            while(var5.hasNext()) {
                S source = (Object)var5.next();
                T bean = build(source, function, copyProperties);
                destList.add(bean);
            }

            return destList;
        }
    }

    public static <T> List<T> buildListByMapList(List<Map<String, Object>> mapList, Class<T> destClazz) {
        if (CollectionUtils.isEmpty(mapList)) {
            log.error("convertMapToEntity mapRowList is empty");
            return Lists.newArrayList();
        } else {
            List<T> beanList = Lists.newArrayList();
            Iterator var4 = mapList.iterator();

            while(var4.hasNext()) {
                Map<String, Object> map = (Map)var4.next();

                try {
                    T bean = destClazz.newInstance();
                    copyProperties(map, bean);
                    beanList.add(bean);
                } catch (Exception var6) {
                    var6.printStackTrace();
                }
            }

            return beanList;
        }
    }

    public static void fillBean(Map<String, Object> source, Object target) {
        copyProperties(source, target);
    }

    public static void copyProperties(Map<String, Object> source, Object target) {
        try {
            BeanUtils.populate(target, source);
        } catch (Exception var3) {
            var3.printStackTrace();
        }

    }

    public static <T> void copyProperties(List<Map<String, Object>> mapList, List<T> targetBeanList, Class<T> destClazz) {
        if (CollectionUtils.isEmpty(mapList)) {
            log.error("copyProperties mapList is empty");
        } else if (CollectionUtils.isEmpty(targetBeanList)) {
            log.error("copyProperties targetBeanList is empty");
        } else {
            int targetListSize = targetBeanList.size();

            for(int index = 0; index < mapList.size(); ++index) {
                Map<String, Object> map = (Map)mapList.get(index);
                T target = null;
                if (index >= targetListSize) {
                    try {
                        target = destClazz.newInstance();
                        targetBeanList.add(target);
                    } catch (Exception var8) {
                        var8.printStackTrace();
                    }
                }

                if (target == null) {
                    target = targetBeanList.get(index);
                }

                copyProperties(map, target);
            }

        }
    }

    public static <T> void copyProperties(List<Map<String, Object>> mapList, List<T> targetBeanList) {
        if (CollectionUtils.isEmpty(mapList)) {
            log.error("copyProperties mapList is empty");
        } else if (CollectionUtils.isEmpty(targetBeanList)) {
            log.error("copyProperties targetBeanList is empty");
        } else {
            int targetListSize = targetBeanList.size();

            for(int index = 0; index < mapList.size(); ++index) {
                Map<String, Object> map = (Map)mapList.get(index);
                if (index >= targetListSize) {
                    log.info("mapList size > targetBeanList size");
                    break;
                }

                T target = targetBeanList.get(index);
                copyProperties(map, target);
            }

        }
    }

    public static void copyProperties(Object source, Object target) {
        try {
            BeanUtil.copyProperties(source, target, new String[0]);
        } catch (Exception var3) {
            var3.printStackTrace();
        }

    }
}

3.5 ProjectVo

@Data
public class ProjectVo {

	private Integer id;
	private String code;
	private String name;

	/**
	 * token_str
	 */
	private String tokenStr;
	private String remark;
	private Integer isValid;
	private Integer mailOpen;
	private Integer wechatOpen;
	private Integer signIsOpen;
	private String sign;
	private Integer fileOpen;
	private Integer mailSendSwitch;
	private String forwardMailList;
	private String forwardWechatList;

    private Integer urlClientOpen;
    private String csAuthToken;

    private Integer userCustomDataOpen;
    private Integer chartOpen;
    private Integer configOpen;
    //审计日志类的权限
    private Integer oplogOpen;
    private Integer commonUrlLogOpen;
    private Integer bizViewLogOpen;
    private Integer overstepPermissionLogOpen;
    
}