java笔记_15_动态生成Excel文件

// 创建表头数据
// 内层List按纵向创建，外层List按横向添加，横向重复的名称会自动合并表格。

List<List<String>> list = new ArrayList<>();

List<String> childList1 = new ArrayList<>();
childList1.add("aaa");
childList1.add("bbb");
childList1.add("ccc");
List<String> childList2 = new ArrayList<>();
childList2.add("aaa");
childList2.add("bbb");
childList2.add("ddd");
List<String> childList3 = new ArrayList<>();
childList3.add("aaa");
childList3.add("eee");
childList3.add("fff");

list.add(childList1);
list.add(childList2);
list.add(childList3);

依赖

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;

实现代码

            // 表头数据
            String titleJsonStr = "{\"name\":\"名称\",\"age\":\"年龄\"}";
            JSONObject titleJson = JSONObject.parseObject(req.getTitleJson(), Feature.OrderedField);
            //单行数据
            List<String> dataJsonList = new ArrayList<>();
            dataJsonList.add("{\"name\":\"testname1\",\"age\":\"1\"}");
            dataJsonList.add("{\"name\":\"testname2\",\"age\":\"2\"}");

            // 1.获取字段列表
            List<List<String>> headList = new ArrayList<>();

            List<String> colCodeList = new ArrayList<>();
            Map<String, Object> map = titleJson.getInnerMap();
            Iterator<String> keyList = map.keySet().iterator();
            while (keyList.hasNext()) {
                String key = keyList.next();
                // 添加字段
                colCodeList.add(key);
                // 按列添加标题
                List<String> headTitleName = new ArrayList<>();
                headTitleName.add(map.get(key).toString());
                headList.add(headTitleName);
            }

            // 2.生成Excel文件
            // 2.1.创建文件
            File file = new File(filePath);
            // 2.2.写入表头
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(file);
            excelWriterBuilder = excelWriterBuilder.head(headList).excelType(ExcelTypeEnum.XLSX);
            ExcelWriter excelBuild = excelWriterBuilder.build();
            WriteSheet writeSheet = EasyExcel.writerSheet("sheet").build();
            // 2.3.遍历对象写入表格
            for (int i = 0; i < dataJsonList.size(); i++) {
                // 获取单行数据
                List<List<String>> cellList = new ArrayList<>();
                // 获取单表格数据
                List<String> cell = new ArrayList<>();
                JSONObject dataJson = JSONObject.parseObject(dataJsonList.get(i));
                for (int j = 0; j < colCodeList.size(); j++) {
                    cell.add(dataJson.getString(colCodeList.get(j)));
                }
                cellList.add(cell);
                excelBuild.write(cellList, writeSheet);
            }
            excelBuild.finish();
            // 2.4.文件生成
            file.createNewFile();

 

Java ExcelWriter自定义表格构建：https://blog.csdn.net/Dvvvvvvvvvvvv/article/details/129255189