poi导出

1. 引入pom

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!-- easypoi相关jar包 -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.1.0</version>
</dependency>

　　

2.  EasypoiUtil.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
package com.diit.dwyxbd.utils;
 
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import com.alibaba.druid.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Base64;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;
 
 
@Component
public class EasypoiUtil {
 
    //模板生成路径
    @Value("${at.file-upload}")
    private String paths;
 
 
    /**
     * 构建向word中插入的图片信息
     *
     * @param width
     * @param height
     * @param imagePath
     * @return
     */
    public ImageEntity structureImageDate(Integer width, Integer height, String imagePath) {
        ImageEntity image = new ImageEntity();
        image.setWidth(width);
        image.setHeight(height);
        image.setUrl(imagePath);
        image.setType(ImageEntity.URL);
        return image;
    }
 
 
    /**
     * 根据base64生成图片
     *
     * @param base64Info
     * @param filePath
     * @return
     */
    public String decodeBase64(String base64Info, File filePath) {
        File dir = new File(filePath + File.separator + "homeImage");
        if (!dir.exists()) {
            dir.mkdirs();
        }
        if (StringUtils.isEmpty(base64Info)) {
            return null;
        }
        String[] arr = base64Info.split("base64,");
        //要保存的路径
        String picPath = "/homeImage/" + UUID.randomUUID().toString() + ".png";
        String save = filePath + picPath;
        try {
            byte[] buffer = Base64.getDecoder().decode(arr[0]);
            OutputStream os = new FileOutputStream(save);
            os.write(buffer);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return picPath;
    }
 
    /**
     * 根据base64生成专题图片
     *
     * @param base64Info
     * @param
     * @return 路径
     */
    public String specialDecodeBase64(String base64Info, String imagename, String tid) {
        File dir = new File(paths + File.separator + "AssessmentImage" + File.separator + tid);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        if (StringUtils.isEmpty(base64Info)) {
            return null;
        }
        String[] arr = base64Info.split("base64,");
 
        String save = dir.getPath() + File.separator + imagename;
        try {
            byte[] buffer = Base64.getDecoder().decode(arr[0]);
            OutputStream os = new FileOutputStream(save);
            os.write(buffer);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return save;
    }
 
    /**
     * 导出多个sheet
     * @param excelName
     * @param head
     * @param title
     * @param data
     * @param exportFolder
     * @return
     */
    public Map<String, Object> exportExcel(String excelName, String head, List<Map<String, Object>> title, List<Map<String, Object>> data, File exportFolder, String key) {
        //构建标题
        List<ExcelExportEntity> titleList = new ArrayList<>();//存放标题
        for (Map<String, Object> map : title) {
            if (map.get("alias") != null) {
                ExcelExportEntity colEntity = new ExcelExportEntity(map.get("alias").toString(), map.get("field").toString());
                colEntity.setNeedMerge(true);
                titleList.add(colEntity);
            }
        }
 
        Map<String, List<Map<String, Object>>> province = data.stream().collect(Collectors.groupingBy(e -> (String) e.get("province")));
        Set<String> province_key = province.keySet();
        List<Map<String, Object>> sheetList = new ArrayList<>();
        for (String pro : province_key) {
            List<Map<String, Object>> maps1 = province.get(pro); // 每个省份数据
            // 创建sheet放入sheet集合
            Map<String, Object> sheet = new HashMap<>();
            sheetList.add(sheet);
            ExportParams ep1 = new ExportParams(head, pro);
            ep1.setStyle(ExcelStyleUtil.class);//设置样式
            sheet.put("title", ep1);
            sheet.put("entity", titleList);
            sheet.put("data", maps1);
        }
        Workbook workbook = new HSSFWorkbook();// excelExportUtils.getWorkbook(ExcelType.HSSF, 0);
        for (int i = 0; i < sheetList.size(); i++) {
            Map<String, Object> map = sheetList.get(i);
            ExportParams exportParams = (ExportParams) map.get("title");
            List<ExcelExportEntity> entity = (List<ExcelExportEntity>) map.get("entity");
            List<Map<String, Object>> listDataMap = (List<Map<String, Object>>) map.get("data");
            ExcelExportService service = new ExcelExportService();
            service.createSheetForMap(workbook, exportParams, entity, listDataMap);
            //获取tab页的表格设置单元格自适应宽度
            Sheet sheet1 = workbook.getSheetAt((short) i);
            for (int j = 0; j < titleList.size(); j++) {
                sheet1.autoSizeColumn((short) j);
            }
        }
        //下载生成的excel
        if (!exportFolder.exists()) {
            exportFolder.mkdirs();
        }
        Map<String, Object> result = new HashMap<>();
        /* 这中方式是直接生成excel保存到服务器某个地址*/
        try {
            FileOutputStream fos = new FileOutputStream(exportFolder.getPath() + "/" + excelName);
            workbook.write(fos);
            result.put("path", exportFolder.getPath());
            result.put("fileName", excelName);
            System.out.println(result);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
 
    /**
     * 单sheet
     * @param excelName
     * @param head
     * @param title
     * @param data
     * @param exportFolder
     * @param key
     * @return
     */
    public Map<String, Object> exportExcel2(String excelName, String head, List<Map<String, Object>> title, List<Map<String, Object>> data, File exportFolder, String key) {
        //构建标题
        List<ExcelExportEntity> titleList = new ArrayList<>();//存放标题
        for (Map<String, Object> map : title) {
            if (map.get("alias") != null) {
                ExcelExportEntity colEntity = new ExcelExportEntity(map.get("alias").toString(), map.get("field").toString());
                colEntity.setNeedMerge(true);
                titleList.add(colEntity);
            }
        }
        List<Map<String, Object>> sheetList = new ArrayList<>();
        Map<String, Object> sheet = new HashMap<>();
        sheetList.add(sheet);
        ExportParams ep1 = new ExportParams(head, key);
        ep1.setStyle(ExcelStyleUtil.class);//设置样式
        sheet.put("title", ep1);
        sheet.put("entity", titleList);
        sheet.put("data", data);
        Workbook workbook = new HSSFWorkbook();// excelExportUtils.getWorkbook(ExcelType.HSSF, 0);
        for (int i = 0; i < sheetList.size(); i++) {
            Map<String, Object> map = sheetList.get(i);
            ExportParams exportParams = (ExportParams) map.get("title");
            List<ExcelExportEntity> entity = (List<ExcelExportEntity>) map.get("entity");
            List<Map<String, Object>> listDataMap = (List<Map<String, Object>>) map.get("data");
            ExcelExportService service = new ExcelExportService();
            service.createSheetForMap(workbook, exportParams, entity, listDataMap);
            //获取tab页的表格设置单元格自适应宽度
            Sheet sheet1 = workbook.getSheetAt((short) i);
            for (int j = 0; j < titleList.size(); j++) {
                sheet1.autoSizeColumn((short) j);
            }
        }
        //下载生成的excel
        if (!exportFolder.exists()) {
            exportFolder.mkdirs();
        }
        Map<String, Object> result = new HashMap<>();
        /* 这中方式是直接生成excel保存到服务器某个地址*/
        try {
            FileOutputStream fos = new FileOutputStream(exportFolder.getPath() + "/" + excelName);
            workbook.write(fos);
            result.put("path", exportFolder.getPath());
            result.put("fileName", excelName);
            System.out.println(result);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
 
}

　

3. ExcelStyleUtil.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
package com.diit.dwyxbd.utils;
 
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
 
public class ExcelStyleUtil implements IExcelExportStyler{
 
     private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
        private static final short FONT_SIZE_TEN = 10;
        private static final short FONT_SIZE_ELEVEN = 11;
        private static final short FONT_SIZE_TWELVE = 12;
        /**
         * 大标题样式
         */
        private CellStyle headerStyle;
        /**
         * 每列标题样式
         */
        private CellStyle titleStyle;
        /**
         * 数据行样式
         */
        private CellStyle styles;
 
        public ExcelStyleUtil(Workbook workbook) {
            this.init(workbook);
        }
 
        /**
         * 初始化样式
         *
         * @param workbook
         */
        private void init(Workbook workbook) {
            this.headerStyle = initHeaderStyle(workbook);
            this.titleStyle = initTitleStyle(workbook);
            this.styles = initStyles(workbook);
        }
 
        /**
         * 大标题样式
         *
         * @param color
         * @return
         */
        @Override
        public CellStyle getHeaderStyle(short color) {
            return headerStyle;
        }
 
        /**
         * 每列标题样式
         *
         * @param color
         * @return
         */
        @Override
        public CellStyle getTitleStyle(short color) {
            return titleStyle;
        }
 
        /**
         * 数据行样式
         *
         * @param parity 可以用来表示奇偶行
         * @param entity 数据内容
         * @return 样式
         */
        @Override
        public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
            return styles;
        }
 
        /**
         * 获取样式方法
         *
         * @param dataRow 数据行
         * @param obj     对象
         * @param data    数据
         */
        @Override
        public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
            return getStyles(true, entity);
        }
 
        /**
         * 模板使用的样式设置
         */
        @Override
        public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
            return null;
        }
 
        /**
         * 初始化--大标题样式
         *
         * @param workbook
         * @return
         */
        private CellStyle initHeaderStyle(Workbook workbook) {
            CellStyle style = getBaseCellStyle(workbook);
            style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
            return style;
        }
 
        /**
         * 初始化--每列标题样式
         *
         * @param workbook
         * @return
         */
        private CellStyle initTitleStyle(Workbook workbook) {
            CellStyle style = getBaseCellStyle(workbook);
            style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
            //背景色
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            return style;
        }
 
        /**
         * 初始化--数据行样式
         *
         * @param workbook
         * @return
         */
        private CellStyle initStyles(Workbook workbook) {
            CellStyle style = getBaseCellStyle(workbook);
            style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
            style.setDataFormat(STRING_FORMAT);
            return style;
        }
 
        /**
         * 基础样式
         *
         * @return
         */
        private CellStyle getBaseCellStyle(Workbook workbook) {
            CellStyle style = workbook.createCellStyle();
            //下边框
            style.setBorderBottom(BorderStyle.THIN);
            //左边框
            style.setBorderLeft(BorderStyle.THIN);
            //上边框
            style.setBorderTop(BorderStyle.THIN);
            //右边框
            style.setBorderRight(BorderStyle.THIN);
            //水平居中
            style.setAlignment(HorizontalAlignment.CENTER);
            //上下居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置自动换行
            style.setWrapText(true);
            return style;
        }
 
        /**
         * 字体样式
         *
         * @param size   字体大小
         * @param isBold 是否加粗
         * @return
         */
        private Font getFont(Workbook workbook, short size, boolean isBold) {
            Font font = workbook.createFont();
            //字体样式
            font.setFontName("宋体");
            //是否加粗
            font.setBold(isBold);
            //字体大小
            font.setFontHeightInPoints(size);
            return font;
        }
}

　　

4. 使用

?
1
easypoiUtil.exportExcel2("test.xls", null, fieldd, data, filePath, sheetName);

　　

posted @   qukaige  阅读(30)  评论(0)  编辑  收藏  举报
相关博文：
·  Excel 导出
·  blob 数据下载
·  EasyPOI 的基本使用
·  java easypoi 导入导出
·  Java可视化报表，关于easyPoi的使用
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
点击右上角即可分享
微信分享提示
SQL AI 助手