1、前台jsp
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ include file="/WEB-INF/views/include/taglib.jsp"%>
<html>
<head>
<title>Excel数据入表</title>
<meta name="decorator" content="default"/>
<script type="text/javascript" src="${ctxStatic}/jquery/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="${ctxStatic}/jquery-validation/1.11.0/jquery.validate.min.js"></script>
<script type="text/javascript">
$(function(){
$("#btnSubmit").click(function(){
var excelDoc = $("#excelDoc").val();
if(excelDoc == null || '' == excelDoc){
alert("请选择要导入的文件!");
return false;
}
var suffix = excelDoc.substring(excelDoc.lastIndexOf("."),excelDoc.length);
suffix = suffix.toLowerCase();
if(!(suffix == '.xls' || suffix == '.xlsx')){
alert('请选择正确的Excel文件!');
return false;
}
});
});
</script>
</head>
<body>
<ul class="nav nav-tabs">
<li class="active"><a href="###">Excel数据入表</a></li>
</ul><br/>
<form action="${ctx}/hdb/outsideData/excelDataInsertIntoTable" enctype="multipart/form-data" method="post" class="form-horizontal">
<div class="control-group">
<label class="control-label">数据库名:</label>
<div class="controls">
<input id="databaseName" name="databaseName" placeholder="数据库名必填" style="text-align:center" type="text" maxlength="60" class="input-medium" />
</div>
</div>
<div class="control-group">
<label class="control-label">数据表名:</label>
<div class="controls">
<input id="tableName" name="tableName" placeholder="数据表名必填" style="text-align:center" type="text" maxlength="60" class="input-medium" />
</div>
</div>
<div class="control-group">
<label class="control-label">始终行号:</label>
<div class="controls">
<input id="startLine" name="startLine" type="text" maxlength="30" placeholder="默认为1" style="text-align:center;width:70px"/>
-
<input id="endLine" name="endLine" type="text" maxlength="30" placeholder="默认尾行" style="text-align:center;width:70px"/>
</div>
</div>
<!-- <div class="control-group">
<label class="control-label">解析类型:</label>
<div class="controls">
<input id="analysisType0" name="analysisType" type="radio" value="0"/>所有列无规则
<input id="analysisType0" name="analysisType" type="radio" value="1"/>所有列有规则
<input id="analysisType1" name="analysisType" type="radio" value="2"/>部分列无规则
<input id="analysisType1" checked="checked" name="analysisType" type="radio" value="3"/>部分列有规则
</div>
</div> -->
<div class="control-group">
<label class="control-label">解析规则:</label>
<div class="controls">
<textarea id="analysisRoles" name="analysisRoles" rows="2" placeholder="务必严格按照规则填写" style="width:400px"></textarea>
</div>
</div>
<div class="control-group">
<label class="control-label">规则详情:</label>
<div class="controls">
<p>
1、无分隔符:列序号(从0开始)-对应字段名称<br/>
2、一个分隔符:列序号-对应字段名-分隔符号-位置(0-前 1-后)<br/>
3、两个分隔符:列序号-对应字段名-分隔符1-标识符(除'-'外的任意字符)-分隔符2<br/>
4、相邻两列之间用#分开<br/>
例:0-bank_name-(-0#0-org_code-(-s-)#9-account_template
</p>
</div>
</div>
<div class="control-group">
<label class="control-label">Excel文件:</label>
<div class="controls">
<input id="excelDoc" name="excelDoc" type="file" maxlength="60" class="input-medium" />
</div>
</div>
<div class="control-group">
<span style="margin-left:180px;color:red">${result}</span>
</div>
<div class="form-actions">
<input id="btnSubmit" class="btn btn-primary" type="submit" value="导入文件"/>
</div>
</form>
</body>
</html>
2、实体OutsideData.java
package com.hdb.modules.hdb.entity;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import com.hdb.common.persistence.BaseEntity;
/**
* 2016-08-06
* @author 逺塵
*/
@Entity
public class OutsideData extends BaseEntity {
private static final long serialVersionUID = -5128222582703319704L;
private Long id;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
}
3、OutsideDataController
package com.hdb.modules.hdb.web;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.http.HttpServletRequest;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import com.hdb.common.web.BaseController;
import com.hdb.modules.hdb.service.OutsideDataService;
/**
* 2016-08-06
* 操作外部数据的Controller
*
* @author 逺塵
*/
@Controller
@RequestMapping(value = "${adminPath}/hdb/outsideData")
public class OutsideDataController extends BaseController {
@Autowired
private OutsideDataService outsideDataService;
@RequestMapping(value = "toExcelDataInsertPage")
public String toExcelDataInsertPage() {
/** 库名列表:放入session或者static块 */
/** 表名列表:可将外部数据的表都加个outer_前缀便于查询和向前端显示 */
return "modules/hdb/excelDataInsertPage";
}
@RequestMapping(value = "excelDataInsertIntoTable")
public String excelDataInsertIntoTable(String databaseName, String tableName, Integer startLine, Integer endLine,
String analysisRoles, MultipartFile excelDoc, HttpServletRequest request) {
String jsp = "modules/hdb/excelDataInsertPage";
if (databaseName == null || "".equals(databaseName)) {
request.setAttribute("result", "数据库名未填写,导入失败!");
return jsp;
} else {
List<String> databaseNames = outsideDataService.getAllDatabaseNames();
Set<String> namesSet = new HashSet<String>(databaseNames);
if (!namesSet.contains(databaseName)) {
request.setAttribute("result", "数据库名填写有误,导入失败!");
return jsp;
} else {
if (tableName == null || "".equals(tableName)) {
request.setAttribute("result", "数据表名未填写,导入失败!");
return jsp;
} else {
List<String> tableNames = outsideDataService.getAllTableNamesByDatabaseName(databaseName);
Set<String> tablesSet = new HashSet<String>(tableNames);
if (!tablesSet.contains(tableName)) {
request.setAttribute("result", "数据表名填写有误,导入失败!");
return jsp;
} else {
List<Map<String, String>> analysisData = analysisExcelDoc(startLine, endLine, analysisRoles,
excelDoc);
int num = 0;
if (analysisData != null) {
try {
num = outsideDataService.importComments(tableName, analysisData);
request.setAttribute("result", "导入成功,共计:" + num + "条记录");
} catch (Exception e) {
request.setAttribute("result", "你输入的解析规则有误,导入失败!");
}
} else {
request.setAttribute("result", "您选择的文档中没有数据,导入失败!");
}
}
}
}
}
return jsp;
}
/**
* 根据规则解析Excel
*
* @param startLine
* @param endLine
* @param analysisRoles
* @param excelDoc
* @return
*/
private List<Map<String, String>> analysisExcelDoc(Integer startLine, Integer endLine, String analysisRoles,
MultipartFile excelDoc) {
List<Map<String, String>> listInfo = null;
if (excelDoc != null) {
listInfo = new ArrayList<Map<String, String>>();
InputStream iS = null;
Workbook wb = null;
try {
iS = excelDoc.getInputStream();
wb = WorkbookFactory.create(iS);
} catch (Exception e) {
e.printStackTrace();
}
Sheet sheet = wb.getSheetAt(0);
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
if (firstRowNum == lastRowNum) {
return null;
} else {
if (startLine == null || "".equals(startLine) || startLine < firstRowNum) {
/** 默认第0行为表头 :不解析 */
startLine = firstRowNum + 1;
}
if (endLine == null || "".equals(endLine) || endLine > lastRowNum) {
/** 默认解析到最后一行 */
endLine = lastRowNum;
}
/** 解析规则 */
List<Map<String, Object>> roleMapList = getAnalysisRole(analysisRoles);
for (int i = startLine; i <= endLine; i++) {
Row row = sheet.getRow(i);
Map<String, String> map = new LinkedHashMap<String, String>();
for (Map<String, Object> roleMap : roleMapList) {
Integer colNum = (Integer) roleMap.get("colNum");
String colName = (String) roleMap.get("colName");
String splitMark = "";
String bob = "";
String splitMark1 = "";
String splitMark2 = "";
Cell cell = row.getCell(colNum);
String cellValue = getCellValue(cell);
int size = roleMap.size();
switch (size) {
case 4:
splitMark = (String) roleMap.get("splitMark");
bob = (String) roleMap.get("bob");
int index = cellValue.indexOf(splitMark);
if ("0".equals(bob)) {
cellValue = cellValue.substring(0, index);
} else {
cellValue = cellValue.substring(index + 1);
}
break;
case 5:
splitMark1 = (String) roleMap.get("splitMark1");
splitMark2 = (String) roleMap.get("splitMark2");
int beginIndex = cellValue.indexOf(splitMark1);
int endIndex = cellValue.indexOf(splitMark2);
cellValue = cellValue.substring(beginIndex + 1, endIndex);
break;
}
map.put(colName, cellValue);
}
listInfo.add(map);
}
}
}
return listInfo;
}
/**
* 获取解析规则 :
* 1、列序号-字段名称
* 2、列序号-字段名称-分隔符-前后(0-前 1-后)
* 3、列序号-字段名称-分隔符1-识别符-分隔符2(识别符为任意字符)
*
* @param analysisRoles
* @return
*/
private List<Map<String, Object>> getAnalysisRole(String analysisRoles) {
List<Map<String, Object>> rolesMapList = new ArrayList<Map<String, Object>>();
if (analysisRoles != null && !"".equals(analysisRoles)) {
String[] roles = analysisRoles.split("#");
for (int i = 0; i < roles.length; i++) {
Map<String, Object> rolesMap = new HashMap<String, Object>();
String[] split = roles[i].split("-");
switch (split.length) {
case 2:
rolesMap.put("colNum", Integer.parseInt(split[0]));
rolesMap.put("colName", split[1]);
break;
case 4:
rolesMap.put("colNum", Integer.parseInt(split[0]));
rolesMap.put("colName", split[1]);
rolesMap.put("splitMark", split[2]);
rolesMap.put("bob", split[3]);
break;
case 5:
rolesMap.put("colNum", Integer.parseInt(split[0]));
rolesMap.put("colName", split[1]);
rolesMap.put("splitMark1", split[2]);
rolesMap.put("identifier", split[3]);
rolesMap.put("splitMark2", split[4]);
break;
}
rolesMapList.add(rolesMap);
}
} else {
/** 默认应该无规则解析所有列:有空再说吧 */
}
return rolesMapList;
}
/**
* 获取解析规则
*
* @param analysisType
* @param analysisRole
* @return
*/
// private String getAnalysisRole(Integer analysisType, String analysisRole)
// {
//
// StringBuffer role = new StringBuffer();
//
// switch (analysisType) {
// case 0:
// role.append("all-none:");
//
// break;
// case 1:
// role.append("all-role:");
//
// break;
// case 2:
// role.append("part-none:");
//
// break;
// case 3:
// role.append("part-role:");
// role.append(analysisRole);
// break;
// }
// return role.toString();
// }
/**
* 将cell中的内容都转化为字符串
*
* @param cell
* @return
*/
private String getCellValue(Cell cell) {
if (cell == null) {
return "null";
}
String ret;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
ret = "null";
break;
case Cell.CELL_TYPE_BOOLEAN:
ret = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
ret = "null";
break;
case Cell.CELL_TYPE_FORMULA:
Workbook wb = cell.getSheet().getWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
FormulaEvaluator evaluator = createHelper.createFormulaEvaluator();
ret = getCellValue(evaluator.evaluateInCell(cell));
break;
case Cell.CELL_TYPE_NUMERIC:
short dataFormat = cell.getCellStyle().getDataFormat();
if (dataFormat != 0) {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日 hh:mm:ss");
double value = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(value);
ret = simpleDateFormat.format(date);
} else {
ret = NumberToTextConverter.toText(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
ret = cell.getRichStringCellValue().getString();
break;
default:
ret = "null";
}
return ret;
}
}
4、OutsideDataService
package com.hdb.modules.hdb.service;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import com.hdb.common.service.BaseService;
import com.hdb.modules.hdb.dao.OutsideDataDao;
/**
* 2016-08-06
*
* @author 逺塵
*/
@Component
@Transactional(readOnly = true)
public class OutsideDataService extends BaseService {
@SuppressWarnings("unused")
private static Logger logger = LoggerFactory.getLogger(OutsideDataService.class);
@Autowired
private OutsideDataDao outsideDataDao;
public List<String> getAllDatabaseNames() {
return outsideDataDao.getAllDatabaseNames();
}
public List<String> getAllTableNamesByDatabaseName(String databaseName) {
return outsideDataDao.getAllTableNamesByDatabaseName(databaseName);
}
@Transactional(readOnly = false)
public int importComments(String tableName, List<Map<String, String>> analysisData) {
int n = batchInsert(tableName, analysisData);
return n;
}
/**
* 批量插入
* @param corpBidInfoList
*/
@Transactional(readOnly = false)
public Integer batchInsert(String tableName, List<Map<String, String>> analysisData) {
int size = analysisData.size();
if (size > 0) {
StringBuffer sb = new StringBuffer();
sb.append("INSERT INTO ");
sb.append(tableName + "(");
/** 取出所有字段名 */
Map<String, String> map = analysisData.get(0);
List<String> columnNames = new ArrayList<String>();
@SuppressWarnings("rawtypes")
Iterator iter = map.entrySet().iterator();
while (iter.hasNext()) {
@SuppressWarnings("rawtypes")
Map.Entry entry = (Map.Entry) iter.next();
Object key = entry.getKey();
String keyStr = key.toString();
columnNames.add(keyStr);
sb.append(keyStr + ",");
}
int lastIndex = sb.lastIndexOf(",");
String sub = sb.substring(0, lastIndex);
sb = new StringBuffer(sub);
sb.append(") VALUES ");
for (int i = 0; i < analysisData.size(); i++) {
Map<String, String> value = analysisData.get(i);
sb.append("(");
for (int j = 0; j < columnNames.size(); j++) {
String val = value.get(columnNames.get(j));
sb.append("'" + val + "',");
}
int lastIndexOf = sb.lastIndexOf(",");
sub = sb.substring(0, lastIndexOf);
sb = new StringBuffer(sub);
sb.append("),");
}
int lastIndexOf = sb.lastIndexOf(",");
sub = sb.substring(0, lastIndexOf);
outsideDataDao.createSqlQuery(sub).executeUpdate();
}
return analysisData.size();
}
}
5、OutsideDataDao
package com.hdb.modules.hdb.dao;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Component;
import com.hdb.common.persistence.BaseDao;
import com.hdb.common.persistence.BaseDaoImpl;
import com.hdb.modules.hdb.entity.OutsideData;
public interface OutsideDataDao extends OutsideDataDaoCustom, CrudRepository<OutsideData, Long> {
@Query(value = "SELECT schema_name FROM information_schema.schemata",nativeQuery = true)
List<String> getAllDatabaseNames();
@Query(value = "SELECT table_name FROM information_schema.tables WHERE table_schema = ?1",nativeQuery = true)
List<String> getAllTableNamesByDatabaseName(String databaseName);
}
interface OutsideDataDaoCustom extends BaseDao<OutsideData> {
}
@Component
class OutsideDataDaoImpl extends BaseDaoImpl<OutsideData> implements OutsideDataDaoCustom {
}