关于java的Excel导入导出之easypoi

导入easypoi相关jar包,这里的easypoi-base的包也可以不倒入，因为easypoi-web中有依赖easypoi-base会自动导入的

 
 
     cn.afterturn
     easypoi-base
     4.2.0
 
 
 
     cn.afterturn
     easypoi-annotation
     4.2.0
 
 
 
     cn.afterturn
     easypoi-web
     4.2.0
 

编写导出使用的Javabean，根据具体的需求来定。

 //导出实体类的唯一标识
 @ExcelTarget("EcCommodityManagementExport")
 //提供get、set
 @Data
 //实现序列化
 public class EcCommodityManagementExportVo implements Serializable {
  private static final long serialVersionUID = 42L;

 /**
 * 图片  ，此处做的第一列与第二列的列名合并(只是列名合并)，因为合并之后是第一列的值作为新值填充所以与第二列		* 的name一样
 * name 导出时的列名名称
 * type 导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本
 * imageType 导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的
 * numFormat 数字格式化,参数是Pattern,使用的对象是DecimalFormat
 * replace 值得替换  导出是{a_id,b_id} 导入反过来,所以只用写一个
 * databaseFormat 导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数			* 据库格式
  */
  @Excel(name = "商品名称（规格描述、单位）" , type = 2 , imageType = 1 , height = 54 , width = 25)
  private String picUrl;

  /**
  * 商品名称
   */
   @Excel(name = "商品名称（规格描述、单位）" , height = 54 , width = 25)
   private String productName;

  /**
  * 商品编码
   */
   @Excel(name = "商品编码" , height = 54 , width = 25)
   private String productCode;

   /**
   * 原价
   */
   @Excel(name = "原价" , height = 54 , width = 25 , numFormat = "0.00")
   private BigDecimal primeCost;

  /**
  * 售价
  */
  @Excel(name = "售价" , height = 54 , width = 25 , numFormat = "0.00")
  private BigDecimal price;

 	/**
  * 库存
   */
   @Excel(name = "库存" , height = 54 , width = 25)
  private int repertory;

   /**
  * 商品状态(0未提交1已提交2已上架3已下架)
   */
  @Excel(name = "商品状态" , height = 54 , width = 25 , replace = {"未提交_0","已提交_1","已上架_2","已下架_3"})
  private int productStatus;

  /**
  * 上架时间
   */
   @Excel(name = "上架时间" , height = 54 , width = 25 , databaseFormat = "yyyy-MM-dd HH:mm:ss" , format = "yyyy-MM-dd HH:mm:ss")
  private String addedTime;

  /**
  * 审核状态(0--1未审核2审核成功3审核失败)
  */
  @Excel(name = "审核状态" , height = 54 , width = 25 , replace = {"--_0","未审核_1","审核成功_2","审核失败_3"})
  private int auditStatus;

  /**
  * 审核人姓名
   */
   @Excel(name = "审核人姓名" , height = 54 , width = 25)
  private String auditUserName;

  /**
  * 创建人姓名
  */
  @Excel(name = "创建人姓名" , height = 54 , width = 25)
  private String createUserName;

  /**
  * 创建时间
  */
  @Excel(name = "创建时间" , height = 54 , width = 25 , databaseFormat = "yyyy-MM-dd HH:mm:ss" , format = "yyyy-MM-dd HH:mm:ss")
  private Date createTime;

 }

编写导出控制层代码

 @ApiOperation(value = "导出" , notes = "导出")
 @GetMapping("exportProduct")
 public void exportProduct(@RequestParam("productIdList") List productIdList, HttpServletRequest request, HttpServletResponse response) throws Exception{
 ecCommodityManagementService.exportProduct(productIdList,request,response);
  }

编写导出业务层代码

 /**
  * 导出
  * @param productIdList
  * @param request
  * @param response
  */
 @Override
 public void exportProduct(List productIdList, HttpServletRequest request, HttpServletResponse response) throws Exception{
 //如果勾选商品则查询勾选的数据，如没有勾选则查所有商品数据
 List ecCommodityManagementList = ecCommodityManagementMapper.findExportData(productIdList);
 if (ecCommodityManagementList != null && ecCommodityManagementList.size() > 0){
     //封装图片数据
     for (EcCommodityManagementExportVo ecCommodityManagementExportVo : ecCommodityManagementList) {
 	//导出图片时如果是链接形式要填写全路径
         ecCommodityManagementExportVo.setPicUrl(fileUrl + ecCommodityManagementExportVo.getPicUrl());
     }
 }
 //设置导出文件表头及sheet名
 ExportParams exportParams = new ExportParams("商品列表", "商品列表");
 //设置导出类型为Excel
 exportParams.setType(ExcelType.XSSF);
 //创建数据导出对象
 Workbook workbook = ExcelExportUtil.exportExcel(exportParams, EcCommodityManagementExportVo.class, ecCommodityManagementList);
 //合并单元格new CellRangeAddress(从第几行开始, 到第几行结束, 从第几列开始, 到第几列结束)
 CellRangeAddress cellAddresses = new CellRangeAddress(1, 1, 0, 1);
 workbook.getSheetAt(0).addMergedRegion(cellAddresses);
 //创建导出文件名
 String fileName = "商品列表.xlsx";
 if (workbook != null){
     //让浏览器看的懂我们要干啥
     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());
 }
 }

注：持久层代码就不贴了

编写导入使用的Javabean，根据具体的需求来定。

 //导入实体类的唯一标识
 @ExcelTarget("uploadExcel")
 //提供get、set
 @Data
 //实现序列化
 public class UploadExcelVo implements Serializable {
 //name 对应导入的Excel中的列名，必须一致！
 @Excel(name = "题目")
 private String topic;

 @Excel(name = "选项A")
 private String optionA;

 @Excel(name = "选项B")
 private String optionB;

 @Excel(name = "选项C")
 private String optionC;

 @Excel(name = "选项D")
 private String optionD;

 @Excel(name = "正确答案(多个答案逗号隔开且必须大写)")
 private String rightAnswers;

 }

编写导入控制层代码

 @ApiOperation(value = "题目模板导入" , notes = "题目模板导入")
 @PostMapping("uploadExcel")
 @ResponseBody
 public ResultBody uploadExcel(HttpServletRequest request,MultipartFile file){
 	return sybQuestionBankService.uploadExcel(request,file);
 }

编写导入业务层代码
`

 /**
 * 题目模板导入
 * @param file
 * @return
 */
 @Override
 public ResultBody uploadExcel(HttpServletRequest request,MultipartFile file) {
 //导入数据总条数
 int totalNum = 0;
 //导入成功条数
 int cg = 0;
 try {
     String bankId = request.getParameter("bankId");
     //创建数据导入规则对象
     ImportParams importParams = new ImportParams();
     //设置第一行为表头不读取
     importParams.setHeadRows(1);
     //验证Excel
     importParams.setNeedVerify(true);
     //工具类进行读取直接封装返回值类型UploadExcelVo
     ExcelImportResult objectExcelImportResult = ExcelImportUtil.importExcelMore(file.getInputStream(), UploadExcelVo.class, importParams);
     //遍历读取到的数据
     if (objectExcelImportResult.getList() != null && objectExcelImportResult.getList().size() > 0){
         totalNum = objectExcelImportResult.getList().size();
         SybQuestionBankDetail byQuestionBankIdLimitOne = sybQuestionBankDetailDao.findByQuestionBankIdLimitOne(bankId);
         int detailSort = 0;
         if (byQuestionBankIdLimitOne != null && !byQuestionBankIdLimitOne.equals("")){
             detailSort = byQuestionBankIdLimitOne.getDetailSort()+1;
         }else {
             detailSort = 1;
         }
         for (UploadExcelVo uploadExcelVo : objectExcelImportResult.getList()) {
             //创建题目对象进行存储数据
             SybQuestionBankDetail sybQuestionBankDetail = new SybQuestionBankDetail();
             sybQuestionBankDetail.setId(UUIDGenerator.getUUID());
             sybQuestionBankDetail.setIsDelete(0);
             sybQuestionBankDetail.setTopic(uploadExcelVo.getTopic());
             sybQuestionBankDetail.setQuestionBankId(bankId);
             sybQuestionBankDetail.setDetailSort(detailSort);
             sybQuestionBankDetail.setCreateTime(new Date());
             //封装题目选项
             List> options = new ArrayList<>();
             Map mapA = new HashMap<>();
             mapA.put("optionKey","A");
             mapA.put("optionValue",uploadExcelVo.getOptionA());
             options.add(mapA);
             Map mapB = new HashMap<>();
             mapB.put("optionKey","B");
             mapB.put("optionValue",uploadExcelVo.getOptionB());
             options.add(mapB);
             if (uploadExcelVo.getOptionC() != null && !uploadExcelVo.getOptionC().equals("")){
                 Map mapC = new HashMap<>();
                 mapC.put("optionKey","C");
                 mapC.put("optionValue",uploadExcelVo.getOptionC());
                 options.add(mapC);
             }
             if (uploadExcelVo.getOptionD() != null && !uploadExcelVo.getOptionD().equals("")){
                 Map mapD = new HashMap<>();
                 mapD.put("optionKey","D");
                 mapD.put("optionValue",uploadExcelVo.getOptionD());
                 options.add(mapD);
             }
             sybQuestionBankDetail.setOptions(JSON.toJSONString(options));
             //封装正确答案
             String[] split = uploadExcelVo.getRightAnswers().split(",", -1);
             sybQuestionBankDetail.setRightAnswers(JSON.toJSONString(split));
             int insert = sybQuestionBankDetailDao.insert(sybQuestionBankDetail);
             if (insert > 0){
                 detailSort++;
                 cg++;
             }
         }
     }
 } catch (Exception e) {
     e.printStackTrace();
 }
 return ResultBody.ok().data("应导入"+totalNum+"条题目，导入成功"+cg+"条");
 }

注：持久层代码就不贴了
以上就是easypoi的导入导出的使用。