data-daq/service-base/src/main/java/com/simuwang/base/common/util/ExcelUtil.java

package com.simuwang.base.common.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.NumberFormat;
import java.util.List;
import org.apache.commons.compress.archivers.ArchiveEntry;
import org.apache.commons.compress.archivers.ArchiveInputStream;
import org.apache.commons.compress.archivers.ArchiveStreamFactory;
import org.apache.commons.compress.utils.IOUtils;

public class ExcelUtil {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    public static boolean isExcel(String name) {
        return name.endsWith("xls") || name.endsWith("xlsx");
    }

    public static boolean isPdf(String name) {
        return name.endsWith("pdf") || name.endsWith("PDF");
    }

    public static boolean isZip(String filePath) {
        return filePath.endsWith("zip") || filePath.endsWith("ZIP") || filePath.endsWith("rar") || filePath.endsWith("RAR");
    }

    public static Sheet getSheet(File file, int sheetIndex) {
        if (file == null || !file.exists()) {
            return null;
        }
        InputStream is = file2InStream(file);
        String[] arr = file.getName().split("\\.");
        String ext = arr[arr.length - 1];
        Sheet sheet = null;
        try {
            sheet = getSheet(is, ext, sheetIndex);
        } catch (Exception e) {
            logger.error(e.getMessage());
            if (e.getMessage().contains("XSSF instead of HSSF")) {
                is = file2InStream(file);
                try {
                    sheet = getSheet(is, "xlsx", sheetIndex);
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            } else if (e.getMessage().contains("HSSF instead of XSSF")) {
                is = file2InStream(file);
                try {
                    sheet = getSheet(is, "xls", sheetIndex);
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            } else if ("xls".equals(ext) && e.getMessage().contains("left 4 bytes remaining still to be read")) {
                file = changeXls(file);
                is = file2InStream(file);
                try {
                    sheet = getSheet(is, "xls", sheetIndex);
                    logger.info("文件转换成功！");
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            } else {
                sheet = null;
            }
        }
        return sheet;
    }

    public static Sheet getSheet(InputStream is, String ext, int sheetIndex) throws IOException {
        if (ext == null) {
            ext = "xls";
        }
        ext = ext.toLowerCase();
        Sheet sheet = null;
        if ("xls".equals(ext)) {
            Workbook wb = new HSSFWorkbook(is);
            sheet = getSheet(wb, sheetIndex, null);
        } else if ("xlsx".equals(ext)) {
            Workbook wb = new XSSFWorkbook(is);
            sheet = getSheet(wb, sheetIndex, null);
        }
        return sheet;
    }

    private static Sheet getSheet(Workbook wb, int sheetIndex, String sheetName) throws IOException {
        if (wb != null) {
            Sheet sheet = null;
            if (sheetIndex > -1) {
                sheet = wb.getSheetAt(sheetIndex);
            } else if (StrUtil.isNotBlank(sheetName) && !"null".equals(sheetName.toLowerCase())) {
                sheet = wb.getSheet(sheetName);
            }
            wb.close();
            // 同时会把文件输入流关闭
            return sheet;
        }
        return null;
    }

    public static InputStream file2InStream(File file) {
        if (!file.exists()) {
            return null;
        } else {
            FileInputStream is = null;

            try {
                is = new FileInputStream(file);
            } catch (FileNotFoundException var3) {
                var3.printStackTrace();
            }

            return is;
        }
    }

    public static File changeXls(File file) {
        try {
            jxl.Workbook workbook = jxl.Workbook.getWorkbook(file);
            String fileName = "copy-" + file.getName();
            File file2 = new File(file.getParent() + "/" + fileName);
            if (file2.exists()) {
                return file2;
            }
            jxl.write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(file2, workbook);
            wwb.write();
            wwb.close();
            workbook.close();
            return file2;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        String cellValue = "";

        switch (cell.getCellTypeEnum()) {
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    // 如果是日期格式的数字
                    cellValue = cell.getDateCellValue().toString();
                } else {
                    // 否则是纯数字
                    NumberFormat numberFormat = NumberFormat.getNumberInstance();
                    numberFormat.setMaximumFractionDigits(15);
                    double formulaResult = cell.getNumericCellValue();
                    cellValue = numberFormat.format(formulaResult).replaceAll(",", "");
                }
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                // 处理公式结果
                try {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    cellValue = cell.getStringCellValue();
                }
                break;
            case BLANK:
                break;
            case ERROR:
                cellValue = "ERROR: " + cell.getErrorCellValue();
                break;
            default:
                cellValue = "";
                break;
        }
        // 去掉换行符号
        cellValue = StrUtil.isNotBlank(cellValue) ? cellValue.replaceAll("[\\r\\n]+", "") : "";
        return cellValue;
    }

    public static List<String> extractCompressedFiles(String zipFilePath, String destFilePath) {
        List<String> filePathList = CollUtil.newArrayList();

        File destFile = new File(destFilePath);
        if (!destFile.exists()) {
            destFile.mkdirs();
        }

        try (BufferedInputStream fis = new BufferedInputStream(new FileInputStream(zipFilePath));
             ArchiveInputStream ais = new ArchiveStreamFactory().createArchiveInputStream(fis)) {
            ArchiveEntry entry;
            while ((entry = ais.getNextEntry()) != null) {
                File entryFile = new File(destFilePath, entry.getName());
                if (entry.isDirectory()) {
                    entryFile.mkdirs();
                } else {
                    try (FileOutputStream fos = new FileOutputStream(entryFile)) {
                        IOUtils.copy(ais, fos);
                        filePathList.add(entryFile.getAbsolutePath());
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return filePathList;
    }

}