poi导出Excel报表

   下面是对poi导出excel的封装,稍微改动一下就能用于其它项目

 

    pojo类见http://liudeh-009.iteye.com/blog/1847626的Student类

 

    Excel导出类:

  

/**
 * 
 * 类ExportBookOrderTool.java的实现描述：学生数据报表导出工具类
 * @author liudeh_009 2012-11-5 下午02:34:03
 */
public class ExportStudentDataTool {
    private HSSFWorkbook workbook;
    private HSSFSheet sheet;
    private HSSFCellStyle cellStyle;
    
    public ExportStudentDataTool(){
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet();
        workbook.setSheetName(0, "学生数据报表");
        
        Font font =  workbook.createFont();  
        font.setColor(HSSFColor.RED.index); 
        cellStyle = workbook.createCellStyle();  
        cellStyle.setFont(font);
    }   
    
    public void exportExcel(List<Student> Students,HttpServletResponse response){       
        createSheetTitle(sheet);
        int sumCount = createSheetCotent(Students,sheet);
        totalToExport(sheet,sumCount);
        exportExcel2Client(workbook,response);    
    }
    /**
     * 输出excel的标题行
     * @param sheet
     */
    private void createSheetTitle(HSSFSheet sheet){
        int cellCount=0;
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(cellCount++,1);
        cell.setCellValue("ID");//忽视的列
        cell = row.createCell(cellCount++,1);
        cell.setCellValue("姓名");
        cell = row.createCell(cellCount++,1);
        cell.setCellValue("年龄");
    }
    
    /**
     * 输出excel的主体内容
     * @param Students
     * @param sheet
     * @param count
     * @param userId
     * @return
     */
    private int createSheetCotent(List<Student> Students,HSSFSheet sheet){
        int sumRow = 0; //共导出多少条
        HSSFCell cell = null;
        HSSFRow row = null;
        for(Student student : Students){
           if (student == null) {
                continue;
            }
           int cellCount=0;
           sumRow++;
           row = sheet.createRow(sumRow);
           cell = createCell(sumRow,row,cellCount++, 1);
           cell.setCellValue(student.getId());//忽视的列
           
           cell = createCell(sumRow,row,cellCount++, 1);
           cell.setCellValue(student.getName());//预约流水号
           
           cell = createCell(sumRow,row,cellCount++, 1);
           cell.setCellValue(student.getAge());//订单标号
       }
        
       return sumRow;
    }
    
    /**
     * 偶数行字体增加颜色
     * @param Student
     * @param row
     * @param location
     * @param size
     * @return
     */
    @SuppressWarnings("deprecation")
    private HSSFCell createCell(int sumRow,HSSFRow row,int location, int size){
        HSSFCell cell = row.createCell(location,size);
        if(sumRow%2==0){
        	  cell.setCellStyle(cellStyle);  
        } 
        return cell;
    }
    
    
    /**
     * 总共行
     * @param sheet
     * @param totalCount
     */
    private void totalToExport(HSSFSheet sheet,int totalCount){
        HSSFRow row = sheet.createRow(totalCount + 2);
        HSSFCell cell = row.createCell(1,1);
        cell.setCellValue("总共");
        cell = row.createCell(2,1);
        cell.setCellValue(Integer.toString(totalCount));
  }
    
  @SuppressWarnings("unused")
  private void exportExcel2Client(HSSFWorkbook workbook,HttpServletResponse response){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
           String nowDate = sdf.format(new Date());
           String fileName = "attachment; filename=学生数据报表-"+nowDate+".xls";
           OutputStream out = null; 
           try {
                response.setHeader("Content-disposition",new String(fileName.getBytes("gbk"),"ISO-8859-1"));    
                response.setContentType("application/msexcel;charset=GBK");    
                out = response.getOutputStream();
                workbook.write(out);
                out.flush();    
                out.close();    
            } catch (IOException e) {
                e.printStackTrace();
            }finally{
                if(out!=null){
                    try {
                        out.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
    }  
}

   

 

     调用方式非常简单.只需传学生的列表对象stList和response对象,如下:

      

  ExportStudentDataTool exportStudentDataTool = new ExportStudentDataTool();
  exportStudentDataTool.exportExcel(stList, response);

 

 

    导出最终excel表格内容如下:

    

 

   最后要注意,一个sheet的记录行数不能超过65535,超过这个数就要考虑创建多个sheet或者多个excel文件