List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getDrawingPatriarch
@Override
public HSSFPatriarch getDrawingPatriarch()
From source file:cn.afterturn.easypoi.util.PoiPublicUtil.java
License:Apache License
/** * ?Excel2003/*from w w w . jav a 2 s.com*/ * * @param sheet * ?sheet * @param workbook * * @return Map key:?1_1Stringvalue:?PictureData */ public static Map<String, PictureData> getSheetPictrues03(HSSFSheet sheet, HSSFWorkbook workbook) { Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(); List<HSSFPictureData> pictures = workbook.getAllPictures(); if (!pictures.isEmpty()) { for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) { HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor(); if (shape instanceof HSSFPicture) { HSSFPicture pic = (HSSFPicture) shape; int pictureIndex = pic.getPictureIndex() - 1; HSSFPictureData picData = pictures.get(pictureIndex); String picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1()); sheetIndexPicMap.put(picIndex, picData); } } return sheetIndexPicMap; } else { return sheetIndexPicMap; } }
From source file:com.qihang.winter.poi.util.PoiPublicUtil.java
License:Apache License
/** * ?Excel2003//from w ww . j a v a2 s.c o m * * @param sheet * ?sheet * @param workbook * * @return Map key:?1_1Stringvalue:?PictureData */ public static Map<String, PictureData> getSheetPictrues03(HSSFSheet sheet, HSSFWorkbook workbook) { Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(); List<HSSFPictureData> pictures = workbook.getAllPictures(); if (!pictures.isEmpty()) { for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) { HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor(); if (shape instanceof HSSFPicture) { HSSFPicture pic = (HSSFPicture) shape; int pictureIndex = pic.getPictureIndex() - 1; HSSFPictureData picData = pictures.get(pictureIndex); String picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1()); sheetIndexPicMap.put(picIndex, picData); } } return sheetIndexPicMap; } else { return null; } }
From source file:com.wangzhu.poi.ExcelToHtmlConverter.java
License:Apache License
protected void processSheet(HSSFSheet sheet) { // this.processSheetHeader(this.htmlDocumentFacade.getBody(), sheet); final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); if (physicalNumberOfRows <= 0) { return;/*www. j av a 2 s .c om*/ } Element table = this.htmlDocumentFacade.createTable(); this.htmlDocumentFacade.addStyleClass(table, this.cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;"); Element tableBody = this.htmlDocumentFacade.createTableBody(); final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet); final List emptyRowElements = new ArrayList(physicalNumberOfRows); int maxSheetColumns = 1; for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } if (!this.isOutputHiddenRows() && row.getZeroHeight()) { continue; } Element tableRowElement = this.htmlDocumentFacade.createTableRow(); this.htmlDocumentFacade.addStyleClass(tableRowElement, this.cssClassPrefixRow, "height:" + (row.getHeight() / 20f) + "pt;"); int maxRowColumnNumber = this.processRow(mergedRanges, row, tableRowElement); if (maxRowColumnNumber == 0) { emptyRowElements.add(tableRowElement); } else { if (!emptyRowElements.isEmpty()) { for (Iterator iterator = emptyRowElements.iterator(); iterator.hasNext();) { Element emptyRowElement = (Element) iterator.next(); tableBody.appendChild(emptyRowElement); } emptyRowElements.clear(); } tableBody.appendChild(tableRowElement); } maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber); } this.processColumnWidths(sheet, maxSheetColumns, table); if (this.isOutputColumnHeaders()) { this.processColumnHeaders(sheet, maxSheetColumns, table); } table.appendChild(tableBody); this.htmlDocumentFacade.getBody().appendChild(table); if (null != this.getExcelImageManager()) { table = this.htmlDocumentFacade.createTable(); this.htmlDocumentFacade.addStyleClass(table, this.cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;"); tableBody = this.htmlDocumentFacade.createTableBody(); List<String> urlPaths = this.getExcelImageManager() .getImagePath(sheet.getDrawingPatriarch().getChildren()); if ((urlPaths != null) && (urlPaths.size() != 0)) { Document document = this.htmlDocumentFacade.getDocument(); for (int i = 0, size = urlPaths.size(); i < size; i++) { Element tableRowElement = this.htmlDocumentFacade.createTableRow(); String[] urlPathArr = urlPaths.get(i).split("@"); Element result = document.createElement("img"); result.setAttribute("src", urlPathArr[0]); String imageWidth = urlPathArr[1]; String imageHeight = urlPathArr[2]; result.setAttribute("style", "width:" + imageWidth + "in;height:" + imageHeight + "in;vertical-align:text-bottom;"); Element tableCellElement = this.htmlDocumentFacade.createTableCell(); tableCellElement.appendChild(result); tableRowElement.appendChild(tableCellElement); tableBody.appendChild(tableRowElement); } table.appendChild(tableBody); this.htmlDocumentFacade.getBody().appendChild(table); } } }
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
private void table(final HSSFSheet sheet) { if (sheet == null) { return;/*from w w w. j a va 2s. c o m*/ } if (sheet.getDrawingPatriarch() != null) { final List<HSSFShape> shapes = sheet.getDrawingPatriarch().getChildren(); for (int i = 0; i < shapes.size(); ++i) { if (shapes.get(i) instanceof HSSFPicture) { try { // Gain access to private field anchor. final HSSFShape pic = shapes.get(i); final Field f = HSSFShape.class.getDeclaredField("anchor"); f.setAccessible(true); final HSSFClientAnchor anchor = (HSSFClientAnchor) f.get(pic); // Store picture cell row, column and picture data. if (!pix.containsKey(anchor.getRow1())) { pix.put(anchor.getRow1(), new HashMap<Short, List<HSSFPictureData>>()); } if (!pix.get(anchor.getRow1()).containsKey(anchor.getCol1())) { pix.get(anchor.getRow1()).put(anchor.getCol1(), new ArrayList<HSSFPictureData>()); } pix.get(anchor.getRow1()).get(anchor.getCol1()) .add(book.getAllPictures().get(((HSSFPicture) pic).getPictureIndex())); } catch (final Exception e) { throw new RuntimeException(e); } } } } out.append("<table cellspacing='0' style='border-spacing:0; border-collapse:collapse;'>\n"); for (rowIndex = 0; rowIndex < sheet.getPhysicalNumberOfRows(); ++rowIndex) { tr(sheet.getRow(rowIndex)); } out.append("</table>\n"); }
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
private void table(final HSSFSheet sheet, int startIndex, int endIndex) { if (sheet == null) { return;/*from ww w .j a v a 2 s.c om*/ } if (sheet.getDrawingPatriarch() != null) { final List<HSSFShape> shapes = sheet.getDrawingPatriarch().getChildren(); for (int i = 0; i < shapes.size(); ++i) { if (shapes.get(i) instanceof HSSFPicture) { try { // Gain access to private field anchor. final HSSFShape pic = shapes.get(i); final Field f = HSSFShape.class.getDeclaredField("anchor"); f.setAccessible(true); final HSSFClientAnchor anchor = (HSSFClientAnchor) f.get(pic); // Store picture cell row, column and picture data. if (!pix.containsKey(anchor.getRow1())) { pix.put(anchor.getRow1(), new HashMap<Short, List<HSSFPictureData>>()); } if (!pix.get(anchor.getRow1()).containsKey(anchor.getCol1())) { pix.get(anchor.getRow1()).put(anchor.getCol1(), new ArrayList<HSSFPictureData>()); } pix.get(anchor.getRow1()).get(anchor.getCol1()) .add(book.getAllPictures().get(((HSSFPicture) pic).getPictureIndex())); } catch (final Exception e) { throw new RuntimeException(e); } } } } out.append("<table id=\"" + "rvs_table" + "\" width=\"915\" class=\"mt\">\n"); tr(sheet.getRow(0)); StringBuffer buf = new StringBuffer(); tr(sheet.getRow(0), buf); String t = buf.toString(); resolvedValueSetList.add(t); for (int i = startIndex; i <= endIndex; i++) { tr(sheet.getRow(i)); buf = new StringBuffer(); tr(sheet.getRow(i), buf); t = buf.toString(); resolvedValueSetList.add(t); } out.append("</table>\n"); resolvedValueSetIterator = resolvedValueSetList.listIterator(); }
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
private void table(final HSSFSheet sheet, int startIndex, int col, String code, boolean cdisc) { resolvedValueSetList = new ArrayList(); if (sheet == null) { return;// www .j a va 2 s .c o m } if (sheet.getDrawingPatriarch() != null) { final List<HSSFShape> shapes = sheet.getDrawingPatriarch().getChildren(); for (int i = 0; i < shapes.size(); ++i) { if (shapes.get(i) instanceof HSSFPicture) { try { // Gain access to private field anchor. final HSSFShape pic = shapes.get(i); final Field f = HSSFShape.class.getDeclaredField("anchor"); f.setAccessible(true); final HSSFClientAnchor anchor = (HSSFClientAnchor) f.get(pic); // Store picture cell row, column and picture data. if (!pix.containsKey(anchor.getRow1())) { pix.put(anchor.getRow1(), new HashMap<Short, List<HSSFPictureData>>()); } if (!pix.get(anchor.getRow1()).containsKey(anchor.getCol1())) { pix.get(anchor.getRow1()).put(anchor.getCol1(), new ArrayList<HSSFPictureData>()); } pix.get(anchor.getRow1()).get(anchor.getCol1()) .add(book.getAllPictures().get(((HSSFPicture) pic).getPictureIndex())); } catch (final Exception e) { throw new RuntimeException(e); } } } } out.append("<table id=\"" + "rvs_table" + "\" width=\"915\" class=\"mt\">\n"); tr(sheet.getRow(0)); StringBuffer buf = new StringBuffer(); tr(sheet.getRow(0), buf); String t = buf.toString(); resolvedValueSetList.add(t); int rows = sheet.getPhysicalNumberOfRows(); for (int i = startIndex; i <= rows; i++) { HSSFRow row = sheet.getRow(i); if (row != null) { if (col != -1) { HSSFCell cell = row.getCell(col); if (cell != null) { String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; default: } if ((cdisc && i == startIndex) || (value != null && value.compareTo(code) == 0)) { tr(row); buf = new StringBuffer(); tr(row, buf); t = buf.toString(); resolvedValueSetList.add(t); } } } else { tr(row); buf = new StringBuffer(); tr(row, buf); t = buf.toString(); resolvedValueSetList.add(t); } } } out.append("</table>\n"); resolvedValueSetIterator = resolvedValueSetList.listIterator(); }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ???1SHEET1SHEET// w ww . jav a2 s .c om * * @param destBook -- * @param srcBook -- ?? */ private static void copySheetImage(HSSFWorkbook destBook, HSSFWorkbook srcBook) { //??? HSSFSheet srcSheet = srcBook.getSheetAt(0); //? HSSFSheet destSheet = destBook.getSheetAt(0); //??? int endRowNum = destSheet.getPhysicalNumberOfRows(); //???? List<HSSFPicture> lsSrcPicture = getAllPicture(srcSheet); _log.showDebug("----------source picture size:" + lsSrcPicture.size()); if (lsSrcPicture.isEmpty()) return; //????? List<HSSFPictureData> lsPicData = null; try { lsPicData = srcBook.getAllPictures(); } catch (Exception e) { _log.showWarn( "book?getAllPictures?book??"); HSSFWorkbook tmpBook = copyWorkbook(srcBook); if (tmpBook != null) { lsPicData = tmpBook.getAllPictures(); tmpBook = null; } /* ???? //??? lsPicData = destBook.getAllPictures(); if (lsPicData == null || lsPicData.isEmpty()) return; //??1? List<HSSFPictureData> destData = FactoryUtil.newList(); for (int i = 0, n = lsSrcPicture.size(); i < n; i++) { destData.add(lsPicData.get(0)); } lsPicData = destData;*/ } if (lsPicData == null || lsPicData.isEmpty()) return; _log.showDebug("----------source data size:" + lsPicData.size()); //???? //????sheet???book if (lsSrcPicture.size() > lsPicData.size()) { _log.showWarn("?????"); return; } //?? HSSFPatriarch destDraw = destSheet.getDrawingPatriarch(); if (destDraw == null) { destDraw = destSheet.createDrawingPatriarch(); } //?? List<HSSFPicture> lsDestPicture = getAllPicture(destSheet); int index = lsDestPicture.size(); for (int i = 0, n = lsSrcPicture.size(); i < n; i++) { //? HSSFPicture picture = lsSrcPicture.get(i); //????? HSSFPictureData picdata = lsPicData.get(i); //?? byte[] datas = picdata.getData(); //??? HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor(); //?? anchor.setRow1(anchor.getRow1() + endRowNum); anchor.setRow2(anchor.getRow2() + endRowNum); //??? destBook.addPicture(datas, picdata.getFormat()); //???????+1?? index++; _log.showDebug("---------copy new image index=" + index); destDraw.createPicture(anchor, index); } }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ??POI?ReportImageUtil//from w ww.j a v a2 s . c o m * @param cell -- ? * @param bytes -- */ public static void addImageToSheet(HSSFCell cell, byte[] bytes) { if (cell == null) { _log.showError("-----insertImageToSheet: cell is null!"); return; } if (bytes == null || bytes.length == 0) { _log.showError("-----insertImageToSheet: bytes is null!"); return; } //?? HSSFSheet sheet = cell.getSheet(); //? int firstRow = cell.getRowIndex(); int lastRow = cell.getRowIndex(); int firstCol = cell.getColumnIndex(); int lastCol = cell.getColumnIndex(); //?? CellRangeAddress range = getMergedRegion(cell); if (range != null) { firstRow = range.getFirstRow(); lastRow = range.getLastRow(); firstCol = range.getFirstColumn(); lastCol = range.getLastColumn(); } _log.showDebug("---------image cells=[" + firstRow + "," + firstCol + "," + lastRow + "," + lastCol + "]"); //????5??1023255 HSSFClientAnchor anchor = new HSSFClientAnchor(10, 5, 1013, 250, (short) firstCol, firstRow, (short) lastCol, lastRow); anchor.setAnchorType(HSSFClientAnchor.MOVE_AND_RESIZE); //?? HSSFPatriarch draw = sheet.getDrawingPatriarch(); if (draw == null) { draw = sheet.createDrawingPatriarch(); } //??? sheet.getWorkbook().addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG); //???????+1?? List<HSSFPicture> lsPicture = getAllPicture(sheet); int index = lsPicture.size() + 1; _log.showDebug("---------new image index=" + index); draw.createPicture(anchor, index); }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ??// w w w .j ava2 s . c o m * @param sheet -- ? * @return */ private static List<HSSFPicture> getAllPicture(HSSFSheet sheet) { List<HSSFPicture> lsPicture = FactoryUtil.newList(); HSSFPatriarch draw = sheet.getDrawingPatriarch(); if (draw == null) return lsPicture; //??? List<HSSFShape> lsShape = draw.getChildren(); if (lsShape == null || lsShape.isEmpty()) return lsPicture; for (int i = 0, n = lsShape.size(); i < n; i++) { HSSFShape shape = lsShape.get(i); if (shape instanceof HSSFPicture) { lsPicture.add((HSSFPicture) shape); } } return lsPicture; }
From source file:uk.ac.liverpool.spreadsheet.ExcelFeatureAnalysis.java
License:Apache License
private static void analyseSheet(Sheet ss, Element s, Namespace n, ExcelFeatureAnalysis efa) { // generic part boolean costumFormatting = false; boolean formulae = false; boolean UDF = false; boolean hasComments = false; Set<String> udfs = new HashSet<String>(); FormulaEvaluator evaluator = ss.getWorkbook().getCreationHelper().createFormulaEvaluator(); s.setAttribute("name", ss.getSheetName()); s.setAttribute("firstRow", "" + ss.getFirstRowNum()); s.setAttribute("lastRow", "" + ss.getLastRowNum()); try {//w ww . jav a2s .c o m s.setAttribute("forceFormulaRecalc", "" + ss.getForceFormulaRecalculation()); } catch (Throwable x) { //x.printStackTrace(); } // shapes in detail? Footer footer = ss.getFooter(); if (footer != null) { s.setAttribute("footer", "true"); } Header header = ss.getHeader(); if (header != null) { s.setAttribute("header", "true"); } PaneInformation paneInformation = ss.getPaneInformation(); if (paneInformation != null) { s.setAttribute("panels", "true"); } HSSFSheet hs = null; XSSFSheet xs = null; if (ss instanceof HSSFSheet) { hs = (HSSFSheet) ss; try { if (hs.getDrawingPatriarch() != null) { if (hs.getDrawingPatriarch().containsChart()) s.addContent(new Element("charts", sn)); if (hs.getDrawingPatriarch().countOfAllChildren() > 0) s.addContent(new Element("shapes", sn)); } } catch (Exception x) { x.printStackTrace(); } if (hs.getSheetConditionalFormatting().getNumConditionalFormattings() > 0) { s.setAttribute("conditionalFormatting", "true"); } } if (ss instanceof XSSFSheet) { xs = (XSSFSheet) ss; } Iterator<Row> rows = ss.rowIterator(); int firstColumn = (rows.hasNext() ? Integer.MAX_VALUE : 0); int endColumn = 0; while (rows.hasNext()) { Row row = rows.next(); short firstCell = row.getFirstCellNum(); if (firstCell >= 0) { firstColumn = Math.min(firstColumn, firstCell); endColumn = Math.max(endColumn, row.getLastCellNum()); } } s.setAttribute("firstColumn", "" + firstColumn); s.setAttribute("lastColumn", "" + endColumn); rows = ss.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) if (cell != null) { try { if (!cell.getCellStyle().getDataFormatString().equals("GENERAL")) costumFormatting = true; } catch (Throwable t) { } if (cell.getCellComment() != null) hasComments = true; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: // if (DateUtil.isCellDateFormatted(cell)) { // // System.out.println(cell.getDateCellValue()); // } else { // // System.out.println(cell.getNumericCellValue()); // } break; case Cell.CELL_TYPE_BOOLEAN: // System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: // System.out.println(cell.getCellFormula()); formulae = true; if (!UDF) try { evaluator.evaluate(cell); } catch (Exception x) { if (x instanceof NotImplementedException) { Throwable e = x; //e.printStackTrace(); while (e != null) { for (StackTraceElement c : e.getStackTrace()) { if (c.getClassName().contains("UserDefinedFunction")) { UDF = true; System.out.println("UDF " + e.getMessage()); udfs.add(e.getMessage()); } } e = e.getCause(); } } } break; default: } } } if (costumFormatting) { Element cf = new Element("customisedFormatting", sn); s.addContent(cf); } if (formulae) { Element cf = new Element("formulae", sn); s.addContent(cf); } if (UDF) { Element cf = new Element("userDefinedFunctions", sn); for (String sss : udfs) cf.addContent(new Element("userDefinedFunction", sn).setAttribute("functionName", sss)); s.addContent(cf); } if (hasComments) { Element cf = new Element("cellComments", sn); s.addContent(cf); } }