excel导出springboot

最新推荐文章于 2025-06-01 07:30:27 发布

猿原缘

最新推荐文章于 2025-06-01 07:30:27 发布

阅读量566

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/csdn_12345678910/article/details/80740357

本文介绍如何使用 Spring Boot 和 Apache POI 实现 Excel 数据导出功能，包括项目搭建、配置、实体类定义、持久层接口及实现、控制器编写等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先创建一个maven的web项目：excel_export_boot

pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.xy</groupId>
<artifactId>excel_export_boot</artifactId>
<packaging>war</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>excel_export_boot Maven Webapp</name>
<url>http://maven.apache.org</url>
<parent>
<groupId> org.springframework.boot</groupId>
<artifactId> spring-boot-starter-parent</artifactId>
<version>1.3.5.RELEASE</version>
</parent>

<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.tomcat.embed</groupId>
<artifactId>tomcat-embed-jasper</artifactId>
<scope>provided</scope>
</dependency>

<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>

</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>

<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.18</version>
<scope>provided</scope>
</dependency>


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


<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.0</version>
</dependency>

<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<finalName>excel_export_boot</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>

在src/main/resources中创建boot的配置文件

application.properties:

#链接数据库
spring.datasource.url=jdbc:mysql://192.168.26.3:3306/two
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-idle=10
spring.datasource.max-wait=10000
spring.datasource.min-idle=5
spring.datasource.initial-size=5

#mybatis的mapper.xml文件

#这个可以要可以不要，自己测试了感觉没用，可能有些地方没配置好

mybatis.mapper-locations=class:mapper/*.xml

#mvc的视图
#页面默认前缀目录
spring.mvc.view.prefix=/WEB-INF/jsp/
#页面默认后缀
spring.mvc.view.suffix=.jsp

创建springboot的启动类，注意包路径：

App.java:

package com.xy;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

//这个和UserMapper接口@Mapper是重复的，两者选其一，可以用这个，也可以用接口里面的

//@MapperScan(value="com.xy.mapper")
public class App {

public static void main(String[] args) throws Exception {
SpringApplication.run(App.class, args);
}

}

创建实体类：

user.java:

package com.xy.bean;

import lombok.Data;

@Data
public class User {

private Integer uid;

private String uname;

private String uaddress;

}

持久层：

接口UserMapper.java:

package com.xy.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import com.xy.bean.User;

//这里和上面的启动类扫描包路径填一个地方就行

@Mapper
public interface UserMapper {

public List<User> findAll();
}

映射文件：注意映射文件必须在UserMapper的同一个包下

UserMapper.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xy.mapper.UserMapper">

<select id="findAll" resultType="com.xy.bean.User">
select
*
from
users
</select>

</mapper>

工具类:

ExcelUtil:

package com.xy.utils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
* excel导出的工具类
* @author xy
*
*/
public class ExcleUtil {
/**
* 提取头公共的样式
* @param workbook
* @param fontSize
* @return
*/
public static HSSFCellStyle createTitleStyle(HSSFWorkbook workbook, int fontSize){
HSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//设置水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中

//创建字体样式
HSSFFont font = workbook.createFont();

font.setFontHeightInPoints((short)fontSize);

font.setBold(true);//设置字体样式如加粗，斜体等

font.setFontName("华文行楷");

style.setFont(font);

return style;
}
/**
* 提取小标题公共的样式
* @param workbook
* @param fontSize
* @return
*/
public static HSSFCellStyle createSecondTitleStyle(HSSFWorkbook workbook, int fontSize) {
HSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//设置水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中

// 创建一个字体样式

HSSFFont font = workbook.createFont();

font.setFontHeightInPoints((short)fontSize);

font.setBold(true);

style.setFont(font);

return style;
}
/**

* 提取表格头部的样式

*

* @param workbook

* @param fontSize

* @return

*/
public static HSSFCellStyle createDataHeaderStyle(HSSFWorkbook workbook, int fontSize) {

HSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//设置水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中

// 创建一个字体样式

HSSFFont font = workbook.createFont();

font.setFontHeightInPoints((short)fontSize);

font.setBold(true);

style.setFont(font);

return style;

}
/**

* 提取表格数据的样式

*

* @param workbook

* @param fontSize

* @return

*/
public static HSSFCellStyle createDataStyle(HSSFWorkbook workbook, int fontSize) {

HSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//设置水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中

// 创建一个字体样式

HSSFFont font = workbook.createFont();

font.setFontHeightInPoints((short)fontSize);

style.setFont(font);

return style;

}
}

业务层：

接口：UserService

package com.xy.service;

import java.util.List;

import com.xy.bean.User;

public interface UserService {

public List<User> findUser();
}

实体类：UserServiceImpl

package com.xy.service.impl;

import java.util.List;

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

import com.xy.bean.User;
import com.xy.mapper.UserMapper;
import com.xy.service.UserService;

@Service
public class UserServiceImpl implements UserService{

@Autowired
private UserMapper userMapper;

@Override
public List<User> findUser() {
// TODO Auto-generated method stub
return userMapper.findAll();
}

}

控制层：

UserController:

package com.xy.controller;

import java.net.URLEncoder;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.xy.bean.User;
import com.xy.service.UserService;
import com.xy.utils.ExcleUtil;

/**

*访问：http://127.0.0.1:8080/user/index

@Controller
@RequestMapping("/user")
public class UserController {

@Autowired
private UserService userService;
/**
* 跳转到index页面
*/
@RequestMapping("/index")
public String index(){
return "index";
}
/**
* 创建导出的方法
* @param response
* @param users
* @throws Exception
*/

@RequestMapping("/export")
public void exportUserExcel(HttpServletResponse response) throws Exception{
List<User> users = userService.findUser();
//声明文件名

String fileName="用户数据.xls";

//处理文件名

try {

fileName=URLEncoder.encode(fileName, "utf-8");

} catch (Exception e) {

e.printStackTrace();

}

//处理文件类型

response.setContentType("application/x-download");

// 设置下载的文件名

response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");

//创建一个工作表

HSSFWorkbook workbook = new HSSFWorkbook();//是一个空的，没有sheet的excel

//创建sheet并取名

HSSFSheet sheet = workbook.createSheet("用户数据");

//设置整个Excel表格的列的默认宽度

sheet.setDefaultColumnWidth((short) 30);

//开始写数据

int row=0;

//写第一行数据

HSSFRow titleRow = sheet.createRow(row);

//在第一行里面创建列

HSSFCell titleCell = titleRow.createCell(0);

//创建合并的对象

/**

* 参数1:开始行参数2：结束行参数3 开始列参数4 ：结束列的下标

*/

CellRangeAddress region=new CellRangeAddress(row, row, 0, 2);

//合并

sheet.addMergedRegion(region);

//设置合并后的列的的数据

titleCell.setCellValue("用户数据");

//从公共方法里面提取样式

HSSFCellStyle titleStyle = ExcleUtil.createTitleStyle(workbook, 25);

//把样式应该到Cell里面

titleCell.setCellStyle(titleStyle);





//第二行

row++;
HSSFRow secondTitleRow = sheet.createRow(row);

//在第二行里面创建列

HSSFCell secondCell = secondTitleRow.createCell(0);

CellRangeAddress region2=new CellRangeAddress(row, row, 0, 2);

//合并

sheet.addMergedRegion(region2);

//设置合并后的列的的数据

secondCell.setCellValue("总数:"+users.size()+"条，导出时间:"+new Date().toLocaleString());

//从公共方法里面提取样式

HSSFCellStyle secondTitleStyle = ExcleUtil.createSecondTitleStyle(workbook, 14);

//把样式应该到Cell里面

secondCell.setCellStyle(secondTitleStyle);





String[] headers={"用户ID","用户姓名","用户地址"};

//第三行是头部数据

row++;

HSSFRow rowHeader = sheet.createRow(row);

HSSFCellStyle dataHeaderStyle = ExcleUtil.createDataHeaderStyle(workbook, 12);

for (int i = 0; i < headers.length; i++) {

HSSFCell cell = rowHeader.createCell(i);

cell.setCellValue(headers[i]);

cell.setCellStyle(dataHeaderStyle);

}

//第四行到最后都是用户数据

HSSFCellStyle dataStyle = ExcleUtil.createDataStyle(workbook, 12);

for (int i = 0; i < users.size(); i++) {

row++;

User user = users.get(i);

HSSFRow rowData = sheet.createRow(row);

String [] data={user.getUid()+"",user.getUname(),user.getUaddress()};

for (int j = 0; j < data.length; j++) {

HSSFCell cell = rowData.createCell(j);

cell.setCellValue(data[j]);

cell.setCellStyle(dataStyle);

}

}

//把它写出去

workbook.write(response.getOutputStream());


}
}

页面：因为用到了mvc的视图，所以jsp放在WEB-INF/jsp下

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>
<a href="export">导出所有数据</a>
</h1>
</body>
</html>

只是为了看测试结果，很简单的一个a标签

访问：http://127.0.0.1:8080/user/index