Java实现Excel导出(查询数据库中的数据并以Excel文件导出)
数据库数据准备
CREATE TABLE `people` ( `id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, `status` int(11) DEFAULT NULL, `content` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
填充数据
insert into `people`(`id`,`name`,`status`,`content`) values (1,'zhangsan',1,'hello'),(2,'lisi',1,'word'),(3,'wangwu',1,'java');
简易页面
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta charset="UTF-8">
<title>测试</title>
</head>
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
<body>
<h1>文档导出</h1>
<form method="post" action="/">
<button type="button" id="peopleId" onclick="upload()">Excel导出</button>
</form>
</body>
<script>
function upload() {
$.ajax({
type: "post",
async: false,
cache: false,
processData: false,
contentType: false,
url: "/excel/export",
success: function (res) {
alert(res);
}
})
}
</script>
</html>
代码Demo
1、controller
@RestController @RequestMapping("/excel") public class ExcelController { @Autowired public ExcelService excelService; @RequestMapping("export") public String excelExport() { return excelService.excelExport(); } }
2、service
@Override public String excelExport() { //根据id查询数据 List<People> peopleList = excelMapper.getPeopleById(); if (peopleList == null || peopleList.isEmpty()) { return "failed"; } //生成文件路径 String newFilePath = "D:\\ExcelExport"; //文件名称 String fileName = "people.xlsx"; //输出流 OutputStream out = null; //Excel文件 XSSFWorkbook workBook = new XSSFWorkbook(); //Excel页脚 XSSFSheet sheet = workBook.createSheet("数据导出"); //设置列的宽度 sheet.setDefaultColumnWidth(16); //创建标题行 XSSFRow titleRow = sheet.createRow(0); String[] title = new String[]{"id", "name", "status", "content"}; //设置标题字体样式 XSSFCellStyle cellStyle = workBook.createCellStyle(); XSSFFont font = workBook.createFont(); font.setBold(true);//加粗 font.setFontHeightInPoints((short) 14);//设置字体大小 cellStyle.setFont(font); //设置标题列 for (int i = 0; i < title.length; i++) { //创建标题的单元格 XSSFCell titleCell = titleRow.createCell(i); //填充标题数值 titleCell.setCellValue(title[i]); //设置样式 titleCell.setCellStyle(cellStyle); } //填充数据 //第一行是标题所以要从第二行开始 for (int i = 0; i < peopleList.size(); i++) { People people = peopleList.get(i); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < title.length; j++) { XSSFCell titleCell = row.createCell(j); String exportKey = title[j]; switch (exportKey) { case "id": titleCell.setCellValue(people.getId()); break; case "name": titleCell.setCellValue(people.getName()); break; case "status": titleCell.setCellValue(people.getStatus()); break; case "content": titleCell.setCellValue(people.getContent()); break; } } } try { File file = new File(newFilePath + File.separator + fileName); out = new FileOutputStream(file); workBook.write(out); out.flush(); out.close(); } catch (Exception e) { log.info(e.getMessage()); } return "success"; }
3、mapper
<?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">
<mapper namespace="com.bootdemo.myspringboot.mapper.ExcelMapper">
<resultMap id="people" type="com.bootdemo.myspringboot.bean.People">
<result column="id" property="id"></result>
<result column="name" property="name"></result>
<result column="status" property="status"></result>
<result column="content" property="content"></result>
</resultMap>
<select id="getPeopleById" resultMap="people">
SELECT
*
FROM
people t
</select>
</mapper>
4、bean对象
public class People { private String id; private String name; private String status; private String content; 。。。setterAndGetter。。。 }
结果
1、先准备一个空文件夹（例如D盘的ExcelExport）
2、点击页面“Excel导出”，提示“成功”
3、查看目录中文件是否生成并打开查看文件内容
补充：
参考学习博客：https://blog.csdn.net/w893932747/article/details/89354979
