excel表格导入到数据库

controller层
package com.cqlt.test1.controller;

import com.cqlt.test1.enums.RestMessageEnum;
import com.cqlt.test1.pojo.Person;
import com.cqlt.test1.service.AuditService;
import com.cqlt.test1.service.PersonService;
import com.cqlt.test1.utils.RetMessageUtils;
import com.cqlt.test1.utils.ServiceResponse;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

@RestController
@RequestMapping("/audit")
public class AuditController extends BaseController {
@Autowired
private AuditService auditService;

private static final Logger log = LogManager.getLogger(Person.class);
@PostMapping("/impAuditCost")
public ServiceResponse impAuditCost(@RequestParam("file") MultipartFile file, @RequestParam Long id){
    try {
        int total = auditService.impAuditCost(file,id);
        if(total==0){
            return retMsg(RestMessageEnum.MODEL_ERROR);
        }
        return  retMsg(RestMessageEnum.SUCCESS);
    }catch (Exception e){
        log.error(e.getMessage(),e);
        return retMsgServerException();
    }
}

}

service层
package com.cqlt.test1.service;

import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

public interface AuditService {
int impAuditCost(MultipartFile file, Long id) throws IOException;
}
serviceImpl层
package com.cqlt.test1.service.impl;

import com.alibaba.excel.EasyExcel;
import com.cqlt.test1.annotion.TableConstant;
import com.cqlt.test1.excel.AuditCostData;
import com.cqlt.test1.excel.AuditCostDataListener;
import com.cqlt.test1.mapper.AuditCostMapper;
import com.cqlt.test1.pojo.AuditCost;
import com.cqlt.test1.service.AuditService;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class IAuditService implements AuditService {
@Resource
private AuditCostMapper auditCostMapper;
@Override
public int impAuditCost(MultipartFile file, Long id) throws IOException {
AuditCostDataListener auditCostDataListener = new AuditCostDataListener();
EasyExcel.read(file.getInputStream(),
AuditCostData.class,auditCostDataListener).sheet().doRead();
List list = auditCostDataListener.getList();
List costs = new ArrayList<>();
List cost2 = new ArrayList<>();
AuditCost auditCost = new AuditCost();
AuditCost auditCost1 = new AuditCost();
boolean data =false;
boolean end =false;
boolean end1 =false;
int seq = 1;
for (int i = 0; i < list.size(); i++) {
AuditCostData cost = list.get(i);
if(cost.getLine2()==null){
continue;
}
if(cost.getLine2().contains(“行次”)&&cost.getLine6().contains(“行次”)){
data = true;
if(cost.getLine3().contains(“末”)){
end = true;
}
if(cost.getLine7().contains(“末”)){
end1 = true;
}

            continue;
        }
        if(data){
            auditCost = new AuditCost();
            auditCost.setAuditId(id);
            auditCost.setCostType(TableConstant.ASSETS);
            auditCost.setCostName(cost.getLine1());
            auditCost.setLine(cost.getLine2());
            if(StringUtils.isNotEmpty(cost.getLine3())){
                String line3 = new BigDecimal(cost.getLine3()).setScale(2,BigDecimal.ROUND_HALF_UP).toPlainString();
                if (end) {
                    auditCost.setBalanceEnd(line3);
                }else {
                    auditCost.setBalanceStart(line3);
                }
            }
            if(StringUtils.isNotEmpty(cost.getLine4())){
                String line4 = new BigDecimal(cost.getLine4()).setScale(2,BigDecimal.ROUND_HALF_UP).toPlainString();
                if (end) {
                    auditCost.setBalanceStart(line4);
                }else {
                    auditCost.setBalanceEnd(line4);
                }
            }

            auditCost.setSeq(seq);

            auditCost1 = new AuditCost();

            auditCost1.setAuditId(id);
            auditCost1.setCostType(TableConstant.DEBT);
            auditCost1.setCostName(cost.getLine5());
            auditCost1.setLine(cost.getLine6());

            if(StringUtils.isNotEmpty(cost.getLine7())){
                String line7 = new BigDecimal(cost.getLine7()).setScale(2,BigDecimal.ROUND_HALF_UP).toPlainString();
                if (end1) {
                    auditCost1.setBalanceEnd(line7);
                }else {
                    auditCost1.setBalanceStart(line7);
                }
            }
            if(StringUtils.isNotEmpty(cost.getLine8())){
                String line8 = new BigDecimal(cost.getLine8()).setScale(2,BigDecimal.ROUND_HALF_UP).toPlainString();
                if (end1) {
                    auditCost1.setBalanceStart(line8);
                }else {
                    auditCost1.setBalanceEnd(line8);
                }
            }

            auditCost1.setSeq(seq);
            costs.add(auditCost);
            cost2.add(auditCost1);
            seq ++;
        }
    }

    if(!CollectionUtils.isEmpty(costs)){
        auditCostMapper.deleteByAuditId(id);
        int total = auditCostMapper.batchInsertCost(costs);
        int total2 = auditCostMapper.batchInsertCost(cost2);
        return total+total2;
    }

    return 0;
}

}
mapper层
package com.cqlt.test1.mapper;

import com.cqlt.test1.pojo.AuditCost;

import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface AuditCostMapper {
// int deleteByPrimaryKey(Long id);
//
// int insert(AuditCost record);
//
// int insertSelective(AuditCost record);
//
// AuditCost selectByPrimaryKey(Long id);
//
// int updateByPrimaryKeySelective(AuditCost record);
//
// int updateByPrimaryKey(AuditCost record);

int batchInsertCost(List<AuditCost> costs);

int deleteByAuditId(Long id);

// List listAuditCost(AuditCost auditCost);
}
mapping.xml层

<?xml version="1.0" encoding="UTF-8" ?> id, audit_id, cost_type, cost_name, line, balance_end, balance_start, seq select from audit_cost where id = #{id,jdbcType=BIGINT} delete from audit_cost where id = #{id,jdbcType=BIGINT} insert into audit_cost (id, audit_id, cost_type, cost_name, line, balance_end, balance_start, seq) values (#{id,jdbcType=BIGINT}, #{auditId,jdbcType=BIGINT}, #{costType,jdbcType=VARCHAR}, #{costName,jdbcType=VARCHAR}, #{line,jdbcType=VARCHAR}, #{balanceEnd,jdbcType=VARCHAR}, #{balanceStart,jdbcType=VARCHAR}, #{seq,jdbcType=INTEGER}) insert into audit_cost id, audit_id, cost_type, cost_name, line, balance_end, balance_start, seq, #{id,jdbcType=BIGINT}, #{auditId,jdbcType=BIGINT}, #{costType,jdbcType=VARCHAR}, #{costName,jdbcType=VARCHAR}, #{line,jdbcType=VARCHAR}, #{balanceEnd,jdbcType=VARCHAR}, #{balanceStart,jdbcType=VARCHAR}, #{seq,jdbcType=INTEGER}, update audit_cost audit_id = #{auditId,jdbcType=BIGINT}, cost_type = #{costType,jdbcType=VARCHAR}, cost_name = #{costName,jdbcType=VARCHAR}, line = #{line,jdbcType=VARCHAR}, balance_end = #{balanceEnd,jdbcType=VARCHAR}, balance_start = #{balanceStart,jdbcType=VARCHAR}, seq = #{seq,jdbcType=INTEGER}, where id = #{id,jdbcType=BIGINT} update audit_cost set audit_id = #{auditId,jdbcType=BIGINT}, cost_type = #{costType,jdbcType=VARCHAR}, cost_name = #{costName,jdbcType=VARCHAR}, line = #{line,jdbcType=VARCHAR}, balance_end = #{balanceEnd,jdbcType=VARCHAR}, balance_start = #{balanceStart,jdbcType=VARCHAR}, seq = #{seq,jdbcType=INTEGER} where id = #{id,jdbcType=BIGINT}

<insert id="batchInsertCost" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
    insert into audit_cost (id, audit_id, cost_type,
    cost_name, line, balance_end,
    balance_start, seq)
    values
    <foreach collection="list" item="params" index="index"
             separator=",">
        (#{params.id,jdbcType=BIGINT}, #{params.auditId,jdbcType=BIGINT}, #{params.costType,jdbcType=VARCHAR},
        #{params.costName,jdbcType=VARCHAR}, #{params.line,jdbcType=VARCHAR}, #{params.balanceEnd,jdbcType=VARCHAR},
        #{params.balanceStart,jdbcType=VARCHAR}, #{params.seq,jdbcType=INTEGER})
    </foreach>
</insert>


<delete id="deleteByAuditId" parameterType="java.lang.Long">
delete from audit_cost
where audit_id = #{id,jdbcType=BIGINT}