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.PictureData; import org.apache.poi.ss.usermodel.Row; 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.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("")) { return -1; // ThrowException.throwXiaoShiException("文件上传失败,服务器忙请稍后再试!"); } File file = new File(filePath); if (!file.exists()) { return -1; // ThrowException.throwXiaoShiException("文件上传失败,服务器忙请稍后再试!"); } // 检测是否为excel文件 if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx") && !filePath.endsWith(".XLS") && !filePath.endsWith(".XLSX")) { return -1; // 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 <= 0) { return -2; // ThrowException.throwXiaoShiException("文件内容格式不正确!"); } Row firstRow = sheet.getRow(0); if (!firstRow.getCell(0).getStringCellValue().equals("公开(公告)号")) { return -2; // ThrowException.throwXiaoShiException("文件内容格式不正确!"); } //返回文件总行数-1(即专利总数量) return rows - 1; } /** * 获取一行专利的全部数据(专利内容数据 + 摘要附图) * * @param filePath Excel文件路径 * @param row 行数 * @return 返回装载专利数据(专利内容数据 + 摘要附图)的对象 */ public static PatentData readExcelOneRow(String filePath, Integer row) { //返回最终结果的对象 PatentData patentData = new PatentData(); //装载专利数据(除了摘要附图)的map:(key:表头如 "公开(公告)号" value:表头对应内容如 "CN1307082B") Map map = new HashMap<>(); //装载摘要附图的对象 PictureData pictureData = 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 sheet = workbook.getSheetAt(0); //读取总行数 int rows = sheet.getPhysicalNumberOfRows(); if (rows <= row) { ThrowException.throwXiaoShiException("row超出Excel文档中数量"); } //开始装载专利数据 Row firstRow = sheet.getRow(0); Row needRow = sheet.getRow(row); //读取第一行的时候会多读一列 int firstColumns = firstRow.getLastCellNum() - 0; for (int i = 0; i < firstColumns; 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); } workbook.close(); //返回结果对象装载结果 patentData.setMap(map); patentData.setPictureData(pictureData); } catch (IOException e) { e.printStackTrace(); } return patentData; } /** * 03版本Excel取附图 * * @param sheet Excel工作簿 * @return 返回附图map */ public static PictureData getPictures1(HSSFSheet sheet, Integer row) throws IOException { if (sheet.getDrawingPatriarch() != null) { List 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 list = sheet.getRelations(); for (POIXMLDocumentPart part : list) { if (part instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) part; List 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; } }