【POI】个人封装的常用的导入导出方法

本文保存一些个人封装的常用的POI导入导出方法，随时调整

1.个人封装

1.1 验证excel是否全部为空

    /**
     * 验证excel是否全部为空
     *
     * @param row      当前行
     * @param firstRow 第一行标题行
     * @return
     */
    public static boolean isAllRowEmpty(Row row, Row firstRow) {
        if (row == null) {
            return true;
        }
        int count = 0;
        //单元格数量
        int rowCount = firstRow.getLastCellNum() - firstRow.getFirstCellNum();
        //判断多少个单元格为空
        for (int c = 0; c < rowCount; c++) {
            Cell cell = row.getCell(c);
            if (cell == null || cell.getCellType() == CellType.BLANK || StrUtil.isEmpty((cell + "").trim())) {
                count += 1;
            }
        }
        if (count == rowCount) {
            return true;
        }
        return false;
    }

1.2 从单元格中获取指定类型的值

    private static Object getCellValue(Cell cell, Class fieldType) {
        Object result = null;
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case _NONE:
                break;
            case BLANK:
                break;
            case STRING:
                if (fieldType.equals(Integer.class)) {
                    DecimalFormat decimalFormat = new DecimalFormat("#");
                    result = Integer.valueOf(decimalFormat.format(cell.getNumericCellValue()));
                } else {
                    result = cell.getStringCellValue().trim();
                }
                break;
            case NUMERIC:
                if (fieldType.equals(LocalDate.class)) {
                    result = cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                } else if (fieldType.equals(LocalDateTime.class)) {
                    result = cell.getLocalDateTimeCellValue();
                } else if (fieldType.equals(String.class)) {
                    DecimalFormat decimalFormat = new DecimalFormat("#");
                    result = decimalFormat.format(cell.getNumericCellValue());
                } else if (fieldType.equals(Integer.class)) {
                    result = Integer.valueOf((int)cell.getNumericCellValue());
                } else if (fieldType.equals(BigDecimal.class)) {
                    result = BigDecimal.valueOf(cell.getNumericCellValue());
                } else {
                    result = cell.getNumericCellValue();
                }
                break;
            case FORMULA:
                break;
            case BOOLEAN:
                break;
            case ERROR:
                break;
        }
        return result;
    }

1.3 设置某列单元格可选下拉框

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框(隐式数据域).
     *
     * @param sheet    要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow   结束行
     * @param firstCol 开始列
     * @param endCol   结束列
     * @return 设置好的sheet.
     */
    public static void setXSSFValidation(Workbook wb,List<Sheet> hiddenSheetList,Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        DataValidationConstraint constraint = null;
        int charNum = Arrays.stream(textlist).mapToInt(String::length).sum();
        if (charNum < 256){
            constraint = helper.createExplicitListConstraint(textlist);
        }else {
            // 下拉列表太多 设置隐藏Sheet存储值
            String hiddenSheetName = UUID.randomUUID().toString().substring(1,16);
            Sheet hiddenSheet = wb.createSheet(hiddenSheetName);

            for (int i = 0; i < textlist.length; i++) {
                Row row = hiddenSheet.createRow(i);
                Cell cell = row.createCell(0);
                String cat = textlist[i];
                cell.setCellValue(cat);
            }

            hiddenSheetList.add(hiddenSheet);
            hiddenSheet.protectSheet("passw0rd");
            wb.setSheetHidden(hiddenSheetList.size(),true);
            String hiddenSheetScope = "'"+hiddenSheetName+"'"+"!$A$1:$A$"+textlist.length;
            constraint = helper.createFormulaListConstraint(hiddenSheetScope);
        }
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }

1.4 导出单元格图片

    // 填充 单元格图片
    public static void setPictureCell(String pictureUrl, Cell cell, CellStyle bodyCellStyle) {
        if (bodyCellStyle != null) {
            cell.setCellStyle(bodyCellStyle);
        }
        if (StrUtil.isEmpty(pictureUrl)) {
            return;
        }
        ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
        // 此处是把网络图片进行解析为byte数组
        byte[] imgData = ImageUtils.getImage(pictureUrl);
        ImageUtils.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                cell.getSheet().getWorkbook().addPicture(imgData, ImageUtils.getImageType(imgData)));
    }

2. 工具类

2.1 图片解析工具类

import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.util.Arrays;

public class ImageUtils {


    private static final Logger log = LoggerFactory.getLogger(ImageUtils.class);

    /**
     * 获取文件类型
     *
     * @param photoByte 文件字节码
     * @return 后缀（不含".")
     */
    public static String getFileExtendName(byte[] photoByte)
    {
        String strFileExtendName = "JPG";
        if ((photoByte[0] == 71) && (photoByte[1] == 73) && (photoByte[2] == 70) && (photoByte[3] == 56)
                && ((photoByte[4] == 55) || (photoByte[4] == 57)) && (photoByte[5] == 97))
        {
            strFileExtendName = "GIF";
        }
        else if ((photoByte[6] == 74) && (photoByte[7] == 70) && (photoByte[8] == 73) && (photoByte[9] == 70))
        {
            strFileExtendName = "JPG";
        }
        else if ((photoByte[0] == 66) && (photoByte[1] == 77))
        {
            strFileExtendName = "BMP";
        }
        else if ((photoByte[1] == 80) && (photoByte[2] == 78) && (photoByte[3] == 71))
        {
            strFileExtendName = "PNG";
        }
        return strFileExtendName;
    }
    /**
     * 获取图片类型,设置图片插入类型
     */
    public static int getImageType(byte[] value)
    {
        String type = ImageUtils.getFileExtendName(value);
        if ("JPG".equalsIgnoreCase(type))
        {
            return Workbook.PICTURE_TYPE_JPEG;
        }
        else if ("PNG".equalsIgnoreCase(type))
        {
            return Workbook.PICTURE_TYPE_PNG;
        }
        return Workbook.PICTURE_TYPE_JPEG;
    }
    /**
     * 获取画布
     */
    public static Drawing<?> getDrawingPatriarch(Sheet sheet)
    {
        if (sheet.getDrawingPatriarch() == null)
        {
            sheet.createDrawingPatriarch();
        }
        return sheet.getDrawingPatriarch();
    }

    public static byte[] getImage(String imagePath)
    {
        InputStream is = getFile(imagePath);
        try
        {
            return IOUtils.toByteArray(is);
        }
        catch (Exception e)
        {
            log.error("图片加载异常 {}", e);
            return null;
        }
        finally
        {
            IOUtils.closeQuietly(is);
        }
    }

    public static InputStream getFile(String imagePath)
    {
        try
        {
            byte[] result = readFile(imagePath);
            result = Arrays.copyOf(result, result.length);
            return new ByteArrayInputStream(result);
        }
        catch (Exception e)
        {
            log.error("获取图片异常 {}", e);
        }
        return null;
    }

    /**
     * 读取文件为字节数据
     *
     * @param url 地址
     * @return 字节数据
     */
    public static byte[] readFile(String url)
    {
        InputStream in = null;
        try
        {
            if (url.startsWith("http"))
            {
                // 网络地址
                URL urlObj = new URL(url);
                URLConnection urlConnection = urlObj.openConnection();
                urlConnection.setConnectTimeout(30 * 1000);
                urlConnection.setReadTimeout(60 * 1000);
                urlConnection.setDoInput(true);
                in = urlConnection.getInputStream();
            }
            return IOUtils.toByteArray(in);
        }
        catch (Exception e)
        {
            log.error("获取文件路径异常 {}", e);
            return null;
        }
        finally
        {
            IOUtils.closeQuietly(in);
        }
    }
}