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>

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏技术/开源

Enum引发的血案,反思

前几天公司产品更新版本,更新完后不少用户反应原先保存的report的一些表在新版本打开后设置突然变了,本来选的第六个,现在打开变成第四个了。领导要求赶紧查出原因...

20650
来自专栏一个会写诗的程序员的博客

《Springboot极简教程》系统异常全局统一处理:@ControllerAdvice plus @ExceptionHandler统一异常处理代码实例运行

用@ControllerAdvice和@ExceptionHandler两个注解来做异常的统一处理。

12230
来自专栏NetCore

[原创]Fluent NHibernate之旅二--Entity Mapping

接着上一篇,今天我们说说ORM中的Mapping。如果你要体验NHibernate的强大,首先你就要学会配置,包括SessionFactory和Mapping的...

24490
来自专栏Android相关

IjkPlayer初始化过程

最近调研做视频秒开,使用B站开源的ijkplayer作为播放器。ijkplayer基于ffmpeg的播放器。

39010
来自专栏分布式系统进阶

一个有限状态机的C++实现

我的博客即将搬运同步至腾讯云+社区,邀请大家一同入驻:https://cloud.tencent.com/developer/support-plan?invi...

91550
来自专栏java学习

Java每日一题1_关于JDBC

A、 JDBC提供了Statement、PreparedStatement 和 CallableStatement三种方式来执行查询语句,其中 Statemen...

9910
来自专栏IT派

Python;ipaddresst; 模块之概述

在本文中,我们会看一看Python 3.3及更高版本中提供的ipaddress模块。 本教程旨在为想知道如何在Python中解析和使用IP地址的网络工程师们提供...

15020
来自专栏Java开发者杂谈

For update带来的思考

​ 之所以想写这个专题,是因为最近在做一个抢占任务的实现。假设数据库很多个任务,在抢占发生之前任务的状态都是FREE。现在假设同时有一堆抢占线程开始工作,抢占线...

14430
来自专栏Kirito的技术分享

分布式限流

经典限流算法 在介绍分布式限流之前,先介绍经典限流算法。经过笔者自己的整理,核心的算法主要可以总结为以下两类四种: A类:计数器法,滑动窗口法 B类:令牌桶法,...

52690
来自专栏Java开发者杂谈

分布式改造剧集1

背景介绍 ​ 我所在的项目组,使用的技术一直是接近原始社会的:jdk1.6 + SpringMVC + hessian + Mybatis,当前最火的中间件技术...

30240

扫码关注云+社区

领取腾讯云代金券