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

posted @ 2022-04-27 17:00  请别耽误我写BUG  阅读(3325)  评论(0)    收藏  举报