目录
- 一、动态下拉框
- 二、合并行单元格
- 三、复杂表头
- 四、批量生成文件上传到文件服务器,再从文件服务器批量下载压缩成压缩包后导出
一、动态下拉框
如何得到这样一张表格?在单元格中插入可选下拉框。
思路分析:
① excel 表头每个字段对应实体的每个字段
② 表头字段是从注解 @ExcelProperty的value中取得的(参考alibaba的写excel方法)
③那么,是否可以自定义1个注解,通过这个注解得到下拉框的值
使用的工具类:easyexcel
第一步,自定义一个注解
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExplicitConstraint {
Class<? extends EasyExcelSelect> sourceClass();
String value() default "";
}
第二步,定义一个抽象类
public abstract class EasyExcelSelect {
public abstract String[] selectValue(Object value);
}
第三步,定义一个实现类
public class DictEasySelect extends EasyExcelSelect {
@Override
public String[] selectValue(Object value) {
if(ObjectUtils.allNotNull(value)){
RemoteDataDictService remoteDataDictService = SpringUtils.getBean(RemoteDataDictService.class);
List<DictTreeVo> list = remoteDataDictService.findDictList(value.toString());
if(StringUtils.isNotEmpty(list)){
return list.stream().map(DictTreeVo::getDictName).toArray(String[]::new);
}
}
return null;
}
}
第四步,new一个实体,并在需要下拉的字段上引用该注解
@ApiModel("试题批量导入模板")
@ColumnWidth(value = 10)
@HeadRowHeight(5)
@ContentRowHeight(5)
@HeadFontStyle(fontHeightInPoints = 12)
public class TopicTemplateDto {
@ApiModelProperty(value = "试题类型", required = true)
@ExcelProperty(value = "试题类型",index = 3)
@ExplicitConstraint(sourceClass = DictEasySelect.class,value = Constants.SUBJECT_TYPE)
private Integer topicType;
}
第五步,实现excel导出功能
public static ExportVo writeExcel(String fileName, Class clazz) {
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
Map<Integer, String[]> map = new HashMap<>();
Field[] fields = clazz.getDeclaredFields();
fields = Arrays.stream(fields).filter(field -> null != field.getAnnotation(ExcelProperty.class)).toArray(Field[]::new);
try {
getSelect(map, fields);
} catch (Exception e) {
}
ExcelWriter excelWriter = EasyExcel.write(byteArrayOutputStream, clazz).registerWriteHandler(
new SheetWriteHandler() {
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
map.forEach((k, v) -> {
CellRangeAddressList rangeAddressList = new CellRangeAddressList(1, 1000, k, k);
DataValidationConstraint dataValidationConstraint = dataValidationHelper.createExplicitListConstraint(v);
DataValidation validation = dataValidationHelper.createValidation(dataValidationConstraint, rangeAddressList);
if (validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
} else {
validation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(validation);
});
}
}
).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
excelWriter.write(null, sheet).finish();
return new ExportVo(fileName, byteArrayOutputStream.toByteArray());
}
getSelect方法
public static void getSelect(Map<Integer, String[]> maps, Field[] fields) throws IllegalAccessException, InstantiationException {
ExcelProperty excelProperty;
ExplicitConstraint explicitConstraint;
Class<? extends EasyExcelSelect> selectClass;
EasyExcelSelect easyExcelSelect;
String[] value;
for (int i = 0; i < fields.length; i++) {
excelProperty = fields[i].getAnnotation(ExcelProperty.class);
explicitConstraint = fields[i].getAnnotation(ExplicitConstraint.class);
if (null != explicitConstraint) {
selectClass = explicitConstraint.sourceClass();
easyExcelSelect = selectClass.newInstance();
value = easyExcelSelect.selectValue(explicitConstraint.value());
if (null != value && value.length > 0) {
if (excelProperty.index() == -1) {
maps.put(i, value);
} else {
maps.put(excelProperty.index(), value);
}
}
}
}
}
二、合并行单元格
法一:
CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow,endRow,firstCol,lastCol);
法二:
easypoi实现表格导出(实现一对多,合并单元格)
三、复杂表头
EasyExcel.write(fileName)
.head(header).sheet(sheetName)
.doWrite(dataList);
easyexcel的.head(header)方法接受一个List<List>对象作为动态表头:
List 对应一列,T对应该列中每行单元格的数据。在列表中的位置下标对应excel中的位置。
例如:3行5列的表头
List<List<String>> dataList = new ArrayList<>();
List<List<String>> header = new ArrayList<>();
List<String> col1 = new ArrayList<>();
col1.add("T11");
col1.add("T12");
col1.add("T13");
header.add(col1);
List<String> col2 = new ArrayList<>();
col2.add("T11");
col2.add("T12");
col2.add("T23");
header.add(col2);
List<String> col3 = new ArrayList<>();
col3.add("T11");
col3.add("T32");
col3.add("T23");
header.add(col3);
List<String> col4 = new ArrayList<>();
col4.add("T41");
col4.add("T32");
col4.add("T23");
header.add(col4);
List<String> col5 = new ArrayList<>();
col5.add("T41");
col5.add("T52");
col5.add("T53");
header.add(col5);
EasyExcel.write(fileName)
.head(header).sheet(sheetName)
.doWrite(dataList);
四、批量生成文件上传到文件服务器,再从文件服务器批量下载压缩成压缩包后导出
1.批量生成文件
try {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
byte[] bytes = outputStream.toByteArray();
outputStream.close();
MultipartFile file = new MockMultipartFile("file", “newFileName”, ContentType.APPLICATION_OCTET_STREAM.toString(), bytes);
files.add(file);
} catch (Exception e) {
e.printStackTrace();
}
2.批量下载excel打包成压缩包
ByteArrayOutputStream bos = new ByteArrayOutputStream();
ZipOutputStream zos = new ZipOutputStream(bos, Charset.forName("utf-8"));
ByteArrayInputStream bis;
Map<String, byte[]> maps = this.remoteFileService.downloadFiles(userIds, FileTypeEnums.ARCHIVES_FILE.getCode());
try {
if (StringUtils.isNotEmpty(maps)) {
for (Map.Entry<String, byte[]> entry : maps.entrySet()) {
bis = new ByteArrayInputStream(entry.getValue());
zos.putNextEntry(new ZipEntry(entry.getKey()));
int temp;
while ((temp = bis.read()) != -1) {
zos.write(temp);
}
bis.close();
}
}
zos.close();
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
return new ExportVo(Constants.DRIVER_ARCHIVE_FILE_ZIP, bos.toByteArray());
3.FeignClient内部调用文件服务,①要在注解里加consumes说明 ②参数用@requestPart注解
@ApiOperation(value = "批量处理文件到文件服务器")
@PostMapping( value = "/file/upload",consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
public ResultVo uploadFileList(@RequestPart("relevanceDtos")List<MultipartFile> files, @RequestParam("revelanceIds") List<Long> revelanceIds){
return this.fileService.uploadFileList(files, revelanceIds);
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)