真真真有用-Excel完美导入导出(有填雷)
1.导入依赖-easypoi
<!-- easypoi -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2.utils:工具类
public class ExcelUtil {
/**
* 导出工具类
* @param list
* @param title
* @param sheetName
* @param pojoClass
* @param fileName
* @param isCreateHeader
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
String fileName, boolean isCreateHeader, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* 导出工具类
* @param list
* @param title
* @param sheetName
* @param pojoClass
* @param fileName
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,
HttpServletResponse response){
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
defaultExport(list, fileName, response);
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
if (workbook != null); downLoadExcel(fileName, response, workbook);
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
//throw new NormalException(e.getMessage());
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
if (StringUtils.isBlank(filePath)){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}catch (NoSuchElementException e){
//throw new NormalException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
//throw new NormalException(e.getMessage());
} return list;
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
if (file == null){ return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}catch (NoSuchElementException e){
// throw new NormalException("excel文件不能为空");
} catch (Exception e) {
//throw new NormalException(e.getMessage());
System.out.println(e.getMessage());
}
return list;
}
}
3.entity:确定导出对象格式DTO
三种导出关系:
1.一对一:@Excel
@Excel注解如下:按需处理
属性 | 类型 | 用途 | 示例 |
---|---|---|---|
name | String | 定义Excel列的名称,用于Excel表头。 | @Excel(name = “学生姓名”) |
width | double | 定义列宽度,单位为字符。 | @Excel(width = 20) |
height | double | 定义行高,单位为磅。仅对导出有效。 | @Excel(height = 10) |
orderNum | String | 定义列的显示顺序。 | @Excel(orderNum = “1”) |
needMerge | boolean | 表示是否需要合并单元格。 | @Excel(needMerge = true) |
mergeVertical | boolean | 表示是否纵向合并单元格。 | @Excel(mergeVertical = true) |
mergeRely | int[] | 指定合并单元格时依赖的列,数组中的数字为列的索引。 | @Excel(mergeRely = {1}) |
isImportField | String | 定义是否为导入字段,“true"或"false”。 | @Excel(isImportField = “true”) |
isWrap | boolean | 定义是否自动换行。 | @Excel(isWrap = true) |
exportFormat | String | 定义导出时的格式化模式,如日期格式。 | @Excel(exportFormat = “yyyy-MM-dd”) |
importFormat | String | 定义导入时的格式化模式。 | @Excel(importFormat = “yyyy-MM-dd”) |
imageType | int | 定义图片类型(如1表示从文件导入,2表示从数据库导入)。 | @Excel(imageType = 1) |
suffix | String | 定义文本后缀,如在文本后添加特定的后缀。 | @Excel(suffix = “%”) |
type | int | 定义字段类型(如1表示文本,2表示图片)。 | @Excel(type = 1) |
enumExportField | String | 定义枚举导出时,调用枚举字段的哪个方法获取实际的值。 | @Excel(enumExportField = “getText”) |
savePath | String | 定义图片保存路径,仅对导入有效。 | @Excel(savePath = “/tmp”) |
dateFormat | String | 简化的日期格式设置,简化的exportFormat/importFormat。 | @Excel(dateFormat = “yyyy-MM-dd”) |
use1904windowing | boolean | 定义是否使用1904年日期窗口。 | @Excel(use1904windowing = true) |
fixedIndex | int | 指定Excel列的索引,从0开始,用于导入时准确匹配列位置。 | @Excel(fixedIndex = 0) |
groupName | String | 用于分组导出/导入时定义的组名。 | @Excel(groupName = “group1”) |
dict | String | 用于指定数据字典,将数据转换为对应的文本值。 | @Excel(dict = “sex=1_男,2_女”) |
replace | String[] | 定义要替换的值,用于导出时将字段值替换为指定的文本值。 | @Excel(replace = {“男_1”, “女_2”}) |
statistics | boolean | 定义是否进行统计,用于对数字字段进行求和统计。 | @Excel(statistics = true) |
显示:平铺
2.多对一(多个属性在一个对象中封装):@ExcelEntity
显示:嵌套
3.一对多:@ExcelCollection
显示:列表
!避雷:听话的孩子不会被恶心
!!!字段中有一对多(@ExcelCollection
)和一对一(@Excel)时候,一对一必须合并单元格!!!
否则如图:
!!!多对一时(@ExcelEntity)必须加字段,否则无法正常显示
代码如下:
@Data
public class ExportAchievement {
/**
* 主键ID
*/
@TableId
@Excel(name = "ID", width = 10, needMerge = true)
private Long id;
/**
* 成果单位
*/
@Excel(name = "成果单位", width = 30, needMerge = true)
private String achievementUnit;
/**
* 成果名称
*/
@Excel(name = "成果名称", width = 30, needMerge = true)
private String achievementName;
/**
* 所属区域
*/
@Excel(name = "所属区域", width = 30, needMerge = true)
private String region;
/**
* 细分领域
*/
@Excel(name = "细分领域", width = 30, needMerge = true)
private String subArea;
/**
* 所属领域
*/
@Excel(name = "所属领域", width = 30, needMerge = true)
private String domain;
/**
* 成果形式
*/
@Excel(name = "成果形式", width = 30, needMerge = true)
private String form;
/**
* 转化内容
*/
@Excel(name = "转化内容", width = 100, needMerge = true)
private String transformationContent;
/**
* 成果负责人
*/
@Excel(name = "成果负责人", width = 10, needMerge = true)
private String leader;
/**
* 联系方式
*/
@Excel(name = "联系方式", width = 30, needMerge = true)
private String contact;
/**
* 联系人
*/
@Excel(name = "联系人", width = 10, needMerge = true)
private String contactName;
/**
* 第二联系方式
*/
@Excel(name = "第二联系方式", width = 30, needMerge = true)
private String secondContact;
@ExcelCollection(name = "推荐需求")
private List<ExportRecommendDict> recommendDictList;
}
@Data
public class ExportDemand {
/**
* 主键ID
*/
@TableId
@Excel(name = "ID", width = 10, needMerge = true, orderNum="0")
private Long id;
@ExcelEntity(name = "进企入校调研", show = true)
private EnterpriseInfoEntity enterpriseInvestSchool;
@ExcelEntity(name = "技术需求", show = true)
private TechniqueDemandEntity techniqueDemand;
@ExcelEntity(name = "技术人员需求", show = true)
private TechPersonEntity techPersonEntity;
@ExcelEntity(name = "服务需求", show = true)
private ServiceDemandEntity serviceDemand;
@ExcelEntity(name = "创新成果", show = true)
private InnovationProductEntity innovationProduct;
/**
* 需求标题
*/
@Excel(name = "需求标题", width = 10, needMerge = true,orderNum="6")
private String demandTitle;
/**
* 领域指数JSON化
*/
@TableField(exist = false)
@Excel(name = "领域指数", width = 10, needMerge = true,orderNum="7")
private String regionNumJson;
@ExcelCollection(name = "推荐成果",orderNum="8")
private List<ExportRecommendDict> recommendDictList;
}
4.service层
调用mapper基本方法进行数据库增删改查
@Override
public List<ExportDemand> getDemandlist() {
//查询enterprise_demand表
List<EnterpriseDemandEntity> enterpriseDemands = enterpriseDemandDao.selectList(null);
List<ExportDemand> exportDemands = new ArrayList<>();
for (EnterpriseDemandEntity enterpriseDemand : enterpriseDemands) {
ExportDemand exportDemand = new ExportDemand();
// 设置主键ID和需求标题
exportDemand.setId(enterpriseDemand.getId());
EnterpriseInfoEntity enterpriseInfoEntity = enterpriseInfoDao.selectById(enterpriseDemand.getEnterpriseId());
TechPersonEntity techPersonEntity = techPersonDao.selectById(enterpriseDemand.getTechPersonId());
TechniqueDemandEntity techniqueDemandEntity = techniqueDemandDao.selectById(enterpriseDemand.getTechniqueDemandId());
InnovationProductEntity innovationProductEntity = innovationProductDao.selectById(enterpriseDemand.getInnovationProductId());
ServiceDemandEntity serviceDemandEntity = serviceDemandDao.selectById(enterpriseDemand.getServiceDemandId());
exportDemand.setEnterpriseInvestSchool(enterpriseInfoEntity);
exportDemand.setTechPersonEntity(techPersonEntity);
exportDemand.setTechniqueDemand(techniqueDemandEntity);
exportDemand.setInnovationProduct(innovationProductEntity);
exportDemand.setServiceDemand(serviceDemandEntity);
exportDemand.setRegionNumJson(Arrays.toString(enterpriseDemand.getRegionNumJson()));
exportDemand.setDemandTitle(enterpriseDemand.getDemandTitle());
exportDemand.setRecommendDictList(getExportAchievementByDemandId(exportDemand.getId()));
// 然后将每个ExportDemand对象添加到列表中
exportDemands.add(exportDemand);
}
return exportDemands;
}
5.controller层
/**
* @param response
*/
@GetMapping(value = "/exportDemandList")
public void exportDemandList(HttpServletResponse response) {
List<ExportDemand> list = collegeAppliedPropertyService.getDemandlist();
// 使用工具类导出excel
try {
ExcelUtil.exportExcel(list, "需求", "需求", ExportDemand.class, "需求信息推荐表.xlsx", response);
} catch (Exception e) {
e.printStackTrace();
}
}
6.mind层
“选择就不要后悔,努力就不会害怕”
我一直相信你从来都不是笨小孩