Java导出数据生成Excel表格

事先准备：

工具类：

package com.wazn.learn.util.export;

import java.sql.Connection;

import java.sql.DriverManager;

public class DbUtil {

    private String dbUrl="jdbc:mysql://localhost:3306/basepro";

    private String dbUserName="user";

    private String dbPassword="user";

    private String jdbcName = "com.mysql.jdbc.Driver";

        public Connection getCon() throws Exception {

            Class.forName(jdbcName);

            Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);

            return con;

        }

        public void closeCon(Connection con) throws Exception {

            if (con != null) {

                con.close();

            }

        }

}

package com.wazn.learn.util.export;

import java.sql.ResultSet;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {

      public static void fillExcelData(ResultSet rs, Workbook wb, String[] headers) throws Exception {

            int rowIndex = 0; //定义行的初始值

            Sheet sheet = wb.createSheet(); //创建sheet页

            Row row = sheet.createRow(rowIndex++); //行数自增+1

            //将头信息填进单元格

            for (int i = 0; i < headers.length; i++) {

                row.createCell(i).setCellValue(headers[i]);

            }

            while (rs.next()) {

                row = sheet.createRow(rowIndex++); //增加行数

                System.out.println(row);

                for (int i = 0; i < headers.length; i++) { // 添加内容

                    row.createCell(i).setCellValue(rs.getObject(i + 1).toString());

                }

            }

        }

}

package com.wazn.learn.util.export;

import java.io.OutputStream;

import java.io.PrintWriter;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;

public class ResponseUtil {

    public static void write(HttpServletResponse response, Object o) throws Exception {

        response.setContentType("text/html;charset=utf-8");

        PrintWriter out = response.getWriter();

        out.println(o.toString());

        out.flush();

        out.close();

    }

    public static void export(HttpServletResponse response, Workbook wb, String fileName) throws Exception{

         //设置头  固定格式

        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1"));

        response.setContentType("text/html;charset=utf-8");

        OutputStream out = response.getOutputStream();

        wb.write(out);

        out.flush();

        out.close();

    }

}

Controller层：

package com.wazn.learn.controller.teachclass;

import java.sql.Connection;

import java.sql.ResultSet;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.shiro.authz.annotation.RequiresPermissions;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.context.annotation.Scope;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.ResponseBody;

import com.wazn.learn.dao.impl.ExportDao;

import com.wazn.learn.util.export.DbUtil;

import com.wazn.learn.util.export.ExcelUtil;

import com.wazn.learn.util.export.ResponseUtil;

import com.wordnik.swagger.annotations.ApiOperation;

@Controller

@Scope("prototype")

@RequestMapping("/teach")

public class ExportController {

    ExportDao exportDao;

    ExcelUtil excelUtil;

    @GetMapping("/page")

    public String stulook() {

        return "teach/course/export";

    }

        @SuppressWarnings("static-access")

        @ApiOperation(value = "导出Excel")

        @RequiresPermissions("upms:system:export")

        @RequestMapping(value = "/export", method = RequestMethod.GET)

        @ResponseBody

        public String export(HttpServletResponse response,String sdate,String edate) throws Exception {

            ExportDao exportDao = new ExportDao();

            DbUtil dbUtil = new DbUtil();

            Connection con = null;

            ExcelUtil excelUtil = new ExcelUtil();

            try {

                con = dbUtil.getCon();

                Workbook wb = new HSSFWorkbook();

                String headers[] = {"编号","学号","签到时间", "签到日期", "用户名","所属公司","职业"};

                ResultSet rs = exportDao.exportSign(con,sdate,edate);

                excelUtil.fillExcelData(rs, wb, headers);

                ResponseUtil.export( response, wb, "签到管理.xls");

            } catch (Exception e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            } finally {

                try {

                    dbUtil.closeCon(con);

                } catch (Exception e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

            }

            return null;

        }

        @SuppressWarnings("static-access")

        @ApiOperation(value = "导出Excel")

        @RequiresPermissions("upms:system:export")

        @RequestMapping(value = "/export2", method = RequestMethod.GET)

        @ResponseBody

        public String export2(HttpServletResponse response) throws Exception {

            ExportDao exportDao = new ExportDao();

            DbUtil dbUtil = new DbUtil();

            Connection con = null;

            ExcelUtil excelUtil = new ExcelUtil();

            try {

                con = dbUtil.getCon();

                Workbook wb = new HSSFWorkbook();

                String headers[] = { "签到日期","签到人数","请假人数"};

                ResultSet rs = exportDao.exportSign2(con);

                excelUtil.fillExcelData(rs, wb, headers);

                ResponseUtil.export( response, wb, "签到综合.xls");

            } catch (Exception e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            } finally {

                try {

                    dbUtil.closeCon(con);

                } catch (Exception e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

            }

            return null;

        }

}

dao层：

package com.wazn.learn.dao.impl;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

public class ExportDao {

    public ResultSet exportSign(Connection con, String sdate, String edate) throws Exception{

        String sql="select s.id,u.stunum, FROM_UNIXTIME(s.signtime/1000),s.signdate,u.nickname,u.company,u.job from teach_sign s join sys_user u on s.user_id=u.id ";

        if(sdate!=null&&sdate!=""){

            if(edate!=null&&edate!=""){

                 sql+=" where s.signdate>='"+sdate+"'  and s.signdate<='"+edate+"' ";

            }else{

                sql+=" where s.signdate>='"+sdate+"' ";

            }

        }else{

            if(edate!=null&&edate!=""){

                sql+=" where s.signdate<='"+edate+"' ";

            }else{

            }

        }

        StringBuffer sb = new StringBuffer(sql);

        PreparedStatement pstmt = con.prepareStatement(sb.toString());

        return pstmt.executeQuery();

    }

    public ResultSet exportSign2(Connection con) throws Exception{

        String sql="select signdate as signdate, count(distinct user_id)-count(leave1) as countuser,count(leave1) as countleave from teach_signs group by signdate";

        StringBuffer sb = new StringBuffer(sql);

        PreparedStatement pstmt = con.prepareStatement(sb.toString());

        return pstmt.executeQuery();

    }

}

前台页面：

两个不同的，传参数根据日期和不传参数

<%@ page language="java" contentType="text/html; charset=utf-8"

    pageEncoding="utf-8"%>

<%@ page

    import="org.springframework.security.core.context.SecurityContextHolder"%>

<%@ page import="com.wazn.learn.configure.security.CustomerUser"%>

<%

    String basePath = request.getContextPath();

    CustomerUser user = (CustomerUser) SecurityContextHolder.getContext().getAuthentication().getPrincipal();

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

<title>选择导出日期</title>

<script type="text/javascript"

    src="<%=basePath%>/res/js/jquery-1.12.4.min.js"></script>

<script src="<%=basePath%>/res/layui/layui.js" charset="utf-8"></script>

<link rel="stylesheet" href="<%=basePath%>/res/layui/css/layui.css"

    media="all">

</head>

<style type="text/css">

</style>

<body class="gray-bg">

<div class="layui-form-item " >

            <label class="layui-form-label">开始日期：</label>

            <div class="layui-input-block">

               <input type="text" class="layui-input" placeholder="请选择开始时间" id="sdate1" name="sdate1">

            </div>

        </div>

        <div class="layui-form-item" >

            <label class="layui-form-label">结束日期：</label>

            <div class="layui-input-block">

                <input type="text" class="layui-input" placeholder="请选择截止时间" id="edate1" name="edate1">

            </div>

        </div>

      <div class="layui-input-block">

        <a class="waves-effect waves-button" href="javascript:;" onclick="exportAction()"><button class="layui-btn" style="transform: translateY(-3px);"

                            data-type="reload">导出签到表格</button></a>

     </div>

<script>

    //导出Excel文件

    function exportAction(){

        var s = $('#sdate1').val();

        var e = $('#edate1').val();

        var str="sdate='"+s+"'&&edate='"+e+"'";

        window.open("<%=basePath%>/teach/export?sdate="+s+"&&edate="+e+" ");

    }

    layui.use(['table','form','laydate'], function(){

          var table = layui.table,

          form = layui.form,

          laydate = layui.laydate;;

          laydate.render({

                elem: '#sdate1',

                type: 'date'

              });

          laydate.render({

                elem: '#edate1',

                type: 'date'

              });

    });

</script>

</body>

</html>

<%@ page language="java" contentType="text/html; charset=utf-8"

    pageEncoding="utf-8"%>

<%@ page import="org.springframework.security.core.context.SecurityContextHolder"%>

<%@ page import="com.wazn.learn.configure.security.CustomerUser"%>

<%

    String basePath = request.getContextPath();

    CustomerUser user = (CustomerUser)SecurityContextHolder.getContext().getAuthentication().getPrincipal();

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

<script type="text/javascript" src="<%=basePath%>/res/js/jquery-1.12.4.min.js"></script>

<script src="<%=basePath%>/res/layui/layui.js" charset="utf-8"></script>

<link rel="stylesheet" href="<%=basePath%>/res/layui/css/layui.css" media="all">

<title>数据报表</title>

</head>

<body>

    <div style="margin: 0px; background-color: white; margin: 0 10px;">

        <blockquote class="layui-elem-quote" style="height: 45px">

            <div class="layui-col-md2">

                <a class="waves-effect waves-button" href="javascript:;" onclick="exportAction()"><button class="layui-btn" style="transform: translateY(-3px);"

                            data-type="reload">导出签到表格</button></a>

            </div>

        </blockquote>

    </div>

    <table class="layui-table"

        lay-data="{url:'<%=basePath%>/teach/course/getsignreport', page:true, id:'idTest', limit: 10,limits: [10,20,30]}"

        lay-filter="demo">

        <thead>

            <tr>

                <th lay-data="{field:'signdate', width:'30%',align:'center', sort: true}">签到日期</th>

                <th lay-data="{field:'user', width:'30%',align:'center',toolbar: '#bar1'}">签到人数</th>

                <th lay-data="{field:'leave', width:'30%',align:'center' ,toolbar: '#bar2'}">请假人数</th>

            </tr>

        </thead>

    </table>

<script>

    //导出Excel文件

    function exportAction(){

        window.open("<%=basePath%>/teach/export2");

    }

</script>

<script type="text/html" id="bar1">

<a class="layui-btn layui-btn-primary layui-btn-xs" id="test" lay-event="sign">{{d.countuser}}</a>

</script>

<script type="text/html" id="bar2">

<a class="layui-btn layui-btn-primary layui-btn-xs" id="test" lay-event="leave">{{d.countleave}}</a>

</script>

<script>

layui.use(['table','form','laydate','layer'], function(){

  var table = layui.table,

  form = layui.form,

  layer=layui.layer,

  laydate = layui.laydate;;

  laydate.render({

        elem: '#edate',

        type: 'datetime'

      });

  laydate.render({

        elem: '#sdate',

        type: 'datetime'

      });

  laydate.render({

        elem: '#sdate1',

        type: 'date'

      });

  laydate.render({

        elem: '#edate1',

        type: 'date'

      });

  //监听工具条

  table.on('tool(demo)', function(obj){

    var data = obj.data;

    if(obj.event === 'sign'){

        layer.open({

              title : "签到详情",

              type : 2,

              area: ['70%', '80%'],

              content : "<%=basePath%>/teach/course/signlook?leave=0&date="+data.signdate,

          })

    }else if(obj.event==='leave'){

        layer.open({

              title : "请假详情",

              type : 2,

              area: ['80%', '80%'],

              content : "<%=basePath%>/teach/course/signlook?leave=1&date="+data.signdate,

          })

    }

  });

  var $ = layui.$, active = {

    reload: function(){

      var demoReload = $('#demoReload');

      //执行重载

      table.reload('idTest', {

        page: {

          curr: 1 //重新从第 1 页开始

        },

        where: {

          name:  demoReload.val(),

          sdate:$('#sdate').val(),

          edate:$('#edate').val()

        }

      });

    }

  };

  $('.demoTable .layui-btn').on('click', function(){

    var type = $(this).data('type');

    active[type] ? active[type].call(this) : '';

  });

});

</script>

</body>

</html>