/** * 获取XLSX工作簿指定sheet中图片列表 * * @param workbook 工作簿{@link Workbook} * @param sheetIndex sheet的索引 * @return 图片映射,键格式:行_列,值:{@link PictureData} */ private static Map<String, PictureData> getPicMapXlsx(XSSFWorkbook workbook, int sheetIndex) { final Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(); final XSSFSheet sheet = workbook.getSheetAt(sheetIndex); XSSFDrawing drawing; for (POIXMLDocumentPart dr : sheet.getRelations()) { if (dr instanceof XSSFDrawing) { drawing = (XSSFDrawing) dr; final List<XSSFShape> shapes = drawing.getShapes(); XSSFPicture pic; CTMarker ctMarker; for (XSSFShape shape : shapes) { pic = (XSSFPicture) shape; ctMarker = pic.getPreferredSize().getFrom(); sheetIndexPicMap.put(StrUtil.format("{}_{}", ctMarker.getRow(), ctMarker.getCol()), pic.getPictureData()); } } } return sheetIndexPicMap; } // -------------------------------------------------------------------------------------------------------------- Private method end
/** * @return the list of Single Xml Cells that provide a map rule to this mapping. */ public List<XSSFSingleXmlCell> getRelatedSingleXMLCell() { List<XSSFSingleXmlCell> relatedSimpleXmlCells = new ArrayList<>(); int sheetNumber = mapInfo.getWorkbook().getNumberOfSheets(); for (int i = 0; i < sheetNumber; i++) { XSSFSheet sheet = mapInfo.getWorkbook().getSheetAt(i); for (POIXMLDocumentPart p : sheet.getRelations()) { if (p instanceof SingleXmlCells) { SingleXmlCells singleXMLCells = (SingleXmlCells) p; for (XSSFSingleXmlCell cell : singleXMLCells.getAllSimpleXmlCell()) { if (cell.getMapId() == ctMap.getID()) { relatedSimpleXmlCells.add(cell); } } } } } return relatedSimpleXmlCells; }
/** * 获取XLSX工作簿指定sheet中图片列表 * * @param workbook 工作簿{@link Workbook} * @param sheetIndex sheet的索引 * @return 图片映射,键格式:行_列,值:{@link PictureData} */ private static Map<String, PictureData> getPicMapXlsx(XSSFWorkbook workbook, int sheetIndex) { final Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(); final XSSFSheet sheet = workbook.getSheetAt(sheetIndex); XSSFDrawing drawing; for (POIXMLDocumentPart dr : sheet.getRelations()) { if (dr instanceof XSSFDrawing) { drawing = (XSSFDrawing) dr; final List<XSSFShape> shapes = drawing.getShapes(); XSSFPicture pic; CTMarker ctMarker; for (XSSFShape shape : shapes) { pic = (XSSFPicture) shape; ctMarker = pic.getPreferredSize().getFrom(); sheetIndexPicMap.put(StrUtil.format("{}_{}", ctMarker.getRow(), ctMarker.getCol()), pic.getPictureData()); } } } return sheetIndexPicMap; } // -------------------------------------------------------------------------------------------------------------- Private method end
List<POIXMLDocumentPart> rels = _wb.getSheetAt(sheetIndex).getRelations(); for (POIXMLDocumentPart r : rels) { if (r instanceof XSSFDrawing) {
private String tableParts(XLXContext context) { StringBuilder tableParts = new StringBuilder(); if(!context.getTables().isEmpty()){ tableParts.append(String.format("<tableParts count=\"%d\">" ,context.getTables().size())); for (POIXMLDocumentPart next : getSheet().getRelations()) { tableParts.append( table(next) ); } tableParts.append("</tableParts>"); } return tableParts.toString(); }
/** * 获取指定工作表里的所有图片数据,只能处理xlsx文档 * @param sheet 工作表 * @return 工作表里所有图片Picture对象组成的列表 */ public List<Picture> getXlsxPictures(Sheet sheet) { XSSFSheet xssfSheet = (XSSFSheet) sheet; List<POIXMLDocumentPart> documentPartList = xssfSheet.getRelations(); List<Picture> pictureList = new ArrayList<>(); for (POIXMLDocumentPart documentPart : documentPartList) { if (documentPart instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) documentPart; List<XSSFShape> shapeList = drawing.getShapes(); for (XSSFShape shape : shapeList) { if (shape instanceof XSSFPicture) { Picture picture = (XSSFPicture) shape; pictureList.add(picture); } } } } return pictureList; }
/** * Gets all pictures from the Workbook. * * @return the list of pictures (a list of {@link XSSFPictureData} objects.) * @see #addPicture(byte[], int) */ public List<XSSFPictureData> getAllPictures() { if(pictures == null) { //In OOXML pictures are referred to in sheets, //dive into sheet's relations, select drawings and their images pictures = new ArrayList<XSSFPictureData>(); for(XSSFSheet sh : sheets){ for(POIXMLDocumentPart dr : sh.getRelations()){ if(dr instanceof XSSFDrawing){ for(POIXMLDocumentPart img : dr.getRelations()){ if(img instanceof XSSFPictureData){ pictures.add((XSSFPictureData)img); } } } } } } return pictures; }
public static List<Picture> getSheetPictures(XSSFSheet sheet) throws Exception { ArrayList<Picture> pictures = new ArrayList<Picture>(); for (POIXMLDocumentPart relation : sheet.getRelations()) { if (relation instanceof XSSFDrawing) { for (POIXMLDocumentPart img : relation.getRelations()) { if (img instanceof XSSFPictureData) { pictures.add(parsePicture((XSSFPictureData) img)); } } } } return pictures; } }
private void collectTables() { for (POIXMLDocumentPart p : sheet.getRelations()) { if (p.getPackageRelationship().getRelationshipType().equals(XSSFRelation.TABLE.getRelation())) { Table table = (Table) p; tables.add(table); } } }
private void collectPivots() { for (POIXMLDocumentPart p : sheet.getRelations()) { if (p.getPackageRelationship().getRelationshipType().equals(XLSXFactory.PIVOT.getRelation())) { PivotTable table = (PivotTable) p; pivotTables.add(table); } } }
private void savePivots(XSSFSheet sheet, XLXContext context, List<String> savedParts) throws Exception { for (POIXMLDocumentPart p : sheet.getRelations()) { if (p.getPackageRelationship().getRelationshipType().equals(XLSXFactory.PIVOT.getRelation())) { savePivotTable(context, savedParts, p); } } }
private POIXMLDocumentPart findLegacyDrawingPart(XSSFSheet sheet) { CTLegacyDrawing legacyDrawingHF = sheet.getCTWorksheet().getLegacyDrawingHF(); if (legacyDrawingHF != null) { String id = legacyDrawingHF.getId(); for (POIXMLDocumentPart relation : sheet.getRelations()) { if (relation instanceof XSSFVMLDrawing && relation.getPackageRelationship().getId().equals(id)) { return relation; } } } return null; }
/** * 获取Excel2007图片 * * @param sheet * 当前sheet对象 * @param workbook * 工作簿对象 * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData */ public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet, XSSFWorkbook workbook) { Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(); for (POIXMLDocumentPart dr : sheet.getRelations()) { if (dr instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) dr; List<XSSFShape> shapes = drawing.getShapes(); for (XSSFShape shape : shapes) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor anchor = pic.getPreferredSize(); CTMarker ctMarker = anchor.getFrom(); String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol(); sheetIndexPicMap.put(picIndex, pic.getPictureData()); } } } return sheetIndexPicMap; }
/** * 获取Excel2007图片 * * @param sheet * 当前sheet对象 * @param workbook * 工作簿对象 * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData */ public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet, XSSFWorkbook workbook) { Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(); for (POIXMLDocumentPart dr : sheet.getRelations()) { if (dr instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) dr; List<XSSFShape> shapes = drawing.getShapes(); for (XSSFShape shape : shapes) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor anchor = pic.getPreferredSize(); CTMarker ctMarker = anchor.getFrom(); String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol(); sheetIndexPicMap.put(picIndex, pic.getPictureData()); } } } return sheetIndexPicMap; }
/** * 获取Excel2007图片 * * @param sheet * 当前sheet对象 * @param workbook * 工作簿对象 * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData */ public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet, XSSFWorkbook workbook) { Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(); for (POIXMLDocumentPart dr : sheet.getRelations()) { if (dr instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) dr; List<XSSFShape> shapes = drawing.getShapes(); for (XSSFShape shape : shapes) { if (shape instanceof XSSFPicture) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor anchor = pic.getPreferredSize(); CTMarker ctMarker = anchor.getFrom(); String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol(); sheetIndexPicMap.put(picIndex, pic.getPictureData()); } } } } return sheetIndexPicMap; }
/** * @return the list of Single Xml Cells that provide a map rule to this mapping. */ public List<XSSFSingleXmlCell> getRelatedSingleXMLCell() { List<XSSFSingleXmlCell> relatedSimpleXmlCells = new ArrayList<>(); int sheetNumber = mapInfo.getWorkbook().getNumberOfSheets(); for (int i = 0; i < sheetNumber; i++) { XSSFSheet sheet = mapInfo.getWorkbook().getSheetAt(i); for (POIXMLDocumentPart p : sheet.getRelations()) { if (p instanceof SingleXmlCells) { SingleXmlCells singleXMLCells = (SingleXmlCells) p; for (XSSFSingleXmlCell cell : singleXMLCells.getAllSimpleXmlCell()) { if (cell.getMapId() == ctMap.getID()) { relatedSimpleXmlCells.add(cell); } } } } } return relatedSimpleXmlCells; }
/** * @return the list of Single Xml Cells that provide a map rule to this mapping. */ public List<XSSFSingleXmlCell> getRelatedSingleXMLCell() { List<XSSFSingleXmlCell> relatedSimpleXmlCells = new Vector<XSSFSingleXmlCell>(); int sheetNumber = mapInfo.getWorkbook().getNumberOfSheets(); for (int i = 0; i < sheetNumber; i++) { XSSFSheet sheet = mapInfo.getWorkbook().getSheetAt(i); for (POIXMLDocumentPart p : sheet.getRelations()) { if (p instanceof SingleXmlCells) { SingleXmlCells singleXMLCells = (SingleXmlCells) p; for (XSSFSingleXmlCell cell : singleXMLCells.getAllSimpleXmlCell()) { if (cell.getMapId() == ctMap.getID()) { relatedSimpleXmlCells.add(cell); } } } } } return relatedSimpleXmlCells; }
/** * @return the list of all Tables that provide a map rule to this mapping */ public List<XSSFTable> getRelatedTables() { List<XSSFTable> tables = new Vector<XSSFTable>(); int sheetNumber = mapInfo.getWorkbook().getNumberOfSheets(); for (int i = 0; i < sheetNumber; i++) { XSSFSheet sheet = mapInfo.getWorkbook().getSheetAt(i); for (POIXMLDocumentPart p : sheet.getRelations()) { if (p.getPackageRelationship().getRelationshipType().equals(XSSFRelation.TABLE.getRelation())) { XSSFTable table = (XSSFTable) p; if (table.mapsTo(ctMap.getID())) { tables.add(table); } } } } return tables; } }
/** * 获取XLSX工作簿指定sheet中图片列表 * * @param workbook 工作簿{@link Workbook} * @param sheetIndex sheet的索引 * @return 图片映射,键格式:行_列,值:{@link PictureData} */ private static Map<String, PictureData> getPicMapXlsx(XSSFWorkbook workbook, int sheetIndex) { final Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(); final XSSFSheet sheet = workbook.getSheetAt(sheetIndex); XSSFDrawing drawing; for (POIXMLDocumentPart dr : sheet.getRelations()) { if (dr instanceof XSSFDrawing) { drawing = (XSSFDrawing) dr; final List<XSSFShape> shapes = drawing.getShapes(); XSSFPicture pic; CTMarker ctMarker; for (XSSFShape shape : shapes) { pic = (XSSFPicture) shape; ctMarker = pic.getPreferredSize().getFrom(); sheetIndexPicMap.put(StrUtil.format("{}_{}", ctMarker.getRow(), ctMarker.getCol()), pic.getPictureData()); } } } return sheetIndexPicMap; } // -------------------------------------------------------------------------------------------------------------- Private method end
protected void read(InputStream is) throws IOException { try { worksheet = WorksheetDocument.Factory.parse(is).getWorksheet(); } catch (XmlException e){ throw new POIXMLException(e); } initRows(worksheet); columnHelper = new ColumnHelper(worksheet); // Look for bits we're interested in for(POIXMLDocumentPart p : getRelations()){ if(p instanceof CommentsTable) { sheetComments = (CommentsTable)p; break; } if(p instanceof XSSFTable) { tables.put( p.getPackageRelationship().getId(), (XSSFTable)p ); } } // Process external hyperlinks for the sheet, if there are any initHyperlinks(); }