文章详细介绍了如何在SpringBoot项目中使用EasyExcel库进行文件导入和导出，包括单个sheet表格数据导入、多个sheet表格数据导入、带参数的文件导入、文件导出以及下载导入模板等操作。同时，文章还提到了文件上传的实现方法和获取预览下载地址的处理。
一、文件导入
1.单个sheet 表格数据导入
基于alibaba.easyexcel实现，这里直接上实现代码
// conrtoller 层
@PostMapping("/import")
@ApiOperation(value = "批量导入区域信息")
public ObjectRestResponse<ImportResultVo> importCreate(
@RequestParam(value = "file") MultipartFile file) {
ImportResultVo resultVo = areaService.importCreate(file);
return new ObjectRestResponse<>().data(resultVo);
}
/**
* service 层
* @author: xuyahui
* @Date: 2022/3/9 0:56
* @Description: 批量导入区域信息
*/
public ImportResultVo importCreate(MultipartFile file) {
try {
ModelExcelListener<AreaExcelVo> listener = new ModelExcelListener<>();
EasyExcel.read(file.getInputStream(), AreaExcelVo.class,listener).sheet(0).doRead();
List<AreaExcelVo> importList = listener.getList();
if (CollectionUtils.isEmpty(importList)){
return ImportResultVo.buildFail("导入失败，文件没有数据");
}
AreaExcelVo firstVo = importList.get(0);
List<String> desc = EasyExcelUtil.getDesc(AreaExcelVo.class);
if (!CollectionUtils.isEmpty(desc) && desc.get(0).equals(firstVo.getCode())){
//如果是描述，则移除
importList.remove(0);
}
List<AreaExcelVo> checkedList = new ArrayList<>();// 验证通过的数据
List<ImportErrorVo> errorList = new ArrayList<>();// 验证失败的错误信息集合
for (AreaExcelVo importVo : importList) {
String errorInfo = ValidatorUtil.validateReturnFirstError(importVo);
if (StringUtils.hasText(errorInfo)){
errorList.add(ImportErrorVo.build(importVo.getRowIndex(),errorInfo));
continue;
}
checkedList.add(importVo);
}
if (CollectionUtils.isEmpty(checkedList)){
return ImportResultVo.buildFail(errorList);
}
//业务校验 区域编码是否存在
Map<String, AreaExcelVo> importAreaMap = checkedList.stream().collect(Collectors.toMap(AreaExcelVo::getCode,
Function.identity(),(k1, k2) -> k1));
List<Area> areaList = areaMapper.listAll();
Map<String, Area> areaMap = areaList.stream().collect(Collectors.toMap(Area::getCode, Function.identity(), (k1, k2) -> k2));
Iterator<String> iterator = importAreaMap.keySet().iterator();
while (iterator.hasNext()) {
String importKey = iterator.next();
if (areaMap.containsKey(importKey)){
//数据库已存在
AreaExcelVo importVo = importAreaMap.get(importKey);
errorList.add(ImportErrorVo.build(importVo.getRowIndex(),"区域编码重复"));
iterator.remove();
importAreaMap.remove(importKey);
}
}
if (!CollectionUtils.isEmpty(errorList)){
List<ImportErrorVo> sortedErrorList = errorList.stream().sorted(Comparator.comparing(ImportErrorVo::getRowIndex)).collect(Collectors.toList());
return ImportResultVo.buildFail(sortedErrorList);
}
List<Area> saveAreaList = new ArrayList<>();
for (Map.Entry<String, AreaExcelVo> entry : importAreaMap.entrySet()) {
AreaExcelVo excelVo = entry.getValue();
Area area = Area.buildImportEntity(excelVo);
area.setId(SnowflakeIdUtil.nextId());
saveAreaList.add(area);
}
areaMapper.batchInsert(saveAreaList);
return ImportResultVo.buildSuccess(importAreaMap.size());
}catch (Exception e){
log.error("区域导入异常 ==>{}",e.getMessage(),e);
return ImportResultVo.buildFail(e.getMessage());
}
}下面是相关的验证和处理类
package com.spring.mes.common.util.easyexcel.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.spring.mes.common.util.easyexcel.EasyExcelUtil;
import com.spring.mes.pojo.msg.exception.BaseException;
import com.spring.mes.pojo.vo.basic.excel.ExcelModel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 实体类模板的导入
*
* @author maji
*/
@Slf4j
@Data
@EqualsAndHashCode(callSuper=false)
public class ModelExcelListener<T extends ExcelModel> extends AnalysisEventListener<T> {
//一次允许多少条数据导入
private int batchCount = 10000;
//用list集合保存解析到的结果
private List<T> list = new ArrayList<>();
//表头
private Map<Integer,Map<Integer,String>> headMap = new HashMap<>();
/**
* 取时，使用实体类读取表头，否则只会默认读第一行为表头
* EasyExcel.read(file.getInputStream(), 实体类.class,listener).sheet(0).doRead();
* @param excelHead
* @param context
*/
@Override
public void invokeHeadMap(Map<Integer, String> excelHead, AnalysisContext context) {
// 读取时，使用实体类读取表头，否则只会默认读第一行为表头
//EasyExcel.read(file.getInputStream(), 实体类.class,listener).sheet(0).doRead();
Integer rowIndex = context.readRowHolder().getRowIndex();
//生成表头并校验
Class tClass = context.currentReadHolder().excelReadHeadProperty().getHeadClazz();
if (CollectionUtils.isEmpty(headMap)){
headMap = EasyExcelUtil.getHeadMap(tClass);
}
//校验表头
Map<Integer, String> classHead = headMap.get(rowIndex);
if (!excelHead.equals(classHead)){
//此处异常会走到 onException方法，不管什么异常都会被转成 ExcelAnalysisException
throw new ExcelAnalysisException("表头校验异常，请使用正确的模板进行导入");
}
}
@Override
public void invoke(T data, AnalysisContext context) {
Integer rowIndex = context.readRowHolder().getRowIndex();
data.setRowIndex(rowIndex + 1);
list.add(data);
if (list.size() > batchCount) {
//此处异常会走到 onException方法，不管什么异常都会被转成 ExcelAnalysisException
throw new ExcelAnalysisException("导入条数超过限制，最多导入"+batchCount+"条");
}
}
/**
* 解析到最后会进入这个方法，需要则重写这个doAfterAllAnalysed方法
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (CollectionUtils.isEmpty(list)){
//此处异常不会走到 onException 方法
throw new ExcelAnalysisException("导入失败，文件没有数据");
}
}
/**
* 在有异常时，抛出异常则停止读取。如果这里不抛出异常则 会继续读取下一行。
*/
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
// 此处抛的异常都会在 AbstractReadHolder类中 转成 ExcelAnalysisException 异常
Integer rowIndex = context.readRowHolder().getRowIndex();
if (rowIndex == headMap.size()){
//如果是表头下一行的数据异常，放行，防止是因为 字段描述 造成的异常
return;
}
ExcelDataConvertException excelDataConvertException = null;
if (exception instanceof ExcelDataConvertException) {
excelDataConvertException = (ExcelDataConvertException) exception;
String errorConvert = String.format("第%s行第%s列，解析异常",excelDataConvertException.getRowIndex()+1,
excelDataConvertException.getColumnIndex()+1);
throw new ExcelAnalysisException(errorConvert);
} else{
throw exception;
}
}
}package com.spring.mes.common.util.easyexcel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.spring.mes.common.util.FieldTool;
import com.spring.mes.common.util.easyexcel.handler.ColumnAutoWidthHandler;
import com.spring.mes.common.util.easyexcel.handler.HiddenSheetSpinnerWriteHandler;
import com.spring.mes.pojo.ExcelFieldDesc;
import com.spring.mes.pojo.msg.exception.BaseException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
/**
* easyExcel 简单工具类
*
* @author maji
*/
@Slf4j
public class EasyExcelUtil {
public static String[] specialCharArr = {"[", "]", "?", "*", "/", "\\"};
/**
* 获取最基础的ExcelWriter
*
* @param response
* @param fileName 导出文件名
*/
public static ExcelWriter getExcelWriter(HttpServletResponse response, String fileName) {
try {
return getExcelWriterBuilder(response, fileName)
.registerWriteHandler(new ColumnAutoWidthHandler())
.registerWriteHandler(new HorizontalCellStyleStrategy(styleHead(), styleCells()))
.build();
} catch (Exception e) {
log.error("getExcelWriter fail ==>{}", e);
}
return null;
}
/**
* 获取ExcelWriterBuilder
*
* @param response
* @param fileName 导出文件名
*/
public static ExcelWriterBuilder getExcelWriterBuilder(HttpServletResponse response, String fileName) {
try {
return EasyExcel.write(getServletOutputStream(response, fileName));
} catch (Exception e) {
log.error("getExcelWriterBuilder fail ==>{}", e);
}
return null;
}
/**
* 获取输出流
*
* @param response
* @param fileName
* @return
* @throws IOException
*/
public static ServletOutputStream getServletOutputStream(HttpServletResponse response, String fileName) throws IOException {
fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
}
/**
* 导出模板
* @param response
* @param headClass
* @param fileSheetName
*/
public static void downloadTemplate(HttpServletResponse response,Class headClass,String fileSheetName) {
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcelUtil.getExcelWriter(response, fileSheetName);
WriteSheet writeSheet = EasyExcel.writerSheet()
.head(headClass)
.sheetName(fileSheetName)
.build();
//设置第二行为字段说明
excelWriter.write(Collections.singletonList(EasyExcelUtil.getDesc(headClass)), writeSheet);
excelWriter.finish();
}catch (Exception e) {
log.error("导出模板失败！", e);
throw new BaseException("导出模板失败");
}finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
/**
* 导出模板，单元格中设置下拉框选项
*/
public static void downloadTemplate(HttpServletResponse response,Class headClass,String fileSheetName,Map<Integer, List<String>> dropDownMap) {
ExcelWriter excelWriter = null;
try {
//选项放下拉框，第一行表头，第2行字段说明，第三行开始放下拉框
HiddenSheetSpinnerWriteHandler spinnerWriteHandler = HiddenSheetSpinnerWriteHandler.builder().dropDownMap(dropDownMap).firstRow(2).build();
excelWriter = EasyExcelUtil.getExcelWriterBuilder(response, fileSheetName)
.registerWriteHandler(new ColumnAutoWidthHandler())
.registerWriteHandler(new HorizontalCellStyleStrategy(EasyExcelUtil.styleHead(), EasyExcelUtil.styleCells()))
.registerWriteHandler(spinnerWriteHandler)
.build();
WriteSheet writeSheet = EasyExcel.writerSheet()
.head(headClass)
.sheetName(fileSheetName)
.build();
//设置第二行为字段说明
excelWriter.write(Collections.singletonList(EasyExcelUtil.getDesc(headClass)), writeSheet);
excelWriter.finish();
}catch (Exception e) {
log.error("导出模板失败！", e);
throw new BaseException("导出模板失败");
}finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
/**
* 导出固定表头的数据
*/
public static void exportData(HttpServletResponse response, String fileSheetName, Class headClass, List<? extends Object> dataList) {
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcelUtil.getExcelWriter(response, fileSheetName);
WriteSheet writeSheet = EasyExcel.writerSheet()
.head(headClass)
.sheetName(fileSheetName)
.build();
excelWriter.write(dataList, writeSheet);
excelWriter.finish();
}catch (Exception e) {
log.error("导出"+fileSheetName+"失败！", e);
throw new BaseException("导出数据失败");
}finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
/**
* 表头样式
* */
public static WriteCellStyle styleHead() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置背景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setBold(true);
headWriteFont.setFontHeightInPoints((short)14);
headWriteCellStyle.setWriteFont(headWriteFont);
// 设置边框
setBorder(headWriteCellStyle);
return headWriteCellStyle;
}
/**
* 内容样式
* */
public static WriteCellStyle styleCells() {
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
return setBorder(contentWriteCellStyle);
}
/**
* 设置边框
* */
private static WriteCellStyle setBorder(WriteCellStyle style) {
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
return style;
}
/**
* 获取模板实体类中的说明
* @param clazz
* @return
*/
public static List<String> getDesc(Class clazz) {
List<Field> fields = FieldTool.getDeclaredFields(clazz);
List<String> descList = new ArrayList<>();
for (Field field : fields) {
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
if (property != null) {
ExcelFieldDesc desc = field.getAnnotation(ExcelFieldDesc.class);
if (desc != null) {
descList.add(desc.value());
}else {
descList.add("");
}
}
}
return descList;
}
/**
* 从模板实体类中转换表头
* @param clazz
* @return
*/
public static Map<Integer,Map<Integer,String>> getHeadMap(Class clazz) {
List<Field> fields = FieldTool.getDeclaredFields(clazz);
Integer index = 0;
//正常只有一行表头，但可能也有多行，key 从0 开始
Map<Integer,Map<Integer,String>> headMap = new HashMap<>();
for (Field field : fields) {
ExcelIgnore ignore = field.getAnnotation(ExcelIgnore.class);
if (ignore != null){
continue;
}
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
if (property != null) {
String[] values = property.value();
for (int i = 0; i < values.length; i++) {
Map<Integer, String> headRow = headMap.get(i);
if (headRow == null){
headRow = new HashMap<>();
headMap.put(i,headRow);
}
headRow.put(index,values[i]);
}
index ++ ;
}
}
return headMap;
}
/**
* sheetName 去除特殊字符
*
* @param sheetName
* @return
*/
public static String checkSheetName(String sheetName) {
for (String s : specialCharArr) {
sheetName = StringUtils.replace(sheetName, s, "");
}
return sheetName;
}
/**
* 自定义表头
*
* @param headList sheet表头
*/
public static List<List<String>> getHead(Collection<String> headList) {
List<List<String>> list = new ArrayList<>();
if (headList != null) {
headList.forEach(h -> list.add(Collections.singletonList(h)));
}
return list;
}
}package com.spring.mes.common.util;
import lombok.experimental.UtilityClass;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import java.util.List;
import java.util.stream.Collectors;
@UtilityClass
public class ValidatorUtil {
private final Validator validator;
static {
validator = Validation.buildDefaultValidatorFactory().getValidator();
}
public List<String> validate(Object object, Class<?>... groups) {
return validator.validate(object, groups)
.stream().map(ConstraintViolation::getMessage)
.collect(Collectors.toList());
}
public static String validateReturnFirstError(Object object){
return validator.validate(object)
.stream().findFirst().map(ConstraintViolation::getMessage)
.orElse(null);
}
}
package com.spring.mes.pojo.vo.basic.excel;
import com.alibaba.excel.annotation.ExcelIgnore;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.List;
@Data
@NoArgsConstructor
public class ImportResultVo implements Serializable {
@ApiModelProperty(value = "是否导入成功")
private Boolean success;
@ApiModelProperty(value = "成功数量")
private Integer successNum;
@ApiModelProperty(value = "单独的异常信息")
private String errorMsg;
@ApiModelProperty(value = "异常行信息集合")
private List<ImportErrorVo> errorList;
@ApiModelProperty(value = "导入成功的对象集合")
private List<Object> list;
public static ImportResultVo buildFail(String errorMsg) {
ImportResultVo vo = new ImportResultVo();
vo.setSuccess(false);
vo.setErrorMsg(errorMsg);
return vo;
}
public static ImportResultVo buildFail(List<ImportErrorVo> errorList) {
ImportResultVo vo = new ImportResultVo();
vo.setSuccess(false);
vo.setErrorMsg("部分数据导入失败，请重新导入！");
vo.setErrorList(errorList);
return vo;
}
public static ImportResultVo buildSuccess(Integer successNum) {
ImportResultVo vo = new ImportResultVo();
vo.setSuccess(true);
vo.setSuccessNum(successNum);
return vo;
}
public static ImportResultVo buildItemList(List<Object> list) {
ImportResultVo vo = new ImportResultVo();
vo.setSuccess(true);
vo.setList(list);
vo.setSuccessNum(list.size());
return vo;
}
public static ImportResultVo buildErrAndOkItemList(List<ImportErrorVo> errorList,List<Object> list) {
ImportResultVo vo = new ImportResultVo();
vo.setSuccess(false);
vo.setErrorMsg("部分数据导入失败，请重新导入！");
vo.setErrorList(errorList);
vo.setSuccess(true);
vo.setList(list);
return vo;
}
}
2.多个sheet表格数据导入
@ApiOperation(value = "批量导入用户")
@PostMapping("/import")
@SysLog(value = "导入用户",module = LogModuleConstants.USER_CENTER,menu = LogMenuConstants.USER)
public ResponseBody importCreate(@RequestParam(value = "file",required = false) MultipartFile file) {
UserImportResultVo resultVo = userService.importCreate(file);
return ResponseBody.success(resultVo);
}
/**
* @author: xuyahui
* @Date: 2021/4/7 14:11
* @Description: 批量导入用户
*/
@Override
@Transactional(rollbackFor = Exception.class)
public UserImportResultVo importCreate(MultipartFile file) {
try {
InputStream in = file.getInputStream();
ExcelReader excelReader = EasyExcel.read(in).build();
PhoneListener phoneListener = new PhoneListener(this);
EmailListener emailListener = new EmailListener(this);
UsernameListener usernameListener = new UsernameListener(this);
//获取sheet对象
ReadSheet phoneSheet =
EasyExcel.readSheet(0).head(PhoneImportTemplate.class).registerReadListener(phoneListener).build();
ReadSheet emailSheet =
EasyExcel.readSheet(1).head(EmailImportTemplate.class).registerReadListener(emailListener).build();
ReadSheet usernameSheet =
EasyExcel.readSheet(2).head(UsernameImportTemplate.class).registerReadListener(usernameListener).build();
// 业务处理
excelReader.read(phoneSheet, emailSheet, usernameSheet);
return convertAndSave(phoneListener, emailListener, usernameListener);
} catch (IOException e) {
log.error("批量导入用户失败！");
}
return null;
}
/**
* @author: xuyahui
* @Date: 2021/5/8 15:49
* @Description: 转换并保存数据
*/
private UserImportResultVo convertAndSave(PhoneListener phoneListener,
EmailListener emailListener,
UsernameListener usernameListener) {
List<PhoneImportTemplate> phoneUserList = phoneListener.getData();
List<EmailImportTemplate> emailUserList = emailListener.getData();
List<UsernameImportTemplate> usernameUserList = usernameListener.getData();
// 验证表头
checkTableHead(phoneListener.getHeadMap(),emailListener.getHeadMap(),usernameListener.getHeadMap());
// 验证数据
checkImportUserInfo(phoneUserList, emailUserList, usernameUserList);
List<UserImportVo> userImportVoList = buildImportUserVoList(phoneUserList, emailUserList, usernameUserList);
SysOrganizationEntity company = organizationBiz.selectById(SessionBean.getTenantId());
// 处理数据
int successNum = 0;
List<SysOrganizationUserRel> organizationUserRels = null;
List<SysRoleUserRel> sysRoleUserRelList = null;
for (UserImportVo importUser : userImportVoList) {
// 查询组织机构和角色
organizationUserRels = new ArrayList<>();
sysRoleUserRelList = new ArrayList<>();
SysOrganizationEntity organization = organizationBiz.selectByOrgName(importUser.getOrganization());
SysRoleEntity roleEntity = sysRoleBiz.selectByNameAndTenantId(importUser.getRole(), SessionBean.getTenantId());
SysUserEntity user = ObjectCopyUtil.objectCopy(importUser, SysUserEntity.class);
initUser(user);
SysOrganizationUserRel companyUserRel = buildUserOrganizationRel(user.getId(), SessionBean.getTenantId(), null, SessionBean.getTenantId(), true);
organizationUserRels.add(companyUserRel);
if (!Objects.isNull(organization)) {
SysOrganizationUserRel orgRel = buildUserOrganizationRel(user.getId(), null, organization.getId(), SessionBean.getTenantId(), true);
organizationUserRels.add(orgRel);
}
if (!Objects.isNull(roleEntity)) {
SysRoleUserRel roleUserRel = SysRoleUserRel.create(user, roleEntity);
sysRoleUserRelList.add(roleUserRel);
}
// 查询验证角色是否有管理员
SysRoleEntity addAdminRole = getAdminRole(sysRoleUserRelList);
sysUserBiz.insertUser(user, organizationUserRels, sysRoleUserRelList, companyUserRel);
// 平台创建
try {
createPlatUser(user, company, organizationUserRels);
if (Objects.nonNull(addAdminRole)) {// 新增组织管理员
aclServiceFeign.addUserToOrganizationAdmin(SessionBean.getTenantId(), user.getId());
}
successNum++;
} catch (Exception e) {
sysUserBiz.deleteMetaUserById(user.getId());
sysUserBiz.deleteUserById(user.getId());
}
}
return UserImportResultVo.build(userImportVoList.size(), successNum);
}
3.带参数的文件导入
除了文件，还有对象信息的参数，请求参数可以放在form-data里面，具体示例如下：
（1）controller 类中代码写法
/**
* 上传模型
* @param abFile
* @param jpgFile
* @param sxEquipment
* @return
* @throws IOException
*/
@PreAuthorize("@ss.hasAnyPermi('asset:workshop:add,asset:device:add,asset:facilities:add')")
@PostMapping("/addEquipment")
public AjaxResult addEquipment(MultipartFile abFile, MultipartFile jpgFile,SxEquipment sxEquipment) throws IOException {
try {
List<SxEquipment> sxEquipmentlist = sxEquipmentService.selectListEq(sxEquipment);
if (sxEquipmentlist.size() > 0) return AjaxResult.error("设备已存在");
sxEquipment.setModelTime(DateUtils.getNowDate());
// sxEquipment.setCreateBy(SecurityUtils.getLoginUser().getUser().getUserName());
sxEquipment.setThumbTime(DateUtils.getNowDate());
sxEquipment.setCreateFlag("0");
sxEquipment.setBrowseNum(0);
sxEquipment.setDownloadNum(0);
// sxEquipment.setCompanyName("火星视觉数字科技有限公司");
sxEquipment.setCreateTime(DateUtils.getNowDate());
sxEquipmentService.save(sxEquipment);
if (Objects.nonNull(abFile)) {
// String abUrl = FileUploadUtils.mixstationA(abFile, 1, sxEquipment.getEquipmentId());
String abUrl = ossService.mixstation1(abFile, 1, sxEquipment.getEquipmentId());
sxEquipment.setModel(abUrl);
}
String jpgUrl = ossService.mixstation1(jpgFile, 0, sxEquipment.getEquipmentId());
sxEquipment.setPic(jpgUrl);
sxEquipmentService.updateEquipmentById(sxEquipment);
if (Objects.nonNull(sxEquipment.getPic())) sxEquipment.setThumbTime(DateUtils.getNowDate());
return AjaxResult.success();
} catch (Exception e) {
throw new IOException(e.getMessage(), e);
}
}（2）apifox请求示例：
二、文件导出
1.普通excel文件导出
// controller 层
@PostMapping(value = "/export")
@ApiOperation(value = "区域信息导出")
public void export(HttpServletResponse response, @RequestBody AreaQueryDto dto){
log.info("区域信息导出==>{}", JSON.toJSONString(dto));
areaService.export(response,dto);
}/**
* @author: xuyahui
* @Date: 2022/3/4 11:16
* @Description: 区域信息导出
*/
public void export(HttpServletResponse response, AreaQueryDto dto) {
List<AreaExcelVo> resultList = getExportResultList(dto);
String fileSheetName = "区域";
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcelUtil.getExcelWriter(response, fileSheetName+"_"+ DateUtils.format(new Date()));
WriteSheet writeSheet = EasyExcel.writerSheet()
.head(AreaExcelVo.class)
.sheetName(fileSheetName)
.build();
excelWriter.write(resultList, writeSheet);
excelWriter.finish();
}catch (Exception e) {
log.error("导出区域信息失败！", e);
throw new BaseException("导出区域信息失败");
}finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
/**
* @author: xuyahui
* @Date: 2022/3/4 11:25
* @Description: 获取区域导出信息
*/
private List<AreaExcelVo> getExportResultList(AreaQueryDto dto) {
List<Area> areaList = areaMapper.filterArea(dto);
if(CollectionUtils.isEmpty(areaList)){
return Lists.newArrayList();
}
List<AreaExcelVo> areaExcelVoList = areaList.stream().map(v->v.buildExportVo()).collect(Collectors.toList());
return areaExcelVoList;
}2.导出时合并指定列的单元格（基于easyexcel）
版本 com.alibaba:easyexcel:2.2.6
效果如下
（1）创建自定义合并策略类
package com.hxsj.hdc.common.util.excel;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.*;
public class AllSameValueMergeStrategy extends AbstractMergeStrategy {
/** 需要合并的列索引（从0开始） */
private int[] mergeColumnIndices;
/** 数据总条数（用于判断最后一行） */
private int totalRowCount;
/** 缓存每列的所有值和对应的行索引：key=列索引，value=Map<单元格值, 行索引列表> */
private Map<Integer, Map<String, List<Integer>>> columnValueRowsMap = new HashMap<>();
/** 标记是否已执行合并（避免重复合并） */
private boolean merged = false;
/**
* 构造方法
* @param mergeColumnIndices 需要合并的列索引
* @param totalRowCount 数据总条数（不含表头）
*/
public AllSameValueMergeStrategy(int[] mergeColumnIndices, int totalRowCount) {
this.mergeColumnIndices = mergeColumnIndices;
this.totalRowCount = totalRowCount;
// 初始化缓存结构
for (int col : mergeColumnIndices) {
columnValueRowsMap.put(col, new HashMap<>());
}
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
// 跳过表头行（relativeRowIndex为null时是表头）
if (relativeRowIndex == null) {
return;
}
// 只处理目标列
int currentCol = head.getColumnIndex();
if (!columnValueRowsMap.containsKey(currentCol)) {
return;
}
// 1. 缓存当前单元格的值和行索引
String cellValue = getCellValue(cell);
int currentRow = cell.getRowIndex(); // 实际行索引（含表头，表头行索引为0时，数据行从1开始）
Map<String, List<Integer>> valueRowsMap = columnValueRowsMap.get(currentCol);
valueRowsMap.computeIfAbsent(cellValue, k -> new ArrayList<>()).add(currentRow);
// 2. 判断是否为最后一行：relativeRowIndex从0开始，最后一行的索引为totalRowCount - 1
boolean isLastRow = relativeRowIndex == (totalRowCount - 1);
if (isLastRow && !merged) {
// 3. 执行合并逻辑
mergeAllColumns(sheet);
merged = true;
}
}
/**
* 合并所有列中值相同的行
*/
private void mergeAllColumns(Sheet sheet) {
for (Map.Entry<Integer, Map<String, List<Integer>>> entry : columnValueRowsMap.entrySet()) {
int col = entry.getKey();
Map<String, List<Integer>> valueRows = entry.getValue();
for (List<Integer> rows : valueRows.values()) {
if (rows.size() >= 2) { // 至少2行才需要合并
rows.sort(Comparator.naturalOrder()); // 确保行索引递增
int firstRow = rows.get(0);
int lastRow = rows.get(rows.size() - 1);
// 合并单元格（起始行，结束行，起始列，结束列）
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, col, col);
sheet.addMergedRegionUnsafe(region);
}
}
}
}
/**
* 安全获取单元格的字符串值（强制转为字符串，避免类型错误）
*/
private String getCellValue(Cell cell) {
if (cell == null) {
return "";
}
// 强制转换为字符串类型，兼容所有数据类型
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
}
}（2）定义要合并的列，并传入参数
int[] mergeColumnIndexArray = {0}; // 要合并的列的下标，从0开始
@Override
public void exportScheduleResult(HttpServletResponse response) {
try {
// 查询和排序
List<SubAssemblyManualScheduleVo> exportDataList = getSubAssemblyManualScheduleVos();
// 对于导出的数据计算和调整
// 排序
exportDataList.sort(Comparator.comparing(SubAssemblyManualScheduleVo::getSequence)
.thenComparing(SubAssemblyManualScheduleVo::getSort)
.thenComparing(SubAssemblyManualScheduleVo::getTwinFlag));
// 2. 调用工具类导出Excel
// 创建自定义合并策略
int[] mergeColumnIndexArray = {0}; // 要合并的列
ExcelExportUtil.exportExcel(
response,
"部装工位排产结果", // 文件名
"部装工位排产结果", // 工作表名
SubAssemblyManualScheduleVo.class, // 实体类
exportDataList, // 数据集合
new AllSameValueMergeStrategy(mergeColumnIndexArray, exportDataList.size())
);
} catch (IOException e) {
throw new RuntimeException(e);
}
}（3）绑定策略
EasyExcel.write(response.getOutputStream(), clazz)
.sheet(sheetName)
.registerWriteHandler(styleStrategy) // 应用样式
.registerWriteHandler(new AutoColumnWidthStrategy()) // 新增的自适应列宽策略
.registerWriteHandler(new HeaderRowHeightStrategy()) // 3. 新增：表头高度策略
.registerWriteHandler(mergeStrategy) // 合并单元格
.doWrite(dataList);
package com.hxsj.hdc.common.util.excel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
/**
* EasyExcel 导出工具类
*/
public class ExcelExportUtil {
/**
* 导出 Excel
*
* @param response HttpServletResponse
* @param fileName 导出文件名（不含后缀）
* @param sheetName Excel 工作表名
* @param clazz 导出数据的类类型
* @param dataList 导出的数据集合
* @throws IOException IO异常
*/
public static <T> void exportExcel(
HttpServletResponse response,
String fileName,
String sheetName,
Class<T> clazz,
List<T> dataList,
AllSameValueMergeStrategy mergeStrategy) throws IOException {
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 处理文件名编码，避免中文乱码
String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name())
.replaceAll("\\+", "%20");
response.setHeader("Content-disposition",
"attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
// 构建单元格样式策略
HorizontalCellStyleStrategy styleStrategy = buildCellStyleStrategy();
// 写入数据
EasyExcel.write(response.getOutputStream(), clazz)
.sheet(sheetName)
.registerWriteHandler(styleStrategy) // 应用样式
.registerWriteHandler(new AutoColumnWidthStrategy()) // 新增的自适应列宽策略
.registerWriteHandler(new HeaderRowHeightStrategy()) // 3. 新增：表头高度策略
.registerWriteHandler(mergeStrategy) // 合并单元格
.doWrite(dataList);
}
/**
* 构建单元格样式策略（表头和内容样式）
*/
private static HorizontalCellStyleStrategy buildCellStyleStrategy() {
// 表头样式
WriteCellStyle headCellStyle = new WriteCellStyle();
// 设置表头水平居中
headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置表头垂直居中
headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 表头字体
WriteFont headFont = new WriteFont();
headFont.setFontHeightInPoints((short) 12);
headFont.setBold(true); // 加粗
headCellStyle.setWriteFont(headFont);
// 内容样式
WriteCellStyle contentCellStyle = new WriteCellStyle();
// 内容水平居中
contentCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 内容垂直居中
contentCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 内容字体
WriteFont contentFont = new WriteFont();
contentFont.setFontHeightInPoints((short) 11);
contentCellStyle.setWriteFont(contentFont);
// 返回样式策略
return new HorizontalCellStyleStrategy(headCellStyle, contentCellStyle);
}
}
（4）自适应列宽策略（在步骤（3）里面被绑定和引用）
package com.hxsj.hdc.common.util.excel;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;
import java.util.List;
public class AutoColumnWidthStrategy extends AbstractColumnWidthStyleStrategy {
// 最小列宽(字符数)
private static final int MIN_COLUMN_WIDTH = 12;
// 最大列宽(字符数)，防止过宽
private static final int MAX_COLUMN_WIDTH = 60;
// Excel列宽单位转换系数（1字符宽度 ≈ 256 * 1.14）
private static final int UNIT_CONVERSION = 292;
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList,
Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (cell == null || writeSheetHolder == null) {
return;
}
// 获取当前单元格使用的字体
Font font = getCellFont(writeSheetHolder, cell, isHead);
// 计算列宽（根据字体宽度动态调整）
int columnWidth = calculateColumnWidth(cellDataList, cell, isHead, font);
// 设置列宽（转换为Excel实际宽度单位）
if (columnWidth > 0) {
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * UNIT_CONVERSION);
}
}
/**
* 获取单元格使用的字体
*/
private Font getCellFont(WriteSheetHolder writeSheetHolder, Cell cell, Boolean isHead) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
// 表头使用加粗字体，内容使用普通字体
if (isHead) {
Font headFont = workbook.createFont();
headFont.setFontHeightInPoints((short) 12);
headFont.setBold(true);
return headFont;
} else {
Font contentFont = workbook.createFont();
contentFont.setFontHeightInPoints((short) 11);
return contentFont;
}
}
/**
* 优化的列宽计算逻辑
*/
private int calculateColumnWidth(List<CellData> cellDataList, Cell cell, Boolean isHead, Font font) {
String value = getCellValue(cellDataList, cell, isHead);
// 移除换行符（避免表头换行影响计算）
value = value.replace("\n", "").replace("\r", "");
if (value.isEmpty()) {
return MIN_COLUMN_WIDTH;
}
// 计算字符宽度总和（考虑字体差异）
float totalWidth = 0;
for (char c : value.toCharArray()) {
// 中文字符宽度约为英文字符的1.8倍，结合字体大小调整
if (isChineseChar(c)) {
totalWidth += 1.8 * font.getFontHeightInPoints() / 11f;
} else {
totalWidth += 1.0 * font.getFontHeightInPoints() / 11f;
}
}
// 增加边距（左右各2个字符宽度）
totalWidth += 2;
// 转换为整数并限制范围
int columnWidth = Math.round(totalWidth);
return Math.min(Math.max(columnWidth, MIN_COLUMN_WIDTH), MAX_COLUMN_WIDTH);
}
/**
* 获取单元格实际值（兼容各种数据类型）
*/
private String getCellValue(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
try {
return cell.getStringCellValue() != null ? cell.getStringCellValue() : "";
} catch (Exception e) {
return "";
}
}
if (cellDataList == null || cellDataList.isEmpty()) {
return "";
}
CellData cellData = cellDataList.get(0);
if (cellData == null) {
return "";
}
try {
CellDataTypeEnum type = cellData.getType();
if (type == CellDataTypeEnum.STRING) {
return cellData.getStringValue() != null ? cellData.getStringValue() : "";
} else if (type == CellDataTypeEnum.NUMBER) {
return cellData.getNumberValue() != null ? cellData.getNumberValue().toString() : "";
} else if (type == CellDataTypeEnum.BOOLEAN) {
return String.valueOf(cellData.getBooleanValue());
}
// else if (type == CellDataTypeEnum.DATE) {
// return cellData.getDateValue() != null ? cellData.getDateValue().toString() : "";
// }
else {
return cellData.getStringValue() != null ? cellData.getStringValue() : "";
}
} catch (Exception e) {
return "";
}
}
/**
* 判断是否为中文字符
*/
private boolean isChineseChar(char c) {
// 中文字符、中文标点范围
return (c >= 0x4e00 && c <= 0x9fa5) ||
(c >= 0xff00 && c <= 0xffef) ||
(c >= 0x3000 && c <= 0x303f);
}
}
（5）设置表头高度策略（在步骤（3）里面被绑定和引用）
package com.hxsj.hdc.common.util.excel;
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Row;
public class HeaderRowHeightStrategy extends AbstractRowHeightStyleStrategy {
// 表头高度（单位：1/20 像素，Excel 行高的原生单位）
// 建议值：800（对应 40 像素，可根据需求调整）
private static final short HEADER_ROW_HEIGHT = 800;
// 内容行默认高度（可选，避免内容行过高/过低）
private static final short CONTENT_ROW_HEIGHT = 500;
@Override
protected void setHeadColumnHeight(Row row, int i) {
// relativeRowIndex = 0 表示「表头行」（单级表头场景）
if (i == 0) {
// 设置表头高度
row.setHeight(HEADER_ROW_HEIGHT);
} else {
// 可选：统一设置内容行高度（避免内容行高度不一致）
row.setHeight(CONTENT_ROW_HEIGHT);
}
}
@Override
protected void setContentColumnHeight(Row row, int i) {
}
}
3.导出表格首行加说明文字
（1）添加依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel-core</artifactId>
<version>3.3.2</version>
</dependency>（2）导出拦截器
package com.hxsj.hdc.common.util.excel;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class CustomHeaderWriteHandler implements SheetWriteHandler {
private String descContent;
public CustomHeaderWriteHandler() {
}
public CustomHeaderWriteHandler(String descContent) {
this.descContent = descContent;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
// 设置第一行标题
Row row1 = sheet.createRow(0);
row1.setHeight((short) 300);
Cell row1Cell1 = row1.createCell(0);
row1Cell1.setCellValue(descContent);
CellStyle row1CellStyle = workbook.createCellStyle();
row1CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
row1CellStyle.setAlignment(HorizontalAlignment.CENTER);
Font row1Font = workbook.createFont();
row1Font.setBold(true);
row1Font.setFontName("方正小标宋_GBK");
row1Font.setFontHeightInPoints((short) 20);
row1CellStyle.setFont(row1Font);
row1Cell1.setCellStyle(row1CellStyle);
//合并单元格，起始行,结束行,起始列,结束列
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 1, 0, 11));
}
}
(3) 拦截器注册和使用
.registerWriteHandler(headerWriteHandler)// 注册自定义表头处理器，添加新表头 .useDefaultStyle(true).relativeHeadRowIndex(2) // 数据列表从第2行开始
@Override
public void exportScheduleResult(HttpServletResponse response) {
try {
// 查询和排序
List<SubAssemblyManualScheduleVo> exportDataList = getSubAssemblyManualScheduleVos();
// 对于导出的数据计算和调整
// 排序
exportDataList.sort(Comparator.comparing(SubAssemblyManualScheduleVo::getSequence)
.thenComparing(SubAssemblyManualScheduleVo::getSort)
.thenComparing(SubAssemblyManualScheduleVo::getTwinFlag));
// 2. 调用工具类导出Excel
// 创建自定义合并策略
int[] mergeColumnIndexArray = {0}; // 要合并的列
// 定义新表头内容，可以是一个总标题或多个标题
List<String> planStartDateList = exportDataList.stream().filter(v -> StringUtils.isNotBlank(v.getPlannedStartDate()))
.map(SubAssemblyManualScheduleVo::getPlannedStartDate).collect(Collectors.toList());
List<String> planEndDateList = exportDataList.stream().filter(v -> StringUtils.isNotBlank(v.getPlannedEndDate()))
.map(SubAssemblyManualScheduleVo::getPlannedEndDate).collect(Collectors.toList());
String startDate = DateUtils.findMinDate(planStartDateList);
String endDate = DateUtils.findMaxDate(planEndDateList);
String currentDate = DateUtils.getCurrentDate();
String formatStr = "%s-%s总装产品大梁部装工位预排（更新日期：%s）";
String headerContent = String.format(formatStr, startDate, endDate, currentDate);
ExcelExportUtil.exportExcel(
response,
"部装工位排产结果", // 文件名
"部装工位排产结果", // 工作表名
SubAssemblyManualScheduleVo.class, // 实体类
exportDataList, // 数据集合
new AllSameValueMergeStrategy(mergeColumnIndexArray, exportDataList.size()),
new CustomHeaderWriteHandler(headerContent)
);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* 导出 Excel
*
* @param response HttpServletResponse
* @param fileName 导出文件名（不含后缀）
* @param sheetName Excel 工作表名
* @param clazz 导出数据的类类型
* @param dataList 导出的数据集合
* @throws IOException IO异常
*/
public static <T> void exportExcel(
HttpServletResponse response,
String fileName,
String sheetName,
Class<T> clazz,
List<T> dataList,
AllSameValueMergeStrategy mergeStrategy,
CustomHeaderWriteHandler headerWriteHandler) throws IOException {
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 处理文件名编码，避免中文乱码
String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name())
.replaceAll("\\+", "%20");
response.setHeader("Content-disposition",
"attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
// 构建单元格样式策略
HorizontalCellStyleStrategy styleStrategy = buildCellStyleStrategy();
// 写入数据
EasyExcel.write(response.getOutputStream(), clazz)
.sheet(sheetName)
.registerWriteHandler(styleStrategy) // 应用样式
.registerWriteHandler(new AutoColumnWidthStrategy()) // 新增的自适应列宽策略
.registerWriteHandler(new HeaderRowHeightStrategy()) // 3. 新增：表头高度策略
.registerWriteHandler(mergeStrategy) // 合并单元格
// 注册自定义表头处理器，添加新表头
.registerWriteHandler(headerWriteHandler)
.useDefaultStyle(true).relativeHeadRowIndex(2) // 数据列表从第2行开始
.doWrite(dataList);
}
效果如下：
三、下载导入模板
1.多个sheet实现
@ApiOperation(value = "下载用户导入模板")
@GetMapping("/template")
public void downloadTemplate(HttpServletResponse response) {
log.info("下载用户导入模板");
userService.downloadTemplate(response);
}
@Override
public void downloadTemplate(HttpServletResponse response) {
try {
ResourceBundle resourceBundle = i18nSet();
String fileName = URLEncoder.encode(resourceBundle.getString("EXCEL_USER_TEMPLATE"), "UTF-8");
// String fileName = URLEncoder.encode("用户导入模板", "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
List<SysRoleEntity> sysRoleEntityList = sysRoleBiz.listAll(SessionBean.getTenantId());
List<String> roleNameList = sysRoleEntityList.stream()
.filter(role -> (0 != role.getRoleType() && 1 != role.getRoleType()))
.map(SysRoleEntity::getRoleName).collect(Collectors.toList());
Set<String> authorizedOrgIdSet = userService.getUserAuthorizedOrgIdList(SessionBean.getUserId());
List<SysOrganizationEntity> organizationList = organizationBiz.listByIds(authorizedOrgIdSet);
List<String> organizationNameList = organizationList.stream().map(SysOrganizationEntity::getOrganizationName).collect(Collectors.toList());
WriteSheet phoneSheet = buildPhoneSheet(roleNameList, organizationNameList,resourceBundle);
WriteSheet emailSheet = buildEmailSheet(roleNameList, organizationNameList,resourceBundle);
WriteSheet usernameSheet = buildUserNameSheet(roleNameList, organizationNameList,resourceBundle);
String displayNameDesc = resourceBundle.getString("EXCEL_DISPLAYNAME_DESC");
String phoneDesc = resourceBundle.getString("EXCEL_HPONE_DESC");
String emailDesc = resourceBundle.getString("EXCEL_EMAIL_DESC");
String userNameDesc = resourceBundle.getString("EXCEL_USERNAME_DESC");
String pwdDesc = resourceBundle.getString("EXCEL_PASSWORD_DESC");
List<String> phoneDescs = Arrays.asList(displayNameDesc, phoneDesc, "", "");
List<String> emailDescs = Arrays.asList(displayNameDesc, emailDesc, "", "");
List<String> usernameDescs = Arrays.asList(displayNameDesc, userNameDesc, pwdDesc, "", "");
// 写入数据
excelWriter.write(Collections.singletonList(phoneDescs), phoneSheet)
.write(Collections.singletonList(emailDescs), emailSheet)
.write(Collections.singletonList(usernameDescs), usernameSheet);
excelWriter.finish();
} catch (Exception e) {
log.error("[excelExport] error:", e);
}
}四、文件上传
1.文件上传实现方法一（基于Minio实现）
@PostMapping(value = "/upload")
@ApiOperation(value = "上传文件返回文件存储名称", httpMethod = "POST", notes = "上传文件返回文件存储名称")
public String upload(@RequestParam("file") MultipartFile file,
@RequestParam("bucketName") String bucketName,
@RequestParam("childBucketName") String childBucketName,
@RequestParam(required = false) Boolean previewFlag) throws Exception {
log.info("上传文件返回可下载地址,fileName{},businessName{}",childBucketName);
return minioYunService.uploadFile(file,bucketName,childBucketName,previewFlag);
}
public String uploadFile(MultipartFile file,
String bucketName,
String childBucketName,
Boolean previewFlag) throws Exception {
String fileName = file.getOriginalFilename();
fileName = fileName.substring(Constants.ZERO,fileName.lastIndexOf(Constants.POINT)) + Constants.UNDERLINE
+ System.currentTimeMillis() + Constants.POINT
+ fileName.substring(fileName.lastIndexOf(Constants.POINT) + Constants.ONE);
return upload(file, fileName, Constants.CONTENT_TYPE_STREAM,bucketName,childBucketName, previewFlag);
}
private String upload(MultipartFile file, String fileName, String contentType, String bucketName,String childBucketName, Boolean previewFlag) throws Exception {
previewFlag = null == previewFlag ? true : previewFlag;
log.info("上传文件参数:fileName:{},bucketName:{},childBucketName:{}",fileName,bucketName,childBucketName);
client = new MinioClient(constants.getEndpoint(), constants.getAccessKey(), constants.getSecretKey());
String objectName = Objects.isNull(childBucketName) ? fileName : childBucketName + "/" + fileName;
//创建文件流
InputStream inputStream = file.getInputStream();
log.info("参数 文件夹名称+文件名称"+objectName);
try {
boolean isExist = client.bucketExists(bucketName);
if (isExist) {
log.debug("桶[{}]已经存在", bucketName);
} else {
//新建桶
client.makeBucket(bucketName);
}
if (!previewFlag) {
client.putObject(bucketName, objectName, inputStream, contentType);
} else {
client.putObject(bucketName, objectName, inputStream, FileContentTypeUtil.getContentType(fileName));
}
inputStream.close();
} catch (Exception e) {
log.error("file update error" + e);
}
return objectName;
}
public class FileContentTypeUtil {
public static String getContentType(String fileName) {
String type = fileName.substring(fileName.lastIndexOf(".")+1);
String contentType = Constants.CONTENT_TYPE_STREAM;
if (StringUtils.isNotBlank(type)) {
if (type.equals("jpg") || type.equals("jpeg")) {
contentType = "image/jpeg";
}
if (type.equals("png")) {
contentType = "image/png";
}
if (type.equals("gif")) {
contentType = "image/gif";
}
if (type.equals("mp4")) {
contentType = "video/mp4";
}
if (type.equals("wmv")) {
contentType = "video/x-ms-wmv";
}
if (type.equals("ogg")) {
contentType = "video/x-theora+ogg";
}
}
return contentType;
}
}2.文件上传实现方法二（基于Minio实现）
controller 层代码
@RequestMapping(value = "/publicUpload", method = RequestMethod.POST)
@ApiOperation(value = "上传文件接口", notes = "")
@ApiImplicitParam(paramType = "header", dataType = "String", name = "workspaceId", value = "工作空间id", required = true)
@ResponseBody
public ObjectRestResponse<DsFileVo> publicUploadFile(@ApiParam("上传文件") @RequestParam("file") MultipartFile multipartFile,
@ApiParam("文件名称") @RequestParam(value = "fileName", required = false)
@Length(max = ValidatorConstant.COMMON_LENGTH_64, message = "文件名称" + ValidatorConstant.COMMON_LENGTH_64_MSG)
@Pattern(regexp = ValidatorConstant.CONVENTIONAL_TEXT_REG, message = "文件名称只能包含" + ValidatorConstant.CONVENTIONAL_TEXT_REG_MSG)
String fileName,
@ApiParam("存储文件加夹名字") @RequestParam(value = "folderName", required = false) String folderName
) throws IOException, XmlPullParserException, NoSuchAlgorithmException, RegionConflictException, InvalidKeyException, InvalidPortException, InvalidArgumentException, ErrorResponseException, NoResponseException, InvalidBucketNameException, InsufficientDataException, InvalidEndpointException, InternalException, InvalidObjectPrefixException {
if (null == multipartFile) {
throw new BaseException("上传文件不能为空", ErrorType.ERROR_REQUEST_FAILD.getIndex());
}
DsFileVo fileVo = fileService.publicUploadFile(multipartFile, fileName,folderName);
return new ObjectRestResponse().data(fileVo);
}service 层代码
/**
*
* @param multipartFile
* @param fileName 文件名
* @param folderName Minio服务存储文件夹的文件夹名
* @return
*/
public DsFileVo publicUploadFile(MultipartFile multipartFile, String fileName,String folderName) throws IOException, InvalidKeyException, NoSuchAlgorithmException, XmlPullParserException, InvalidPortException, InvalidArgumentException, InternalException, ErrorResponseException, NoResponseException, InvalidBucketNameException, InsufficientDataException, InvalidEndpointException, RegionConflictException, InvalidObjectPrefixException{
String fileOriginalName = multipartFile.getOriginalFilename();
if (StringUtils.isEmpty(fileOriginalName)) {
throw new BaseException("文件原始名称缺失");
}
String extension = fileUtil.checkFileExtension(fileOriginalName);
DsFileVo dsFileVo = new DsFileVo();
String fileAbsolutePath = getFileAbsolutePath(folderName, fileOriginalName);
String fileRelativePath = getFileRelativePath(fileAbsolutePath);
InputStream ins = multipartFile.getInputStream();
File targetFile = null;
try {
targetFile = new File(fileRelativePath.substring(fileRelativePath.lastIndexOf("/") + 1));
FileUtils.copyInputStreamToFile(ins, targetFile);
String fileSize = fileUtil.getFileSize(targetFile);
fileUtil.saveMultiFile(targetFile.getName(), server, multipartFile, folderName+ "/" + LocalDate.now().toString());
DsFile dsFile = initLocalDsFile1(fileOriginalName, fileName, fileSize, fileRelativePath, extension);
ObjectCopyUtil.objectPropertiesCopy(dsFile, dsFileVo);
} catch (Exception ex) {
fileUtil.deleteFile(folderName,fileRelativePath);
throw ex;
} finally {
ins.close();
if (targetFile != null && targetFile.exists()) {
targetFile.delete();
}
}
return dsFileVo;
}
/**
* 获取文件存放绝对路径
*
* @param workspaceId
* @param fileOriginalName
* @return
*/
public String getFileAbsolutePath(String workspaceId, String fileOriginalName) {
String filePath = getFileParentPath(workspaceId) + "/" + fileUtil.getFileRandomName(fileOriginalName);
return filePath;
}
/**
* 获取空间文档默认存放路径
*
* @param workspaceId
* @return
*/
public String getFileParentPath(String workspaceId) {
String path = fileUtil.getDefaultRootPath() + "/" + server + "/" + workspaceId + "/" + LocalDate.now().toString();
File temp = new File(path);
if (!temp.exists()) {
if (!temp.mkdirs()) {
throw new BaseException("创建文件存储路径失败");
}
}
return temp.getAbsolutePath();
}
/**
* 根据文件原始名称，获取一个随机名称：xxx-xx...x-xxxx.txt
*
* @param fileOriginalName
* @return
*/
public String getFileRandomName(String fileOriginalName) {
String extension = StringUtils.getFilenameExtension(fileOriginalName);
String resourceId = UUID.randomUUID().toString();
if (StringUtils.isEmpty(extension)) {
return resourceId;
} else {
return resourceId + "." + extension;
}
}
/**
* 从空间开始的相对路径
*
* @param fileAbsolutePath
* @return
*/
public String getFileRelativePath(String fileAbsolutePath) {
String defaultRootPath = fileUtil.getDefaultRootPath();
if (fileAbsolutePath.contains(defaultRootPath)) {
return fileAbsolutePath.replace(defaultRootPath, "").replaceAll("\\\\", "/");
} else {
throw new BaseException("获取文件相对路径失败");
}
}
/**
* 获取默认路径
*
* @return
*/
public String getDefaultRootPath() {
String rootJarPath = getBaseJarPath().getAbsolutePath();
File defaultFile = new File(rootJarPath + "/" + DEFAULT_PATH);
if (!defaultFile.exists()) {
if (!defaultFile.mkdirs()) {
throw new BaseException("创建默认存放路径失败", ErrorType.ERROR.getIndex());
}
}
return defaultFile.getAbsolutePath();
}
minio文件存储实现
public void saveMultiFile(String fileName, String space, MultipartFile file, String filePath) throws IOException, XmlPullParserException, NoSuchAlgorithmException, RegionConflictException, InvalidKeyException, InvalidPortException, InvalidArgumentException, ErrorResponseException, NoResponseException, InvalidBucketNameException, InsufficientDataException, InvalidEndpointException, InternalException, InvalidObjectPrefixException {
minioUtil.saveFile(fileName, space, file, filePath);
}
public void saveFile(String fileName, String space, MultipartFile file, String filePath) throws InvalidPortException, InvalidEndpointException, IOException, InvalidKeyException, NoSuchAlgorithmException, InsufficientDataException, InternalException, NoResponseException, InvalidBucketNameException, XmlPullParserException, ErrorResponseException, RegionConflictException, InvalidArgumentException, InvalidObjectPrefixException {
boolean isExist = template.getMinioClient().bucketExists(space);
if (!isExist) {
template.createBucket(space);
}
template.getMinioClient().setBucketPolicy(space,filePath,PolicyType.NONE);
template.saveObject(space , filePath + "/" + fileName ,file.getInputStream(),file.getSize(),file.getContentType());
}package com.spring.doc.minio.service;
import com.spring.doc.minio.vo.MinioItem;
import io.minio.MinioClient;
import io.minio.ObjectStat;
import io.minio.Result;
import io.minio.errors.*;
import io.minio.messages.Bucket;
import io.minio.messages.Item;
import org.xmlpull.v1.XmlPullParserException;
import java.io.*;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class MinioTemplate {
private String endpoint, accessKey, secretKey;
public MinioTemplate() {
}
/**
* Create new instance of the {@link MinioTemplate} with the access key and secret key.
*
* @param endpoint minio URL, it should be a URL, domain name, IPv4 address or IPv6 address
* @param accessKey uniquely identifies a minio account.
* @param secretKey the password to a minio account.
*/
public MinioTemplate(String endpoint, String accessKey, String secretKey) {
this.endpoint = endpoint;
this.accessKey = accessKey;
this.secretKey = secretKey;
}
/**
* Bucket Operations
*/
public void createBucket(String bucketName) throws XmlPullParserException, NoSuchAlgorithmException, InvalidKeyException, IOException, InvalidPortException, InvalidEndpointException, RegionConflictException, NoResponseException, InternalException, ErrorResponseException, InsufficientDataException, InvalidBucketNameException {
MinioClient client = getMinioClient();
if (!client.bucketExists(bucketName)) {
client.makeBucket(bucketName);
}
}
public List<Bucket> getAllBuckets() throws InvalidPortException, InvalidEndpointException, IOException, InvalidKeyException, NoSuchAlgorithmException, InsufficientDataException, InternalException, NoResponseException, InvalidBucketNameException, XmlPullParserException, ErrorResponseException {
return getMinioClient().listBuckets();
}
public Optional<Bucket> getBucket(String bucketName) throws InvalidPortException, InvalidEndpointException, IOException, InvalidKeyException, NoSuchAlgorithmException, InsufficientDataException, InternalException, NoResponseException, InvalidBucketNameException, XmlPullParserException, ErrorResponseException {
return getMinioClient().listBuckets().stream().filter(b -> b.name().equals(bucketName)).findFirst();
}
public void removeBucket(String bucketName) throws InvalidPortException, InvalidEndpointException, IOException, InvalidKeyException, NoSuchAlgorithmException, InsufficientDataException, InternalException, NoResponseException, InvalidBucketNameException, XmlPullParserException, ErrorResponseException {
getMinioClient().removeBucket(bucketName);
}
public List<MinioItem> getAllObjectsByPrefix(String bucketName, String prefix, boolean recursive) throws InvalidPortException, InvalidEndpointException {
List objectList = new ArrayList();
Iterable<Result<Item>> objectsIterator = getMinioClient().listObjects(bucketName, prefix, recursive);
objectsIterator.forEach(i -> {
try {
objectList.add(new MinioItem(i.get()));
} catch (Exception e) {
new Exception(e);
}
});
return objectList;
}
/**
* Object operations
*/
public String getObjectURL(String bucketName, String objectName, Integer expires) throws InvalidPortException, InvalidEndpointException, IOException, InvalidKeyException, NoSuchAlgorithmException, InsufficientDataException, InvalidExpiresRangeException, InternalException, NoResponseException, InvalidBucketNameException, XmlPullParserException, ErrorResponseException {
return getMinioClient().presignedGetObject(bucketName, objectName, expires);
}
public void saveObject(String bucketName, String objectName, InputStream stream, long size, String contentType) throws InvalidPortException, InvalidEndpointException, IOException, InvalidKeyException, NoSuchAlgorithmException, InsufficientDataException, InvalidArgumentException, InternalException, NoResponseException, InvalidBucketNameException, XmlPullParserException, ErrorResponseException {
getMinioClient().putObject(bucketName, objectName, stream, size, contentType);
}
// public void saveObject(String bucketName, String objectName, InputStream stream, long size, String contentType) throws InvalidPortException, InvalidEndpointException, IOException, InvalidKeyException, NoSuchAlgorithmException, InsufficientDataException, InvalidArgumentException, InternalException, NoResponseException, InvalidBucketNameException, XmlPullParserException, ErrorResponseException {
// getMinioClient().putObject(bucketName, objectName, stream, size, contentType);
// }
public ObjectStat getObjectInfo(String bucketName, String objectName) throws InvalidPortException, InvalidEndpointException, IOException, InvalidKeyException, NoSuchAlgorithmException, InsufficientDataException, InternalException, NoResponseException, InvalidBucketNameException, XmlPullParserException, ErrorResponseException {
return getMinioClient().statObject(bucketName, objectName);
}
public File getObject(String bucketName, String objectName ,String originName) throws IOException, NoSuchAlgorithmException, InvalidKeyException, XmlPullParserException {
File file = new File(originName);
try {
OutputStream os = new FileOutputStream(file);
getMinioClient().statObject(bucketName, objectName);
// 获取"myobject"的输入流。
InputStream stream = getMinioClient().getObject(bucketName, objectName);
// 读取输入流直到EOF并打印到控制台。
byte[] buf = new byte[16384];
int bytesRead = 0;
while ((bytesRead = stream.read(buf, 0, 8192)) != -1) {
os.write(buf, 0, bytesRead);
}
os.close();
// 关闭流，此处为示例，流关闭最好放在finally块。
stream.close();
} catch (MinioException e) {
System.out.println("Error occurred: " + e);
}
return file;
}
public void removeObject(String bucketName, String objectName) throws InvalidPortException, InvalidEndpointException, IOException, InvalidKeyException, NoSuchAlgorithmException, InsufficientDataException, InternalException, NoResponseException, InvalidBucketNameException, XmlPullParserException, ErrorResponseException, InvalidArgumentException {
getMinioClient().removeObject(bucketName, objectName);
}
/**
* Gets a Minio client
*
* @return an authenticated Amazon S3 client
*/
public MinioClient getMinioClient() throws InvalidPortException, InvalidEndpointException {
return new MinioClient(endpoint, accessKey, secretKey);
}
}
3.获取文件预览和下载地址
@GetMapping(value = "/getFilePresignedUrl")
@ApiOperation(value = "获取文件浏览或下载预签地址", httpMethod = "Get", notes = "获取文件浏览或下载预签地址,有失效时间")
public String getFilePresignedUrl(@RequestParam("bucketName") String bucketName,
@RequestParam("objectName") String objectName) throws Exception {
log.info("获取文件浏览或下载预签地址,bucketName{},objectName{}",bucketName,objectName);
return minioYunService.getFilePresignedUrl(bucketName,objectName);
}
/**
* @author: xuyahui
* @Date: 2021/5/27 9:45
* @Description: 根据文件存储名称，获取文件浏览或下载预签地址
*/
@Override
public String getFilePresignedUrl(String bucketName, String objectName) {
try {
client = new MinioClient(constants.getEndpointViewUrl(), constants.getAccessKey(), constants.getSecretKey());
boolean isExist = client.bucketExists(bucketName);
if(isExist){
return client.presignedGetObject(bucketName, objectName, constants.getExpiresTime());
}
} catch (Exception e) {
log.error("get presigned url error" + e);
}
return "";
}五、本地写入文件
开发中，有些场景需要将文件写入到本地，再进行后续的操作，比如上传等，文件写入本地的时候，注意创建文件夹和文件的顺序。建议先创建文件夹，再创建文件，避免系统错误。
// 推荐的做法
String path = RuoYiConfig.getUploadPath()+"\\mixstation\\"+uid+"\\"+projectId+".sdt";
File file = new File(path);
if (!file.exists()) {
file.getParentFile().mkdirs();
}
file.createNewFile();
// 会出错的实现，windows server 2013 会提示文件目录不存在
String pathName = RuoYiConfig.getUploadPath()+"\\mixstation\\"+authorId+"\\"+projectId+".sdt";
File file = new File(pathName);
if (!file.exists()) {
file.createNewFile();
}
扫一扫
840
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
