springBoot导出excle文件_excelexportutil.exportexcel null

---

前言

本文可实现springboot批量导出为excle文件。

---

项目结构

一、pom文件所需依赖

		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.73</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10.1</version>
        </dependency>
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

二、User实体类

import lombok.Data;

@Data
public class User {

    private Integer id;

    private String account;

    private String realname;

    private String phone;

    private String email;

    private Byte sex;

    private String sexStr;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

三、ExportExcelUtil工具类

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.RegionUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.support.ManagedMap;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
public class ExportExcelUtil<T> {
    private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtil.class);

    public HSSFDataValidation createDataValidation(String[] textlist, int firstRow, int lastRow, int firstCol, int lastCol) {
        DVConstraint constraint = DVConstraint
                .createExplicitListConstraint(textlist);
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,
                lastRow, firstCol, lastCol);
        return new HSSFDataValidation(regions, constraint);
    }


    /**
     * @param title   表格标题名
     * @param headers 表格属性列名数组 （第一行标题）
     * @param Col     需要显示的表格属性列名数组 如果是javabean 必须和字段名字一直 如果为Map 必须为Map的key名字对应
     * @param dataset 需要显示的数据集合,集合泛型支持两种，1：符合javabean风格的类的对象 2：Map类型。此方法支持的
     *                javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */
    public HSSFWorkbook exportExcel(String title, String[] headers, String[] Col, Collection<T> dataset, String pattern,
                                    List<HSSFDataValidation> dataValidationList) {
        if (pattern == null || pattern.equals("")){
            pattern = "yyy-MM-dd";
        }
        // 声明一个工作薄
        HSSFWorkbook workbook = null;
        try {
            workbook = new HSSFWorkbook();
        } catch (Exception e) {
            return null;
        }
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);

        for (HSSFDataValidation dataValidation : dataValidationList) {
            sheet.addValidationData(dataValidation);
        }

        //设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(18);

        //冻结第一行
        sheet.createFreezePane( 0, 1, 0, 1 );

//        //加密
//        sheet.protectSheet("careyzhu");

        HSSFCellStyle textStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        textStyle.setDataFormat(format.getFormat("@"));
        textStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        textStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        textStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        textStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        textStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        textStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont textStylefont = workbook.createFont();
        textStylefont.setColor(HSSFColor.BLACK.index);
        textStylefont.setFontName("微软雅黑");
        textStylefont.setFontHeightInPoints((short) 10);
        textStyle.setFont(textStylefont);

//        textStyle.setLocked(false);

//        textStyle.setWrapText(true);

        // 生成一个样式
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        // 设置这些样式
        headerstyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        headerstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerstyle.setDataFormat(format.getFormat("@"));

//        headerstyle.setLocked(true);

        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.WHITE.index);
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        headerstyle.setFont(font);

        HSSFRow row = sheet.createRow(0);
        int Cell = 0;

        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(Cell);
            cell.setCellStyle(headerstyle);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            Cell++;
        }
        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            String[] fields = Col;
            Cell = 0;
            for (short i = 0; i < fields.length; i++) {
                String fieldName = fields[i];
                HSSFCell cell = row.createCell(Cell);
                cell.setCellStyle(textStyle);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                try {
                    Object value = "";
                    Class tCls = null;
                    Map map = null;
                    if (t instanceof Map) {
                        map = (Map) t;
                        value = map.get(fieldName);
                    } else {
                        String getMethodName = "get"
                                + fieldName.substring(0, 1).toUpperCase()
                                + fieldName.substring(1);
                        tCls = t.getClass();
                        Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                        value = getMethod.invoke(t, new Object[]{});
                    }
                    if (value == null) {
                        value = " ";
                    }
                    // 判断值的类型后进行强制类型转换
                    String textValue = null;
                    if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                    }
                    if (textValue != null) {
                        HSSFRichTextString richString = new HSSFRichTextString(
                                textValue);
                        cell.setCellValue(richString);
                    }
                    Cell++;
                } catch (Exception e) {
                    log.error(e.getMessage());
                }
            }
        }
        index++;
        CellRangeAddress cra = new CellRangeAddress(index, index + 1, 0, headers.length - 1);
        sheet.addMergedRegion(cra);
        row = sheet.createRow(index);
        HSSFCell cell = row.createCell(0);
        HSSFCellStyle textPromptStyle = workbook.createCellStyle();
        HSSFDataFormat promptformat = workbook.createDataFormat();
        textPromptStyle.setDataFormat(promptformat.getFormat("@"));
        textPromptStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        textPromptStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        textPromptStyle.setWrapText(true);
        HSSFFont promptfont = workbook.createFont();
        promptfont.setFontName("微软雅黑");
        promptfont.setColor(HSSFColor.RED.index);
        promptfont.setFontHeightInPoints((short) 18);
        promptfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        textPromptStyle.setFont(promptfont);
//        textPromptStyle.setLocked(true);
        cell.setCellStyle(textPromptStyle);
        RegionUtil.setBorderBottom(1, cra, sheet, workbook); // 下边框
        RegionUtil.setBorderLeft(1, cra, sheet, workbook); // 左边框
        RegionUtil.setBorderRight(1, cra, sheet, workbook); // 有边框
        RegionUtil.setBorderTop(1, cra, sheet, workbook); // 上边框
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        HSSFRichTextString richString = new HSSFRichTextString(
                "注意：这里是备注。");
        cell.setCellValue(richString);
        return workbook;
    }

    public <T> List<T> execlToJsonArray(MultipartFile file, String[] headers, String[] Col, Class<T> clazz) {
        InputStream inputStream = null;
        int errRow = 0;
        JSONArray jsonArray = new JSONArray();
        try {
            inputStream = file.getInputStream();
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
            // 获取每一个工作薄
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
            if (hssfSheet == null) {
                return jsonArray.toJavaList(clazz);
            }
            // 获取当前工作薄的每一行
            Map<String, Integer> headersMap = new ManagedMap<>();
            for (int i = 0; i < headers.length; i++) {
                headersMap.put(headers[i], i);
            }
            Map<Integer, String> colMap = new ManagedMap<>();
            for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                JSONObject jsonObject = new JSONObject();
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (isMergedRegion(hssfSheet, rowNum, 0)) {
                    break;
                }
                if (hssfRow != null) {
                    errRow++;
                    if (rowNum == 0) {
                        for (int columnNum = 0; columnNum < hssfRow.getLastCellNum(); columnNum++) {
                            String values = getValue(hssfRow.getCell(columnNum));
                            Integer index = headersMap.get(values);
                            colMap.put(columnNum, Col[index]);
                        }
                        continue;
                    }
                    int tmp = hssfRow.getLastCellNum();
                    for (int columnNum = 0; columnNum < hssfRow.getLastCellNum(); columnNum++) {
                        String values = getValue(hssfRow.getCell(columnNum));
                        String valuesName = colMap.get(columnNum);
                        if (valuesName != null && !valuesName.equals("")) {
                            jsonObject.put(valuesName, values);
                        }
                    }

                }
                jsonArray.add(jsonObject);
            }
            inputStream.close();
            return jsonArray.toJavaList(clazz);
        } catch (IOException e) {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e1) {
                    log.error(e1.getMessage());
                }
            }
            log.error("ExcelOperate ", e.getMessage());
            return jsonArray.toJavaList(clazz);
        }
    }

    public Boolean isMergedRegion(HSSFSheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }


    public static Boolean CheckFileExtension(MultipartFile file) {
        if (file.isEmpty()) {
            return false;
        }
        String filename = file.getOriginalFilename();
        String extension = filename.lastIndexOf(".") == -1 ? "" : filename
                .substring(filename.lastIndexOf(".") + 1);
        if("xls".equals(extension) || "xlsx".equals(extension)){
            return true;
        } else {
            return false;
        }
    }

    //转换数据格式
    public String getValue(HSSFCell hssfCell) {
        if (hssfCell == null) {
            return null;
        }
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return BigDecimal.valueOf(hssfCell.getNumericCellValue()).toString();
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }
}

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
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314

四、Contoller类（导出文件）

import com.example.demo.dao.User;
import com.example.demo.util.ExportExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

@Controller
@RequestMapping("/uploadExcel")
public class Contoller {
    //表头
    static final protected String[] DOWNLOAD_USER_HEADERS = {"编号" ,"英文名", "中文名", "性别", "邮箱", "移动电话"};
    //数据键名或者MODEL类字段名
    static final protected String[] DOWNLOAD_USER_COL = {"id", "account", "realname", "sexStr", "email", "phone"};

    @RequestMapping(value = "/test", method = RequestMethod.GET)
    public void downloadTemplet(HttpServletResponse response) throws Exception {
        ExportExcelUtil<User> ex = new ExportExcelUtil<User>();
        List<User> rows = new LinkedList<>();
        User user = new User();
        user.setId(123456);
        user.setAccount("template");
        user.setRealname("模板");
        user.setSexStr("男");
        user.setEmail("templet@qq.com");
        user.setPhone("13456789");
        rows.add(user);
        //生成Excel
        String[] textlist = {"男", "女"};
        List<HSSFDataValidation> dataValidationList = new LinkedList<>();
        HSSFDataValidation dataValidation = ex.createDataValidation(textlist, 1, rows.size(), 2, 2);
        dataValidationList.add(dataValidation);
        HSSFWorkbook workbook = ex.exportExcel("导出文件", DOWNLOAD_USER_HEADERS, DOWNLOAD_USER_COL, rows, null, dataValidationList);

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        String fileName = "导出文件";
        fileName = URLEncoder.encode(fileName, "UTF-8").trim();
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        OutputStream ouputStream = response.getOutputStream();
        workbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }
}

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

五、启动项目，浏览器访问（http://127.0.0.1:8080/uploadExcel/test）

打开效果