Excle导入

导入工具类ExcelUtil

package com.firefly.performance.core.utils;

import com.alibaba.nacos.common.utils.CollectionUtils;
import com.alibaba.nacos.common.utils.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author: haier excel导入
 * @date: 2022/07/19 17:16
 * @description:
 * @version: 1.0
 */
@Slf4j
public class ExcelUtil {

    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";
    private final static String DECIMAL = "[0-9]+\\.*[0-9]*";
    private final static String DECIMAL_TWO_SCALE = "(([0-9]{1}\\d*)(.\\d{1,2})?)|(0{1}.\\d{1,2})";
    private final static String ALL_NUMBER = "[0-9]*";
    private final static String NEGATIVE_ALL_NUMBER = "[-]?[0-9]*";
    private final static String NEGATIVE_ALL_NUMBER_POINT = "[-]?[0-9]+\\.*[0-9]*";

    /**
     * 读入excel文件，解析后返回
     *
     * @param file
     * @throws IOException
     */
    public static List<List<String>> readExcel(MultipartFile file) throws IOException {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<List<String>> listAll = new ArrayList<List<String>>();
        if (workbook != null) {
            try{
                for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                    //获得当前sheet工作表
                    Sheet sheet = workbook.getSheetAt(sheetNum);
                    if (sheet == null) {
                        continue;
                    }
                    //获得当前sheet的开始行
                    int firstRowNum = sheet.getFirstRowNum();
                    //获得当前sheet的结束行
                    int lastRowNum = sheet.getLastRowNum();
                    //循环除了第一行的所有行
                    log.info("物理编号：{}",sheet.getPhysicalNumberOfRows());
                    for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
                        //获得当前行
                        Row row = sheet.getRow(rowNum);
                        if (row == null) {
                            continue;
                        }
                        //获得当前行的开始列
                        int firstCellNum = row.getFirstCellNum();
                        //获得当前行的列数
                        int lastCellNum = row.getLastCellNum();


                        List<String> cellList = new ArrayList<String>();
                        //循环当前行
                        for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                            Cell cell = row.getCell(cellNum);
                            cellList.add(getCellValue(cell));
                        }
                        if (CollectionUtils.isEmpty(cellList)) {
                            continue;
                        }
                        listAll.add(cellList);
                    }
                }
            }catch (Exception e){
                log.error("excel解析异常:{}",e);
            }finally {
                workbook.close();
            }
        }
        return listAll;
    }

    public static void checkFile(MultipartFile file) throws IOException {
        //判断文件是否存在
        if (null == file) {
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if (!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
            throw new IOException(fileName + "不是excel文件");
        }
    }

    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        //获取excel文件的io流
        try(InputStream is = file.getInputStream()) {
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(XLS)) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(XLSX)) {
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            log.error("异常原因{}", e);
        }
        return workbook;
    }

    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) {
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
//                cellValue = String.valueOf(cell.getNumericCellValue())
                double numericCellValue = cell.getNumericCellValue();
                if (isIntegerForDouble(numericCellValue)){
                    DecimalFormat df = new DecimalFormat("0");
                    cellValue = df.format(numericCellValue);
                }else{
                    cellValue=Double.toString(numericCellValue);
                }
                break;
            case STRING:
                String stringCellValue = cell.getStringCellValue();
                if (isNumeric(stringCellValue)) {
                    if (isAllNumeric(stringCellValue)){
                        cellValue = String.valueOf(stringCellValue);
                    }else{
                        cellValue = String.valueOf(new BigDecimal(stringCellValue).setScale(2, RoundingMode.HALF_UP));
                    }
                }else{
                    cellValue = String.valueOf(stringCellValue);
                }
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK:
                //空值
                cellValue = "";
                break;
            case ERROR:
                //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * 判断是否为整数，是返回true，否则返回false
     */
    public static boolean isIntegerForDouble(Double num){
            double eqs=1e-10;
            return num-Math.floor(num)<eqs;
    }

    public static boolean checkExcelDataPlanB(List<String> cellValues) {
        boolean check = false;
        for (String cellValue : cellValues) {
            if (StringUtils.isNotEmpty(cellValue)) {
                check = false;
                break;
            } else {
                check = true;
            }
        }
        return check;
    }


    public static HSSFWorkbook getHssfWorkbook(String sheetName, String[] title, String[][] values) {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        HSSFSheet sheet = hssfWorkbook.createSheet(sheetName);

        HSSFRow row = sheet.createRow(0);

        HSSFCellStyle style = hssfWorkbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);

        HSSFCell cell = null;

        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return hssfWorkbook;
    }

    public static ResponseEntity<byte[]> download(HSSFWorkbook wb, String fileName) {
        HttpHeaders headers = new HttpHeaders();
        headers.setContentDispositionFormData("attachment", fileName);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);

        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        try {
            wb.write(outByteStream);
        } catch (IOException e) {
            log.error("异常原因：{}",e);
        }
        return new ResponseEntity<>(outByteStream.toByteArray(), headers, HttpStatus.OK);
    }


    /**
     * Mysql支持的时间戳限制
     */
    static long minTime = Timestamp.valueOf("1970-01-01 09:00:00").getTime();
    static long maxTime = Timestamp.valueOf("2038-01-19 11:00:00").getTime();

    /**
     * 判断 并 转换excel中日期列时间格式 ditNumber = 43607.4166666667转为时间戳
     */
    public static Long readAndRegxExcelDate(String ditNumber) {
        //如果不是数字
        if (!isNumeric(ditNumber)) {
            return null;
        }
        //如果是数字 小于0则 返回
        BigDecimal bd = new BigDecimal(ditNumber);
        //天数
        int days = bd.intValue();
        int mills = (int) Math.round(bd.subtract(new BigDecimal(days)).doubleValue() * 24 * 3600);

        //获取时间
        Calendar c = Calendar.getInstance();
        c.set(1900, Calendar.JANUARY, 1);
        c.add(Calendar.DATE, days - 2);
        int hour = mills / 3600;
        int minute = (mills - hour * 3600) / 60;
        int second = mills - hour * 3600 - minute * 60;
        c.set(Calendar.HOUR_OF_DAY, hour);
        c.set(Calendar.MINUTE, minute);
        c.set(Calendar.SECOND, second);
        long timeMillis = c.getTimeInMillis();

        //时间戳区间判断
        if (minTime <= timeMillis && timeMillis <= maxTime) {
            return timeMillis;
        } else {
            return null;
        }
    }

    //校验是否数据含小数点
    public static boolean isNumeric(String str) {
        Pattern pattern = Pattern.compile(DECIMAL);
        Matcher isNum = pattern.matcher(str);
        return isNum.matches();
    }

    /**
     * 校验是否为正负数(包含小数点)
     * @param str
     * @return
     */
    public static boolean isNumber(String str) {
        if(StringUtils.isEmpty(str)){
            return Boolean.FALSE;
        }
        Pattern pattern = Pattern.compile(NEGATIVE_ALL_NUMBER_POINT);
        Matcher isNum = pattern.matcher(str);
        return isNum.matches();
    }

    public static boolean isNumericAndTwoScale(String str) {
        Pattern pattern = Pattern.compile(DECIMAL_TWO_SCALE);
        Matcher isNum = pattern.matcher(str);
        return isNum.matches();
    }

    public static boolean isAllNumeric(String str) {
        //目标格式是否正确，纯数字
        Pattern compile = Pattern.compile(ALL_NUMBER);
        return compile.matcher(str).matches();
    }

    public static boolean isNegativeAllNumeric(String str) {
        //目标格式是否正确，纯数字
        Pattern compile = Pattern.compile(NEGATIVE_ALL_NUMBER);
        return compile.matcher(str).matches();
    }

    /**
     * 格式化金额 为千分位格式
     *
     * @param number
     * @return
     */
    public static String formatComma(BigDecimal number) {
        DecimalFormat df = new DecimalFormat("###,###");
        if (ObjectUtils.isNull(number)) {
            return "";
        }else{
            return df.format(number.setScale(0, RoundingMode.HALF_UP));
        }
    }

    /**
     * 判断数字a是否包含b,包含返回true,否则返回false
     * @param a
     * @param b
     * @return
     */
    public static boolean aContainsB(int a, int b) {
        a = Math.abs(a);
        b = Math.abs(b);
        int p = 1;
        while (p <= b) {
            p *= 10;
        }

        boolean contains = a == b;
        while (!contains && a >= b) {
            contains = a % p == b || a == b;
            a /= 10;
        }
        return contains;
    }

}

调用

package org.springframework.web.multipart;

public Boolean importTargetImportTool(MultipartFile file) throws IOException {
        List<List<String>> orgPerCellList = ExcelUtil.readExcel(file);
        log.info("excel解析后数据 data:{}", JSON.toJSONString(orgPerCellList));
        return true;
    }