java_Excel写入

前言

利用该工具类可以向excel模板中写入数据，而不用写过多代码画excel

引入依赖

<dependency>
    <groupId>net.sf.jxls</groupId>
    <artifactId>jxls-core</artifactId>
    <version>1.0.3</version>
</dependency>

单Sheet页导出

创建如下excel模板，通过文件输入流读取excel模板，利用XLSTransformer类将数据写入模板，并将得到的新excel下载到本地，代码如下：

public static void main(String[] args) throws InvalidFormatException, IOException {
    // 循环数据
    List<Object> list = new ArrayList<>();
    for (int i = 0; i < 100; i++) {
        Map<String, Object> data = new HashMap<>();
        data.put("a1", (int) (Math.random() * 100));
        data.put("a2", (int) (Math.random() * 100));
        data.put("a3", (int) (Math.random() * 100));
        data.put("a4", (int) (Math.random() * 100));
        data.put("a5", (int) (Math.random() * 100));
        list.add(data);
    }

    // 表格使用的数据
    Map<Object,Object> map = new HashMap<>();
    map.put("data", list);
    map.put("title", "java基于模板导出excel表格");
    map.put("val", "演示合并单元格的数据显示");

    InputStream is = new FileInputStream("C:\\1111.xlsx");
    XLSTransformer xlsTransformer = new XLSTransformer();
    // 获取Workbook, 传入模板和数据
    Workbook workbook =  xlsTransformer.transformXLS(is, map);

    OutputStream os = new BufferedOutputStream(new FileOutputStream("C:\\2222.xlsx"));
    workbook.write(os);
    is.close();
    os.flush();
    os.close();
}

多sheet页导出

public class TransformXLS {
    public static void main(String[] args) throws InvalidFormatException, IOException {
        FileInputStream is = new FileInputStream("C:\\Users\\1111.xlsx");

        // data中每一个list对应一个sheet页
        List<List<Map<String, Object>>> data = getList();

        List<String> sheetNames = new ArrayList<String>();
        sheetNames.add("湘潭市");
        sheetNames.add("河南省");

        XLSTransformer transformer = new XLSTransformer();
        Workbook workbook = transformer.transformMultipleSheetsList(
            is,   // 输入流
            data, // 要写入的数据
            sheetNames, // sheet页名字
            "result", // 对应excel中标签的名字
            new HashMap<>(), 
            0);

        FileOutputStream os = new FileOutputStream("C:\\Users\\2222.xlsx");
        workbook.write(os);

        os.flush();
        os.close();
        is.close();
    }
}

模拟要写入模板的数据

public static List<List<Map<String, Object>>> getList() {
    //每一个list代表一个sheet页的数据
    List<List<Map<String, Object>>> list = new ArrayList<>();

    //这里我们用map为例，这个是用什么都可以的。
    List<Map<String, Object>> list1 = new ArrayList<Map<String, Object>>();
    Map<String, Object> map1 = new HashMap<String, Object>();
    map1.put("cityName", "湘潭市");
    map1.put("countyName", "湘潭县");
    map1.put("townName", "中路铺镇");
    Map<String, Object> map2 = new HashMap<String, Object>();
    map2.put("cityName", "湘潭市");
    map2.put("countyName", "雨湖区");
    map2.put("townName", "基建营");
    list1.add(map1);
    list1.add(map2);

    List<Map<String, Object>> list2 = new ArrayList<Map<String, Object>>();
    Map<String, Object> map3 = new HashMap<String, Object>();
    map3.put("rownums", 1);
    map3.put("cityName", "河南省");
    map3.put("countyName", "安阳市");
    map3.put("townName", "林州市");
    Map<String, Object> map4 = new HashMap<String, Object>();
    map4.put("rownums", 2);
    map4.put("cityName", "河南省");
    map4.put("countyName", "安阳市");
    map4.put("townName", "汤阴县");
    list2.add(map3);
    list2.add(map4);

    list.add(list1);
    list.add(list2);
    return list;
}