为了解决大批量公司人员导入的问题,实在是懒得写sql脚本手动导入,工作之余顺便调研了Jacob插件,实现了解析Excel,并将人员信息数据批量导入到数据库的小工具。本来想做成上传后解析Excel插入数据库的,没想到读取上传的流结构的方法,就做成现在这样的了:前台主要是负责触发方法。有大神能指教下就更好了。查询了国内外的api用Jacob的实在是太少了,大部分都用poi去操作office,涉及到的文档也少,源码也没有看到解析上传流的源码,虽然功能实现了,但是还是有些遗憾,并不是初衷。
1、 下载JACOB包:http://danadler.com/jacob/
2、 安装配置
解压jacobBin_17.zip
(1)jacob-1.14.3-x64.dll直接放到JDK的jre\bin文件夹下
(2)maven依赖:
<!-- https://mvnrepository.com/artifact/net.sf.jacob-project/jacob -->
<dependency>
<groupId>net.sf.jacob-project</groupId>
<artifactId>jacob</artifactId>
<version>1.14.3</version>
</dependency>
(3)代码部分:
1)前端代码部分
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<html>
<head>
<title>上传xx培训平台人员</title>
<script type="text/javascript" src="https://cdn.bootcss.com/jquery/3.2.1/jquery.min.js"></script>
<script type="text/javascript" src="http://malsup.github.io/jquery.form.js"></script>
</head>
<body>
<div style="padding: 260px;background: url(http://img.atme8.com/FoDeQwh6JC9_SUvIGI497W78JU3a) no-repeat 330px 94px">
<form id="file_form" action="/user/upload" enctype="multipart/form-data" method="post">
<input type="file" name="file" id="file_input" />
<input type="submit" value="文件上传" id='upFile-btn'>
</form>
</div>
<script type="text/javascript">
$(function() {
$("#file_form").submit(
function() {
//首先验证文件格式
var fileName = $('#file_input').val();
if (fileName === '') {
alert('请选择文件');
return false;
}
var fileType = (fileName.substring(fileName
.lastIndexOf(".") + 1, fileName.length))
.toLowerCase();
if (fileType !== 'xls' && fileType !== 'xlsx') {
alert('文件格式不正确,excel文件!');
return false;
}
$("#file_form").ajaxSubmit({
dataType : "json",
success : function(data, textStatus) {
if (data['result'] === 'OK') {
console.log('上传文件成功');
} else {
console.log('文件格式错误');
}
return false;
}
});
return false;
});
});
</script>
</body>
</html>
2)后端代码部分之java部分
package com.heitian.ssm.controller;import com.heitian.ssm.model.User; import com.heitian.ssm.service.UserService; import com.heitian.ssm.utils.ResponseUtil;import org.apache.log4j.Logger; import org.springframework.http.ResponseEntity;import org.springframework.stereotype.Controller; import org.springframework.util.CollectionUtils; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.InputStream; import java.math.BigDecimal; import java.util.*; import com.jacob.com.*; import com.jacob.activeX.*; import org.springframework.web.multipart.MultipartFile;import org.springframework.web.multipart.MultipartHttpServletRequest;@Controller@RequestMapping("/user")public class UserController {private Logger log = Logger.getLogger(UserController.class); @Resource private UserService userService; private static ActiveXComponent xl; //Excel对象(防止打开多个) private static Dispatch workbooks = null;//工作簿对象 private static Dispatch workbook = null; //具体工作簿 private static Dispatch sheet = null; private static String filename =null; private static boolean readonly = false;private Dispatch currentSheet = null;// 当前sheet private static int rowNum = 0; private static int columnCount = 0; @RequestMapping(value = "/upload", method = RequestMethod.POST) public ResponseEntity<Object> uploadExcel(HttpServletRequest request, HttpServletResponse response) throws Exception { // MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;// InputStream in =null; // List<List<Object>> listob = null; // MultipartFile fileInfo = multipartRequest.getFile("file"); // if(fileInfo.isEmpty()){ // throw new Exception("文件不存在!"); // } // in = fileInfo.getInputStream(); String file = "F:\\new\\xxx培训账号配置.xlsx";//文件地址 OpenExcel(file,false);//false为不显示打开Excel GetValue(); CloseExcel(false); return ResponseUtil.success("SUCCESS"); } //打开Excel文档 private static void OpenExcel(String file,boolean f) { try { filename = file; xl = new ActiveXComponent("Excel.Application"); xl.setProperty("Visible", new Variant(f)); workbooks = xl.getProperty("Workbooks").toDispatch(); workbook = Dispatch.invoke(workbooks, "Open", Dispatch.Method, new Object[]{filename, new Variant(false), new Variant(readonly)},//是否以只读方式打开 new int[1] ).toDispatch(); }catch(Exception e) { e.printStackTrace(); } } //关闭Excel文档 private static void CloseExcel(boolean f) { try { Dispatch.call(workbook,"Save"); Dispatch.call(workbook, "Close", new Variant(f)); } catch (Exception e) { e.printStackTrace(); } finally { xl.invoke("Quit", new Variant[] {}); System.out.print("Excel 关闭成功!!"); } } //读取值,这部分比较主要,解析Excel表结构,获取数据插入数据库 private void GetValue() { //sheet = Dispatch.get(workbook,"ActiveSheet").toDispatch(); Dispatch sheets = Dispatch.get(workbook,"Sheets").toDispatch();//获取所有工作表 sheet = Dispatch.invoke(sheets, "Item", Dispatch.Get,new Object[] { new String("Sheet1") }, new int[1]).toDispatch();//获取第一个工作表 //以名称读写sheet //最大列数 rowNum = this.getRowCount(); //最大行数 columnCount = this.getColumnCount(); for(int r = 3; r<= rowNum; r++) { List<String> list = new ArrayList<String>(); List<User> userList = new ArrayList<User>();//自己定义一个需要接值的bean的list for (int c = 1; c <= columnCount; c++) { //获取当前位置 String position = this.getCellPosition(r, c); Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[]{position}, new int[1]).toDispatch(); String value = Dispatch.get(cell, "Value").toString(); list.add(value);//将解析的数据放入list中 } User user = new User(); String ddId = null; if (list.get(0).contains(".")) { BigDecimal bd = new BigDecimal(list.get(0)); ddId = bd.toPlainString(); } else { ddId = list.get(0); } user.setDdUserId(ddId); user.setUserName(list.get(1)); user.setRealName(list.get(1)); String phone = null; if (list.get(2).contains(".")) {//为了防止电话号码变成科学计数法 BigDecimal bd = new BigDecimal(list.get(2)); phone = bd.toPlainString(); } else { phone = list.get(2); } user.setPhone(phone); if (userService.selectByPhone(phone) == null) {//通过电话号码查询重复 int result = userService.insertUser(user);//读取解析后插入数据库(此处换成自己的接口就可以了) } else { //打印出重复的信息列表 userList.add(user); } if(!CollectionUtils.isEmpty(userList)) { System.out.print("重复添加的人员信息有:"); for(int i = 0; i < userList.size(); i++) { System.out.println("钉钉号:"+userList.get(i).getDdUserId()+", 用户名:"+userList.get(i).getRealName()+", 电话号码:"+userList.get(i).getPhone()); } } } } /** * 获取最大行数 * @return */ private int getRowCount() { currentSheet=this.getCurrentSheet(); Dispatch UsedRange = Dispatch.get(currentSheet, "UsedRange").toDispatch(); Dispatch rows = Dispatch.get(UsedRange, "Rows").toDispatch(); int num = Dispatch.get(rows, "count").getInt(); return num; } /** * 获取最大列数 * @return */ private int getColumnCount() { currentSheet=this.getCurrentSheet(); Dispatch UsedRange = Dispatch.get(currentSheet, "UsedRange").toDispatch(); Dispatch Columns = Dispatch.get(UsedRange, "Columns").toDispatch(); int num = Dispatch.get(Columns, "count").getInt(); return num; } /** * 得到当前sheet * @return */ private Dispatch getCurrentSheet() { currentSheet = Dispatch.get(workbook, "ActiveSheet").toDispatch(); return currentSheet; } /** * 获取位置 * @param rnum 最大行数 * @param cnum 最大列数 */ private String getCellPosition(int rnum,int cnum) { String cposition = ""; if(cnum > 26) { int multiple = (cnum)/26; int remainder = (cnum)%26; char mchar = (char)(multiple+64); char rchar = (char)(remainder+64); cposition = mchar+""+rchar; } else { cposition = (char)(cnum+64)+""; } cposition += rnum; return cposition; } }
(4)表格部分: