数据写入excel，实现批量上传
1：页面：[/size]
<%@pagelanguage="java"import="java.util.*,java.io.*"
pageEncoding="UTF-8"%>
<%@tagliburi="http://java.sun.com/jsp/jstl/core"prefix="c"%>
<%@taglibprefix="s"uri="/struts-tags"%>
<%
Stringpath=request.getContextPath();
StringbasePath=request.getScheme()+"://"
+request.getServerName()+":"+request.getServerPort()
+path+"/";
if(session.getAttribute("admin_info")==null){
out.println("<html>");
out.println("<script>");
out.println("window.open('"+basePath
+"asiasys/login.jsp','_top')");
out.println("</script>");
out.println("</html>");
}
%>
<!DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.01Transitional//EN">
<html>
<head>
<basehref="<%=basePath%>">
<linkhref="asiasys/css/admin.css"type="text/css"rel="stylesheet"/>
<scripttype="text/javascript"src="js/jquery.js"></script>
<linkhref="asiasys/css/public.css"type="text/css"rel="stylesheet"/>
<scripttype="text/javascript"src="asiasys/js/calendar.js"></script>
<scripttype="text/javascript"src="asiasys/js/ajaxfileupload.js"></script>
<scripttype="text/javascript"src="asiasys/js/jquery.form.js"></script>
<scripttype="text/javascript">
functionisvalidatefile()
{
varobj=$("#excel").val();
if(!CheckPic(obj)){
alert("文件格式不对，请选择xls文件！");
returnfalse;
}
else
returntrue;
}
functionCheckPic(v){
if(v.indexOf(".")>0){
varo=v.split(".");
vare=o[o.length-1].toLowerCase();
if(e!="xls")
returnfalse;
else
returntrue;
}
else{returntrue;}
}
functionuploadFile(){
if(!isvalidatefile())
{
returnfalse;
}
$("#reloading").show();
$("#edit_bg").show();
$("#form1").ajaxSubmit({
type: "POST",
success: function(data){
$("#reloading").hide();
$("#edit_bg").hide();
if(data.indexOf("true")!=-1){
alert("操作成功。");
}else{
alert(data);
alert("操作失败,请联系管理员！");
}
} //操作成功后的操作
});
}
$(function(){
$("#reloading").hide();
$("#edit_bg").hide();
});
</script>
</head>
<body>
<divid="tel_position">
<divid="position">
<STRONG>管理导航：</STRONG>
<ahref="asiasys/market/list.jsp"target="mainFrame">药材行情管理</a>
<ahref="asiasys/market/excelUpload.jsp"target="mainFrame">excel数据上传</a>
</div>
</div>
<br/>
<br/>
<formname="form1"id="form1"enctype="multipart/form-data"
action="XlsAction!uploadXlsToDB.action"method="post">
<TABLEclass=bordercellSpacing=1cellPadding=2width="100%"
align=centerborder=0bgcolor='#FFFFFF'>
<tbody>
<captionstyle="text-align:center">
上传行情
</caption>
<trclass=tdbg>
<tdclass='tdbg5'>
文件
</td>
<td>
<inputtype="file"name="excel"id="excel"size="20">
<inputtype="button"onclick="uploadFile();"value="确定"></input>
<divid="reloading">
<imgalt="操作进行中..."src="images/zoomloader.gif"/>
</div>
</td>
</tr>
</table>
<divclass="black_overlay"id="fade"></div>
<divalign="center"id="edit_bg"></div>
<br>
</body>
</html>
1：action：[size=large]
publicStringuploadXlsToDB()
{
Workbookworkbook=null;
InputStreaminp=null;
booleanisBlankFlag=false;
PrintWriterout=null;
try
{
out=response.getWriter();
inp=newFileInputStream(excel);
workbook=Workbook.getWorkbook(inp);//excel文档
Sheetsheet=workbook.getSheet(0);//excel的sheet
if(sheet.getRows()<=1)
{
out.write("true");
returnnull;
}
for(inti=1;i<sheet.getRows();i++)//sheet.getRows()为excel数据的总行数
{
MsMarketmsmarket=newMsMarket();
Stringid=StringUtil.genrateKey();
msmarket.setID(id);
//排除某些空格数据,如果有空格行，停止数据读取
if(i>1&&"".equals(sheet.getCell(0,i).getContents()))
{
out.write("true");
isBlankFlag=true;
break;
}
msmarket.setMarketName(sheet.getCell(0,i).getContents());
msmarket.setMedNname(sheet.getCell(1,i).getContents());
msmarket.setSpecification(sheet.getCell(2,i).getContents());
msmarket.setPrice(sheet.getCell(3,i).getContents());
//数据提供者
msmarket.setAuthor(sheet.getCell(4,i).getContents());
msmarket.setMarkType("2");
//msmarket.setCreatTime(newDate());
msmarket.setCreatTime("".equals(sheet.getCell(5,i).getContents())?newDate():StringUtil.getDate(sheet.getCell(5,i).getContents()));
msmarket.setIsIndex("".equals(sheet.getCell(6,i).getContents())?0:Integer.parseInt(sheet.getCell(6,i).getContents()));
msmarket.setIsChannel("".equals(sheet.getCell(7,i).getContents())?0:Integer.parseInt(sheet.getCell(7,i).getContents()));
msmarket.setIsTOP("".equals(sheet.getCell(8,i).getContents())?0:Integer.parseInt(sheet.getCell(8,i).getContents()));
msmarket.setKeyWord(sheet.getCell(9,i).getContents());
//点击率
//msmarket.setIsNUM(Integer.parseInt(sheet.getCell(9,i).getContents()));
//录入者
msmarket.setAddUser(sheet.getCell(10,i).getContents());
msmarket.setNote(sheet.getCell(11,i).getContents());
//是否可用
msmarket.setStatus(1);
SysUserssessionInfo=(SysUsers)session.get(UserAction.Admin_INFO);
if(sessionInfo!=null)
msmarket.setAddUser(sessionInfo.getName());
//添加索引
if(marketService.addMarket(msmarket)){
MsMarket_Viewmarket_View=newMsMarket_View();
market_View.setID(id);
market_View.setMarketName(msmarket.getMarketName());
market_View.setMedNname(msmarket.getMedNname());
market_View.setSpecification(msmarket.getSpecification());
market_View.setPrice(msmarket.getPrice());
market_View.setCreatTime(msmarket.getCreatTime());
market_View.setAddUser(msmarket.getAddUser());
market_View.setAuthor(msmarket.getAuthor());
market_View.setKeyWord(msmarket.getKeyWord());
market_View.setStatus(msmarket.getStatus());
market_View.setMarkType("2");
market_View.setStatus(1);
if(compassService.insertMarket_View(market_View))
{
System.out.println("索引更新成功");
}
}
}
if(!isBlankFlag)
{
out.write("true");
}
}
catch(Exceptione)
{
e.printStackTrace();
logger.info("添加市场"+e);
out.write(e.getMessage());
}
finally
{
workbook.close();
try
{
inp.close();
}
catch(IOExceptione)
{
e.printStackTrace();
}
}
returnnull;
}