daxt/src/test/java/com/gz/ImportArchives.java

package com.gz;

import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSONObject;
import com.gz.core.exception.BusinessException;
import com.gz.dto.archive.ArchiveDTO;
import com.gz.mapper.archive.ArchiveMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.util.LittleEndianOutputStream;
import org.junit.jupiter.api.Test;
import org.omg.CORBA.IRObject;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;
import sun.rmi.runtime.Log;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * @author LiuchangLan
 * @date 2021/2/25 13:04
 */
@Slf4j
@SpringBootTest
class ImportArchives {

    private String excelPath = "C:\\Users\\84731\\Desktop\\a.xlsx";

    @Resource
    private ArchiveMapper mapper;

    @Test
//    @Transactional
    public void test() {
        ExcelReader reader = ExcelUtil.getReader(FileUtil.file(excelPath), 0);
        List<List<Object>> rows = reader.read();
        for (int i = 1; i < rows.size(); i++) {
            try {
                List<Object> row = rows.get(i);
                ArchiveDTO dto = new ArchiveDTO();
                dto.setDagdm(this.getExcelCellStringValue(row, 0));
                dto.setDh(this.getExcelCellStringValue(row, 1));
                dto.setQzh(this.getExcelCellStringValue(row, 2));
                dto.setGdnd(this.getExcelCellStringValue(row, 3));
                dto.setMj(this.getExcelCellStringValue(row, 4));
                dto.setBgqx(this.getExcelCellStringValue(row, 5));
                // TODO: 2021/2/25 机构代号 6 馆编号 7
                dto.setZtsl(this.getExcelCellIntegerValue(row, 8));
                dto.setWjbh(this.getExcelCellStringValue(row, 9));
                dto.setWjxcsj(this.getExcelCellStringValue(row, 10));
                dto.setZrz(this.getExcelCellStringValue(row, 11));
                dto.setTm(this.getExcelCellStringValue(row, 12));
                dto.setBz(this.getExcelCellStringValue(row, 13));
                dto.setZbbm(this.getExcelCellStringValue(row, 14));
                dto.setXbbm(this.getExcelCellStringValue(row, 15));
                dto.setKzf(this.getExcelCellStringValue(row, 16));
                dto.setDzwdh(this.getExcelCellStringValue(row, 17));
                dto.setSwh(this.getExcelCellStringValue(row, 18));
                dto.setGb(this.getExcelCellStringValue(row, 19));
                dto.setWz(this.getExcelCellStringValue(row, 20));
                dto.setZtgg(this.getExcelCellStringValue(row, 21));
                dto.setZtlx(this.getExcelCellStringValue(row, 22));
                dto.setZtdw(this.getExcelCellStringValue(row, 23));
                dto.setZzjgdm(this.getExcelCellStringValue(row, 24));
                dto.setFlh(this.getExcelCellStringValue(row, 25));
                dto.setZtc(this.getExcelCellStringValue(row, 26));
                dto.setQwbs(this.getExcelCellStringValue(row, 27));
                System.out.println(dto);
                mapper.insertSelective(dto);
            } catch (Exception e) {
                new BusinessException(500, String.format("导入出错，错误位置:{}行,错误原因{}", (i + 1), e.getMessage()));
            }
        }
        log.info("成功添加{}行数据", rows.size());
    }

    private String getExcelCellStringValue(List<Object> row, int index) {
        if (index >= row.size()) {
            return null;
        }
        return String.valueOf(row.get(index));
    }

    private Integer getExcelCellIntegerValue(List<Object> row, int index) {
        return Integer.valueOf(getExcelCellStringValue(row, index));
    }

}