busy developers' guide to hssf features.htm

Jakarta POI 是apache的子项目

      <SPAN class=codefrag>Graphics2d</SPAN> is a poor match to the capabilities 
      of the Microsoft Office drawing commands. The older <SPAN 
      class=codefrag>Graphics</SPAN> class offers a closer match but is still a 
      square peg in a round hole. </DIV></DIV>
      <P>All Graphics commands are issued into an <SPAN 
      class=codefrag>HSSFShapeGroup</SPAN>. Here's how it's done: </P><PRE class=code>    a = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 );
    group = patriarch.createGroup( a );
    group.setCoordinates( 0, 0, 80 * 4 , 12 * 23  );
    float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) / (float)Math.abs(group.getY2() - group.getY1());
    g = new EscherGraphics( group, wb, Color.black, verticalPointsPerPixel );
    g2d = new EscherGraphics2d( g );
    drawChemicalStructure( g2d );
                    </PRE>
      <P>The first thing we do is create the group and set it's coordinates to 
      match what we plan to draw. Next we calculate a reasonable 
      fontSizeMultipler then create the EscherGraphics object. Since what we 
      really want is a <SPAN class=codefrag>Graphics2d</SPAN> object we create 
      an EscherGraphics2d object and pass in the graphics object we created. 
      Finally we call a routine that draws into the EscherGraphics2d object. 
</P>
      <P>The vertical points per pixel deserves some more explanation. One of 
      the difficulties in converting Graphics calls into escher drawing calls is 
      that Excel does not have the concept of absolute pixel positions. It 
      measures it's cell widths in 'characters' and the cell heights in points. 
      Unfortunately it's not defined exactly what type of character it's 
      measuring. Presumably this is due to the fact that the Excel will be using 
      different fonts on different platforms or even within the same platform. 
      </P>
      <P>Because of this constraint we've had to implement the concept of a 
      verticalPointsPerPixel. This the amount the font should be scaled by when 
      you issue commands such as drawString(). To calculate this value use the 
      follow formula: </P><PRE class=code>    multipler = groupHeightInPoints / heightOfGroup
                    </PRE>
      <P>The height of the group is calculated fairly simply by calculating the 
      difference between the y coordinates of the bounding box of the shape. The 
      height of the group can be calculated by using a convenience called <SPAN 
      class=codefrag>HSSFClientAnchor.getAnchorHeightInPoints()</SPAN>. </P>
      <P>Many of the functions supported by the graphics classes are not 
      complete. Here's some of the functions that are known to work. </P>
      <UL>
        <LI>fillRect() 
        <LI>fillOval() 
        <LI>drawString() 
        <LI>drawOval() 
        <LI>drawLine() 
        <LI>clearRect() </LI></UL>
      <P>Functions that are not supported will return and log a message using 
      the POI logging infrastructure (disabled by default). </P><A 
      name=Outlining></A>
      <DIV class=h2>
      <H2>Outlining</H2></DIV>
      <P>Outlines are great for grouping sections of information together and 
      can be added easily to columns and rows using the POI API. Here's how: 
</P><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");

    sheet1.groupRow( 5, 14 );
    sheet1.groupRow( 7, 14 );
    sheet1.groupRow( 16, 19 );

    sheet1.groupColumn( (short)4, (short)7 );
    sheet1.groupColumn( (short)9, (short)12 );
    sheet1.groupColumn( (short)10, (short)11 );

    FileOutputStream fileOut = new FileOutputStream(filename);
    wb.write(fileOut);
    fileOut.close();
                    </PRE>
      <P>To collapse (or expand) an outline use the following calls: </P><PRE class=code>    sheet1.setRowGroupCollapsed( 7, true );
    sheet1.setColumnGroupCollapsed( (short)4, true );
                    </PRE>
      <P>The row/column you choose should contain an already created group. It 
      can be anywhere within the group. </P><A name=Images></A>
      <DIV class=h3>
      <H3>Images</H3></DIV>
      <P>Images are part of the drawing support. To add an image just call <SPAN 
      class=codefrag>createPicture()</SPAN> on the drawing patriarch. At the 
      time of writing the following types are supported: </P>
      <UL>
        <LI>PNG 
        <LI>JPG 
        <LI>DIB </LI></UL>
      <P>It should be noted that any existing drawings may be erased once you 
      add a image to a sheet. </P><PRE class=code>    // Create the drawing patriarch.  This is the top level container for
    // all shapes. This will clear out any existing shapes for that sheet.
    HSSFPatriarch patriarch = sheet5.createDrawingPatriarch();

    HSSFClientAnchor anchor;
    anchor = new HSSFClientAnchor(0,0,0,255,(short)2,2,(short)4,7);
    anchor.setAnchorType( 2 );
    patriarch.createPicture(anchor, loadPicture( "src/resources/logos/logoKarmokar4.png", wb ));
  </PRE>
      <P>Creating an image and setting its anchor to the actual width and 
      height:</P><PRE class=code>    HSSFPatriarch patriarch = sheet5.createDrawingPatriarch();

    HSSFPicture picture = patriarch.createPicture(new HSSFClientAnchor(), loadPicture( "src/resources/logos/logoKarmokar4.png", wb ));
    picture.resize();
      </PRE>
      <P>or</P><PRE class=code>    HSSFPatriarch patriarch = sheet5.createDrawingPatriarch();

    HSSFPicture picture = patriarch.createPicture(new HSSFClientAnchor(), loadPicture( "src/resources/logos/logoKarmokar4.png", wb ));
    HSSFClientAnchor preferredSize = picture.getPreferredSize();
    picture.setAnchor(preferredSize);
      </PRE>
      <DIV class="frame warning">
      <DIV class=label>Warning</DIV>
      <DIV class=content>HSSFPicture.resize() works only for JPEG and PNG. Other 
      formats are not yet supported. </DIV></DIV>
      <P>Reading images from a workbook:</P><PRE class=code>    HSSFWorkbook wb;

    List lst = wb.getAllPictures();
    for (Iterator it = lst.iterator(); it.hasNext(); ) {
        HSSFPictureData pict = (HSSFPictureData)it.next();
        String ext = pict.suggestFileExtension();
        byte[] data = pict.getData();
        if (ext.equals("jpeg")){
          FileOutputStream out = new FileOutputStream("pict.jpg");
          out.write(data);
          out.close();
        }
    }
      </PRE><A name=NamedRanges></A>
      <DIV class=h3>
      <H3>Named Ranges and Named Cells</H3></DIV>
      <P>Named Range is a way to refer to a group of cells by a name. Named Cell 
      is a degenerate case of Named Range in that the 'group of cells' contains 
      exactly one cell. You can create as well as refer to cells in a workbook 
      by their named range. When working with Named Ranges, the classes: 
      org.apache.poi.hssf.util.CellReference and &amp; 
      org.apache.poi.hssf.util.AreaReference are used. </P>
      <P>Creating Named Range / Named Cell </P><PRE class=code>    // setup code
    String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(sname);
    sheet.createRow(0).createCell((short) 0).setCellValue(cvalue);

    // 1. create named range for a single cell using areareference
    HSSFName namedCell = wb.createName();
    namedCell.setNameName(cname);
    String reference = sname+"!A1:A1"; // area reference
    namedCell.setReference(reference);

    // 2. create named range for a single cell using cellreference
    HSSFName namedCell = wb.createName();
    namedCell.setNameName(cname);
    String reference = sname+"!A1"; // cell reference
    namedCell.setReference(reference);

    // 3. create named range for an area using AreaReference
    HSSFName namedCell = wb.createName();
    namedCell.setNameName(cname);
    String reference = sname+"!A1:C5"; // area reference
    namedCell.setReference(reference);

            </PRE>
      <P>Reading from Named Range / Named Cell </P><PRE class=code>    // setup code
    String cname = "TestName";
    HSSFWorkbook wb = getMyWorkbook(); // retrieve workbook

    // retrieve the named range
    int namedCellIdx = wb.getNameIndex(cellName);
    HSSFName aNamedCell = wb.getNameAt(namedCellIdx);

    // retrieve the cell at the named range and test its contents
    AreaReference aref = new AreaReference(aNamedCell.getReference());
    CellReference[] crefs = aref.getAllReferencedCells();
    for (int i=0; i&lt;crefs.length; i++) {
        HSSFSheet s = wb.getSheet(crefs[i].getSheetName());
        HSSFRow r = sheet.getRow(crefs[i].getRow());
        HSSFCell c = r.getCell(crefs[i].getCol());
        // extract the cell contents based on cell type etc.
    }
            </PRE>
      <P>Reading from non-contiguous Named Ranges </P><PRE class=code>    // Setup code
    String cname = "TestName";
    HSSFWorkbook wb = getMyWorkbook(); // retrieve workbook

    // Retrieve the named range
    // Will be something like "$C$10,$D$12:$D$14";
    int namedCellIdx = wb.getNameIndex(cellName);
    HSSFName aNamedCell = wb.getNameAt(namedCellIdx);

    // Retrieve the cell at the named range and test its contents
    // Will get back one AreaReference for C10, and
    //  another for D12 to D14
    AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getReference());
    for (int i=0; i&lt;arefs.length; i++) {
        // Only get the corners of the Area
        // (use arefs[i].getAllReferencedCells() to get all cells)
        CellReference[] crefs = arefs[i].getCells();
        for (int j=0; j&lt;crefs.length; j++) {
            // Check it turns into real stuff
            HSSFSheet s = wb.getSheet(crefs[j].getSheetName());
            HSSFRow r = s.getRow(crefs[j].getRow());
            HSSFCell c = r.getCell(crefs[j].getCol());
            // Do something with this corner cell
        }
    }
            </PRE><A name=CellComments></A>
      <DIV class=h3>
      <H3>Cell Comments</H3></DIV>
      <P>In Excel a comment is a kind of a text shape, so inserting a comment is 
      very similar to placing a text box in a worksheet: </P><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Cell comments in POI HSSF");

    // Create the drawing patriarch. This is the top level container for all shapes including cell comments.
    HSSFPatriarch patr = sheet.createDrawingPatriarch();

    //create a cell in row 3
    HSSFCell cell1 = sheet.createRow(3).createCell((short)1);
    cell1.setCellValue(new HSSFRichTextString("Hello, World"));

    //anchor defines size and position of the comment in worksheet
    HSSFComment comment1 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short) 6, 5));

     // set text in the comment
    comment1.setString(new HSSFRichTextString("We can set comments in POI"));

    //set comment author.
    //you can see it in the status bar when moving mouse over the commented cell
    comment1.setAuthor("Apache Software Foundation");

    // The first way to assign comment to a cell is via HSSFCell.setCellComment method
    cell1.setCellComment(comment1);

    //create another cell in row 6
    HSSFCell cell2 = sheet.createRow(6).createCell((short)1);
    cell2.setCellValue(36.6);


    HSSFComment comment2 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 8, (short) 6, 11));
    //modify background color of the comment
    comment2.setFillColor(204, 236, 255);

    HSSFRichTextString string = new HSSFRichTextString("Normal body temperature");

    //apply custom font to the text in the comment
    HSSFFont font = wb.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short)10);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);
    string.applyFont(font);

    comment2.setString(string);
    //by default comments are hidden. This one is always visible.
    comment2.setVisible(true);

    comment2.setAuthor("Bill Gates");

    /**
     * The second way to assign comment to a cell is to implicitly specify its row and column.
     * Note, it is possible to set row and column of a non-existing cell.
     * It works, the commnet is visible.
     */
    comment2.setRow(6);
    comment2.setColumn((short)1);

    FileOutputStream out = new FileOutputStream("poi_comment.xls");
    wb.write(out);
    out.close();
        </PRE>
      <P>Reading cell comments </P><PRE class=code>    HSSFCell cell = sheet.get(3).getColumn((short)1);
    HSSFComment comment = cell.getCellComment();
    if (comment != null) {
      HSSFRichTextString str = comment.getString();
      String author = comment.getAuthor();
    }
    //  alternatively you can retrieve cell comments by (row, column)
    comment = sheet.getCellComment(3, 1);
  </PRE><A name=Autofit></A>
      <DIV class=h3>
      <H3>Adjust column width to fit the contents</H3></DIV><PRE class=code>    HSSFSheet sheet = workbook.getSheetAt(0);
    sheet.autoSizeColumn((short)0); //adjust width of the first column
    sheet.autoSizeColumn((short)1); //adjust width of the second column
        </PRE>
      <DIV class="frame warning">
      <DIV class=label>Warning</DIV>
      <DIV class=content>To calculate column width HSSFSheet.autoSizeColumn uses 
      Java2D classes that throw exception if graphical environment is not 
      available. In case if graphical environment is not available, you must 
      tell Java that you are running in headless mode and set the following 
      system property: <SPAN class=codefrag>java.awt.headless=true 
      </SPAN>(either via <SPAN class=codefrag>-Djava.awt.headless=true</SPAN> 
      startup parameter or via <SPAN 
      class=codefrag>System.setProperty("java.awt.headless", "true")</SPAN>). 
      </DIV></DIV>
      <DIV id=authors align=right>by&nbsp;Glen Stampoultzis,&nbsp;Yegor 
      Kozlov</DIV></DIV></DIV></DIV></TD><!--================= end Content ==================--></TR></TBODY></TABLE><!--================= end Main ==================--><!--================= start Footer ==================-->
<DIV id=footer>
<TABLE cellSpacing=0 cellPadding=4 width="100%" summary=footer border=0>
  <TBODY>
  <TR><!--================= start Copyright ==================-->
    <TD colSpan=2>
      <DIV align=center>
      <DIV class=copyright>Copyright © 2002-2007&nbsp;The Apache Software 
      Foundation. All rights reserved. </DIV></DIV></TD><!--================= end Copyright ==================--></TR>
  <TR>
    <TD align=left><!--================= start Host ==================--><!--================= end Host ==================--></TD>
    <TD align=right><!--================= start Credits ==================-->
      <DIV align=right>
      <DIV class=credit></DIV></DIV><!--================= end Credits ==================--></TD></TR></TBODY></TABLE></DIV><!--================= end Footer ==================--></BODY></HTML>