HSSF接口学习记录

一．POI简介

Jakarta POI 是apache的子项目，目标是处理ole2对象。它提供了一组操纵Windows文档的Java API

目前比较成熟的是HSSF接口，处理MS Excel（97-2002）对象。它不象我们仅仅是用csv生成的没有格式的可以由Excel转换的东西，而是真正的Excel对象，你可以控制一些属性如sheet,cell等等。

二．HSSF概况

HSSF 是Horrible SpreadSheet Format的缩写，也即“讨厌的电子表格格式”。 也许HSSF的名字有点滑稽，就本质而言它是一个非常严肃、正规的API。通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。

HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。前者很好理解，后者比较抽象，但操作效率要高得多。

三．开始编码

1 ）． 准备工作

要求:JDK 1.4+POI开发包

可以到 http://www.apache.org/dyn/closer.cgi/jakarta/poi/ 最新的POI工具包

2 ）． EXCEL 结构

HSSFWorkbook excell 文档对象介绍
HSSFSheet excell的表单
HSSFRow excell的行
HSSFCell excell的格子单元
HSSFFont excell字体
HSSFName 名称
HSSFDataFormat 日期格式
在poi1.7中才有以下2项：
HSSFHeader sheet头
HSSFFooter sheet尾
和这个样式
HSSFCellStyle cell样式
辅助操作包括
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表

3 ）．具体用法实例 （采用 usermodel ）

如何读Excel

读取Excel文件时，首先生成一个POIFSFileSystem对象，由POIFSFileSystem对象构造一个HSSFWorkbook，该HSSFWorkbook对象就代表了Excel文档。下面代码读取上面生成的Excel文件写入的消息字串：
POIFSFileSystemfs=newPOIFSFileSystem(new FileInputStream("d:\test.xls"));
HSSFWorkbook 　wb = new HSSFWorkbook(fs);
　　} catch (IOException e) {
　　e.printStackTrace();
　　}
　　HSSFSheet sheet = wb.getSheetAt(0);
　　HSSFRow row = sheet.getRow(0);
　　HSSFCell cell = row.getCell((short) 0);
　　String msg = cell.getStringCellValue();

如何写excel，

将excel的第一个表单第一行的第一个单元格的值写成“a test”。

POIFSFileSystem fs =new POIFSFileSystem(new FileInputStream("workbook.xls"));

    HSSFWorkbook wb = new HSSFWorkbook(fs);

    HSSFSheet sheet = wb.getSheetAt(0);

    HSSFRow row = sheet.getRow(0);

    HSSFCell cell = row.getCell((short)0);

    cell.setCellValue("a test");

    // Write the output to a file

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

fileOut.close();

4） .相关方法介绍

New Workbook

Workbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); Workbook wb = new XSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("workbook.xlsx"); wb.write(fileOut); fileOut.close();

New Sheet

Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); Sheet sheet2 = wb.createSheet("second sheet"); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 


Creating Cells

Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short)0); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue(1); // Or do it on one line. row.createCell(1).setCellValue(1.2); row.createCell(2).setCellValue( createHelper.createRichTextString("This is a string")); row.createCell(3).setCellValue(true); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 


Creating Date Cells

Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow(0); // Create a cell and put a date value in it. The first cell is not styled // as a date. Cell cell = row.createCell(0); cell.setCellValue(new Date()); // we style the second cell as a date (and time). It is important to // create a new cell style from the workbook otherwise you can end up // modifying the built in style and effecting not only this cell but other cells. CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat("m/d/yy h:mm")); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); //you can also set date as java.util.Calendar cell = row.createCell(2); cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(cellStyle); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 


Working with different types of cells

Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow((short)2); row.createCell(0).setCellValue(1.1); row.createCell(1).setCellValue(new Date()); row.createCell(2).setCellValue(Calendar.getInstance()); row.createCell(3).setCellValue("a string"); row.createCell(4).setCellValue(true); row.createCell(5).setCellType(HSSFCell.CELL_TYPE_ERROR); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 


Demonstrates various alignment options

public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow((short) 2); row.setHeightInPoints(30); createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY); createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP); createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx"); wb.write(fileOut); fileOut.close(); } /** * Creates a cell and aligns it a certain way. * * @param wb the workbook * @param row the row to create the cell in * @param column the column number to create the cell in * @param halign the horizontal alignment for the cell. */ private static void createCell(Workbook wb, Row row, short column, short halign, short valign) { Cell cell = row.createCell(column); cell.setCellValue(new XSSFRichTextString("Align It")); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cell.setCellStyle(cellStyle); } 


Working with borders

Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow(1); // Create a cell and put a value in it. Cell cell = row.createCell(1); cell.setCellValue(4); // Style the cell with borders all around. CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREEN.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLUE.getIndex()); style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); cell.setCellStyle(style); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 


Iterate over rows and cells

Sometimes, you'd like to just iterate over all the rows in a sheet, or all the cells in a row. This is possible with a simple for loop.
Luckily, this is very easy. Row defines a CellIterator inner class to handle iterating over the cells (get one with a call to row.cellIterator()), and Sheet provides a rowIterator() method to give an iterator over all the rows.
Alternately, Sheet and Row both implement java.lang.Iterable, so if you're using Java 1.5, you can simply take advantage of the built in "foreach" support - see below.
Sheet sheet = wb.getSheetAt(0); for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) { Row row = (Row)rit.next(); for (Iterator cit = row.cellIterator(); cit.hasNext(); ) { Cell cell = (Cell)cit.next(); // Do something here } } 
HSSFSheet sheet = wb.getSheetAt(0); for (Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator(); rit.hasNext(); ) { HSSFRow row = rit.next(); for (Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator(); cit.hasNext(); ) { HSSFCell cell = cit.next(); // Do something here } } 

Iterate over rows and cells using Java 1.5 foreach loops

Sometimes, you'd like to just iterate over all the rows in a sheet, or all the cells in a row. If you are using Java 5 or later, then this is especially handy, as it'll allow the new foreach loop support to work.
Luckily, this is very easy. Both Sheet and Row implement java.lang.Iterable to allow foreach loops. For Row this allows access to the CellIterator inner class to handle iterating over the cells, and for Sheet gives the rowIterator() to iterator over all the rows.
Sheet sheet = wb.getSheetAt(0); for (Row row : sheet) { for (Cell cell : row) { // Do something here } } 


Getting the cell contents

To get the contents of a cell, you first need to know what kind of cell it is (asking a string cell for its numeric contents will get you a NumberFormatException for example). So, you will want to switch on the cell's type, and then call the appropriate getter for that cell.
In the code below, we loop over every cell in one sheet, print out the cell's reference (eg A3), and then the cell's contents.
// import org.apache.poi.ss.usermodel.*; Sheet sheet1 = wb.getSheetAt(0); for (Row row : sheet1) { for (Cell cell : row) { CellReference cellRef = new CellReference(row.getRowNum(), cell.getCellNum()); System.out.print(cellRef.formatAsString()); System.out.print(" - "); 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()); break; default: System.out.println(); } } } 


Text Extraction

For most text extraction requirements, the standard ExcelExtractor class should provide all you need.
InputStream inp = new FileInputStream("workbook.xls"); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp)); ExcelExtractor extractor = new ExcelExtractor(wb); extractor.setFormulasNotResults(true); extractor.setIncludeSheetNames(false); String text = extractor.getText(); 
For very fancy text extraction, XLS to CSV etc, take a look at /src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java 


Fills and colors

Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short) 1); // Aqua background CellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.BIG_SPOTS); Cell cell = row.createCell((short) 1); cell.setCellValue("X"); cell.setCellStyle(style); // Orange "foreground", foreground being the fill foreground not the font color. style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row.createCell((short) 2); cell.setCellValue("X"); cell.setCellStyle(style); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 


Merging cells

Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow((short) 1); Cell cell = row.createCell((short) 1); cell.setCellValue("This is a test of merging"); sheet.addMergedRegion(new CellRangeAddress( 1, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 2 //last column (0-based) )); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 


Working with fonts

Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow(1); // Create a new font and alter it. Font font = wb.createFont(); font.setFontHeightInPoints((short)24); font.setFontName("Courier New"); font.setItalic(true); font.setStrikeout(true); // Fonts are set into a style so create a new one to use. CellStyle style = wb.createCellStyle(); style.setFont(font); // Create a cell and put a value in it. Cell cell = row.createCell(1); cell.setCellValue("This is a test of fonts"); cell.setCellStyle(style); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 
Note, the maximum number of unique fonts in a workbook is limited to 32767 ( the maximum positive short). You should re-use fonts in your apllications instead of creating a font for each cell. Examples: 
Wrong: 
for (int i = 0; i < 10000; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell((short) 0); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); cell.setCellStyle(style); } 
Correct: 
CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int i = 0; i < 10000; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell((short) 0); cell.setCellStyle(style); } 


Custom colors

HSSF: 
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("Default Palette"); //apply some colors from the standard palette, // as in the previous examples. //we'll use red text on a lime background HSSFCellStyle style = wb.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIME.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.RED.index); style.setFont(font); cell.setCellStyle(style); //save with the default palette FileOutputStream out = new FileOutputStream("default_palette.xls"); wb.write(out); out.close(); //now, let's replace RED and LIME in the palette // with a more attractive combination // (lovingly borrowed from freebsd.org) cell.setCellValue("Modified Palette"); //creating a custom palette for the workbook HSSFPalette palette = wb.getCustomPalette(); //replacing the standard red with freebsd.org red palette.setColorAtIndex(HSSFColor.RED.index, (byte) 153, //RGB red (0-255) (byte) 0, //RGB green (byte) 0 //RGB blue ); //replacing lime with freebsd.org gold palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102); //save with the modified palette // note that wherever we have previously used RED or LIME, the // new colors magically appear out = new FileOutputStream("modified_palette.xls"); wb.write(out); out.close(); 
XSSF: 
XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow(0); XSSFCell cell = row.createCell( 0); cell.setCellValue("custom XSSF colors"); XSSFCellStyle style1 = wb.createCellStyle(); style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128))); style1.setFillPattern(CellStyle.SOLID_FOREGROUND); 


Reading and Rewriting Workbooks

InputStream inp = new FileInputStream("workbook.xls"); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(2); Cell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("a test"); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 


Using newlines in cells

Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(2); Cell cell = row.createCell(2); cell.setCellValue("Use \n with word wrap on to create a new line"); //to enable newlines you need set a cell styles with wrap=true CellStyle cs = wb.createCellStyle(); cs.setWrapText(true); cell.setCellStyle(cs); //increase row height to accomodate two lines of text row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints())); //adjust column width to fit the content sheet.autoSizeColumn((short)2); FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx"); wb.write(fileOut); fileOut.close(); 


Data Formats

Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); CellStyle style; DataFormat format = wb.createDataFormat(); Row row; Cell cell; short rowNum = 0; short colNum = 0; row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); 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(); 


Fit Sheet to One Page

Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); PrintSetup 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(); 


Set Print Area

Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Sheet1"); //sets the print area for the first sheet wb.setPrintArea(0, "$A$1:$C$2"); //Alternatively: wb.setPrintArea( 0, //sheet index 0, //start column 1, //end column 0, //start row 0 //end row ); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 


Set Page Numbers on Footer

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(); 


Using the Convenience Functions

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. 
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(); 


Shift rows up or down on a sheet

Workbook wb = new HSSFWorkbook(); Sheet 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); 


Set a sheet as selected

Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("row sheet"); sheet.setSelected(true); 


Set the zoom magnification

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. 
Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); sheet1.setZoom(3,4); // 75 percent magnification 


Splits and freeze panes

There are two types of panes you can create; freeze panes and split panes. 
A freeze pane is split by columns and rows. You create a freeze pane using the following mechanism: 
sheet1.createFreezePane( 3, 2, 3, 2 ); 
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. 
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. 
Split panes are created with the following call: 
sheet2.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT ); 
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. 
The last parameter indicates which pane currently has the focus. This will be one of Sheet.PANE_LOWER_LEFT, PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT. 
Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); Sheet sheet2 = wb.createSheet("second sheet"); Sheet sheet3 = wb.createSheet("third sheet"); Sheet 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, Sheet.PANE_LOWER_LEFT ); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); 


Repeating rows and columns

It's possible to set up repeating rows and columns in your printouts by using the setRepeatingRowsAndColumns() function in the HSSFWorkbook class. 
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. 
Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); Sheet 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(); 


Headers and Footers

Example is for headers but applies directly to footers. 
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Header 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(); 


  


  

    
3 
顶
0 
踩
    
分享到：
      
      
    
  

  

    

      谁有比按键精灵更好的模拟软件？
      |
      一个女生看了会哭，男生会沉默的故事
    
  
  

    

      
2008-12-15 13:16
      
浏览 2664
      
评论(0)
      
      
            
      
查看更多