导出Excel功能的简单封装

最新推荐文章于 2022-09-02 16:20:13 发布

套码汉子

最新推荐文章于 2022-09-02 16:20:13 发布

阅读量200

点赞数

分类专栏： Java 文章标签： poi导出 Excel工具类 Excel封装封装poi

本文链接：https://blog.csdn.net/weixin_40636906/article/details/88816884

版权

Java 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

表格实体类：

/**
 * @Description：Excel表格实体
 * @Author：wengjunhe
 * @Date： 2019/03/26
 */
public class ExcelCellBean {
    /**
     * 表头
     */
    private String title;

    /**
     * 对应字段
     */
    private String field;

    public String getTitle() {
        return title;
    }

    public String getField() {
        return field;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public void setField(String field) {
        this.field = field;
    }
}

工具类：

**
 * @Description：Excel导出工具类
 * @Author：wengjunhe
 * @Date： 2019/03/26
 */
public class ExcelUtils {
    public static HSSFWorkbook get(List<ExcelCellBean> excelCellBeanList, List<Map<String, Object>> resultList) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow header = sheet.createRow(0);
        //时间格式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        cellStyle.setDataFormat(format.getFormat("yyyy年MM月dd日 HH:mm:ss"));

        List<String> titles = getTitles(excelCellBeanList);
        //设置表头
        for (int i = 0; i < titles.size(); i++) {
            HSSFCell cell = header.createCell(i);
            cell.setCellValue(titles.get(i));
        }
        //设置行数据
        for (int i = 0; i < resultList.size(); i++) {
            HSSFRow row = sheet.createRow(i + 1);
            Map<String, Object> map = resultList.get(i);
            for (int j = 0; j < excelCellBeanList.size(); j++) {
                HSSFCell cell = row.createCell(j);
                ExcelCellBean excelCellBean = excelCellBeanList.get(j);
                Object cellValue = map.get(excelCellBean.getField());
                if (null == cellValue) {
                    cell.setCellValue("无");
                } else if (cellValue instanceof Short) {
                    cell.setCellValue(Short.parseShort(String.valueOf(cellValue)));
                } else if (cellValue instanceof Byte) {
                    cell.setCellValue(Byte.parseByte(String.valueOf(cellValue)));
                } else if (cellValue instanceof Integer) {
                    cell.setCellValue(Integer.parseInt(String.valueOf(cellValue)));
                } else if (cellValue instanceof Long) {
                    cell.setCellValue(Long.parseLong(String.valueOf(cellValue)));
                } else if (cellValue instanceof Float) {
                    cell.setCellValue(Float.parseFloat(String.valueOf(cellValue)));
                } else if (cellValue instanceof Double) {
                    cell.setCellValue(Double.parseDouble(String.valueOf(cellValue)));
                } else if (cellValue instanceof BigDecimal) {
                    cell.setCellValue(Double.parseDouble(String.valueOf(cellValue)));
                } else if (cellValue instanceof String) {
                    cell.setCellValue(String.valueOf(cellValue));
                } else if (cellValue instanceof Timestamp) {
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(String.valueOf(cellValue));
                } else {
                    cell.setCellValue(String.valueOf(cellValue));
                }
            }
        }
        return workbook;
    }

 /**
     * @Description: 导出Excel
     * @Param: [headers, fields, resultList]
     * @return: org.apache.poi.hssf.usermodel.HSSFWorkbook
     */
    public static HSSFWorkbook get(String[] headers, String[] fields, List<Map<String, Object>> resultList) {
        List<ExcelCellBean> excelCellBeans = new ArrayList<>();
        for (int i = 0; i < headers.length; i++) {
            ExcelCellBean excelCellBean = new ExcelCellBean();
            excelCellBean.setTitle(headers[i]);
            excelCellBean.setField(fields[i]);
            excelCellBeans.add(excelCellBean);
        }
        return get(excelCellBeans, resultList);
    }

    /**
     * @Description: 获取表头
     * @Param: [excelCellBeanList]
     * @return: java.util.List<java.lang.String>
     */
    public static List<String> getTitles(List<ExcelCellBean> excelCellBeanList) {
        ArrayList<String> titles = new ArrayList<>();
        for (ExcelCellBean excelCellBean : excelCellBeanList) {
            titles.add(excelCellBean.getTitle());
        }
        return titles;
    }
}

调用：

 private HSSFWorkbook getHSSFWorkbook(List<Map<String, Object>> productList) {
        String[] headers = {"商品ID","商品名称", "商家名称", "一级类目","二级类目","是否共享",
                "累计销量", "最高日销量", "最低日销量", "当日销量", "价格", "佣金",
                "近七日销量", "价格变动区间", "当日均价", "近七天均价", "历史均价",
                "创建日期", "更新日期", "监控版本"};
        //field
        String[] fields = {"productId","productName", "sellerName", "channel","cateName","isShare",
                "solds", "topSolds", "lowSolds", "daySolds", "price", "commissionAmount",
                "weekAverageSolds", "priceSection", "dayAveragePrice", "sevenAveragePrice", "historyAveragePrice",
                "createTime", "updateTime", "monitorVersion"};
        HSSFWorkbook workbook = ExcelUtils.get(headers, fields, productList);
        return workbook;
    }

结果