POI动态生成Excel

根据操蛋需求写的代码，新手可以参考，大神勿喷！！！

package com.epipe.plm.pdc;

import java.io.IOException;
import java.io.OutputStream;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.util.Region;

import com.rh.core.base.Bean;

/**
 * 利用开源组件POI3.0.2动态导出EXCEL文档
 * 
 * @author liujunzhe
 * @param <T>
 *            应用泛型，代表任意一个符合javabean风格的类
 *            注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
 *            byte[]表jpg格式的图片数据
 */
public class ExportExcel<T> {

    public void exportExcel(String title, List<Bean> beans, OutputStream out) {
        exportExcel(title, beans, out, "yyyy-MM-dd");
    }

    /**
     * 根据集合Bean动态生成Excel
     */
    @SuppressWarnings("unchecked")
    public void exportExcel(String title, List<Bean> beans, OutputStream out,
            String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth(20);

        // 标题样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 边框设置
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 字体居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 16);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);

        // 样式 1
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 指定当单元格内容显示不下时自动换行
        style2.setWrapText(true);

        // 样式2
        HSSFCellStyle style3 = workbook.createCellStyle();
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font3 = workbook.createFont();
        font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style3.setFont(font3);
        // 指定当单元格内容显示不下时自动换行
        style3.setWrapText(true);

        // 标题行
        sheet.addMergedRegion(new Region(0, (short) (0), 0, (short) 6));
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        row.setHeight((short) 800);
        cell.setCellStyle(style);
        HSSFRichTextString text = new HSSFRichTextString(title);
        cell.setCellValue(text);
        // 数据行
        int index = 1;
        int r = 1;
        int rf = 3;
        int j = 1;
        for (Bean b : beans) {
            sheet.addMergedRegion(new Region(r, (short) (0), rf, (short) 0));
            sheet.addMergedRegion(new Region(r, (short) (2), r, (short) 6));
            sheet.addMergedRegion(new Region(rf, (short) (2), rf, (short) 6));
            r += 3;
            rf += 3;
            row = sheet.createRow(index++);
            row.setHeight((short) 500);
            sheet.setColumnWidth(0, 30 * 35);
            for (short i = 0; i < 7; i++) {
                cell = row.createCell(i);
                if (i == 1) {
                    cell.setCellStyle(style3);
                } else {
                    cell.setCellStyle(style2);
                }

                if (i == 1) {
                    cell.setCellValue("工作任务");
                } else if (i == 2) {
                    cell.setCellValue(b.getStr("WORK_TASK"));
                } else if (i == 0) {
                    cell.setCellValue(j++);

                }
            }
            row = sheet.createRow(index++);
            row.setHeight((short) 500);
            for (short i = 0; i < 7; i++) {
                cell = row.createCell(i);
                if (i == 1 || i == 3 || i == 5) {
                    cell.setCellStyle(style3);
                } else {
                    cell.setCellStyle(style2);
                }
                if (i == 1) {
                    cell.setCellValue("主办部门");
                } else if (i == 2) {
                    cell.setCellValue(b.getStr("RESPON_DEPT"));
                } else if (i == 3) {
                    cell.setCellValue("布置时间");
                } else if (i == 4) {
                    cell.setCellValue(b.getStr("FACT_START"));
                } else if (i == 5) {
                    cell.setCellValue("落实时间");
                } else if (i == 6) {
                    cell.setCellValue(b.getStr("FACT_FINISH"));
                }
            }
            row = sheet.createRow(index++);
            row.setHeight((short) 1000);
            for (short i = 0; i < 7; i++) {
                cell = row.createCell(i);
                if (i == 1) {
                    cell.setCellStyle(style3);
                } else {
                    cell.setCellStyle(style2);
                }
                if (i == 1) {
                    cell.setCellValue("落实情况");
                } else if (i == 2) {
                    cell.setCellValue(b.getStr("CARRY_SITUATION"));
                }
            }
        }

        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    
    /**
     * 导出 Excel
     * @param param
     * @throws IOException
     */
    public void export(ParamBean param) {

        HttpServletResponse response = Context.getResponse();
        HttpServletRequest request = Context.getRequest();

        String paramStr = param.getStr("param");
        Bean paramBean = JsonUtils.toBean(paramStr);
        String dataId = paramBean.getStr("pkCodes");
        String dataIdArrayList = "('" + dataId.replaceAll(",", "','") + "')";

        String sql = "select t.type, t.WORK_TASK,t.RESPON_DEPT,t.FACT_START,t.FACT_FINISH,t.CARRY_SITUATION ,m.issue from PDC_WORK_TASK t, PDC_WEEK_MEET m WHERE  t.WEEK_MEET_ID = m.ID and t.ID in "
                + dataIdArrayList;
        // 任务集合
        List<Bean> beans = Context.getExecutor().query(sql);
        String fileName = "";// 文件名称
        String title = "";// 标题
        int issue = beans.get(0).getInt("issue");// 期号
        if (beans.get(0).getStr("type").equals("1")) {
            fileName = "第" + issue + "期任务落实情况";
            title = "上周例会落实情况";
        } else {
            fileName = title = "其他工作落实情况";
        }
        response.setContentType("application/x-zip-compressed;charset=utf-8");
        RequestUtils.setDownFileName(request, response, fileName + ".xls");// 设置文件名称

        javax.servlet.ServletOutputStream outPutStream = null;
        try {
            // 工作表对象
            ExportExcel<Bean> ex = new ExportExcel<Bean>();
            outPutStream = response.getOutputStream();
            ex.exportExcel(title, beans, outPutStream);

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outPutStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }
}