poi使用

利用poi操作excel 备忘如下

写操作：

FileOutputStream fos = new FileOutputStream("d:/stock.xls");
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet s = wb.createSheet();
			wb.setSheetName(0, "stock");

			HSSFCell cell;
			int i = 0;
			
			HSSFCellStyle numberCellStyle = wb.createCellStyle();
			HSSFDataFormat numberFormat = wb.createDataFormat();
			numberCellStyle.setDataFormat(numberFormat.getFormat("0.00"));	
			
			HSSFCellStyle profitCellStyle = wb.createCellStyle();
			HSSFDataFormat profitFormat = wb.createDataFormat();			
			profitCellStyle.setDataFormat(profitFormat.getFormat("0.00%"));	
			
			for (Iterator it = data1.iterator(); it.hasNext();) {
				Map m = (Map) it.next();
				HSSFRow row = s.createRow(i++);
				String name = (String) m.get("NAME");
				cell = row.createCell(0);				
				cell.setCellValue(name);

				String code = (String) m.get("CODE");
				cell = row.createCell(1);
				cell.setCellValue(code);

				BigDecimal importPrice = (BigDecimal) m.get("IMPORT_PRICE");
				cell = row.createCell(2);				
				cell.setCellStyle(numberCellStyle); 
				cell.setCellValue(importPrice.doubleValue());

				BigDecimal closePrice = (BigDecimal) m.get("CLOSE_PRICE");
				cell = row.createCell(3);
				cell.setCellStyle(numberCellStyle);
				cell.setCellValue(closePrice.setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue());

				String profit = (String) m.get("PROFIT");
				cell = row.createCell(4);
				cell.setCellValue(profit);
				
				cell = row.createCell(5);
				cell.setCellFormula(getProfitFomulaByRowNum(row.getRowNum()));
				cell.setCellStyle(profitCellStyle);
				
			}
wb.write(fos);
			fos.close();

读操作：

FileInputStream fis = new FileInputStream(
					"D:/eclipse/workspaces/handworkspace/xyzq/xyzq-data/data/stockpool.xls"); // 根据excel文件路径创建文件流
			POIFSFileSystem fs = new POIFSFileSystem(fis); // 利用poi读取excel文件流
			HSSFWorkbook wb = new HSSFWorkbook(fs); // 读取excel工作簿
			HSSFSheet sheet = wb.getSheetAt(0); // 读取excel的sheet，0表示读取第一个、1表示第二个.....
			Map cateMap = cateMap();
			// 获取sheet中总共有多少行数据sheet.getPhysicalNumberOfRows()
			// log.info("sheet.getPhysicalNumberOfRows()="+sheet.getPhysicalNumberOfRows());
			for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
				HSSFRow row = sheet.getRow(i); // 取出sheet中的某一行数据
				if (row != null) {
					StringBuffer sb = new StringBuffer();
					sb.append(row.getCell(0).getStringCellValue()).append(" ")
							.append(row.getCell(1).getStringCellValue())
							.append(" ").append(
									row.getCell(2).getDateCellValue()).append(
									" ").append(
									row.getCell(3).getNumericCellValue())
							.append(" ").append(
									row.getCell(4).getStringCellValue())
							.append(" ").append(
									row.getCell(5).getStringCellValue())
							.append(" ");
					System.out.println(sb);
					Map param = new HashMap();
					param.put("CODE", row.getCell(0).getStringCellValue());
					param.put("NAME", row.getCell(1).getStringCellValue());
					param.put("IMPORT_TIME", row.getCell(2).getDateCellValue());
					param.put("IMPORT_PRICE", new BigDecimal(row.getCell(3)
							.getNumericCellValue()).setScale(2,
							BigDecimal.ROUND_HALF_UP));
					param.put("DESCRIPTION", row.getCell(5)
							.getStringCellValue());
					param.put("RISK_PROMPT", row.getCell(6)
							.getStringCellValue());
					param.put("POOL", new Long(STOCK_POOL_TYPE_0));
					param.put("CATE_ID", cateMap.get(row.getCell(4)
							.getStringCellValue()));
					this.insert("TG_STOCK_POOL", param);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}