java实现将Excel表导入数据库

最新推荐文章于 2024-07-03 14:13:18 发布

請叫我菜鳥

最新推荐文章于 2024-07-03 14:13:18 发布

阅读量2k

点赞数

分类专栏： java学习

java学习专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一.java实现将Excel表导入数据库需要三个页面(以导入国税评定数据为例)：
（1）drgspdsj.jsp页面（表单页面）：
<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%@page import="com.hhzs.nsxydj.common.Tools"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%
String rootpath = request.getContextPath();
String nsrsbh = Tools.nullToSpace(request.getParameter("nsrsbh"));
%>
<html>
<head>
<link rel="stylesheet" href="<%=rootpath%>/css/decss.css"
type="text/css" media="all" />
<script type="text/javascript" src="<%=rootpath%>/js/ajaxtools.js"></script>
<script type="text/javascript" src="<%=rootpath%>/js/public.js"></script>
<script type="text/javascript" src="<%=rootpath%>/js/calendar1.js"></script>
<script type="text/javascript" src="<%=rootpath%>/js/addRow.js"></script>
<title>导入国税评定数据</title>
<style type="text/css">
table {
border-collapse: collapse;
margin: 0 auto
}
table tr td {
border: 1px #999 solid;
padding: 5px;
}
table .header {
background: #E1E1E1
}
table .header:hover {
text-decoration: none
}
table tr {
background: #F1F7FC
}
table tr:hover {
text-decoration: underline
}
</style>
<script type="text/javascript">

function scxxcx(){
if(document.getElementByIdx_x_x_x('scfj').value==""){
alert('请选择要上传的文件!');
return;
}
var arr = document.getElementsByName("scfj");
for(i=0;i<arr.length;i++){
if(arr[i].value != "" && !uploadFileCheck(arr[i])){
return false;
}
}
var formxx = document.getElementByIdx_x_x_x("formxx");
showOrHideDiv("divCtais","block");
formxx.action = "gsbc.jsp";
formxx.submit();
}


//验证文件格式
function uploadFileCheck(objF){//验证文件
var regImg = new RegExp(/\.(xls|XLS|XLs|Xls)$/);
var FileMaxSize = 2048;
var obj=objF.value;
var fileObj = objF;
if(!obj.match(regImg)){
fileObj.outerHTML=fileObj.outerHTML.replace(/(value=").+"/i,"$1"");
alert('请上传正确的格式文件');
return false;
}
return true;
}

function close5(){
parent.window.closeWork(false, this);
}

</script>
</head>
<XML id="DSO_BGXM">
<ROOT></ROOT>
</XML>
<body>
<form id="formxx" action="" method="POST"
enctype="multipart/form-data">
<center>
导入附件
<input type="file" id="scfj" name="scfj" style="width: 600px" />
</center>
</form>
<center>
<input type="button" value="导入" οnclick="scxxcx()" class=bottom>
<input type="button" value="关闭" οnclick="close5()" class=bottom>
</center>
<br />
<br />
<center>
<fieldset class="helpinfo" style="width: 680px;">
<br />
<font color="red" style="font-size: 12px">注意：请以正确的格式导入(Office的Excel文件)，格式如下：</font>
          
          
          
          
<br />
<table border="1" style="width: 90%">
<tr class="bt">
<td align="center">
    纳税人识别号
</td>
<td align="center">
评定等级
</td>
<td align="center">
评定年度
</td>
<td align="center">
评定时间
</td>
</tr>
<tr class="lbxs">
<td>
371122198202108349L0
</td>
<td>
A
</td>
<td>
2009-2011
</td>
<td>
2011-11-25
</td>
</tr>
<tr class="lbxs">
<td>
371122198202240913K0
</td>
<td>
A
</td>
<td>
2009-2011
</td>
<td>
2011-11-25
</td>
</tr>
<tr class="lbxs">
<td>
371122198203230610
</td>
<td>
A
</td>
<td>
2009-2011
</td>
<td>
2011-11-25
</td>
</tr>
<tr class="lbxs">
<td>
371122198201145455
</td>
<td>
A
</td>
<td>
2009-2011
</td>
<td>
2011-11-25
</td>
</tr>
<tr class="lbxs">
<td>
371122198111251254
</td>
<td>
A
</td>
<td>
2009-2011
</td>
<td>
2011-11-25
</td>
</tr>
<tr class="lbxs">
<td>
371122198111010012
</td>
<td>
A
</td>
<td>
2009-2011
</td>
<td>
2011-11-25
</td>
</tr>
</table>
<br />
</fieldset>
</center>
<DIV id="divCtais"
style="position: absolute; top: 30%; left: 38%; display: none"
align=center>
<IMG src="../../images/loading.gif" width=100 height=100>
<BR>
<font color=red>正在导入数据,请稍等...</font>
</DIV>
</body>
</html>

（2）gsbc.jsp页面（临时保存页面，小脚本调用java）：
<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%@page import="org.apache.commons.fileupload.servlet.ServletFileUpload"%>
<%@page import="org.apache.commons.fileupload.disk.DiskFileItemFactory"%>
<%@page import="java.io.File"%>
<%@page import="org.apache.commons.fileupload.FileItemIterator"%>
<%@page import="org.apache.commons.fileupload.FileItemStream"%>
<%@page import="org.apache.commons.fileupload.util.Streams"%>
<%@page import="com.hhzs.nsxydj.dao.impl.BaseDao"%>
<%@page import="com.hhzs.nsxydj.common.Tools"%>
<%@page import="com.hhzs.nsxydj.dao.ExcelInsertOraclegs"%>
<%@page import="com.hhzs.nsxydj.data.GSJ_NSXYDJ_DM_SWRY_DATA"%>
<%
String nsrsbh = Tools.nullToSpace(request.getParameter("nsrsbh"));
String nsrdzdah = Tools.nullToSpace(request.getParameter("nsrdzdah"));
//System.out.println("当前上传人的纳税人识别号："+nsrsbh+"当前上传人的"+nsrdzdah);
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>保存文件</title>
</head>

<body>
<%
File tmpDir = null;// 初始化上传文件的临时存放目录
File saveDir = null;// 初始化上传文件后的保存目录
BaseDao dao = new BaseDao();
String tmpPath = "E:\";
String savePath = "E:\";
String fileName = "";
tmpDir = new File(tmpPath);
saveDir = new File(savePath);
if (!tmpDir.isDirectory())
tmpDir.mkdir();
if (!saveDir.isDirectory())
saveDir.mkdir();
try {
if (ServletFileUpload.isMultipartContent(request)) {
DiskFileItemFactory dff = new DiskFileItemFactory();// 创建该对象
dff.setRepository(tmpDir);//指定上传文件的临时目录
dff.setSizeThreshold(1024000);// 指定在内存中缓存数据大小,单位为byte
ServletFileUpload sfu = new ServletFileUpload(dff);// 创建该对象
sfu.setSizeMax(5000000);// 指定单个上传文件的最大尺寸
sfu.setSizeMax(10000000);// 指定一次上传多个文件的总尺寸
FileItemIterator fii = sfu.getItemIterator(request);
while (fii.hasNext()) {
FileItemStream fis = fii.next();// 从集合中获得一个文件流
if (!fis.isFormField() && fis.getName().length() > 0) {
fileName = fis.getName().substring(fis.getName().lastIndexOf("\") + 1);// 获得上传文件的文件名
BufferedInputStream in = new BufferedInputStream(fis.openStream());// 获得文件输入流
BufferedOutputStream out2 = new BufferedOutputStream(new FileOutputStream(new File(saveDir + "\" + fileName)));// 获得文件输出流
Streams.copy(in, out2, true);// 开始把文件写到你指定的上传文件夹
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
GSJ_NSXYDJ_DM_SWRY_DATA data = (GSJ_NSXYDJ_DM_SWRY_DATA)request.getSession().getAttribute("swryData");
ExcelInsertOraclegs inse = new ExcelInsertOraclegs();
String jg = inse.InsertComOracle(data.getSwry_dm(),tmpPath,fileName);
request.getRequestDispatcher("drgspdsj2.jsp?&jg="+jg).forward(request,response);
%>
</body>
</html>
（3）drgspdsj2.jsp 页面（刷新页面）
<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%@page import="com.hhzs.nsxydj.common.Tools"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%
String rootpath = request.getContextPath();
String jg = Tools.nullToSpace(request.getParameter("jg"));
%>
<html>
<head>
<link rel="stylesheet" href="<%=rootpath%>/css/decss.css"
type="text/css" media="all" />
<script type="text/javascript" src="<%=rootpath%>/js/ajaxtools.js"></script>
<script type="text/javascript" src="<%=rootpath%>/js/public.js"></script>
<script type="text/javascript" src="<%=rootpath%>/js/calendar1.js"></script>
<script type="text/javascript" src="<%=rootpath%>/js/addRow.js"></script>
<title>手工采集</title>
<style type="text/css">
table {
border-collapse: collapse;
margin: 0 auto
}
table tr td {
border: 1px #999 solid;
padding: 5px;
}
table .header {
background: #E1E1E1
}
table .header:hover {
text-decoration: none
}
table tr {
background: #F1F7FC
}
table tr:hover {
text-decoration: underline
}
</style>
<script type="text/javascript">


function on_load(){
var jg = '<%=jg%>';
if(jg.split("$")[0] == 'true'){
alert("导入成功,共导入"+jg.split("$")[1]+"条！");
}else{
alert("导入失败！");
}
}


function scxxcx(){
if(document.getElementByIdx_x_x_x('scfj').value==""){
alert('请选择要上传的文件!');
return;
}
var arr = document.getElementsByName("scfj");
for(i=0;i<arr.length;i++){
if(arr[i].value != "" && !uploadFileCheck(arr[i])){
return false;
}
}
var formxx = document.getElementByIdx_x_x_x("formxx");
showOrHideDiv("divCtais","block");
formxx.action = "gsbc.jsp";
formxx.submit();
}


//验证文件格式
function uploadFileCheck(objF){//验证文件
var regImg = new RegExp(/\.(xls|XLS|XLs|Xls)$/);
var FileMaxSize = 2048;
var obj=objF.value;
var fileObj = objF;
if(!obj.match(regImg)){
fileObj.outerHTML=fileObj.outerHTML.replace(/(value=").+"/i,"$1"");
alert('请上传正确的格式文件');
return false;
}
return true;
}

function close5(){
parent.window.closeWork(false, this);
}

</script>
</head>
<XML id="DSO_BGXM">
<ROOT></ROOT>
</XML>
<body οnlοad="on_load()">
<form id="formxx" action="" method="POST" enctype="multipart/form-data">
<center>
导入附件<input type="file" id="scfj" name="scfj" style="width: 600px" />
</center>
</form>
<center>
<input type="button" value="导入" οnclick="scxxcx()" class=bottom>
<input type="button" value="关闭" οnclick="close5()" class=bottom>
</center><br />
<center>
<fieldset class="helpinfo" style="width: 680px;" ><br/>
<font color="red">注意：请以正确的格式导入(Office的Excel文件)，格式如下：</font>
          
          
          
          <br />
<table border="1" style="width: 90%">
<tr class="bt" >
<td align="center">    纳税人识别号</td>
<td align="center">评定等级</td>
<td align="center">批次代码</td>
<td align="center">评定时间</td>
</tr>
<tr class="lbxs">
<td>137112200141</td>
<td>A</td>
<td>1</td>
<td>2011-11-25</td>
</tr>
<tr class="lbxs">
<td>137112200142</td>
<td>A</td>
<td>1</td>
<td>2011-11-25</td>
</tr>
<tr class="lbxs">
<td>137112200143</td>
<td>A</td>
<td>1</td>
<td>2011-11-25</td>
</tr>
<tr class="lbxs">
<td>137112200144</td>
<td>A</td>
<td>1</td>
<td>2011-11-25</td>
</tr>
<tr class="lbxs">
<td>137112200145</td>
<td>A</td>
<td>1</td>
<td>2011-11-25</td>
</tr>
<tr class="lbxs">
<td>137112200147</td>
<td>A</td>
<td>1</td>
<td>2011-11-25</td>
</tr>
</table>
<br/>
</fieldset>
</center>
<DIV id="divCtais"
style="position:absolute;top:30%;left:38%;display:none" align=center>
<IMG src="../../images/loading.gif" width=100 height=100>
<BR>
<font color=red>正在导入数据,请稍等...</font>
</DIV>
</body>
</html>
二.保存页面调用的类ExcelInsertOraclegs.java
package com.hhzs.nsxydj.dao;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.hhzs.nsxydj.util.ConnectionFactory;
public class ExcelInsertOraclegs {
public ArrayList getListExcelInsertOracleSql(String swjgdm,String tmpPath,String fileToBeRead){
ArrayList list = new ArrayList();
try {
FileInputStream instr = new FileInputStream(tmpPath+fileToBeRead);
HSSFWorkbook workbook = new HSSFWorkbook(instr);
//HSSFSheet nsrsbh = workbook.getSheet("Sheet1");
HSSFSheet nsrsbh = workbook.getSheetAt(0);
//HSSFSheet nsrsbh = workbook.getSheet("纳税人识别号");
int rows = nsrsbh.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++) {
HSSFRow row = nsrsbh.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
String value = "";
for (short c = 0; c < cells; c++) {
HSSFCell cell = row.getCell(c);
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value += (long) cell.getNumericCellValue()+ "#";
break;
case HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + "#";
break;
default:
value += "";
}
}
}
if (r > 0) {
System.out.println(value);
String[] str = value.split("#");
String sql = "insert into gsj_nsxydj_zzdj_gs(id,nsrsbh,pdjg,pc_dm,cjr,cjsj)";
sql += " values(GSJ_NSXYDJ_ZZDJ_GS_SEQ.Nextval,'"
+ str[0].trim() + "','" + str[1].trim() + "','" + str[2].trim()
+ "','"+swjgdm.trim()+"',sysdate)";
String delsql = "delete from gsj_nsxydj_zzdj_gs where nsrsbh = '"+str[0].trim()+"' and pc_dm = '"+str[2].trim()+"'";
System.out.println("插入国税："+sql);
System.out.println("删除国税："+delsql);
list.add(delsql);//如果数据库中已存在该纳税人识别号，先删除
list.add(sql);//再重新插入
}
}
}
instr.close();
} catch (Exception e) {
System.out.println(e);
}
return list;
}

public String InsertComOracle(String swjgdm,String tmpPath,String fileName){
String b = "false";
ArrayList list = getListExcelInsertOracleSql(swjgdm,tmpPath,fileName);
System.out.println(list.size());
Connection conn = null;
Statement state = null;
int num = (int)Math.floor(list.size()/1000);
try {
conn = ConnectionFactory.getConnection();
conn.setAutoCommit(false);
state = conn.createStatement();
for(int i = 0;i <= num;i++){
int xhnum = 0;
if(num == 0){
xhnum = list.size();
}else if(i == num-1){
xhnum = list.size();
}else{
xhnum = i*1000+1000;
}
for(int j = i*1000;j < xhnum;j++){
state.addBatch(list.get(j).toString());
}
state.executeBatch();
conn.commit();
b = "true";
}
conn.setAutoCommit(true);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnectionFactory.closeConnection(null, state, conn);
}
delFile(tmpPath+fileName);
return b+"$"+list.size()/2;
}

public void delFile(String fileToBeRead){ //删除文件
File file = new File(fileToBeRead);
if (file.exists()) {
file.delete();
}
}

public static void main(String argv[]) {
//new ExcelInsertOracle().InsertComOracle("13711000000");
//new ExcelInsertOracle().delFile("E:\\test.xls");
}
}