springboot数据导出到Excel表格

springboot整合apache.poi将数据导出为Excel格式

第一步导入依赖jar包

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
</dependency>

第二步创建接收数据的vo


这是要获取的数据内容

实体类:

package cn.aplid.vo;

import java.io.Serializable;

/**
 * Created by Administrator on 2019/12/27.
 */
public class ServiceVo implements Serializable{
    private int service_id;
    private String service_name;
    private int HF;
    private int HP;
    private int HB;
    private int HH;
    private int HA;
    private int HD;
    private int HN;
    private int HV;
    private int HS;
    private int HW;
    private int HE;
    private int HC;
    private int SC;
    private int HL;
    private int HT;
    private int PD;

    public int getService_id() {
        return service_id;
    }

    public void setService_id(int service_id) {
        this.service_id = service_id;
    }


    public String getService_name() {
        return service_name;
    }

    public void setService_name(String service_name) {
        this.service_name = service_name;
    }

    public int getHF() {
        return HF;
    }

    public void setHF(int HF) {
        this.HF = HF;
    }

    public int getHP() {
        return HP;
    }

    public void setHP(int HP) {
        this.HP = HP;
    }

    public int getHB() {
        return HB;
    }

    public void setHB(int HB) {
        this.HB = HB;
    }

    public int getHH() {
        return HH;
    }

    public void setHH(int HH) {
        this.HH = HH;
    }

    public int getHA() {
        return HA;
    }

    public void setHA(int HA) {
        this.HA = HA;
    }

    public int getHD() {
        return HD;
    }

    public void setHD(int HD) {
        this.HD = HD;
    }

    public int getHN() {
        return HN;
    }

    public void setHN(int HN) {
        this.HN = HN;
    }

    public int getHV() {
        return HV;
    }

    public void setHV(int HV) {
        this.HV = HV;
    }

    public int getHS() {
        return HS;
    }

    public void setHS(int HS) {
        this.HS = HS;
    }

    public int getHW() {
        return HW;
    }

    public void setHW(int HW) {
        this.HW = HW;
    }

    public int getHE() {
        return HE;
    }

    public void setHE(int HE) {
        this.HE = HE;
    }

    public int getHC() {
        return HC;
    }

    public void setHC(int HC) {
        this.HC = HC;
    }

    public int getSC() {
        return SC;
    }

    public void setSC(int SC) {
        this.SC = SC;
    }

    public int getHL() {
        return HL;
    }

    public void setHL(int HL) {
        this.HL = HL;
    }

    public int getHT() {
        return HT;
    }

    public void setHT(int HT) {
        this.HT = HT;
    }

    public int getPD() {
        return PD;
    }

    public void setPD(int PD) {
        this.PD = PD;
    }
}

第三部通过代码进行表格参数设置

package cn.aplid.service;

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

/**
 * Created by Administrator on 2019/12/17.
 */
public class SetTitle {
    public void setTitle(HSSFWorkbook workbook, HSSFSheet sheet){
        HSSFRow row = sheet.createRow(0);
        //设置列宽,setColumnWidth的第二个参数要乘以256,这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(0, 5*256);
        sheet.setColumnWidth(1, 10*256);
        sheet.setColumnWidth(2, 10*256);
        sheet.setColumnWidth(3, 10*256);
        sheet.setColumnWidth(4, 10*256);
        sheet.setColumnWidth(5, 10*256);
        sheet.setColumnWidth(6, 10*256);
        sheet.setColumnWidth(7, 10*256);
        sheet.setColumnWidth(8, 10*256);
        sheet.setColumnWidth(9, 10*256);
        sheet.setColumnWidth(10, 10*256);
        sheet.setColumnWidth(11, 10*256);
        sheet.setColumnWidth(12, 10*256);
        sheet.setColumnWidth(13, 10*256);
        sheet.setColumnWidth(14, 10*256);
        sheet.setColumnWidth(15, 10*256);
        sheet.setColumnWidth(16, 10*256);
        sheet.setColumnWidth(17, 10*256);

        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);

        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("服务序号");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("服务机构");
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue("助餐");
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue("助洁");
        cell.setCellStyle(style);

        cell = row.createCell(4);
        cell.setCellValue("助浴");
        cell.setCellStyle(style);

        cell = row.createCell(5);
        cell.setCellValue("助急");
        cell.setCellStyle(style);

        cell = row.createCell(6);
        cell.setCellValue("加电维修");
        cell.setCellStyle(style);

        cell = row.createCell(7);
        cell.setCellValue("助医");
        cell.setCellStyle(style);

        cell = row.createCell(8);
        cell.setCellValue("护理");
        cell.setCellStyle(style);

        cell = row.createCell(9);
        cell.setCellValue("探望");
        cell.setCellStyle(style);

        cell = row.createCell(10);
        cell.setCellValue("助购");
        cell.setCellStyle(style);

        cell = row.createCell(11);
        cell.setCellValue("住行");
        cell.setCellStyle(style);

        cell = row.createCell(12);
        cell.setCellValue("助乐");
        cell.setCellStyle(style);

        cell = row.createCell(13);
        cell.setCellValue("助聊");
        cell.setCellStyle(style);

        cell = row.createCell(14);
        cell.setCellValue("精神慰藉");
        cell.setCellStyle(style);

        cell = row.createCell(15);
        cell.setCellValue("助学");
        cell.setCellStyle(style);

        cell = row.createCell(16);
        cell.setCellValue("其他");
        cell.setCellStyle(style);

        cell = row.createCell(17);
        cell.setCellValue("开设家庭养老床位");
        cell.setCellStyle(style);

    }

}

业务实现

接口

package cn.aplid.mapper;

import cn.aplid.vo.ServiceVo;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by Administrator on 2019/12/27.
 */
@Repository
public interface QueryServiceMapper {

    List<ServiceVo> queryService();

}

实现

package cn.aplid.service;

import cn.aplid.mapper.QueryServiceMapper;
import cn.aplid.vo.ServiceVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * Created by Administrator on 2019/12/27.
 */
@Service
public class QueryService implements QueryServiceMapper{
    @Autowired
    private QueryServiceMapper queryServiceMapper;

    @Override
    public List<ServiceVo> queryService() {
        return queryServiceMapper.queryService();
    }
}

业务

package cn.aplid.controller;

import java.io.*;
import java.util.ArrayList;
import java.util.List;


import cn.aplid.mapper.QueryServiceMapper;
import cn.aplid.service.SetTitle;
import cn.aplid.vo.ServiceVo;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.*;


import javax.servlet.http.HttpServletResponse;


/**
 * Created by Administrator on 2019/12/17.
 */

@RestController
public class Excel_Controller {

    @Autowired
    private QueryServiceMapper queryServiceMapper;
    @RequestMapping(value = "/download",method = RequestMethod.POST)
    public void download(HttpServletResponse response) throws Exception{
        HSSFWorkbook workbook = new HSSFWorkbook();
        SetTitle setTitle = new SetTitle();
        //创建一个Excel表单,参数为sheet的名字
        HSSFSheet sheet = workbook.createSheet("服务老人统计");
        //创建表头
        setTitle.setTitle(workbook, sheet);


        List<ServiceVo> list = queryServiceMapper.queryService();
        System.out.print(list.toString());
        int rowNum = 1;
        for (ServiceVo serviceVo:list) {
            HSSFRow row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue(serviceVo.getService_id());
            row.createCell(1).setCellValue(serviceVo.getService_name());
            row.createCell(2).setCellValue(serviceVo.getHF());
            row.createCell(3).setCellValue(serviceVo.getHP());
            row.createCell(4).setCellValue(serviceVo.getHB());
            row.createCell(5).setCellValue(serviceVo.getHH());
            row.createCell(6).setCellValue(serviceVo.getHA());
            row.createCell(7).setCellValue(serviceVo.getHD());
            row.createCell(8).setCellValue(serviceVo.getHN());
            row.createCell(9).setCellValue(serviceVo.getHV());
            row.createCell(10).setCellValue(serviceVo.getHS());
            row.createCell(11).setCellValue(serviceVo.getHW());
            row.createCell(12).setCellValue(serviceVo.getHE());
            row.createCell(13).setCellValue(serviceVo.getHC());
            row.createCell(14).setCellValue(serviceVo.getSC());
            row.createCell(15).setCellValue(serviceVo.getHL());
            row.createCell(16).setCellValue(serviceVo.getHT());
            row.createCell(17).setCellValue(serviceVo.getPD());
            rowNum++;
        }
        String fileName = "service.xlsx";
        //清空response
        response.reset();
        //设置response的Header
        response.addHeader("Content-Disposition", "attachment;filename="+ fileName);
        OutputStream os = new BufferedOutputStream(response.getOutputStream());

        response.setContentType("application/vnd.ms-excel;charset=gb2312");
        //将excel写入到输出流中
        workbook.write(os);
        os.flush();
        os.close();


    }

}

最后在前端设置一个下载按钮即可(不可用ajax请求地址,得用form表单访问)

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>

<center>
    <div>
        <form action="/download" method="post">
            <input type="submit" value="下载">
        </form>
    </div>
</center>
</body>
</html>

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

可以看到导出成功!

  • 2
    点赞
  • 0
    评论
  • 10
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值