easyPoi导入导出

依赖

           <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-spring-boot-starter</artifactId>
                <version>4.0.0</version>
            </dependency>

import java.io.Serializable;
import java.util.Date;

import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.*;

import javax.persistence.Table;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Table(name = "[classA]")
@ApiModel
public class classA implements Serializable {

    @ApiModelProperty(value = "id")
    private Long id;

    @ApiModelProperty(value = "批号")
    private String batchNo;

    @ApiModelProperty(value = "编码")
    @Excel(name="编码")
    private String dealerCode;

    @ApiModelProperty(value = "名称")
    @Excel(name="名称")
    private String dealerName;

    @ApiModelProperty(value = "部")
    @Excel(name="部")
    private String businessDivisionName;

    @ApiModelProperty(name="bizDate", value = "月份")
    @Excel(name="月份", format = "yyyy年MM月")
    private Date bizDate;

    @ApiModelProperty(value = "运营")
    @Excel(name="运营")
    private Double onlinePlatScore;

    @ApiModelProperty(value = "优秀案例分享")
    @Excel(name="优秀案例分享")
    private Double expScore;

    @ApiModelProperty(value = "贡献")
    @Excel(name="贡献")
    private Double dmcScore;

    @ApiModelProperty(value = "得分")
    @Excel(name="得分")
    private Double score;

    @ApiModelProperty(value = "公式")
    private String scoreFormula;

    @ApiModelProperty(value = "创建人")
    private String createBy;

    @ApiModelProperty(value = "创建时间")
    private Date createAt;

    private static final long serialVersionUID = 1L;
}

导入导出

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.export.styler.ExcelExportStylerBorderImpl;
import com.github.pagehelper.PageInfo;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;


@RestController
@RequestMapping("/integral/dmm")
@Api(value = "得分", tags = {"得分"})
public class IntegralScoreDmmController {

    @Autowired
    private IntegralScoreDmmService dmmService;

    @Autowired
    PositionTargetWeightConfigService configService;


    private static final String ONLINE_PLATE_RATE = "线上平台运营";
    private static final String EXP_RATE = "优秀经验分享";
    private static final String DMC_SCORE_RATE = "数字营销贡献";




    @ApiOperation("查询导入结果")
    @GetMapping("/query")
    public BaseResult<PageInfo> query(IntegralScoreDmm dmm) {
        return BaseResult.ok(dmmService.queryIntegralScoreDmmByPage(dmm));
    }


    @ApiOperation("导入")
    @PostMapping("/import")
    public BaseResult importBatch(MultipartFile file, HttpServletRequest request) {
        String batchNo = ExcelUtil.getBatchNo("DMM");
        try {
            int sheetNum = 0;
            List<ClassA> dmmList = ExcelUtil.importExcel(file, sheetNum, 0, 1, ClassA.class);
            String userId = LoginUserUtil.getCurrentUserId(request);
            // 取权重
            List<PositionTargetWeightConfig> configs = configService.getByPosition("数字营销经理");
            Map<String, Object> weightMap = configs.stream().collect(Collectors.toMap(PositionTargetWeightConfig::getTarget, Function.identity()));
            PositionTargetWeightConfig config = null;
            StringBuffer formula = new StringBuffer("=");
            for (ClassA dmm : dmmList) {
                dmm.setBatchNo(batchNo);
                //线上平台运营得分
                config = (PositionTargetWeightConfig) weightMap.get(ONLINE_PLATE_RATE);
                Double onlineWeight = config.getWeight();
                BigDecimal onlineScore = BigDecimal.valueOf(dmm.getOnlinePlatScore()).multiply(BigDecimal.valueOf(onlineWeight)).setScale(2);
                //优秀经验分享得分
                config = (PositionTargetWeightConfig) weightMap.get(EXP_RATE);
                Double expWeight = config.getWeight();
                BigDecimal expScore = BigDecimal.valueOf(dmm.getExpScore()).multiply(BigDecimal.valueOf(expWeight)).setScale(2);
                //数字营销贡献得分  todo 不确定
                config = (PositionTargetWeightConfig) weightMap.get(DMC_SCORE_RATE);
                Double dmcWeight = config.getWeight();
                BigDecimal dmcScore = BigDecimal.valueOf(dmm.getDmcScore()).multiply(BigDecimal.valueOf(dmcWeight)).setScale(2);

                //得分
                dmm.setScore(onlineScore.add(expScore).add(dmcScore).doubleValue());
                //得分公式
                formula.append("得分:").append(onlineScore).append("*").append(onlineWeight)
                        .append("+").append(expScore).append("*").append(expWeight)
                        .append("+").append(dmcScore).append("*").append(dmcWeight);
                dmm.setScoreFormula(formula.toString());
                dmm.setCreateBy(userId);
                dmmService.insert(dmm);
            }

        } catch (Exception e) {
            throw new BusinessException(e.getMessage());
        }
        return BaseResult.ok(batchNo);
    }




    @ApiOperation("导出")
    @GetMapping("/export")
    public void exportBatch(ClassA dmm, HttpServletResponse response) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            String sheetName = "积分发放排名得分管理(数字营销经理)";
            StringBuffer fileName = new StringBuffer(sheetName);
            if (StringUtils.isNotBlank(dmm.getBatchNo())) {
                fileName.append(dmm.getBatchNo());
            }
            fileName.append(".xlsx");

            //导出操作
            ExportParams exportParams = new ExportParams(null, sheetName);
            exportParams.setStyle(ExcelExportStylerBorderImpl.class);

            Workbook workbook = null;
            Boolean next = Boolean.TRUE;
            Integer page = 0;
            Integer pageSize = 2000;
            dmm.setPageSize(pageSize);
            while (next) {
                dmm.setPage(++page);
                PageInfo currentPage = dmmService.queryIntegralScoreDmmByPage(dmm);
                workbook = ExcelExportUtil.exportBigExcel(exportParams, IntegralScoreDmm.class, currentPage.getList());
                if (currentPage.getSize() < pageSize) {
                    next = Boolean.FALSE;
                }
            }
            ExcelExportUtil.closeExportBigExcel();

            //响应到客户端
            this.setResponseHeaderAndWriteResponse(response, fileName.toString(), workbook);
        } catch (Exception e) {
            throw new BusinessException(e.getMessage());
        }

    }

    public void setResponseHeaderAndWriteResponse(HttpServletResponse response, String fileName, Workbook workbook) throws IOException {
        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());
    }

}

工具类

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.NoSuchElementException;

public class ExcelUtil {

    private static final String DATE_FORMAT = "yyyyMMdd";

    public static String getBatchNo(String prefix) {
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
        StringBuffer batchNo = new StringBuffer(prefix);
        batchNo.append(sdf.format(System.currentTimeMillis()))
                .append((int)((Math.random()*9+1)*100000));
        return batchNo.toString();
    }

    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        //这样写excel能兼容03和07
        InputStream is = file.getInputStream();
        Workbook hssfWorkbook = null;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (Exception ex) {
            is =file.getInputStream();
            hssfWorkbook = new XSSFWorkbook(is);
        }
        return hssfWorkbook;
    }

    /**
     * 功能描述：根据接收的Excel文件来导入多个sheet,根据索引可返回一个集合
     * @param file 导入文件路径
     * @param sheetIndex 导入sheet索引
     * @param headerRows 表头行数
     * @param pojoClass Excel实体类
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, int sheetIndex, int titleRows, Integer headerRows, Class<T> pojoClass) {
        // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
        ImportParams params = new ImportParams();
        // 第几个sheet页
        params.setStartSheetIndex(sheetIndex);
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

}