使用jar包
org.apache.poi poi 3.9 org.apache.poi poi-ooxml 3.9
java代码：
/** * 会员excel导入 * */ @RequestMapping("/customerImport.do") @ResponseBody public String customerImport(@RequestParam("excelFile") CommonsMultipartFile excelFile, HttpServletRequest request) throws MmoException { if (null == excelFile) { throw new MmoException(Status.ARGS_FAIL, "获取excel文件失败"); } List custInfos = new ArrayList (); new ExcelImportUtil2 ().parseExcel(excelFile, request, custInfos, new ExcelToCustInfoImpl()); Map map_params = new HashMap (); map_params.put("custInfos", JSONHelper.toJson(custInfos)); map_params.put("brandId", getSessionUser().getBrandId()); map_params.put("createId", getSessionUser().getRowId()); String result = null; try { result = HttpUtil.doPost(TradePortalUtil.readConfig() + "/cust/importCusttomers", getBrandHead(), map_params); } catch (IOException e) { throw new MmoException(Status.SERVICE_FAIL, "数据服务器异常，请稍后"); } if (StringUtils.isNotBlank(result)) { Res jsonResp = JSONHelper.toObj(result, new TypeToken >() { }); if (jsonResp.getStatus() == Status.SUCCESS) { if (Status.SUCCESS.equals(jsonResp.getStatus())) { result = "success"; } } } return result; } ExcelImportUtil2.java
package com.its.mmo.common.util;import java.io.File;import java.io.FileInputStream;import java.text.DecimalFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.List;import javax.servlet.http.HttpServletRequest;import org.apache.commons.lang.StringUtils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.multipart.commons.CommonsMultipartFile;import com.its.mmo.MmoException;import com.its.mmo.Status;import com.its.mmo.app.customer.model.CustInfo;public class ExcelImportUtil2{ public static final DecimalFormat df = new DecimalFormat("0"); public static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); public void parseExcel(CommonsMultipartFile excelFile, HttpServletRequest request, List datas, ExcelRowToObj rowToObj) throws MmoException { String updatePath = request.getServletContext().getRealPath("upload"); String fileName = excelFile.getFileItem().getName(); File file = new File(updatePath, fileName); if (!file.exists()) { file.mkdirs(); } FileInputStream fileInputStream = null; try { // 保存临时文件 excelFile.transferTo(file); Workbook workbook = null; try { fileInputStream = new FileInputStream(file); workbook = new HSSFWorkbook(fileInputStream); } catch (Exception e) { fileInputStream = new FileInputStream(file); workbook = new XSSFWorkbook(fileInputStream); } // 读取第一个sheet Sheet sheet = workbook.getSheetAt(0); // 读取数据,从第二行开始，第一列岗位名称，第二列岗位描述,第三列岗位编号 for (int i = 1; i <= sheet.getLastRowNum(); i++) { if (StringUtils.isNotBlank(getCellValue(sheet.getRow(i).getCell(0)))) { T t= (T) rowToObj.excelRowToObj(sheet.getRow(i)); datas.add(t); } } if (null != fileInputStream) { fileInputStream.close(); } } catch (Exception e) { throw new MmoException(Status.SERVICE_FAIL, "excel文件错误", e); } finally { file.delete(); } } public static String getCellValue(Cell cell) { Object value = null; if (null == cell) { return null; } switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = sdf.format(cell.getDateCellValue()); } else { value = df.format(cell.getNumericCellValue()); } break; default: value = cell.getStringCellValue(); break; } return String.valueOf(value); }}
ExcelRowToObj.java
package com.its.mmo.common.util;import java.text.ParseException;import org.apache.poi.ss.usermodel.Row;public interface ExcelRowToObj{ public T excelRowToObj(Row row)throws ParseException; }
ExcelToCustInfoImpl.java
package com.its.mmo.app.customer.componeny;import java.text.ParseException;import org.apache.poi.ss.usermodel.Row;import com.its.mmo.app.customer.model.CustInfo;import com.its.mmo.common.util.ExcelImportUtil2;import com.its.mmo.common.util.ExcelRowToObj;public class ExcelToCustInfoImpl implements ExcelRowToObj{ public CustInfo excelRowToObj(Row row) throws ParseException { CustInfo cust = new CustInfo(); cust.setChannelId(ExcelImportUtil2.getCellValue(row.getCell(0))); // cust.setRealName(ExcelImportUtil2.getCellValue(row.getCell(1))); cust.setNick(ExcelImportUtil2.getCellValue(row.getCell(2))); cust.setPicUrl(ExcelImportUtil2.getCellValue(row.getCell(3))); cust.setCustType(ExcelImportUtil2.getCellValue(row.getCell(4))); cust.setGender(ExcelImportUtil2.getCellValue(row.getCell(5)) == null ? null : Integer.valueOf(ExcelImportUtil2.getCellValue(row.getCell(5)))); cust.setMobile(ExcelImportUtil2.getCellValue(row.getCell(6))); cust.setMail(ExcelImportUtil2.getCellValue(row.getCell(7))); cust.setWechat(ExcelImportUtil2.getCellValue(row.getCell(8))); cust.setPostCode(ExcelImportUtil2.getCellValue(row.getCell(9))); cust.setAddress(ExcelImportUtil2.getCellValue(row.getCell(10))); cust.setStoreId(ExcelImportUtil2.getCellValue(row.getCell(11))); cust.setBirthday(ExcelImportUtil2.getCellValue(row.getCell(12)) == null ? null : ExcelImportUtil2.sdf.parse(ExcelImportUtil2.getCellValue(row.getCell(12)))); cust.setOccuption(ExcelImportUtil2.getCellValue(row.getCell(13))); cust.setQq(ExcelImportUtil2.getCellValue(row.getCell(14))); cust.setBlog(ExcelImportUtil2.getCellValue(row.getCell(15))); cust.setPassportType(ExcelImportUtil2.getCellValue(row.getCell(16))); cust.setPassportNo(ExcelImportUtil2.getCellValue(row.getCell(17))); cust.setWorkplace(ExcelImportUtil2.getCellValue(row.getCell(18))); cust.setHobby(ExcelImportUtil2.getCellValue(row.getCell(19))); cust.setIncome(ExcelImportUtil2.getCellValue(row.getCell(20))); cust.setProvince(ExcelImportUtil2.getCellValue(row.getCell(21))); cust.setCity(ExcelImportUtil2.getCellValue(row.getCell(22))); cust.setArea(ExcelImportUtil2.getCellValue(row.getCell(23))); return cust; }}
upload.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%>bookstrap-table Hello World!
dialog.css
.mask { position: fixed; top: 0; left: 0; display: none; width: 100%; height: 100%; background-color: rgba(0,0,0,.75); z-index: 9998; }.dialog_wrp { position: absolute; top: 20%; left: 50%; }.dialog { overflow: visible; border: 0; background-color: #fff; border-radius: 0; -moz-border-radius: 0; -webkit-border-radius: 0;}.dialog_hd { position: relative; border-bottom-width: 0; background-color: #f4f5f9; background-image: -moz-linear-gradient(top,#f4f5f9 0,#f4f5f9 100%); background-image: -webkit-gradient(linear,0 0,0 100%,from(#f4f5f9),to(#f4f5f9)); background-image: -webkit-linear-gradient(top,#f4f5f9 0,#f4f5f9 100%); background-image: -o-linear-gradient(top,#f4f5f9 0,#f4f5f9 100%); background-image: linear-gradient(to bottom,#f4f5f9 0,#f4f5f9 100%); }.dialog_hd h3 { padding-left: 20px; margin: 0; font-weight: 400; font-size: 16px; font-style: normal; color: #222; line-height: 52px;}.pop_closed { position: absolute; top: 50%; margin-top: -8px; right: 20px; width: 16px; height: 16px; line-height: 999em; overflow: hidden; background: url(../images/base_z2b638f.png) 0 -2833px no-repeat;}.dialog_bd { max-height: 580px; overflow-y: auto; padding: 20px;}.msg_dialog .dialog_bd { max-height: 485px; padding: 66px 45px 108px;}.page_msg.simple { text-align: center;}.page_msg .page_inner { display: inline-block; text-align: left; min-width: 55%; background-color: transparent; border: 0; box-shadow: none; -moz-box-shadow: none; -webkit-box-shadow: none; padding: 12px 70px; position: relative;}.page_msg .msg_icon_wrapper { float: left; margin-right: 10px; font-size: 0;}.page_msg h4 { font-size: 14px; font-weight: 400; font-style: normal; line-height: 1.6;}.page_msg p { font-size: 14px; color: #8d8d8d; line-height: 1.6;}.icon_msg { width: 46px; height: 46px; vertical-align: middle; display: inline-block;}.icon_msg.warn { background: url(../images/base_z2b638f.png) 0 -1964px no-repeat;}.page_msg .msg_content { padding-top: 0; overflow: hidden; }.dialog_ft { margin: 0; padding: 16px 0; background-color: #f4f5f9; text-align: center; border-top: 1px solid transparent; box-shadow: none; -moz-box-shadow: none; -webkit-box-shadow: none;}.dialog_ft .btn { margin-left: .3em; margin-right: .3em;} ajaxupload.js
/** * AJAX Upload * Project page - http://valums.com/ajax-upload/ * Copyright (c) 2008 Andris Valums, http://valums.com * Licensed under the MIT license (http://valums.com/mit-license/) */(function () { var d = document, w = window; /** * Get element by id */ function get(element) { if (typeof element == "string") element = d.getElementById(element); return element; } /** * Attaches event to a dom element */ function addEvent(el, type, fn) { if (w.addEventListener) { el.addEventListener(type, fn, false); } else if (w.attachEvent) { var f = function () { fn.call(el, w.event); }; el.attachEvent('on' + type, f) } } /** * Creates and returns element from html chunk */ var toElement = function () { var div = d.createElement('div'); return function (html) { div.innerHTML = html; var el = div.childNodes[0]; div.removeChild(el); return el; } }(); function hasClass(ele, cls) { return ele.className.match(new RegExp('(\\s|^)' + cls + '(\\s|$)')); } function addClass(ele, cls) { if (!hasClass(ele, cls)) ele.className += " " + cls; } function removeClass(ele, cls) { var reg = new RegExp('(\\s|^)' + cls + '(\\s|$)'); ele.className = ele.className.replace(reg, ' '); }// getOffset function copied from jQuery lib (http://jquery.com/) if (document.documentElement["getBoundingClientRect"]) { // Get Offset using getBoundingClientRect // http://ejohn.org/blog/getboundingclientrect-is-awesome/ var getOffset = function (el) { var box = el.getBoundingClientRect(), doc = el.ownerDocument, body = doc.body, docElem = doc.documentElement, // for ie clientTop = docElem.clientTop || body.clientTop || 0, clientLeft = docElem.clientLeft || body.clientLeft || 0, // In Internet Explorer 7 getBoundingClientRect property is treated as physical, // while others are logical. Make all logical, like in IE8. zoom = 1; if (body.getBoundingClientRect) { var bound = body.getBoundingClientRect(); zoom = (bound.right - bound.left) / body.clientWidth; } if (zoom > 1) { clientTop = 0; clientLeft = 0; } var top = box.top / zoom + (window.pageYOffset || docElem && docElem.scrollTop / zoom || body.scrollTop / zoom) - clientTop, left = box.left / zoom + (window.pageXOffset || docElem && docElem.scrollLeft / zoom || body.scrollLeft / zoom) - clientLeft; return { top: top, left: left }; } } else { // Get offset adding all offsets var getOffset = function (el) { if (w.jQuery) { return jQuery(el).offset(); } var top = 0, left = 0; do { top += el.offsetTop || 0; left += el.offsetLeft || 0; } while (el = el.offsetParent); return { left: left, top: top }; } } function getBox(el) { var left, right, top, bottom; var offset = getOffset(el); left = offset.left; top = offset.top; right = left + el.offsetWidth; bottom = top + el.offsetHeight; return { left: left, right: right, top: top, bottom: bottom }; } /** * Crossbrowser mouse coordinates */ function getMouseCoords(e) { // pageX/Y is not supported in IE // http://www.quirksmode.org/dom/w3c_cssom.html if (!e.pageX && e.clientX) { // In Internet Explorer 7 some properties (mouse coordinates) are treated as physical, // while others are logical (offset). var zoom = 1; var body = document.body; if (body.getBoundingClientRect) { var bound = body.getBoundingClientRect(); zoom = (bound.right - bound.left) / body.clientWidth; } return { x: e.clientX / zoom + d.body.scrollLeft + d.documentElement.scrollLeft, y: e.clientY / zoom + d.body.scrollTop + d.documentElement.scrollTop }; } return { x: e.pageX, y: e.pageY }; } /** * Function generates unique id */ var getUID = function () { var id = 0; return function () { return 'ValumsAjaxUpload' + id++; } }(); function fileFromPath(file) { return file.replace(/.*(\/|\\)/, ""); } function getExt(file) { return (/[.]/.exec(file)) ? /[^.]+$/.exec(file.toLowerCase()) : ''; } /** * Cross-browser way to get xhr object */ var getXhr = function () { var xhr; return function () { if (xhr) return xhr; if (typeof XMLHttpRequest !== 'undefined') { xhr = new XMLHttpRequest(); } else { var v = [ "Microsoft.XmlHttp", "MSXML2.XmlHttp.5.0", "MSXML2.XmlHttp.4.0", "MSXML2.XmlHttp.3.0", "MSXML2.XmlHttp.2.0" ]; for (var i = 0; i < v.length; i++) { try { xhr = new ActiveXObject(v[i]); break; } catch (e) { } } } return xhr; } }();// Please use AjaxUpload , Ajax_upload will be removed in the next version Ajax_upload = AjaxUpload = function (button, options) { if (button.jquery) { // jquery object was passed button = button[0]; } else if (typeof button == "string" && /^#.*/.test(button)) { button = button.slice(1); } button = get(button); this._input = null; this._button = button; this._disabled = false; this._submitting = false; // Variable changes to true if the button was clicked // 3 seconds ago (requred to fix Safari on Mac error) this._justClicked = false; this._parentDialog = d.body; if (window.jQuery && jQuery.ui && jQuery.ui.dialog) { var parentDialog = jQuery(this._button).parents('.ui-dialog'); if (parentDialog.length) { this._parentDialog = parentDialog[0]; } } this._settings = { // Location of the server-side upload script action: 'upload.php', // File upload name name: 'userfile', // Additional data to send data: {}, // Submit file as soon as it's selected autoSubmit: true, // The type of data that you're expecting back from the server. // Html and xml are detected automatically. // Only useful when you are using json data as a response. // Set to "json" in that case. responseType: false, // Location of the server-side script that fixes Safari // hanging problem returning "Connection: close" header closeConnection: '', // Class applied to button when mouse is hovered hoverClass: 'hover', // When user selects a file, useful with autoSubmit disabled onChange: function (file, extension) { }, // Callback to fire before file is uploaded // You can return false to cancel upload onSubmit: function (file, extension) { }, // Fired when file upload is completed // WARNING! DO NOT USE "FALSE" STRING AS A RESPONSE! onComplete: function (file, response) { } }; // Merge the users options with our defaults for (var i in options) { this._settings[i] = options[i]; } this._createInput(); this._rerouteClicks(); }// assigning methods to our class AjaxUpload.prototype = { setData: function (data) { this._settings.data = data; }, disable: function () { this._disabled = true; }, enable: function () { this._disabled = false; }, // removes instance destroy: function () { if (this._input) { if (this._input.parentNode) { this._input.parentNode.removeChild(this._input); } this._input = null; } }, /** * Creates invisible file input above the button */ _createInput: function () { var self = this; var input = d.createElement("input"); input.setAttribute('type', 'file'); input.setAttribute('name', this._settings.name); var styles = { 'position': 'absolute', 'margin': '-5px 0 0 -175px', 'padding': 0, 'width': '220px', 'height': '30px', 'fontSize': '14px', 'opacity': 0, 'cursor': 'pointer', 'display': 'none', 'zIndex': 2147483583 //Max zIndex supported by Opera 9.0-9.2x // Strange, I expected 2147483647 // Doesn't work in IE :( //,'direction' : 'ltr' }; for (var i in styles) { input.style[i] = styles[i]; } // Make sure that element opacity exists // (IE uses filter instead) if (!(input.style.opacity === "0")) { input.style.filter = "alpha(opacity=0)"; } this._parentDialog.appendChild(input); addEvent(input, 'change', function () { // get filename from input var file = fileFromPath(this.value); if (self._settings.onChange.call(self, file, getExt(file)) == false) { return; } // Submit form when value is changed if (self._settings.autoSubmit) { self.submit(); } }); // Fixing problem with Safari // The problem is that if you leave input before the file select dialog opens // it does not upload the file. // As dialog opens slowly (it is a sheet dialog which takes some time to open) // there is some time while you can leave the button. // So we should not change display to none immediately addEvent(input, 'click', function () { self._justClicked = true; setTimeout(function () { // we will wait 3 seconds for dialog to open self._justClicked = false; }, 2500); }); this._input = input; }, _rerouteClicks: function () { var self = this; // IE displays 'access denied' error when using this method // other browsers just ignore click() // addEvent(this._button, 'click', function(e){ // self._input.click(); // }); var box, dialogOffset = {top: 0, left: 0}, over = false; addEvent(self._button, 'mouseover', function (e) { if (!self._input || over) return; over = true; box = getBox(self._button); if (self._parentDialog != d.body) { dialogOffset = getOffset(self._parentDialog); } }); // We can't use mouseout on the button, // because invisible input is over it addEvent(document, 'mousemove', function (e) { var input = self._input; if (!input || !over) return; if (self._disabled) { removeClass(self._button, self._settings.hoverClass); input.style.display = 'none'; return; } var c = getMouseCoords(e); if ((c.x >= box.left) && (c.x <= box.right) && (c.y >= box.top) && (c.y <= box.bottom)) { input.style.top = c.y - dialogOffset.top + 'px'; input.style.left = c.x - dialogOffset.left + 'px'; input.style.display = 'block'; addClass(self._button, self._settings.hoverClass); } else { // mouse left the button over = false; var check = setInterval(function () { // if input was just clicked do not hide it // to prevent safari bug if (self._justClicked) { return; } if (!over) { input.style.display = 'none'; } clearInterval(check); }, 25); removeClass(self._button, self._settings.hoverClass); } }); }, /** * Creates iframe with unique name */ _createIframe: function () { // unique name // We cannot use getTime, because it sometimes return // same value in safari :( var id = getUID(); // Remove ie6 "This page contains both secure and nonsecure items" prompt // http://tinyurl.com/77w9wh var iframe = toElement(''); iframe.id = id; iframe.style.display = 'none'; d.body.appendChild(iframe); return iframe; }, /** * Upload file without refreshing the page */ submit: function () { var self = this, settings = this._settings; if (this._input.value === '') { // there is no file return; } // get filename from input var file = fileFromPath(this._input.value); // execute user event if (!(settings.onSubmit.call(this, file, getExt(file)) == false)) { // Create new iframe for this submission var iframe = this._createIframe(); // Do not submit if user function returns false var form = this._createForm(iframe); form.appendChild(this._input); // A pretty little hack to make uploads not hang in Safari. Just call this // immediately before the upload is submitted. This does an Ajax call to // the server, which returns an empty document with the "Connection: close" // header, telling Safari to close the active connection. // http://blog.airbladesoftware.com/2007/8/17/note-to-self-prevent-uploads-hanging-in-safari if (settings.closeConnection && /AppleWebKit|MSIE/.test(navigator.userAgent)) { var xhr = getXhr(); // Open synhronous connection xhr.open('GET', settings.closeConnection, false); xhr.send(''); } form.submit(); d.body.removeChild(form); form = null; this._input = null; // create new input this._createInput(); var toDeleteFlag = false; addEvent(iframe, 'load', function (e) { if (// For Safari iframe.src == "javascript:'%3Chtml%3E%3C/html%3E';" || // For FF, IE iframe.src == "javascript:'';") { // First time around, do not delete. if (toDeleteFlag) { // Fix busy state in FF3 setTimeout(function () { d.body.removeChild(iframe); }, 0); } return; } var doc = iframe.contentDocument ? iframe.contentDocument : frames[iframe.id].document; // fixing Opera 9.26 if (doc.readyState && doc.readyState != 'complete') { // Opera fires load event multiple times // Even when the DOM is not ready yet // this fix should not affect other browsers return; } // fixing Opera 9.64 if (doc.body && doc.body.innerHTML == "false") { // In Opera 9.64 event was fired second time // when body.innerHTML changed from false // to server response approx. after 1 sec return; } var response; if (doc.XMLDocument) { // response is a xml document IE property response = doc.XMLDocument; } else if (doc.body) { // response is html document or plain text response = doc.body.innerHTML; if (settings.responseType && settings.responseType.toLowerCase() == 'json') { // If the document was sent as 'application/javascript' or // 'text/javascript', then the browser wraps the text in a // tag and performs html encoding on the contents. In this case, // we need to pull the original text content from the text node's // nodeValue property to retrieve the unmangled content. // Note that IE6 only understands text/html if (doc.body.firstChild && doc.body.firstChild.nodeName.toUpperCase() == 'PRE') { response = doc.body.firstChild.firstChild.nodeValue; } if (response) { response = window["eval"]("(" + response + ")"); } else { response = {}; } } } else { // response is a xml document var response = doc; } settings.onComplete.call(self, file, response); // Reload blank page, so that reloading main page // does not re-submit the post. Also, remember to // delete the frame toDeleteFlag = true; // Fix IE mixed content issue iframe.src = "javascript:'';"; }); } else { // clear input to allow user to select same file // Doesn't work in IE6 // this._input.value = ''; d.body.removeChild(this._input); this._input = null; // create new input this._createInput(); } }, /** * Creates form, that will be submitted to iframe */ _createForm: function (iframe) { var settings = this._settings; // method, enctype must be specified here // because changing this attr on the fly is not allowed in IE 6/7 var form = toElement(' '); form.style.display = 'none'; form.action = settings.action; form.target = iframe.name; d.body.appendChild(form); // Create hidden input element for each data key for (var prop in settings.data) { var el = d.createElement("input"); el.type = 'hidden'; el.name = prop; el.value = settings.data[prop]; form.appendChild(el); } return form; } };})();