sis-background/gyee-sample-impala/src/main/java/com/gyee/impala/common/util/ExcelUtil.java

package com.gyee.impala.common.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.util.StringUtils;

import java.io.*;
import java.util.*;

/**
 */
@Slf4j
public class ExcelUtil {

    /**
     * 解析场站的部件DI测点
     *
     * @param fileName 文件名
     * @return
     */
    public static Map<String, List<String>> readExcelToMap(String fileName) {
        Map<String, List<String>> map = new HashMap<>();
        File file = new File(fileName);

        if (!file.isFile() && file.exists()) {
            log.error("解析的excel文件不存在！");
            return map;
        }

        Workbook wb;
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
            if (checkFile(fileName) == 1) {
                wb = new XSSFWorkbook(file);
            } else if (checkFile(fileName) == 2) {
                wb = new HSSFWorkbook(fis);
            } else {
                log.error("解析的文件类型错误！");
                return map;
            }

            // 解析文件  第一个sheet用不到
            for (int i = 1; i < wb.getNumberOfSheets(); i++) {
                Sheet sheet = wb.getSheetAt(i);
                if (sheet == null)
                    continue;
                if(i == 2)
                    break;
                String key = null;
                List<String> list = new ArrayList<>();
                // 第一行是标题，不解析
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                    Row row = sheet.getRow(j);
                    if (row == null)
                        continue;

                    String temp = "";
                    key = row.getCell(0).toString() + row.getCell(1).toString();
                    for (int k = 2; k < row.getLastCellNum(); k++) {
                        String data = row.getCell(k).toString();
                        if (StringUtils.hasText(data))
                            temp += data;
                        if (k < row.getLastCellNum() - 1)
                            temp += ",";
                    }
                    list.add(temp);
                }

                map.put(key, list);
            }

        } catch (Exception e) {
            log.error("excel文件解析异常：" + e.getMessage());
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return map;
    }


    /**
     * 解析风机基础配置
     *
     * @param fileName
     * @return
     */
    public static List<String[]> readExcelToList(String fileName) {
        List<String[]> list = new ArrayList<>();
        File file = new File(fileName);

        if (!file.isFile() && file.exists()) {
            log.error("解析的excel文件不存在！");
            return list;
        }

        Workbook wb;
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
            if (checkFile(fileName) == 1) {
                wb = new XSSFWorkbook(file);
            } else if (checkFile(fileName) == 2) {
                wb = new HSSFWorkbook(fis);
            } else {
                log.error("解析的文件类型错误！");
                return list;
            }

            // 解析文件  读第一个sheet

            Sheet sheet = wb.getSheetAt(0);
            // 第一行是标题，不解析
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null)
                    continue;

                String[] str = new String[row.getLastCellNum()];
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    String data = row.getCell(j) + "";
                    str[j] = data;
                }
                list.add(str);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return list;
    }


    /**
     * 判断File文件的类型
     *
     * @param fileName 传入的文件
     * @return 1-XLS文件，2-XLSX文件，3-文件类型错误
     */
    public static int checkFile(String fileName) {
        if (fileName.endsWith(".xlsx")) {
            return 1;
        }
        if (fileName.endsWith(".xls")) {
            return 2;
        }

        return 3;
    }
}