blogjava

http://www.blogjava.net/czcode/archive/2006/12/18/88450.html


1、遍历workbook
代码

1. // load源文件
2. POIFSFileSystem fs = new POIFSFileSystem( new FileInputStream(filePath));
3. HSSFWorkbook wb = new HSSFWorkbook(fs);
4. for ( int i = 0 ; i < wb.getNumberOfSheets(); i++) {
5. HSSFSheet sheet = wb.getSheetAt(i);
6. for ( int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i ++) {
7. HSSFRow row = sheet.getRow(i);
8. if (row != null ) {
9. 。。。操作}
10. }
11. }
12. // 目标文件
13. FileOutputStream fos = new FileOutputStream(objectPath);
14. //写文件
15. swb.write(fos);
16. fos.close();

2、得到列和单元格
代码

1. HSSFRow row = sheet.getRow(i);
2. HSSFCell cell = row.getCell(( short ) j);

3、设置sheet名称和单元格内容为中文
代码

1. wb.setSheetName(n, "中文" ,HSSFCell.ENCODING_UTF_16);
2. cell.setEncoding(( short ) 1 );
3. cell.setCellValue( "中文" );

4、单元格内容未公式或数值，可以这样读写
代码

1. cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
2. cell.getNumericCellValue()


5、设置列宽、行高
代码

1. sheet.setColumnWidth(( short )column,( short )width);
2. row.setHeight(( short )height);


6、添加区域，合并单元格
代码

1. Region region = new Region(( short )rowFrom,( short )columnFrom,( short )rowTo,( short )columnTo);
2. sheet.addMergedRegion(region);
3. //得到所有区域
4. sheet.getNumMergedRegions()

7、常用方法
根据单元格不同属性返回字符串数值
代码

1. public String getCellStringValue(HSSFCell cell) {
2. String cellValue = "" ;
3. switch (cell.getCellType()) {
4. case HSSFCell.CELL_TYPE_STRING:
5. cellValue = cell.getStringCellValue();
6. if (cellValue.trim().equals( "" )||cellValue.trim().length()<= 0 )
7. cellValue= " " ;
8. break ;
9. case HSSFCell.CELL_TYPE_NUMERIC:
10. cellValue = String.valueOf(cell.getNumericCellValue());
11. break ;
12. case HSSFCell.CELL_TYPE_FORMULA:
13. cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
14. cellValue = String.valueOf(cell.getNumericCellValue());
15. break ;
16. case HSSFCell.CELL_TYPE_BLANK:
17. cellValue= " " ;
18. break ;
19. case HSSFCell.CELL_TYPE_BOOLEAN:
20. break ;
21. case HSSFCell.CELL_TYPE_ERROR:
22. break ;
23. default :
24. break ;
25. }
26. return cellValue;
27. }


8、常用单元格边框格式

虚线HSSFCellStyle.BORDER_DOTTED
实线HSSFCellStyle.BORDER_THIN
代码

1. public static HSSFCellStyle getCellStyle( short type)
2. {
3. HSSFWorkbook wb = new HSSFWorkbook();
4. HSSFCellStyle style = wb.createCellStyle();
5. style.setBorderBottom(type); //下边框
6. style.setBorderLeft(type); //左边框
7. style.setBorderRight(type); //右边框
8. style.setBorderTop(type); //上边框
9. return style;
10. }


9、设置字体和内容位置
代码

1. HSSFFont f = wb.createFont();
2. f.setFontHeightInPoints(( short ) 11 ); //字号
3. f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //加粗
4. style.setFont(f);
5. style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //左右居中
6. style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //上下居中
7. style.setRotation( short rotation); //单元格内容的旋转的角度
8. HSSFDataFormat df = wb.createDataFormat();
9. style1.setDataFormat(df.getFormat( "0.00%" )); //设置单元格数据格式
10. cell.setCellFormula(string); //给单元格设公式
11. style.setRotation( short rotation); //单元格内容的旋转的角度
12. cell.setCellStyle(style);


10、插入图片
论坛里看到的
代码

1. //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
2. ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
3. BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));
4. ImageIO.write(bufferImg,"jpg",byteArrayOut);
5. //读进一个excel模版
6. FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");
7. fs = new POIFSFileSystem(fos);
8. //创建一个工作薄
9. HSSFWorkbook wb = new HSSFWorkbook(fs);
10. HSSFSheet sheet = wb.getSheetAt(0);
11. HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
12. HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);
13. patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));