Java excel一对多导入

作者: adm 分类: go 发布时间: 2021-10-28 11:40

基于spring boot框架,先上pom配置

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.2</version>
        </dependency>

实体类

public class OrdersExcel implements Serializable {
@Excel(name = "内部运单号", needMerge=true)
private String tradeMode;
@Excel(name = "姓名",needMerge = true)
private String copMsgId;
@Excel(name = "顺丰运单号",needMerge = true)
private String cid;

@ExcelCollection(name="商品")
private List<OrdersGoodsExcel> orderGoods ;
}

@ExcelTarget("OrdersGoodsExcel")
public class OrdersGoodsExcel implements Serializable {
@Excel(name = "商品名称")
private String itemNo;
@Excel(name = "商品价格")
private String itemName;
}

通用方法类

 

public class Excel {
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);

}

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 RuntimeException(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);
}

/**
* 导入
* @param filePath
* @param titleRows
* @param headerRows
* @param pojoClass
* @param <T>
* @return
*/
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 RuntimeException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
return list;
}

/**
* 导入表格
* @param file
* @param pojoClass
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass,ImportParams params) {
if (file == null) {
return null;
}

try {
List<T> list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
return list;
} catch (NoSuchElementException e) {
throw new RuntimeException("excel文件不能为空");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
}

 

接口调用相关方法,这里演示一下导入功能

public String orderImprot(@RequestParam("files") MultipartFile file){

System.out.println(file);
try {
ImportParams params = new ImportParams();
params.setHeadRows(2);
List<OrdersExcel> userList = Excel.importExcel(file, OrdersExcel.class,params);
System.out.println(userList);

}catch (Exception e){
//msgBody.setMsg("导入失败["+e.getMessage()+"]");
}
return "msgBody";
}

如果觉得我的文章对您有用,请随意赞赏。您的支持将鼓励我继续创作!