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.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.simuwang.base.common.conts.DateConst;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.NumberFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;

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");
    }

    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)) {
                    // 如果是日期格式的数字
                    Date dateCellValue = cell.getDateCellValue();
                    cellValue = DateUtil.format(dateCellValue, DateConst.YYYY_MM_DD);
                } 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.getPath());
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return filePathList;
    }

    public static void writeDataToSheet(Sheet sheet, Elements rows) {
        int rowSize = rows.size();
        for (int rowNum = 0; rowNum < rowSize; rowNum++) {
            Row sheetRow = sheet.createRow(rowNum);

            Element elementRow = rows.get(rowNum);
            Elements cells = elementRow.select("td");
            if (cells.size() == 0) {
                cells = elementRow.select("th");
            }
            int cellSize = cells.size();
            for (int cellNum = 0; cellNum < cellSize; cellNum++) {
                Cell sheetRowCell = sheetRow.createCell(cellNum);
                sheetRowCell.setCellValue(cells.get(cellNum).text());
            }
        }
    }


    /**
     * 获取优先级高的字段值
     *
     * @param sheetRow         行
     * @param priorityPosition 字段位置
     * @param basePosition     优先级搞的字段位置
     * @return 优先级高的字段值
     */
    public static String getPriorityFieldValue(Row sheetRow, Integer priorityPosition, Integer basePosition) {
        boolean hasPriorityValue = priorityPosition != null && sheetRow.getCell(priorityPosition) != null && StrUtil.isNotBlank(ExcelUtil.getCellValue(sheetRow.getCell(priorityPosition)));
        if (hasPriorityValue) {
            return ExcelUtil.getCellValue(sheetRow.getCell(priorityPosition));
        }
        return basePosition != null && sheetRow.getCell(basePosition) != null ? ExcelUtil.getCellValue(sheetRow.getCell(basePosition)) : null;
    }

    /**
     * 去掉逗号
     *
     * @param numberData 数字型字符串
     * @return 无逗号的数字型字符串
     */
    public static String numberDataStripCommas(String numberData) {
        if (StrUtil.isBlank(numberData)) {
            return null;
        }
        // pdf解析到的值带有",",比如:"10,656,097.37"
        String data = numberData.replaceAll(",", "");
        if (!StringUtil.isNumeric(data)) {
            return null;
        }
        return data;
    }

    public static HSSFWorkbook getHSSFWorkbook(String sheetName, List<String> title, Map<String, List<List<String>>> valueMap, HSSFWorkbook wb) {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        try {
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.LEFT);
            style.setWrapText(true);
            sheet.setColumnWidth(0, 10000);
            sheet.setColumnWidth(1, 10000);
            sheet.setColumnWidth(2, 5000);
            sheet.setColumnWidth(3, 8000);
            sheet.setColumnWidth(4, 8000);
            sheet.setColumnWidth(5, 5000);
            //声明列对象
            HSSFCell cell = null;
            //创建标题
            for (int i = 0; i < title.size(); i++) {
                cell = row.createCell(i);
                cell.setCellValue(title.get(i));
                cell.setCellStyle(style);
            }
            List<List<String>> values = valueMap.get(sheetName);
            //创建内容
            for (int i = 0; i < values.size(); i++) {
                row = sheet.createRow(i + 1);
                for (int j = 0; j < values.get(i).size(); j++) {
                    //将内容按顺序赋给对应的列对象
                    row.createCell(j).setCellValue(values.get(i).get(j));
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return wb;
    }

}