springBoot导出excle文件

---

前言

本文可实现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>

二、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;
}

三、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());
        }
    }
}

四、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();
    }
}

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

打开效果