一。前端页面:importExcelWindow.html
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>excel导入</title>
<link rel="stylesheet" type="text/css" href="../../util/public.css">
<script type="text/javascript" src="../../util/public.js"></script>
<script type="text/javascript" src="js/importProject.js"></script>
<script language="javascript"
src="../../components/jquery/jquery-1.7-min.js"></script>
<style>
.error_body_item{
font-size:12px;
height:24px;
line-height: 24px;
padding-left: 20px;
}
.error_head{
font-size:12px;
height:24px;
line-height: 24px;
color:red;
}
.red{
color:red;
margin:0 5px;
}
.import{
margin:10px 5px;
cursor:pointer;
width:47%;
height:30px;
}
.close{
cursor:pointer;
width:47%;
height:30px;
margin:10px 5px;
}
#errorMsg{
border:1px solid #bbb;
width:99%;
height:260px;
padding:10px 5px;
color:#aaa;
overflow-y: auto;
}
#errp{
width:60px;
position:relative;
top:7px;
left:15px;
z-index:999;
text-align:center;
background:#fff;
color:#999;
overflow:auto;
}
</style>
</head>
<body>
<table width="100%" height="100%" style="margin-top:20px;">
<tr>
<td>
<form method="post" enctype="multipart/form-data" id="importForm" name="importForm" target="user_upload">
<div align="center" style="margin-top: 5px;">
<table height="100%" width="100%">
<tr height="30" align ="center">
<td align="right" width="20%">请选择导入文件:</td>
<td width="65%" align ="left">
<input type="file" name="file" id="upfile">
</td>
</tr>
<tr align = "center" height="30">
<td colspan="2"><input type="button" class="import" id="btn_import_commit" value=" 导 入 " οnclick="saveFile()">
<input type="button" class="close" id="btn_import_cancle" value=" 关 闭 " οnclick="closeWindow()"></td>
</tr>
<tr >
<td colspan="2">
<div id="info" style="padding:0 10px;display:none;">用户导入信息中,总共<span id='total_r'></span>条记录,当前正在处理第<span id='current_r'></span>条记录...</div>
<div id="lastinfo" style="padding:0 10px;height:30px;"><span id="linfo"></span></div>
</td>
</tr>
<tr>
<td colspan="2">
<div style="height:auto; width:100%; overflow: auto;padding:10px;">
<div id="errp" >导入信息</div>
<div id="errorMsg"> </div>
<div style="text-align:right"><input style="width:100px;margin:5px;" type="button" id="copys" value="复制错误信息" οnclick="copyInnerHtml()"></div>
</div>
</td>
</tr>
</table>
</div>
</form>
</td>
</tr>
</table>
<iframe id="bodyFrame" style="display:none" src="blank_html.html"></iframe>
</body>
</ht ml>
二:导入处理js页面:importProject
//$(function(){
// $("#btn_import_commit").click(function(){
// $("#importForm").attr("action","../../base/importProject/importProject.action");
// document.getElementById("importForm").submit();
// });
// $("#btn_import_cancle").click(function(){
// Util.closeWindow();
// });
//});
var parm = Util.getParm(),pId;//如果有pId,那么就导入bss业务号码
if(parm != null && parm.pid != null){
pId = parm.pid;
}
window.onload = function(){
$("#info").hide();
$("#lastinfo").hide();
};
function updateFile(file){
var str='';
str = file.value;
var reg=/[^\.](\.xls)$/i;
alert(reg.test(str));
if(reg.test(str) == false){
alert('格式无效! 只能上传2003版本以下(.xls)的Excel文件!');
file.outerHTML=file.outerHTML;
return false;
}
// document.getElementById("fileType").value = getFileType(file.value);
}
function saveFile(){
// $("#importForm").attr("action","../../base/uploadExcel/upload.action");
// alert($('#upfile').val());
var file = $('#upfile').val();
var reg=/[^\.](\.xls)$/i;
if (reg.test(file)== true){
if(pId != null){
$("#importForm").attr("action","../../base/uploadBssExcel/upload.action?pId="+pId);//如果有pId,那么就导入bss业务号码
}else{
var auth = Util.getUserInfo().allFunc; //登录用户所有权限
var auths = auth.split(",");
var ts = "";
for(var i = 0 ;i<auths.length;i++){
if("pm_selectAll" == auths[i]){
ts = "[计划部],[网建部],[行拓部,bss业务号码]";
break;
}else{
if("jk_jhb_update" == auths[i]){
if(ts == ''){
ts = "[计划部]";
}else{
ts += ",[计划部]";
}
break;
}if("jk_wjb_update" == auths[i]){
if(ts == ''){
ts = "[网建部]";
}else{
ts += ",[网建部]";
}
break;
}if("jk_htb_update" == auths[i]){
if(ts == ''){
ts = "[行拓部,bss业务号码]";
}else{
ts += ",[行拓部,bss业务号码]";
}
break;
}
}
}
if(confirm("本次导入会覆盖"+ts+"数据,是否确认导入?")){
$("#importForm").attr("action","../../base/uploadsExcel/uploads.action");
}
}
$("#errorMsg").html('');
$("#importForm").submit();
$("#btn_import_commit").attr("disabled",true);
}else{
alert('格式无效! 只能上传2003版本以下(.xls)的Excel文件!');
}
}
function writeMsg(as_msg){
$("#errorMsg").append("<div style='margin:10px;'>" + as_msg + "</div>");
}
function writeLastMsg(total,success,failure,updateSum,insertSum){
var str="批量导入完成!共<span class='red'>"+ total + "</span>条数据,成功<span class='red'>"+success+"</span>条,失败<span class='red'>"+failure+"</span>条<br>成功导入数据中新增<span class='red'>"+insertSum+"</span>条,修改<span class='red'>"+updateSum+"</span>条";
$("#info").hide();
$("#lastinfo").show();
$("#linfo").html(str);
}
function writeLastMsge(type,total,success,failure){
var str = "[" + type + "] 批量导入完成!共<span class='red'>"+ total + "</span>条数据,成功<span class='red'>"+success+"</span>条,失败<span class='red'>"+failure+"</span>条";
$("#info").hide();
$("#lastinfo").show();
$("#linfo").html(str);
}
function writeLastMesge(type,total,success,failure){
var str = "[" + type + "] 批量导入完成!共<span class='red'>"+ total + "</span>条数据,成功<span class='red'>"+success+"</span>条,失败<span class='red'>"+failure+"</span>条";
$("#info").hide();
$("#bsslastinfo").show();
$("#bsslinfo").html(str);
}
function writeCurrentRecord(an_cr,to_cr){
$("#info").show();
$("#total_r").html(to_cr);
$("#current_r").html(an_cr);
}
function closeWindow(){
Util.closeWindow();
// jkgrid.clearAll();
// param.sourceWindow.initGrid();
}
三:action类:ImportBSSAction
/**
* 导入项目信息 <功能简述> <功能详细描述>
*
* @return [参数说明]
*
* @return String [返回类型说明]
* @exception throws [异常类型] [异常说明]
* @see [类、类#方法、类#成员]
*/
@Override
public String execute() {
InputStream is;
try {
is = new FileInputStream(file);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
// if (!"BSS业务号码导入模板".equals(sheet.getSheetName())) {
// responseVal("parent.writeMsg('请使用模板导入!" + "');");
// } else {
int updateSum = 0, insertSum = 0, status = -1;
User user = Util.getInstance().getUserInfo(ServletActionContext.getRequest());
if(sheet.getLastRowNum() < 1){
responseVal("parent.writeMsg('无导入内容!" + "');");
return null;
}
Map map = new HashMap();
for (int i = 1; i <= sheet.getLastRowNum(); i++) {// 从第一行开始导入
HSSFRow row = sheet.getRow(i);
HSSFCell bsscell = row.getCell(0);
HSSFCell SFJScell = row.getCell(1);
responseVal("parent.writeCurrentRecord("+i+","+sheet.getLastRowNum()+");");
if (!"".equals(pId)) {
map.put("proId", pId);
map.put("PID", pId);
}
map.put("userid", user.getUserID());
map.put("username", user.getUserName());
if(bsscell == null){
//表格为空
failure++;
responseVal("parent.writeMsg('第<span class=\"red\">"+i+"</span>行数据中【BSS业务号】列输入错误,不能为空!" +"');");
continue;
}else if(bsscell.getCellType()!= HSSFCell.CELL_TYPE_STRING){
bsscell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
if ("".equals(bsscell.getStringCellValue())) {
// bss业务号码报错 为空
failure++;
responseVal("parent.writeMsg('第<span class=\"red\">"+i+"</span>行数据中【BSS业务号】列输入错误,不能为空!" +"');");
continue;
} else if(!StringUtil.cmpInterpunction(getValue(bsscell).trim())){//验证不通过
failure++;
responseVal("parent.writeMsg('第<span class=\"red\">"+i+"</span>行数据中【BSS业务号】列输入错误,只能包含字母或数字!" +"');");
continue;
}else{
map.put("bssnum", getValue(bsscell).trim());
}
if (SFJScell == null || "".equals(SFJScell.getStringCellValue())
&& SFJScell.getCellType() != HSSFCell.CELL_TYPE_STRING) {
failure++;
responseVal("parent.writeMsg('第<span class=\"red\">"+i+"</span>行数据中【是否计收】列输入错误,不能为空!" +"');");
continue;
} else {
if ("是".equals(SFJScell.getStringCellValue())) {
map.put("shifoujishou", 1);
}
if ("否".equals(SFJScell.getStringCellValue())) {
map.put("shifoujishou", 0);
}
}
bssNumService.importBSS(map);
if (map.get("status") instanceof BigDecimal) {
status = Integer.valueOf(map.get("status").toString());
}
if (status == 0) {
failure++;
Map isBssExit = compareBss("'"+bsscell.getStringCellValue().trim()+"'");
responseVal("parent.writeMsg('第<span class=\"red\">"+i+"</span>行数据添加错误,BSS号码:"+isBssExit.get("BSS_NUM")+"在[ "+ isBssExit.get("ZIPROJECTNAME") +" ]项目中已经引用!"+ "');");
} else if (status == 1) {
updateSum++;
success++;
continue;
} else if (status == 2) {
insertSum++;
success++;
continue;
}
}
bssNumService.refreshProbss(map);
responseVal("parent.writeLastMsg("+ sheet.getLastRowNum() + ","+success+","+failure+","+updateSum+","+insertSum+");");
// }
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(ServletActionContext.getResponse() != null){
try {
ServletActionContext.getResponse().getWriter().close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return null;
}
@SuppressWarnings("static-access")
private String getValue(HSSFCell hSSFCell) {
String hssfVal = "";
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
if(hSSFCell == null) return "";
if (hSSFCell.getCellType() == hSSFCell.CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
hssfVal = String.valueOf(hSSFCell.getBooleanCellValue());
} else if (hSSFCell.getCellType() == hSSFCell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
hssfVal = String.valueOf(hSSFCell.getNumericCellValue());
} else if(hSSFCell.getCellType() == hSSFCell.CELL_TYPE_STRING){
// 返回字符串类型的值
hssfVal = String.valueOf(hSSFCell.getStringCellValue());
}else if(hSSFCell.getCellType() == hSSFCell.CELL_TYPE_FORMULA){ //判断是否有公式
try {
if(HSSFDateUtil.isCellDateFormatted(hSSFCell)){
double d = hSSFCell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(d);
hssfVal = df.format(date);
}else{
hssfVal = String.valueOf(hSSFCell.getNumericCellValue());
}
} catch (IllegalStateException e) {
hssfVal = String.valueOf(hSSFCell.getRichStringCellValue());
}
}
return hssfVal;
}
/**
* 显示当前处理条数 tc 总共条数 c 当前 <功能简述> <功能详细描述> [参数说明]
*
* @return void [返回类型说明]
* @exception throws [异常类型] [异常说明]
* @see [类、类#方法、类#成员]
*/
public void responseVal(String content) {
if(response_c == null){
response_c = ServletActionContext.getResponse();
}
try {
String str = "<html><head><meta http-equiv='Content-Type' "
+ "content='text/html; charset=utf-8'><script language='javascript'>window.name='user_upload';"+content+"</script></head></html>";
response_c.getWriter().print(str);
response_c.getWriter().flush();
} catch (IOException e) {
e.printStackTrace();
}
}