非同期アップロード解析excel poi方式解析excelデータベースに格納し、excelを操作する
20366 ワード
プロジェクトでsqlserver 2005データをexcelにエクスポートしoracleにインポートすると、エクスポートされたフィールドの順序は変更できますが、2番目の列は空にできません.
解析するときもこのニーズに合わせて解析しなければなりません.コードを貼って共有します.
ページ:
imp.jsp
FileImpDBServlet .java
excelToOracleForImp.java
InsertIntoOracle.java
インタフェースは次のとおりです.
注意:データベースフィールドに対応するexcelを作成すればいいです.excelでは、excel内のワークブックを解析するため、ワークブックの名前を定義します.
注意:次のコードはパラメータが何であるかを説明しています.
はい、たくさん书いて、みんなに役に立つことを望んで、问题があって伝言を残すことができます!!!
解析するときもこのニーズに合わせて解析しなければなりません.コードを貼って共有します.
ページ:
imp.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+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').html(data.message);
if(typeof(data.error) != 'undefined')
{
if(data.error != '')
{
alert(data.error);
}else
{
alert(data.message);
}
}
},
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; HEIGHT: 150px"/>
</div> -->
<div align="center">
<img src="images/loading.gif" id="loading" style="display: none;">
<span style="color: red;" 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" style="display: none"/>
<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.future.zfs.model.Manager;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.FileUploadBase.SizeLimitExceededException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import com.future.zfs.biz.impl.AreaBizImpl;
import com.future.zfs.biz.intf.IAreaBizInf;
import com.future.zfs.model.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 File(request.getRealPath("/")
+ "uploadtemp"));// ,web uploadtemp
//
ServletFileUpload sfu = new ServletFileUpload(dfif);
//
sfu.setSizeMax(MAX_SIZE);
PrintWriter out = response.getWriter();
// 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) {
out.println("[{message:' '}]");
return;
}
//
Iterator fileItr = fileList.iterator();
//
while (fileItr.hasNext()) {
FileItem fileItem = null;
String path = null;
long size = 0;
//
fileItem = (FileItem) fileItr.next();
// form (<input type="text" /> )
if (fileItem == null || fileItem.isFormField()) {
continue;
}
//
path = fileItem.getName();
//
size = fileItem.getSize();
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 = request.getRealPath("/") + "upload/";
// , web upload
//-----------------------
//Integer areaid = Integer.parseInt(request.getParameter("areaid"));
// listarea = areaimpl.selectSupareaById(areaid);
// for (Area area : listarea) {
// sb.append(area.getId()+"/");
// }
// System.out.println(sb.toString());
Manager admin = (Manager)request.getSession().getAttribute("currManager");
String u_name = basepath+ admin.getUsername() + "." + t_ext;
//
File u_name2 = new File(u_name);
// u_name2.getParentFile().mkdirs();
//-------------------------------
//path=request.getRealPath("/") + "upload/"+path;
String filename="upload/"+ prefix + "." + t_ext;
try {
//
fileItem.write(u_name2);
// poi excel ， 。
// ， ，
if(ExcelToOracleForImp.excelToOracleForImp(u_name,"lawpeople","lawpeople"))
{
if(ExcelToOracleForImp.excelToOracleForImp(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;
import java.text.DateFormat;
import java.text.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
* @return
* @author xushigang
* excel
*/
public static boolean excelToOracleForImp(String filename,String sheetname,String tablename) {
File f = new File(filename);
try {
FileInputStream is = new FileInputStream(f);
HSSFWorkbook wbs = new HSSFWorkbook(is);
// HSSFSheet childSheet = wbs.getSheetAt(sheetname);
HSSFSheet childSheet = wbs.getSheet(sheetname);
//System.out.println(childSheet.getPhysicalNumberOfRows());
// System.out.println(" " + childSheet.getLastRowNum());
String[] ArrayExcelRow = new String[childSheet.getRow(0).getLastCellNum()];
String[] headtitle = new String[childSheet.getRow(0).getLastCellNum()];
for (int i = 0; i < childSheet.getPhysicalNumberOfRows(); i++) {
System.out.println(childSheet.getPhysicalNumberOfRows());
HSSFRow row = childSheet.getRow(i);
// System.out.println(" " + row.getLastCellNum());
if (null != row) {
for (int j = 0; j < row.getLastCellNum(); j++) {
HSSFCell cell = row.getCell(j);
if (null != cell) {
if(i==0)
{
headtitle[j] = cell.getStringCellValue();
// System.out
// .println(headtitle[j]);
}
else
{
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: //
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
ArrayExcelRow[j] = "to_date('"+formater.format(d)+"','yyyy-mm-dd hh24:mi:ss')";
}
else{
ArrayExcelRow[j] = String.valueOf((int)cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING: //
ArrayExcelRow[j] = "'"+cell.getStringCellValue()+"'";
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
ArrayExcelRow[j] = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA: //
ArrayExcelRow[j] = String.valueOf(cell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_BLANK: //
ArrayExcelRow[j] = "";
break;
case HSSFCell.CELL_TYPE_ERROR: //
ArrayExcelRow[j] = "";
break;
default:
System.out.print(" ");
break;
}
}
} else {
System.out.print("");
}
}
if(i!=0&&ArrayExcelRow[1]!=""&&ArrayExcelRow[1]!=null)
{
System.out.println("--- --");
new InsertIntoOracle().save(headtitle, ArrayExcelRow,tablename);
}
}
//System.out.println();
}
return true;
} catch (Exception e) {
return false;
// e.printStackTrace();
}
}
}
InsertIntoOracle.java
package com.future.zfs.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.log4j.Logger;
import com.future.zfs.dao.impl.AreaDaoImpl;
import com.future.zfs.db.BaseDao;
import com.future.zfs.db.TransactionUtil;
import com.future.zfs.exception.TransactionException;
/**
*
* @author xushigang
* excel
*/
public class InsertIntoOracle extends BaseDao{
private Connection conn;
private PreparedStatement pstmt;
private Statement stmt;
public ResultSet res;
public static final Logger logger = Logger.getLogger(AreaDaoImpl.class);
public boolean save(String[] headtitle,String[] ExcelBody,String table)
{
try
{
StringBuffer sql = new StringBuffer();
StringBuffer forargs = new StringBuffer();
sql.append("insert into "+table+"(");
for (int i = 1; i < headtitle.length; i++) {
// System.out.println(i);
if(i==headtitle.length-1)
{
sql.append(headtitle[i]);
// System.out.println(headtitle[i]);
}
else
{// id ， id 。
sql.append(headtitle[i]+",");
// System.out.println(headtitle[i]);
}
}
// sql.append(") values(?,?,?,?,?,?,?,?,?,?");
// sql.append(",?,?,?,?,?,?,?,?,?,?");
// sql.append(",?,?,?,?,?,?,?,?,?,?");
// sql.append(",?,?,?,?,?,?,?,?,?,?");
// sql.append(",?,?,?,?,?,?,?,?,?)");
// String[] args = new String[49];
// for (int i = 1; i < ExcelBody.length; i++) {
// args[i-1] = ExcelBody[i];
// }
// System.out.println(sql.toString()+"===================");
for (int i = 1; i < ExcelBody.length; i++) {
if(i==ExcelBody.length-1)
{
forargs.append(ExcelBody[i]);
}
else
{
forargs.append(ExcelBody[i]+",");
// System.out.println(ExcelBody[i]+",");
}
}
sql.append(") values(");
sql.append(forargs.toString());
sql.append(")");
System.out.println(sql.toString()+"===================");
int n = this.executeUpdate(sql.toString(), null);
return n == 1 ? true : false;
} catch (Exception e)
{
logger.info(" ， ！ ："+ e.getMessage(), e.fillInStackTrace());
throw new TransactionException(
" ， ！ ："+ e.getMessage(), e.fillInStackTrace());
} finally
{
this.closeRes();
this.closePstmt();
try {
this.conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Override
public int executeUpdate(String sql, String[] args) {
int result = 0;
try {
conn = TransactionUtil.getConnection();
if(conn.getAutoCommit()){
conn.setAutoCommit(false);
}
pstmt = conn.prepareStatement(sql);
if(args!=null && args.length>0){
for(int i=0;i<args.length;i++){
pstmt.setString(i+1, args[i]);
}
}
result = pstmt.executeUpdate();
conn.commit();
} catch (SQLException e) {
logger.info(" ， ！");
throw new TransactionException(e.getMessage(),e);
}finally{
this.closePstmt();
}
return result;
}
}
インタフェースは次のとおりです.
注意:データベースフィールドに対応するexcelを作成すればいいです.excelでは、excel内のワークブックを解析するため、ワークブックの名前を定義します.
注意:次のコードはパラメータが何であるかを説明しています.
// poi excel ， 。
// ， ，
if(ExcelToOracleForImp.excelToOracleForImp(u_name,"lawpeople","lawpeople"))
{
if(ExcelToOracleForImp.excelToOracleForImp(u_name,"change","change"))
{
response.setStatus(200);
out.println("[{message:\" .\"},{imagepath:\""+filename+"\"}]");
}
else
{
out.println("[{message:\" .\"},{imagepath:\""+filename+"\"}]");
}
}
else
{
out.println("[{message:\" .\"},{imagepath:\""+filename+"\"}]");
}はい、たくさん书いて、みんなに役に立つことを望んで、问题があって伝言を残すことができます!!!