java Execl导出功能-优快云博客

本文详细介绍了一种使用SpringBoot框架进行Excel数据导出的方法，包括创建Excel工具类、实现控制器测试方法及前端页面设计。具体步骤涉及自定义Excel视图类、构建Excel文档、设置样式和内容，以及通过控制器调用导出功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.先写Execl 工具类两个

package com.utils.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.LocalizedResourceHelper;
import org.springframework.web.servlet.support.RequestContextUtils;
import org.springframework.web.servlet.view.AbstractView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.util.Locale;
import java.util.Map;

public abstract class XlsxAbstractExcelView extends AbstractView {
    /** The content type for an Excel response */
    private static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    /** The extension to look for existing templates */
    private static final String EXTENSION = ".xlsx";
    private String url;

    /**     * Default Constructor.     * Sets the content type of the view to "application/vnd.ms-excel".     */
    public XlsxAbstractExcelView() {
        setContentType(CONTENT_TYPE);
    }
    public void setUrl(String url) {
        this.url = url;
    }


    @Override
    protected boolean generatesDownloadContent() {
        return true;
    }

    /**
     * Renders the Excel view, given the specified model.
     */
    @Override
    protected final void renderMergedOutputModel(
            Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {

        Workbook workbook;
        ByteArrayOutputStream baos = createTemporaryOutputStream();
      /*if (this.url != null) {
         workbook = getTemplateSource(this.url, request);
      }
      else {*/
        workbook = new XSSFWorkbook();
        logger.debug("Created Excel Workbook from scratch");
        //}

        buildExcelDocument(model, workbook, request, response);

        // Set the content type.
        //response.setContentType(getContentType());

        // Should we set the content length here?
        // response.setContentLength(workbook.getBytes().length);

        // Flush byte array to servlet output stream.
        //ServletOutputStream out = response.getOutputStream();
        workbook.write(baos);
        writeToResponse(response, baos);
        //out.flush();
    }

    protected Workbook getTemplateSource(String url, HttpServletRequest request) throws Exception {
        LocalizedResourceHelper helper = new LocalizedResourceHelper(getApplicationContext());
        Locale userLocale = RequestContextUtils.getLocale(request);
        Resource inputFile = helper.findLocalizedResource(url, EXTENSION, userLocale);

        // Create the Excel document from the source.
        if (logger.isDebugEnabled()) {
            logger.debug("Loading Excel workbook from " + inputFile);
        }
        //POIFSFileSystem fs = new POIFSFileSystem(inputFile.getInputStream());
        return new XSSFWorkbook(inputFile.getInputStream());
    }
    protected abstract void buildExcelDocument(
            Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response)
            throws Exception;

    protected Cell getCell(Sheet sheet, int row, int col) {
        Row sheetRow = sheet.getRow(row);
        if (sheetRow == null) {
            sheetRow = sheet.createRow(row);
        }
        Cell cell = sheetRow.getCell(col);
        if (cell == null) {
            cell = sheetRow.createCell(col);
        }
        return cell;
    }
    protected void setText(Cell cell, String text) {
        cell.setCellType(CellType.STRING);
        cell.setCellValue(text);
    }
}

package com.utils.excel;

import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;

/**
 * Excel导出
 */
public class ObjectExcelView extends XlsxAbstractExcelView {

    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {

        Sheet sheet;
        Cell cell;
        workbook.createName().setNameName(model.get("fileName").toString());
        sheet = workbook.createSheet(model.get("sheetName").toString());

        List<String> titles = (List<String>) model.get("titles");
        int len = titles.size();
        CellStyle headerStyle = workbook.createCellStyle(); // 标题样式
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font headerFont = workbook.createFont(); // 标题字体
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 11);
        headerStyle.setFont(headerFont);
        int width = 20;
        short height = 25 * 20;
        sheet.setDefaultColumnWidth(width);
        for (int i = 0; i < len; i++) { // 设置标题
            String title = titles.get(i);
            cell = getCell(sheet, 0, i);
            cell.setCellStyle(headerStyle);
            setText(cell, title);
        }
        sheet.getRow(0).setHeight(height);
        CellStyle contentStyle = workbook.createCellStyle(); // 内容样式
        contentStyle.setAlignment(HorizontalAlignment.CENTER);

        List<Map<String, Object>> varList = (List<Map<String, Object>>) model.get("varList");
        int varCount = varList.size();
        for (int i = 0; i < varCount; i++) {
            Map<String, Object> vpd = varList.get(i);
            for (int j = 0; j < len; j++) {
                Object varstr = vpd.get("var" + (j + 1)) != null ? vpd.get("var" + (j + 1)) : "";
                cell = getCell(sheet, i + 1, j);
                cell.setCellStyle(contentStyle);
                setText(cell, varstr.toString());
            }
        }
    }


}

2.controller 测试方法

package com.controller.download;

import com.service.UserService;
import com.utils.BaseUtils;
import com.utils.ResultJson;
import com.utils.excel.ObjectExcelView;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import java.util.*;

/**
 * @Author SPF
 * @Date 2017/5/24
 */
@Controller
public class ExeclDownController extends BaseUtils {

    @Autowired private UserService userService;

    @RequestMapping("/down/index")
    public String index(ModelMap model) {
        ResultJson n = userService.findAll();
        List<Map<String, Object>> list = n.getDatas();
        model.addAttribute("data", list);
        return "down/index";
    }

    @RequestMapping("/down")
    public ModelAndView down() {
        Map<String,Object> model = new HashMap<String, Object>();
        ResultJson n = userService.findAll();
        List<Map<String, Object>> list = n.getDatas();

        model.put("fileName","测试execl下载");
        model.put("sheetName","测试execl下载");
        model.put("titles", Arrays.asList(new String[]{"序号","姓名","年龄","性别","手机","密码"}));

        List<Map<String,Object>> r = new ArrayList<Map<String,Object>>();

        for (Map<String, Object> map : list) {
            Map<String, Object> m = new HashMap<String, Object>();
            m.put("var1",map.get("id"));
            m.put("var2",map.get("name"));
            m.put("var3",map.get("age"));
            m.put("var4",map.get("sex"));
            m.put("var5",map.get("phone"));
            m.put("var6",map.get("pwd"));
            r.add(m);
        }
        model.put("varList",r);
        return new ModelAndView(new ObjectExcelView(),model);
    }

}

3.html页面

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>execl导出</title>
    
   <meta http-equiv="pragma" content="no-cache">
   <meta http-equiv="cache-control" content="no-cache">
   <meta http-equiv="expires" content="0">    
   <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
   <meta http-equiv="description" content="This is my page">
   <!--
   <link rel="stylesheet" type="text/css" href="styles.css">
   -->

  </head>
  
  <body>
   <table>
        <thead>
            <tr>
                <th>序号</th>
                <th>姓名</th>
                <th>年龄</th>
                <th>性别</th>
                <th>手机</th>
                <th>密码</th>
            </tr>
        </thead>
       <tbody>
            <c:forEach items="${data}" var="ite">
                <tr>
                    <td>${ite.id}</td>
                    <td>${ite.name}</td>
                    <td>${ite.age}</td>
                    <td>${ite.sex}</td>
                    <td>${ite.phone}</td>
                    <td>${ite.pwd}</td>
                </tr>
            </c:forEach>
       </tbody>
   </table>
  <a href="down">导出</a>
  </body>
</html>

4.浏览器效果