Java 类org.apache.poi.hssf.usermodel.HSSFPatriarch 实例源码

private void insertItemToSheet(String table, HSSFSheet sheet, ArrayList<String> columns) {
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    Cursor cursor = database.rawQuery("select * from " + table, null);
    cursor.moveToFirst();
    int n = 1;
    while (!cursor.isAfterLast()) {
        HSSFRow rowA = sheet.createRow(n);
        for (int j = 0; j < columns.size(); j++) {
            HSSFCell cellA = rowA.createCell(j);
            if (cursor.getType(j) == Cursor.FIELD_TYPE_BLOB) {
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) j, n, (short) (j + 1), n + 1);
                anchor.setAnchorType(3);
                patriarch.createPicture(anchor, workbook.addPicture(cursor.getBlob(j), HSSFWorkbook.PICTURE_TYPE_JPEG));
            } else {
                cellA.setCellValue(new HSSFRichTextString(cursor.getString(j)));
            }
        }
        n++;
        cursor.moveToNext();
    }
    cursor.close();
}

private static Table<Integer, Integer, ImageData> readAllCellImages(HSSFPatriarch patriarch, Sheet sheet) {
    val images = HashBasedTable.<Integer, Integer, ImageData>create();
    val allPictures = sheet.getWorkbook().getAllPictures();
    for (val shape : patriarch.getChildren()) {
        if (!(shape instanceof HSSFPicture && shape.getAnchor() instanceof HSSFClientAnchor)) continue;

        val picture = (HSSFPicture) shape;
        val imageData = createImageData(allPictures.get(picture.getPictureIndex() - 1));

        val axisRow = computeAxisRowIndex(sheet, picture);
        val axisCol = computeAxisColIndex(sheet, picture);

        images.put(axisRow, axisCol, imageData);
    }

    return images;
}

/**
 * Output Datums in Characterization Results for work sheet.
 *
 * @param rowIndex
 * @param filePath
 * @param wb
 * @param sheet
 */
public static int createImage(int rowIndex, short colIndex,
        String filePath, HSSFWorkbook wb, HSSFSheet sheet,
        HSSFPatriarch patriarch) throws IOException {
    short topLeftCell = colIndex;
    short bottomRightCell = (short) (colIndex + 7);
    int topLeftRow = rowIndex + 1;
    int bottomRightRow = rowIndex + 22;
    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255,
            topLeftCell, topLeftRow, bottomRightCell, bottomRightRow);
    anchor.setAnchorType(2); // 2 = Move but don't size with cells
    patriarch.createPicture(anchor, loadPicture(filePath, wb));
    rowIndex = bottomRightRow + 3;

    return rowIndex;
}

public static Table<Integer, Integer, ImageData> readAllCellImages(Sheet sheet) {
    val patriarch = sheet.getDrawingPatriarch();
    if (patriarch instanceof XSSFDrawing) {
        return readAllCellImages((XSSFDrawing) patriarch, sheet);
    } else if (patriarch instanceof HSSFPatriarch) {
        return readAllCellImages((HSSFPatriarch) patriarch, sheet);
    }

    return HashBasedTable.create();
}

public void exportExcel(String title, String[] headers,
          List<String[]> dataset, OutputStream out) {

     // 声明一个工作薄
      HSSFWorkbook workbook = new HSSFWorkbook();
      // 生成一个表格
      HSSFSheet sheet = workbook.createSheet(title);
      // 设置表格默认列宽度为15个字节
      sheet.setDefaultColumnWidth((short) 15);
      // 生成一个样式
      HSSFCellStyle style = workbook.createCellStyle();
      HSSFCellStyle style2 = workbook.createCellStyle();
      HSSFRow row=null;
   // 声明一个画图的顶级管理器
      HSSFPatriarch  patriarch = sheet.createDrawingPatriarch();
    this.buildHeader(workbook,sheet,style,style2,patriarch,title, headers);
      // 遍历集合数据，产生数据行
      Iterator<String[]> it = dataset.iterator();
      int index = 0;
      while (it.hasNext()) {
          index++;
          row = sheet.createRow(index);
          String[] rowData =it.next();
          for (short i = 0; i < rowData.length; i++) {
              HSSFCell cell = row.createCell(i);
              cell.setCellStyle(style2);
              cell.setCellValue(rowData[i]);
          }
      }
      try {
    workbook.write(out);
} catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}
  }

public static void exportDetail(PublicationBean aPub, OutputStream out)
        throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("detailSheet");
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    int startRow = 0;
    setDetailSheet(aPub, wb, sheet, patriarch, startRow);
    wb.write(out);
    if (out != null) {
        out.flush();
        out.close();
    }
}

/**
 * Output Composition Files data => bodyCompositionFileSummaryView.jsp
 *
 * @param compBean
 * @param sheet
 * @param headerStyle
 * @param rowIndex
 */
private static int outputFilesEntities(CompositionBean compBean,
        HSSFWorkbook wb, HSSFCellStyle headerStyle,
        HSSFCellStyle hlinkStyle, int entityCount, String downloadURL) {
    List<FileBean> nanoList = compBean.getFiles();
    if (nanoList != null && !nanoList.isEmpty()) {
        StringBuilder sb = new StringBuilder();
        for (FileBean nanoEntity : nanoList) {
            if (!StringUtils.isEmpty(nanoEntity.getDomainFile().getType())) {
                int rowIndex = 0;
                sb.setLength(0);
                sb.append(entityCount++).append('.').append(
                        nanoEntity.getDomainFile().getType());

                // Create one work sheet for each Composition File.
                HSSFSheet sheet = wb.createSheet(sb.toString());
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

                // 1. Output Composition type at (0, 0).
                HSSFRow row = sheet.createRow(rowIndex++);
                ExportUtils.createCell(row, 0, headerStyle,
                        CompositionBean.FILE_SELECTION);
                rowIndex++; // Create one empty line as separator.

                // 2. Output File info, one File per sheet.
                outputFile(nanoEntity, downloadURL, wb, sheet, headerStyle,
                        hlinkStyle, patriarch, rowIndex);
            }
        }
    }
    return entityCount;
}

/**
 * Output Characterization Results for work sheet.
 *
 * @param charBean
 * @param sheet
 * @param headerStyle
 * @param rowIndex
 * @throws IOException
 */
private static int outputCharResults(CharacterizationBean charBean,
        String downloadURL, HSSFWorkbook wb, HSSFSheet sheet,
        HSSFCellStyle headerStyle, HSSFCellStyle hlinkStyle,
        HSSFPatriarch patriarch, int rowIndex) throws IOException {
    // 9. Output Characterization Results at (8, 0).
    List<FindingBean> findings = charBean.getFindings();
    if (findings != null && !findings.isEmpty()) {
        int count = 1;
        for (FindingBean findingBean : findings) {
            rowIndex++; // Create one empty line as separator.
            HSSFRow row = sheet.createRow(rowIndex++);
            ExportUtils
                    .createCell(row, 0, headerStyle, CHAR_RESULT + count);

            // 9a. Output Characterization Datum Results.
            rowIndex = outputDatumResult(findingBean, sheet, headerStyle,
                    rowIndex);

            // 9b. Output Characterization File Results.
            rowIndex = outputFileResult(findingBean, downloadURL, wb,
                    sheet, headerStyle, hlinkStyle, patriarch, rowIndex);
            count++;
        }
    }
    return rowIndex;
}

private void setImage(final HSSFWorkbook workbook, final HSSFSheet sheet, final CellLocation cellLocation, int width, int height) {
    POIUtils.setCellValue(sheet, cellLocation, "");

    if (imageBuffer != null) {
        final HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

        final HSSFPicture picture = patriarch.createPicture(new HSSFClientAnchor(), pictureIndex);

        final Dimension dimension = picture.getImageDimension();
        final float rate = (float) dimension.width / (float) dimension.height;
        final float specifiedRate = (float) width / (float) height;

        if (width == -1 || height == -1) {
            width = dimension.width;
            height = dimension.height;

        } else {
            if (rate > specifiedRate) {
                if (dimension.width > width) {
                    height = (int) (width / rate);

                } else {
                    width = dimension.width;
                    height = dimension.height;
                }

            } else {
                if (dimension.height > height) {
                    width = (int) (height * rate);

                } else {
                    width = dimension.width;
                    height = dimension.height;
                }
            }
        }

        final HSSFClientAnchor preferredSize = getPreferredSize(sheet, new HSSFClientAnchor(0, 0, 0, 0, (short) cellLocation.c, cellLocation.r, (short) 0, 0), width, height);
        picture.setAnchor(preferredSize);
    }
}

private void setImage(HSSFWorkbook workbook, HSSFSheet sheet,
        CellLocation cellLocation, int width, int height) {
    POIUtils.setCellValue(sheet, cellLocation, "");

    if (this.imageBuffer != null) {
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

        HSSFPicture picture = patriarch.createPicture(
                new HSSFClientAnchor(), this.pictureIndex);

        Dimension dimension = picture.getImageDimension();
        float rate = (float) dimension.width / (float) dimension.height;
        float specifiedRate = (float) width / (float) height;

        if (width == -1 || height == -1) {
            width = dimension.width;
            height = dimension.height;

        } else {
            if (rate > specifiedRate) {
                if (dimension.width > width) {
                    height = (int) (width / rate);

                } else {
                    width = dimension.width;
                    height = dimension.height;
                }

            } else {
                if (dimension.height > height) {
                    width = (int) (height * rate);

                } else {
                    width = dimension.width;
                    height = dimension.height;
                }
            }
        }

        HSSFClientAnchor preferredSize = this.getPreferredSize(sheet,
                new HSSFClientAnchor(0, 0, 0, 0, (short) cellLocation.c,
                        cellLocation.r, (short) 0, 0), width, height);
        picture.setAnchor(preferredSize);
    }
}

private void setImage(HSSFWorkbook workbook, HSSFSheet sheet,
        CellLocation cellLocation, int width, int height) {
    POIUtils.setCellValue(sheet, cellLocation, "");

    if (this.imageBuffer != null) {
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

        HSSFPicture picture = patriarch.createPicture(
                new HSSFClientAnchor(), this.pictureIndex);

        Dimension dimension = picture.getImageDimension();
        float rate = (float) dimension.width / (float) dimension.height;
        float specifiedRate = (float) width / (float) height;

        if (width == -1 || height == -1) {
            width = dimension.width;
            height = dimension.height;

        } else {
            if (rate > specifiedRate) {
                if (dimension.width > width) {
                    height = (int) (width / rate);

                } else {
                    width = dimension.width;
                    height = dimension.height;
                }

            } else {
                if (dimension.height > height) {
                    width = (int) (height * rate);

                } else {
                    width = dimension.width;
                    height = dimension.height;
                }
            }
        }

        HSSFClientAnchor preferredSize = this.getPreferredSize(sheet,
                new HSSFClientAnchor(0, 0, 0, 0, (short) cellLocation.c,
                        cellLocation.r, (short) 0, 0), width, height);
        picture.setAnchor(preferredSize);
    }
}

/**
 * Output Composition File info => bodyCompositionFileSummaryView.jsp
 *
 * @param fileBeans
 * @param sheet
 * @param headerStyle
 * @param rowIndex
 * @return
 */
private static int outputFile(FileBean fileBean, String downloadURL,
        HSSFWorkbook wb, HSSFSheet sheet, HSSFCellStyle headerStyle,
        HSSFCellStyle hlinkStyle, HSSFPatriarch patriarch, int rowIndex) {
    File file = fileBean.getDomainFile();

    // 1. output File Type.
    HSSFRow row = sheet.createRow(rowIndex++);
    ExportUtils.createCell(row, 0, headerStyle, file.getType());
    rowIndex++; // Create one empty line as separator.

    // 2. output Title and Download Link.
    row = sheet.createRow(rowIndex++);
    ExportUtils.createCell(row, 0, headerStyle, "Title and Download Link");

    // Construct the URL for downloading the file.
    StringBuilder sb = new StringBuilder(downloadURL);
    sb.append(file.getId());
    if (file.getUriExternal()) {
        ExportUtils.createCell(row, 1, hlinkStyle, file.getUri(), sb
                .toString());
    } else if (fileBean.isImage()) {
        ExportUtils.createCell(row, 1, file.getTitle());
        sb.setLength(0);
        sb.append(fileRoot).append(java.io.File.separator);
        sb.append(file.getUri());
        String filePath = sb.toString();
        java.io.File imgFile = new java.io.File(filePath);
        if (imgFile.exists()) {
            try {
                rowIndex = ExportUtils.createImage(rowIndex, (short) 1,
                        filePath, wb, sheet, patriarch);
            } catch (Exception e) {
                logger.error("Error exporting Comp image file.", e);
            }
        } else {
            logger.error("Composition image file not exists: " + filePath);
        }
    } else {
        ExportUtils.createCell(row, 1, hlinkStyle, file.getTitle(), sb
                .toString());
    }

    // 3. output Keywords.
    Collection<Keyword> keywords = file.getKeywordCollection();
    if (keywords != null && !keywords.isEmpty()) {
        sb.setLength(0);
        for (Keyword keyword : keywords) {
            sb.append(',').append(' ').append(keyword.getName());
        }
        row = sheet.createRow(rowIndex++);
        ExportUtils.createCell(row, 0, headerStyle, "Keywords");
        ExportUtils.createCell(row, 1, sb.substring(2));
    }

    // 4. output Description.
    if (!StringUtils.isEmpty(file.getDescription())) {
        row = sheet.createRow(rowIndex++);
        ExportUtils.createCell(row, 0, headerStyle, "Description");
        ExportUtils.createCell(row, 1, file.getDescription());
    }

    return rowIndex;
}

/**
 * Outputting Files info: => bodyFileView.jsp
 *
 * @param fileBeans
 * @param sheet
 * @param headerStyle
 * @param rowIndex
 * @return
 */
private static int outputFiles(List<FileBean> fileBeans,
        String downloadURL, HSSFWorkbook wb, HSSFSheet sheet,
        HSSFCellStyle headerStyle, HSSFCellStyle hlinkStyle,
        HSSFPatriarch patriarch, int rowIndex) {
    // Output file table Header.
    HSSFRow row = sheet.createRow(rowIndex++);
    ExportUtils.createCell(row, 0, headerStyle, "File Type");
    ExportUtils.createCell(row, 1, headerStyle, "Title and Download Link");
    ExportUtils.createCell(row, 2, headerStyle, "Keywords");
    ExportUtils.createCell(row, 3, headerStyle, "Description");
    for (FileBean fileBean : fileBeans) {
        File file = fileBean.getDomainFile();
        if (!StringUtils.isEmpty(file.getType())) {
            // 1. output File Type.
            row = sheet.createRow(rowIndex++);
            ExportUtils.createCell(row, 0, file.getType());

            /**
             * 2. output Title and Download Link. Construct the URL for
             * downloading the file.
             */
            StringBuilder sb = new StringBuilder(downloadURL);
            sb.append(file.getId());
            if (file.getUriExternal().booleanValue()) {
                ExportUtils.createCell(row, 1, hlinkStyle, file.getUri(),
                        sb.toString());
            } else if (fileBean.isImage()) {
                ExportUtils.createCell(row, 1, file.getTitle());
                sb.setLength(0);
                sb.append(fileRoot).append(java.io.File.separator);
                sb.append(file.getUri());
                String filePath = sb.toString();
                java.io.File imgFile = new java.io.File(filePath);
                if (imgFile.exists()) {
                    try {
                        rowIndex = ExportUtils.createImage(rowIndex,
                                (short) 1, filePath, wb, sheet, patriarch);
                    } catch (Exception e) {
                        logger.error("Error exporting Comp image file.", e);
                    }
                } else {
                    logger.error("Composition image file not exists: "
                            + filePath);
                }
            } else {
                ExportUtils.createCell(row, 1, hlinkStyle, file.getTitle(),
                        sb.toString());
            }

            // 3. output Keywords.
            Collection<Keyword> keywords = file.getKeywordCollection();
            if (keywords != null && !keywords.isEmpty()) {
                sb.setLength(0);
                for (Keyword keyword : keywords) {
                    sb.append(',').append(' ').append(keyword.getName());
                }
                ExportUtils.createCell(row, 2, sb.substring(2));
            }

            // 4. output Description.
            if (!StringUtils.isEmpty(file.getDescription())) {
                ExportUtils.createCell(row, 3, file.getDescription());
            }
        }
    }
    return rowIndex;
}

/**
 * Output Sample Characterization Summary report (==>
 * bodyCharacterizationSummaryPrintViewTable.jsp)
 *
 * @param summaryBean
 * @param wb
 * @throws IOException
 */
private static void outputSummarySheet(List<String> charTypes,
        CharacterizationSummaryViewBean summaryBean, String downloadURL,
        HSSFWorkbook wb) throws IOException {
    HSSFFont headerFont = wb.createFont();
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    headerStyle.setFont(headerFont);

    HSSFCellStyle hlinkStyle = wb.createCellStyle();
    HSSFFont hlinkFont = wb.createFont();
    hlinkFont.setUnderline(HSSFFont.U_SINGLE);
    hlinkFont.setColor(HSSFColor.BLUE.index);
    hlinkStyle.setFont(hlinkFont);

    int charCount = 1;
    Map<String, SortedSet<CharacterizationBean>> charBeanMap = summaryBean
            .getType2Characterizations();
    for (String type : charTypes) {
        // Output data of report
        SortedSet<CharacterizationBean> charBeans = charBeanMap.get(type);
        if (charBeans != null && !charBeans.isEmpty()) {
            for (CharacterizationBean charBean : charBeans) {
                int rowIndex = 0;

                // Create one work sheet for each Characterization.
                HSSFSheet sheet = wb.createSheet(charCount++ + "."
                        + charBean.getCharacterizationName());
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

                // 1. Output Characterization type at (0, 0).
                rowIndex = outputHeader(charBean, sheet, headerStyle,
                        rowIndex);
                // 2. Output Assay Type (2, 0).
                rowIndex = outputAssayType(charBean, sheet, headerStyle,
                        rowIndex);
                // 3. Output POC at (3, 0).
                rowIndex = outputPOC(charBean, sheet, headerStyle, rowIndex);
                // 4. Output Characterization Date at (4, 0).
                rowIndex = outputCharDate(charBean, sheet, headerStyle,
                        rowIndex);
                // 5. Output Protocol at (5, 0).
                rowIndex = outputProtocol(charBean, sheet, headerStyle,
                        rowIndex);
                // 6. Output Properties at (6, 0).
                rowIndex = outputProperties(charBean, sheet, headerStyle,
                        rowIndex);
                // 7. Output Design Description at (7, 0).
                rowIndex = outputDesignDescription(charBean, sheet,
                        headerStyle, rowIndex);
                // 8. Output Technique and Instruments at (8, 0).
                rowIndex = outputTechInstruments(charBean, sheet,
                        headerStyle, rowIndex);
                // 9. Output Characterization Results at (9, 0).
                rowIndex = outputCharResults(charBean, downloadURL, wb,
                        sheet, headerStyle, hlinkStyle, patriarch, rowIndex);
                // 10.Output Analysis and Conclusion at (10, 0).
                rowIndex = outputConclusion(charBean, sheet, headerStyle,
                        rowIndex);
            }
        }
    }
}

@Override
public void inlineToXls(HSSFPatriarch patriarch, HSSFCell resultCell, Object paramValue, Matcher matcher) {
    throw new UnsupportedOperationException("Inline html content to XSL is not supported");
}

private void buildHeader(HSSFWorkbook workbook, HSSFSheet sheet, HSSFCellStyle style, HSSFCellStyle style2,HSSFPatriarch patriarch,String title,String[] headers){

    // 设置这些样式
    style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // 生成一个字体
    HSSFFont font = workbook.createFont();
    font.setColor(HSSFColor.VIOLET.index);
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    // 把字体应用到当前的样式
    style.setFont(font);
    // 生成并设置另一个样式
    style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
    style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    // 生成另一个字体
    HSSFFont font2 = workbook.createFont();
    font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    // 把字体应用到当前的样式
    style2.setFont(font2);

    // 定义注释的大小和位置,详见文档
    HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
            0, 0, 0, (short) 4, 2, (short) 6, 5));
    // 设置注释内容
    comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
    // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
    comment.setAuthor("leno");

    // 产生表格标题行
    HSSFRow row = sheet.createRow(0);
    for (short i = 0; i < headers.length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellStyle(style);
        HSSFRichTextString text = new HSSFRichTextString(headers[i]);
        cell.setCellValue(headers[i]);
    }

}