## 1.准备工作
1. 创建springboot和配置maven我就不说了，说一下需要的依赖

```xml
<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</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>2.0.0</version>
		</dependency>

<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-json</artifactId>
		</dependency>
!

<dependency>
			<groupId>com.microsoft.sqlserver.jdbc</groupId>
			<artifactId>sqljdbc</artifactId>
			<version>4.0</version>
		</dependency>

```
需要的jar![](https://kuangstudy.oss-cn-beijing.aliyuncs.com/bbs/2021/11/02/kuangstudy0a4e1bcb-38c7-4582-8cbb-d3167dd747ab.png)

## 2. 进入代码实现
1. 创建一个xxx.properties文件，用来设置连接数据库等等的配置.

```java
server.port = 9000
# 设置连接数据的信息 MySQL
#spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
#spring.datasource.url = jdbc:mysql://数据库地址/数据库名字?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
## mysql账号
#spring.datasource.username = 账号
## mysql密码
#spring.datasource.password = 密码

# SqlServer数据库连接
spring.datasource.driver-class-name = com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url = jdbc:sqlserver://数据库地址;DatabaseName=数据库名字
spring.datasource.username = 账号
spring.datasource.password = 密码

spring.jackson.date-format = yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone = GMT+8

# 设置扫描的包
mybatis.type-aliases-package = com.example.demo.bean
# 设置扫描的xml文件
mybatis.mapper-locations = classpath:mapper/*.xml
```

2. 在resources文件夹下创建mapper文件夹，并在mapper文件夹下创建xxx.xml文件

```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">

<select id="getAllData" resultType="你定义实体Bean的路径">
        select
        字段名
        from
       	表名
        
    </select>
</mapper>

```

3. 创建实体Bean类这个类字段名字对应你的数据库字段名字就成

```java
package com.example.demo.bean;

import java.util.Date;
// 其他要写的方法自行创建，或者也可以使用Lombok，看自己
public class My_Data {
    private Integer id;
    private String name;
    private String password;
    ......

}

```

4. 创建dao层

```java
package com.example.demo.dao;

import com.example.demo.bean.My_Data;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface MyDataDao {
	// 这个方法名字使用你mapper.xml文件里的那个名字
    List<My_Data> getAllData();
}

```

5. service接口层

```java
package com.example.demo.service;

import com.example.demo.bean.My_Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public interface DownloadService {

HSSFWorkbook downloadExcel(List<My_Data> list);
}

```

6. service接口实现层

```java
package com.example.demo.service.impl;

import com.example.demo.bean.My_Data;
import com.example.demo.service.DownloadService;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class DownloadImpl implements DownloadService {

@Override
    public HSSFWorkbook downloadExcel(List<My_Data> list) {
        // excel表头设置名字
        String[] excelHeader = {"id", "姓名", "密码"};
        // 创建excel
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        // 创建表
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("数据表");
        // 创建第一行
        HSSFRow hssfRow = hssfSheet.createRow(0);
        // 创建样式
        HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
        hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中

// 遍历设置表头
        for (int i = 0; i < excelHeader.length; i++) {
            // 创建单元格第一行第i个单元格
            HSSFCell hssfCell = hssfRow.createCell(i);
            // 为其写入excelHeader数组的第i个表头名称
            hssfCell.setCellValue(excelHeader[i]);
            // 遍历样式
            hssfCell.setCellStyle(hssfCellStyle);
            // 设置自动调整宽度
            hssfSheet.autoSizeColumn(i);
            hssfSheet.setColumnWidth(hssfCell.getColumnIndex(), 100 * 50);

}

// 遍历追加数据库数据
        for (int i = 0; i < list.size(); i++) {
            // 从第i + i行开始遍历追加，因为前面第一行用做表头了
            hssfRow = hssfSheet.createRow(i + 1);
            // 将获取到的数据库数据遍历进实体类，在进行逐行的添加
            My_Data my_data = list.get(i);
            // 开始添加
            hssfRow.createCell(0).setCellValue(my_data.getId());
            hssfRow.createCell(1).setCellValue(my_data.getName());
            hssfRow.createCell(2).setCellValue(my_data.getPassword());
        }
        // 最后返回整个表
        return hssfWorkbook;
    }
}

```

7. controller层

```java
package com.example.demo.controller;

import com.example.demo.bean.My_Data;
import com.example.demo.dao.MyDataDao;
import com.example.demo.service.DownloadService;
import com.sun.xml.internal.ws.resources.HttpserverMessages;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.logging.SimpleFormatter;

@RestController
public class MyDataController {

@Autowired
    private MyDataDao myDataDao;

@Autowired
    private DownloadService downloadService;

@RequestMapping(value = "/my_data")
    public void getAll(HttpServletResponse response) throws IOException {
        // 获取数据库数据
        List<My_Data> list = myDataDao.getAllData();

// 下载excel
        HSSFWorkbook hssfWorkbook = downloadService.downloadExcel(list);
        OutputStream outputStream = response.getOutputStream();

String fileName = "my_data.xls";

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

hssfWorkbook.write(outputStream);
        outputStream.close();

}

```

Springboot+mybatis+sqlserver导出数据库数据并实现下载到excel

总共已有 0 条评论