PAS/src/main/java/cn/cslg/pas/common/utils/ReadExcelUtils.java

package cn.cslg.pas.common.utils;

import cn.cslg.pas.common.utils.commonUtils.BusinessUtil;
import cn.cslg.pas.common.vo.PatentData;
import cn.cslg.pas.domain.es.Patent;
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.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

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

        // 检测是否为excel文件
        String suffix = tempFile.getPath().substring(tempFile.getPath().lastIndexOf("."));
        if (!suffix.equals(".xls") && !suffix.equals(".xlsx") && !suffix.equals(".XLS") && !suffix.equals(".XLSX")) {
            //删除临时文件tempFile
            new File(tempFile.getPath()).delete();
            ThrowException.throwXiaoShiException("文件格式错误，请上传Excel文件！");
        }

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

        //获取第一行抬头
        Row firstRow = sheet.getRow(0);
        boolean flag1 = false;  //是否有 "公开(公告)号"
        boolean flag2 = false;  //是否有 "申请号"
        //遍历第一行单元格抬头，检查合法性
        String title = "", source = "";
        if (sourceId.equals("1")) {
            source = "智慧芽";
            title = "公开(公告)号";
        } else if (sourceId.equals("2")) {
            source = "合享";
            title = "公开（公告）号";
        } else {
            source = "Patentics";
            title = "公开号";
        }
        for (Cell cell : firstRow) {
            if (cell.getStringCellValue().equals(title)) {
                flag1 = true;
            }
            if (cell.getStringCellValue().equals("申请号")) {
                flag2 = true;
            }
        }
        if (!flag1 || !flag2) {
            //删除临时文件tempFile
            fis.close();
            new File(tempFile.getPath()).delete();
            ThrowException.throwXiaoShiException("文件内容格式不正确，您选择【" + source + "】来源，Excel第一行抬头必须有【" + title + "】和【申请号】");
        }
        //关闭流
        fis.close();
        //返回文件总行数-1（即专利总数量）
        return rows - 1;
    }

    /**
     * 获取一行专利的全部数据（专利内容数据 + 摘要附图）
     *
     * @param tempFile Excel临时文件
     * @param row      行数
     * @return 返回装载专利数据（专利内容数据 + 摘要附图）的对象
     */
    public static PatentData readExcelOneRow(File tempFile, Sheet sheet, int row) throws IOException {
        //创建返回最终结果的对象 patentData
        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) + "", ExcelUtils.getValue(needRow.getCell(i)) + "");
        }

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

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

        return patentData;
    }

    public static Sheet readExcel(File tempFile) {
        Sheet sheet = null;

        try {
            InputStream inputStream = new FileInputStream(tempFile);
            //POI可以处理Excel文件
            Workbook workbook = null;
            //当文件以.xls结尾时
            String suffix = tempFile.getName().substring(tempFile.getName().lastIndexOf("."));
            if (suffix.equals(".xls") || suffix.equals(".XLS")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (suffix.equals(".xlsx") || suffix.equals(".XLSX")) {
                workbook = new XSSFWorkbook(inputStream);
            }

            //读取第几个sheet
            sheet = workbook.getSheetAt(0);

            //关闭流
            inputStream.close();

        } 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;
    }

    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 "";
    }


    /**
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String,List<String>>  getPatentNoFromExcel(Sheet sheet) throws IOException {
        Map<String,List<String>> map =new HashMap<>();

        int rows = sheet.getPhysicalNumberOfRows();
        Integer PNOCellId = null;
        Integer ANOCellId = null;
        List<String> publicNos = new ArrayList<>();
        List<String> appNos = new ArrayList<>();
        //开始装载专利数据
        Row firstRow = sheet.getRow(0);


        //查找列
        int columns = firstRow.getLastCellNum();
        for (int t = 0; t < columns; t++) {
            if (firstRow.getCell(t).toString().trim().equals("申请号")) {
                ANOCellId = t;
            }
            if (firstRow.getCell(t).toString().trim().equals("公开(公告)号")) {
                PNOCellId = t;
            }
        }
        for (int i = 1; i < rows; i++) {
            Row row = sheet.getRow(i);
            if (ANOCellId != null) {
                if(row.getCell(ANOCellId)!=null) {
                    String appNo = row.getCell(ANOCellId).toString();
                    appNos.add(appNo);
                }
            }
            if(PNOCellId!=null){
                if(row.getCell(PNOCellId)!=null) {
                    String publicNo = row.getCell(PNOCellId).toString();
                    publicNos.add(publicNo);
                }
            }

        }
        map.put(BusinessUtil.APP_NO,appNos);
        map.put(BusinessUtil.PUBLIC_NO,publicNos);
        return map;
    }


    public static Integer testNoImport(Sheet sheet) throws IOException {
        int rows = sheet.getPhysicalNumberOfRows();
        Integer cellId = null;
        //开始装载专利数据
        Row firstRow = sheet.getRow(0);

        //查找列
        int columns = firstRow.getLastCellNum();
        for (int t = 0; t < columns; t++) {
            if (firstRow.getCell(t).toString().equals("专利号")) {
                cellId = t;
            }
        }

        if (cellId != null && rows == 1) {
            cellId = -1;
        }
        return cellId;
    }


}