Java使用poi来导出Excel表格

package com.ifourthwall.ppm.controller;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

/***
 * Excel导出工具类
 * @author gzb
 */
@SuppressWarnings({ "deprecation", "unused" })
public class ExportExcelUtils {

    public static String TITlE = "title";//标题字体
    public static String HEAD = "head";
    public static String SYSTEM = "system";//系统字体
    public static String NORMAL = "normal";//正常字体
    public static String LINETYPE = "lineType";//条线字体
    public static String FUWU = "fuwu";//服务区域字体。样式与normal一样。用于区分是否换行
    public static int jobNameWidth = 21 * 160;//系统名称宽度
    public static int seqWidth = 13 * 160;//档案号宽度
    public static int taskWidth = 51 * 160;//保养类别宽度
    public static int zoneWidth = 50 * 160;//服务区域宽度
    public static int cycleWidth = 20 * 160;//服务频率宽度
    public static int vendorWidth = 20 * 160;//负责方宽度
    public static int weekNoWidth = 4 * 160;//周宽度
    public static int titleHeight = 23 * 3;//标题高度
    public static int lineTypeHeight = 7 * 3;//条线高度
    public static int weekNoHeight = 4 * 3;//周高度
    public static short GREEN = HSSFColor.LIME.index;//绿色
    public static short RED = HSSFColor.RED.index;//红色
    public static short ORANGE = HSSFColor.ORANGE.index;//橙色
    public static short GREY = HSSFColor.GREY_50_PERCENT.index;//灰色
    public static short WHITE = HSSFColor.WHITE.index;//白色
    public static short GREENOUT = HSSFColor.GREEN.index;//绿色

    /**
     *  用于本地测试
     * @author gzb
     * Date: 2016年11月7日 下午4:11:36
     * @param filePath 文件路径
     * @param wb 要写入文件的工作簿
     * @return
     * @throws Exception
     */
    public static synchronized String writeExcel(final String filePath, HSSFWorkbook wb) throws Exception {
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream(filePath);
            wb.write(fileOut);
        } catch (Exception e) {
            throw e;
        } finally {
            if (fileOut != null) {
                fileOut.close();
            }
            if (wb != null) {
                wb.close();
            }
        }

        return filePath;
    }

    /**
     * excel 下载
     * @author xuye
     * Date: 2016年11月10日 上午11:09:26
     * @param wb
     * @param fileTile
     * @param response
     */
    public static void exportExeclData(HSSFWorkbook wb, String fileTile, HttpServletResponse response) {
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        String filename = fileTile + ".xls";
        try {
            wb.write(os);//把excel装到os中
            response.reset();//清缓存，设置不缓存
            response.setContentType("application/vnd.ms-excel;charset=utf-8");//文件客户端按.xls来解析文档
            //告知客户端为下载类型文件
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "iso-8859-1"));
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            ServletOutputStream out = response.getOutputStream();//读取文件流
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {//下载
                bos.write(buff, 0, bytesRead);
            }
        } catch (final Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (bis != null) {
                    bis.close();
                }
                if (bos != null) {
                    bos.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }

    /**
     * 设置标题
     * @author xuye
     * Date: 2016年11月8日 上午9:49:41
     * @param wb
     * @param title 标题
     * @param sheet 页
     * @param num 合并单元格的数量
     * @return
     */
    public static HSSFSheet createTitle(HSSFWorkbook wb, String title, HSSFSheet sheet, int num) {
        HSSFRow row = sheet.createRow(0);//创建行
        //<<<<<<<<<<<<<<<<
        HSSFFont font = wb.createFont();//设置字体
        font.setColor(HSSFColor.BLACK.index);//颜色
        font.setFontHeightInPoints((short) 23);//字体大小
        font.setBold(true); //字体加粗
        font.setFontName("微软雅黑");
        //<<<<<<<<<<<<<<<<
        ExportExcelUtils.createCell(wb, row, 0, TITlE, HorizontalAlignment.CENTER,
                VerticalAlignment.CENTER, title, WHITE, font);//插入标题
        row.setHeightInPoints(titleHeight);//设置行高
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, num));
        return sheet;
    }

    /**
     * 创建条线类别一栏
     * @author xuye
     * Date: 2016年11月8日 上午10:09:43
     * @param wb
     * @param sheet 页
     * @param linetype 条线
     * @param regions 服务区域
     * @param services 服务频次
     * @param providers 负责方
     * @param num 合并数量
     * @return
     */
    public static HSSFSheet createLineType(HSSFWorkbook wb, HSSFSheet sheet, String linetype, int num,
            String regions, String services, String providers) {
        String[] str = { "服务区域：", "服务频次：", "负责方：" };
        String[] str2 = { regions, services, providers };
        //<<<<<<<<<<<<<<<<
        HSSFFont lineTypeFont = wb.createFont();//条线字体
        lineTypeFont.setColor(HSSFColor.RED.index);
        lineTypeFont.setFontHeightInPoints((short) 17);
        lineTypeFont.setBold(true);
        lineTypeFont.setFontName("微软雅黑");
        //<<<<<<<<<<<<<<<<
        HSSFFont systemFont = wb.createFont();//条线字体
        systemFont.setColor(HSSFColor.BLACK.index);
        systemFont.setFontHeightInPoints((short) 11);
        systemFont.setBold(true);
        systemFont.setFontName("微软雅黑");
        //<<<<<<<<<<<<<<<<
        for (int i = 1; i < 4; i++) {//循环创建第2行到第4行
            HSSFRow row = sheet.createRow(i);
            row.setHeightInPoints(lineTypeHeight);//设置行高
            if (i == 1) {
                ExportExcelUtils.createCell(wb, row, 0, LINETYPE, HorizontalAlignment.CENTER,
                        VerticalAlignment.CENTER, linetype, WHITE, lineTypeFont);//插入条线
            } else {
                ExportExcelUtils.createCell(wb, row, 0, LINETYPE, HorizontalAlignment.CENTER,
                        VerticalAlignment.CENTER, "", WHITE, lineTypeFont);//用于条线合并单元格
            }
            ExportExcelUtils.createCell(wb, row, 1, SYSTEM, HorizontalAlignment.CENTER,
                    VerticalAlignment.CENTER, str[i - 1], WHITE, systemFont);//插入服务区域等.
            //ExportExcelUtils.createCell(wb, row, 2, SYSTEM, HorizontalAlignment.CENTER,
            //VerticalAlignment.CENTER, "", WHITE, systemFont);//用于服务区域的单元合并
            //sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));//合并服务区域单元格
            ExportExcelUtils.createCell(wb, row, 2, SYSTEM, HorizontalAlignment.LEFT,
                    VerticalAlignment.CENTER, str2[i - 1], WHITE, systemFont);//插入相应的值
            for (int f = 3; f < num + 1; f++) {
                ExportExcelUtils.createCell(wb, row, f, SYSTEM, HorizontalAlignment.LEFT,
                        VerticalAlignment.CENTER, "", WHITE, systemFont);//插入相应的值
            }
            sheet.addMergedRegion(new CellRangeAddress(i, i, 2, num));

        }
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));//合并条线单元格
        return sheet;

    }

    /**
     * 创建系统月份这一栏
     * @author xuye
     * Date: 2016年11月8日 下午1:24:33
     * @param wb
     * @param sheet 页
     * @param date 月份集合
     * @return
     */
    public static HSSFSheet createYearMonth(HSSFWorkbook wb, HSSFSheet sheet, List