Java导出数据到Excel文件

Java导出数据到Excel文件需要的jar包：easypoi-0.1.3.jar， poi-3.7-20101029

package com.sais.inkaNet.reportStatistics.operationBeanavior.service;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import com.sais.inkaNet.base.db.VOpbDateResult;
import com.sais.inkaNet.base.db.VOpbMonthResult;
import com.sais.inkaNet.base.db.VOpbWeekResult;
import com.sais.inkaNet.base.db.VOpbYearResult;
public class SummaryHSSF {   
//这个是创建和书写excel文档的代码。
public void outExcel(String sheetP,String[] titles,List list,int width,String address,String type,HttpServletResponse response) throws IOException {   
 
    //创建Workbook对象（这一个对象代表着对应的一个Excel文件）   
                 //HSSFWorkbook表示以xls为后缀名的文件   
    Workbook wb = new HSSFWorkbook();  
    Map<String, CellStyle> styles = createStyles(wb);
    //获得CreationHelper对象,这个应该是一个帮助类   
    CreationHelper helper = wb.getCreationHelper();   
    //创建Sheet并给名字(表示Excel的一个Sheet) 
      Sheet sheet = wb.createSheet(sheetP);
      PrintSetup printSetup = sheet.getPrintSetup();
      printSetup.setLandscape(true);
      sheet.setFitToPage(true);
      sheet.setHorizontallyCenter(true);
      sheet.setDefaultColumnWidth(width);
      Row titleRow = sheet.createRow(0);
      titleRow.setHeightInPoints(45);
      Cell titleCell = titleRow.createCell(0);
      titleCell.setCellValue(sheetP);
      titleCell.setCellStyle(styles.get("title"));
      sheet.addMergedRegion(CellRangeAddress.valueOf(address));
     
      //header row
      Row headerRow = sheet.createRow(1);
      headerRow.setHeightInPoints(60);
      Cell headerCell;
      for (int i = 0; i < titles.length; i++) {
       headerCell = headerRow.createCell(i);
       headerCell.setCellValue(titles[i]);
          headerCell.setCellStyle(styles.get("header"));
      }
      
      VOpbYearResult vOpbYearResult=new VOpbYearResult();
      VOpbWeekResult vOpbWeekResult=new VOpbWeekResult();
      VOpbDateResult vOpbDateResult=new VOpbDateResult();
      VOpbMonthResult vOpbMonthResult=new VOpbMonthResult();
     
    
      
      //表头的设置以及J列和K列的设置
      int rownum = 2;
      for (int i = 0; i < list.size(); i++) {
          Row row = sheet.createRow(rownum++);
          for (int j = 0; j < titles.length; j++) {
              Cell cell = row.createCell(j);
                  cell.setCellStyle(styles.get("cell"));
                  if("1".equals(type)){
                   vOpbYearResult=(VOpbYearResult) list.get(i);
                  
                   if(j==0){
                    cell.setCellValue(vOpbYearResult.getObName());
                   }
                   if(j==1){
                    cell.setCellValue(vOpbYearResult.getObTotleNumber());
                   }
                  }else if("2".equals(type)){
                   vOpbMonthResult=(VOpbMonthResult) list.get(i);
                      
                   if(j==0){
                    cell.setCellValue(vOpbMonthResult.getObName());
                   }
                   if(j==1){
                    cell.setCellValue(vOpbMonthResult.getObTotleNumber());
                   }
                  }else if("3".equals(type)){
                   vOpbWeekResult=(VOpbWeekResult) list.get(i);
                      
                   if(j==0){
                    cell.setCellValue(vOpbWeekResult.getObName());
                   }
                   if(j==1){
                    cell.setCellValue(vOpbWeekResult.getObTotleNumber());
                   }
                  }else if("4".equals(type)){
                   vOpbDateResult=(VOpbDateResult) list.get(i);
                      
                   if(j==0){
                    cell.setCellValue(vOpbDateResult.getObName());
                   }
                   if(j==1){
                    cell.setCellValue(vOpbDateResult.getObTotleNumber());
                   }
                  }
      
          }
      }
      //输出   
      response.setHeader("Content-disposition",  "attachment;  filename=test.xls");//设定输出文件头
      response.setContentType("application/vnd.ms-excel");//定义输出类型
      OutputStream os = response.getOutputStream();
      
     
      wb.write(os); 
      os.close();
     
//      response.flushBuffer();
      //response.reset();
      
//      response.resetBuffer();
    //  response.getWriter().close();
  }   
  /**  
   * 边框  
   * @param wb  
   * @return  
   */  
  public static CellStyle createStyleCell(Workbook wb){   
      CellStyle cellStyle = wb.createCellStyle();   
      //设置一个单元格边框颜色 
      //BORDER_SLANTED_DASH_DOT加粗虚线 
      //BORDER_DASH_DOT虚线 
      cellStyle.setBorderBottom(CellStyle.BORDER_DASH_DOT_DOT);   
      cellStyle.setBorderTop(CellStyle.BORDER_DASH_DOT_DOT);   
      cellStyle.setBorderLeft(CellStyle.BORDER_DASH_DOT_DOT);   
      cellStyle.setBorderRight(CellStyle.BORDER_DASH_DOT_DOT);   
      //设置一个单元格边框颜色   
      cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());   
      cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());   
      cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());   
      cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());           
      return cellStyle;   
  }   
  /**  
   * 设置文字在单元格里面的位置  
   * CellStyle.ALIGN_CENTER  
   * CellStyle.VERTICAL_CENTER  
     * @param cellStyle  
     * @param halign  
     * @param valign  
     * @return  
     */  
    public static CellStyle setCellStyleAlignment(CellStyle cellStyle,short halign,short valign){   
        //设置上下   
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);   
        //设置左右   
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);   
        return cellStyle;   
    }   
    /**  
     * 格式化单元格  
     * 如#,##0.00,m/d/yy去HSSFDataFormat或XSSFDataFormat里面找  
     * @param cellStyle  
     * @param fmt  
     * @return  
     */  
    public static CellStyle setCellFormat(CreationHelper helper,CellStyle cellStyle,String fmt){   
        //还可以用其它方法创建format   
        cellStyle.setDataFormat(helper.createDataFormat().getFormat(fmt));   
        return cellStyle;   
    }   
    /**  
     * 前景和背景填充的着色  
     * @param cellStyle  
     * @param bg IndexedColors.ORANGE.getIndex();  
     * @param fg IndexedColors.ORANGE.getIndex();  
     * @param fp CellStyle.SOLID_FOREGROUND  
     * @return  
     */  
    public static CellStyle setFillBackgroundColors(CellStyle cellStyle,short bg,short fg,short fp){   
        //cellStyle.setFillBackgroundColor(bg);   
        cellStyle.setFillForegroundColor(fg);   
        cellStyle.setFillPattern(fp);   
        return cellStyle;   
    }   
    /**  
     * 设置字体  
     * @param wb  
     * @return  
     */  
    public static Font createFonts(Workbook wb){   
        //创建Font对象   
        Font font = wb.createFont();   
        //设置字体   
        font.setFontName("黑体");   
        //着色   
        font.setColor(HSSFColor.BLUE.index);   
        //斜体   
        font.setItalic(true);   
        //字体大小   
        font.setFontHeight((short)300);   
        return font;   
    }
    
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)18);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);
        Font monthFont = wb.createFont();
        monthFont.setFontHeightInPoints((short)11);
        monthFont.setColor(IndexedColors.WHITE.getIndex());
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(monthFont);
        style.setWrapText(true);
        styles.put("header", style);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setWrapText(true);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell", style);
        return styles;
    }
}