busy developers' guide to hssf features.htm

Jakarta POI 是apache的子项目

    row = sheet.createRow(rowNum++);
    cell = row.createCell(colNum);
    cell.setCellValue(11111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("#,##0.0000"));
    cell.setCellStyle(style);

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=FitTo></A>
      <DIV class=h2>
      <H2>Fit Sheet to One Page</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("format sheet");
    HSSFPrintSetup ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);

    ps.setFitHeight((short)1);
    ps.setFitWidth((short)1);


    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=PrintArea2></A>
      <DIV class=h2>
      <H2>Set Print Area</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    wb.setPrintArea(0, "$A$1:$C$2");
    //sets the print area for the first sheet
    //Alternatively:
    //wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details)

    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();


                    </PRE><A name=FooterPageNumbers></A>
      <DIV class=h2>
      <H2>Set Page Numbers on Footer</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("format sheet");
    HSSFFooter footer = sheet.getFooter()

    footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );



    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=ConvenienceFunctions></A>
      <DIV class=h2>
      <H2>Using the Convenience Functions</H2></DIV>
      <P>The convenience functions live in contrib and provide utility features 
      such as setting borders around merged regions and changing style 
      attributes without explicitly creating new styles. </P><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet( "new sheet" );

    // Create a merged region
    HSSFRow row = sheet1.createRow( (short) 1 );
    HSSFRow row2 = sheet1.createRow( (short) 2 );
    HSSFCell cell = row.createCell( (short) 1 );
    cell.setCellValue( "This is a test of merging" );
    Region region = new Region( 1, (short) 1, 4, (short) 4 );
    sheet1.addMergedRegion( region );

    // Set the border and border colors.
    final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED;
    HSSFRegionUtil.setBorderBottom( borderMediumDashed,
        region, sheet1, wb );
    HSSFRegionUtil.setBorderTop( borderMediumDashed,
        region, sheet1, wb );
    HSSFRegionUtil.setBorderLeft( borderMediumDashed,
        region, sheet1, wb );
    HSSFRegionUtil.setBorderRight( borderMediumDashed,
        region, sheet1, wb );
    HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
    HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
    HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
    HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

    // Shows some usages of HSSFCellUtil
    HSSFCellStyle style = wb.createCellStyle();
    style.setIndention((short)4);
    HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style);
    HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell");
    HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER);

    // Write out the workbook
    FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );
    wb.write( fileOut );
    fileOut.close();
                    </PRE><A name=ShiftRows></A>
      <DIV class=h2>
      <H2>Shift rows up or down on a sheet</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("row sheet");

    // Create various cells and rows for spreadsheet.

    // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
    sheet.shiftRows(5, 10, -5);

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=SelectSheet></A>
      <DIV class=h2>
      <H2>Set a sheet as selected</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("row sheet");
    sheet.setSelected(true);

    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=Zoom></A>
      <DIV class=h2>
      <H2>Set the zoom magnification</H2></DIV>
      <P>The zoom is expressed as a fraction. For example to express a zoom of 
      75% use 3 for the numerator and 4 for the denominator. </P><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");
    sheet1.setZoom(3,4);   // 75 percent magnification
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=Splits></A>
      <DIV class=h2>
      <H2>Splits and freeze panes</H2></DIV>
      <P>There are two types of panes you can create; freeze panes and split 
      panes. </P>
      <P>A freeze pane is split by columns and rows. You create a freeze pane 
      using the following mechanism: </P>
      <P>sheet1.createFreezePane( 3, 2, 3, 2 ); </P>
      <P>The first two parameters are the columns and rows you wish to split by. 
      The second two parameters indicate the cells that are visible in the 
      bottom right quadrant. </P>
      <P>Split pains appear differently. The split area is divided into four 
      separate work area's. The split occurs at the pixel level and the user is 
      able to adjust the split by dragging it to a new position. </P>
      <P>Split panes are created with the following call: </P>
      <P>sheet2.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT ); 
      </P>
      <P>The first parameter is the x position of the split. This is in 1/20th 
      of a point. A point in this case seems to equate to a pixel. The second 
      parameter is the y position of the split. Again in 1/20th of a point. </P>
      <P>The last parameter indicates which pane currently has the focus. This 
      will be one of HSSFSheet.PANE_LOWER_LEFT, PANE_LOWER_RIGHT, 
      PANE_UPPER_RIGHT or PANE_UPPER_LEFT. </P><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");
    HSSFSheet sheet2 = wb.createSheet("second sheet");
    HSSFSheet sheet3 = wb.createSheet("third sheet");
    HSSFSheet sheet4 = wb.createSheet("fourth sheet");

    // Freeze just one row
    sheet1.createFreezePane( 0, 1, 0, 1 );
    // Freeze just one column
    sheet2.createFreezePane( 1, 0, 1, 0 );
    // Freeze the columns and rows (forget about scrolling position of the lower right quadrant).
    sheet3.createFreezePane( 2, 2 );
    // Create a split with the lower left side being the active quadrant
    sheet4.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT );

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=Repeating></A>
      <DIV class=h2>
      <H2>Repeating rows and columns</H2></DIV>
      <P>It's possible to set up repeating rows and columns in your printouts by 
      using the setRepeatingRowsAndColumns() function in the HSSFWorkbook class. 
      </P>
      <P>This function Contains 5 parameters. The first parameter is the index 
      to the sheet (0 = first sheet). The second and third parameters specify 
      the range for the columns to repreat. To stop the columns from repeating 
      pass in -1 as the start and end column. The fourth and fifth parameters 
      specify the range for the rows to repeat. To stop the columns from 
      repeating pass in -1 as the start and end rows. </P><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");
    HSSFSheet sheet2 = wb.createSheet("second sheet");

    // Set the columns to repeat from column 0 to 2 on the first sheet
    wb.setRepeatingRowsAndColumns(0,0,2,-1,-1);
    // Set the the repeating rows and columns on the second sheet.
    wb.setRepeatingRowsAndColumns(1,4,5,1,2);

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=HeaderFooter></A>
      <DIV class=h2>
      <H2>Headers and Footers</H2></DIV>
      <P>Example is for headers but applies directly to footers. </P><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    HSSFHeader header = sheet.getHeader();
    header.setCenter("Center Header");
    header.setLeft("Left Header");
    header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
                    HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=DrawingShapes></A>
      <DIV class=h2>
      <H2>Drawing Shapes</H2></DIV>
      <P>POI supports drawing shapes using the Microsoft Office drawing tools. 
      Shapes on a sheet are organized in a hiearchy of groups and and shapes. 
      The top-most shape is the patriarch. This is not visisble on the sheet at 
      all. To start drawing you need to call <SPAN 
      class=codefrag>createPatriarch</SPAN> on the <SPAN 
      class=codefrag>HSSFSheet</SPAN> class. This has the effect erasing any 
      other shape information stored in that sheet. By default POI will leave 
      shape records alone in the sheet unless you make a call to this method. 
      </P>
      <P>To create a shape you have to go through the following steps: </P>
      <OL>
        <LI>Create the patriarch. 
        <LI>Create an anchor to position the shape on the sheet. 
        <LI>Ask the patriarch to create the shape. 
        <LI>Set the shape type (line, oval, rectangle etc...) 
        <LI>Set any other style details converning the shape. (eg: line 
        thickness, etc...) </LI></OL><PRE class=code>    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    a = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 );
    HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
    shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
                    </PRE>
      <P>Text boxes are created using a different call: </P><PRE class=code>    HSSFTextbox textbox1 = patriarch.createTextbox(
            new HSSFClientAnchor(0,0,0,0,(short)1,1,(short)2,2));
    textbox1.setString(new HSSFRichTextString("This is a test") );
                    </PRE>
      <P>It's possible to use different fonts to style parts of the text in the 
      textbox. Here's how: </P><PRE class=code>    HSSFFont font = wb.createFont();
    font.setItalic(true);
    font.setUnderline(HSSFFont.U_DOUBLE);
    HSSFRichTextString string = new HSSFRichTextString("Woo!!!");
    string.applyFont(2,5,font);
    textbox.setString(string );
                    </PRE>
      <P>Just as can be done manually using Excel, it is possible to group 
      shapes together. This is done by calling <SPAN 
      class=codefrag>createGroup()</SPAN> and then creating the shapes using 
      those groups. </P>
      <P>It's also possible to create groups within groups. </P>
      <DIV class="frame warning">
      <DIV class=label>Warning</DIV>
      <DIV class=content>Any group you create should contain at least two other 
      shapes or subgroups.</DIV></DIV>
      <P>Here's how to create a shape group: </P><PRE class=code>    // Create a shape group.
    HSSFShapeGroup group = patriarch.createGroup(
            new HSSFClientAnchor(0,0,900,200,(short)2,2,(short)2,2));

    // Create a couple of lines in the group.
    HSSFSimpleShape shape1 = group.createShape(new HSSFChildAnchor(3,3,500,500));
    shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
    ( (HSSFChildAnchor) shape1.getAnchor() ).setAnchor((short)3,3,500,500);
    HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor((short)1,200,400,600));
    shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
                    </PRE>
      <P>If you're being observant you'll noticed that the shapes that are added 
      to the group use a new type of anchor: the <SPAN 
      class=codefrag>HSSFChildAnchor</SPAN>. What happens is that the created 
      group has it's own coordinate space for shapes that are placed into it. 
      POI defaults this to (0,0,1023,255) but you are able to change it as 
      desired. Here's how: </P><PRE class=code>    myGroup.setCoordinates(10,10,20,20); // top-left, bottom-right
                    </PRE>
      <P>If you create a group within a group it's also going to have it's own 
      coordinate space. </P><A name=StylingShapes></A>
      <DIV class=h2>
      <H2>Styling Shapes</H2></DIV>
      <P>By default shapes can look a little plain. It's possible to apply 
      different styles to the shapes however. The sorts of things that can 
      currently be done are: </P>
      <UL>
        <LI>Change the fill color. 
        <LI>Make a shape with no fill color. 
        <LI>Change the thickness of the lines. 
        <LI>Change the style of the lines. Eg: dashed, dotted. 
        <LI>Change the line color. </LI></UL>
      <P>Here's an examples of how this is done: </P><PRE class=code>    HSSFSimpleShape s = patriarch.createSimpleShape(a);
    s.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);
    s.setLineStyleColor(10,10,10);
    s.setFillColor(90,10,200);
    s.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT * 3);
    s.setLineStyle(HSSFShape.LINESTYLE_DOTSYS);
                    </PRE><A name=Graphics2d></A>
      <DIV class=h2>
      <H2>Shapes and Graphics2d</H2></DIV>
      <P>While the native POI shape drawing commands are the recommended way to 
      draw shapes in a shape it's sometimes desirable to use a standard API for 
      compatibility with external libraries. With this in mind we created some 
      wrappers for <SPAN class=codefrag>Graphics</SPAN> and <SPAN 
      class=codefrag>Graphics2d</SPAN>. </P>
      <DIV class="frame warning">
      <DIV class=label>Warning</DIV>
      <DIV class=content>It's important to not however before continuing that