Excel4J 实现excle的导入导出功能

1.首先在pom.xml文件里添加依赖

<dependency>
   <groupId>com.github.crab2died</groupId>
   <artifactId>Excel4J</artifactId>
   <version>3.0.0</version>
</dependency>

2.实体类

package net.longjin.entity.entityCase;

import com.github.crab2died.annotation.ExcelField;
import lombok.Data;

import java.io.Serializable;

/**
 * 描述:商标类案件登记
 *
 * @author 何志鹏
 * @ClassName:TrademarkCase
 * @create 2020-12-24 18:15
 * Version 1.0
 */
@Data
public class TrademarkCase implements Serializable {

    @ExcelField(title = "立案日期")
    private  String caseRegisterDate;

    @ExcelField(title = "序 号")
    private  String serialNumber;

    @ExcelField(title = "案 号")
    private  String caseNum;

    @ExcelField(title = "当   事   人")
    private  String party;

    @ExcelField(title = "案件来源")
    private  String caseSource;

    @ExcelField(title = "性 质")
    private  String natures;

    @ExcelField(title = "涉案商品")
    private  String involvedGoods;

    @ExcelField(title = "商标")
    private  String involvedIcon;

    @ExcelField(title = "强制措施日期")
    private  String enforceActionTime;

    @ExcelField(title = "强制措施数量")
    private  String enforceMeasureNum;

    @ExcelField(title = "是否为网络案件")
    private  String isNetCase;

    @ExcelField(title = "处理结果")
    private  String processingOtherContent;

    @ExcelField(title = "结案日期")
    private  String caseOverDate;

    @ExcelField(title = "经办人")
    private  String agent;

    @ExcelField(title = "备  注")
    private  String registerRemark;
}

3.service层：

package net.longjin.normalCase.service;

import java.io.InputStream;

/**
 * 描述:ExcleImportService
 *
 * @author 何志鹏
 * @ClassName:ExcleImportService
 * @create 2020-12-25 9:41
 * Version 1.0
 */
public interface ExcleImportService {

    /**
     *商标类案件登记导入
     *
     * @return
     */
    Boolean ImpotTrademarkCase(InputStream stream)throws  Exception;

}

4.service实现层：

package net.longjin.normalCase.service.impl;

import com.github.crab2died.ExcelUtils;
import net.longjin.comm.utils.StringStrUtils;
import net.longjin.commonSearch.comm.dao.CommonSearchDao;
import net.longjin.entity.entityCase.*;
import net.longjin.entity.system.SysOrg;
import net.longjin.normalCase.dao.NormalInfoDao;
import net.longjin.normalCase.dao.NormalPminfoDao;
import net.longjin.normalCase.service.ExcleImportService;
import net.longjin.simpleCase.dao.CasePartyDao;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.InputStream;
import java.util.List;

/**
 * 描述:ExcleImportServiceImpl
 *
 * @author 何志鹏
 * @ClassName:ExcleImportServiceImpl
 * @create 2020-12-25 9:43
 * Version 1.0
 */
@Service
@Transactional
public class ExcleImportServiceImpl  implements ExcleImportService {

    @Resource
    private NormalInfoDao normalInfoDao;

    @Autowired
    private NormalPminfoDao normalPminfoDao;

    @Autowired
    private CommonSearchDao commonSearchDao;

    @Autowired
    private CasePartyDao  casePartyDao;




    /**
     * 商标类案件登记导入
     *
     * @param stream
     * @return
     * @throws Exception
     */
    @Override
    public Boolean ImpotTrademarkCase(InputStream stream)throws  Exception {
        List<TrademarkCase> trademarkList = ExcelUtils.getInstance().readExcel2Objects(stream, TrademarkCase.class);
        for(TrademarkCase trademarkCase:trademarkList){
            String uuid = StringStrUtils.getUUID();
            NormalInfo  normalInfo = new NormalInfo();
            BeanUtils.copyProperties(trademarkCase,normalInfo);
            normalInfo.setNmId(uuid);
            String agent = trademarkCase.getAgent();
            String[] splits = agent.split("、");
            //主办案人名称
            normalInfo.setInvestigatorsName(splits[0]);
            //无部门默认为保护处
            normalInfo.setHandlingDepartment("197ff4ee3e194176ba88326419f422bf");
            //协助办案人名称
            normalInfo.setCoInvestigatorsName(splits[1]);
            normalInfo.setCaseStatus("caseOver");
            normalInfo.setProcessRunStatus("CASEOVER");
            normalInfoDao.doCreateSelective(normalInfo);
            NormalPminfo  normalPminfo = new NormalPminfo();
            BeanUtils.copyProperties(trademarkCase,normalPminfo);
            normalPminfo.setNmId(uuid);
            normalPminfoDao.doCreateSelective(normalPminfo);
            //添加当事人信息
            CaseParty caseParty = new CaseParty();
            caseParty.setPartyId(StringStrUtils.getUUID());
            caseParty.setScId(uuid);
            caseParty.setCaseType("normal");
            caseParty.setParty(trademarkCase.getParty());
            caseParty.setCreateTime(System.currentTimeMillis());
            casePartyDao.addCaseParty(caseParty);
        }
       return  Boolean.TRUE;
    }

}

5.controller层：

package net.longjin.normalCase.controller;

import net.longjin.entity.system.Results;
import net.longjin.normalCase.service.ExcleImportService;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.time.LocalDateTime;

/**
 * 描述:ExcleImportController
 *
 * @author 何志鹏
 * @ClassName:ExcleImportController
 * @create 2020-12-25 9:44
 * Version 1.0
 */

@RestController
@RequestMapping("/Excle")
public class ExcleImportController {

    @Autowired
    private ExcleImportService excleImportService;


    //导入功能
    @PostMapping(value = "/upload")
    public Results importExcel( @RequestParam("file") MultipartFile file, @RequestParam("importType") String importType,@RequestHeader("token")String token) {
        try {
            InputStream stream = file.getInputStream();
            //商标类案件登记
            Boolean   aBoolean = excleImportService.ImpotTrademarkCase(stream);
            if(aBoolean){
                return  Results.success(LocalDateTime.now().toString());
            }
            return Results.requestFailMsg(LocalDateTime.now().toString(),"导入失败");
        } catch (Exception e) {
            e.printStackTrace();
            return Results.requestFailMsg(LocalDateTime.now().toString(),"导入失败");
        }
    }
}

6.备注：导出调用方法如下：

 @Test
 public void object2Excel(){
     String tempPath = "D:\\IdeaSpace\\Excel4J\\src\\test\\java\\resource\\template.xlsx";
     List<Student> list = new ArrayList<>();
     list.add(new Student("1010001", "盖伦", "六年级三班"));
     list.add(new Student("1010002", "古尔丹", "一年级三班"));
     list.add(new Student("1010003", "蒙多", "六年级一班"));
     list.add(new Student("1010004", "萝卜特", "三年级二班"));
     Map<String, String> datas = new HashMap<>();
     datas.put("title", "战争学院花名册");
     datas.put("info", "学校统一花名册");
     ExcelUtil.getInstance().exportObj2ExcelByTemplate(datas, tempPath, "D:\\2.xlsx", list, Student.class, false, true);
 }