POI Excel转换工具

场景：将对象转为json数据，再根据json生成excel表格和html表格

代码：[亲点下文章最下面公众号关注 回复 表格导出 获取源码]

效果展示

使用

    public static void template() throws IOException {
        // 任意对象
        OrderDTO orderDTO = new OrderDTO("54320001","已过期");
        // 创建模板set表头
        TableTemplateImpl tableTemplate = new TableTemplateImpl();
        tableTemplate.setTableHead("单号","状态");
        TableResult4Template<TableTemplateImpl> tableResult =
                TableResult4Template.builder()
                        .template(tableTemplate)
                        // set对象的属性，对应表格的内容
                        .items(orderDTO.getOrderCode(),orderDTO.getStatus())
                        .build();
        String toolResult = tableResult.toJson();
        // 表格头及数据
        StringBuffer sBuffer = new StringBuffer();
        List<String> tableHead = dealTableHead2(toolResult);
        dealTableData2(toolResult,sBuffer);
        // 导出成excel
        String filePath = "D:\\模板设计模式" + System.currentTimeMillis()+".xls";
        export(tableHead,sBuffer,filePath);

        // 导出成html table
        String htmltable = convertTaskResultToHtmlTable(tableHead,sBuffer);
        System.out.println(htmltable);
    }

结果
控制台

单号	状态
54320001	已过期

<table border='1' cellspacing='0' cellpadding='3'  align='center'><tr><th>单号</th><th>状态</th></tr><tr><td>54320001</td><td>已过期</td></tr></table>

filepath excel表格

​ 模板设计模式1656141582396.xls

实现

依赖

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.2</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>19.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.68</version>
        </dependency>

code

主类

package com.wwt.example.indepartment.poi;

/**
 * @author wwt
 * @title: Object2Table
 * @description:
 * 只要将对象或者列表传进去，构造TableResultUtil对象
 * 就可以将结果处理为excel表格和html表格
 * @date 2022/6/25 13:53
 */

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Splitter;
import com.google.common.collect.Lists;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;


public class Object2Table {

    public static void main(String[] args) throws IOException {
        template();
        build();
    }

    /**
     * 模板模式
     **/
    public static void template() throws IOException {
        // 任意对象
        OrderDTO orderDTO = new OrderDTO("54320001","已过期");
        // 创建模板set表头
        TableTemplateImpl tableTemplate = new TableTemplateImpl();
        tableTemplate.setTableHead("单号","状态");
        TableResult4Template<TableTemplateImpl> tableResult =
                TableResult4Template.builder()
                        .template(tableTemplate)
                        // set对象的属性，对应表格的内容
                        .items(orderDTO.getOrderCode(),orderDTO.getStatus())
                        .build();
        String toolResult = tableResult.toJson();
        // 表格头及数据
        StringBuffer sBuffer = new StringBuffer();
        List<String> tableHead = dealTableHead2(toolResult);
        dealTableData2(toolResult,sBuffer);
        // 导出成excel
        String filePath = "D:\\模板设计模式" + System.currentTimeMillis()+".xls";
        export(tableHead,sBuffer,filePath);

        // 导出成html table
        String htmltable = convertTaskResultToHtmlTable(tableHead,sBuffer);
        System.out.println(htmltable);
    }

    /**
     * 建造者模式
     **/
    public static void build() throws IOException {
        //对象列表，支持泛型，单个对象和list都可以传
        List<OrderDTO> list = Lists.newArrayList();
        list.add(new OrderDTO("12345","创建中"));
        OrderDTO orderDTO = new OrderDTO("12346", "销毁");
        // 不传表格表头名为字段名，字段别名map可以将字段名替换成自定义的名称
        HashMap<String,String> tableHeadDisplayMap = new HashMap();
        tableHeadDisplayMap.put("orderCode","单号");

        // 建造json对象
        TableResultUtil<OrderDTO> tableResultUtil = new TableResultUtil<OrderDTO>();
        TableResult4Build<OrderDTO> tableResult=TableResult4Build.builder().tableHead(tableHeadDisplayMap)
                .item(orderDTO)
                .items(list).build();
        String jsonData = tableResultUtil.createTableResult(tableResult);

        // 根据对象生成json数据
        String toolResult = jsonData;

        // 表格头及数据
        StringBuffer sBuffer = new StringBuffer();
        List<String> tableHead = dealTableHead(toolResult);
        dealTableData(toolResult,sBuffer);

        // 导出成excel
        String filePath = "D:\\建造者设计模式" + System.currentTimeMillis()+".xls";
        export(tableHead,sBuffer,filePath);

        // 导出成html table
        String htmltable = convertTaskResultToHtmlTable(tableHead,sBuffer);
        System.out.println(htmltable);
    }

    /**
     * 导出服务
     * @return
     */
    private static HSSFWorkbook export(List<String> tableHeadDisPlayFiels,StringBuffer sBuffer, String filePath) throws IOException {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("result000");
        HSSFRow hssfRow = hssfSheet.createRow(0);
        HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
        HSSFDataFormat format = hssfWorkbook.createDataFormat();
        hssfCellStyle.setDataFormat(format.getFormat("@"));


        //填入表头
        int i = 0;
        for (String entry : tableHeadDisPlayFiels) {
            HSSFCell hssfCell = hssfRow.createCell(i);
            hssfCell.setCellValue(entry);
            hssfCell.setCellStyle(hssfCellStyle);
            i++;
        }

        //填入表内数据
        //第一行是表头，第二行开始填写数据
        List<String> resultList = Splitter.on("\n").trimResults().omitEmptyStrings().splitToList(sBuffer);
        if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(resultList)){
            for (int j = 0; j < resultList.size(); j++) {
                hssfRow = hssfSheet.createRow(j+1);
                List<String> fieldList = Splitter.on(",").trimResults().omitEmptyStrings().splitToList(resultList.get(j));
                if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(fieldList)) {
                    for (int k = 0; k < fieldList.size(); k++) {
                        HSSFCell hssfCell = hssfRow.createCell(k);
                        hssfCell.setCellStyle(hssfCellStyle);
                        hssfCell.setCellValue(String.valueOf(fieldList.get(k)));
                    }
                }
            }
        }
        FileOutputStream out = new FileOutputStream(filePath);
        //保存Excel文件
        hssfWorkbook.write(out);
        //关闭文件流
        out.close();
        return hssfWorkbook;

    }

    public static StringBuffer arrayIdToString2(List<String> tableHead, StringBuffer sBuffer) {
        for (String field: tableHead) {
            sBuffer.append(field).append(",");
        }
        // 这里写全局变量，和框架内容保持对齐
        return sBuffer.append("\n");
    }

    public static StringBuffer arrayIdToString(JSONObject jsonobejct, List<String> tableHead,
                                               StringBuffer sBuffer) {
        for (String field: tableHead) {
            sBuffer.append(jsonobejct.get(field)).append(",");
        }
        // 这里写全局变量，和框架内容保持对齐
        return sBuffer.append("\n");
    }

    public static List<String> dealTableHead(String toolResult) {
        JSONObject tableResultJson = JSONObject.parseObject(toolResult);
        JSONArray tableHeadDisplayArr = tableResultJson.getJSONArray("tableHeadDisplay");
        List<String> tableHeadDisPlayFiels = tableHeadDisplayArr.toJavaList(String.class);
        return tableHeadDisPlayFiels;
    }
    public static List<String> dealTableHead2(String toolResult) {
        JSONObject tableResultJson = JSONObject.parseObject(toolResult);
        JSONArray tableHeadArr = tableResultJson.<String>getJSONArray("tableHead");
        tableHeadArr.toJavaList(String.class);
        List<String> tableHeadDisPlayFiels = tableHeadArr.toJavaList(String.class);
        return tableHeadDisPlayFiels;
    }

    public static void dealTableData(String toolResult, StringBuffer sBuffer){
        JSONObject tableResultJson = JSONObject.parseObject(toolResult);
        JSONArray tableData = tableResultJson.getJSONArray("tableData");
        JSONArray tableHeadArr = tableResultJson.getJSONArray("tableHead");
        List<String> tableHeadFiels = tableHeadArr.toJavaList(String.class);
        tableData.stream().forEach(jsonobejct -> arrayIdToString((JSONObject) jsonobejct, tableHeadFiels, sBuffer));
    }

    public static void dealTableData2(String toolResult, StringBuffer sBuffer){
        JSONObject tableResultJson = JSONObject.parseObject(toolResult);
        JSONArray tableDataArr = tableResultJson.getJSONArray("tableData");
        List<String> tableDataFiels = tableDataArr.toJavaList(String.class);
        arrayIdToString2( tableDataFiels, sBuffer);
    }

    public static String convertTaskResultToHtmlTable(List<String> tableHeadDisPlayFiels,StringBuffer sBuffer){


        StringBuffer sb = new StringBuffer();
        sb.append("<table border='1' cellspacing='0' cellpadding='3'  align='center'><tr>");
        // 获取显示字段名称，并转换成html格式
        if (CollectionUtils.isNotEmpty(tableHeadDisPlayFiels)) {
            tableHeadDisPlayFiels.forEach(tableHeadFiel -> {
                sb.append("<th>");
                sb.append(tableHeadFiel);
                sb.append("</th>");
            });
        }
        sb.append("</tr>");

        // 转换值为table格式
        List<String> resultList = Splitter.on("\n").trimResults().omitEmptyStrings()
                .splitToList(sBuffer);

        if (CollectionUtils.isNotEmpty(resultList)) {
            resultList.forEach(r -> {
                sb.append("<tr>");
                List<String> fieldList = Splitter.on(",").trimResults().omitEmptyStrings().splitToList(r);
                if (CollectionUtils.isNotEmpty(fieldList)) {
                    fieldList.forEach(field -> {
                        sb.append("<td>");
                        sb.append(field);
                        sb.append("</td>");
                    });
                }
                sb.append("</tr>");
            });
        }
        sb.append("</table>");
        return sb.toString();
    }
}

工具类

package com.wwt.example.indepartment.poi;

/**
 * @author wwt
 * @title: TableResultUtil
 * @description: TODO
 * @date 2022/6/25 14:10
 */

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;

import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;

public class TableResultUtil<E> {

    public String createTableResult(TableResult4Build<E> tableResult) {
        Map<String, String> tableHeadMap = tableResult.getTableHead();
        List<E> list = tableResult.getTableRows();

        JSONObject resultObjWhole = new JSONObject();
        List<String> tableHead = createTableHead(list);
        List<String> tableHeadDisplay = Lists.newArrayList();
        tableHeadDisplay.addAll(tableHead);
        if (!org.springframework.util.CollectionUtils.isEmpty(tableHeadMap)) {
            for (Map.Entry<String, String> map : tableHeadMap.entrySet()) {
                for (int i = 0; i < tableHeadDisplay.size(); i++) {
                    if (map.getKey().equals(tableHeadDisplay.get(i))) {
                        tableHeadDisplay.set(i, map.getValue());
                    }
                }

            }
        }
        if (org.springframework.util.CollectionUtils.isEmpty(list)) {
            throw new RuntimeException("表格结果构建结果为空");
        }

        resultObjWhole.put("tableHead", tableHead);
        resultObjWhole.put("tableHeadDisplay", tableHeadDisplay);
        resultObjWhole.put("tableData", list);
        return resultObjWhole.toJSONString();
    }

    /**
     * 对象所有字段名
     *
     * @return
     * @paramlist
     */
    private List<String> createTableHead(List<E> list) {
        List<String> tableHead = Lists.newArrayList();
        if (list == null || list.size() < 1) {
            throw new RuntimeException("对象tableHead参数构建失败，入参为空");
        }
        // 构造表头和数据
        Field[] fields = list.get(0).getClass().getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            tableHead.add(fields[i].getName());
        }
        return tableHead;
    }
}

实体类

@Data
public class OrderDTO {
    private String orderCode;
    private String status;

    public OrderDTO(String orderCode, String status) {
        this.orderCode = orderCode;
        this.status = status;
    }
}

模板结果类

public class TableResult4Build<T> {
    private List<T> tableRows;
    private Map<String, String> tableHead;

    public List<T> getTableRows() {
        return tableRows;
    }

    public Map<String, String> getTableHead() {
        return tableHead;
    }

    public TableResult4Build(Map<String, String> tableHead, List<T> tableRows) {
        this.tableHead = tableHead;
        this.tableRows = tableRows;
    }

    @Override
    public String toString() {
        return "TableResult{" +
                "tableRows=" + tableRows +
                ", tableHead=" + tableHead +
                '}';
    }

    public static TableResult4Build.TableResultBuilder builder() {
        return new TableResult4Build.TableResultBuilder();
    }

     static class TableResultBuilder<T> {

        private List<T> tableRows = new ArrayList<>();
        private Map<String, String> tableHead;
        private T item;
        private Map<String, String> tableHeadMap;

        TableResultBuilder() {
        }

        public TableResult4Build.TableResultBuilder item(T t) {
            this.tableRows.add(t);
            return this;
        }

        public TableResult4Build.TableResultBuilder items(List<T> items) {
            this.tableRows.addAll(items);
            return this;
        }

        public TableResult4Build.TableResultBuilder tableHead(Map<String, String> tableHeadMap) {
            this.tableHead = tableHeadMap;
            return this;
        }

        public TableResult4Build build() {
            return new TableResult4Build(this.tableHead, this.tableRows);
        }
    }
}

建造者结果类

package com.wwt.example.indepartment.poi;


import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;

import java.util.List;

public class TableResult4Template<T extends TableTemplate> {
    private T template;
    private List<String> bodyList;

    public T getTemplate() {
        return template;
    }

    public void setTemplate(T template) {
        this.template = template;
    }

    public void setBodyList(List<String> bodyList) {
        this.bodyList = bodyList;
    }

    public List<String> getBodyList() {
        return bodyList;
    }

    private TableResult4Template() {
    }

    public String toJson(){
        if (this.getTemplate() == null||this.getBodyList()==null) {
            throw new RuntimeException("模板内参数不允许为空");
        }

        if (this.getTemplate().getTableHead().size() != this.getBodyList().size()) {
            throw new RuntimeException("表头与内容参数长度不一致");
        }
        JSONObject resultObjWhole = new JSONObject();

        resultObjWhole.put("tableHead",this.getTemplate().getTableHead());
        resultObjWhole.put("tableData",this.getBodyList());
        return resultObjWhole.toJSONString();
    }


    public static TableResult4Template.TableResultBuilder builder() {
        return new TableResult4Template.TableResultBuilder();
    }

    public static class TableResultBuilder<T extends TableTemplate> {

        TableResult4Template tableResult2 = new TableResult4Template();
        private T template;
        private List<String> bodyList;


        TableResultBuilder() {
        }

        public TableResult4Template.TableResultBuilder template(T template) {
            this.template = template;
            return this;
        }

        public TableResult4Template.TableResultBuilder items(String... items) {
            this.bodyList = Lists.newArrayList(items);
            return this;
        }

        public TableResult4Template build() {
            tableResult2.setTemplate(this.template);
            tableResult2.setBodyList(this.bodyList);
            return tableResult2;
        }
    }
}

表格模板

interface TableTemplate<T extends TableTemplate> {
    void setTableHead(String... tableHead);
    List<String> getTableHead();
    abstract Object setTableStyle();
}

模板实现类

import com.google.common.collect.Lists;

import java.util.List;

public class TableTemplateImpl implements TableTemplate {

    private List<String> headList;

    public List<String> getHeadList() {
        return headList;
    }


    @Override
    public void setTableHead(String... tableHead) {
        headList = Lists.newArrayList(tableHead);
    }

    @Override
    public List<String> getTableHead() {
        return this.headList;
    }


    @Override
    public Object setTableStyle() {
        return null;
    }
}