Wispro-CodeWarehouse-BackEnd/PAS/src/main/java/cn/cslg/pas/common/utils/ReadExcelUtils.java

package cn.cslg.pas.common.utils;

import cn.cslg.pas.domain.PatentData;
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 org.springframework.stereotype.Service;

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

/**
 * @Author xiexiang
 * @Date 2023/5/30
 */
@Service
public class ReadExcelUtils {
    /**
     * 检测Excel文件合法性
     *
     * @param filePath 文件路径
     * @return 返回文件总行数
     */
    public static Integer textExcel(String filePath) throws IOException {
        //判断文件是否存在
        if (filePath == null || filePath.equals("")) {
            ThrowException.throwXiaoShiException("文件上传失败，服务器忙请稍后再试！");
        }
        File file = new File(filePath);
        if (!file.exists()) {
            ThrowException.throwXiaoShiException("文件上传失败，服务器忙请稍后再试！");
        }

        // 检测是否为excel文件
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx") && !filePath.endsWith(".XLS") && !filePath.endsWith(".XLSX")) {
            ThrowException.throwXiaoShiException("文件格式错误，请上传Excel文件！");
        }

        InputStream fis = new FileInputStream(file);
        //使用poi框架解析处理Excel文件
        Workbook workbook = null;
        //区分不同版本Excel，使用各自对应的工具类
        if (filePath.endsWith(".xls") || filePath.endsWith(".XLS")) {
            workbook = new HSSFWorkbook(fis);
        } else if (filePath.endsWith(".xlsx") || filePath.endsWith(".XLSX")) {
            workbook = new XSSFWorkbook(fis);
        }
        //读取第几个sheet
        Sheet sheet = workbook.getSheetAt(0);
        //读取总行数
        int rows = sheet.getPhysicalNumberOfRows();
        if (rows <= 1) {
            ThrowException.throwXiaoShiException("文件内容格式不正确，请检查总行数是否有专利内容");
        }

        //获取第一行抬头
        Row firstRow = sheet.getRow(0);
        boolean flag1 = false;  //是否有 "公开(公告)号"
        boolean flag2 = false;  //是否有 "申请号"
        //遍历第一行单元格抬头
        for (Cell cell : firstRow) {
            if (cell.getStringCellValue().equals("公开(公告)号")) {
                flag1 = true;
            }
            if (cell.getStringCellValue().equals("申请号")) {
                flag2 = true;
            }
        }
        if (!flag1 || !flag2) {
            ThrowException.throwXiaoShiException("文件内容格式不正确，第一行抬头必须有【公开(公告)号】和【申请号】");
        }

        //返回文件总行数-1（即专利总数量）
        return rows - 1;
    }

    /**
     * 获取一行专利的全部数据（专利内容数据 + 摘要附图）
     *
     * @param filePath Excel文件路径
     * @param row      行数
     * @return 返回装载专利数据（专利内容数据 + 摘要附图）的对象
     */
    public static PatentData readExcelOneRow(String filePath, Sheet sheet, int row) throws IOException {
        //返回最终结果的对象
        PatentData patentData = new PatentData();
        //装载专利数据（除了摘要附图）的map：（key：表头如 "公开（公告）号"  value：表头对应内容如 "CN1307082B"）
        Map<Object, Object> map = new HashMap<>();
        //装载摘要附图的对象
        PictureData pictureData = null;

        //开始装载专利数据
        Row firstRow = sheet.getRow(0);
        Row needRow = sheet.getRow(row);
        //获得总列数
        int columns = firstRow.getLastCellNum();
        for (int i = 0; i < columns; i++) {
            map.put(firstRow.getCell(i) + "", needRow.getCell(i) + "");
        }

        //开始装载专利摘要附图（判断用07还是03的方法获取图片）
        if (filePath.endsWith(".xls") || filePath.endsWith(".XLS")) {
            pictureData = getPictures1((HSSFSheet) sheet, row);
        } else if (filePath.endsWith(".xlsx") || filePath.endsWith(".XLSX")) {
            pictureData = getPictures2((XSSFSheet) sheet, row);
        }

        //返回结果对象装载结果
        patentData.setMap(map);
        patentData.setPictureData(pictureData);

        return patentData;
    }

    public static Sheet readExcel(String filePath) {
        Sheet sheet = null;

        File file = new File(filePath);
        try {
            InputStream inputStream = new FileInputStream(file);
            //POI可以处理Excel文件
            Workbook workbook = null;
            //当文件以.xls结尾时
            if (filePath.endsWith(".xls") || filePath.endsWith(".XLS")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (filePath.endsWith(".xlsx") || filePath.endsWith(".XLSX")) {
                workbook = new XSSFWorkbook(inputStream);
            }

            //读取第几个sheet
            sheet = workbook.getSheetAt(0);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return sheet;
    }

    /**
     * 03版本Excel取附图
     *
     * @param sheet Excel工作簿
     * @return 返回附图map
     */
    public static PictureData getPictures1(HSSFSheet sheet, Integer row) throws IOException {
        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();
                    int row1 = cAnchor.getRow1();
                    if (row1 == row) {
                        return picture.getPictureData();
                    }
                }
            }
        }
        return null;
    }

    /**
     * 07版本Excel取附图
     *
     * @param sheet Excel工作簿
     * @return 返回附图map
     */
    public static PictureData getPictures2(XSSFSheet sheet, Integer row) throws IOException {
        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) {
                    //解决图片空指针报错问题 lig  2021-06-03
                    XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                    //XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    int excelRow = marker.getRow();
                    if (excelRow == row) {
                        XSSFPicture picture = (XSSFPicture) shape;
                        return picture.getPictureData();
                    }
                }
            }
        }

        return null;
    }

}