javaweb导出SQL数据到excel

javaweb导出SQL数据到excel

最近javaweb项目需要用到导出MySQL数据到excel的功能，于是记录一下我使用的方法，供大家参考。
关于POI组件使用的原理，大家可以参考这篇博客。

一、后台实现

这种方法是通过后台将数据写入excel，在通过字节流输出到前端页面下载，较为繁琐，不推荐。

所需jar包

1. 官网下载：地址
2. 积分多的大佬，可以下载我整理好的，点击下载

代码实现

1. 前台

<%--导出数据到excel--%>
<div>
  <form role="form" method="get" action="ExportServlet">
    <input type="submit" value="导出">
  </form>
</div>

[注意]：访问servlet只能用form提交方式，不能用ajax请求方式！！！（别问我咋知道的~~MD）
2. 后台servlet

• 功能描述: sql数据导出到excel
• 步骤
  1. 查询要导出的数据,放入List中（我这里是查询表中所有数据，可根据需求更改查询条件）
  2. 创建一个excel工作簿
  3. 创建sheet页,设置表格样式
  4. 在sheet中创建 行/单元格,向单元格中添加数据
  5. 控制台写入数据

@WebServlet("/ExportServlet")
public class ExportServlet extends HttpServlet {
    private StudentDao studentDao = new StudentDaoImpl();
    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        response.setHeader("Cache-Control", "no-cache");
        //1. 查询要导出的数据,放入List中
        List<student_info> student_list = studentDao.select();
        if (student_list.size() > 0) {
            //2. 创建一个excel工作簿
            String fileName = "学生信息表.xlsx";
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            XSSFWorkbook wb = new XSSFWorkbook();
            //3. 创建sheet页,设置表格样式
            XSSFSheet sheet = wb.createSheet("学生信息");
            sheet.setDefaultRowHeight((short) (2 * 256));       //设置行高
            sheet.setColumnWidth(0, 2500);    //设置列宽
            sheet.setColumnWidth(1,2500);
            sheet.setColumnWidth(2,2500);
            sheet.setColumnWidth(3,2500);
            sheet.setColumnWidth(4,2500);
            XSSFFont font = wb.createFont();                    //设置字体
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 16);
            //4. 在sheet中创建 行/单元格,向单元格中添加数据
            XSSFRow row = sheet.createRow(0);           //第0行
            XSSFCell cell = row.createCell(0);      //创建单元格
            cell.setCellValue("学生信息");
            CellRangeAddress region = new CellRangeAddress(0,0,0,4);
            sheet.addMergedRegion(region);                      //合并单元格

            row = sheet.createRow(1);                   //第一行
            cell = row.createCell(0);
            cell.setCellValue("姓名");
            cell = row.createCell(1);
            cell.setCellValue("性别");
            cell = row.createCell(2);
            cell.setCellValue("学号儿");
            cell = row.createCell(3);
            cell.setCellValue("电话");
            cell = row.createCell(4);
            cell.setCellValue("邮箱");
            XSSFRow rows;
            XSSFCell cells;
            /*插入数据*/
            for (int i=0; i<student_list.size(); i++) {
                student_info studentClass = student_list.get(i);
                rows = sheet.createRow(i+2);
                cells = rows.createCell(0);
                cells.setCellValue(studentClass.getName());
                cells = rows.createCell(1);
                cells.setCellValue(studentClass.getSex());
                cells = rows.createCell(2);
                cells.setCellValue(studentClass.getStudent_id());
                cells = rows.createCell(3);
                cells.setCellValue(studentClass.getTel());
                cells = rows.createCell(4);
                cells.setCellValue(studentClass.getEmail());
            }
            //5. 控制台写入数据
            try {
                OutputStream out = response.getOutputStream();
                wb.write(out);
                out.close();
                wb.close();
            } catch (IOException e) {
                System.out.println("error");
                e.printStackTrace();    //输出异常信息
            }
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request,response);
    }
}

效果展示

前台页面如下图：

点击导出按钮，即可弹出文件下载提示，如下图：

导出后的excel如下图所示：

二、前端实现

这种方法利用bootstrap前端框架，直接在前端导出，非常之方便，推荐用这种方法。

所需资源

1. bootstrap.min.js
2. jquery-3.4.1.min.js
3. bootstrap-table.min.js
4. bootstrap-table-zh-CN.min.js
5. bootstrap-table-export.min.js
6. tableExport.min.js
7. xlsx.core.min.js
8. FileSaver.min.js

下载地址 点击下载

代码实现

1. 页面引用资源

<script src="../plugin_unit/jquery-3.4.1.min.js"></script>
<script src="../plugin_unit/bootstrap-4.3.1/js/bootstrap.min.js"></script>
<script type="text/javascript" src="../plugin_unit/bootstrap-4.3.1/js/bootstrap-table.min.js"></script>
<script type="text/javascript" src="../plugin_unit/bootstrap-4.3.1/js/bootstrap-table-zh-CN.min.js"></script>
<script type="text/javascript" src="../plugin_unit/bootstrap-4.3.1/js/bootstrap-table-export.min.js"></script>
<script type="text/javascript" src="../plugin_unit/bootstrap-4.3.1/js/tableExport.min.js"></script>
<script type="text/javascript" src="../plugin_unit/bootstrap-4.3.1/js/xlsx.core.min.js"></script>
<script type="text/javascript" src="../plugin_unit/bootstrap-4.3.1/js/FileSaver.min.js"></script>

2. 新建一个table

<table id="logs_table" class="table table-striped">
    <thead>
    <tr>
        <th width="15%">账号</th>
        <th width="15%">姓名</th>
        <th width="40%">操作信息</th>
        <th width="30%">时间</th>
    </tr>
    </thead>
    <tbody id="logs_info_table">

    </tbody>
</table>

3. 查询数据库数据，放入table中
简单给出我的例子

前端发送请求

$.ajax({
    type: "POST",
    url: "../LogsServlet/search",
    dataType: "json",
    data: {time:time},
    success:function (data) {
        if (data.length > 0){
            table.html("");
            $(data).each(function () {
                var item = "<tr><td>"+this.user_id+"</td><td>"+this.user_name+"</td><td>"+this.logs+"</td><td>"+this.time+"</td></tr>";
                table.append(item);
            });
        }
    }
});

后端返回查询结果

String time = request.getParameter("time");
LogsDao logsDao = new LogsDao();
try {
    List select = logsDao.select(time);
    if (select.size()>0){
        // 序列化为json
        ObjectMapper mapper = new ObjectMapper();
        String json = mapper.writeValueAsString(select);
        System.out.println(json);
        // 响应结果
        response.setContentType("application/json;charset=utf-8");
        response.getWriter().write(json);
    }else
        response.getWriter().write("no_exist");
} catch (IOException e) {
    e.printStackTrace();
    response.getWriter().write("error");
}

就像这样

4. 添加导出按钮，绑定点击事件

function export_excel() {
    var time = $("#logs_time").val();
    if (time !== ""){
        $('#logs_table').tableExport({
            exportDataType: "all",
            exportTypes:'excel',
            exportOptions:{
                fileName: ''+time+'日志信息',       //文件名称设置
                worksheetName: 'Sheet1',           //表格工作区名称
                tableName: '日志信息表'
            }
        });
    }
}

这里的"logs_table"就是第二步的那个table的ID

就像这样

点击后自动弹出文件保存窗口，如图

打开excel，里面的内容就是页面table中的内容，到这里就ok了，
是不是非常的简单呢~