一、引入依赖
<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>
最后,感谢观看。