ssm添加POI导出数据至Excel功能通用模板与部署步骤代码详解

最新推荐文章于 2022-07-19 15:29:12 发布

戴眼镜的小青年

最新推荐文章于 2022-07-19 15:29:12 发布

阅读量674

点赞数 4

分类专栏：学习之路学习之路文章标签： ssm poi Excel 导出数据

本文链接：https://blog.csdn.net/weixin_44594056/article/details/88540202

版权

学习之路同时被 2 个专栏收录

42 篇文章 0 订阅

订阅专栏

学习之路

15 篇文章 1 订阅

订阅专栏

前言: 在web开发中，有一个经典的功能，就是数据的导入导出。特别是数据的导出，在生产管理或者财务系统中用的非常普遍，因为这些系统经常要做一些报表打印的工作。而数据导出的格式一般是EXCEL，这里就用这篇文章给大家介绍下小编的自己依赖poi所编写的通用导出模板.一共分为五步,接下来给大家一一带来.
1.添加依赖

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>4.0.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.0.0</version>
    </dependency>

2.test.jsp页面

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--
  Created by IntelliJ IDEA.
  User: lilei
  Date: 13/03/2019
  Time: 17:20
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>success</title>
</head>
<body>
<table>
    <th>账户id</th>
    <th>用户编号</th>
    <th>用户余额</th>
    <c:forEach var="s" items="${list}">
        <tr>
            <td>${s.accountId}</td>
            <td>${s.uid}</td>
            <td>${s.money}</td>
        </tr>
    </c:forEach>
    <tr><td><a href="/account/derive"><input type="button" value="导入"></a></td></tr>
</table>
</body>
</html>

3.对应需导出数据的entity类

package com.lilei.entity;

public class Account {
    private Integer accountId;
    private Integer uid;
    private Double money;

    @Override
    public String toString() {
        return "Account{" +
                "accountId=" + accountId +
                ", uid=" + uid +
                ", money=" + money +
                '}';
    }

    public Integer getAccountId() {
        return accountId;
    }

    public void setAccountId(Integer accountId) {
        this.accountId = accountId;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }
}

4.poi数据导出控制器

package com.lilei.web.controller;

import com.lilei.domain.Account;
import com.lilei.service.AccountService;
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.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;
@Controller
@RequestMapping("/account")
public class AccountController {
    @Autowired
    private AccountService accountService;
@RequestMapping("derive")
    public void derive(HttpServletResponse response) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("test1");
        //导出数据的封装,account为需导出的数据封装成的实体类
        List<Account> all = accountService.findAll();
        //list的长度为到excel表行数
        for (int i = 0; i < all.size(); i++) {
            Row row = sheet.createRow(i);
            //利用反射获得一行数据(实体类属性)的集合
            Field[] fields = all.get(i).getClass().getDeclaredFields();
            for (int j = 0; j < fields.length; j++) {
                fields[j].setAccessible(true);
                //创建单元格并反射调用get方法获得对应属性的值并添加至单元格
                row.createCell(j).setCellValue(fields[j].get(all.get(i)).toString());
            }
        }
        response.setCharacterEncoding("utf-8");
        //编辑导出的表名
        String encode = URLEncoder.encode("test表","utf-8");
        //获得输出流
        ServletOutputStream out = response.getOutputStream();
        //设置下载响应头
        response.setHeader("content-disposition","attachment;fileName="+encode+".xlsx");
        //将生成的文件写入输出流,提供下载
        workbook.write(out);
    }

5.部署测试
在这里插入图片描述