springboot导出excel_SpringBoot+Spring Data JPA+Vue前后端分离实现Excel导出功能

点击上方 web项目开发，选择 设为星标

优质文章，及时送达

效果图

支持导出选中数据

导出的选中数据结果图

样式有点丑，但是实现导出功能就行了，你们的测试的时候自己完善一下。

还支持导出全部数据

环境介绍

jdk：1.8

数据库：mysql5.6

前端：vue+element ui

后端：SpringBoot+Spring Data JPA

完整源码获取

扫码关注回复【excel导出代码】获取源码

如果你在运行这个代码的过程中有遇到问题，请加小编微信xxf960513，我拉你进对应微信学习群！！帮助你快速掌握这个功能代码！

核心代码介绍

pox.xml

         <dependency>      <groupId>org.apache.poigroupId>      <artifactId>poiartifactId>      <version>3.16version>    dependency>    <dependency>      <groupId>org.apache.poigroupId>      <artifactId>poi-ooxmlartifactId>      <version>3.16version>    dependency>    <dependency>      <groupId>org.apache.poigroupId>      <artifactId>poi-examplesartifactId>      <version>3.16version>    dependency>            <dependency>      <groupId>org.springframework.bootgroupId>      <artifactId>spring-boot-starter-data-jpaartifactId>    dependency>            <dependency>       <groupId>mysqlgroupId>       <artifactId>mysql-connector-javaartifactId>       <scope>runtimescope>    dependency>

application.yml

spring:  datasource:    driverClassName: com.mysql.cj.jdbc.Driver    url: jdbc:mysql://127.0.0.01:3306/item_jd?characterEncoding=UTF8&useSSL=false    username: root    password: 3456788  jpa:    database: MySQL    show-sql: true    hibernate:      ddl-auto: update    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect    open-in-view: falseserver:  port: 9527

NewsDao.interface

package com.xiaofei.page.dao;import java.util.List;import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.query.Param;import com.xiaofei.page.pojo.News;public interface NewsDao extends JpaRepository<News, String>{  @Query(value="select t.* from news t where t.id in ( :ids)",nativeQuery = true)  List findByIds(@Param("ids") List ids);}

DownExcelService.java

package com.xiaofei.page.service;import java.io.IOException;import java.util.Arrays;import java.util.List;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.Row;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.util.StringUtils;import com.xiaofei.page.dao.NewsDao;import com.xiaofei.page.pojo.News;import com.xiaofei.page.util.DateUtil;@Servicepublic class DownExcelService {    @Autowired    private NewsDao newsDao;    public void downExcel(HttpServletRequest request, HttpServletResponse response, String ids) {        ServletOutputStream outputStream = null;        try {            List<News> lists = null;            if (StringUtils.isEmpty(ids)) {                lists = newsDao.findAll();            } else {                List<String> asList = Arrays.asList(ids.split("\\-"));                lists = newsDao.findByIds(asList);            }            HSSFWorkbook writeListWeekExcel = writeListWeekExcel("测试案列", lists, 6);            //响应到客户端            this.setResponseHeader(request, response, "测试案列.xls");            outputStream = response.getOutputStream();            writeListWeekExcel.write(outputStream);            outputStream.flush();        } catch (IOException e) {            e.printStackTrace();        } finally {            if (null != outputStream) {                try {                    outputStream.close();                } catch (IOException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            }        }    }    private void setResponseHeader(HttpServletRequest request, HttpServletResponse response, String fileName) {        try {            String userAgent = request.getHeader("User-Agent");            if (userAgent.contains("MSIE") || userAgent.contains("Trident"))                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");            else                fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");            response.reset();            response.setContentType("application/vnd.ms-excel;charset=UTF-8");            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);            response.addHeader("Pargam", "no-cache");            response.addHeader("Cache-Control", "no-cache");        } catch (Exception ex) {            ex.printStackTrace();        }    }    private static HSSFWorkbook writeListWeekExcel(String titleName, List<News> dataList,                                                   int cloumnCount) {        HSSFWorkbook workbook = null;        try {            // 获取总列数            int columnNumCount = cloumnCount;            // 创建工作薄            workbook = new HSSFWorkbook();            // 创建标题格式            HSSFCellStyle titleStyle = workbook.createCellStyle();            // 创建一个居中格式            titleStyle.setAlignment(HorizontalAlignment.CENTER);            // 字体            HSSFFont titleFont = workbook.createFont();            titleFont.setFontName("宋体");            // 设置字体大小            titleFont.setFontHeightInPoints((short) 14);            titleFont.setBold(true);// 粗体显示            titleStyle.setFont(titleFont);            // 创建表头格式            HSSFCellStyle headStyle = workbook.createCellStyle();            // 创建一个居中格式            headStyle.setAlignment(HorizontalAlignment.CENTER);            // 字体            HSSFFont headFont = workbook.createFont();            headFont.setFontName("宋体");            // 设置字体大小            headFont.setFontHeightInPoints((short) 12);            headFont.setBold(true);// 粗体显示            headStyle.setFont(headFont);            // 设置边框            headStyle.setBorderBottom(BorderStyle.THIN); // 下边框            headStyle.setBorderLeft(BorderStyle.THIN);// 左边框            headStyle.setBorderTop(BorderStyle.THIN);// 上边框            headStyle.setBorderRight(BorderStyle.THIN);// 右边框            // 创建单元格格式，并设置值表头 设置表头居中            HSSFCellStyle style = workbook.createCellStyle();            // 创建一个居中格式            style.setAlignment(HorizontalAlignment.CENTER);            // 自动换行            style.setWrapText(true);            HSSFFont font = workbook.createFont();            font.setFontName("宋体");            // 设置字体大小            font.setFontHeightInPoints((short) 12);            style.setFont(font);            // 设置边框            style.setBorderBottom(BorderStyle.THIN); // 下边框            style.setBorderLeft(BorderStyle.THIN);// 左边框            style.setBorderTop(BorderStyle.THIN);// 上边框            style.setBorderRight(BorderStyle.THIN);// 右边框            // 创建工作表            HSSFSheet sheet = workbook.createSheet(titleName);            // 设置列宽            sheet.setColumnWidth(0, 2000);            /**             * 往Excel中写新数据             */            // 标题            Row titleRow = sheet.createRow(0);            Cell titleCell = titleRow.createCell(0);            titleCell.setCellStyle(titleStyle);            titleCell.setCellValue(titleName);            Row row0 = sheet.createRow(0);            Cell cell0 = row0.createCell(0);            cell0.setCellValue("id");                Cell cell1 = row0.createCell(1);            cell1.setCellValue("create_time");            Cell cell2 = row0.createCell(2);            cell2.setCellValue("source");            Cell cell3 = row0.createCell(3);            cell3.setCellValue("t_time");            Cell cell4 = row0.createCell(4);            cell4.setCellValue("url");             Cell cell5 = row0.createCell(5);            cell5.setCellValue("title");                      // 得到要插入的每一条记录            for (int i = 0; i < dataList.size(); i++) {                // 创建一行：从第二行开始，跳过属性列                Row row = sheet.createRow(i + 1);                String id = dataList.get(i).getId() + "";                String createTime = DateUtil.format(dataList.get(i).getCreateTime(), "yyyy-MM-dd HH:mm:ss");                String source = dataList.get(i).getSource();                String ttime = DateUtil.format(dataList.get(i).getTTime(), "yyyy-MM-dd HH:mm:ss");                String url = dataList.get(i).getUrl();                String title = dataList.get(i).getTitle();                for (int k = 0; k <= columnNumCount; k++) {                    // 在一行内循环                    Cell first = row.createCell(0);                    first.setCellValue(id);                    Cell second = row.createCell(1);                    second.setCellValue(createTime);                                    Cell third = row.createCell(2);                    third.setCellValue(source);                    Cell fourth = row.createCell(3);                    fourth.setCellValue(ttime);                    Cell fiveth = row.createCell(4);                    fiveth.setCellValue(url);                    Cell sixth = row.createCell(5);                    sixth.setCellValue(title);                }            }        } catch (Exception e) {            e.printStackTrace();        }        return workbook;    }}

main.js

import Vue from 'vue'import App from './App'import router from './router'import axios from 'axios'import ElementUI from 'element-ui';import 'element-ui/lib/theme-chalk/index.css';Vue.use(ElementUI);import $ from 'jquery'axios.defaults.baseURL = 'http://127.0.0.1:8080';Vue.prototype.HOST='/email'Vue.config.productionTip = falseVue.prototype.$axios = axios/* eslint-disable no-new */new Vue({  el: '#app',  router,  components: { App },  template: ''})

Helloworld.vue

<template>  <div style="text-align:center;width: 80%;margin: auto">    <div class="btn-wrap">      <el-button type="primary" plain @click="handleSelect">导出选中el-button>      <el-button type="primary" plain @click="handleSelectAll">导出全部数据el-button>    div>    <el-table :data="tableData" stripe @selection-change="handleSelectionChange">      <el-table-column type="selection" width="55">el-table-column>      <el-table-column prop="title" label="标题">el-table-column>      <el-table-column prop="source" label="来源">el-table-column>      <el-table-column prop="ttime" :formatter="dateFormat" label="发布时间">el-table-column>      <el-table-column prop="url" label="地址">el-table-column>    el-table>    <div class="block" style="display: inline">      <el-pagination        background        style="width:100%;margin: auto"        layout="prev, pager, next"        @current-change="current_change"        :total="total"      >el-pagination>    div>  div>template><script>export default {  name: "HelloWorld",  data() {    return {      total: 111,//总页数      size: 5,//每页条数      currentPage: 1,//第几页      tableData: [],      multipleSelection: ""    };  },  created() {    this.$axios      .get(`page/newlist/${this.currentPage}/${this.size}`)      .then(res => {        this.tableData = res.data.data.content;        console.log(res.data.data.totalPages);        this.total = res.data.data.totalElements;      })      .catch(res => {        alert(res.data.message);      });  },  timestampToTime(row, column) {    var date = new Date(row.cjsj); //时间戳为10位需*1000，时间戳为13位的话不需乘1000    var Y = date.getFullYear() + "-";    var M =      (date.getMonth() + 1 < 10        ? "0" + (date.getMonth() + 1)        : date.getMonth() + 1) + "-";    var D = date.getDate() + " ";    var h = date.getHours() + ":";    var m = date.getMinutes() + ":";    var s = date.getSeconds();    return Y + M + D + h + m + s;    console.log(timestampToTime(1533293827000));  },  methods: {    handleSelectAll() {      window.location.href = "http://127.0.0.1:8080/excel/download";    },    //导出选中    handleSelect() {      window.location.href = `http://127.0.0.1:8080/excel/download?ids=${this.multipleSelection}`;    },    handleSelectionChange(val) {      console.log("val,val", val);      let centerValue = val.map(item => {        return item.id;      });      this.multipleSelection = centerValue.join("-");      console.log("mu", this.multipleSelection);    },    current_change: function(currentPage) {      this.currentPage = currentPage;      console.log(currentPage);      this.$axios        .get(`page/newlist/${currentPage}/${this.size}`)        .then(res => {          this.tableData = res.data.data.content;          console.log(res.data.data.content);        })        .catch(res => {          alert(res.data.message);        });    },    dateFormat(row, column, cellValue, index) {      let daterc = row[column.property];      daterc = daterc.substr(0, 10);      console.log(daterc);      if (daterc != null) {        return daterc;      }    }  }};script>

--完--

如果你觉得这个案例以及我们的分享思路不错，对你有帮助，请分享给身边更多需要学习的朋友。别忘了《留言+点在看》给作者一个鼓励哦！

推荐案例

1、springboot+mybatis+vue前后端分离实现用户登陆注册功能

2、SpringBoot+Vue前后分离实现邮件发送功能

3、SpringBoot+Spring Data JPA+Vue前后端分离实现分页功能

温暖提示

为了方便大家更好的学习，本公众号经常分享一些完整的单个功能案例代码给大家去练习，如果本公众号没有你要学习的功能案例，你可以联系小编(微信：xxf960513)提供你的小需求给我，我安排我们这边的开发团队免费帮你完成你的案例。注意：只能提单个功能的需求不能要求功能太多，比如要求用什么技术，有几个页面，页面要求怎么样？