实现导出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
<!--excel导入导出-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
</exclusions>
<version>4.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
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;
/**
* Excel工具类
*
* @author zlt
* @date 2019/1/6
*/
public class ExcelUtil {
private ExcelUtil() {
throw new IllegalStateException("Utility class");
}
/**
* 导出
*
* @param list 数据列表
* @param title 标题
* @param sheetName sheet名称
* @param pojoClass 元素类型
* @param fileName 文件名
* @param isCreateHeader 是否创建列头
* @param response
* @throws IOException
*/
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);
}
/**
* 导出
*
* @param list 数据列表
* @param title 标题
* @param sheetName sheet名称
* @param pojoClass 元素类型
* @param fileName 文件名
* @param response
* @throws IOException
*/
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));
}
/**
* 导出
*
* @param list 数据列表(元素是Map)
* @param fileName 文件名
* @param response
* @throws IOException
*/
public static void exportExcel(List<Map<String, Object>> 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<Map<String, Object>> 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());
}
/**
* 手动创建表格，导出文件（适用于无法使用工具类正常导出，或者需要定制表头）
* @param map 表头集合<表头名，对应实体内字段名> 例如 "姓名"："name"
* @param jsonArray 导出数据list转化为jsonArray JSONArray objects = JSONObject.parseArray(JSON.toJSONString(list))
* @param title 表格的标题
* @param sheetName sheet
* @param exclName excl文档的名称
* @param response response
* @throws IOException
*/
public static void createExclByHand(Map<String, String> map, JSONArray jsonArray, String title, String sheetName, String exclName, HttpServletResponse response) throws IOException {
//数据
List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
//表头
ExcelExportEntity colEntity = null;
//循环遍历map,创建表头
for (String key : map.keySet()) {
colEntity = new ExcelExportEntity(key, map.get(key));
colList.add(colEntity);
}
//赋值数据
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
//遍历要填入的数据jsonArray
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject jsonObject = jsonArray.getJSONObject(i);
Map<String, Object> valMap1 = new HashMap<String, Object>();
//遍历表头，拿到想要的数据
for (int j = 0; j < map.size(); j++) {
List<String> list2 = new ArrayList<String>(map.keySet());
valMap1.put(map.get(list2.get(j)), jsonObject.getStr(map.get(list2.get(j))));
}
list.add(valMap1);
}
//构造表格
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title, sheetName), colList,
list);
//进行下载
downLoadExcel(exclName, response, workbook);
}
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> 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 <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
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 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;
/**
* @author lenovo
*/
@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;
/**
* 诊所id
*/
@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. 主要代码实现。
/**
* 导入药品excel文件
* @param excl
* @return
* @throws Exception
*/
@PostMapping(value = "/medicine/import")
public Result importExcl(@RequestParam("file") MultipartFile excl) throws Exception {
System.out.println("准备导入药品"+excl);
Map<String,Object> map=new HashMap<>();
List<FailMsgVO> failList=new ArrayList<>();
//总条数
int rowNum = 0;
//失败条数
int failNum=0;
//成功条数
int successNum=0;
if (!excl.isEmpty()){
List<MedicineExportVO> 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);
}
2457
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
