将数据库表中的数据导出到excel中

一、引入依赖

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

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

二、工具类

package com.wb.srpingboot.demoz.utils;

import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Arrays;
import java.util.Collection;
import java.util.Iterator;

/**
 *
 * <p>
 * Description: 将数据库表中信息导出为excel文件工具类
 * </p>
 *
 * @author 阿萨
 * @version v1.0.0
 * @since 2020-08-26 13:45:12
 * @see com.wb.srpingboot.demoz.utils
 *
 */
public class ExportExcel<T> {
    public void exportExcel(String[] headers, Collection<T> dataset, String fileName, HttpServletResponse response) {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(fileName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);
        // 产生表格标题行
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        try {
            // 遍历集合数据，产生数据行
            Iterator<T> it = dataset.iterator();

            int index = 0;
            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                T t = (T) it.next();
                // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
                Field[] fields = t.getClass().getDeclaredFields();
                for (short i = 0; i < headers.length; i++) {
                    XSSFCell cell = row.createCell(i);
                    Field field = fields[i];
                    String fieldName = field.getName();
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(t, new Object[]{});
                    // 判断值的类型后进行强制类型转换
                    String textValue = null;
                    // 其它数据类型都当作字符串简单处理
                    if (value != null && value != "") {
                        textValue = value.toString();
                    }
                    if (textValue != null) {
                        XSSFRichTextString richString = new XSSFRichTextString(textValue);
                        cell.setCellValue(richString);
                    }
                }
            }
            getExportedFile(workbook, fileName, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private void getExportedFile(XSSFWorkbook workbook, String name, HttpServletResponse response) {
        BufferedOutputStream fos = null;
        try {
            String fileName = name + ".xlsx";
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
            fos = new BufferedOutputStream(response.getOutputStream());
            workbook.write(fos);
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fos != null) {
                try {
                    fos.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

三、controller代码

记得得用自己的service

/**
     * TODO: 将数据库表中的数据导入成为excel文件
     * @param response 。。。。
     */
    @RequestMapping(value = "/download", produces = { "application/json;charset=UTF-8" }, method = RequestMethod.GET)
    @ResponseBody
    public void downloadFile(HttpServletResponse response){
        //获取数据库的信息，不加分页
        List<User> userList = userService.queryAll();
        //创建导出类
        ExportExcel<User> ee= new ExportExcel<User>();
        //创建表头
        String[] headers = {"序号","姓名", "账号", "密码" };
        //默认导出表名
        String fileName = "用户信息表";
        //调用导出方法
        ee.exportExcel(headers,userList,fileName,response);
    }

说一嘴，如果你在html中发现导出不好使，建议使用原生的js，我不知道为啥ajax不好使，至于VUE我压根不会。
这就是一个简但的导出。

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script src="../js/jquery.js"></script>
</head>

<body>
    <button type="button" onclick="downLoad()" >导出信息</button>
</body>
<script>
    function downLoad() {
        location.href = "/user/download"
    }
</script>
</html>

最后，感谢观看。