POI操作Excel

一. Apache 的 POI 可以操作ole对象，对Excel的操作更是方便。

 

http://poi.apache.org/hssf/quick-guide.html

 

quick-guide中介绍了基本的对Excel的控制，可以用JUnit逐项测试，直观的了解下功能。

 

功能项如下：

How to create a new workbook How to create a sheet How to create cells How to create date cells Working with different types of cells Iterate over rows and cells Text Extraction Aligning cells Working with borders Fills and color Merging cells Working with fonts Custom colors Reading and writing Use newlines in cells. Create user defined data formats Fit Sheet to One Page Set print area for a sheet Set page numbers on the footer of a sheet Shift rows Set a sheet as selected Set the zoom magnification for a sheet Create split and freeze panes Repeating rows and columns Headers and Footers Drawing Shapes Styling Shapes Shapes and Graphics2d Outlining Images Named Ranges and Named Cells How to set cell comments How to adjust column width to fit the contents Hyperlinks

 

二. 对合并单元格的格式化

 

for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {

	HSSFRow row = HSSFCellUtil.getRow(i, sheet);

	for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {

		HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);

		//给合并的单元格加边框
		HSSFCellStyle style = wb.createCellStyle();
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBottomBorderColor(HSSFColor.BLACK.index);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setLeftBorderColor(HSSFColor.BLACK.index);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setRightBorderColor(HSSFColor.BLACK.index);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setTopBorderColor(HSSFColor.BLACK.index);

		cell.setCellStyle(style);
	}
}

 

三. 输出文件流

 

//获取到HSSFWorkbook实例（你生成的Excel）
HSSFWorkbook wb = yourManager.generateExcel();

//处理汉字文件名
		String fileName = "统计表";
		fileName = new String(fileName.getBytes("gb2312"), "iso8859-1") + ".xls";

		try {
			response.reset();
			response.setHeader("content-disposition", "attachment;filename=" + fileName);
			response.setContentType("APPLICATION/msexcel");
			wb.write(response.getOutputStream());
		} catch (Exception e) {
			System.out.print(e + "生成Excel统计报表失败！");
		}