- easyExcel简单使用
/**
* id
*/
@ExcelIgnore
@ApiModelProperty(value = "id")
private Integer id;
/**
* 系统名称
*/
@ColumnWidth(15)
@ExcelProperty(value = "系统名称" ,index = 0)
@ApiModelProperty(value = "系统名称")
private String sysName;
/**
* 分页数据导出
* @return
*/
@GetMapping("/exportSystemPage")
@ApiOperation(value = "系统分页数据导出", response = SystemPageDto.class)
@ApiImplicitParams({
@ApiImplicitParam(dataType = "String", name = "sysName", value = "系统名称"),
@ApiImplicitParam(dataType = "Integer", name = "sysType", value = "系统类型:0-新建/1-已建"),
@ApiImplicitParam(dataType = "Integer", name = "state", value = "状态:0-建设中、1-运行中、2-停用、3-其它"),
@ApiImplicitParam(dataType = "String", name = "belongDept", value = "所属部门"),
@ApiImplicitParam(dataType = "Integer", name = "category", value = "类别(0-存量系统、1-关联系统)")
})
public void exportSystemPage(HttpServletResponse response, String sysName, Integer sysType, Integer state,
String belongDept, Integer category) throws UnsupportedEncodingException {
// 用户信息
// 获取这个人的身份,判断是不是管理中心帐号
String userDeptCode = SessionContext.getUserDeptCode();
String deptCode = null;
if (!topDeptCode.startsWith(userDeptCode)) {
deptCode = userDeptCode;
}
if (StringUtil.isNotBlank(sysName)) {
sysName = URLDecoder.decode(sysName, StandardCharsets.UTF_8.name());
}
if (StringUtil.isNotBlank(belongDept)) {
belongDept = URLDecoder.decode(belongDept, StandardCharsets.UTF_8.name());
}
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
String date = sdf.format(new Date());
List<SystemPageDto> systemList = systemService.getSystemList(sysName, sysType, state, belongDept, category, deptCode);
String fileName = "系统列表-" + date + ".xlsx";
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8").replace("+", " "));
EasyExcel.write(response.getOutputStream(), SystemPageDto.class).sheet("系统数据导出").doWrite(systemList);
} catch (Exception e) {
log.error("系统数据导出失败" + e.getMessage(), e);
}
}
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
- easyExcel解决多sheet导出的问题
package com.sdy.resdir.biz.dto;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.sdy.common.model.BaseModel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@ApiModel(description="下载部门下资源DTO")
public class TestOneDTO extends BaseModel {
@ExcelProperty(value = "部门名称" ,index = 0)
@ApiModelProperty(value = "资源id")
private Long resId;
@ExcelProperty(value = "部门名称" ,index = 1)
@ColumnWidth(20)
@ApiModelProperty(value = "部门名称")
private String deptName;
@ExcelProperty(value = "资源名称" ,index = 2)
@ColumnWidth(40)
@ApiModelProperty(value = "资源名称")
private String resName;
@ExcelProperty(value = "资源类型" ,index = 3)
@ColumnWidth(20)
@ApiModelProperty(value = "资源类型")
private String resTypeStr;
}
package com.sdy.resdir.biz.dto;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.sdy.common.model.BaseModel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@ApiModel(description="下载部门下资源DTO")
public class TestTwoDTO extends BaseModel {
@ExcelProperty(value = "部门名称" ,index = 0)
@ApiModelProperty(value = "资源id")
private Long resId;
@ExcelProperty(value = "资源名称" ,index = 1)
@ColumnWidth(40)
@ApiModelProperty(value = "资源名称")
private String resName;
@ExcelProperty(value = "资源权限" ,index = 2)
@ColumnWidth(10)
@ApiModelProperty(value = "资源权限")
private String resPowerStr;
@ExcelProperty(value = "资源层级" ,index = 3)
@ColumnWidth(10)
@ApiModelProperty(value = "资源层级")
private String resLevelStr;
}
@ApiOperation(value = "数源单位下资源下载")
@GetMapping("/downLoadCollectDeptRes")
public void downLoadCollectDeptRes(HttpServletResponse response) throws BizException {
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
String date = sdf.format(new Date());
String fileName = "资源列表-" + date + ".xlsx";
List<RdResourceDir> rdResourceDirList = rdResourceDirService.list(Wrappers.<RdResourceDir>lambdaQuery()
.in(RdResourceDir::getId, 2, 3, 6, 8, 10));
ArrayList<TestOneDTO> testOneDTOS = new ArrayList<>();
ArrayList<TestTwoDTO> testTwoDTOS = new ArrayList<>();
for (RdResourceDir rdResourceDir : rdResourceDirList) {
TestOneDTO testOneDTO = new TestOneDTO();
testOneDTO.setResId((long)rdResourceDir.getId());
testOneDTO.setDeptName(rdResourceDir.getResSourceDept());
testOneDTO.setResName(rdResourceDir.getResName());
testOneDTO.setResTypeStr(String.valueOf(rdResourceDir.getResType()));
testOneDTOS.add(testOneDTO);
TestTwoDTO testTwoDTO = new TestTwoDTO();
testTwoDTO.setResId((long)rdResourceDir.getId());
testTwoDTO.setResName(rdResourceDir.getResName());
testTwoDTO.setResPowerStr(String.valueOf(rdResourceDir.getResPower()));
testTwoDTO.setResLevelStr(String.valueOf(rdResourceDir.getResLevel()));
testTwoDTOS.add(testTwoDTO);
}
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8").replace("+", " "));
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0, "one").head(TestOneDTO.class).build();
excelWriter.write(testOneDTOS, writeSheet);
WriteSheet writeSheetTwo = EasyExcel.writerSheet(1, "two").head(TestTwoDTO.class).build();
excelWriter.write(testOneDTOS, writeSheetTwo);
response.flushBuffer();
excelWriter.finish();
} catch (Exception e) {
log.error("系统数据导出失败" + e.getMessage(), e);
}
}