java生成excl工具类

package cn.inspower.elesituation.handler;

import cn.inspower.dto.MarkListDTO;
import cn.inspower.monitor.data.dto.HistoryDataDTO;
import cn.inspower.util.ListUtil;
import framework.common.exception.FrameworkException;
import framework.common.tools.map.ExtHashMap;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 电量报表导出抽象类
 */
public abstract class AbstractExcelExporter {

    //excel表对象
    protected Workbook workbook;
    //工作表对象
    protected Sheet sheet;
    //标题
    protected String title;
    //数据起始行数
    protected Integer dataStartRowNum;
    //各个列的元素key值
    protected List<String> dataIndexs;
    //sheet需要填充的数据信息
    protected List<Map> data;
    //工作表列宽
    protected Integer columnWidth = 15;
    //工作表默认行高
    protected Integer rowHeight = 15;
    //工作表默认行高
    protected Integer titleRowHeight = 20;
    //标题字体大小
    protected Short titleFontSize = 16;
    //正文字体大小
    protected Short bodyFontSize = 11;
    //文件名
    protected String fileName;

    public AbstractExcelExporter() {
        this.dataIndexs = new ArrayList<>();
        this.data = new ArrayList<>();
        this.workbook = new XSSFWorkbook();
        this.sheet = workbook.createSheet("数据报表");
    }

    /**
     * 初始化方法
     *
     * @param dataList
     * @throws FrameworkException
     */
    protected void init(List<HistoryDataDTO> dataList, Date startTime, Date endTime, Integer dataStartRowNum) throws FrameworkException {
        this.dataStartRowNum = dataStartRowNum;
        this.processingData(dataList, startTime, endTime).writeTitle().writeTableHead().writeMainData();
    }

    /**
     * 处理数据
     *
     * @param dataList
     * @return
     */
    protected abstract AbstractExcelExporter processingData(List<HistoryDataDTO> dataList, Date startTime, Date endTime) throws FrameworkException;

    /**
     * @return *
     * @Description 添加表头
     * @Param headRowNum: 添加表头所在行数
     */
    protected abstract AbstractExcelExporter writeTableHead();

    /**
     * @return *
     * @Description 插入数据到表格（body）
     * @Param startRow: 开始插入数据的行
     */
    protected abstract AbstractExcelExporter writeMainData();

    /**
     * 返回给浏览器下载
     *
     * @param response
     * @return
     * @throws FrameworkException
     */
    public AbstractExcelExporter export(HttpServletResponse response) throws FrameworkException {
        //导出数据
        OutputStream out = null;
        try {
            //设置Http响应头告诉浏览器下载这个附件
            response.setHeader("Content-Disposition", "attachment;Filename=" + URLEncoder.encode(fileName, "utf-8") + ".xlsx");
            response.setHeader("FileName", URLEncoder.encode(fileName, "utf-8") + ".xlsx");
            response.setHeader("Access-Control-Expose-Headers", "FileName");
            response.setHeader("Transfer-Encoding", "utf-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new FrameworkException("导出Excel异常，异常信息：" + ex.getMessage());
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return this;
    }

    /**
     * 保存到文件
     *
     * @param filePath
     * @throws IOException
     * @throws FrameworkException
     */
    public void saveFile(String filePath) throws FrameworkException {
        //导出数据
        OutputStream out = null;
        try {
            File file = new File(filePath + fileName + ".xlsx");
            out = new FileOutputStream(file);
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new FrameworkException("保存Excel异常，异常信息：" + ex.getMessage());
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * @return
     * @Author
     * @Description 添加表格标题
     * @Param
     */
    protected AbstractExcelExporter writeTitle() throws FrameworkException {
        checkConfig();
        //设置默认行宽
        this.sheet.setDefaultColumnWidth(this.columnWidth);
        this.sheet.setDefaultRowHeight(Short.valueOf(rowHeight * 20 + ""));
        //在第0行创建rows  (表标题)
        Row title = this.sheet.createRow(0);
        title.setHeightInPoints(titleRowHeight);//行高
        CellStyle cellStyle = getTitleStyle();
        createTextCell(title, 0, this.title, cellStyle);
        mergeCell(sheet, 0, 0, 0, this.dataIndexs.size() - 1);
        return this;
    }

    /**
     * 生成文本类单元格
     *
     * @param row
     * @param index
     * @param value
     * @param style
     * @return
     */
    protected Cell createTextCell(Row row, int index, Object value, CellStyle style) {
        Cell cell = row.createCell(index);
        if (style != null) {
            cell.setCellStyle(style);
        }
        XSSFRichTextString cellValue = new XSSFRichTextString(value == null ? "-" : value.toString());
        cell.setCellValue(cellValue);
        return cell;
    }

    /**
     * 生成数字类单元格
     *
     * @param row
     * @param index
     * @param value
     * @param style
     * @return
     */
    protected Cell createNumberCell(Row row, int index, double value,
                                    CellStyle style, boolean haveDecimal) {
        Cell cell = row.createCell(index);
        if (style == null) {
            style = this.workbook.createCellStyle();
        }
        if (haveDecimal) {
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        }
        cell.setCellStyle(style);
        String str = new DecimalFormat("0.00").format(value);
//        XSSFRichTextString cellValue = new XSSFRichTextString(value == 0 ? "-" : str);
        cell.setCellValue(value);
        return cell;
    }


    /**
     * 检查数据配置问题
     *
     * @throws FrameworkException 抛出数据异常类
     */
    protected void checkConfig() throws FrameworkException {
        if (ListUtil.isEmpty(dataIndexs)) {
            throw new FrameworkException("", "列名数组不能为空或者为NULL");
        }
        if (ListUtil.isEmpty(data)) {
            throw new FrameworkException("", "数据为空");
        }
    }

    /**
     * @return *
     * @Description 标题样式 :加粗，垂直居中
     * @Param
     */
    protected CellStyle getTitleStyle() {
        // 标题样式（加粗，垂直居中）
        CellStyle cellStyle = workbook.createCellStyle();
        center(cellStyle);
        Font font = setFontSize(titleFontSize);//设置标题字体大小
        font.setBold(true);   //加粗
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * @return *
     * @Description 标题样式 :加粗，垂直居中
     * @Param
     */
    protected CellStyle getDefaultHeaderStyle() {
        return getHeaderStyle(null);
    }

    /**
     * @return *
     * @Description 标题样式 :加粗，垂直居中,颜色
     * @Param
     */
    protected CellStyle getHeaderStyle(java.awt.Color color) {
        // 标题样式（加粗，垂直居中）
        CellStyle cellStyle = workbook.createCellStyle();
        center(cellStyle);
        Font font = setFontSize(bodyFontSize);
        font.setBold(true);   //加粗
        if (color != null) {
            ((XSSFCellStyle) cellStyle).setFillForegroundColor(new XSSFColor(color));
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * @Author
     * @Description 样式居中
     * @Param
     */
    protected void center(CellStyle cellStyle) {
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
    }


    /**
     * @return * @param null
     * @Author
     * @Description 单元格合并
     * @Param wbSheet :工作表对象 firstRow ：合并的开始行 lastRow：合并的结束行 firstCol: 合并的开始列 lastColL: 合并的结束列
     */
    protected void mergeCell(Sheet wbSheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        wbSheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * @return * @param null
     * @Author
     * @Description body通用样式: 居中，设置字体大小
     * @Param
     */
    protected CellStyle getBodyDefaultStyle() {
        return getBodyStyle(null);
    }

    /**
     * @return * @param null
     * @Author
     * @Description 带颜色的body样式: 居中，设置字体大小
     * @Param
     */
    protected CellStyle getBodyStyle(java.awt.Color color) {
        CellStyle cellStyle = workbook.createCellStyle();
        //设置单元格样式
        center(cellStyle);
        Font font = setFontSize(bodyFontSize);
        cellStyle.setFont(font);
        if (color != null) {
            ((XSSFCellStyle) cellStyle).setFillForegroundColor(new XSSFColor(color));
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        return cellStyle;
    }

    protected Font setFontSize(int fontSize) {
        //设置字体
        Font font = workbook.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) fontSize);
        return font;
    }
}