- 实现效果如图：
- 前提
- 实现过程
- 实现思路
- 代码实现
实现导出excel文件可以参考一下文章。
映射导出： springboot整合easypoi实现浏览器自动下载excel文件，一行代码实现，附带完整项目和导出工具。（一）
自定义导出：springboot整合easypoi实现浏览器自动下载自定义表头excel文件(二)
实现效果如图：
导入数据经过代码保存到数据库
数据库本身自带excel带入数据功能，但是如果通过页面导入excel数据到数据库是怎么实现的。
实现过程 实现思路首先创建一个excel文件，注意格式，第一行是标题，第二行才是表头，第三行开始才是真正的数据。
然后通过接口实现接收数据，然后进行处理。
备注：easy-poi自带数据替换功能，比如：效果图excel中的剂型是颗粒剂、中药，但是数据库存到是8、1。男女一样适用。
实现方法：
@Excel(name = "剂型", width = 50, isimportField = "true_st",replace = {"颗粒剂_8", "中药_1"})
private String medicineType;
接收到数据直接新增到数据库
代码实现1. 导入jar
cn.afterturn
easypoi-base
org.apache.poi
poi-ooxml
org.apache.poi
poi
4.2.0
org.apache.poi
poi-ooxml
4.1.1
compile
cn.afterturn
easypoi-annotation
4.2.0
org.apache.poi
poi
4.1.1
2. 直接复制工具类到项目中
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelimportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.importParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.hutool.core.util.StrUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
public class ExcelUtil {
private ExcelUtil() {
throw new IllegalStateException("Utility class");
}
public static void exportExcel(List list, String title, String sheetName, Class pojoClass, String fileName
, boolean isCreateHeader, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
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) throws IOException {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
}
public static void exportExcel(List> list, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, fileName, response);
}
private static void defaultExport(List list, Class pojoClass, String fileName
, HttpServletResponse response, ExportParams exportParams) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
private static void defaultExport(List> list, String fileName, HttpServletResponse response) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setHeader("FileName", URLEncoder.encode(fileName, "UTF-8")+".xlsx");
response.setHeader("Access-Control-Expose-Headers", "FileName");
workbook.write(response.getOutputStream());
}
public static List importExcel(String filePath, Integer titleRows, Integer headerRows, Class pojoClass) {
if (StrUtil.isBlank(filePath)) {
return Collections.emptyList();
}
importParams params = new importParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
return ExcelimportUtil.importExcel(new File(filePath), pojoClass, params);
}
public static List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class pojoClass) throws Exception {
if (file == null) {
return null;
}
importParams params = new importParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List list = null;
try {
list = ExcelimportUtil.importExcel(file.getInputStream(), pojoClass, params);
} catch (NoSuchElementException e) {
throw new RuntimeException("excel文件不能为空");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
return list;
}
}
3. 写excel映射实体类
@ExcelTarget(“MedicineExportVO”)注意此注解最好要和实体类名字一样。
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;
import java.io.Serializable;
import java.math.BigDecimal;
@Data
@ExcelTarget("MedicineExportVO")
public class MedicineExportVO implements Serializable {
private static final long serialVersionUID = 1L;
@Excel(name = "药品编码", width = 50, isimportField = "true_st")
private String goodsId;
@Excel(name = "药品名称", width = 50)
private String goodsName;
@Excel(name = "规格", width = 50, isimportField = "true_st")
private String specs;
@Excel(name = "单位", width = 50, isimportField = "true_st")
private String unit;
@Excel(name = "生产厂家", width = 50, isimportField = "true_st")
private String manufacturer;
@Excel(name = "零售价", width = 50, isimportField = "true_st")
private BigDecimal price;
@Excel(name = "剂型", width = 50, isimportField = "true_st",replace = {"颗粒剂_8", "中药_1"})
private String medicineType;
@Excel(name = "厂家", width = 50, isimportField = "true_st")
private String factory;
@Excel(name = "转换比", width = 50, isimportField = "true_st")
private String convertRatio;
@Excel(name = "助记码", width = 50, isimportField = "true_st")
private String medicineCode;
@Excel(name = "诊所id", width = 50, isimportField = "true_st")
private String clinicId;
@Excel(name = "库存", width = 50, isimportField = "true_st")
private String stock;
}
4. 写返回信息实体类
只要作用是展示返回的成功数、失败数，总数，以及错误信息。例如下图：
@Data
public class FailMsgVO {
private String goodsId;
private String goodsName;
private String failMsg;
}
5. 主要代码实现。
@PostMapping(value = "/medicine/import")
public Result importExcl(@RequestParam("file") MultipartFile excl) throws Exception {
System.out.println("准备导入药品"+excl);
Map map=new HashMap<>();
List failList=new ArrayList<>();
//总条数
int rowNum = 0;
//失败条数
int failNum=0;
//成功条数
int successNum=0;
if (!excl.isEmpty()){
List medicineExportVOList = ExcelUtil.importExcel(excl, 1, 1, MedicineExportVO.class);
JSON json=JSONUtil.parseObj(medicineExportVOList);
System.out.println("导入药品信息："+json+medicineExportVOList);
System.out.println("导入药品："+medicineExportVOList.size()+"条");
if (medicineExportVOList.size()>0){
for (MedicineExportVO medicineExportVO : medicineExportVOList) {
rowNum++;
System.out.println("药品信息"+medicineExportVO);
int res=0;
if (StringUtils.isNotEmpty(medicineExportVO.getGoodsId())){
medicineExportVO.setClinicId(account);
res = medicineInfoService.save1(medicineExportVO);
}
if (res==1){
successNum+=1;
}else {
FailMsgVO failMsgVO = new FailMsgVO();
failMsgVO.setGoodsId(medicineExportVO.getGoodsId());
failMsgVO.setGoodsName(medicineExportVO.getGoodsName());
failMsgVO.setFailMsg("第【"+rowNum+"】行出错");
failList.add(failMsgVO);
failNum+=1;
}
}
}
}
map.put("rowNum",rowNum);
map.put("failNum",failNum);
map.put("succesNum",successNum);
map.put("failList", failList);
return Result.ok(map);
}
