第一步引入EasyUi的库
<link href="<%=request.getContextPath() %>/web/css/icon.css" type="text/css"rel="stylesheet">
<link href="<%=request.getContextPath() %>/web/css/easyui.css"type="text/css" rel="stylesheet">
<script src="<%=request.getContextPath() %>/web/js/easyui/jquery.easyui.min.js"></script>
<script src="<%=request.getContextPath() %>/web/js/easyui/easyui-lang-zh_CN.js"></script>
EasyUi 基于jquery 的必须引入jquery包
<script src="<%=request.getContextPath() %>/web/js/jquery-1.8.3.min.js"></script>
第二步写主要代码(实现实现方式一般有两种 第一种在html标签里直接写,第二种用js代码是现,我个人喜欢第二种)
<table id="dg" style="width: 100%"></table>
加载页面时加载数据
$(function() { //数据表格加载 $('#dg').datagrid({ url:'<%=request.getContextPath() %>/user.do?onSearchUser', pagination:true, iconCls:"icon-edit", singleSelect:true, onClickCell:onClickCell, toolbar: "#tb", fitColumns:true, checkOnSelect:false, selectOnCheck:false, columns:[[ {field:'ck',title:'CK',checkbox:true}, {field:'ID',title:'ID',hidden:true}, {field:'NAME',title:'Name',editor:'text'}, {field:'PASSWORD',title:'PASSWORD',align:'right',editor:'text'}, {field:'PHONE',title:'PHONE',align:'right',editor:'text'}, {field:'STATUS',title:'STATUS',align:'right',editor:'text'}, {field:'EMAIL',title:'EMAIL',align:'right',editor:'text'}, {field:'CREATE_DATE',title:'CREATEDATE',align:'right', formatter:function(val,rec){ return formattime(val);//日期格式化函数,写的不好自己写哦! }}, {field:'LOGINID',title:'LOGINID',align:'right',editor:'text'} , {field:'SEX',title:'SEX',align:'right',editor:'text'}, {field:'REMARKS',title:'REMARKS',align:'right',editor:'text'} ]], loadMsg: "查询数据请稍候。。。" }); var pager=$('#dg').datagrid('getPager'); //分页组件 $(pager).pagination({ pageSize:15, pageList: [5,10,15,20,30], layout:['list','sep','first','prev','links','next','last','sep','refresh'], onSelectPage:function(pageNumber, pageSize){ Oseasch(pageNumber, pageSize); } });
第三步后台代码实现,看下这地址(我用的是SpringMVC+Spring+MyBatis)
url:'<%=request.getContextPath() %>/user.do?onSearchUser',
找到类 UserController 里边的 onSearchUser
@Controller
@RequestMapping(value="/user.do",method=RequestMethod.POST)
public class UserController {
@Autowired
private UserServiceImpl userServiceImpl;
@RequestMapping(params="onSearchUser",method=RequestMethod.POST)
@ResponseBody
public JSONObject onSearchUser(HttpServletRequest request){
String pageNumber = request.getParameter("pageNumber");
String pageSize = request.getParameter("pageSize");
String Name = request.getParameter("Name");
String LoginId = request.getParameter("LoginId");
String StateCreateTime = request.getParameter("StateCreateTime");
String EndCreateTime = request.getParameter("EndCreateTime");
// 当前页
int pageNo = Integer.parseInt(((pageNumber == null || "0".equals(pageNumber)) ? "1" : pageNumber).toString());
// 每页显示条数
int rows = Integer.parseInt((pageSize == null || "0".equals(pageSize)) ? "15" : pageSize);
//参数封装
Map<String, Object> params=new HashMap<String, Object>();
params.put("Name", Name);
params.put("LoginId", LoginId);
params.put("StateCreateTime", StateCreateTime);
params.put("EndCreateTime", EndCreateTime);
List list = userServiceImpl.findUserList(params,pageNo,rows);
int Count = userServiceImpl.findAllUserCount(params);
JSONObject result = new JSONObject();
result.put("rows", list);
result.put("total", Count);
return JSONObject.fromObject(result);
}
}
提示:这里list中你可以认为是个jsonArray 对象 里边的键必须和 js columns 属性的field 值对应上才能正确显示
再说下service层吧
List list = userServiceImpl.findUserList(params,pageNo,rows); int Count = userServiceImpl.findAllUserCount(params)
//定义一个常量
private static final String nameSpace = "userHandleService";
public List findUserList(Map<String, Object> params,int pageNo,int pageSize) {
return commonDAO.findByPage(nameSpace, "findUserList", params, pageNo, pageSize);
}
public int findAllUserCount(Map<String, Object> params) {
return (Integer) commonDAO.findByUnique(nameSpace, "findUserCount", params);
}
找到commonDAO
public List findByPage(String nameSpace, String sqlId, Object params, int pageNo, int pageSize) {
RowBounds row = new RowBounds( (pageNo - 1) * pageSize, pageSize);
return mybatisTemplate.selectList(nameSpace + "." + sqlId, params, row);
}
public Object findByUnique(String nameSpace, String sqlId, Object params) {
Object object = null;
if (params == null) {
object = mybatisTemplate.selectOne(nameSpace + "." + sqlId);
} else {
object = mybatisTemplate.selectOne(nameSpace + "." + sqlId, params);
}
mybatisTemplate.clearCache();
return object;
}
(这里spring的注入管理我就不细说了)
找到映射xml
<mapper namespace="userHandleService"> <select id="findUserList" parameterType="java.util.Map" resultType="java.util.HashMap"> SELECT ID, NAME,PASSWORD,PHONE,MOBILE, MSN,STATUS,EMAIL,QQ,CREATE_DATE,POSTCODE,ADDRESS,LOGINID,SEX,REMARKS FROM T_SYS_USER t WHERE 1=1 <if test="Name != null and Name != ''"> AND UPPER(t.NAME) LIKE '%' || UPPER(#{Name}) ||'%' </if> <if test="LoginId != null and LoginId != ''"> AND t.LOGINID=#{LoginId} </if> <if test="StateCreateTime != null and StateCreateTime != ''"> AND TO_DATE(t.CREATE_DATE)>=TO_DATE(#{StateCreateTime}, 'YYYY-MM-DD') </if> <if test="EndCreateTime != null and EndCreateTime != ''"> <![CDATA[ AND TO_DATE(t.CREATE_DATE)<=TO_DATE(#{EndCreateTime}, 'YYYY-MM-DD') ]]> </if> </select> <select id="findUserCount" resultType="java.lang.Integer"> select count(*) FROM T_SYS_USER t where 1=1 <if test="Name != null and Name != ''"> AND UPPER(t.NAME) LIKE '%' || UPPER(#{Name}) ||'%' </if> <if test="LoginId != null and LoginId != ''"> AND t.LOGINID=#{LoginId} </if> <if test="StateCreateTime != null and StateCreateTime != ''"> AND t.CREATE_DATE>=TO_DATE(#{StateCreateTime}, 'yyyy-MM-dd') </if> <if test="EndCreateTime != null and EndCreateTime != ''"> <![CDATA[ AND t.CREATE_DATE<=TO_DATE(#{EndCreateTime}, 'yyyy-MM-dd') ]]> </if> </select>
大家看清这句代码 <mapper namespace="userHandleService"> userHandleService在service 层的时候定义的那个常量
我这里有时间的查询 但结束时间 没有加<![CDATA[ ]]> xml就会报错
<![CDATA[ AND t.CREATE_DATE<=TO_DATE(#{EndCreateTime}, 'yyyy-MM-dd') ]]>
我们在看看列表查询时我返回的是 java.util.HashMap 这个是和数据库的字段映射上的,所以我在前边页面field 的这个值时都是大写。
说到这咋们在回头看看 模糊条件查询吧
<span>姓名:</span> <input id="Name"
style="line-height: 24px; height: 24px; border: 1px solid #ccc">
<span>登录ID:</span> <input id="LoginId"
style="line-height: 24px; height: 24px; border: 1px solid #ccc">
<span>注册日期:</span> <input id="StateCreateTime" class="Wdate"
onFocus="WdatePicker()"
style="line-height: 24px; height: 24px; border: 1px solid #ccc">
<span>结束日期:</span> <input id="EndCreateTime" class="Wdate"
onFocus="WdatePicker()"
style="line-height: 24px; height: 24px; border: 1px solid #ccc">
<a href="#" class="easyui-linkbutton" iconCls="icon-search"
plain="true" οnclick="Oseasch(0,0)">搜索</a>
/* 条件查询*/ function Oseasch(pageNumber, pageSize){ var Name=$("#Name").val(); var LoginId=$("#LoginId").val(); var StateCreateTime=$("#StateCreateTime").val(); var EndCreateTime=$("#EndCreateTime").val(); $.ajax({ type:"POST", dataType:"json", url:'<%=request.getContextPath() %>/user.do?onSearchUser', data:{ pageNumber:pageNumber, pageSize:pageSize, Name:Name, LoginId:LoginId, StateCreateTime:StateCreateTime, EndCreateTime:EndCreateTime }, success:function(data,state){ $('#dg').datagrid('loadData', data); }, error:function(e) { alert("出错:"+e); } }); }
这样基本就完成数据显示条件查询
单元格修改
看看图吧
首先在 columns 必须添加属性 editor:'text'
/* 单元格编辑 */ $.extend($.fn.datagrid.methods, { editCell: function(jq,param){ return jq.each(function(){ var opts = $(this).datagrid('options'); var fields = $(this).datagrid('getColumnFields',true).concat($(this).datagrid('getColumnFields')); for(var i=0; i<fields.length; i++){ var col = $(this).datagrid('getColumnOption', fields[i]); col.editor1 = col.editor; if (fields[i] != param.field){ col.editor = null; } } $(this).datagrid('beginEdit', param.index); for(var i=0; i<fields.length; i++){ var col = $(this).datagrid('getColumnOption', fields[i]); col.editor = col.editor1; } }); } }); var editIndex = undefined; function endEditing(){ if (editIndex == undefined){return true} if ($('#dg').datagrid('validateRow', editIndex)){ $('#dg').datagrid('endEdit', editIndex); editIndex = undefined; return true; } else { return false; } } function onClickCell(index, field){ if (endEditing()){ $('#dg').datagrid('selectRow', index) .datagrid('editCell', {index:index,field:field}); editIndex = index; } }
//单元格数据编辑后提交 function saveAllData(){ if (endEditing()){ //获取到改变行的个数 var i=$("#dg").datagrid('getChanges').length; if (i){ //获取插入更改的行的集合 var updated = $("#dg").datagrid('getChanges', "updated"); var effectRow = new Object(); if (updated.length) { effectRow = JSON.stringify(updated); } $.ajax({ type:"POST", dataType:"json", url:'<%=request.getContextPath() %>/user.do?UpdateUserForm', data:{ 'effectRow':effectRow }, success:function(data){ alert(data.massage); var pager=$('#dg').datagrid('getPager'); var pageNumber=$(pager).pagination('options').pageNumber; var pageSize=$(pager).pagination('options').pageSize; Oseasch(pageNumber, pageSize); }, error:function(e) { $.messager.alert("出错:"+e); } }); }else{ $.messager.alert("没有改编数据!"); } } }
想看看数据是什么可以将effectRow 弹出来看看 这里是个字符串
看看后台代码吧
@RequestMapping(params="UpdateUserForm",method=RequestMethod.POST)
@ResponseBody
public JSONObject UpdateUserForm(HttpServletRequest request){
String json = request.getParameter("effectRow");
JSONArray jsonArray = JSONArray.fromObject(json);
@SuppressWarnings("unchecked")
List<DataMappingUser> list = JSONArray.toList(jsonArray, DataMappingUser.class);
List<User> userlist = DataMappingUser.changeUser(list);
userServiceImpl.updateUser(userlist);
JSONObject JS=new JSONObject();
JS.put("massage", "本次更新"+userlist.size()+"条!");
JS.put("success", true);
return JSONObject.fromObject(JS);
}
//这句代码的到的list 和 咋们之前查询的list 结构是类似的 DataMappingUser这个类是我写的一个和数据库字段对应的实体,其实有点麻烦如果查询时返回的为use对象实体就没必要写它了
List<DataMappingUser> list = JSONArray.toList(jsonArray, DataMappingUser.class);
//转换为user的集合
List<User> userlist = DataMappingUser.changeUser(list);
service层
public void updateUser(List<User> list) {
commonDAO.update(nameSpace, "updateUser", list);
}
commonDAO 层
public Object update(String nameSpace, String sqlId, Object params) {
mybatisTemplate.update(nameSpace + "." + sqlId, params);
return params;
}
xml映射文件(和查询同一文件)
<update id="updateUser" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";"> UPDATE T_SYS_USER SET NAME=#{item.name,jdbcType=VARCHAR}, PASSWORD=#{item.password,jdbcType=VARCHAR}, PHONE=#{item.phone,jdbcType=VARCHAR}, STATUS=#{item.status,jdbcType=VARCHAR}, EMAIL=#{item.email,jdbcType=VARCHAR}, LOGINID=#{item.loginId,jdbcType=VARCHAR}, SEX=#{item.sex,jdbcType=VARCHAR}, REMARKS=#{item.remark,jdbcType=VARCHAR} where ID = #{item.id, jdbcType=VARCHAR} </foreach> </update>
这就ok了
如果你改了很多东西,但不想改了撤销 js里调用这函数就行
function reject() { $('#dg').datagrid('rejectChanges'); editIndex = undefined; }
下边在说下新增上传文件吧 uploadfy
<link href="<%=request.getContextPath() %>/web/js/uploadify/uploadify.css" type="text/css" rel="stylesheet">
<script src="<%=request.getContextPath() %>/web/js/uploadify/jquery.uploadify.js"></script>
<div style="float: padding:10px 60px 20px 40px; float: left;">
<div id="uploadfileQueue" style="display: none;"></div>
<div id="beforehand" style=" padding-top:60px; width: 97px; height:97px; padding-left: 30px;">
<img id="headPhoto" src="<%=request.getContextPath() %>/web/js/uploadify/head.png">
</div>
<div style="padding-left: 30px; padding-top: 20px;">
<center><input type="file" name="Filedata" id="Filedata" /></center>
</div>
</div>
//文件上传 $("#Filedata").uploadify({ 'auto':true, 'successTimeout':99999, 'swf': "<%=request.getContextPath() %>/web/js/uploadify/uploadify.swf", 'queueID':'uploadfileQueue', 'fileObjName':'Filedata', 'uploader':'<%=request.getContextPath() %>/upload.do?uploadingHead', 'hideButton' : true, 'buttonText':'上传头像', 'buttonCursor':'hand', 'height':20, 'width':60, 'folder':'<%=request.getContextPath() %>/jxdBlog/photos', 'rollover':true, 'multi':true, 'wmode' : 'transparent', 'fileTypeExts':'*.jpg;*.jpge;*.gif;*.png', 'fileTypeDesc':'支持的格式:jpg;*.jpge;*.gif;*.png', 'onUploadSuccess':function(file, data, response){ var datas = eval("("+data+")"); var rePath=datas.list[0].rePath; var path='<%=request.getContextPath() %>'+rePath; $("#headPhoto").remove(); $("#beforehand").html('<img id="uploadfileQueue" src="'+path+'" style="width: 97px; height:97px;" >'); $("#headPhotoInfo").val(path); }, 'onUploadError': function (file, errorCode, errorMsg, errorString) { alert("上传失败"); }, 'onSelectError':function(file, errorCode, errorMsg){ switch(errorCode) { case -100: alert("上传的文件数量已经超出系统限制的"+$('#Filedata').uploadify('settings','queueSizeLimit')+"个文件!"); break; case -110: alert("文件 ["+file.name+"] 大小超出系统限制的"+$('#Filedata').uploadify('settings','fileSizeLimit')+"大小!"); break; case -120: alert("文件 ["+file.name+"] 大小异常!"); break; case -130: alert("文件 ["+file.name+"] 类型不正确!"); break; } } });我用的springMvc 所以上传必须的配置(springmvc的配置文件)
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <property name="defaultEncoding" value="utf-8" /> <property name="maxUploadSize" value="10485760000" /> <property name="maxInMemorySize" value="40960" /> </bean>后台代码
package org.pbw.web.controller.upload;
import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONObject;
import org.pbw.web.entity.upload.FileBean;
import org.pbw.web.service.imp.UploadServiceImp;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;
/**
*
* @ClassName: FileUploadController
* @Description: TODO(文件上传)
* @author 彭保卫
* @Email pengbaowei@163.com
* @date 2016年4月28日 下午1:35:19
*
*/
@Controller
@RequestMapping(value="/upload.do",method=RequestMethod.POST)
public class FileUploadController {
@Autowired
private UploadServiceImp uploadServiceImp;
@RequestMapping(params="uploadingHead",method=RequestMethod.POST)
@ResponseBody
public JSONObject uploadingHead( HttpServletRequest request, HttpServletResponse response){
ServletContext servletContext = request.getSession().getServletContext();
String realPath = servletContext.getRealPath("\\")+"file";
CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(servletContext);
List<FileBean> fileList =new ArrayList<FileBean>();
if(multipartResolver.isMultipart(request)){
MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
Iterator iter = multiRequest.getFileNames();
while(iter.hasNext()){
FileBean fileBean=new FileBean();
MultipartFile file = multiRequest.getFile((String)iter.next());
System.out.println(file.getName());
if(file != null){
String fileName = file.getOriginalFilename();
String uuid = UUID.randomUUID().toString();
//文件保存的地址
String path = realPath+"\\"+"H"+uuid+fileName;
//前台所需地址
String rePath="/file"+"/"+"H"+uuid+fileName;
File localFile = new File(path);
fileBean.setRePath(rePath);
fileBean.setFileName(fileName);
fileBean.setUploadTime(new Date());
//写文件到本地
try {
file.transferTo(localFile);
} catch (Exception e) {
}
}
fileList.add(fileBean);
}
}
if(fileList!=null&&fileList.size()>0){
uploadServiceImp.inser(fileList);
}
Map<String, Object> mp=new HashMap<String, Object>();
mp.put("list", fileList);
JSONObject fromObject = JSONObject.fromObject(mp);
System.out.println(fromObject);
return JSONObject.fromObject(mp);
}
}
提示:别导错包哦!
下边再说下导出excel文件 osCache缓存
加载数据和用户的基本是一样的
$(function() { $('#dg').datagrid({ url:'<%=request.getContextPath() %>/report.wms?report', columns:[ [ {field:'projectName',title:'项目',width:200,rowspan:2,align:'center'}, {title:'上月结存',colspan:3}, {title:'本月增加',colspan:3}, {title:'本月消耗',colspan:3}, {title:'本月结存',colspan:3} ], [ {field:'lastMomthBalanceRetailPrice',title:'零售价',width:100,align:'center'}, {field:'lastMomthBalancePurchasePrice',title:'进价',width:80,align:'center'}, {field:'lastMomthBalancePriceDifference',title:'进销差价',width:100,align:'center'} , {field:'instantAddRetailPrice',title:'零售价',width:100,align:'center'} , {field:'instantAddPurchasePrice',title:'进价',width:80,align:'center'} , {field:'instantAddPriceDifference',title:'进销差价',width:100,align:'center'} , {field:'instantConsumeRetailPrice',title:'零售价',width:100,align:'center'} , {field:'instantConsumePurchasePrice',title:'进价',width:80,align:'center'} , {field:'instantConsumePriceDifference',title:'进销差价',width:100,align:'center'} , {field:'instantBalanceRetailPrice',title:'零售价',width:100,align:'center'} , {field:'instantBalancePurchasePrice',title:'进价',width:80,align:'center'} , {field:'instantBalancePriceDifference',title:'进销差价',width:100,align:'center'} ]] , loadMsg: "查询数据请稍候。。。" }); });这里我只是想说下缓存 因为数据都是通过大量的计算得出的,导出文件时还需要,所以报存到缓存里边就ok,导出时直接从缓存里取 osCache 能缓存jsp页面 ,也能缓存对象。
maven 里引用
<dependency> <groupId>opensymphony</groupId> <artifactId>oscache</artifactId> <version>2.4.1</version> </dependency>下载完包 在etc下找到 oscache.properties将其放在源文件下
cache.memory
值为true 或 false ,默认为在内存中作缓存,
如设置为false,那cache只能缓存到数据库或硬盘中,那cache还有什么意义cache.persistence.class持久化缓存类,如此类打开,则必须设置cache.path信息
cache.path
当使用磁盘缓存时,须指定目录如果不存在将被建立。同时注意
oscache应该要有权限写文件系统。
cache.path=c:\\myapp\\cache or *ix:
cache.path=/opt/myapp/cache
cache.capacity 缓存大小
cache.cluster.properties 为集群属性
cache.cluster.multicast.ip为广播IP地址
看看API
主要是这个类 GeneralCacheAdministrator
public class CacheManager extends GeneralCacheAdministrator {
/**
* @Fields serialVersionUID : TODO(用一句话描述这个变量表示什么)
*/
private static final long serialVersionUID = 1L;
private static final Log LOG = LogFactory.getLog(CacheManager.class.getName());
private static CacheManager singleton;
public static CacheManager create() throws CacheException {
synchronized (CacheManager.class) {
if (singleton == null) {
if (LOG.isDebugEnabled()) {
LOG.debug("创建了一个新的CacheManager");
}
singleton = new CacheManager();
} else {
if (LOG.isDebugEnabled()) {
LOG.debug("已经创建了个单例");
}
}
return singleton;
}
}
/**
*
* @Title: put
* @Description: TODO(将对象加载到缓存里边)
* @param @param key
* @param @param objct 设定文件
* @return void 返回类型
* @throws
*/
//添加被缓存的对象+过期时间
public void put(String key, Object value, int time) {
this.putInCache(key, value, new ExpiresRefreshPolicy(time));
}
}
了解缓存后我们回到之前表格的数据查询吧
@Controller
@RequestMapping(value="report.wms", method = RequestMethod.POST)
public class ReportController {
@Autowired
private ReporServiceImpl reporServiceImpl;
@RequestMapping(value="report", method = RequestMethod.POST)
@ResponseBody
public JSONObject Report(HttpServletRequest request){
CacheManager cacheManager = CacheManager.create();
try {
Object fromCache = cacheManager.getFromCache("DataList");
if(fromCache!=null){
cacheManager.flushEntry("DataList");
}
} catch (NeedsRefreshException e) {
}
List<ReportEntity> list =new ArrayList<ReportEntity>();
//中间过程为计算数据。。。。。
Map<String,Object> mp=new HashMap<String,Object>();
mp.put("rows", list);
cacheManager.put("DataList", list, 1800);
return JSONObject.fromObject(mp);
}
导出文件成功后下载
function exportExcel(){ <%-- location.href="<%=request.getContextPath()%>/poi.wms?exportExcel"; --%> $.ajax({ type:"GET", dataType:"json", url:'<%=request.getContextPath()%>/poi.wms?exportExcel', success:function(data){ if(data.success){ location.href="<%=request.getContextPath()%>/poi.wms?dowloadExportExcel"; }else{ alert("数据已经过期,请重新查询!"); } }, error:function(e) { $.messager.alert("出错:"+e); } }); }导出文件后台
@Controller
@RequestMapping(value="poi.wms" ,method=RequestMethod.GET)
public class PoiController {
@RequestMapping(params="exportExcel" ,method=RequestMethod.GET)
@ResponseBody
public JSONObject exportExcel(HttpServletRequest request){
Map<String , Object> mp=new HashMap<String , Object>();
CacheManager create = CacheManager.create();
try {
@SuppressWarnings({ "rawtypes", "unchecked" })
List list = (List<ReportEntity>)create.getFromCache("DataList");
String[] headers={"项目","零售价","进价","进销差价","零售价","进价","进销差价","零售价","进价","进销差价","零售价","进价","进销差价"};
PoiUtile<ReportEntity> p=new PoiUtile<ReportEntity>();
if(list!=null){
p.realizeExportExcel("测试", "测试导出", headers, list);
mp.put("success", true);
}else{
mp.put("success", false);
}
} catch (Exception e) {
e.printStackTrace();
}
return JSONObject.fromObject(mp);
}
}
看下这个类
//PoiUtile<ReportEntity> p=new PoiUtile<ReportEntity>();
package org.hrwms.report.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import com.opensymphony.oscache.base.NeedsRefreshException;
public class PoiUtile<T>{
@SuppressWarnings("deprecation")
public void realizeExportExcel(String sheetName,String title, String[] headers, Collection<T> dataset){
boolean flat=false;
// 1先创建工作簿对
HSSFWorkbook workbook = new HSSFWorkbook();
// 2创建工作表对象并命名
HSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth(15);
sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 12));
sheet.addMergedRegion(new Region(2, (short) 9, 2, (short) 11));
sheet.addMergedRegion(new Region(3, (short) 0, 4, (short) 0));
sheet.addMergedRegion(new Region(3, (short) 1, 3, (short) 3));
sheet.addMergedRegion(new Region(3, (short) 4, 3, (short) 6));
sheet.addMergedRegion(new Region(3, (short) 7, 3, (short) 9));
sheet.addMergedRegion(new Region(3, (short) 10, 3, (short) 12));
HSSFCellStyle createCellStyle1 = workbook.createCellStyle();
createCellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
createCellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
//标题行
HSSFRichTextString rich = new HSSFRichTextString(
title);
HSSFRow row = sheet.createRow(0);
row.setHeightInPoints((short) 30);
HSSFCell createCell = row.createCell(0);
createCell.setCellStyle(createCellStyle1);
HSSFFont f = workbook.createFont();
f.setFontHeightInPoints((short)24);
f.setFontName("宋体");
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
rich.applyFont(f);
createCell.setCellValue(rich);
//时间显示
HSSFCellStyle createCellStyle2 = workbook.createCellStyle();
createCellStyle2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//左右居中
createCellStyle2.setBorderBottom((short)2);
HSSFRow createRow3 = sheet.createRow(2);
HSSFFont fontTime = workbook.createFont();
fontTime.setFontHeightInPoints((short) 16);
fontTime.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
fontTime.setFontName("宋体");
createCellStyle2.setFont(fontTime);
createRow3.setHeightInPoints(20);
SimpleDateFormat format=new SimpleDateFormat("yyyy年MM月dd日");
String path = format.format(new Date()).toString();
for (int i = 0; i < headers.length; i++) {
HSSFCell createCell2 = createRow3.createCell(i);
createCell2.setCellStyle(createCellStyle2);
if(i==9){
createCell2.setCellValue("截至日期 : "+path);
}
}
//首层目录显示行
HSSFRow row0 = sheet.createRow(3);
HSSFCellStyle createCellStyle3 = workbook.createCellStyle();
createCellStyle3.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
createCellStyle3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
createCellStyle3.setBorderBottom((short)2);
createCellStyle3.setBorderLeft((short)2);
createCellStyle3.setBorderRight((short)2);
createCellStyle3.setBorderTop((short)2);
row0.setHeightInPoints(30);
String[] Fheaders={"项目","上月结存"," "," ","本月增加"," "," ","本月消耗"," "," ","本月结存","",""};
for (int i = 0; i < Fheaders.length; i++) {
HSSFCell cell = row0.createCell(i);
cell.setCellStyle(createCellStyle3);
HSSFRichTextString text = new HSSFRichTextString(Fheaders[i]);
HSSFFont fontSY = workbook.createFont();
fontSY.setFontHeightInPoints((short) 16);
fontSY.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
fontSY.setFontName("宋体");
text.applyFont(fontSY);
cell.setCellValue(text);
}
//在sheet里增加合并单元格
HSSFCellStyle createCellStyle4 = workbook.createCellStyle();
createCellStyle4.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
createCellStyle4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
createCellStyle4.setBorderBottom((short)2);
createCellStyle4.setBorderRight((short)2);
createCellStyle4.setBorderLeft((short)2);
createCellStyle4.setBorderTop((short)2);
//3 创标题建行
HSSFRow row1 = sheet.createRow(4);
row1.setHeightInPoints(40);
for (short i = 0; i < headers.length; i++)
{
HSSFCell cell = row1.createCell(i);
cell.setCellStyle(createCellStyle4);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
HSSFFont fontSY = workbook.createFont();
fontSY.setFontHeightInPoints((short) 16);
fontSY.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
fontSY.setFontName("宋体");
text.applyFont(fontSY);
cell.setCellValue(text);
}
HSSFCellStyle createCellStyle5 = workbook.createCellStyle();
createCellStyle5.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
createCellStyle5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
createCellStyle5.setBorderBottom((short)2);
createCellStyle5.setBorderRight((short)2);
createCellStyle5.setBorderLeft((short)2);
createCellStyle5.setBorderTop((short)2);
//数据内容行
Iterator<T> it = dataset.iterator();
int index = 4;
while (it.hasNext()){
index++;
row = sheet.createRow(index);
row.setHeightInPoints(30);
T t = (T) it.next();
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(createCellStyle5);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Date)
{
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
textValue = sdf.format(date);
} else if (value instanceof byte[]) {
// 有图片时,设置行高为60px;
row.setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, (short) (35.7 * 80));
byte[] bsValue = (byte[]) value;
} else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
// 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null)
{
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches())
{
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
}
else
{
HSSFRichTextString richString = new HSSFRichTextString(
textValue);
HSSFFont font3 = workbook.createFont();
font3.setFontHeightInPoints((short) 16);
font3.setFontName("宋体");
richString.applyFont(font3);
cell.setCellValue(richString);
}
}
} catch (Exception e) {
}
}
}
HSSFRow endRow = sheet.createRow(index);
endRow.setHeightInPoints(30);
sheet.addMergedRegion(new Region(index, (short) 1, index, (short) 3));
sheet.addMergedRegion(new Region(index, (short) 7, index, (short) 8));
sheet.addMergedRegion(new Region(index, (short) 9, index, (short) 10));
HSSFFont font3 = workbook.createFont();
font3.setFontHeightInPoints((short) 16);
font3.setFontName("宋体");
for (int j = 0; j < Fheaders.length; j++) {
HSSFCell createCell3 = endRow.createCell(j);
if(j==1){
HSSFRichTextString richString = new HSSFRichTextString(
"药学部负责人 :");
richString.applyFont(font3);
createCell3.setCellValue(richString);
}else if(j==7){
HSSFRichTextString richString = new HSSFRichTextString(
"部门负责人 :");
richString.applyFont(font3);
createCell3.setCellValue(richString);
}else if(j==9){
HSSFRichTextString richString = new HSSFRichTextString(
"制表 :");
richString.applyFont(font3);
createCell3.setCellValue(richString);
}
}
//5创建文件
SimpleDateFormat formatpath=new SimpleDateFormat("yyyyMMddHHmmss");
String tr = formatpath.format(new Date());
String fileName="R"+tr+".xlsx";
String path1="D:\\"+fileName;
File file = new File(path1);
FileOutputStream fos = null;
try {
fos = new FileOutputStream(file);
workbook.write(fos);
CacheManager create = CacheManager.create();
create.flushEntry("fileName");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fos != null) {
try {
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
download(fileName);
}
private void download(String fileName) {
CacheManager create = CacheManager.create();
try {
create.put("fileName", fileName, 1800);
} catch (Exception e) {
}
}
}
/**
导出文件成功后将文件路径保存在缓存里,所以在保存新文件路径时先清理下旧的缓存
*/
下下载
//导出成功后下载文件(下载文件不能用ajax)
@RequestMapping(params="dowloadExportExcel" ,method=RequestMethod.GET)
@ResponseBody
public void dowloadExportExcel(HttpServletResponse response){
CacheManager create = CacheManager.create();
try {
String fileName = (String)create.getFromCache("fileName");
if(fileName!=null&&fileName.length()>0){
//处理文件名
String realname = fileName;
//设置响应头,控制浏览器下载该文件
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(realname, "UTF-8"));
//读取要下载的文件,保存到文件输入流
FileInputStream in = new FileInputStream("D:" + "\\" + fileName);
//创建输出流
OutputStream out = response.getOutputStream();
//创建缓冲区
byte buffer[] = new byte[1024];
int len = 0;
//循环将输入流中的内容读取到缓冲区当中
while((len=in.read(buffer))>0){
//输出缓冲区的内容到浏览器,实现文件下载
out.write(buffer, 0, len);
}
//关闭文件输入流
in.close();
//关闭输出流
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}