SpringBoot 导出Excel功能

SpringBoot 导出Excel功能

前言：话不多说，直接上代码。

一、pom.xml依赖

    	<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.1</version>
        </dependency>

二、UserService

    File createUserExcelFile();

三.UserServiceImpl

    @Override
    public File createUserExcelFile() {
        QueryWrapper<PatUser> wrapper = new QueryWrapper<>();
        wrapper.eq("is_delete",0).eq("status",0);
        List<PatUser> list= patUserMapper.selectList(wrapper);
        Workbook workbook = new XSSFWorkbook();
        //创建一个sheet，括号里可以输入sheet名称，默认为sheet0
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        int columnIndex = 0;
        row0.createCell(columnIndex).setCellValue("No");
        row0.createCell(++columnIndex).setCellValue("姓名");
        row0.createCell(++columnIndex).setCellValue("手机号");
        row0.createCell(++columnIndex).setCellValue("性别");
        row0.createCell(++columnIndex).setCellValue("婚姻状况");
        row0.createCell(++columnIndex).setCellValue("身份证号");
        row0.createCell(++columnIndex).setCellValue("地址");
        row0.createCell(++columnIndex).setCellValue("邮箱");

        for (int i = 0; i < list.size(); i++) {
            PatUser patVip=list.get(i);
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < columnIndex + 1; j++) {
                row.createCell(j);
            }
            columnIndex = 0;
            row.getCell(columnIndex).setCellValue(i + 1);
            row.getCell(++columnIndex).setCellValue(patVip.getWxName());
            row.getCell(++columnIndex).setCellValue(patVip.getPhone());
            row.getCell(++columnIndex).setCellValue(patVip.getGender());
            row.getCell(++columnIndex).setCellValue(patVip.getMarriage());
            row.getCell(++columnIndex).setCellValue(patVip.getCardId());
            row.getCell(++columnIndex).setCellValue(patVip.getAddress());
            row.getCell(++columnIndex).setCellValue(patVip.getEmail());

      }
        return PoiUtils.createExcelFile(workbook, "用户信息");
    }

三.UserController

	 /**
     * 导出患者列表
     * @param res
     */
    @RequestMapping(value="/deriveExcelUser")
    public void deriveExcelUser(HttpServletResponse res) {
        File file = patUserService.createUserExcelFile();
        FileUtils.downloadFile(res, file, file.getName());
    }

四.FileUtils工具类

package com.jkjl.common.util;

import javax.servlet.http.HttpServletResponse;
import java.io.*;

/**
 * @author YQC
 * @Package com.jkjl.common.util
 * @date 2021/5/19 18:10
 * @Copyright © 2020-2021 河南伽林云智慧健康科技产业发展有限公司
 */
public class FileUtils {
    /**
     *下载文件
     * @param response
     * @param file
     * @param newFileName
     */
    public static void downloadFile(HttpServletResponse response, File file, String newFileName) {
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + new String(newFileName.getBytes("ISO-8859-1"), "UTF-8"));
            BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
            InputStream is = new FileInputStream(file.getAbsolutePath());
            BufferedInputStream bis = new BufferedInputStream(is);
            int length = 0;
            byte[] temp = new byte[1 * 1024 * 10];
            while ((length = bis.read(temp)) != -1) {
                bos.write(temp, 0, length);
            }
            bos.flush();
            bis.close();
            bos.close();
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

五.PoiUtils工具类

package com.jkjl.common.util;

import org.apache.poi.ss.usermodel.Workbook;

import java.io.*;

/**
 * @author YQC
 * @Package com.jkjl.common.util
 * @date 2021/5/19 18:36
 * @Copyright © 2020-2021 河南伽林云智慧健康科技产业发展有限公司
 */
public class PoiUtils {
    /**
     * 生成Excel文件
     * @param workbook
     * @param fileName
     * @return
     */
    public static File createExcelFile(Workbook workbook, String fileName) {
        OutputStream stream = null;
        File file = null;
        try {
            //用了createTempFile，这是创建临时文件，系统会自动给你的临时文件编号，所以后面有号码，你用createNewFile的话就完全按照你指定的名称来了
            file = File.createTempFile(fileName, ".xlsx");
            stream = new FileOutputStream(file.getAbsoluteFile());
            workbook.write(stream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //这里调用了IO工具包控制开关
            closeQuietly(workbook);
            closeQuietly(stream);
        }
        return file;
    }

    /**
     * 关闭对象，连接
     * @param closeable
     */
    public static void closeQuietly(final Closeable closeable) {
        try {
            if (closeable != null) {
                closeable.close();
            }
        } catch (final IOException ioe) {
            // ignore
        }
    }
}