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=" 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=" 135px; margin-top: 3px;" /> <input type="button" onclick="doToolbar(this.id);" id="deleteTJXX"class="btn form-control btn-danger" value="全部删除"style=" 135px; margin-top: 3px;" /> </div> </div> </div> <div id="tableDIV" class="tablelist"style=" 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="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="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({ 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:'序号',fillsize(0.2)}, {field:'体检者',title:'姓名',fillsize(0.2)}, {field:'体检日期',title:'体检日期',fillsize(0.2)}, {field:'体检机构',title:'体检机构',fillsize(0.2)}, {field:'体检结果',title:'体检结果',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批量导入数据成功，本人只是写了一个简单的小案例可自行扩展功能及所需表格格式