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;
}