选择性导出excel表中内容

package com.huawei.utils;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.huawei.dao.UsersDAO;

public class ExcelUtil {

/**
* 我们传入数据生成的excel的title 列的名字然后得到workbook
* @param title 生成的excel的名字
* @param headers 列的显示名称 header和include要一对一
* @param include 显示的列
* @param data 传入的数据
*
* @return wb 生成的WorkBook
*/
public static <T> Workbook export(String title,Map<String,String> params,List<T> data){
//生成workbook
Workbook wb = new HSSFWorkbook();

Sheet sheet = wb.createSheet("Sheet 1");

//构建title
Row title_row = sheet.createRow(0);
Cell title_cell = title_row.createCell(0);
//设置值
title_cell.setCellValue(title);
//设置样式
CellStyle title_style = wb.createCellStyle();
title_style.setAlignment(CellStyle.ALIGN_CENTER);
title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
title_cell.setCellStyle(title_style);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,params.size()-1));
//设置行高
title_row.setHeight((short)(20*20));

//构建表头
Row header_row = sheet.createRow(1);

int ii = 0;

for(String key:params.keySet()){
sheet.setColumnWidth(ii, 256*24);
Cell header_cell = header_row.createCell(ii);
//设置值
header_cell.setCellValue(params.get(key));
//设置样式
header_cell.setCellStyle(title_style);
ii++;
}

//得到一个样本主要用于反射结构信息
//T d = data.get(0);
//Class<?> clazz = d.getClass();

Class<?> clazz = data.get(0).getClass();

//得到列（Field）
//Field []fields = clazz.getDeclaredFields();

try{
for(int i=0;i<data.size();i++){
//表示一行
Row row = sheet.createRow(i+2);
T temp = data.get(i);
//遍历属性（列）
int j=0;
for(String key :params.keySet()){
//创建一个单元格
Cell cell = row.createCell(j);
Method method = clazz.getDeclaredMethod(BeanUtil.getter(key));
Object value = method.invoke(temp);

if(value==null){
cell.setCellValue("");
}else{
if(value instanceof Date){
//如果是日期类型需要强转而且需要设置格式
cell.setCellValue((Date)value);
//创建一个style
CellStyle style = wb.createCellStyle();
//创建一个数据格式化器
style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("yyyy年MM月dd日 hh:mm:ss"));
cell.setCellStyle(style);
}else if(value instanceof Number){ //判断是否为数字
cell.setCellValue(Double.parseDouble(value+""));
}else if(value instanceof String){
if(Pattern.matches("^\d+(\.\d+)?$", value+"")){ //判断是否为纯数字的字符串
cell.setCellValue(Double.parseDouble(value+""));
}else{
cell.setCellValue(value+"");
}
}
}
j++;
}
}
}catch (Exception e) {
e.printStackTrace();
}

return wb;
}

public static <T> Workbook export(String title,String []headers,String []include,List<T> data){
//生成workbook
Workbook wb = new HSSFWorkbook();

Sheet sheet = wb.createSheet("Sheet 1");

//构建title
Row title_row = sheet.createRow(0);
Cell title_cell = title_row.createCell(0);
//设置值
title_cell.setCellValue(title);
//设置样式
CellStyle title_style = wb.createCellStyle();
title_style.setAlignment(CellStyle.ALIGN_CENTER);
title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
title_cell.setCellStyle(title_style);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,headers.length-1));
//设置行高
title_row.setHeight((short)(20*20));

//构建表头
Row header_row = sheet.createRow(1);
for(int i=0;i<headers.length;i++){
sheet.setColumnWidth(i, 256*24);
Cell header_cell = header_row.createCell(i);
//设置值
header_cell.setCellValue(headers[i]);
//设置样式
header_cell.setCellStyle(title_style);
}

//得到一个样本主要用于反射结构信息
//T d = data.get(0);
//Class<?> clazz = d.getClass();

Class<?> clazz = data.get(0).getClass();

//得到列（Field）
//Field []fields = clazz.getDeclaredFields();

try{
for(int i=0;i<data.size();i++){
//表示一行
Row row = sheet.createRow(i+2);
T temp = data.get(i);
//遍历属性（列）
for(int j=0;j<include.length;j++){
//创建一个单元格
Cell cell = row.createCell(j);
Method method = clazz.getDeclaredMethod(BeanUtil.getter(include[j]));
Object value = method.invoke(temp);

if(value==null){
cell.setCellValue("");
}else{
if(value instanceof Date){
//如果是日期类型需要强转而且需要设置格式
cell.setCellValue((Date)value);
//创建一个style
CellStyle style = wb.createCellStyle();
//创建一个数据格式化器
style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("yyyy年MM月dd日 hh:mm:ss"));
cell.setCellStyle(style);
}else if(value instanceof Number){ //判断是否为数字
cell.setCellValue(Double.parseDouble(value+""));
}else if(value instanceof String){
if(Pattern.matches("^\d+(\.\d+)?$", value+"")){ //判断是否为纯数字的字符串
cell.setCellValue(Double.parseDouble(value+""));
}else{
cell.setCellValue(value+"");
}
}
}
}
}
}catch (Exception e) {
e.printStackTrace();
}

return wb;
}

/**
* 我们传入数据生成的excel的title 列的名字然后得到workbook
* @param title 生成的excel的名字
* @param headers 列的显示名称
* @param data 传入的数据
*
* @return wb 生成的WorkBook
*/

public static <T> Workbook export(String title,String []headers,List<T> data){
//生成workbook
Workbook wb = new HSSFWorkbook();

Sheet sheet = wb.createSheet("Sheet 1");

//构建title
Row title_row = sheet.createRow(0);
Cell title_cell = title_row.createCell(0);
//设置值
title_cell.setCellValue(title);
//设置样式
CellStyle title_style = wb.createCellStyle();
title_style.setAlignment(CellStyle.ALIGN_CENTER);
title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
title_cell.setCellStyle(title_style);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,headers.length-1));
//设置行高
title_row.setHeight((short)(20*20));

//构建表头
Row header_row = sheet.createRow(1);
for(int i=0;i<headers.length;i++){
sheet.setColumnWidth(i, 256*24);
Cell header_cell = header_row.createCell(i);
//设置值
header_cell.setCellValue(headers[i]);
//设置样式
header_cell.setCellStyle(title_style);
}

//得到一个样本主要用于反射结构信息
//T d = data.get(0);
//Class<?> clazz = d.getClass();

Class<?> clazz = data.get(0).getClass();

//得到列（Field）
Field []fields = clazz.getDeclaredFields();

try{
for(int i=0;i<data.size();i++){
//表示一行
Row row = sheet.createRow(i+2);
T temp = data.get(i);
//遍历属性（列）
for(int j=0;j<fields.length;j++){
//创建一个单元格
Cell cell = row.createCell(j);
Method method = clazz.getDeclaredMethod(BeanUtil.getter(fields[j]));

Object value = method.invoke(temp);

if(value==null){
cell.setCellValue("");
}else{
if(value instanceof Date){
//如果是日期类型需要强转而且需要设置格式
cell.setCellValue((Date)value);
//创建一个style
CellStyle style = wb.createCellStyle();
//创建一个数据格式化器
style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("yyyy年MM月dd日 hh:mm:ss"));
cell.setCellStyle(style);
}else if(value instanceof Number){ //判断是否为数字
cell.setCellValue(Double.parseDouble(value+""));
}else if(value instanceof String){
if(Pattern.matches("^\d+(\.\d+)?$", value+"")){ //判断是否为纯数字的字符串
cell.setCellValue(Double.parseDouble(value+""));
}else{
cell.setCellValue(value+"");
}
}
}
}
}
}catch (Exception e) {
e.printStackTrace();
}

return wb;
}

public static void main(String[] args) {
UsersDAO dao = new UsersDAO();

Workbook wb = export("用户信息", new String[]{"ID","用户名","年龄","邮箱"},new String[]{"id","username","age","email"}, dao.findAll());

try {
FileOutputStream out = new FileOutputStream("F:/text-export.xls");
wb.write(out);
out.flush();
out.close();
wb.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8">

.navbar-inverse{
border-radius:0;
margin-bottom: 0;
}
.navbar-inverse .navbar-header{
180px;
text-align: center;
border-right: 1px solid rgba(255,255,255,0.4);
}
.navbar-inverse .navbar-header .navbar-brand{
float: none;
display: inline-block;
}
.slider{
180px;
min-180px;
top:52px;
bottom: 0;
position: absolute;
background-color: rgba(0,0,0,0.00);
box-shadow:3px 0 6px rgba(0,0,0,0.3)
}
.slider .nav li a:hover,
.slider .nav li.active > a{
background-color: rgba(0,0,0,0.2)!important;
}
.slider .sub-menu li a{
padding-left:40px;
}
.slider .sub-menu{
/* border-bottom:1px solid #e5e5e5; */
border-top:1px solid #e5e5e5;
}
#wrap{
margin: 0 0 0 180px;
}

#wrap .wrap{
padding:5px 10px;
}

.panel .panel-footer{
background-color: #ffffff;
padding: 0 15px;
}

.panel-footer .pagination{
margin: 5px;
}

</style>
</head>

<body>
<div id="wrapper">
<nav class="navbar navbar-inverse">

<div class="navbar-header">
<a class="navbar-brand" href="#">后台系统</a>
</div>
<ul class="nav navbar-nav">
<li class="active"><a href="#">首页 <span class="sr-only">(current)</span></a></li>
<li><a href="views/login.jsp">登录</a></li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">更多 <span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="#">注册</a></li>
<li><a href="#">Another action</a></li>
<li><a href="#">Something else here</a></li>
<li role="separator" class="divider"></li>
<li><a href="#">Separated link</a></li>
<li role="separator" class="divider"></li>
<li><a href="#">One more separated link</a></li>
</ul>
</li>
</ul>
<ul class="nav navbar-nav pull-right">
<li><a href="logout" title="安全退出">安全退出</a></li>
</ul>
</nav>
<div class="slider">
<ul class="nav">
<li class="active">
<a href="javascript:void(0);" sub-menu="true"><i class="glyphicon glyphicon-user"></i> 用户管理 <i class="glyphicon glyphicon-chevron-down pull-right"></i></a>
<ul class="nav sub-menu">
<li><a href="users/usersController?_method=findAll"><i class="glyphicon glyphicon-list"></i> 用户列表</a></li>
<li class="active"><a href="#"><i class="glyphicon glyphicon-plus"></i> 用户添加</a></li>
</ul>
</li>
<li>
<a href="javascript:void(0);" sub-menu="true"><i class="glyphicon glyphicon-th-list"></i> 地址管理 <i class="glyphicon glyphicon-chevron-left pull-right"></i></a>
<ul class="nav sub-menu hidden">
<li ><a href="#"><i class="glyphicon glyphicon-list"></i> 地址列表</a></li>
<li><a href="#"><i class="glyphicon glyphicon-plus"></i> 地址添加</a></li>
</ul>
</li>

</ul>
</div>

<div id="wrap">
<div class="wrap">
<div class="panel panel-info">
<div class="panel-heading">
用户列表
<form action="users/usersController" style="display:inline;">
<div class="col-sm-4 pull-right" style="margin-top: -7px;">
<div class="input-group">
<input type="text" class="form-control" name="keyword" placeholder="搜索..." value="${page.keywords['username'] }">
<input type="hidden" name="_method" value="findAll">
<span class="input-group-btn">
<button class="btn btn-primary" type="submit">搜索</button>
</span>
</div>
</div>
</form>
<button type="button" class="btn btn-primary btn-sm pull-right" style="margin-top:-5px;" data-toggle="modal" data-target="#gridSystemModalLabel">年龄分析</button>
<button type="button" class="btn btn-primary btn-sm pull-right" style="margin-top:-5px;margin-right:15px;" onclick="exportExcel(this)">导出Excel</button>
</div>
<table class="table table-bordered table-hover" style="margin-bottom: 0">
<tr id="head">
<th>ID <input type="checkbox" name="export" value="id:ID" checked="checked" class="pull-left hidden"></th>
<th>用户名<input type="checkbox" name="export" value="username:用户名" checked="checked" class="pull-left hidden"></th>
<th>密码<input type="checkbox" name="export" value="password:密码" checked="checked" class="pull-left hidden"></th>
<th>年龄<input type="checkbox" name="export" value="age:年龄" checked="checked" class="pull-left hidden"></th>
<th>邮箱<input type="checkbox" name="export" value="email:邮箱" checked="checked" class="pull-left hidden"></th>
<th>操作</th>
</tr>
<c:forEach items="${page.data }" var="user">
<tr>
<td>${user.id }</td>
<td>${user.username }</td>
<td>${user.password }</td>
<td>${user.age }</td>
<td>${user.email }</td>
<td>
<a href="#" class="btn btn-info">详情</a>
<a href="users/usersController?_method=findById&id=${user.id }" class="btn btn-warning">修改</a>
<a href="users/usersController?_method=deleteById&id=${user.id }" onclick="return confirm('确认删除?');" class="btn btn-danger">删除</a>
</td>
</tr>
</c:forEach>
</table>
<div class="panel-footer text-right">
<ul class="pagination">
<li><span>${page.curr } / ${page.pageCount }</span></li>
<li ${(page.curr eq page.first)?'class="disabled"':'' }>
<a href="users/usersController?_method=findAll&keyword=${page.keywords['username'] }&curr=${page.first }" title="首页">首页</a>
</li>
<li ${(page.curr eq page.first)?'class="disabled"':'' }>
<a href="users/usersController?_method=findAll&keyword=${page.keywords['username'] }&curr=${page.prev }" title="上一页">上一页</a>
</li>

<c:forEach begin="${page.start }" end="${page.end }" var="num">
<c:choose>
<c:when test="${page.curr == num }">
<li class="active"><a title="第${num }页">${num }</a></li>
</c:when>
<c:otherwise>
<li><a href="users/usersController?_method=findAll&keyword=${page.keywords['username'] }&curr=${num }" title="第${num }页">${num }</a></li>
</c:otherwise>
</c:choose>
</c:forEach>

<li ${(page.curr eq page.last)?'class="disabled"':'' }>
<a href="users/usersController?_method=findAll&keyword=${page.keywords['username'] }&curr=${page.next }" title="下一页">下一页</a>
</li>
<li ${(page.curr eq page.last)?'class="disabled"':'' }>
<a href="users/usersController?_method=findAll&keyword=${page.keywords['username'] }&curr=${page.last }" title="尾页">尾页</a>
</li>
</ul>
</div>
</div>
</div>
</div>
<div class="modal fade" role="dialog" aria-labelledby="gridSystemModalLabel" id="gridSystemModalLabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
<h4 class="modal-title" id="gridSystemModalLabel">用户年龄分析</h4>
</div>
<div class="modal-body">
<div class="container-fluid">
<div class="row">
<div class="col-lg-12">
<div id="charts" style=" 100%;height:60%;"></div>
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
</div>
</div>
</div>
</div>
</body>
<script type="text/javascript" src="assets/js/jquery-1.11.3.min.js"></script>
<script type="text/javascript" src="assets/js/bootstrap.min.js"></script>
<script type="text/javascript" src="assets/js/echarts.min.js"></script>
<script type="text/javascript" src="assets/js/Utils.js"></script>
<script type="text/javascript">
$('#gridSystemModalLabel').on('shown.bs.modal',function(){
var charts = echarts.init(document.getElementById("charts"));

var option = {
title: {
text: '用户年龄分析',
left:'center'
},
tooltip: {},
legend: {
orient:'vertical',
left:'left',
data: ["衬衫","羊毛衫","雪纺衫","裤子","高跟鞋","袜子"]
},
series: [{
name: '用户年龄分析',
type: 'pie',
radius:'65%',
data: []
}]
};
charts.showLoading();
utils.ajax({
url:'users/usersController',
data:{"_method":"analyzeAge"},
success:function(data){
charts.hideLoading();
eval("data = "+data);
//为了得到名字
var names = [];
for(var i in data){
names.push(data[i]['name']);
}
//设置值
option.legend.data = names;
option.series[0].data = data;
charts.setOption(option);
}
});
});

var links = document.querySelectorAll(".slider li > a");
for(var i=0;i<links.length;i++){
links[i].index = i+1;
links[i].onclick = function(){
if(this.getAttribute("sub-menu")){
openOrClose(this);
}else{
window.sessionStorage.setItem("index",this.index);
for(var j=0;j<links.length;j++){
if(hasClass(links[j].parentNode,'active')){
utils.removeClass(links[j].parentNode,'active');
}
}
utils.addClass(this.parentNode,"active");
}
//return false;
};
}

if(window.sessionStorage.getItem("index")){
var index = window.sessionStorage.getItem("index");
for(var j=0;j<links.length;j++){
if(utils.hasClass(links[j].parentNode,'active')){
utils.removeClass(links[j].parentNode,'active');
}
if(j+1 == index){
utils.addClass(links[j].parentNode,"active");
}
}
}

//打开或是关闭菜单
function openOrClose(_dom){
var sub_menu = utils.getNextSibling(_dom);
var flag = _dom.querySelector(".pull-right");
//如果包含某些样式则做一些事情
if(utils.hasClass(sub_menu,"hidden")){
//去掉关于隐藏的class
utils.removeClass(sub_menu,"hidden");
//去掉菜单上的向左的箭头
utils.removeClass(flag,"glyphicon-chevron-left");
//添加上向下的箭头
utils.addClass(flag,"glyphicon-chevron-down");
}else{
//添加是隐藏的class
utils.addClass(sub_menu,"hidden");
//移除向下的箭头
utils.removeClass(flag,"glyphicon-chevron-down");
//添加上向左的箭头
utils.addClass(flag,"glyphicon-chevron-left");
}
}

/*
* 思路

当点击了按钮后按钮将记住当前的状态

如果是为submit的状态则为提交到后台

否则就是对前台dom的操作
*/

function exportExcel(_dom){
//得到状态
var state = _dom.getAttribute('state');
//得到所有的checkbox
var boxs = document.querySelectorAll("#head > th > input");
//判断状态
if(state && state=="submit"){
//提交的业务
_dom.setAttribute("state","");
//恢复原来的状态
utils.removeClass(_dom,"btn-warning");
utils.addClass(_dom,"btn-primary");
_dom.innerHTML = "导出Excel";
//创建一个form表单用于提交数据
var form = document.createElement("form");
form.action = "users/usersController";
form.method = "post";
//将input添加到form中
for(var k=0;k<boxs.length;k++){
form.appendChild(boxs[k].cloneNode(true));
utils.addClass(boxs[k],"hidden");
}
//创建一个隐藏域
var input = document.createElement("input")
input.type="hidden";
input.name="_method";
input.value="exportExcel";
form.appendChild(input) ;
//提交
form.submit();
return;
}
//将隐藏的checkbox显示出来
for(var k=0;k<boxs.length;k++){
utils.removeClass(boxs[k],"hidden");
}
//改变状态
utils.removeClass(_dom,"btn-primary");
utils.addClass(_dom,"btn-warning");
_dom.setAttribute("state","submit");
_dom.innerHTML = "导出?";

}
</script>
</html>

Utils.js

/**
* Created by Administrator on 16-1-6.
*/

(function(w){

function Utils(){}

Utils.prototype.getChilds = function(_selector){
var childs = [];
var c = _selector.childNodes;
for(var i=0;i<c.length;i++){
if(c[i].nodeType==1){
childs.push(c[i]);
}
}
return childs;
};

Utils.prototype.getNextSibling = function(_selector){
_selector = _selector.nextSibling;
while(_selector.nodeType!=1){
_selector = _selector.nextSibling;
}
return _selector;
};

Utils.prototype.getPrevSibling = function(_selector){
_selector = _selector.previousSibling;
while(_selector.nodeType!=1){
_selector = _selector.previousSibling;
}
return _selector;
};

Utils.prototype.validate = function(_form){

};

//判断是否包含没有个class样式
Utils.prototype.hasClass = function(_dom,_className){
if(_dom.className.indexOf(_className)!=-1){
return true;
}
return false;
};
//给指定的元素添加指定的样式
Utils.prototype.addClass = function(_dom,_className){
_dom.className = _dom.className+" "+_className;
};
//给指定的元素移除指定的样式
Utils.prototype.removeClass = function(_dom,_className){
_dom.className = _dom.className.replace(_className,"");
//将每次添加上的多余的空格去掉
_dom.className = _dom.className.replace(/s+/ig," ");
};

Utils.prototype.checkdata = function(_input){
//得到 input里面的自定义的正则表达式字段用于动态构建正则表达式
var regex = _input.getAttribute("regex");
eval("regex = /"+regex+"/ig;");
var span = document.getElementById(_input.name+"-msg");
//如果没有值则提示
if(_input.value){
if(regex.test(_input.value)){
span.innerHTML = "<font color='green'>"+_input.getAttribute("successmsg")+"</font>"
return true;
}else{
span.innerHTML = "<font color='red'>"+_input.getAttribute("errormsg")+"</font>"
return false;
}
}else{
span.innerHTML = "<font color='red'>"+_input.getAttribute("nullmsg")+"</font>";
return false;
}

};

/**
*
* @param config
*
* config{
* url:'testController',
* method:'get',
* data:{"name":"lisi","age":"20"}, //name=lisi&age=20
* async:true|false,
* success:function(){},
* error:function(){},
* loading:function(){}
* }
*
* $.ajax({
* url:'',
* success:function(data){
*
* //
* }
* });
*
*
*/
Utils.prototype.ajax = function(config){
// init config
config = config || {};
config['url'] = config['url'] || "";
config['method'] = config['method'] || "get";
config['data'] = config['data'] || {};
config['async'] = (String(config['async']) === 'false')?false:true;

//create request
function createRequest(){
var request;
//创建 request对象
if(window.XMLHttpRequest){ //兼容性
request = new XMLHttpRequest();
}else if(window.ActiveXObject){ //针对IE
request = new ActiveXObject("Msxml2.XMLHTTP");
}
return request;
}

function parseParam(param){
var result = [];
for(var key in param){
result.push(key+"="+param[key]);
}

return result.join("&");
}

w.utils = new Utils();

})(window);

package com.cdsxt.controller;

import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.ss.usermodel.Workbook;

import com.cdsxt.common.Page;
import com.cdsxt.po.Users;
import com.cdsxt.service.UsersService;
import com.cdsxt.utils.ExcelUtil;

/**
* Servlet implementation class UsersController
*/
public class UsersController extends HttpServlet {
private static final long serialVersionUID = 1L;

private UsersService usersService = new UsersService();

/**
* @see HttpServlet#HttpServlet()
*/
public UsersController() {
super();
// TODO Auto-generated constructor stub
}

/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
this.doPost(request, response);
}

/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
/**
* 将当前的doPost方法当作中专站
*/
String _method = request.getParameter("_method");

if("findAll".equalsIgnoreCase(_method)){
this.findAll(request, response);
}else if("deleteById".equalsIgnoreCase(_method)){
this.deleteById(request, response);
}else if("register".equalsIgnoreCase(_method)){
this.register(request, response);
}else if("findById".equalsIgnoreCase(_method)){
this.findById(request, response);
}else if("update".equalsIgnoreCase(_method)){
this.update(request, response);
}else if("analyzeAge".equalsIgnoreCase(_method)){
this.analyzeAge(request, response);
}else if("exportExcel".equalsIgnoreCase(_method)){
this.exportExcel(request, response);
}
}

/**
*
* 当从前台提交一个请求过来的时候应该去后台数据库将数据查询出来动态的生成WorkBook
*
* 直接将WorkBook的输出流定向到 response的输出流中去
*
*
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/

private void exportExcel(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{
response.setHeader("Content-Disposition", "attachment;filename="users.xls"");
//response.getOutputStream();
//Workbook wb =this.usersService.exportExcel();
Map<String, String> params = new HashMap<String, String>();
String []exports = request.getParameterValues("export");
for(String s:exports){
String [] kv = s.split(":");
params.put(kv[0], kv[1]);
}

//wb.write(response.getOutputStream());

Workbook wb = ExcelUtil.export("用户信息", params, this.usersService.findAll());
wb.write(response.getOutputStream());
wb.close();
}

private void analyzeAge(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{
// TODO Auto-generated method stub
response.getWriter().write(this.usersService.analyzeAge());

}

private void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{

Users curr = (Users) request.getSession().getAttribute("admin");

if(ServletFileUpload.isMultipartContent(request)){
Map<String, FileItem> map = new HashMap<String, FileItem>();
//创建工厂
DiskFileItemFactory factory = new DiskFileItemFactory();
//得到JVM提供的缓存目录
ServletContext context = this.getServletContext();
File repository = (File)context.getAttribute("javax.servlet.context.tempdir");
factory.setRepository(repository);
//创建ServletFileUpload
ServletFileUpload upload = new ServletFileUpload(factory);

//解析请求
try {
List<FileItem> items = upload.parseRequest(request);
//users = new Users();

for(FileItem item:items){
//form表单里面的每一个字段
map.put(item.getFieldName(), item);
}

Users u = new Users();

u.setId(Integer.parseInt(map.get("id").getString()));
u.setUsername(map.get("username").getString());
u.setPassword(map.get("password").getString());
u.setEmail(map.get("email").getString());
//u.setImage(Integer.parseInt(map.get("id").getString()));
u.setAge(Integer.parseInt(map.get("age").getString()));

//处理前一步的数据没有顺序的问题
//for(String key:map.keySet()){
//FileItem item = map.get(key);

//if(item.isFormField()){

//没有顺序

//}else{
//得到跟路径
String path = context.getRealPath("/");
//得到附件目录
File attachment = new File(path,"attachment/"+map.get("username").getString());
//如果没有就创建目录
System.out.println(attachment.getAbsolutePath());
System.out.println(attachment.exists());
if(!attachment.exists()){
attachment.mkdirs();
}

FileItem image = map.get("image");

File output = new File(attachment,System.currentTimeMillis()+"."+getSuffix(image.getName()));

image.write(output);
u.setImage("attachment/"+map.get("username").getString()+"/"+output.getName());
//System.currentTimeMillis()
this.usersService.update(u);
this.findAll(request, response);
//}
//}
//map.get("id");
} catch (Exception e) {
e.printStackTrace();
}
}

}

private String getSuffix(String name){
if(name!=null){
String[] suffixs = name.split("\.");
if(suffixs.length>1){
return suffixs[suffixs.length-1];
}
}
return "";
}

private void findById(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
if(id!=null && id.trim()!=""){
Users users = this.usersService.findById(Integer.parseInt(id));
request.setAttribute("users", users);
request.getRequestDispatcher("/views/update.jsp").forward(request, response);
return ;
}
}

private void register(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
Users users = null;
//判断是否有文件上传
if(ServletFileUpload.isMultipartContent(request)){
//创建工厂
DiskFileItemFactory factory = new DiskFileItemFactory();
//得到JVM提供的缓存目录
ServletContext context = this.getServletContext();
File repository = (File)context.getAttribute("javax.servlet.context.tempdir");
factory.setRepository(repository);
//创建ServletFileUpload
ServletFileUpload upload = new ServletFileUpload(factory);

//解析请求
try {
List<FileItem> items = upload.parseRequest(request);
users = new Users();

for(FileItem item:items){
//form表单里面的每一个字段

}
//得到跟路径
String path = context.getRealPath("/");
//得到附件目录
File attachment = new File(path,"attachment");
//如果没有就创建目录
if(!attachment.exists()){
attachment.mkdirs();
}

} catch (FileUploadException e) {
e.printStackTrace();
}
}

}

protected void deleteById(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String id = request.getParameter("id");
this.usersService.deleteById(Integer.parseInt(id));
response.sendRedirect("usersController?_method=findAll");

}

protected void findAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//List<Users> users = this.usersService.findAll();
//request.setAttribute("users", users);

String keyword = request.getParameter("keyword");

if(keyword==null){
keyword = "";
}

Page page = new Page();
String curr = request.getParameter("curr");
if(curr ==null || curr.trim().equals("")){
curr ="1";
}
page.getKeywords().put("username", keyword);
page.setCurr(Integer.parseInt(curr));
page = this.usersService.find4Page(page);
request.setAttribute("page", page);
request.getRequestDispatcher("/views/index.jsp").forward(request, response);
return;
}

}

package com.cdsxt.base;

import java.io.Serializable;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cdsxt.common.CallBack;
import com.cdsxt.common.Page;
import com.cdsxt.utils.BeanUtil;
import com.cdsxt.utils.DBUtil;

/**
* @author Administrator
*
*
* void executeQuery(String sql,Object[] parmas,CallBack callback) 通用的查询
* void executeUpdate(String sql,Object[] parmas) //通用的执行
*
* List<Object> findAll() //查询全部
* Object findOne(Integer id) //根据id查询一个
*
* void save(Object o) //给定一个对象然后存储到数据库
*
* void update(Object o) //根据一个对象更新数据库中所对应的字段
*
* void delete(Object o) //根据一个对象删除数据库中对应的记录
* void deleteById(Integer id) //根据传入的id删除
* void delete(String sql,Object[] params) //自定义删除
*
*
*
*
*/
public abstract class BaseDAO<T,ID extends Serializable> {

//目标类用于反射
protected Class<T> clazz;

@SuppressWarnings("unchecked")
public BaseDAO() {
ParameterizedType type = (ParameterizedType) this.getClass().getGenericSuperclass();
//得到类上面的泛型参数的实际类型
clazz = (Class<T>) type.getActualTypeArguments()[0];
}

/**
* 通用的查寻方法
* @param sql 给定一个sql语句
* @param params 给定与sql语句中相对应的参数
* @param callBack 用于处理结果集的回调函数
*/
public void executeQuery(String sql,Object []params,CallBack callBack){

// Connection connection = DBUtil.getConnection();
Connection connection = DBUtil.getConn();

PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = connection.prepareStatement(sql);
//处理参数
if(params!=null && params.length>0){
for(int i=0;i<params.length;i++){
ps.setObject(i+1, params[i]);
}
}
System.out.println("ORM:"+sql);
rs = ps.executeQuery();
//处理业务逻辑
callBack.execute(rs);

} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.close(rs,ps,connection);
}

}

/**
* 除了查询以外的所有操作
* @param sql 给定一个sql语句
* @param params 参数
*/
public void executeUpdate(String sql,Object []params){
// Connection connection = DBUtil.getConnection();
Connection connection = DBUtil.getConn();
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(sql);
//处理参数
if(params!=null && params.length>0){
for(int i=0;i<params.length;i++){
ps.setObject(i+1, params[i]);
}
}
System.out.println("ORM:"+sql);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.close(ps,connection);
}
}

/**
* 通用查询
* @param sql
* @param params
* @return
*/
public List<T> find(String sql,Object []params){

final List<T> result = new ArrayList<T>();
this.executeQuery(sql, params, new CallBack() {

@Override
public void execute(ResultSet rs) throws SQLException {
//处理结果
try {
//得到虚拟表的结构信息
ResultSetMetaData rsmd = rs.getMetaData();
while(rs.next()){
//每一行代表一个对象
T o = clazz.newInstance();
//每一个单元格代表对象中的一个属性
for(int i=0;i<rsmd.getColumnCount();i++){
//得到列明
String column_name = rsmd.getColumnName(i+1);
//根据列名去得到 clazz中的属性
Field field = clazz.getDeclaredField(column_name);
//得到 set方法 setUsername(String name)
Method method = clazz.getDeclaredMethod(BeanUtil.setter(field), field.getType());
method.invoke(o, rs.getObject(column_name));
}
result.add(o);
}
} catch (Exception e) {
e.printStackTrace();
}
}
});
return result;
}

/**
* 查找全部
* @return 返回一个结果集
*/
public List<T> findAll(){
//存储结果集
String sql = "SELECT * FROM "+BeanUtil.getTableName(clazz);
return this.find(sql, null);
}

public T findById(ID id){
String sql = "SELECT * FROM "+BeanUtil.getTableName(clazz) +" WHERE id=?";
List<T> result = this.find(sql, new Object[]{id});
return result.size()>0?result.get(0):null;
}

public List<T> findBy(String prop,Object param){
String sql = "SELECT * FROM "+BeanUtil.getTableName(clazz) +" WHERE "+prop+"=?";
return this.find(sql, new Object[]{param});
}

public Page find4Page(Page page){
//构建sql语句
String sql = "SELECT * FROM "+BeanUtil.getTableName(clazz)+" LIMIT ?,?";
//得到数据
List<T> data = this.find(sql, new Object[]{(page.getCurr()-1)*page.getPageSize(),page.getPageSize()});
page.setRowCount(this.getCount());
page.setData(data);
return page;

}

public Long getCount(){
String sql = "SELECT COUNT(1) FROM "+BeanUtil.getTableName(clazz);
return this.getCount(sql, null);

}
public Long getCount(String sql,Object[] params){

final long []count = new long[]{0L};

this.executeQuery(sql, params, new CallBack() {

@Override
public void execute(ResultSet rs) throws SQLException {
while(rs.next()){
count[0] = rs.getLong(1);
}

}
});

return count[0];
}

/**
* 将给定的对象持久化到数据库
* @param o 被持久化对象
*/
public void save(T o){
StringBuilder sb = new StringBuilder("INSERT INTO "+BeanUtil.getTableName(clazz)+" (");
StringBuilder values = new StringBuilder(" VALUES (");
//存储参数
List<Object> params = new ArrayList<Object>();
//得到所有的属性
Field []fields = clazz.getDeclaredFields();
try{
for(Field field:fields){
sb.append(BeanUtil.getColumnName(field)).append(",");
values.append("?,");
Method method = clazz.getDeclaredMethod(BeanUtil.getter(field));
//得到属性的值
params.add(method.invoke(o));
}
}catch (Exception e) {
e.printStackTrace();
}
//处理sql语句
sb.deleteCharAt(sb.length()-1).append(")");
values.deleteCharAt(values.length()-1).append(")");
sb.append(values);
this.executeUpdate(sb.toString(), params.toArray());
}

/**
* 更新更新的对象中一定要包含id
* @param o
*/
public void update(T o){
StringBuilder sb = new StringBuilder("UPDATE "+BeanUtil.getTableName(clazz)+" SET ");
//存储参数
List<Object> params = new ArrayList<Object>();
//得到所有的属性
Field []fields = clazz.getDeclaredFields();

Object id = null;

try{
for(Field field:fields){
//UPDATE USERS SET USERNAME=?,PASSWORD=?
String name = BeanUtil.getColumnName(field);
Method method = clazz.getDeclaredMethod(BeanUtil.getter(field));
//得到属性的值

Object value = method.invoke(o);

if("id".equals(name)){
id = value;
continue;
}
sb.append(name+"=?").append(",");
params.add(value);
}
}catch (Exception e) {
e.printStackTrace();
}
//处理sql语句
sb.deleteCharAt(sb.length()-1).append(" WHERE id=?");
if(id==null){
System.out.println("ID不能为空");
return;
}
params.add(id);
this.executeUpdate(sb.toString(), params.toArray());

}

@SuppressWarnings("unchecked")
public void delete(ID id){
//动态创建泛型数组
ID []ids = (ID[])Array.newInstance(id.getClass(), 1);
ids[0] =id;
this.delete(ids);
}

@SuppressWarnings("unchecked")
public void delete(T o){
try {
ID id = (ID)this.clazz.getDeclaredMethod("getId").invoke(o);
if(id!=null){
this.delete(id);
return ;
}
System.out.println("ID不能为空");
} catch (Exception e) {
e.printStackTrace();
}
}
public void delete(ID[] ids){
String sql = "DELETE FROM "+BeanUtil.getTableName(clazz) + " WHERE id in (?)";
this.executeUpdate(sql, ids);
}

public void delete(String sql,Object[] params){
this.executeUpdate(sql, params);
}
}