IDS/src/main/java/com/cslg/ids/common/utils/ExcelUtils.java

package com.cslg.ids.common.utils;

import org.apache.poi.hssf.usermodel.*;
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;

import java.awt.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {

    public static Map<String, PictureData> getDataFromExcel(String filePath) throws IOException {
        //判断是否为excel类型文件
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx") && !filePath.endsWith(".XLS") && !filePath.endsWith(".XLSX")) {
            System.out.println("文件不是excel类型");
        }

        FileInputStream fis = null;
        Workbook wookbook = null;
        Sheet sheet = null;
        try {
            //获取一个绝对地址的流
            fis = new FileInputStream(filePath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            //2003版本的excel，用.xls结尾
            wookbook = new HSSFWorkbook(fis);//得到工作簿
        } catch (Exception ex) {
            //ex.printStackTrace();
            try {
                //2007版本的excel，用.xlsx结尾
                fis = new FileInputStream(filePath);
                wookbook = new XSSFWorkbook(fis);//得到工作簿
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        Map<String, PictureData> maplist = null;
        //拿到excel表格的第一个sheet工作簿
        sheet = wookbook.getSheetAt(0);
        // 判断用07还是03的方法获取图片
        if (filePath.endsWith(".xls") || filePath.endsWith(".XLS")) {
            maplist = getPictures1((HSSFSheet) sheet);
        } else if (filePath.endsWith(".xlsx") || filePath.endsWith(".XLSX")) {
            maplist = getPictures2((XSSFSheet) sheet);
        }
        wookbook.close();
        return maplist;
    }

    /**
     * 获取图片和位置 (xls)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPictures1(HSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap();
        if (sheet.getDrawingPatriarch() != null) {
            List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
            for (HSSFShape shape : list) {
                if (shape instanceof HSSFPicture) {
                    HSSFPicture picture = (HSSFPicture) shape;
                    HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                    PictureData pdata = picture.getPictureData();
                    String key = String.valueOf(cAnchor.getRow1());
                    map.put(key, pdata);
                }
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPictures2(XSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap();
        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();
                    //解决图片空指针报错问题 lig  2021-06-03
                    XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                    //XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = String.valueOf(marker.getRow());
                    map.put(key, picture.getPictureData());
                }
            }
        }
        return map;
    }

//    /**
//     * @param pictureData 图片
//     * @return 返回图片的文件路径和文件名称
//     */
//    public static Map<String, String> savePicture(PictureData pictureData) throws IOException {
//        FileUtils fileUtils = SpringUtils.getBean(FileUtils.class);
//        Map<String, String> result = new HashMap<>();
//        String ext = pictureData.suggestFileExtension();
//        byte[] data = pictureData.getData();
//        String picName = IdUtil.simpleUUID() + "." + ext;
//        String date = DateUtils.getNowTimeFormat("yyyyMMdd");
//        String folderPath = fileUtils.getSavePath(date);
//        String filePath = FileUtils.FILE_SEPARATOR + date + FileUtils.FILE_SEPARATOR + picName;
//        File directory = new File(folderPath);
//        if (!directory.exists()) {
//            directory.mkdir();
//        }
//        FileOutputStream out = new FileOutputStream(folderPath + picName);
//        out.write(data);
//        out.close();
//        result.put("path", filePath);
//        result.put("name", picName);
//        return result;
//    }

    /**
     * @param
     * @param x           单元格x轴坐标
     * @param y           单元格y轴坐标
     * @param pictureData 图片二进制数据
     * @param picType     图片格式
     */
    public static void writePicture(Sheet sheet, int x, int y, byte[] pictureData, int picType) {
        Drawing drawingPatriarch = sheet.createDrawingPatriarch();
        //设置图片单元格位置
        ClientAnchor anchor = drawingPatriarch.createAnchor(0, 0, 0, 0, x, y, x + 1, y + 1);
        //随单元格改变位置和大小
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        //添加图片
        int pictureIndex = sheet.getWorkbook().addPicture(pictureData, picType);
        drawingPatriarch.createPicture(anchor, pictureIndex);
    }

    public static void setExcelCellStyle(HSSFCellStyle cellStyle) {
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
    }

    public static String getValue(Cell cell) {
        if (cell != null) {
            if (cell.getCellType() == CellType.NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                String dateString = dateFormat.format(date);
                return dateString;
            } else {
                return cell.toString();
            }
        }
        return "";
    }


}