SpringBoot 数据导出excel

SpringBoot导出数据的思路是自定义xls视图解析器

1、maven

<!-- Apache POI Microsoft Documents API -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>

2、自定义xls视图

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.StringUtil;
import org.springframework.http.ContentDisposition;
import org.springframework.http.HttpHeaders;
import org.springframework.web.servlet.view.document.AbstractXlsView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * Spring Web - XLS视图
 */
public class ExcelView extends AbstractXlsView {

    /** 字符集 */
    private static final Charset UTF8 = StringUtil.UTF8;
    /** 文件名 */
    public static final String FILENAME_KEY = "filename";
    /** 属性 */
    public static final String PROPERTY_KEY = "properties";
    /** 内容 */
    public static final String CONTENT_KEY = "contents";

    @Override
    protected void buildExcelDocument(@SuppressWarnings("NullableProblems") Map<String, Object> model, @SuppressWarnings("NullableProblems") Workbook workbook, @SuppressWarnings("NullableProblems") HttpServletRequest request, @SuppressWarnings("NullableProblems") HttpServletResponse response) throws UnsupportedEncodingException {
        //获取文件名
        String filename = model.get(FILENAME_KEY) + "-" + DateFormatUtils.format(new Date(), "yyyyMMddHHmmss") + ".xls";
        //获取属性
        LinkedHashMap<String, String> properties = (LinkedHashMap<String, String>) model.get(PROPERTY_KEY);
        //获取内容
        ArrayList<Map<String, Object>> contents = (ArrayList<Map<String, Object>>) model.get(CONTENT_KEY);
        // 标题样式-粗体
        CellStyle boldStyle = workbook.createCellStyle();
        Font fontStyle = workbook.createFont();
        fontStyle.setBold(true);
        boldStyle.setFont(fontStyle);
        // 写入excel
        Sheet sheet = workbook.createSheet();
        int rowNumber = 0;
        //写入标题
        if (MapUtils.isNotEmpty(properties)) {
            Row headerRow = sheet.createRow(rowNumber++);
            int cellNumber = 0;
            for (Map.Entry<String, String> property : properties.entrySet()) {
                Cell cell = headerRow.createCell(cellNumber++);
                cell.setCellStyle(boldStyle);
                cell.setCellValue(property != null ? property.getValue() : null);
                // 列宽自适应
                sheet.autoSizeColumn(cell.getColumnIndex(), true);
            }
        }
        // 写入内容
        if (CollectionUtils.isNotEmpty(contents)) {
            for (Map<String, Object> content : contents) {
                Row dataRow = sheet.createRow(rowNumber++);
                int cellNumber = 0;
                for (Map.Entry<String, String> property : properties.entrySet()) {
                    Cell cell = dataRow.createCell(cellNumber++);
                    cell.setCellValue(content != null && property != null ? String.valueOf(content.get(property.getKey())) : null);
                }
            }
        }
        //文件名编码
        String name;
        String userAgent = request.getHeader("user-agent").toLowerCase();
        if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
            // IE
            name = URLEncoder.encode(filename, UTF8.name());
        } else {
            // 非IE
            name = new String(filename.getBytes(UTF8.name()), "ISO-8859-1");
        }
        // 设置header 下载
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, ContentDisposition.builder("attachment").filename(name).build().toString());
        // 字符集
        response.setCharacterEncoding(UTF8.name());
    }
}

3、视图使用

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping("/export")
public class TestExportController {

    @GetMapping
    public ModelAndView exprot(){
        //属性
        Map<String, String> properties = new LinkedHashMap<>(4);
        properties.put("id", "ID");
        properties.put("name", "名称");
        properties.put("createDate", "创建时间");
        properties.put("updateDate", "更新时间");
        //内容
        List<Map<String, Object>> contents = new ArrayList<>();
        Map<String, Object> content = new HashMap<>(4);
        content.put("id", 1);
        content.put("name", "测试");
        content.put("createDate", new Date());
        content.put("updateDate", new Date());
        contents.add(content);
        //模型
        Map<String, Object> model = new HashMap<>(3);
        model.put(ExcelView.FILENAME_KEY, "测试数据导出");
        model.put(ExcelView.PROPERTY_KEY, properties);
        model.put(ExcelView.CONTENT_KEY, contents);
        //返回视图
        return new ModelAndView(new ExcelView(), model);
    }
}