EasyPOI导入 导出功能实现

EasyPOI导入  导出功能实现

---

1. 1注解实现导出

    在实体类上添加注解 @ExcelTarget("his_DrugStorageEntity") ,其中his_DrugStorageEntity为别名(可以任意取).

    在参数上添加注解  @Excel(name = "生产日期",isImportField = "true",orderNum = "5",exportFormat = "yyyy-MM-dd", importFormat =  "yyyy-MM-dd" ,databaseFormat = "yyyy-MM-dd")

name:导出到excel中的名字

orderNum :排在第几个输出

isImportField :校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id

exportFormat :导出日期类格式化

package com.cjj.po;

import java.util.Date;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import com.fasterxml.jackson.annotation.JsonFormat;

@ExcelTarget("his_DrugStorageEntity")
public class His_DrugStorage {

    @Excel(name = "药品名称",isImportField = "true",orderNum = "1")
    private String drugstoragename;

    
    @Excel(name = "药品分类",isImportField = "true",orderNum = "2")
    private String drugstorageclassify;

   
    @Excel(name = "入库数量(只能填数字)",isImportField = "true",orderNum = "3")
    private Integer drugstoragenumber;

    
    @Excel(name = "药品价格",isImportField = "true",orderNum = "8")
    private String drugstorageprice;

   

    @Excel(name = "生产日期",isImportField = "true",orderNum = "5",exportFormat = "yyyy-MM-dd", importFormat =  "yyyy-MM-dd" ,databaseFormat = "yyyy-MM-dd")
    private Date drugstorageproducedtime;

   
    @Excel(name = "有效时间",isImportField = "true",orderNum = "4")
    private String drugstoragevalidity;


}

controller层代码

@GetMapping("W")
    public void wuzijieyong() { 

//先从数据库或页面获得数据list,一下为测试所用
List<His_DrugStorage > list=servie.findAll();


Workbook workbook = null;
        try {
          workbook = ExcelExportUtil.exportExcel(new ExportParams(name + "批量导出模板", "导出"),His_DrugStorage .class, list);
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + "批量导出模板" + System.currentTimeMillis() + ".xls");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
}

jsp页面代码  点击按钮下载

function WZJYDC(){
	window.location.href="${ctx}/daochu/W.do";

	}

2.2自定义ExcelExportEntity实现导出

@GetMapping("wuzijieyong")
    public void wuzijieyong() {
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        //向对象中加入属性
        entity.add(new ExcelExportEntity("物资名称", "warehousingname"));
        entity.add(new ExcelExportEntity("分类", "warehousingclassification"));
        entity.add(new ExcelExportEntity("物资有效期", "warehousingtermofvalidity"));
        entity.add(new ExcelExportEntity("物资生产日期", "warehousingdateofproduction"));
        entity.add(new ExcelExportEntity("规格", "warehousingspecifications"));
        entity.add(new ExcelExportEntity("现有库存", "warehousingstock"));
        entity.add(new ExcelExportEntity("借用人名字", "jymz"));
        entity.add(new ExcelExportEntity("借用数量", "jysl"));
        entity.add(new ExcelExportEntity("借用物资时间", "jysj"));
        entity.add(new ExcelExportEntity("预计归还时间", "ghsj"));
        entity.add(new ExcelExportEntity("归还状态", "ghzt"));

        //获得数据库中的数据
        List<His_WarehousingBorrow> lists = his_warehousingBorrowService.selectAll();
        //easypoi中自定义模板 必须使用此类型的集合
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

        //将从数据库中获得的数据封装到一个map中,map<String,Object>   String :为属性 ,  object:为对应属性输出的数据
        lists.forEach(hwd -> {
            Map<String, Object> valMap = new HashMap<String, Object>();
            valMap.put("warehousingname", hwd.getHis_warehousing().getWarehousingname());
            valMap.put("warehousingclassification", hwd.getHis_warehousing().getWarehousingclassification());
            valMap.put("warehousingtermofvalidity",hwd.getHis_warehousing().getWarehousingtermofvalidity()==null?null:DateFormat.getDateInstance().format(hwd.getHis_warehousing().getWarehousingtermofvalidity()));
            valMap.put("warehousingdateofproduction", hwd.getHis_warehousing().getWarehousingdateofproduction()!=null?DateFormat.getDateInstance().format(hwd.getHis_warehousing().getWarehousingdateofproduction()):null);
            valMap.put("warehousingspecifications", hwd.getHis_warehousing().getWarehousingspecifications());
            valMap.put("warehousingstock", hwd.getHis_warehousing().getWarehousingstock());
            valMap.put("jymz", hwd.getWarehousingborrowpeoplename());
            valMap.put("jysl", hwd.getWarehousingborrownumber());
            valMap.put("jysj", hwd.getWarehousingborrowtime()!=null?DateFormat.getDateInstance().format(hwd.getWarehousingborrowtime()):null);
            valMap.put("ghsj", hwd.getWarehousingborrowreturntime()!=null?DateFormat.getDateInstance().format(hwd.getWarehousingborrowreturntime()):null);
            valMap.put("ghzt", hwd.getWarehousingborrowreturnstatus().equals("0")?"已归还":"未归还");
            list.add(valMap);
        });

//把我们构造好的bean对象放到params就可以了

        Workbook workbook = null;
        try {
            workbook = ExcelExportUtil.exportExcel(new ExportParams("物资借用", "物资借用"), entity, list);
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + "批量导出模板" + System.currentTimeMillis() + ".xls");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }


    }