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>