怎么样给网站的服务器续费,做网站服务,创意旅行社wordpress,兰州网站优化排名需求#xff1a;需要在导出excel时合并指定的单元格 ruoyi excel
项目基于若伊框架二次开发#xff0c;本着能用现成的就不自己写的原则#xff0c;先是尝试了Excel注解中needMerge属性 /*** 是否需要纵向合并单元格,应对需求:含有list集合单元格)*/public boolean needMer… 需求需要在导出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(timezoneGMT8, patternyyyy-MM-dd HH:mm:ss)DateTimeFormat(patternyyyy-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 ListCutterVO cutterChangePosition;Excel(name 累计分切米数)private ListCutterVO 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{ListCutterControl cutterControlList cutterControlService.getCutterControlList();//设置导出的数据表格式ListCutterControlVO 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);}ExcelUtilCutterControlVO util new ExcelUtilCutterControlVO(CutterControlVO.class);util.exportExcel(response,cutterControlVOList,切刀管控台账数据);
}
查看导出效果 黑人问号脸
突然想到别人都是采用的是一个List集合于是我注释了一个List此时效果如下 可以看到一个List效果是正常显示的数据获取和显示也是正常的。 若伊使用的Excel导出工具类底层采用apache poi 只能导出简单的excel表格涉及复杂excel表格或者需要自定义表格时就比较难操作
使用阿里的easyExcel来实现复杂excel表格的导出
easyExcel
首先引入POM依赖 dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion2.2.7/version/dependency
1、不合并单元格的写法
实体类
Getter
Setter
public class CutterControl {/** 主键 */ExcelIgnoreprivate 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(timezoneGMT8, patternyyyy-MM-dd HH:mm:ss)DateTimeFormat(patternyyyy-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{ListCutterControl cutterControlList cutterControlService.getCutterControlList();String fileName System.getProperty(user.dir) / System.currentTimeMillis() .xlsx;// 构建ExcelWriterExcelWriter excelWriter EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();// 构建sheetWriteSheet writeSheet EasyExcel.writerSheet(切刀管控台账数据).head(CutterControl.class).build();// 写sheetexcelWriter.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 {/** 主键 */ExcelIgnoreprivate 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(timezoneGMT8, patternyyyy-MM-dd HH:mm:ss)DateTimeFormat(patternyyyy-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{ListCutterControl 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开始
Getter
Setter
public class CutterControl {/** 主键 */ExcelIgnoreprivate 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(timezoneGMT8, patternyyyy-MM-dd HH:mm:ss)DateTimeFormat(patternyyyy-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代码
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 ReadListenerCutterControl() {/*** 单次缓存的数据量*/public static final int BATCH_COUNT 100;/***临时存储*/private ListCutterControl cachedDataList ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);Overridepublic void invoke(CutterControl data, AnalysisContext context) {cachedDataList.add(data);if (cachedDataList.size() BATCH_COUNT) {saveData();// 存储完成清理 listcachedDataList ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}Overridepublic void doAfterAllAnalysed(AnalysisContext context) {saveData();}/*** 加上存储数据库*/private void saveData() {cutterControlService.batchInsertCutterControl(cachedDataList);log.info({}条数据开始存储数据库, cachedDataList.size());log.info(存储数据库成功);}}).sheet().doRead();return R.ok();}
