Java操作Jxl实现.xsl及.xsls两种数据表格进行批量导入数据到SQL server数据库。
本文实现背景Web项目：前台用的框架是Easyui+Bootstrap结合使用，需要引入相应的Js、Css文件。页面：Jsp、拦截请求：Servlet、逻辑处理：ClassBean、数据库：SQLserver。
注：Bean中操作SQL语句进行处理是公司内部方法，可替换为其它方法自行扩展！主要看代码逻辑业务处理！
首先我们看见Easyui中的datagrid数据网格列表中没有任何数据：
其次创建一个xsl文件以下是需要导入的数据格式：
让我们把注意力放到SQLserver数据库：
点击导入按钮后弹出一个窗口选择我们刚刚创建的Excel文件，最后点击导入
导入成功此时此刻Easyui中的datagrid数据网格中已有了7条我们之前创建的记录！
我们回到数据库进行查看数据，果然和预期效果一样与Excel表格数据一致证明导入成功！
Jsp代码：
注：from中的enctype="multipart/form-data"属性是必加的，要想进行上传必须使用HTML中的文件控件，且必须使用enctype进行分装，表示将表单按照二进制的方式提交。即所有的操作表单此时不再是分别提交，否则将所有内容都按照二进制的方式提交。如果要上传文件，表单则必须封装。但是当一个表单使用了enctype="multipart/form-data"封装后，其它的非表单控件的内容就无法通过request内置对象获取，此时在Servlet中必须调用SmartUpload类中提供的getRequest（）方法获取全部的请求参数
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="com.pantech.base.common.tools.MyTools"%>
<%@ page import="com.pantech.base.common.tools.PublicTools"%>
<%@ page import="com.pantech.src.develop.Logs.*"%>
<%@ page import="com.pantech.base.common.tools.*"%>
<%@ page import="com.pantech.src.develop.store.user.*"%>
<%@ page import="com.pantech.src.develop.manage.workremind.WorkRemind"%>
<%@ page import="java.util.Vector"%>
<%@ page import="java.util.*"%>
<%@ page import="java.text.*"%>
<%@ page import="com.pantech.base.common.db.DBSource"%> <html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>导入</title> <link rel="stylesheet" type="text/css" href="<%=request.getContextPath()%>/css/themes/default/easyui.css">
<link rel="stylesheet" type="text/css" href="<%=request.getContextPath()%>/css/themes/icon.css">
<link charset="utf-8" rel="stylesheet" href="<%=request.getContextPath()%>/css/bootstrap.css"/>
<link charset="utf-8" rel="stylesheet" href="<%=request.getContextPath()%>/css/naber.css"/>
<link charset="utf-8" rel="stylesheet" href="<%=request.getContextPath()%>/css/font-awesome/css/font-awesome.css">
<link charset="utf-8" rel="stylesheet" href="<%=request.getContextPath()%>/css/SMS-index.css"/>
<link charset="utf-8" rel="stylesheet" href="<%=request.getContextPath()%>/css/listPage.css"/> <script type="text/javascript" src="<%=request.getContextPath()%>/script/JQueryUI/jquery.min.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/script/JQueryUI/jquery.easyui.min.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/script/JQueryUI/locale/easyui-lang-zh_CN.js"></script>
<script charset="utf-8" src="<%=request.getContextPath()%>/script/bootstrap.min.js"></script>
<script charset="utf-8" src="<%=request.getContextPath()%>/script/layer/layer.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/script/common/clientScript.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/script/common/publicScript.js"></script> </head>
<body> <%-- 遮罩层 --%>
<div id="divPageMask4" class="maskStyle">
<div id="maskFont2"></div>
</div> <div class="bg-box indexBox" style="width: 1000px;">
<div class="box">
<div class="manage-list">
<div class="row">
<div class="col-lg-6 col-md-6 col-sm-6 col-xs-12">
<span>个人信息列表</span>
</div>
<input type="button" onclick="doToolbar(this.id);" id="importTJXX"class="btn form-control btn-success" value="体检信息批量导入"style="width: 135px; margin-top: 3px;" />
<input type="button" onclick="doToolbar(this.id);" id="deleteTJXX"class="btn form-control btn-danger" value="全部删除"style="width: 135px; margin-top: 3px;" />
</div>
</div>
</div> <div id="tableDIV" class="tablelist"style="width: 100%; height: 600px;">
<table id="list" width="100%"></table>
</div>
</div> <!-- 弹窗 -->
<div id="importInfoDialog">
<div class="col-lg-10 col-md-10 col-sm-12 col-xs-12 content" style="width:100%;padding-top: 30px;">
<div class="content-border">
<div id="querybox2">
<form id="form3" name="form3" method="POST" action="<%=request.getContextPath()%>/ImportFileBean_Servlet" enctype="multipart/form-data">
<div class="row">
<div class="col-lg-12 col-md-12 col-sm-12 col-xs-12" style="height:80px;">
<input id="excel1" name="excel1" width="100%" type="text" style="width:90%;height: 34px;" >
</div>
</div>
<div class="row">
<div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 text-center">
<input type="button" id="saveImport" class="btn form-control btn-success dialogButtom-btn" value="导入" onclick="doToolbar(this.id);"/>
</div>
</div>
<input type="hidden" id="active2" name="active" />
</form>
</div>
</div>
</div>
</div>
</body> <script type="text/javascript"> $(document).ready(function(){
initDialog();//初始化对话框
initFilebox();
loadGrid();
}); /**工具栏按钮调用方法，传入按钮的id
@id 当前按钮点击事件
**/
function doToolbar(iToolbar){ //点击按钮打开上传附件弹窗
if (iToolbar == 'importTJXX'){
$('#importInfoDialog').dialog('setTitle', '导入');
$('#importInfoDialog').dialog("open");
} //点击按钮打开上传附件弹窗
if (iToolbar == 'deleteTJXX'){
layer.confirm('删除后无法恢复，您是否确认删除?', {
btn: ['确定','取消']
}, function(){
deleteTJXX();
}, function(){ });
} //上传体检excel文件
if (iToolbar == 'saveImport'){
var excel1 = $('#excel1').filebox('getValue')
var excelsuffix=excel1.substring(excel1.lastIndexOf("."),excel1.length); if($('#excel1').filebox('getValue') == ''){
layer.alert('请选择上传文件');
return;
}
if(excelsuffix!=".xls" && excelsuffix!=".xlsx"){
layer.alert('只能导入Excel类型的文件!');
return;
} $("#active2").val("importExamInfo");
showShade();//打开遮罩层
$('#form3').submit(); } } //删除ajax
function deleteTJXX(){
$.ajax({
type:"post",
url:'<%=request.getContextPath()%>/ImportFileBean_Svl',
data:"active=deleteTJXX",
dataType:"json",
success: function(datas){
if(datas[0].MSG == '删除成功'){
layer.msg(datas[0].MSG);
loadGrid();
}else{
layer.alert(datas[0].MSG);
}
}
});
} //遮罩层
function showShade(){
$('#maskFont2').html('导入中，请稍候...');
$('#divPageMask4').show();
} //上传按钮初始化
function initFilebox(){
$('#excel1').filebox({
buttonText: '选择文件',
buttonAlign: 'right'
});
} /**加载 dialog控件**/
function initDialog(){
$('#importInfoDialog').dialog({
width: 360,//宽度设置
height: 200,//高度设置
modal:true,
closed: true,
cache: false,
draggable:false,//是否可移动dialog框设置
//打开事件
onOpen:function(data){},
//读取事件
onLoad:function(data){},
//关闭事件
onClose:function(data){
$('#form3').form('clear');
}
}); } //加载datagrid主页面信息
function loadGrid(){
$('#list').datagrid({
url: '<%=request.getContextPath()%>/ImportFileBean_Svl',
queryParams: {"active":"queryList"},
loadMsg : "信息加载中请稍后!",//载入时信息
nowrap: false,//当数据长度超出列宽时将会自动截取
showFooter:true,
rownumbers: true,
animate:true,
striped : true,//隔行变色
pageSize : 10,//每页记录数
singleSelect : true,//单选模式
pageNumber : 20,//当前页码
pagination:true,
fit:true,
fitColumns: true,//设置边距
columns:[[
{field:'编号',title:'序号',width:fillsize(0.2)},
{field:'体检者',title:'姓名',width:fillsize(0.2)},
{field:'体检日期',title:'体检日期',width:fillsize(0.2)},
{field:'体检机构',title:'体检机构',width:fillsize(0.2)},
{field:'体检结果',title:'体检结果',width:fillsize(0.2)}
]],
onClickRow:function(rowIndex, rowData){
row=rowData;
},
onLoadSuccess: function(data){
iKeyCode='';
},
onLoadError:function(none){ }
}); } //表单提交方法
$('#form3').form({
//定位到servlet位置的url
url:'<%=request.getContextPath()%>/ImportFileBean_Servlet',
//当点击事件后触发的事件
onSubmit: function(data){
},
//当点击事件并成功提交后触发的事件
success:function(data){
var json = eval("("+data+")");
if(json[0].MSG=="导入完成"){
$('#divPageMask4').hide();
$('#importInfoDialog').dialog("close");
layer.msg(json[0].MSG);
loadGrid();
}else if(json[0].MSG=="未找到sheet1"){
layer.alert("工作表名称不符");
}else{
layer.alert(json[0].MSG);
} }
}); </script>
</html>
Servlet代码如下：
Servlet中拦截前台的importExamInfo导入请求，在调用Bean层中的导入逻辑处理方法！
package or.og.jxldemo; import java.io.IOException;
import java.sql.SQLException;
import java.util.Vector; import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import com.jspsmart.upload.SmartUpload;
import com.jspsmart.upload.SmartUploadException;
import com.pantech.base.common.exception.WrongSQLException;
import com.pantech.base.common.tools.JsonUtil;
import com.pantech.base.common.tools.MyTools; import net.sf.json.JSONArray;
public class ImportFileBean_Servlet extends HttpServlet {
private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
} protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("UTF-8");//request编码类型
response.setContentType("text/html;charset=UTF-8");//reponse类型统一 Vector vector=new Vector();
JSONArray jsonArray = new JSONArray();
Vector jsonV = null;//返回结果集
JSONArray jal = null;//返回json对象 ImportFileBean bean = new ImportFileBean(request);
ServletConfig sc = this.getServletConfig();
SmartUpload mySmartUpload = new SmartUpload("UTF-8");
mySmartUpload.initialize(sc, request, response);//获取request及response对象
try {
mySmartUpload.upload();
} catch (SmartUploadException exception1) {
// TODO 自动生成 catch 块
exception1.printStackTrace();
}
getParameters(request, bean,mySmartUpload);//初始化参数
String active = MyTools.StrFiltr(mySmartUpload.getRequest().getParameter("active"));//获取前台提交的操作类型 //导入信息
if(active.equalsIgnoreCase("importExamInfo")){
try {
bean.importExamInfo(mySmartUpload);//调用bean层中逻辑方法
jsonArray = JsonUtil.addJsonParams(jsonArray, "MSG", bean.getMSG());//转换JSON数据格式
response.getWriter().write(jsonArray.toString());//返回服务器端响应 } catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (SmartUploadException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (WrongSQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
} } //获得页面参数
public void getParameters(HttpServletRequest request,ImportFileBean bean,SmartUpload mySmartUpload){ }
}
Servlet中拦截前台的查询datadrig数据网格请求，在调用Bean层中的查询方法！
package or.og.jxldemo; import java.io.IOException;
import java.sql.SQLException;
import java.util.Vector; import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import com.jspsmart.upload.SmartUploadException;
import com.pantech.base.common.exception.WrongSQLException;
import com.pantech.base.common.tools.JsonUtil;
import com.pantech.base.common.tools.MyTools;
import com.pantech.base.common.tools.TraceLog; import net.sf.json.JSONArray; public class ImportFileBean_Svl extends HttpServlet { protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
} /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//设置字符编码为UTF-8
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8"); String active = MyTools.StrFiltr(request.getParameter("active"));// 拿取前台的active值
int pageNum = MyTools.parseInt(request.getParameter("page")); //获得页面page参数 分页
int pageSize = MyTools.parseInt(request.getParameter("rows")); //获得页面rows参数 分页 Vector jsonV = null;//返回结果集
JSONArray jal = null;//返回json对象
ImportFileBean bean = new ImportFileBean(request);
this.getFormData(request, bean); //获取SUBMIT提交时的参数（AJAX适用）
System.out.println("active:--"+active); //查询全部信息
if("queryList".equalsIgnoreCase(active)){
try {
try {
jsonV=bean.queryList(pageNum,pageSize);
} catch (WrongSQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
jal = (JSONArray)jsonV.get(2);
response.getWriter().write("{\"total\":" + MyTools.StrFiltr(jsonV.get(0)) + ",\"rows\":" + jal.toString() + "}"); TraceLog.Trace("response: "+jal.toString());
} catch (SQLException e) {
e.printStackTrace();
}
} //删除全部信息
if("deleteTJXX".equalsIgnoreCase(active)) {
try {
bean.deleteTJXX();
//返回操作信息
jal=JsonUtil.addJsonParams(jal, "MSG", bean.getMSG());
response.getWriter().write(jal.toString());
} catch (WrongSQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } } /**
* 从界面没获取参数
* @date
* @author:yeq
* @param request
* @param MajorSetBean
*/
private void getFormData(HttpServletRequest request, ImportFileBean bean){
} }
Bean代码：
注：Bean中的导入方法中分别对.xsl和.xsls两种表格数据文件进行不同处理！
package or.og.jxldemo; import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector; import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest; import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.jspsmart.upload.SmartFile;
import com.jspsmart.upload.SmartFiles;
import com.jspsmart.upload.SmartUpload;
import com.jspsmart.upload.SmartUploadException;
import com.pantech.base.common.db.DBSource;
import com.pantech.base.common.exception.WrongSQLException;
import com.pantech.base.common.tools.MyTools; public class ImportFileBean { private HttpServletRequest request;
private DBSource db; private String MSG;//提示消息 /**
* <p>Title: </p>
* <p>Description: </p>
* @param request
*/
//获取对象
public ImportFileBean(HttpServletRequest request) {
this.request = request;
this.db = new DBSource(request);
} //查询全部信息
public Vector queryList(int pageNum,int pageSize)throws WrongSQLException,SQLException{
Vector vec = null;
String sql="SELECT [编号],[体检者],convert(nvarchar(19),[体检日期],21)as 体检日期,[体检机构],[体检结果] " +
"FROM [V_个人档案_体检记录] ";
vec = db.getConttexJONSArr(sql, pageNum, pageSize);
return vec;
} //删除全部信息
public void deleteTJXX()throws WrongSQLException,SQLException{
String sql="delete from [V_个人档案_体检记录] ";
if(db.executeInsertOrUpdate(sql)) {
this.setMSG("删除成功");
}else {
this.setMSG("删除失败");
}
} /**
* @Title: saveimpxls
* @Description: 导入体检信息
* @author lupengfei
* @date 2016-7-19
*/
@SuppressWarnings("unchecked")
public void importExamInfo(SmartUpload mySmartUpload) throws SQLException, ServletException, IOException, SmartUploadException, WrongSQLException{
DBSource db = new DBSource(request);
String sql="";
String sqlmx="";
Vector vec = null; // 结果集 String tempsheet="";//sheet名
String tempsoin="";//第1列
String tempsoin2="";
String templine="";//第3列
int sheetnum=0;//sheet计数
String BH="";//编号
String TJZXM="";//体检者姓名
String TJRQ="";//体检日期
String TJJGMC="";//体检机构名称
String TJJG="";//体检结果 String path = "c:/temp/upload";
File f1 = new File(path);
//当文件夹不存在时创建
if (!f1.exists()) {
f1.mkdirs();
}
//Workbook workbook = null;
HSSFWorkbook workbook = null;
XSSFWorkbook workbook2 = null;
Vector vectormx=new Vector(); SmartFiles files = mySmartUpload.getFiles(); //获取文件 SmartFile file= null; //判断 if(files.getCount() > 0){
file = files.getFile(0);
if(file.getSize()<=0){
MSG = "文件内容为空，请确认！";
}
file.saveAs(path +"/"+file.getFileName());
}
path=path +"/"+file.getFileName();
String filename=file.getFileName();
System.out.println("file=:"+file.getFileName()); File file1=new File(path); try {
InputStream is = new FileInputStream(file1);
if (filename.substring(filename.indexOf(".")+1,filename.length()).equals("xls")) {
workbook = new HSSFWorkbook(is);
System.out.println(workbook.getNumberOfSheets()); for(int k=0;k<workbook.getNumberOfSheets();k++){ //控制循环几个Sheet HSSFSheet sheet = workbook.getSheetAt(k); //获取sheet(k)的数据对象
tempsheet=workbook.getSheetName(k);//获取sheet名
int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getLastRowNum();
int rsRows=lastRowIndex; if(lastRowIndex==0){ }else{ if("sheet1".equalsIgnoreCase(tempsheet.trim().toLowerCase())){//对工作表名称进行判断
System.out.println("sheet:"+tempsheet+" rows="+rsRows);
sheetnum++; int locbh=-1;//编号
int loctjzxm=-1;//体检者姓名
int loctjrq=-1;//体检日期
int loctjjgmc=-1;//体检机构名称
int loctjjg=-1;//体检结果 Row currentRow = sheet.getRow(0);// 当前行
int firstColumnIndex = currentRow.getFirstCellNum(); // 首列
int lastColumnIndex = currentRow.getLastCellNum();// 最后一列 //对头标题进行判断记录下标
for (int columnIndex = firstColumnIndex; columnIndex < lastColumnIndex; columnIndex++) {
Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格
tempsoin = this.getCellValue(currentCell, true);// 当前单元格的值 if(tempsoin.equalsIgnoreCase("编号")){
locbh=columnIndex;
}else if(tempsoin.equalsIgnoreCase("体检者")){
loctjzxm=columnIndex;
}else if(tempsoin.equalsIgnoreCase("体检日期")){
loctjrq=columnIndex;
}else if(tempsoin.equalsIgnoreCase("体检机构")){
loctjjgmc=columnIndex;
}else if(tempsoin.equalsIgnoreCase("体检结果")){
loctjjg=columnIndex;
}
} for (int i = 1; i <= rsRows; i++) {//从第2行遍历excel文件
currentRow = sheet.getRow(i);// 当前行
if (currentRow == null) { }else{
firstColumnIndex = currentRow.getFirstCellNum(); // 首列
lastColumnIndex = currentRow.getLastCellNum();// 最后一列
for (int columnIndex =firstColumnIndex; columnIndex < lastColumnIndex; columnIndex++) {
Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格
tempsoin = this.getCellValue(currentCell, true);// 当前单元格的值
if(tempsoin.equalsIgnoreCase("")){
continue;
}else{
if(locbh==-1){
BH=""; //编号列
}else{
if(locbh==columnIndex){
BH=tempsoin.trim();
}
}
if(loctjzxm==-1){
TJZXM=""; //体检者
}else{
if(loctjzxm==columnIndex){
TJZXM=tempsoin.trim();
}
}
if(loctjrq==-1){
TJRQ=""; //体检日期列
}else{
if(loctjrq==columnIndex){
TJRQ=tempsoin.trim();
TJRQ=convertDate(TJRQ);//转换时间类型
}
}
if(loctjjgmc==-1){
TJJGMC=""; //体检机构
}else{
if(loctjjgmc==columnIndex){
TJJGMC=tempsoin.trim();
}
}
if(loctjjg==-1){
TJJG=""; //体检结果列
}else{
if(loctjjg==columnIndex){
TJJG=tempsoin.trim();
}
}
}
} sqlmx=" insert into V_个人档案_体检记录 ([编号],[体检者],[体检日期],[体检机构],[体检结果]) values (" +
"'"+MyTools.fixSql(BH)+"'," +//编号
"'"+MyTools.fixSql(TJZXM)+"'," +//体检者姓名
"'"+MyTools.fixSql(TJRQ)+"'," +//体检日期
"'"+MyTools.fixSql(TJJGMC)+"'," +//体检机构名称
"'"+MyTools.fixSql(TJJG)+"') " ;//体检结果 vectormx.add(sqlmx);
}
}
}
}
}
} else if (filename.substring(filename.indexOf(".")+1,filename.length()).equals("xlsx")) {
workbook2 = new XSSFWorkbook(is);
System.out.println(workbook2.getNumberOfSheets()); for(int k=0;k<workbook2.getNumberOfSheets();k++){ XSSFSheet sheet = workbook2.getSheetAt(k); //获取sheet(k)的数据对象
tempsheet=workbook2.getSheetName(k);//获取sheet名
int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getLastRowNum()+1;
int rsRows=lastRowIndex;
if(lastRowIndex==1){ }else{
System.out.println("sheet:"+tempsheet+" rows="+rsRows); if("sheet1".equalsIgnoreCase(tempsheet.trim().toLowerCase())){
sheetnum++; int locbh=-1;//编号
int loctjzxm=-1;//体检者姓名
int loctjrq=-1;//体检日期
int loctjjgmc=-1;//体检机构名称
int loctjjg=-1;//体检结果 Row currentRow = sheet.getRow(0);// 当前行
int firstColumnIndex = currentRow.getFirstCellNum(); // 首列
int lastColumnIndex = currentRow.getLastCellNum();// 最后一列 for (int columnIndex = firstColumnIndex; columnIndex < lastColumnIndex; columnIndex++) {
Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格
tempsoin = this.getCellValue(currentCell, true);// 当前单元格的值 if(tempsoin.equalsIgnoreCase("编号")){
locbh=columnIndex;
}else if(tempsoin.equalsIgnoreCase("体检者")){
loctjzxm=columnIndex;
}else if(tempsoin.equalsIgnoreCase("体检日期")){
loctjrq=columnIndex;
}else if(tempsoin.equalsIgnoreCase("体检机构")){
loctjjgmc=columnIndex;
}else if(tempsoin.equalsIgnoreCase("体检结果")){
loctjjg=columnIndex;
}
} for (int i = 1; i < rsRows; i++) {//从第2行遍历excel文件
currentRow = sheet.getRow(i);// 当前行 if (currentRow == null) { }else{
firstColumnIndex = currentRow.getFirstCellNum(); // 首列
lastColumnIndex = currentRow.getLastCellNum();// 最后一列
for (int columnIndex = firstColumnIndex; columnIndex < lastColumnIndex; columnIndex++) {
Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格
tempsoin = this.getCellValue(currentCell, true);// 当前单元格的值 if(tempsoin.equalsIgnoreCase("")){
continue;
}else{
if(locbh==-1){
BH=""; //编号列
}else{
if(locbh==columnIndex){
BH=tempsoin.trim();
}
}
if(loctjzxm==-1){
TJZXM=""; //体检者
}else{
if(loctjzxm==columnIndex){
TJZXM=tempsoin.trim();
}
}
if(loctjrq==-1){
TJRQ=""; //体检日期列
}else{
if(loctjrq==columnIndex){
TJRQ=tempsoin.trim();
TJRQ=convertDate(TJRQ);//转换时间类型
}
}
if(loctjjgmc==-1){
TJJGMC=""; //体检机构
}else{
if(loctjjgmc==columnIndex){
TJJGMC=tempsoin.trim();
}
}
if(loctjjg==-1){
TJJG=""; //体检结果列
}else{
if(loctjjg==columnIndex){
TJJG=tempsoin.trim();
}
}
}
} sqlmx=" insert into V_个人档案_体检记录 ([编号],[体检者],[体检日期],[体检机构],[体检结果]) values (" +
"'"+MyTools.fixSql(BH)+"'," +//编号
"'"+MyTools.fixSql(TJZXM)+"'," +//体检者姓名
"'"+MyTools.fixSql(TJRQ)+"'," +//体检日期
"'"+MyTools.fixSql(TJJGMC)+"'," +//体检机构名称
"'"+MyTools.fixSql(TJJG)+"') " ;//体检结果 } vectormx.add(sqlmx);
}
}
}
}
}
if(db.executeInsertOrUpdateTransaction(vectormx)){
this.MSG="导入完成";
}else{
this.MSG="导入失败";
}
if(sheetnum==0){
this.MSG="未找到sheet1";
}
}catch (Exception e) {
e.printStackTrace();
}
finally {
//workbook.close();
}
} /**
* 取单元格的值
* @param cell 单元格对象
* @param treatAsStr 为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”)
* @return
*/
private String getCellValue(Cell cell, boolean treatAsStr) {
if (cell == null) {
return "";
} if (treatAsStr) {
// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
// 加上下面这句，临时把它当做文本来读取
cell.setCellType(Cell.CELL_TYPE_STRING);
} if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
}else {
return String.valueOf(cell.getStringCellValue());
} } // 转换日期
public static String convertDate(String s) throws ParseException{
if (s == null || "".equals(s)) {
return "";
}
// 将excel读取日期时遇到数字 转化为日期
// Excel 的一个有趣之处就是，当您试图将数字转换为日期时，程序会假定该数字是一个序列号，
// 代表自 1900 年 1 月 1 日起所发生的天数。自 1900 年 1 月 1 日 算起的第 39331 天就是 2007 年 9 月 6 日
String rtn = "1900-01-01";
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date date1 = new Date();
date1 = format.parse("1900-01-01");
long i1 = date1.getTime();
// 这里要减去2，(Long.parseLong(s)-2) 不然日期会提前2天，具体原因不清楚，
// 估计和java计时是从1970-01-01开始有关
// 而excel里面的计算是从1900-01-01开始
i1 = i1 / 1000 + ((Long.parseLong(s) - 2) * 24 * 3600);
date1.setTime(i1 * 1000);
rtn = format.format(date1);
return rtn;
} public String getMSG() {
return MSG;
} public void setMSG(String mSG) {
MSG = mSG;
} }
至此Java操作Jxl批量导入数据成功，本人只是写了一个简单的小案例可自行扩展功能及所需表格格式