导入execl到数据库mysql-白红宇

GwykhrenyuankuList

<span jwcid="@components/AppBorder" subtitle="需要使用务表"

disableCaching="true">

.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>

</h2>

<h2 class="update">搜索条件

<a jwcid="@DirectLink" listener="ognl:listeners.backList" class="btnUpDate"><img jwcid="@Any"

src="/cjxb/common/images/btnFh.gif" /></a>

</h2>

<input type="text" jwcid='qf_khrenyuanname@TextField' displayName="姓名" class="w150p mr10p" style="width:120px"

value='ognl:queryFilter.fields["qf_khrenyuanname"].value' />

<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' />

</p>

<input jwcid='qf_khrenyuanzhiwu@TextField' displayName="名称"

class="w150p mr10p" style="width:120px" value='ognl:queryFilter.fields["qf_khrenyuanzhiwu"].value'/>

<input jwcid='qf_levelNum@dev:PropertySelection' keyType='level' emptyMsg="请选择" displayName=""

style="width: 120px;margin-right: 10px;" value='ognl:queryFilter.fields["qf_levelNum"].value'/>

<input jwcid='qf_status@dev:PropertySelection' keyType='rystatus' emptyMsg="请选择" displayName="状态"

style="width: 120px;margin-right: 10px;" value='ognl:queryFilter.fields["qf_status"].value'/>

</p>

<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>

<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)" οnclick="$('#id_sys_del_link').click()" ><img jwcid="@Any"

src="/cjxb/common/images/sc.gif" /></a>

</h4>

</div>

务列表

</h2>

<table width="100%" border="0" cellspacing="0" cellpadding="0"

class="list-table">

</colgroup>

<tr>

</tr>

<tfoot>

<tr>

</tr>

</tfoot>

<tbody>

<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>

<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" οnclick="return confirm('确定删除?');">[删除]</a>

</td>

</tr>

</tbody>

</table>

</div>

</form>

</div>

</span>

<!--

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

}

//搜索区域下拉框

}