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

╰+哭是因爲堅強的太久メ 2024-02-05 19:39 56阅读 0赞

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

#### 目录标题 ####

*  1.个人封装
 *   *  1.1 验证excel是否全部为空
     *  1.2 从单元格中获取指定类型的值
     *  1.3 设置某列单元格可选下拉框
     *  1.4 导出单元格图片
 *  2. 工具类
 *   *  2.1 图片解析工具类

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