java中的 POI操作

引入jar包文件: poi-3.11-20141221.jar //后面的jar包主要是支持07的 poi-ooxml-3.11-20141221.jar poi-ooxml-schemas-3.11-20141221.jar xmlbeans-2.6.0.jar

简单的读写实例

/**
	 *向execl中写入
	 * */
	public static void writeExecl() throws FileNotFoundException, IOException {
		//创建工作薄	
		HSSFWorkbook workbook	=new HSSFWorkbook();
		//创建工作表
		HSSFSheet sheet = workbook.createSheet("hello word");
		//创建行 注意是从0开始的
		HSSFRow row = sheet.createRow(3);
		//创建单元格
		HSSFCell cell=row.createCell(3);
		//给单元格设置值 
		cell.setCellValue("hello word");
		//输出文件流 	
		FileOutputStream out=new FileOutputStream("D:\\test.xls");
		//把文件输出
		workbook.write(out);
		try {
			workbook.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			out.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			}
	}
	
	/**
	 * 读取execl中的内容
	 * 
	 * */
	public static void readExecl() throws IOException{
		//读取文件流
		FileInputStream in=new FileInputStream("D://test.xls");
		HSSFWorkbook workbook	=new HSSFWorkbook(in);
		HSSFSheet sheet = workbook.getSheetAt(0);
		HSSFRow row = sheet.getRow(3);
		HSSFCell cell = row.getCell(3);
		String value = cell.getStringCellValue();
		System.out.println("单元格的值为"+value);
		workbook.close();
		in.close();
		
	}
	

poI导入导出操作

/** * 导出用户的所有列表到excel * @param userList 用户列表 * @param outputStream 输出流 */ public static void exportUserExcel(List<User> userList, ServletOutputStream outputStream) { try { //1、创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //1.1、创建合并单元格对象 CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);//起始行号，结束行号，起始列号，结束列号

		//1.2、头标题样式
		HSSFCellStyle style1 = createCellStyle(workbook, (short)16);
		
		//1.3、列标题样式
		HSSFCellStyle style2 = createCellStyle(workbook, (short)13);
		
		//2、创建工作表
		HSSFSheet sheet = workbook.createSheet("用户列表");
		//2.1、加载合并单元格对象
		sheet.addMergedRegion(cellRangeAddress);
		//设置默认列宽
		sheet.setDefaultColumnWidth(25);
		
		//3、创建行
		//3.1、创建头标题行；并且设置头标题
		HSSFRow row1 = sheet.createRow(0);
		HSSFCell cell1 = row1.createCell(0);
		//加载单元格样式
		cell1.setCellStyle(style1);
		cell1.setCellValue("用户列表");
		
		//3.2、创建列标题行；并且设置列标题
		HSSFRow row2 = sheet.createRow(1);
		String[] titles = {"用户名","帐号", "所属部门", "性别", "电子邮箱"};
		for(int i = 0; i < titles.length; i++){
			HSSFCell cell2 = row2.createCell(i);
			//加载单元格样式
			cell2.setCellStyle(style2);
			cell2.setCellValue(titles[i]);
		}
		
		//4、操作单元格；将用户列表写入excel
		if(userList != null){
			for(int j = 0; j < userList.size(); j++){
				HSSFRow row = sheet.createRow(j+2);
				HSSFCell cell11 = row.createCell(0);
				cell11.setCellValue(userList.get(j).getName());
				HSSFCell cell12 = row.createCell(1);
				cell12.setCellValue(userList.get(j).getAccount());
				HSSFCell cell13 = row.createCell(2);
				cell13.setCellValue(userList.get(j).getDept());
				HSSFCell cell14 = row.createCell(3);
				cell14.setCellValue(userList.get(j).isGender()?"男":"女");
				HSSFCell cell15 = row.createCell(4);
				cell15.setCellValue(userList.get(j).getEmail());
			}
		}
		//5、输出
		workbook.write(outputStream);
		workbook.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}

/**
 * 创建单元格样式
 * [@param](https://my.oschina.net/u/2303379) workbook 工作簿
 * [@param](https://my.oschina.net/u/2303379) fontSize 字体大小
 * [@return](https://my.oschina.net/u/556800) 单元格样式
 */
private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {
	HSSFCellStyle style = workbook.createCellStyle();
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
	style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
	//创建字体
	HSSFFont font = workbook.createFont();
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体
	font.setFontHeightInPoints(fontSize);
	//加载字体
	style.setFont(font);
	return style;
}

转载于:https://my.oschina.net/mclongyi/blog/784212