springboot将数据库中数据以excel的形式传到前端_web中如何操作excel文件并返回给前端

springboot将数据库中数据以excel的形式传到前端

依赖

 		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

主要用到该依赖下的两个类：XSSFWorkbook和HSSFWorkbook类。

XSSFWorkbook是Excel中的xlsx版本，HSSFWorkbook是xls版本。

这里使用的是XSSFWorkbook类

业务层代码

@Service
public class DeviceServiceImpl implements DeviceService {
	@Resource
    private DeviceMapper deviceMapper;

	@Override
    public XSSFWorkbook getDeviceDataByExcel() {
    	//先查出数据库数据
        List<DeviceData> list = deviceMapper.getDeviceData();
        XSSFWorkbook wb = new XSSFWorkbook();
        //创建一张表
        Sheet sheet = wb.createSheet("deviceData");
        //创建第一行，起始为0
        Row titleRow = sheet.createRow(0);
        //第一行，给每列添加标题
        titleRow.createCell(0).setCellValue("序号");
        titleRow.createCell(1).setCellValue("液位");
        titleRow.createCell(2).setCellValue("温度");
        titleRow.createCell(3).setCellValue("气压");
        titleRow.createCell(4).setCellValue("时间");
        //cell用于标记序号，若不用序号可以去掉
        int cell = 1;
        for (DeviceData data : list) {
            //从第二行开始保存数据
            Row row = sheet.createRow(cell);
            row.createCell(0).setCellValue(cell);
            row.createCell(1).setCellValue(data.getWaterlevel());
            row.createCell(2).setCellValue(data.getTemperature());
            row.createCell(3).setCellValue(data.getAirpressure());
            row.createCell(4).setCellValue(data.getTime());
            cell++;
        }
        return wb;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

控制层代码

@RestController
public class DeviceController {
    @Resource
    private DeviceServiceImpl deviceService;

    @PostMapping("/getDeviceData")
    public void goodsExcel(HttpServletResponse response,) {                         
        XSSFWorkbook wb = deviceService.getDeviceDataByExcel();
        String fileName = "deviceData.xlsx";
        OutputStream outputStream = null;
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            //设置ContentType请求信息格式
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

测试

之后用postman用post方法请求/getDeviceData该地址，或将请求方法改为get用浏览器请求/getDeviceData该地址，就能得到excel文件了
参考：https://www.cnblogs.com/lin02/p/11648899.html