一、概述 企业开发中，文件的导出基本是每个项目都会涉及到的，虽然网络上也有很多大神总结的导出POI的代码，但是感觉写的都比较乱，基于此本文从前端到后台完整演示文件导出是如何实现的。供广大IT爱好者参考。 说明：本项目是在学习尚硅谷-雷丰阳老师的springboot整合web时，做的一个springboot-restful-crud的整合项目，在此感谢尚硅谷的雷老师！视频地址：https://www.bilibili.com/video/BV1Et411Y7tQ?from=search&seid=3721447562465164009 1、先看效果图 2、Excel表格中的数据 3、上代码 （1）前端代码
<!DOCTYPE html> <html lang="en" xmlns:th="http://www.thymeleaf.org"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <meta name="description" content=""> <meta name="author" content=""> <title>Dashboard Template for Bootstrap</title> <!-- Bootstrap core CSS --> <link th:href="@{/asserts/css/bootstrap.min.css}" rel="stylesheet"> <!-- Custom styles for this template --> <link th:href="@{/asserts/css/dashboard.css}" rel="stylesheet"> <style type="text/css"> /* Chart.js */ @-webkit-keyframes chartjs-render-animation { from { opacity: 0.99 } to { opacity: 1 } } @keyframes chartjs-render-animation { from { opacity: 0.99 } to { opacity: 1 } } .chartjs-render-monitor { -webkit-animation: chartjs-render-animation 0.001s; animation: chartjs-render-animation 0.001s; } </style> </head> <body> <!-- 引入顶部栏 语法：th:replace="~{模板名::片段名}" th:replace="模板名::片段名" --> <div th:replace="commons/commonbar::topbar"></div> <div class="container-fluid"> <div class="row"> <!-- 引入侧边栏 语法：th:replace="~{模板名::片段名}" th:replace="模板名::片段名" --> <div th:replace="commons/commonbar::sidebar(activeUri='department/getAllDepartments')"></div> <main role="main" class="col-md-9 ml-sm-auto col-lg-10 pt-3 px-4"> <h2> <a class="btn btn-sm btn-success" th:href="@{/department/toDepartmentAddPage}">添加</a> <a class="btn btn-sm btn-success" th:href="@{/employee/toImportExcelPage}">导入</a><!--TODO--> <a class="btn btn-sm btn-success" th:href="@{/department/exportDepartment}">导出</a> <a class="btn btn-sm btn-success" th:href="@{/employee/batchDeleteEmployee}">批量删除</a><!--TODO--> </h2> <div class="table-responsive"> <table class="table table-striped table-sm"> <thead> <tr> <th>部门编号</th> <th>部门名称</th> <th>操作</th> </tr> </thead> <tbody> <tr th:each="department:${departments}"> <td th:text="${department.id}"></td> <td th:text="${department.departmentName}"></td> <td> <a class="btn btn-sm btn-primary" th:href="@{/department/toDepartmentEditPage/}+${department.id}">编辑</a> <button th:attr="delUri=@{/department/deleteDepartmentById/}+${department.id}" class="btn btn-sm btn-danger delBtn">删除</button> </td> </tr> </tbody> </table> </div> </main> </div> </div> <!-- Bootstrap core JavaScript ================================================== --> <!-- Placed at the end of the document so the pages load faster --> <script type="text/javascript" th:src="@{/asserts/js/jquery-3.2.1.slim.min.js}"></script> <script type="text/javascript" th:src="@{/asserts/js/popper.min.js}"></script> <script type="text/javascript" th:src="@{/asserts/js/bootstrap.min.js}"></script> <!-- Icons --> <script type="text/javascript" th:src="@{/asserts/js/feather.min.js}"></script> <script> feather.replace() </script> <!-- Graphs --> <script type="text/javascript" th:src="@{/asserts/js/Chart.min.js}"></script> <script> var ctx = document.getElementById("myChart"); var myChart = new Chart(ctx, { type: 'line', data: { labels: ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"], datasets: [{ data: [15339, 21345, 18483, 24003, 23489, 24092, 12034], lineTension: 0, backgroundColor: 'transparent', borderColor: '#007bff', borderWidth: 4, pointBackgroundColor: '#007bff' }] }, options: { scales: { yAxes: [{ ticks: { beginAtZero: false } }] }, legend: { display: false, } } }); </script> </body> </html>（2）DepartmentController
@Controller public class DepartmentController { Logger logger = LoggerFactory.getLogger(getClass()); @Resource private DepartmentService departmentService; //导出 @GetMapping(value = "/department/exportDepartment") public void exportDepartment(HttpServletResponse response) { try { response.setContentType("application/binary;charset=UTF-8"); ServletOutputStream outputStream = response.getOutputStream(); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmsssSSS"); String fileName = URLEncoder.encode("部门统计表"+sdf.format(new Date()), "UTF-8"); response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls"); String[] titles = { "部门编号", "部门名称"}; departmentService.exportDepartment(titles,outputStream); } catch (IOException e) { e.printStackTrace(); } } }（3）DepartmentService
package com.atguigu.springboot.service; import com.atguigu.springboot.pojo.Department; import javax.servlet.ServletOutputStream; import java.util.List; public interface DepartmentService { /** * 导出xls格式的Excel * @param titles * @param outputStream */ void exportDepartment(String[] titles, ServletOutputStream outputStream); }（4）DepartmentServiceImpl
package com.atguigu.springboot.service.impl; import com.atguigu.springboot.mapper.DepartmentMapper; import com.atguigu.springboot.pojo.Department; import com.atguigu.springboot.service.DepartmentService; import com.atguigu.springboot.utils.PoiXlsStyleUtils; import javafx.scene.control.Cell; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.CellStyle; import org.springframework.stereotype.Service; import org.springframework.util.CollectionUtils; import javax.annotation.Resource; import javax.servlet.ServletOutputStream; import java.io.IOException; import java.util.Collections; import java.util.List; @Service public class DepartmentServiceImpl implements DepartmentService { @Resource private DepartmentMapper departmentDao; @Override public void exportDepartment(String[] titles, ServletOutputStream outputStream) { //1、创建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); //2、创建工作表 HSSFSheet sheet = workbook.createSheet("部门统计表"); //3、创建一个行，当做标题行 HSSFRow headerRow = sheet.createRow(0); //4、表头样式 CellStyle headerCellStyle = PoiXlsStyleUtils.getHeaderCellStyle(workbook); HSSFCell cell = null; for(int i=0; i<titles.length; i++){ cell = headerRow.createCell(i); //第1行第i列 cell.setCellValue(titles[i]); //第1行第i列填充值 cell.setCellStyle(headerCellStyle); //居中显示 } //5、写入数据 //内容样式 CellStyle contentCellStyle = PoiXlsStyleUtils.getContentCellStyle(workbook); List<Department> departments = departmentDao.getAllDepartments(); if(!CollectionUtils.isEmpty(departments)){ for(int i=0; i<departments.size(); i++){ HSSFRow row = sheet.createRow(i + 1); //第i+1行,从第二行开始 Department department = departments.get(i); HSSFCell cellId = row.createCell(0); //部门编号cell cellId.setCellValue(department.getId()); //设置部门编号 cellId.setCellStyle(contentCellStyle); //部门编号设置样式 HSSFCell cellDepartmentname = row.createCell(1); //部门名称cell cellDepartmentname.setCellValue(department.getDepartmentName()); //设置部门名称 cellDepartmentname.setCellStyle(contentCellStyle); //部门名称设置样式 } } //6、下载文件 try { workbook.write(outputStream); outputStream.flush(); outputStream.close(); workbook.close(); } catch (IOException e) { e.printStackTrace(); } } }（4）PoiXlsStyleUtils
package com.atguigu.springboot.utils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * xls版本的样式设置工具类 */ public class PoiXlsStyleUtils { //设置表头样式 public static CellStyle getHeaderCellStyle(HSSFWorkbook workbook){ HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); //前景色 cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); //线条 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中设置 //设置边框 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框 return cellStyle; } //设置内容样式 public static CellStyle getContentCellStyle(HSSFWorkbook workbook){ HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); //内容左对齐 //设置边框 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框 return cellStyle; } }（5）DepartmentMapper
package com.atguigu.springboot.mapper; import com.atguigu.springboot.pojo.Department; import java.util.List; public interface DepartmentMapper { /** * 查询所有的部门 * @return */ List<Department> getAllDepartments(); /** * 添加部门 * @param department */ void saveDepartment(Department department); /** * 根据id查询部门 * @param id * @return */ Department getDepartmentById(Integer id); /** * 修改部门 * @param department */ void updateDepartment(Department department); }（6）DepartmentMapper.xml
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.atguigu.springboot.mapper.DepartmentMapper"> <!--查询所有的部门--> <select id="getAllDepartments" resultType="department"> select * from department </select> <!--添加部门--> <insert id="saveDepartment" parameterType="department"> insert into department(id,department_name) values (#{id},#{departmentName}) </insert> <!--根据id查询部门--> <select id="getDepartmentById" resultType="department"> select * from department where id = #{id} </select> <!--修改部门--> <update id="updateDepartment"> update department set department_name = #{departmentName} where id = #{id} </update> </mapper> 以上就是springboot中如何使用poi导出数据到Excel中，文档中的代码经过我自己测试验证，可以正常实现导出。学无止境，祝各位在IT的知识海洋里自由地遨游！如果你在学习过程中，遇到问题了，可以在评论区留言，我看到后会第一时间进行回复。