异步上传解析excel采⽤poi⽅式解析excel存⼊数据库，操作
excel
项⽬中遇到将sqlserver2005数据导出到excel并将excel导⼊到oracle中，导出的字段顺序可以变但是第⼆列不能为空。
解析的时候也得按照这个需求来解析，我把代码贴出来分享下。
页⾯：
imp.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = ContextPath();
String basePath = Scheme()+"://"+ServerName()+":"+ServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>数据导⼊</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<script type="text/javascript" src="<%=path %>/js/jquery.js"></script>
<script type="text/javascript" src="js/ajaxfileupload.js"></script>
<script type="text/javascript" src="<%=path %>/js/jquery.easyui.min.js"></script>
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script type="text/javascript">
function ajaxFileUpload()
{
var areaida = $('#areaid').val();
$("#loading")
.
ajaxStart(function(){
$(this).show();
})//开始上传⽂件时显⽰⼀个图⽚
.ajaxComplete(function(){
$(this).hide();
});//⽂件上传完成将图⽚隐藏起来
$.ajaxFileUpload
(
{
url:'FileImpDBServlet',//⽤于⽂件上传的服务器端请求地址
secureuri:false,//⼀般设置为false
fileElementId:'uploadimage',//⽂件上传空间的id属性 <input type="file" id="file" name="file" />
dataType: 'json',//返回值类型⼀般设置为json
success: function (data, status) //服务器成功响应处理函数
{
{
alert(data[0].message);//从服务器返回的json中取出message中的数据，其中message为在struts2中定义的成员变量
$('#previewImage').attr("src",data[1].imagepath);
$('#lawpeopleimage').val(data[1].imagepath);
$('#myspan').ssage);
if() != 'undefined')
{
!= '')
{
);
}else
{
ssage);
}
}
},
error: function (data, status, e)//服务器响应失败处理函数
{
/
/alert("服务中断或连接超时导致通信失败！");
//alert(e);
}
}
)
return false;
}
</script>
</head>
<body>
<!-- <div align="center" ><img src="images/wutupian.jpg" alt="预览图⽚" name="previewImage" align="middle" id="previewImage" style="WIDTH: 100px; HEIGH </div> -->
<div align="center">
<img src="images/loading.gif" id="loading" >
<span id="myspan"></span><br/>
<input type="file" id="uploadimage" name="uploadimage" size="8" />
<!--<s:hidden name="lawpeople.image" id="lawpeopleimage"></s:hidden> -->
<input type="text" name="lawpeople.image" id="lawpeopleimage" readonly="readonly" />
<input type="button" value="导⼊" onClick="return ajaxFileUpload();">
</div>
</body>
</html>
FileImpDBServlet .java
package com.future.zfs.util;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import com.del.Manager;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apachemons.fileupload.FileItem;
import org.apachemons.fileupload.FileUploadException;
import org.apachemons.fileupload.FileUploadBase.SizeLimitExceededException;
import org.apachemons.fileupload.disk.DiskFileItemFactory;
import org.apachemons.fileupload.servlet.ServletFileUpload;
import com.future.zfs.biz.impl.AreaBizImpl;
import com.future.zfs.biz.intf.IAreaBizInf;
import com.del.Area;
/**
*
* @author xushigang
*此⽅法是接收页⾯传来的excel
*/
@SuppressWarnings("serial")
public class FileImpDBServlet extends HttpServlet {
final long MAX_SIZE = 50*1024*1024;// 设置上传⽂件最⼤为 50M
// 允许上传的⽂件格式的列表
final String[] allowtype = new String[] {"xlsb","xls"};
private IAreaBizInf areaimpl = new AreaBizImpl();
private List<Area> listarea = new ArrayList<Area>();
public FileImpDBServlet() {
super();
}
public void destroy() {
super.destroy();
}
@Override
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
// 设置字符编码为UTF-8, 这样⽀持汉字显⽰
response.setCharacterEncoding("UTF-8");
// 实例化⼀个硬盘⽂件⼯⼚,⽤来配置上传组件ServletFileUpload
DiskFileItemFactory dfif = new DiskFileItemFactory();
dfif.setSizeThreshold(4096);// 设置上传⽂件时⽤于临时存放⽂件的内存⼤⼩，这⾥是4K.多于的部分将临时存在硬盘 dfif.setRepository(new RealPath("/")
+ "uploadtemp"));// 设置存放临时⽂件的⽬录,web根⽬录下的uploadtemp⽬录
// ⽤以上⼯⼚实例化上传组件
ServletFileUpload sfu = new ServletFileUpload(dfif);
// 设置最⼤上传尺⼨
sfu.setSizeMax(MAX_SIZE);
PrintWriter out = Writer();
// 从request得到所有上传域的列表
List fileList = null;
try {
fileList = sfu.parseRequest(request);
} catch (FileUploadException e) {// 处理⽂件尺⼨过⼤异常
if (e instanceof SizeLimitExceededException) {
// out.println("[{message:'⽂件尺⼨超过规定⼤⼩:"+MAX_SIZE+"字节'}]");
out.println("[{message:'⽂件尺⼨超过规定⼤⼩请导⼊50M以内的⽂件！'}]");
return;
}
e.printStackTrace();
}
/
/ 没有⽂件上传
if (fileList == null || fileList.size() == 0) {
if (fileList == null || fileList.size() == 0) {
out.println("[{message:'请选择⽂件进⾏导⼊'}]");
return;
}
// 得到所有上传的⽂件
Iterator fileItr = fileList.iterator();
// 循环处理所有⽂件
while (fileItr.hasNext()) {
FileItem fileItem = null;
String path = null;
long size = 0;
// 得到当前⽂件
fileItem = (FileItem) ();
// 忽略简单form字段⽽不是上传域的⽂件域(<input type="text" />等)
if (fileItem == null || fileItem.isFormField()) {
continue;
}
// 得到⽂件的完整路径
path = Name();
// 得到⽂件的⼤⼩
size = Size();
if ("".equals(path) || size == 0) {
out.println("[{message:'请选择⽂件进⾏导⼊'}]");
return;
}
// 得到去除路径的⽂件名
String t_name = path.substring(path.lastIndexOf("\\") + 1);
// 得到⽂件的扩展名(⽆扩展名时将得到全名)
String t_ext = t_name.substring(t_name.lastIndexOf(".") + 1);
// 拒绝接受规定⽂件格式之外的⽂件类型
int allowFlag = 0;
int allowedExtCount = allowtype.length;
for (; allowFlag < allowedExtCount; allowFlag++) {
if (allowtype[allowFlag].equals(t_ext))
break;
}
if (allowFlag == allowedExtCount) {
String message = "";
for (allowFlag = 0; allowFlag < allowedExtCount; allowFlag++){
message+="*." + allowtype[allowFlag]
+ " ";
}
out.println("[{message:'请导⼊以下类型的⽂件"+message+"'}]");
return;
}
long now = System.currentTimeMillis();
// 根据系统时间⽣成上传后保存的⽂件名
String prefix = String.valueOf(now);
// StringBuffer sb = new StringBuffer();
String basepath = RealPath("/") + "upload/";
// 保存的最终⽂件完整路径，保存在web根⽬录下的upload⽬录下
//-----------------------
//Integer areaid = Integer.Parameter("areaid"));
/
/ listarea = areaimpl.selectSupareaById(areaid);
// for (Area area : listarea) {
// sb.Id()+"/");
// }
// System.out.String());
Manager admin = (Session().getAttribute("currManager"); String u_name = basepath+ Username() + "." + t_ext;
//原来的⽂件名
//原来的⽂件名
File u_name2 = new File(u_name);
// ParentFile().mkdirs();
//-------------------------------
//RealPath("/") + "upload/"+path;
String filename="upload/"+ prefix + "." + t_ext;
try {
// 保存⽂件
fileItem.write(u_name2);
//此⾏代码是根据传来路径通过poi⽅式读取excel并放⼊数组中，⼀⾏⾏插⼊数据库中。
//第⼀个参数为⽂件路径，第⼆个为⼯作簿名称，第三个为要插⼊的表名称
lToOracleForImp(u_name,"lawpeople","lawpeople"))
{
lToOracleForImp(u_name,"change","change"))
{
response.setStatus(200);
out.println("[{message:\"操作成功.\"},{imagepath:\""+filename+"\"}]");
}
else
{
out.println("[{message:\"操作失败.\"},{imagepath:\""+filename+"\"}]");
}
}
else
{
out.println("[{message:\"操作失败.\"},{imagepath:\""+filename+"\"}]");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
excelToOracleForImp.java
package com.future.zfs.util;
import java.io.File;
import java.io.FileInputStream;
plsql developer导入数据
DateFormat;
SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelToOracleForImp {
/**
*
* @param filename
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论