2021-08-01数据导出到Excel表格

数据导出到Excel表格

https://www.cnblogs.com/zhangzhiyong-/p/13376527.html

package com.jxmcloud.business.shop.util;

import com.jxmcloud.business.shop.util.pay.PayUtil;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.BiConsumer;

/**
 * 解析excel中图片和文本数据工具类
 *
 * @author hujun
 **/
@Component
public class ExcelUtilWithPic {
    private final Logger logger = LoggerFactory.getLogger(PayUtil.class);
    @Autowired
    private SaveFileUtil fileRemoteClient;

    /**
     * 解析excel中图片和文本数据
     * Map<String, String>中key格式 1-1 获取这个excel坐标的图片地址
     * Sheet对象,用来获取普通文字数据
     * @Param [file, biConsumer]
     * @author hujun
     **/
    public String handleDataFromExcel(MultipartFile file, BiConsumer<Map<String, List<String>>, Sheet> biConsumer) {
        Workbook workbook = null;
        try {
            String fileName = file.getOriginalFilename();
            if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                return "上传文件格式不正确";
            }
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }
            InputStream is = file.getInputStream();

            if (isExcel2003) {
                workbook = new HSSFWorkbook(is);
            } else {
                workbook = new XSSFWorkbook(is);
            }
//            Map<String, String> maplist = null;
            Map<String, List<String>> maplist =null;
            Sheet sheet = workbook.getSheetAt(0);
            // 解析图片,放入map
            if (isExcel2003) {
//                maplist = getPicturesXls((HSSFSheet) sheet);
                maplist = getPicturesXlsMulit((HSSFSheet) sheet);
            } else if (fileName.endsWith(".xlsx")) {
//                maplist = getPicturesXlsx((XSSFSheet) sheet);
                maplist = getPicturesXlsxMulit((XSSFSheet) sheet);
            }
            //解析文字数据
            biConsumer.accept(maplist, sheet);
            workbook.close();
        } catch (IOException e) {
            logger.error("解析带图片excel失败", e);
            return "解析带图片excel失败";
        }
        return "操作成功!";
    }

    /**
     * 获取图片和位置 (xls)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public Map<String, String> getPicturesXls(HSSFSheet sheet) throws IOException {
        Map<String, String> map = new HashMap<String, String>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                // 行号-列号
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
                map.put(key, byteToMultipartFile(picture.getPictureData().getData()));
        }
        }
        return map;
    }
    /**
     * 获取图片和位置 (xls)
     * 一个单元格中有多个图片
     * @param sheet
     * @return
     * @throws IOException
     */
    public Map<String, List<String>> getPicturesXlsMulit(HSSFSheet sheet) throws IOException {
        Map<String, List<String>> map = new HashMap<String, List<String>>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                // 行号-列号
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
                String img=byteToMultipartFile(picture.getPictureData().getData());
                List<String> list1;
                List<String> list2=map.get(key);
                if(list2==null){
                    list1=new ArrayList<>();
                    list1.add(img);
                    map.put(key, list1);
                }else{
                    list2.add(img);
                    map.put(key, list2);
                }

            }
        }
        return map;
    }
    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public Map<String, String> getPicturesXlsx(XSSFSheet sheet) throws IOException {
        Map<String, String> map = new HashMap<String, String>();
        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();
                    String key = marker.getRow() + "-" + marker.getCol();
                    map.put(key, byteToMultipartFile(picture.getPictureData().getData()));
                }
            }
        }
        return map;
    }
    /**
     * 获取图片和位置 (xlsx)
     * 获取一个单元格中的多个图片
     * @param sheet
     * @return
     * @throws IOException
     */
     public Map<String, List<String>> getPicturesXlsxMulit(XSSFSheet sheet) throws IOException {
        Map<String, List<String>> map = new HashMap<String, List<String>>();
        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();
                    String key = marker.getRow() + "-" + marker.getCol();
                    String img=byteToMultipartFile(picture.getPictureData().getData());
                    List<String> list1;
                    List<String> list2=map.get(key);
                    if(list2==null){
                        list1=new ArrayList<>();
                        list1.add(img);
                        map.put(key, list1);
                    }else{
                        list2.add(img);
                        map.put(key, list2);
                    }
                }
            }
        }
        return map;
    }




    /**
     * 通过byte[]获取fastdfs中地址
     * @Param [bytes]
     * @author hujun
     **/
    public String byteToMultipartFile(byte[] bytes) {
        MultipartFile multipartFile = new MockMultipartFile("file", "test.jpg", "text/plain", bytes);
        return fileRemoteClient.saveFileOSS(multipartFile);
    }

}