用POI实现excel文件导出 代码示例分享

import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Font;

/** *
    * @return  
	* @desc
    */
public class exprotExcel {
	public void exprotExcel(HttpServletResponse response,Integer page, Integer rows) throws Exception{	
 // 第一步，创建一个webbook，对应一个Excel文件  
       HSSFWorkbook wb = new HSSFWorkbook();  
       // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
       HSSFSheet sheet = wb.createSheet("操作记录");  
       // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
       HSSFRow row = sheet.createRow((int) 0);  
       // 第四步，创建单元格，并设置值表头 设置表头居中  
       HSSFCellStyle style = wb.createCellStyle();  
       style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
       
       String fileName = "操作记录.xls";  
       this.setResponseHeader(response, fileName);  
//       ***********************
//       HSSFRow curRow = sheet.createRow((int) 0); //取XSL文件Sheet1页上第2行
//       HSSFCell curCell = curRow.getCell(0); //第1列
       HSSFCellStyle cellStyle = wb.createCellStyle();         
       cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //填充单元格
       cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);    //填黄色
       cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
       Font font = wb.createFont();
       font.setFontHeightInPoints((short)10); //字体大小
       font.setFontName("楷体");
       font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体
       cellStyle.setFont(font);
//     font.setColor(HSSFColor.GREEN.index);    //绿字
//       curCell.setCellStyle(cellStyle);
//       ***********************
       //汇总查询
       HSSFCell cell = row.createCell(0); 
       /*cell.setCellValue("操作记录");  
       cell.setCellStyle(cellStyle);*/  
      
       //设置列名及风格
       row = sheet.createRow(0);
       cell = row.createCell(0); 
       cell.setCellValue("序号");  
       cell.setCellStyle(style); 
       cell = row.createCell(1);  
       cell.setCellValue("时间");  
       cell.setCellStyle(style);  
       cell = row.createCell(2);  
       cell.setCellValue("地点");  
       cell.setCellStyle(style);  
       cell = row.createCell(3);  
       cell.setCellValue("操作人");  
       cell.setCellStyle(style);
       cell = row.createCell(4);  
       cell.setCellValue("操作动作");  
       cell.setCellStyle(style);
       cell = row.createCell(5);  
       cell.setCellValue("描述");  
       cell.setCellStyle(style);
       
      
       //调整excel列的宽度  与上面一一对应   
       sheet.setColumnWidth(0, 4000);
       sheet.setColumnWidth(1, 6000);
       sheet.setColumnWidth(2, 6000);
       sheet.setColumnWidth(3, 4000);
       sheet.setColumnWidth(4, 4000);
       sheet.setColumnWidth(5, 50000);  
      
	   //以下为查询出所有要导出的信息，存储在一个list中
       
      List<OptLog> list =os.queryExcel(optLog);
		if (list.size()>0) {
			 for (int i = 0; i < list.size(); i++) {
		    	  row = sheet.createRow(i+1);  
		          OptLog ls = (OptLog)list.get(i);
		         String st="";
		          switch (ls.getOptType()) {
			        case 1:
			       		st="类型1";
			       		break;
			       	case 2:
			       		st="类型2";
			       		break;
					default:
						break;
				}
		            cell = row.createCell(0);
		            cell.setCellValue(i+1); 
		            cell.setCellStyle(style);
		            cell = row.createCell(1); 
		            cell.setCellValue(ls.getOptTime());
		            cell.setCellStyle(style);
		            cell = row.createCell(2); 
		            cell.setCellValue(ls.getCenterdotName());
		            cell.setCellStyle(style);
		            cell = row.createCell(3); 
		            cell.setCellValue(ls.getUserName());  
		            cell.setCellStyle(style);
		            cell = row.createCell(4); 
		            cell.setCellValue(st);  
		            cell.setCellStyle(style);
		            cell = row.createCell(5); 
		            cell.setCellValue(ls.getOptDesc());  
		            cell.setCellStyle(style);	
		}
     
	  
       try {  
		//输出文件
            OutputStream os = response.getOutputStream();  
            wb.write(os);  
            os.flush();  
            os.close();  
       } catch (Exception e) {      	
           throw new Exception(e.toString());  
       }  
	}
	
	}
	public void setResponseHeader(HttpServletResponse response, String fileName) throws Exception {  
	    try {  
	         try {  
	        	    fileName = new String(fileName.getBytes("GBK"), "ISO-8859-1");
	         } catch (UnsupportedEncodingException e) {  	           
	        	 throw new Exception(e.toString());
	         }  
	         response  
	                   .setContentType("application/octet-stream;charset=ISO8859-1");  
	         response.setHeader("Content-Disposition", "attachment;filename="  
	                   + fileName);  
	         response.addHeader("Pargam", "no-cache");  
	         response.addHeader("Cache-Control", "no-cache");  
	    } catch (Exception ex) {  
	    	throw new Exception(ex.toString()); 
	    }  
	} 
}