java Execl导出功能

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 */@Controllerpublic 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.浏览器效果

5.点击导出

OK