选择性导出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">

<title>This is my JSP page</title>

<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<link href="assets/css/bootstrap.min.css" rel="stylesheet">
<style type="text/css">

html,body,#wrapper{
width: 100%;
height: 100%;
}

.navbar-inverse{
border-radius:0;
margin-bottom: 0;
}
.navbar-inverse .navbar-header{
width: 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{
width: 180px;
min-width: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">
<!-- Brand and toggle get grouped for better mobile display -->
<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">&times;</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="width: 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><!-- /.modal-content -->
</div><!-- /.modal-dialog -->
</div><!-- /.modal -->
</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;

var request = createRequest();
request.onreadystatechange = function(){
if(request.readyState == 4){
if(request.status == 200){
if(typeof config[‘success‘] == ‘function‘){
config[‘success‘](request.responseText);
}
}else{
if(typeof config[‘error‘] == ‘function‘){
config[‘error‘](request.responseText);
}
}
}else{
if(typeof config[‘loading‘] == ‘function‘){
config[‘loading‘]();
}
}
};
//将json的参数解析为字符串
var params = parseParam(config[‘data‘]);
if(config[‘method‘].toLowerCase() == ‘get‘){
if(config[‘url‘] && params){
//有?号
config[‘url‘] = config[‘url‘]+((config[‘url‘].indexOf(‘?‘)!=-1)?"&":"?")+params;
}
//打开请求
request.open(config[‘method‘],config[‘url‘],config[‘async‘]);
request.send(null);
}else if(config[‘method‘].toLowerCase() == ‘post‘){
//打开请求
request.open(config[‘method‘],config[‘url‘],config[‘async‘]);
request.setRequestHeader("Content-Type","application/x-www-form-urlencoded");
request.send(params);
}else{
if(w.console){
console.log(config[‘method‘]+" 方法没有被实现!");
}
}
};

//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);
}
}

选择性导出excel表中内容

原文：http://www.cnblogs.com/hwgok/p/5886816.html