SpringBoot 数据导出excel

电玩女神 2022-01-22 07:47 356阅读 0赞

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

1、maven

<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);
        }
    }