使用Java类库POI生成简易的Excel报表

使用Java类库POI生成简易的Excel报表

1.需求

1.数据库生成报表需要转义其中字段的信息。比如 1,有效 2.无效等

2.日期格式的自数据需要转义其格式。

3.标题的格式和数据的格式需要分别设置

4.可能出现的实体类嵌套实体类的情况

5.需要在行尾添加统计数据

2.具体实现

首先创建一个用于存放标题、反射方法、转义列表、统计的辅助实体对象

import java.util.Map;

/**
 * 列开头信息和结尾信息
 *
 * @author yanlong 2018年03月06日09:36:18
 *
 *
 */
public class BaseInf {
    /*
     * 标题
     */
    private String titleName;
    /*
     * 行读取方法
     */
    private String columMethod;
    /*
     * 转义列表
     */
    private Map<String, String> map;
    /*
     * 行尾统计
     */
    private String count;


    /**
     * 有转义列表 有统计的构造方法
     * @param titleName 标题
     * @param columMethod 获取方法
     * @param map 转义列表
     * @param count 统计
     */
    public BaseInf(String titleName, String columMethod, Map<String, String> map, String count) {
        super();
        this.titleName = titleName;
        this.columMethod = columMethod;
        this.map = map;
        this.count = count;
    }

    /**
     * 有转义列表构造方法
     *
     * @param titleName 标题
     * @param columMethod 获取方法
     * @param map 转义列表
     */
    public BaseInf(String titleName, String columMethod, Map<String, String> map) {
        super();
        this.titleName = titleName;
        this.columMethod = columMethod;
        this.map = map;
        this.count = null;
    }

    /**
     * 无转义列表 有合计数据
     * @param titleName 标题
     * @param columMethod 读取方法列表
     * @param count 合计值
     */
    public BaseInf(String titleName, String columMethod, String count) {
        super();
        this.titleName = titleName;
        this.columMethod = columMethod;
        this.map = null;
        this.count = count;
    }

    /**
     * 无转义列表无统计构造方法
     *
     * @param titleName
     *            标题
     * @param columMethod
     *            获取方法
     */
    public BaseInf(String titleName, String columMethod) {
        super();
        this.titleName = titleName;
        this.columMethod = columMethod;
        this.map = null;
        this.count = null;
    }

    public String getTitleName() {
        return titleName;
    }

    public String getColumMethod() {
        return columMethod;
    }

    public Map<String, String> getMap() {
        return map;
    }

    public String getCount() {
        return count;
    }
}

具体的创建过程将创建表单和传输方式分开 以便于可以本地下载或者服务器下载

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
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.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
    // 私有构造方法禁止new实例
    private ExcelUtil() {
    }

    // 日志工具
    // private static final Logger logger =
    // LoggerFactory.getLogger(ExcelUtil.class);
    // 默认日期格式
    private static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日 hh点mm分ss秒";
    // 默认行高
    private static final Short DEFAULT_COLOUMN_HEIGHT = 400;

    private static final Short DEFAULT_COLOUMN_WEIGHT = 170;

    /**
     * 将工作表输出到浏览器中
     *
     * @param response
     *            响应流
     * @param workbook
     *            创建完成的工作表
     * @param fileName
     *            文件名
     * @param sufferNm
     *            文件后缀名
     * @throws Exception
     */
    public static void workbook2InputStream(HttpServletResponse response, Workbook workbook, String fileName,
            String sufferNm) throws Exception {
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment; filename=" + new String((fileName).getBytes("gb2312"), "ISO8859-1") + sufferNm);
        // 设置下载头信息
        response.setContentType("application nd.ms-excel; charset=utf-8");
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
    }

    /**
     * 实体类单层嵌套
     */
    public static Workbook createWorkbook(int version, String sheetNm, List<BaseInf> baseInfList, List<?> list)
            throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException,
            InvocationTargetException {
        return createWorkbook(version, sheetNm, baseInfList, list, null);
    }

    /**
     * 创建一个数据表 实体类嵌套实体类
     *
     * @param version
     *            excel版本 2007 或者其他
     * @param sheetNm
     *            sheet 名称
     * @param baseInfList
     *            数据基础信息
     * @param list
     *            数据
     * @param innerMethod
     *            实体类多层嵌套
     *
     * @see BaseInf
     *
     * @return 构建完成的数据表对象
     *
     * @throws SecurityException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static Workbook createWorkbook(int version, String sheetNm, List<BaseInf> baseInfList, List<?> list,
            String innerMethod) throws NoSuchMethodException, SecurityException, IllegalAccessException,
            IllegalArgumentException, InvocationTargetException {
        SimpleDateFormat sdf = new SimpleDateFormat(DEFAULT_DATE_PATTERN);
        Workbook workbook = null;
        if (version == 2007) {
            workbook = new XSSFWorkbook();
        } else {
            workbook = new HSSFWorkbook();
        }
        Sheet sheet = workbook.createSheet(isEmpty(sheetNm) ? "sheet1" : sheetNm);
        // 写入标题
        CellStyle titleStyle = titleStyle(workbook);
        // 创建标题行(第一行)
        Row titleRow = sheet.createRow(0);
        // 设置第一行的行高
        titleRow.setHeight(DEFAULT_COLOUMN_HEIGHT);
        Cell cell = null;
        // 设置序号
        sheet.setColumnWidth(0, DEFAULT_COLOUMN_WEIGHT);
        cell = titleRow.createCell(0);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue("序号");
        cell.setCellStyle(titleStyle);
        // 其他标题
        for (int i = 0; i < baseInfList.size(); i++) {
            String titleName = baseInfList.get(i).getTitleName();
            // 设置单元格的宽
            sheet.setColumnWidth(i, titleName.length() * 1500);
            cell = titleRow.createCell(i + 1);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(titleName);
            cell.setCellStyle(titleStyle);
        }
        /**
         * 写入数据
         *
         * 写入数据按照先行 后列的的方式进行
         *
         */
        CellStyle dataStyle = dataStyle(workbook);
        Row dataRow = null;
        for (int i = 0; i < list.size(); i++) {
            // 创建行
            dataRow = sheet.createRow(i + 1);
            // 创建列 此处为序号列
            cell = dataRow.createCell(0);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(i + 1);
            cell.setCellStyle(titleStyle);
            // 序号列创建完毕 开始创建数据列
            for (int j = 0; j < baseInfList.size(); j++) {
                // 创建数据列
                cell = dataRow.createCell(j + 1);
                BaseInf baseInf = baseInfList.get(j);
                // 设值
                Method method;
                Object value;
                if (innerMethod != null) {
                    method = list.get(i).getClass().getMethod(innerMethod);
                    Object obj = method.invoke(list.get(i));
                    method = obj.getClass().getMethod(baseInf.getColumMethod());
                    value = method.invoke(obj);
                } else {
                    method = list.get(i).getClass().getMethod(baseInf.getColumMethod());
                    value = method.invoke(list.get(i));
                }
                String returnType = method.getReturnType().getName().toLowerCase();
                cell.setCellStyle(dataStyle);
                // 转义列表
                Map<String, String> transMap = baseInf.getMap();
                // 判断是否需要转义
                if (transMap == null) {
                    if (returnType.indexOf("string") != -1) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(value == null ? "" : value.toString());
                    } else if (returnType.indexOf("integer") != -1 || returnType.indexOf("int") != -1
                            || returnType.indexOf("bigdecimal") != -1 || returnType.indexOf("double") != -1
                            || returnType.indexOf("long") != -1 || returnType.indexOf("float") != -1) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(value == null ? null : new Double(value.toString()));
                    } else if (returnType.indexOf("date") != -1) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(value == null ? null : sdf.format((Date) value));
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(value == null ? "" : value.toString());
                    }
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = value == null ? "" : transMap.get(tse(value.toString()));
                    cell.setCellValue(cellValue == null ? tse(value.toString()) : cellValue);
                }
            }
        }
        // 创建统计行
        // 创建行
        dataRow = sheet.createRow(list.size() + 1);
        // 创建列 此处为序号列
        cell = dataRow.createCell(0);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue("统计");
        cell.setCellStyle(titleStyle);
        for (int i = 0; i < baseInfList.size(); i++) {
            BaseInf baseInf = baseInfList.get(i);
            cell = dataRow.createCell(i + 1);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellStyle(dataStyle);
            if (baseInf.getCount() != null) {
                cell.setCellValue(baseInf.getCount());
            } else {
                cell.setCellValue("");
            }
        }
        return workbook;
    }

    /**
     *
     * 删除字符串内的回车 空格和两端空白
     *
     * @author yanlong 2017-7-5
     *
     */
    private static String tse(String str) {
        return str == null ? "" : str.replace(" ", "").replace("/r", "").replace("/n", "").trim();
    }

    // 判断非空
    private static boolean isEmpty(String str) {
        return str == null || "".equals(str.trim());
    }

    /**
     * 设置标题样式
     *
     * @param workbook 工作表
     * @return 标题样式
     */
    private static CellStyle titleStyle(Workbook workbook) {
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); // 居中
        titleStyle.setBorderLeft((short) 1);
        titleStyle.setBorderRight((short) 1);
        titleStyle.setBorderBottom((short) 1);
        titleStyle.setBorderTop((short) 1);
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);// 设置字体大小
        titleStyle.setFont(font);// 选择需要用到的字体格式
        return titleStyle;
    }

    /**
     * 数据样式
     *
     * @param workbook 工作表
     * @return 数据样式
     */
    private static CellStyle dataStyle(Workbook workbook) {
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setBorderBottom((short) 1);
        dataStyle.setBorderLeft((short) 1);
        dataStyle.setBorderRight((short) 1);
        dataStyle.setBorderTop((short) 1);
        dataStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        return dataStyle;
    }
}

调用方式

//下载文件
List<BaseInf> baseInfList = new ArrayList<BaseInf>();
BaseInf baseInf = new BaseInf("商户编号","getTACCOUNTID");
baseInfList.add(baseInf);
baseInf = new BaseInf("商户名称","getMERNAME");
baseInfList.add(baseInf);
baseInf = new BaseInf("商户类型","getMERTYPE",Tmerinfo.merTypeTransMap());
baseInfList.add(baseInf);
baseInf = new BaseInf("商户状态","getSTATE",Tmerinfo.statesTransMap());
baseInfList.add(baseInf);
baseInf = new BaseInf("商户分级","getMERLEVEL",Tmerinfo.merlevelTransMap(ms));
baseInfList.add(baseInf);
baseInf = new BaseInf("企业全称","getFULLNAME");
baseInfList.add(baseInf);
baseInf = new BaseInf("创建时间","getOPENTIME");
baseInfList.add(baseInf);
baseInf = new BaseInf("开通时间","getREGTIME");
baseInfList.add(baseInf);
Workbook workbook = ExcelUtil.createWorkbook(2007, "商户管理-商户查询", baseInfList, mercertinfo,"getTmerinfoDTO");
ExcelUtil.workbook2InputStream(response, workbook, "userQuery", ".xls");

转载于:https://www.cnblogs.com/yanlong300/p/8582022.html