ExcelUtils工具类（基于POI）

ExcelUtils工具类（POI）

该工具类基于POI4.1.2，
并修改POI 3.9版本正常，但是4.0版本以上报空指针异常的bug，代码中注释已体现

该工具类含有一下功能

1. 读取xlsx
2. 读取sheet中的图片
3. 读取文本，日期，数字等数据格式
4. …（待用的时候再扩充）

<!-- maven中引入excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.12.0</version>
        </dependency>
        

ExcelUtils.class

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import java.awt.*;
import java.io.*;
import java.util.*;
import java.util.List;

/**
 * Excel工具类，扩展 hutool 工具包
 *
 * @author 不存在的昵称
 * @date 2021-10-27
 */
public class ExcelUtils {
  
    /**
     * xlsx
     * @param filePath
     * @return
     * @throws IOException
     */
    public static XSSFWorkbook getXssWorkbook(String filePath) throws IOException {

        XSSFWorkbook workbook = new XSSFWorkbook(filePath);
//        in.close();
        return workbook;
    }

    private static LinkedList splitEduToRecordAndDegree(String spouseEdu){
        return new LinkedList(Arrays.asList(spouseEdu.split("/")));
    }

    /**
     * 判断单元格value类型
     * @author lubaocheng
     * @param cell
     * @return
     */
    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case NUMERIC:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    }else {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case STRING:{
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = cell.toString();
                    break;
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }

    /**
     * 读取Excel
     * @param filePath
     * @author lubaocheng
     * @return
     */
    public static Workbook readExcel(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is);
            }else{
                return wb = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPictures(XSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;

                    Dimension d = picture.getImageDimension();
                    // 解决空指针 poi版本4.1.2
                    XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                    // poi版本3.9正常，升级为4以上，如果用户整行复制粘贴，图片会报空指针
//                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    byte[] data = picture.getPictureData().getData();
                    map.put(key, picture.getPictureData());
                }
            }
        }
        return map;
    }

    /**
     * excel图片上传服务器
     * @param sheetList
     * @param path
     * @return
     * @throws IOException
     */
    public static Map<String, String> uploadImg(Map<String, PictureData> sheetList, String path) throws IOException {
        Map<String, String> pathMap = new HashMap<String, String>();
        Object[] key = sheetList.keySet().toArray();
        File f = new File(path);
        if (!f.exists()) {
            f.mkdirs(); // 创建目录
        }
        for (int i = 0; i < sheetList.size(); i++) {
            // 获取图片流
            PictureData pic = sheetList.get(key[i]);
            // 获取图片索引
            String picName = key[i].toString();
            // 获取图片格式
            String ext = pic.suggestFileExtension();
            String fileName = encodingFilename(picName);
            byte[] data = pic.getData();

            // 图片保存路径
            String imgPath = path + fileName + "." + ext;
            FileOutputStream out = new FileOutputStream(imgPath);
            // 截取图片路径
            pathMap.put(picName, imgPath);
            out.write(data);
            out.close();
        }
        return pathMap;
    }

    private static final String encodingFilename(String fileName) {
        fileName = fileName.replace("_", " ");
        fileName = fileName + "-UUID"+ UUID.randomUUID();
        return fileName;
    }

}