导入execl到数据库mysql
GwykhrenyuankuList
<body jwcid="$content$">
<span jwcid="@components/AppBorder" subtitle="需要使用务表"
disableCaching="true"> <!--当前位置--> <!--页面主体-->
<style type="text/css">
.fileInputContainer{
height:29px;
background:url(/cjxb/common/images/btnDrry.png);
position:relative;
width: 85px;
float: right;
}
.fileInput{
height:29px;
width: 85px;
overflow: hidden;
position:absolute;
right:0;
top:0;
opacity: 0;
filter:alpha(opacity=0);
cursor:pointer;
}
</style>
<div jwcid="listData@Any" name="data" id="listData">
<form jwcid="queryForm@Form">
<!--功能区-->
<h2 class="pagetitle">
<span>测评管理</span>
</h2>
<div class="news-list tol">
<h2 class="update">搜索条件
<a jwcid="@DirectLink" listener="ognl:listeners.backList" class="btnUpDate"><img jwcid="@Any"
src="/cjxb/common/images/btnFh.gif" /></a>
</h2>
<!--搜索-->
<div class="news-list tol">
<div class="newForm">
<p class="clearfix">
<span class="spanTitle">姓名：</span>
<input type="text" jwcid='qf_khrenyuanname@TextField' displayName="姓名" class="w150p mr10p" style="width:120px"
value='ognl:queryFilter.fields["qf_khrenyuanname"].value' />
<span class="spanTitle">：</span>
<input jwcid='qf_sijuid_code@PropertySelection' readOnly="readOnly" model="ognl:@com.app.util.GwykhUtils@getGzdwList()" style="width:120px"
displayName="" class="w150p mr10p" value='ognl:queryFilter.fields["qf_sijuid_code"].value' />
<span class="spanTitle"> ：</span>
<input jwcid='qf_offices@TextField' displayName="" class="w150p mr10p" value='ognl:queryFilter.fields["qf_offices"].value' style="width:114px"/>
</p>
<p class="clearfix">
<span class="spanTitle">：</span>
<input jwcid='qf_khrenyuanzhiwu@TextField' displayName="名称"
class="w150p mr10p" style="width:120px" value='ognl:queryFilter.fields["qf_khrenyuanzhiwu"].value'/>
<span class="spanTitle">：</span>
<input jwcid='qf_levelNum@dev:PropertySelection' keyType='level' emptyMsg="请选择" displayName=""
style="width: 120px;margin-right: 10px;" value='ognl:queryFilter.fields["qf_levelNum"].value'/>
<span class="spanTitle">状态：</span>
<input jwcid='qf_status@dev:PropertySelection' keyType='rystatus' emptyMsg="请选择" displayName="状态"
style="width: 120px;margin-right: 10px;" value='ognl:queryFilter.fields["qf_status"].value'/>
</p>
<p class="clearfix">
<span class="spanTitle">岗位标志：</span>
<input jwcid='qf_postFlag@dev:PropertySelection' keyType='post' emptyMsg="请选择" displayName="现岗位标志"
style="width: 120px;margin-right: 10px;" value='ognl:queryFilter.fields["qf_postFlag"].value'/>
</p>
</div></div>
<input jwcid="qf_khchengcibiaoid@TextField" value='ognl:queryFilter.fields["qf_khchengcibiaoid"].value' style="display: none;"/>
<h4 class="sign-h4">
<a href="#" jwcid="@LinkSubmit"><img jwcid="@Any"
src="/cjxb/common/images/btnSearch01.gif" /></a>
<a href="#" parameters="ognl:new java.lang.String[]{'add',queryFilter.fields['qf_khchengcibiaoid'].value}"
jwcid="@cofortune:ExternalLink" page="GwykhrenyuankuEdit"><img jwcid="@Any"
src="/cjxb/common/images/zj.gif" /></a>
<a href="javascript:void(0)" onclick="$('#id_sys_del_link').click()" ><img jwcid="@Any"
src="/cjxb/common/images/sc.gif" /></a>
<!-- <a href="#" onclick="javascript:$('#xlsFile').get(0).click();">导入人员</a> -->
</h4>
<!--列表-->
<h2 class="h2-table-title">
<div class="fileInputContainer">
<input type="file" jwcid="xlsFile@Upload" file="ognl:xlsFile" onchange="xlsUpload();" class="fileInput"/>
</div>
<input jwcid="addPeople@Submit" value="" type="submit" listener="ognl:listeners.addPeople" style="display: none;"/>
务列表
</h2>
<table width="100%" border="0" cellspacing="0" cellpadding="0"
class="list-table">
<colgroup>
<col width="4%" />
<col width="12%"/>
<col width="12%"/>
<col width="12%"/>
<col width="12%"/>
<col width="10%"/>
<col width="12%"/>
<col width="14%"/>
<col width="12%" />
</colgroup>
<tr>
<th><input type="checkbox" jwcid="allbox@cofortune:CheckAllBox" /></th>
<th>姓名</th>
<th></th>
<th></th>
<th>现</th>
<th></th>
<th>岗位标志</th>
<th>状态</th>
<th>操作</th>
</tr>
<tfoot>
<tr>
<td colspan="9"><span jwcid="nav">翻页组件</span></td>
</tr>
</tfoot>
<tbody>
<tr class="even" jwcid="foreach">
<td align="center" class="chk"><input type="checkbox" jwcid="rowSelect" groupby="allbox" /></td>
<td align="center"><span jwcid="@Insert"
value="ognl:item.khrenyuanname">名称</span> </td>
<td align="center"><span jwcid="@Insert"
value="ognl:item.sijuid.gzdwValue"></span> </td>
<td align="center"><span jwcid="@Insert"
value="ognl:@com.app.util.GwykhUtils@officesView(item.offices)"></span> </td>
<td align="center"><span jwcid="@Insert"
value="ognl:item.khrenyuanzhiwu"></span> </td>
<td align="center"><span jwcid="@dev:Insert" dicType="level"
value="ognl:item.levelNum"></span> </td>
<td align="center"><span jwcid="@dev:Insert" dicType="post"
value="ognl:item.postFlag">现岗位标志</span> </td>
<td align="center"><span jwcid="@dev:Insert" dicType="rystatus"
value="ognl:item.status">状态</span> </td>
<td align="center">
<a href="#" parameters="ognl:new java.lang.String[]{'edit',item.id}"
jwcid="@cofortune:ExternalLink" page="GwykhrenyuankuEdit">[修改]</a>
<a jwcid="@DirectLink" listener="ognl:listeners.delete"
parameters="ognl:item.id" onclick="return confirm('确定删除?');">[删除]</a>
</td>
</tr>
</tbody>
</table>
</div>
<!--数据列表 end-->
<a jwcid="id_sys_del_link@Submit" listener="ognl:listeners.delete" style="display: none" onclick="return confirm('确定要删除?');" />
</form>
</div>
</span>
<script type="text/javascript">
<!--
function xlsUpload(){
var file = $('#xlsFile').val();
if(file != null && file != "" && file != undefined){
var ext = file.substr(file.lastIndexOf('.'));
if(ext == '.xls'){
$("#addPeople").get(0).click();
}else{
alert("请上传Excel文件（2003格式）");
}
}
}
-->
</script>
</body>
package com.app.pages.gwykh;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.commons.lang.StringUtils;
import org.apache.tapestry.IRequestCycle;
import org.apache.tapestry.request.IUploadFile;
import com.app.iface.gwykh.GwykhchengcibiaoIface;
import com.app.iface.gwykh.GwykhdanweiIface;
import com.app.iface.gwykh.GwykhrenyuankuIface;
import com.app.model.Gwykhdanwei;
import com.app.model.Gwykhrenyuanku;
import com.app.model.GwykhGzdw;
import com.app.service.gwykh.GwykhchengcibiaoService;
import com.app.service.gwykh.GwykhdanweiService;
import com.app.service.gwykh.GwykhrenyuankuService;
import com.app.util.GwykhUtils;
import com.cofortune.base.cache.CacheManager;
import com.cofortune.base.factory.ServiceFactory;
import com.cofortune.base.model.BaseDictionary;
import com.cofortune.base.model.BaseObject;
import com.cofortune.base.pages.CieccListPage;
import com.cofortune.framework.query.IPagingAndSorting;
import com.cofortune.framework.query.PagingAndSorting;
import com.cofortune.framework.query.QueryFilter;
/**
* 人员库表列表页面
* @每日一句:旁观者的姓名永远爬不到比赛的计分板上。
* @author 自动生成与2013-10-14 13:17:05 ^_^ GOOD LUCK!!
*/
public abstract class GwykhrenyuankuList extends CieccListPage {
/**
* 初始化分页排序
* 排序注意要加id
*/
public IPagingAndSorting initPagingAndSorting() {
//默认以ID列进行排序，如需指定排序列请按照“dicCode,id”格式添加
PagingAndSorting pagingAndSorting = new PagingAndSorting("sijuid.gzdwCode,postFlag,offices,levelNum,sort",false);
pagingAndSorting.setMaxResults(20);
return pagingAndSorting;
}
/**
* 初始化查询条件
*/
public QueryFilter initQueryFilter() {
QueryFilter qf = new QueryFilter();
qf.addField("qf_khrenyuanname", "khrenyuanname", QueryFilter.OPERATION_LIKE, "");//人员名称
qf.addField("qf_sijuid_code", "sijuid.gzdwCode", QueryFilter.OPERATION_EQ, "");//名称
qf.addField("qf_offices", "offices", QueryFilter.OPERATION_EQ, "");//
qf.addField("qf_khrenyuanzhiwu", "khrenyuanzhiwu", QueryFilter.OPERATION_LIKE, "");//人员
qf.addField("qf_levelNum", "levelNum", QueryFilter.OPERATION_EQ, "");//职级
qf.addField("qf_postFlag", "postFlag", QueryFilter.OPERATION_EQ, "");//现岗位标志
qf.addField("qf_status", "status", QueryFilter.OPERATION_EQ, "");//状态
qf.addField("qf_khchengcibiaoid", "khchengcibiaoid.id", QueryFilter.OPERATION_EQ, "");//层次表ID
return qf;
}
/**
* 得到人员库表服务类
* @author 自动生成与2013-10-14 13:17:05 ^_^ GOOD LUCK!!
* @return
*/
public GwykhrenyuankuIface getGwykhrenyuankuService(){
GwykhrenyuankuIface service = ServiceFactory.getBean(GwykhrenyuankuService.class, GwykhrenyuankuIface.class);
return service;
}
/**
* 得到层次表服务类
* @author 自动生成与2013-10-14 13:17:28 ^_^ GOOD LUCK!!
* @return
*/
public GwykhchengcibiaoIface getGwykhchengcibiaoService(){
GwykhchengcibiaoIface service = ServiceFactory.getBean(GwykhchengcibiaoService.class, GwykhchengcibiaoIface.class);
return service;
}
/**
* 得到测评表服务类
* @author 自动生成与2013-10-14 13:17:15 ^_^ GOOD LUCK!!
* @return
*/
public GwykhdanweiIface getGwykhdanweiService(){
GwykhdanweiIface service = ServiceFactory.getBean(GwykhdanweiService.class, GwykhdanweiIface.class);
return service;
}
/**
* 加载人员库表数据
* @author 自动生成与2013-10-14 13:17:05 ^_^ GOOD LUCK!!
* @return
*/
public void loadData() {
GwykhrenyuankuIface service=getGwykhrenyuankuService();
List<BaseObject> find = service.findGwykhrenyuandaku(getQueryFilter(), getPagingAndSorting());
setItems(find);
}
/**
* 删除人员库表
* @param id
* @author 自动生成与2013-10-14 13:17:05 ^_^ GOOD LUCK!!
* @return
*/
public void delete(String id ){
GwykhrenyuankuIface service=getGwykhrenyuankuService();
if(StringUtils.isBlank(id)){
service.removeGwykhrenyuanku(getSelectedList().toArray(new String[0]));
}else{
service.removeGwykhrenyuanku(id);
}
this.backToListWithParms("GwykhrenyuankuList,S"+System.currentTimeMillis());//带查询分页参数返回
}
public void backList(){
this.backToListWithParms("GwykhchengcibiaoList,S"+System.currentTimeMillis());//带查询分页参数返回
}
public abstract IUploadFile getXlsFile();
public abstract void setXlsFile(IUploadFile xlsFile);
public void addPeople(){
/**接装**/
Map dwMap = new HashMap();
QueryFilter qf = new QueryFilter();
qf.addField("qf_status", "status", QueryFilter.OPERATION_LIKE, "1");
qf.addField("qf_khchengcibiaoid", "khchengcibiaoid", QueryFilter.OPERATION_LIKE, getQueryFilter().getField("qf_khchengcibiaoid").getValue().toString());
List danwei = getGwykhdanweiService().findGwykhdanwei(qf, null);
if(danwei != null && danwei.size() > 0){
for(Object obj : danwei){
dwMap.put(((Gwykhdanwei)obj).getGzdwid().getGzdwCode(), obj);
}
}
IUploadFile xlsFile = getXlsFile();
InputStream is = xlsFile.getStream();
Workbook rwb = null;
List<Gwykhrenyuanku> list = new ArrayList<Gwykhrenyuanku>();
try {
rwb = Workbook.getWorkbook(is);
Sheet rs = rwb.getSheet(0);// 获取第一张Sheet表
int rsRows = rs.getRows();// 获取Sheet表中所包含的总行数
for (int i = 1; i < rsRows; i++) {//**从第二行开始读取数据 这里默认从第2行开始读取
Gwykhrenyuanku ry = new Gwykhrenyuanku();
ry.setKhchengcibiaoid(getGwykhchengcibiaoService().editGwykhchengcibiao(getQueryFilter().getField("qf_khchengcibiaoid").getValue().toString()));
String name = rs.getCell(0, i).getContents();
if(StringUtils.isNotBlank(name)){
ry.setKhrenyuanname(name);
String sijuName = rs.getCell(1, i).getContents();//获得名称
GwykhGzdw gzdw = GwykhUtils.getGzdwByGzdwValue(sijuName);//获得信息
ry.setSijuid(gzdw);
ry.setSijuname(gzdw.getGzdwValue());
ry.setKhdanweiid((Gwykhdanwei)dwMap.get(gzdw.getGzdwCode()));//
String offices = rs.getCell(2, i).getContents();//名
// BaseDictionary dic = CacheManager.getInstance().getBaseDicByName("offices", offices);
// if(dic != null){
// offices = dic.getDicCode();
// }
ry.setOffices(offices);
String zhiwu = rs.getCell(3, i).getContents();//现
// BaseDictionary dic = CacheManager.getInstance().getBaseDicByName("zw", zhiwu);
// if(dic != null){
// zhiwu = dic.getDicCode();
// }
ry.setKhrenyuanzhiwu(zhiwu);
String level = rs.getCell(4, i).getContents();//序号
ry.setLevelNum(level);
String postFlag = rs.getCell(5, i).getContents();//现岗位标志
/** 通过现岗位标志确定状态 */
//1只2测评3无测评
// if(GwykhUtils.POST_NAME_1.contains(postFlag)){
// ry.setStatus(GwykhUtils.STATUS_FLAG_1);
// }else if(GwykhUtils.POST_NAME_2.contains(postFlag)){
// ry.setStatus(GwykhUtils.STATUS_FLAG_2);
// }else
if(GwykhUtils.POST_NAME_3.contains(postFlag)){
ry.setStatus(GwykhUtils.STATUS_FLAG_3);
}else{
ry.setStatus("");
}
BaseDictionary dic = CacheManager.getInstance().getBaseDicByName("post", postFlag);
if(dic != null){
postFlag = dic.getDicCode();
}
ry.setPostFlag(postFlag);
String sort = rs.getCell(6, i).getContents();//排序
if(sort != null && sort != ""){
ry.setSort(Integer.parseInt(sort));
}
ry.setIstongguo("0");
list.add(ry);
}
}
this.getGwykhrenyuankuService().saveOrUpdateGwykhrenyuankuByList(list, getQueryFilter().getField("qf_khchengcibiaoid").getValue().toString());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(rwb != null){
rwb.close();
}
if(is != null){
try {
is.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
this.backToListWithParms("GwykhrenyuankuList,S"+System.currentTimeMillis());//带查询分页参数返回
}
//接收参数，需要传递参数在此修改
@Override
public void activateExternalPage(Object[] arg0, IRequestCycle arg1) {
if(arg0!=null){//添加额外参数使用，添加好的参数一定要传入到queryFilter中，不然分页时候出现错误，如下部分
if("list".equals(arg0[0])){
getQueryFilter().getField("qf_khchengcibiaoid").setValue(arg0[1]);
}
}
super.activateExternalPage(arg0, arg1);
}
//搜索区域下拉框
}
