​ 文件导出

​
文件导出
1、Apache POI的maven坐标

2、Controller层

package com.zsh.controller;

import com.zsh.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;

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

@Autowired
private UserService userService;

/**
 * 导出7天的报表数据
 * @param response
 */
@GetMapping("/export")
public void export(HttpServletResponse response){
    userService.export(response);
}

}

3、Service层

package com.zsh.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.zsh.pojo.User;

import javax.servlet.http.HttpServletResponse;

public interface UserService extends IService {
void export(HttpServletResponse response);
}
4、拷贝模板

将报表模板.xlsx拷贝到项目的resources/template目录中

5、ServiceImpl

package com.zsh.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zsh.mapper.UserMapper;
import com.zsh.pojo.User;
import com.zsh.pojo.UserVO;
import com.zsh.service.UserService;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.List;

@Service
public class UserServiceImpl extends ServiceImpl implements UserService {

@Autowired
private UserMapper userMapper;

@Override
public void export(HttpServletResponse response) {
    // 获取前七天的起止日期
    // 假设今天是2023-12-31  [begin：2023-12-23    end：2023-12-30]
    LocalDate begin = LocalDate.now().minusDays(7);
    LocalDate end = LocalDate.now().minusDays(1);

    // 获取开始的最早时间和结束的最晚时间
    LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
    LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);

    // 查询日期范围内的数据
    List<UserVO> userList =  userMapper.selectTime(beginTime,endTime);

    // 设置HTTP相应的MIME类型，指定Excel
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    // 通知浏览器以附件的姓氏下载文件，并告知文件名称，设置URLEncoder保存时不乱码
    response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("开朗觉觉.xlsx", StandardCharsets.UTF_8));

    // 填充数据到报表中
    InputStream xlsx = this.getClass().getClassLoader().getResourceAsStream("xlsx\\开朗觉觉.xlsx");

    try {
        // 创建工作簿
        XSSFWorkbook excel = new XSSFWorkbook(xlsx);

        // 获得Excel文件中的Sheet页
        XSSFSheet sheet1 = excel.getSheet("Sheet1");

        // 获取学生总数第一行(因为第一行已存在，所以直接获取)
        XSSFRow row1 = sheet1.getRow(0);

        // 获取第3个单元格(因为这个单元格不存在，所以创建单元格)
        row1.createCell(5).setCellValue(userList.size());  // 学生总数
        System.out.println(userList.size());
        // 循环打印每一天进班的学生(当前算的7天的)
        for (int i = 0; i < 7;i++){
            // 获取当天的时间
            LocalDate date = begin.plusDays(i);

            // 获取当天最小时间和最大时间的时分秒
            beginTime = LocalDateTime.of(date, LocalTime.MIN);
            endTime = LocalDateTime.of(date, LocalTime.MAX);

            // 准备当天的数据
            userList = userMapper.selectTime(beginTime, endTime);

            // 获取要添加数据的行数(每循环一次获取下一行)
            XSSFRow rown = sheet1.createRow(2 + i);

            // 因为查询到的结果在集合中，所以要遍历获取
            for (UserVO userVO : userList) {
                // 设置第一个单元格的值(入学时间)
                rown.createCell(0).setCellValue(date.toString());

                // 设置第二个单元格的值(id)
                rown.createCell(1).setCellValue(userVO.getId());

                // 设置第三个单元格的值(姓名)
                rown.createCell(2).setCellValue(userVO.getName());

                // 设置第四个单元格的值(年龄)
                rown.createCell(3).setCellValue(userVO.getAge());

            }
        }
        // 通过输出流文件下载到客户浏览器中
        ServletOutputStream out = response.getOutputStream();
        excel.write(out);

        //关闭资源
        out.flush();
        excel.close();
        out.close();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

}
6、Mapper

package com.zsh.mapper;

@Mapper
public interface UserMapper extends BaseMapper {

List<UserVO> selectTime(LocalDateTime beginTime, LocalDateTime endTime);

}
7、Mapper.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="selectTime" resultType="com.zsh.pojo.UserVO">
    select * from user where date_time between #{beginTime} and #{endTime}
</select>