1.引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
2.添加模板加入resoures目录下
3.创建导出VO类添加excel注解
package com.qiang.service.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.converters.string.StringImageConverter;
import com.qiang.covert.StateCodeConverter;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.File;
import java.io.InputStream;
import java.time.LocalDateTime;
/**
* @Description StartUserVO
* @Author lizhq
* @Date 2022/5/6 13:57
**/
@AllArgsConstructor
@NoArgsConstructor
@Data
public class StartUserVO {
private static final long serialVersionUID = 1L;
@ExcelProperty(value = "序号")
private Integer no;
@ExcelProperty(value = "用户名")
private String userName;
@ExcelProperty(value = "密码")
private String password;
@ExcelProperty(value = "电子邮箱")
private String email;
@ExcelProperty(value = "手机号")
private String phone;
// @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
// @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "注册时间")
private LocalDateTime registrationDate;
@ExcelProperty(value = "注册IP地址")
private String registrationIp;
@ExcelProperty(value = "密码试错次数")
private Integer trialErrorTimes;
@ExcelProperty(value = "状态码",converter = StateCodeConverter.class)
@ApiModelProperty(value = "状态码:0全都未验证;1邮箱验证;2手机验证;3邮箱和手机都验证")
private Integer stateCode;
@ExcelProperty(value = "问题照片1")
private File path1;
@ExcelProperty(value = "问题照片2")
private InputStream path2;
@ExcelProperty(value = "问题照片4",converter = StringImageConverter.class)
private String path4;
@ExcelProperty(value = "问题照片3")
private byte[] path3;
}
3.导出代码service
@Override
public void exportExcel(HttpServletResponse response) {
AtomicInteger i= new AtomicInteger();
ArrayList<StartUserVO> StartUsers1 = new ArrayList<>();
List<StartUser> StartUsers = StartUserMapper.selectList(new LambdaQueryWrapper<StartUser>().eq(StartUser::getIsBuiltIn,0));
StartUsers.stream().forEach(user -> {
StartFile startFile = fileMapper.selectOne(new LambdaQueryWrapper<StartFile>().eq(StartFile::getEntityId, user.getUuid()));
StartUserVO StartUserVO = new StartUserVO();
InputStream inputStream = null;
try {
inputStream = FileUtils.openInputStream(new File(startFile.getPath()));
} catch (IOException e) {
e.printStackTrace();
}
StartUserVO.setPath1(new File(startFile.getPath()));
StartUserVO.setPath2(inputStream);
try {
StartUserVO.setPath3(FileUtils.readFileToByteArray(new File(startFile.getPath())));
} catch (IOException e) {
e.printStackTrace();
}
StartUserVO.setPath4(startFile.getPath());
StartUserVO.setNo(i.incrementAndGet());
BeanUtils.copyProperties(user, StartUserVO);
StartUsers1.add(StartUserVO);
});
//生成excel
// 填充list 的时候还要注意 模板中{.} 多了个点 表示list
String templatePath = "/template/用户.xlsx";
InputStream templateInputStream = this.getClass().getResourceAsStream(templatePath);
response.setHeader(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_OCTET_STREAM_VALUE);
try {
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode("用户-导出.xlsx", "utf-8"));
EasyExcel.write(response.getOutputStream(), StartUserVO.class)
.withTemplate(templateInputStream)
.sheet().registerWriteHandler(new CustomCellWriteHandler()).registerWriteHandler(new CustomRowWriteHandler())
.doFill(StartUsers1);
}
catch (IOException e) {
e.printStackTrace();
}
}
4.导出controller
@GetMapping("/template/easyExcelPort")
public void index5(HttpServletResponse response) throws Exception {
userService.exportExcel(response);
}
5.结果
6.其中用到了图片,背景颜色,序号
6.1 图片 添加完这些代码后图片就会显示出来
6.1.1实体类
@ExcelProperty(value = "问题照片1")
private File path1;
@ExcelProperty(value = "问题照片2")
private InputStream path2;
@ExcelProperty(value = "问题照片4",converter = StringImageConverter.class)
private String path4;
@ExcelProperty(value = "问题照片3")
private byte[] path3;
6.1.2service
StartUsers.stream().forEach(user -> {
StartFile startFile = fileMapper.selectOne(new LambdaQueryWrapper<StartFile>().eq(StartFile::getEntityId, user.getUuid()));
StartUserVO StartUserVO = new StartUserVO();
InputStream inputStream = null;
try {
inputStream = FileUtils.openInputStream(new File(startFile.getPath()));
} catch (IOException e) {
e.printStackTrace();
}
StartUserVO.setPath1(new File(startFile.getPath()));
StartUserVO.setPath2(inputStream);
try {
StartUserVO.setPath3(FileUtils.readFileToByteArray(new File(startFile.getPath())));
} catch (IOException e) {
e.printStackTrace();
}
StartUserVO.setPath4(startFile.getPath());
StartUserVO.setNo(i.incrementAndGet());
BeanUtils.copyProperties(user, StartUserVO);
StartUsers1.add(StartUserVO);
});
6.2根据值动态设置背景颜色 添加完这些代码后背景颜色就会显示出来
6.2.1 实体类
@ExcelProperty(value = "状态码",converter = StateCodeConverter.class)
@ApiModelProperty(value = "状态码:0全都未验证;1邮箱验证;2手机验证;3邮箱和手机都验证")
private Integer stateCode;
6.2.2 自定义转换器
package com.qiang.covert;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.qiang.enumerate.StateCodeEnum;
/**
* @Description StateCodeConverter
* @Author lizhq
* @Date 2022/5/12 10:48
**/
public class StateCodeConverter implements Converter<Integer> {
@Override
public Class<Integer> supportJavaTypeKey() {
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public WriteCellData<String> convertToExcelData(Integer status, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new WriteCellData<String>(StateCodeEnum.findByCode(status).getName());
}
}
6.2.3枚举类
package com.qiang.enumerate;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.qiang.util.EnumUtils;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import java.util.Map;
@AllArgsConstructor
@NoArgsConstructor
@Getter
public enum StateCodeEnum implements Coded<Object> {
ALLNOVERIFY(0,"全都未验证",(short)2),
EMAILNOVERIFY(1,"邮箱验证",(short)5),
PHONENOVERIFY(2,"手机验证",(short)53),
EMAILANDPHONENOVERIFY(3,"邮箱和手机都验证",(short)3);
private Integer code;
private String name;
private Short backColor;
private static final Map<Object, StateCodeEnum> map = EnumUtils.createValuedEnumMap2(StateCodeEnum.class, item-> item.getCode());
private static final Map<Object, StateCodeEnum> Stringmap = EnumUtils.createValuedEnumMap2(StateCodeEnum.class, item-> item.getName());
public static void main(String[] args) {
StateCodeEnum anEnum = StateCodeEnum.findByName("全都未验证");
System.out.println(anEnum.backColor);
StateCodeEnum anEnum1 = StateCodeEnum.findByCode(0);
System.out.println(anEnum1.backColor);
}
public static StateCodeEnum findByCode(@JsonProperty("code") Integer code) {
return map.get(code);
}
public static StateCodeEnum findByName(@JsonProperty("name") String name) {
return Stringmap.get(name);
}
}
6.2.4设置自定义拦截器如果不设置背景颜色将不会显示
package com.qiang.config.easyexcel;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import com.qiang.enumerate.StateCodeEnum;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.springframework.stereotype.Component;
@Component
public class CustomCellWriteHandler extends AbstractCellStyleStrategy {
@Override
protected void setContentCellStyle(CellWriteHandlerContext context) {
Cell cell = context.getCell();
if (cell.getColumnIndex() == 5){
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景绿色
contentWriteCellStyle.setFillForegroundColor(StateCodeEnum.findByName(cell.getStringCellValue())==null?0:StateCodeEnum.findByName(cell.getStringCellValue()).getBackColor());
WriteFont contentWriteFont = new WriteFont();
// 字体大小
// contentWriteFont.setFontHeightInPoints((short)20);
contentWriteCellStyle.setWriteFont(contentWriteFont);
WriteCellData<?> cellData = context.getFirstCellData();
WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());
}
}
}
6.2.5service注入自定义拦截器,不清楚看3.有完整代码
EasyExcel.write(response.getOutputStream(), StartUserVO.class)
.withTemplate(templateInputStream)
.sheet().registerWriteHandler(new CustomCellWriteHandler()).registerWriteHandler(new CustomRowWriteHandler())
.doFill(StartUsers1);
6.3设置序号
6.3.1实体类
@ExcelProperty(value = "序号")
private Integer no;
6.3.2service 不清楚看3.有完整代码
AtomicInteger i= new AtomicInteger();
StartUserVO.setNo(i.incrementAndGet());