HSSF接口学习记录

hao861002

浏览: 84974 次
性别:
来自: 上海

最近访客更多访客>>

sdx0312

yangke9024

iris19860111

jifashi110

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

java软件

Excel FreeBSD Apache UP JDK

一．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
      浏览 2620
      评论(0)
      
      
            
      查看更多
        
  
    
  
    评论
    
    
    
    
  

  
    发表评论
               您还没有登录,请您登录后再发表评论

相关推荐

POI HSSF - Excel实例

NULL 博文链接：https://xuwoool.iteye.com/blog/1131611

HSSF：纯java的Excel解决方案

HSSF：纯java的Excel解决方案,让你轻松学会,事半功倍!!

HSSF 参考

将java数据转化为Excel表格

org.apache.poi.hssf.usermodel.HSSFCell

velocity开发插件org.apache.poi.hssf.usermodel.HSSFCellorg.apache.poi.hssf.usermodel.HSSFCell

org.apache.poi.hssf.record.RecordInputStream$LeftoverDataException

解决POI读取EXCEL时报org.apache.poi.hssf.record.RecordInputStream$LeftoverDataException异常

Apache POI HSSF和XSSF读写EXCEL总结

Apache POI HSSF和XSSF读写EXCEL总结

NPOI.HSSF.DLL

NPOI 是一组可以在.net环境下不依靠office组建，对excel进行读写的DLL，具有很强的实用性与适用性

java下载excel所需HSSF

ALIGN_CENERAL:short:0x0 水平对其

hssf转sxssf—实现包含图形和格式的复杂xlsx文件输出

excel实现大数据量输出，xlsx是必选格式，目前poi...本文中提出，用hssf接收存储内部数据，在输出时转化成sxssf，兼顾存储效率和输出，项目实测中可以达到既定需求，是一个可以借鉴的较好思路。希望能够对你有所启示。

org.apache.poi.hssf.converter，office转html所需包

excel转html word转html ppt转html 所需jar包用于excel、word、ppt在线预览功能的实现

HSSF:使用HSSF 对Excel导入导出的纯Java操作

HSSF 使用HSSF 对Excel导入导出的纯Java操作简介工作中纯靠数据库软件生成的Excel报表有可能不会满足需求,时间工作中需要的数据结构往往是很复杂的,而且在大数据的情况下需要对数据的处理更加精细化,所以单纯的sql...

Apache POI HSSF读写Excel

自己花了几天时间写的基于Apache POI的Java 读取excel文件，功能还是比较齐全

NPOI-HSSF.zip

NPOI-HSSF excel上传读取数据dll插件

POI HSSF 3.2

apache office 操作API POI HSSF 3.2说明

用POI的HSSF来控制EXCEL的研究

用POI的HSSF来控制EXCEL的研究

使用HSSF、XSSH操作Excel数据时遇到的一些问题

NULL 博文链接：https://1017401036.iteye.com/blog/2257570

poi操作Excel，HSSF,XSSF两种方式（.xls与.xlsx）导入导出

poi操作Excel 包含HSSF,XSSF两种方式的导入导出支持97-2003版本的Excel与2007等高版本的Excel 下载之后eclipse导入替换jdk直接运行即可

POI操作Excel详解，HSSF和XSSF

POI操作Excel详解，HSSF和XSSF文章源码开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中，操作Excel目前有两个框架，一个是apache 的poi

POI 中文学习文档(.doc)

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

工作记录
2010-03-08 11:26 743

sqlserver2000 安装报注册表挂起解决： ...

利用ftp服务器实现文件上传下载
2009-08-12 12:09 1472

利用ftp服务器实现文件� ...

hibernate 多条件组合查询之 sql 拼接
2009-08-10 15:38 1821

public static void main(String[ ...

工作记录
2009-06-16 18:13 738

1 用PrintWriter对象写文件 StringB ...

控件通知消息
2009-05-05 13:49 1862

2008年04月06日星期日 00:09 控件通知消 ...

Hibernate的检索策略
2009-05-04 13:54 711

Hibernate的检索策略包括类级别检索策略和关联级别检索策 ...

ResultSet转换为List的方法
2009-04-11 20:39 2465

ResultSet转换为List的方法 private s ...

文件批量上传
2009-03-23 17:06 1111

执行类: /** * @author LX ...

工作异常记录
2009-03-20 18:44 765

1 hbm.xml配置问题 org.springframewo ...

Date类学习总结
2009-03-06 13:34 918

1.计算某一月份的最大天数 Calendar time=Cal ...

java日期(Date)、日历(Calendar)、日期格式(DateFormat)三类介绍
2009-02-16 13:45 2667

语言的Calendar(日历),Date(日期), 和Date ...

关于日期格式的一些记录
2009-02-16 13:43 852

一、自定义月份： Date aDate = new Dat ...

JAVA正则表达式4种常用功能
2009-02-12 10:31 877

JAVA正则表达式4种常� ...

list排序
2009-02-06 11:28 1014

import java.text.Collator; ...

java中删除一个非空目录的方法
2009-02-04 19:03 2918

在java中没有直接删除非空目录的方法，所以要自己编写方法来删 ...

List与数组实体的转换
2009-01-22 17:41 1199

1.List转换成为数组。（这里的List是实体是ArrayL ...

HttpClient认证（3）
2009-01-15 10:59 1329

清单 2. 在 HttpClient 中自定义 SSL 示例 ...

HttpClient认证（2）
2009-01-15 10:47 2309

Form-Based 认证方式在 J2EE 站点中更为常见。这 ...

HttpClient认证（1）
2009-01-15 10:29 1423

J2EE 站点认证简介出于安全性的需要和用户授权管理的考虑 ...

HttpClient 学习整理(4)
2009-01-15 10:05 1521

5．提交XML格式参数

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

HSSF接口学习记录

New Workbook

New Sheet

Creating Cells

Creating Date Cells

Working with different types of cells

Demonstrates various alignment options

Working with borders

Iterate over rows and cells

Iterate over rows and cells using Java 1.5 foreach loops

Getting the cell contents

Text Extraction

Fills and colors

Merging cells

Working with fonts

Custom colors

Reading and Rewriting Workbooks

Using newlines in cells

Data Formats

Fit Sheet to One Page

Set Print Area

Set Page Numbers on Footer

Using the Convenience Functions

Shift rows up or down on a sheet

Set a sheet as selected

Set the zoom magnification

Splits and freeze panes

Repeating rows and columns

Headers and Footers

评论

发表评论

相关推荐

工作记录

利用ftp服务器实现文件上传下载

hibernate 多条件组合查询 之 sql 拼接

工作记录

控件通知消息

Hibernate的检索策略

ResultSet转换为List的方法

文件批量上传

工作异常记录

Date类学习总结

java日期(Date)、日历(Calendar)、日期格式(DateFormat)三类介绍

关于日期格式的一些记录

JAVA正则表达式4种常用功能

list排序

java中删除一个非空目录的方法

List与数组实体的转换

HttpClient认证（3）

HttpClient认证（2）

HttpClient认证（1）

HttpClient 学习整理(4)

最近访客更多访客>>

hibernate 多条件组合查询之 sql 拼接