需求:需要在导出excel时合并指定的单元格
ruoyi excel
项目基于若伊框架二次开发,本着能用现成的就不自己写的原则,先是尝试了@Excel注解中needMerge属性
/**
* 是否需要纵向合并单元格,应对需求:含有list集合单元格)
*/
public boolean needMerge() default false;
查了一圈别人的使用,大致是需要定义一个List集合,集合元素为对象,对象中的属性标注@Excel注解,并表明name属性
照葫芦画瓢
@Getter
@Setter
@ToString
public class CutterControlVO {
/** 主键 */
private Long id;
/** 工厂编码 */
@Excel(name = "工厂编码",needMerge = true)
private String factoryCode;
/** 产线编码 */
@Excel(name = "产线编码",needMerge = true)
private String productionLineCode;
/** 设备编号 */
@Excel(name = "设备编号",needMerge = true)
private String deviceNumber;
/** 设备名称 */
@Excel(name = "设备名称",needMerge = true)
private String deviceName;
@Excel(name = "检测刀具编码",needMerge = true)
private String cutterCode;
/** 换刀时间 */
@JsonFormat(timezone="GMT+8", pattern="yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
@Excel(name = "换刀时间",dateFormat = "yyyy-MM-dd HH:mm:ss",needMerge = true)
private Date cutterChangeTime;
/** 上刀数 */
@Excel(name = "上刀数",needMerge = true)
private Integer upperKnifeNumber;
/** 下刀数 */
@Excel(name = "下刀数",needMerge = true)
private Integer lowerKnifeNumber;
@Excel(name = "更换人员",needMerge = true)
private String modifyUser;
/** 备注 */
@Excel(name = "备注",needMerge = true)
private String remark;
@Excel(name = "换刀位置")
private List<CutterVO> cutterChangePosition;
@Excel(name = "累计分切米数")
private List<CutterVO> accumulatedCuttingMeters;
}
@Getter
@Setter
@ToString
public class CutterVO {
@Excel(name = "上刀左")
private Integer upperKnifeLeft;
@Excel(name = "上刀中")
private Integer upperKnifeCenter;
@Excel(name = "上刀右")
private Integer upperKnifeRight;
@Excel(name = "下刀左")
private Integer lowerKnifeLeft;
@Excel(name = "下刀中")
private Integer lowerKnifeCenter;
@Excel(name = "下刀右")
private Integer lowerKnifeRight;
}
@PostMapping("/export")
public void export(HttpServletResponse response) throws Exception{
List<CutterControl> cutterControlList = cutterControlService.getCutterControlList();
//设置导出的数据表格式
List<CutterControlVO> cutterControlVOList = new ArrayList<>();
CutterControlVO cutterControlVO = null;
for (CutterControl cutterControl : cutterControlList) {
cutterControlVO = new CutterControlVO();
CutterVO cutterPosition = new CutterVO();
cutterPosition.setUpperKnifeLeft(cutterControl.getCutterChangePositionUpperKnifeLeft());
cutterPosition.setUpperKnifeCenter(cutterControl.getCutterChangePositionUpperKnifeCenter());
cutterPosition.setUpperKnifeRight(cutterControl.getCutterChangePositionUpperKnifeRight());
cutterPosition.setLowerKnifeLeft(cutterControl.getCutterChangePositionLowerKnifeLeft());
cutterPosition.setLowerKnifeCenter(cutterControl.getCutterChangePositionLowerKnifeCenter());
cutterPosition.setLowerKnifeRight(cutterControl.getCutterChangePositionLowerKnifeRight());
CutterVO accumulatedCuttingMeters = new CutterVO();
accumulatedCuttingMeters.setUpperKnifeLeft(cutterControl.getAccumulatedCuttingMetersUpperKnifeLeft());
accumulatedCuttingMeters.setUpperKnifeCenter(cutterControl.getAccumulatedCuttingMetersUpperKnifeCenter());
accumulatedCuttingMeters.setUpperKnifeRight(cutterControl.getAccumulatedCuttingMetersUpperKnifeRight());
accumulatedCuttingMeters.setLowerKnifeLeft(cutterControl.getAccumulatedCuttingMetersLowerKnifeLeft());
accumulatedCuttingMeters.setLowerKnifeCenter(cutterControl.getAccumulatedCuttingMetersLowerKnifeCenter());
accumulatedCuttingMeters.setLowerKnifeRight(cutterControl.getAccumulatedCuttingMetersLowerKnifeRight());
BeanUtils.copyProperties(cutterControl,cutterControlVO);
cutterControlVO.setCutterChangePosition(Arrays.asList(cutterPosition));
cutterControlVO.setAccumulatedCuttingMeters(Arrays.asList(accumulatedCuttingMeters));
cutterControlVOList.add(cutterControlVO);
}
ExcelUtil<CutterControlVO> util = new ExcelUtil<CutterControlVO>(CutterControlVO.class);
util.exportExcel(response,cutterControlVOList,"切刀管控台账数据");
}
查看导出效果:
黑人问号脸?
突然想到别人都是采用的是一个List集合,于是我注释了一个List,此时效果如下:
可以看到,一个List效果是正常显示的,数据获取和显示也是正常的。
若伊使用的Excel导出工具类底层采用apache poi ,只能导出简单的excel表格,涉及复杂excel表格或者需要自定义表格时就比较难操作
使用阿里的easyExcel来实现复杂excel表格的导出
easyExcel
首先引入POM依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
1、不合并单元格的写法
实体类
@Getter
@Setter
public class CutterControl {
/** 主键 */
@ExcelIgnore
private Long id;
/** 工厂编码 */
@ExcelProperty(value = "工厂编码")
private String factoryCode;
/** 产线编码 */
@ExcelProperty(value = "产线编码")
private String productionLineCode;
/** 设备编号 */
@ExcelProperty(value = "设备编号")
private String deviceNumber;
/** 设备名称 */
@ExcelProperty(value = "设备名称")
private String deviceName;
@ExcelProperty(value = "检测刀具编码")
private String cutterCode;
@ExcelProperty(value = "上刀左")
private Integer cutterChangePositionUpperKnifeLeft;
@ExcelProperty(value = "上刀中")
private Integer cutterChangePositionUpperKnifeCenter;
@ExcelProperty(value = "上刀右")
private Integer cutterChangePositionUpperKnifeRight;
@ExcelProperty(value = "下刀左")
private Integer cutterChangePositionLowerKnifeLeft;
@ExcelProperty(value = "下刀中")
private Integer cutterChangePositionLowerKnifeCenter;
@ExcelProperty(value = "下刀右")
private Integer cutterChangePositionLowerKnifeRight;
@ExcelProperty(value = "上刀左")
private Integer accumulatedCuttingMetersUpperKnifeLeft;
@ExcelProperty(value = "上刀中")
private Integer accumulatedCuttingMetersUpperKnifeCenter;
@ExcelProperty(value = "上刀右")
private Integer accumulatedCuttingMetersUpperKnifeRight;
@ExcelProperty(value = "下刀左")
private Integer accumulatedCuttingMetersLowerKnifeLeft;
@ExcelProperty(value = "下刀中")
private Integer accumulatedCuttingMetersLowerKnifeCenter;
@ExcelProperty(value = "下刀右")
private Integer accumulatedCuttingMetersLowerKnifeRight;
/** 换刀时间 */
@JsonFormat(timezone="GMT+8", pattern="yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "换刀时间")
@ColumnWidth(20)
private Date cutterChangeTime;
/** 上刀数 */
@ExcelProperty(value = "上刀数")
private Integer upperKnifeNumber;
/** 下刀数 */
@ExcelProperty(value = "下刀数")
private Integer lowerKnifeNumber;
@ExcelProperty(value = "更换人员")
private String modifyUser;
/** 备注 */
@ExcelProperty(value = "备注")
private String remark;
}
@ExcelIgnore :设置表格忽略该属性
@ColumnWidth(20): 设置列宽
controller代码
@PostMapping("/export")
public void export(HttpServletResponse response) throws Exception{
List<CutterControl> cutterControlList = cutterControlService.getCutterControlList();
String fileName = System.getProperty("user.dir") + "/" + System.currentTimeMillis() + ".xlsx";
// 构建ExcelWriter
ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();
// 构建sheet
WriteSheet writeSheet = EasyExcel.writerSheet("切刀管控台账数据").head(CutterControl.class).build();
// 写sheet
excelWriter.write(cutterControlList, writeSheet);
excelWriter.finish();
}
结果:
2、自定义列合并策略
参考eastExcel文档可知,在实体类上添加属性即可实现我想要的效果
写Excel | Easy Excel
@Getter
@Setter
@EqualsAndHashCode
public class ComplexHeadData {
@ExcelProperty({"主标题", "字符串标题"})
private String string;
@ExcelProperty({"主标题", "日期标题"})
private Date date;
@ExcelProperty({"主标题", "数字标题"})
private Double doubleData;
}
再次照葫芦画瓢
@Getter
@Setter
public class CutterControl {
/** 主键 */
@ExcelIgnore
private Long id;
/** 工厂编码 */
@ExcelProperty(value = "工厂编码")
private String factoryCode;
/** 产线编码 */
@ExcelProperty(value = "产线编码")
private String productionLineCode;
/** 设备编号 */
@ExcelProperty(value = "设备编号")
private String deviceNumber;
/** 设备名称 */
@ExcelProperty(value = "设备名称")
private String deviceName;
@ExcelProperty(value = "检测刀具编码")
private String cutterCode;
@ExcelProperty({"换刀位置", "上刀左"})
private Integer cutterChangePositionUpperKnifeLeft;
@ExcelProperty({"换刀位置", "上刀中"})
private Integer cutterChangePositionUpperKnifeCenter;
@ExcelProperty({"换刀位置", "上刀右"})
private Integer cutterChangePositionUpperKnifeRight;
@ExcelProperty({"换刀位置", "下刀左"})
private Integer cutterChangePositionLowerKnifeLeft;
@ExcelProperty({"换刀位置", "下刀中"})
private Integer cutterChangePositionLowerKnifeCenter;
@ExcelProperty({"换刀位置", "下刀右"})
private Integer cutterChangePositionLowerKnifeRight;
@ExcelProperty({"累计分切米数", "上刀左"})
private Integer accumulatedCuttingMetersUpperKnifeLeft;
@ExcelProperty({"累计分切米数", "上刀中"})
private Integer accumulatedCuttingMetersUpperKnifeCenter;
@ExcelProperty({"累计分切米数", "上刀右"})
private Integer accumulatedCuttingMetersUpperKnifeRight;
@ExcelProperty({"累计分切米数", "下刀左"})
private Integer accumulatedCuttingMetersLowerKnifeLeft;
@ExcelProperty({"累计分切米数", "下刀中"})
private Integer accumulatedCuttingMetersLowerKnifeCenter;
@ExcelProperty({"累计分切米数", "下刀右"})
private Integer accumulatedCuttingMetersLowerKnifeRight;
/** 换刀时间 */
@JsonFormat(timezone="GMT+8", pattern="yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "换刀时间")
@ColumnWidth(20)
private Date cutterChangeTime;
/** 上刀数 */
@ExcelProperty(value = "上刀数")
private Integer upperKnifeNumber;
/** 下刀数 */
@ExcelProperty(value = "下刀数")
private Integer lowerKnifeNumber;
@ExcelProperty(value = "更换人员")
private String modifyUser;
/** 备注 */
@ExcelProperty(value = "备注")
private String remark;
}
其余不用修改
效果如下:
若需要导出excel在浏览器,修改Controller代码如下:
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception{
List<CutterControl> cutterControlList = cutterControlService.getCutterControlList();
String fileName = new String("切刀管控台账数据.xlsx");
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName );
EasyExcel.write(response.getOutputStream(),CutterControl.class)
.sheet("切刀管控台账数据")
.doWrite(cutterControlList);
}
3、自定义行合并策略
具体业务暂时用不到,需要的可参考这个博客
https://www.cnblogs.com/monianxd/p/16359369.html
导入excel并处理同名列
由于实体类中存在excel列名重复的情况,所以如果不进行处理,会出现只有一个有值的情况,最简单的处理方式如下:
同名列添加所在位置的索引,默认从0开始文章来源:https://www.toymoban.com/news/detail-805171.html
@Getter
@Setter
public class CutterControl {
/** 主键 */
@ExcelIgnore
private Long id;
/** 工厂编码 */
@ExcelProperty(value = "工厂编码")
private String factoryCode;
/** 产线编码 */
@ExcelProperty(value = "产线编码")
private String productionLineCode;
/** 设备编号 */
@ExcelProperty(value = "设备编号")
private String deviceNumber;
/** 设备名称 */
@ExcelProperty(value = "设备名称")
private String deviceName;
@ExcelProperty(value = "检测刀具编码")
private String cutterCode;
@ExcelProperty(value = {"换刀位置", "上刀左"},index = 5)
private Integer cutterChangePositionUpperKnifeLeft;
@ExcelProperty(value = {"换刀位置", "上刀中"},index = 6)
private Integer cutterChangePositionUpperKnifeCenter;
@ExcelProperty(value = {"换刀位置", "上刀右"},index = 7)
private Integer cutterChangePositionUpperKnifeRight;
@ExcelProperty(value = {"换刀位置", "下刀左"},index = 8)
private Integer cutterChangePositionLowerKnifeLeft;
@ExcelProperty(value = {"换刀位置", "下刀中"},index = 9)
private Integer cutterChangePositionLowerKnifeCenter;
@ExcelProperty(value = {"换刀位置", "下刀右"},index = 10)
private Integer cutterChangePositionLowerKnifeRight;
@ExcelProperty(value = {"累计分切米数", "上刀左"},index = 11)
private Integer accumulatedCuttingMetersUpperKnifeLeft;
@ExcelProperty(value = {"累计分切米数", "上刀中"},index = 12)
private Integer accumulatedCuttingMetersUpperKnifeCenter;
@ExcelProperty(value = {"累计分切米数", "上刀右"},index = 13)
private Integer accumulatedCuttingMetersUpperKnifeRight;
@ExcelProperty(value = {"累计分切米数", "下刀左"},index = 14)
private Integer accumulatedCuttingMetersLowerKnifeLeft;
@ExcelProperty(value = {"累计分切米数", "下刀中"},index = 15)
private Integer accumulatedCuttingMetersLowerKnifeCenter;
@ExcelProperty(value = {"累计分切米数", "下刀右"},index = 16)
private Integer accumulatedCuttingMetersLowerKnifeRight;
/** 换刀时间 */
@JsonFormat(timezone="GMT+8", pattern="yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "换刀时间")
@ColumnWidth(20)
private Date cutterChangeTime;
/** 上刀数 */
@ExcelProperty(value = "上刀数")
private Integer upperKnifeNumber;
/** 下刀数 */
@ExcelProperty(value = "下刀数")
private Integer lowerKnifeNumber;
@ExcelProperty(value = "更换人员")
private String modifyUser;
/** 备注 */
@ExcelProperty(value = "备注")
private String remark;
}
导入Controller代码文章来源地址https://www.toymoban.com/news/detail-805171.html
@PostMapping("/import")
public R importData(@RequestParam(value = "file") MultipartFile file) throws IOException {
String fileName = file.getOriginalFilename();
String suffixName = fileName.substring(fileName.lastIndexOf("."));
if (!(suffixName.equals(".xlsx"))) {
return R.fail("请上传xlsx格式文件");
}
EasyExcel.read(file.getInputStream(), CutterControl.class, new ReadListener<CutterControl>() {
/**
* 单次缓存的数据量
*/
public static final int BATCH_COUNT = 100;
/**
*临时存储
*/
private List<CutterControl> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
@Override
public void invoke(CutterControl data, AnalysisContext context) {
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
}
/**
* 加上存储数据库
*/
private void saveData() {
cutterControlService.batchInsertCutterControl(cachedDataList);
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
log.info("存储数据库成功!");
}
}).sheet().doRead();
return R.ok();
}
到了这里,关于Java导出Excel并合并单元格的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!