excel 图片数据导入操作工具类

import java.io.*;
import java.text.DecimalFormat;
import java.util.*;


import com.google.gson.GsonBuilder;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;


public class GoodsExcelUtil {


    public static void main(String[] args) throws IOException {
        String filePath = "E:\\newDesk\\a\\test.xlsx";
        FileInputStream fileInputStream = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);


        Map<String, List<PictureData>> pictures = getPictures(sheet);
//        Map<String, List<String>> imagesMap = mapImageToUrl(pictures);


        List<Map<String, Object>> maps = readDataV2(sheet, pictures);

        for (Map<String, Object> map : maps) {
            System.out.println("   ");
            Set<Map.Entry<String, Object>> entries = map.entrySet();
            Iterator<Map.Entry<String, Object>> iterator = entries.iterator();
            while (iterator.hasNext()) {
                Map.Entry<String, Object> next = iterator.next();
                if (next.getValue() instanceof PictureData) {
                    PictureData pd = (PictureData) next.getValue();
                    System.out.println(next.getKey() + "\n" + "图片");
                }else {
                    System.out.print(next.getKey() + "\n" + next.getValue() + "\n");

                }

            }
            System.out.println("   ");


        }

    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, List<PictureData>> getPictures(XSSFSheet sheet) throws IOException {
        Map<String, List<PictureData>> map = new HashMap<String, List<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;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    if (marker.getCol() == 0) {
//                        String key = marker.getRow() + "-" + marker.getCol();
                        String key = marker.getRow() + "";// 此处用第几行的下标作为图片的Key
                        List<PictureData> pictureDataList = map.get(key);
                        XSSFPictureData itemPicData = picture.getPictureData();
                        if (pictureDataList == null) {
                            pictureDataList = new ArrayList<>();
                            map.put(key, pictureDataList);
                            pictureDataList.add(itemPicData);
                        }else {
                            pictureDataList.add(itemPicData);
                        }
                    }
                }
            }
        }
        return map;
    }

    public static Map<String, List<String>> mapImageToUrl(Map<String, List<PictureData>> imageMap) {
        Map<String, List<String>> imageUrlMap = new HashMap<>();
        Set<Map.Entry<String, List<PictureData>>> entries = imageMap.entrySet();
        Iterator<Map.Entry<String, List<PictureData>>> iterator = entries.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, List<PictureData>> next = iterator.next();
            String key = next.getKey();
            List<PictureData> pictureDataList = next.getValue();
            try {
                for (PictureData pictureData : pictureDataList) {
                    List<String> imageUrlList = imageUrlMap.get(key);
                    String imageUrl = saveImageToFile(pictureData.getData(), "E:\\newDesk\\a\\test\\");
                    if (imageUrlList != null) {
                        imageUrlList.add(imageUrl);
                    }else {
                        imageUrlList = new ArrayList<>();
                        imageUrlMap.put(key, imageUrlList);
                        imageUrlList.add(imageUrl);
                    }
                }
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }

        return imageUrlMap;
    }

    private static String saveImageToFile(byte[] imageBytes, String filePath) throws IOException {
        String imageUrl = null;
        if (imageBytes != null) {
            imageUrl = filePath + UUID.randomUUID().toString() + ".jpg";
            try (FileOutputStream fileOutputStream = new FileOutputStream(imageUrl)) {
                fileOutputStream.write(imageBytes);
                fileOutputStream.flush();
                fileOutputStream.close();
                return imageUrl;
            }
        }
        return null;
    }

    /**
     * 读取excel文字
     * <p>
     * Excel 07版本以上
     *
     * @param sheet
     */
    public static List<Map<String, Object>> readDataV2(XSSFSheet sheet, Map<String, List<PictureData>> imageMaps) {

        List<Map<String, Object>> newList = new ArrayList<Map<String, Object>>();// 单行数据
        try {
            int rowNum = sheet.getLastRowNum() + 1;
            for (int i = 1; i < rowNum; i++) {//第二行开始读取
                Row row = sheet.getRow(i);// 得到Excel工作表的行
                if (row != null) {
                    int col = row.getLastCellNum();
                    // 单行数据
                    Map<String, Object> mapRes = new HashMap<String, Object>();// 每格数据
                    for (int j = 0; j < col; j++) {
                        Cell cell = row.getCell(j);


                        // 处理图片数据
                        if (j == 0) {
                            List<PictureData> imageUrlList = imageMaps.get((i - 1) + "");
                            if (imageUrlList != null){
                                mapRes.put(getMapKeyV2(j), imageUrlList);
                            }
                        }

                        // 处理常规数据
                        if (cell == null || cell.getCellType() == CellType.BLANK) {
                        } else if (cell.getCellType() == CellType.NUMERIC) {// 当时数字时的处理
                            //当值为数字式处理一下 字符串带小数点问题
                            String mapKeyV2 = getMapKeyV2(j);
                            if (StringUtils.equals(mapKeyV2,"code") || StringUtils.equals(mapKeyV2,"numbers") || StringUtils.equals(mapKeyV2,"rootId")  ){
//                              由于某些字段是字符串类型，但填的确是数值会被自动带上小数点，所以此处需移除小数点
                                DecimalFormat df = new DecimalFormat("0");
                                String value = df.format(cell.getNumericCellValue());
                                mapRes.put(mapKeyV2, value);
                            }else {
                                mapRes.put(mapKeyV2, new Double(cell.getNumericCellValue()).toString());
                            }
                        } else {// 如果EXCEL表格中的数据类型为字符串型
                            mapRes.put(getMapKeyV2(j), cell.getStringCellValue().trim());
                        }
                    }
                    mapRes.put("rowIndex", (i + 1) + "");
                    newList.add(mapRes);
                }
            }

        } catch (Exception e) {
        }
        return newList;
    }


    /**
     * 读取excel文字
     * <p>
     * Excel 07版本以上
     *
     * @param sheet
     */
    public static List<Map<String, String>> readData(XSSFSheet sheet, Map<String, List<String>> imageMaps) {

        List<Map<String, String>> newList = new ArrayList<Map<String, String>>();// 单行数据
        try {
            int rowNum = sheet.getLastRowNum();
            for (int i = 1; i < rowNum; i++) {//第二行开始读取
                Row row = sheet.getRow(i);// 得到Excel工作表的行
                if (row != null) {
                    int col = row.getLastCellNum();
                    // 单行数据
                    Map<String, String> mapRes = new HashMap<String, String>();// 每格数据
                    for (int j = 0; j < col; j++) {
                        Cell cell = row.getCell(j);
                        // 处理图片数据
                        if (j == 0) {
                            List<String> imageUrlList = imageMaps.get(i + "-0");
//                            List<String> imageUrlList = imageMaps.get((i + 1) + "");// 取当前行的图片Key集合
                            if (imageUrlList != null){
                                String imagesJson = new GsonBuilder().create().toJson(imageUrlList, List.class);
                                mapRes.put(getMapKeyV2(j), imagesJson);
                            }
                        }

                        // 处理常规数据
                        if (cell == null || cell.getCellType() == CellType.BLANK) {
                        } else if (cell.getCellType() == CellType.NUMERIC) {// 当时数字时的处理
                            mapRes.put(getMapKeyV2(j), new Double(cell.getNumericCellValue()).toString());
                        } else {// 如果EXCEL表格中的数据类型为字符串型
                            mapRes.put(getMapKeyV2(j), cell.getStringCellValue().trim());
                        }
                    }
                    mapRes.put("rowIndex", (i + 1) + "");
                    newList.add(mapRes);
                }
            }

        } catch (Exception e) {
        }
        return newList;
    }

    public static String getMapKeyV2(int num) {
        String res = "";
        switch (num) {
            case 0:
                res = "goodsMediaVOS";// 商品图片 goodsMediaVOS A
                break;
            case 1:
                res = "name";//name 商品主标题 B
                break;
            case 2:
                res = "code";// 商品编码 C
                break;
            case 3:
                res = "price";//price 商品价格 D
                break;
            case 4:
                res = "markingPrice";//marking_price 划线价 E
                break;
            case 5:
                res = "purchasePrice"; // 进货价 F
                break;
            case 6:
                res = "type";// 商品类目 G
                break;
            case 7:
                res = "description"; // 商品描述 H
                break;
            case 8:
                res = "specType";// 规格 I
                break;
            case 9:
                res = "attributes";// 商品属性
                break;
            case 10:
                res = "goodsMediaVOSUrl";// 商品Url
                break;
            case 11:
                res = "details";// 商品详情URL
                break;
            case 12:
                res = "piecesType"; // 是否大件商品
                break;
            case 13:
                res = "numbers";// 大件商品小件单件总数
                break;
            case 14:
                res = "rootId";// 小件商品关联的大件商品编码
                break;
            default:
                break;
        }
        return res;
    }
}