Java导入完整版模板

1.在pom引入jra包

<!--导入导出excel-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
    <exclusions>
        <exclusion>
            <groupId>stax</groupId>
            <artifactId>stax-api</artifactId>
        </exclusion>
    </exclusions>
</dependency>

2.1.导入模板(util文件)

import java.io.*;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.neusoft.unieap.base.entity.ExcelImportEntity;
import com.neusoft.unieap.base.entity.SmImportDataDetailTemp;
import com.neusoft.unieap.base.util.xxsFilter.JsoupUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 被解析的Excel最好是什么样的呢？
 * 单元格最好都是文本格式，保存数据前自己去转换，不用poi带的转换。
 * 第一列 和最后一列 必须是必填字段!!!这样的你用我这个Util，得到的List就很准确了，不会出现多余的行或列。
 *
 * @author TMACJ
 * @version 0.000000.002899
 */
@Slf4j
public class ImportExcelUtil {

    private InputStream inputStream;
    private ExcelImportEntity excelImportEntity;
    private final static String excel2003L = ".xls";    //2003- 版本的excel
    private final static String excel2007U = ".xlsx";   //2007+ 版本的excel
    static SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    static Integer[] yyyyMMdd1 = {14, 31, 57, 58, 179, 184, 185, 186, 187, 188};//格式为yyyy/mm/dd
    static Integer[] yyyyMMdd2 = {176};//格式为yyyy-mm-dd
    static Integer[] HHmmss = {20, 32, 190, 191, 192};
    static List<Integer> yyyyMMddList1 = Arrays.asList(yyyyMMdd1);
    static List<Integer> yyyyMMddList2 = Arrays.asList(yyyyMMdd2);
    static List<Integer> hhMMssList = Arrays.asList(HHmmss);

    public ImportExcelUtil(InputStream inputStream, ExcelImportEntity excelImportEntity) {
        this.inputStream = inputStream;
        this.excelImportEntity = excelImportEntity;
    }

    public ImportExcelUtil() {
    }

    // 描述：获取IO流中的数据，组装成List<List<Object>>对象
    public List<SmImportDataDetailTemp> getBankListByExcel() throws Exception {
        List<SmImportDataDetailTemp> mapList = new ArrayList<>();
        //创建Excel工作薄
        Workbook work = this.getWorkbook(inputStream, excelImportEntity.getFileType());
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        //遍历Excel中所有的sheet
        SmImportDataDetailTemp smImportDataDetailTemp = null;
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            log.info("遍历sheet");
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            sheet.setAutobreaks(true);
            String sheetName = sheet.getSheetName();
            row = sheet.getRow(0);
            if (row == null) {
                continue;
            }
            int totalCell = sheet.getRow(0).getPhysicalNumberOfCells();//标题行一共有多少列
            for (int j = 0; j < totalCell; j++) {
                cell = row.getCell(j);
                if (cell == null || "".equals(getCellData(cell).trim())) {
                    totalCell = j;
                    break;
                }
            }
            log.info("标题行一共多少totalCell");
            //遍历当前sheet中的所有行
            for (int j = excelImportEntity.getBeginRow() - 1; j < sheet.getLastRowNum() + 1; j++) {
                smImportDataDetailTemp = new SmImportDataDetailTemp();
                smImportDataDetailTemp.setSheetName(sheetName);