场景:通过项目中的webuploader框架将excel中角色用户数据导入数据库
<%@ page contentType="text/html;charset=UTF-8" language="java"%>
<%@ include file="/WEB-INF/views/include/taglib.jsp"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ "/";
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>上传文件</title>
<body>
<%--<c:set var="itemSavePath" value="${fns:getConfig('platform.resource.baseSavePath')}"/>--%>
<div id="uploader" class="wu-example">
<!--用来存放文件信息-->
<div id="thelist" class="uploader-list"></div>
<div class="btns">
<div id="picker">选择文件</div>
<div id=templeateDirectory class="webuploader-pick">下载模板</div>
<div id="btnStart" class="webuploader-pick">开始上传</div>
<div id="btnClose" class="webuploader-pick">关闭</div>
</div>
</div>
<iframe style="display:none;" name="hiddenIframe1" id="hiddenIframe1"></iframe>
</body>
<script src="${ctxStatic}/assets/global/plugins/jquery.min.js" type="text/javascript"></script>
<script src="${ctxStatic}/assets/global/plugins/json/json2.js" type="text/javascript"></script>
<script src="${ctxStatic}/assets/global/plugins/layer/layer.js"></script>
<link rel="stylesheet" type="text/css" href="${ctxStatic}/assets/global/plugins/webuploader/webuploader.css" />
<script type="text/javascript" src="${ctxStatic}/assets/global/plugins/webuploader/webuploader.js"></script>
<script type="text/javascript" src="${ctxStatic}/modules/workflow/common.js"></script>
<style type="text/css">
body{
margin: 0px 0px;
overflow: hidden;
}
#uploader{
position: absolute;
left: 5px;
right:5px;
top: 5px;
bottom: 5px;
}
#thelist{
position: absolute;
left: 0px;
right: 0px;
top: 0px;
bottom: 45px;
border: 1px solid #00b7ee;
border-bottom: 2px solid #00b7ee;
overflow-y: auto;
}
#picker{
display: inline-block !important;
height: 20px !important;
padding: 6px 5px;
}
#picker div{
height: 20px !important;
}
.webuploader-pick{
padding: 3px 10px;
font-size: 12px;
height: 20px;
font-family: 微软雅黑;
}
.btns{
position: absolute;
width: 100%;
height: 40px;
bottom: 0px;
text-align: right;
padding-right: 20px;
}
#btnClose{
margin: 0px 20px 0px 5px;
}
.file{
position: relative;
height: 30px;
border: 1px solid silver;
}
.file .progress{
position: absolute;
background-color: #00b7ee;
left: 0px;
top: 0px;
bottom: 0px;
}
.file .name{
position: absolute;
left: 0px;
right: 30px;
top: 0px;
bottom: 0px;
font-size: 12px;
font-family: 微软雅黑;
padding: 4px 10px ;
}
.file .remove{
position: absolute;
right: 0px;
top: 0px;
bottom: 0px;
width: 30px;
border: 1px solid transparent;
background: url(${ctxStatic}/image/delete.png) no-repeat center center;
cursor: pointer;
}
.file .remove:hover{
border: 1px solid rgb(20, 99, 216);
}
.display{
pointer-events:none;
}
.color{
background:#DDDDDD;
}
</style>
<script type="text/javascript">
//更新哪个父层,true表示最外层,flase表示倒数第二层
function updateParentFrame(flag){
if(flag){
parent.parent.updateNodeIcon($("#id").val(),"/image/funcgroup.png");
}else{
parent.updateNodeIcon($("#id").val(),"/image/funcgroup.png");
}
}
/**
* 当文件被加入队列之前触发,此事件的handler返回值为false,
* 则此文件不会被添加进入队列
*/
function onBeforeFileQueued(file){
console.log("onBeforeFileQueued方法调用");
var fileName = file.name;
if(fileName.length > 50){
layer.msg('文件名超出长度限制!',{icon:7});
return false;
}
if(file.size > 2147483648){
layer.msg( fileName+ '此文件超过2G,请上传小于2G的文件!',{icon:7});
return false;
}
//格式验证
var fileTypes = new Array("xls","xlsx");
var fileTypeFlag = "0";
var newFileName = file.name.split('.');
newFileName = newFileName[newFileName.length-1];
for(var i=0;i<fileTypes.length;i++){
if(fileTypes[i] == newFileName){
fileTypeFlag = "1";
$("#fileType").val(fileTypes[i]);
}
}
if(fileTypeFlag == "0"){
var str = "";
for(var k=0;k<fileTypes.length;k++){
if(k>0){
str+="、"+fileTypes[k];
}else{
str+=fileTypes[k];
}
}
layer.msg("请上传"+str+"格式文件!",{icon:7});
return false;
}
}
/**
* 当文件被加入队列以后触发
*/
function onFilesQueued(files){
console.log("onFilesQueued方法调用");
$('#btnStart').attr("disabled", "disabled");
$('#btnStart').css("cursor", "not-allowed");
$.ajax({
url: 'generateFileKeys?count=' + files.length,
success: jQuery.proxy(function(keys, status, xhr){
for(var i=0; i<this.length; i++){
this[i].key = keys[i];
}
uploaderUI.addFiles(this);
$('#btnStart').removeAttr("disabled");
$('#btnStart').css("cursor", "pointer");
}, files), error: function(xhr, status, err){
$('#btnStart').removeAttr("disabled");
$('#btnStart').css("cursor", "pointer");
}
});
}
/**
* 当某个文件的分块在发送前触发,主要用来询问是否要添加附带参数,
* 大文件在开起分片上传的前提下此事件可能会触发多次。
*/
function onUploadBeforeSend(object, data, headers){
console.log("执行文件块上传前");
}
/**
* 上传过程中触发,携带上传进度
*/
function onUploadProgress(file, percentage){
console.log("执行文件块上传");
uploaderUI.uploadProgress(file, percentage);
}
/**
* 单个文件上传完成(当文件上传成功时触发)
*/
function onUploadSuccess(file, result){
}
/**
* 文件是否被服务端接受
*/
function onUploadAccept(obj, ret){
console.log("执行onUploadAccept()");
}
/**
* 单个文件上传完成(不管成功或者失败,文件上传完成时触发)
*/
function onUploadComplete(file, result){
console.log("执行onUploadSuccess");
}
var cancelFiles = [];
var windowStatus ="0";
/**
* 当所有文件上传结束时触发
*/
function onUploadFinished(){
$('#btnStart').removeClass("display");
$('#btnClose').removeClass("display");
$('#picker').removeClass("display");
$('#btnStart').removeClass("color");
$('#btnClose').removeClass("color");
$('#picker').find('.webuploader-pick').removeClass("color");
console.log("全部文件上传完了");
layer.msg("上传完毕,开始导入....",{icon:1});
var successObj = uploader.getStats("successNum");// 上传的文件个数
var data = ${dataMap}
var cancelFileList = "";
if(cancelFiles.length>0){
for(var i=0;i<cancelFiles.length;i++){
if(i>0){
cancelFileList = cancelFileList+","+cancelFiles[i];
}else{
cancelFileList = cancelFiles[i];
}
}
}
var fileInfo = { importId:data.importId, importType:data.importType,
userFilePath:data.userFilePath,cancel:cancelFileList,
subGuid:data.subGuid};
$.ajax({
url: 'uploadRoleUserFiles',
type: 'POST',
async:false,
data: JSON.stringify(fileInfo),
dataType: 'json',
contentType:'application/json;charse=UTF-8',
success: function(result){
if(result.status=="200"){
window.opener.succeed(data.importId,result);
}else if(result.status=="error"){
window.opener.showError(result.message);
}
},
error: function(e){
}
});
}
$(document).ready(function(){
var BASE_URL = '${ctxStatic}/assets/global/plugins/webuploader';
window.uploader = WebUploader.create({
// 选完文件后,是否自动上传。
auto: false,
// swf文件路径
swf: BASE_URL + '/Uploader.swf',
// 文件接收服务端。
server: ctx_js +'/rolemgr/roles-group/uploadFiles',
formData: ${dataMap},
thumb: false,
compress: false,
chunked: true,
chunkSize: 1048576, //1M
fileSingleSizeLimit : 2147483648,
// 选择文件的按钮。可选。
// 内部根据当前运行是创建,可能是input元素,也可能是flash.
pick: '#picker',
// 只允许选择指定格式文件。
accept:{
title: 'excel',
extensions: 'xls,xlsx',
mimeTypes: 'application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
}
});
uploader.on('beforeFileQueued',onBeforeFileQueued);
uploader.on('filesQueued', onFilesQueued);
uploader.on('uploadBeforeSend', onUploadBeforeSend)
uploader.on('uploadProgress', onUploadProgress);
uploader.on('uploadFinished', onUploadFinished);
uploader.on('uploadComplete', onUploadComplete);
window.uploaderUI = {
el: $('#thelist'),
files: [],
addFiles: function(files){
var content = '', file;
for(var i=0,l=files.length; i<l; i++){
file = files[i];
var fileName = file.name;
if(fileName.length > 47){
fileName = fileName.substring(0,47) + "...";
}
content += '<div fileid="' + file.id + '" id="' + file.id + '" class="file f_' + file.id + '"><div class="progress"/>' +
'<div class="name">' + fileName + '</div><div class="remove" title="移除"/></div>';
}
this.el.append(content);
},
removeFiles: function(){
},
clear: function(){
},
removeDelete: function(file){
$('.f_' + file.id + ' .remove', this.el).remove();
},
uploadProgress: function(file, percentage){
$('.remove').removeClass();
var item_progress_el = $('.f_' + file.id + ' .progress', this.el);
var p = Math.round(percentage * 10000) / 100;
var w = p + '%';
item_progress_el.width(w);
var item_name_el = $('.f_' + file.id + ' .name', this.el);
var fileName = file.name;
if(fileName.length > 42){
fileName = fileName.substring(0,42) + "...";
}
item_name_el.text(fileName + '(' + w + ')');
}
}
$('#btnStart').click(function(){
var num = uploader.getFiles().length;
if(num == 0){
layer.msg('请选择要上传文件!',{icon:7});
return;
}
if($(this).attr('disabled') == 'disabled')
return;
$('#btnStart').addClass("display");
$('#btnClose').addClass("display");
$('#picker').addClass("display");
$('#btnStart').addClass("color");
$('#btnClose').addClass("color");
$('#picker').find('.webuploader-pick').addClass("color");
uploader.upload();
})
$('#btnClose').click(function(){
window.close();
});
//wxy 2021-11-15下载导出文件模板
$("#templeateDirectory").on('click',function(){
var url=ctx_js + "/rolemgr/roles-group/downloadRoleUsersTemplementFile";
//使用iframe进行无跳转页面下载
$("#hiddenIframe1").attr("src", encodeURI(url));
})
$('#thelist').on('click', '.file .remove', function(){
var fileItem = $(this).parents('.file');
var fileId= fileItem.attr('fileid');
var file = uploader.getFile(fileId);
var status = file.getStatus();
if(status == "progress"){// 右侧移除图标删除正在上传的文件
uploader.cancelFile(fileId);
uploader.removeFile(fileId, true);
fileItem.remove();
var dataMap = ${dataMap};
var data = {name: file.name,userFilePath:dataMap.userFilePath,isCompress:"compress"};
window.opener.deleteFile(data,"");
cancelFiles.push(file.name);
}else{
uploader.removeFile(fileId, true);
fileItem.remove();
}
})
//window.onunload=show;
});
function show(){
var data = ${dataMap}
window.opener.closeAndDelete(windowStatus,data.userFilePath,data.importId);
};
window.onbeforeunload = function(e) {
var fileId = $('#thelist')[0].childNodes[0].id;
var file = uploader.getFile(fileId);
var status = file.getStatus();
if(status == "progress"){// 右侧移除图标删除正在上传的文件
return '有文件正在上传';
}else{
window.close();
}
};
</script>
</head>
</html>
在使用的时候发现它是先将文件上传到服务器
/***
* @author wxy 20211028
* @param file
* @param request
* @param response
*/
@RequestMapping(value="uploadFiles",method = RequestMethod.POST)
public void uploadFiles(@RequestParam MultipartFile file,HttpServletRequest request,
HttpServletResponse response) {
String userFilePath = request.getParameter("userFilePath");
//上传文件夹路径
String importZipFolderPath = importZipPath+separator+userFilePath;
UploadFileUtils.uploadFile(file,importZipFolderPath);
}
工具类
package com.gisquest.platform.utils;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.springframework.web.multipart.MultipartFile;
public class UploadFileUtils {
public static void uploadFile(MultipartFile file,String importZipFolderPath) {
//判断文件夹路径是否存在
File importZipFolder = new File(importZipFolderPath);
if(!importZipFolder.exists()) {
importZipFolder.mkdirs();
}
String fileName = file.getOriginalFilename();
//上传的文件名称
String importZipFilePath = importZipFolderPath+File.separator+fileName;
InputStream in = null;
FileOutputStream out = null;
try {
in = file.getInputStream();
out = new FileOutputStream(importZipFilePath);
int len = 0;
byte[] byt = new byte[in.available()];
while((len = in.read(byt))!=-1) {
out.write(byt);
}
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
if(in != null)
in.close();
if(out!=null)
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* @author wxy 20211028
* 判断目录与目录下所有文件名称长度是否超出限制
* @param files
* 目录下文件集合
* @param maxLength
* 规定的最大长度
* @return
*/
public static boolean checkNameLength(File[] files,int maxLength,String suffixName,String specificationName){
for(int i=0;i<files.length;i++){
File file = files[i];
if(file.getPath().toLowerCase().endsWith(suffixName)){
if(file.getName().length()>maxLength && file.getName().equals(specificationName)){
return false;
}
}
}
return true;
}
}
然后通过前端监听方法onUploadFinished,将服务器上的文件通过poi读取到数据库
function onUploadFinished(){
$('#btnStart').removeClass("display");
$('#btnClose').removeClass("display");
$('#picker').removeClass("display");
$('#btnStart').removeClass("color");
$('#btnClose').removeClass("color");
$('#picker').find('.webuploader-pick').removeClass("color");
console.log("全部文件上传完了");
layer.msg("上传完毕,开始导入....",{icon:1});
var successObj = uploader.getStats("successNum");// 上传的文件个数
var data = ${dataMap}
var cancelFileList = "";
if(cancelFiles.length>0){
for(var i=0;i<cancelFiles.length;i++){
if(i>0){
cancelFileList = cancelFileList+","+cancelFiles[i];
}else{
cancelFileList = cancelFiles[i];
}
}
}
var fileInfo = { importId:data.importId, importType:data.importType,
userFilePath:data.userFilePath,cancel:cancelFileList,
subGuid:data.subGuid};
$.ajax({
url: 'uploadRoleUserFiles',
type: 'POST',
async:false,
data: JSON.stringify(fileInfo),
dataType: 'json',
contentType:'application/json;charse=UTF-8',
success: function(result){
if(result.status=="200"){
window.opener.succeed(data.importId,result);
}else if(result.status=="error"){
window.opener.showError(result.message);
}
},
error: function(e){
}
});
}
@RequestMapping(value="uploadRoleUserFiles",method = RequestMethod.POST)
@ResponseBody
public String uploadRoleUserFiles(HttpServletRequest request,
HttpServletResponse response,@RequestBody Map<String, String> requestParams) {
String subGuid = requestParams.get("subGuid");
String parentGroupId = requestParams.get("importId");
String username = request.getSession().getAttribute("username_") + "";
String password = request.getSession().getAttribute("password_") + "";
String userFilePath =requestParams.get("userFilePath");
String importZipFilePath = importZipPath+separator+userFilePath;
File excelFile = new File(importZipFilePath);
File[] files = excelFile.listFiles(); //获取路径下的文件
String fileName="";
if(files!=null && files.length >0) {
for(File unCompressFile:files) {
//获取文件名称
fileName = unCompressFile.getName();
}
}
String filePath = importZipFilePath+"\\"+fileName;
File readFile = new File(filePath);
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(readFile);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
MultipartFile multipartFile = null;
try {
multipartFile = new MockMultipartFile(readFile.getName(), readFile.getName(),
ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);
} catch (IOException e) {
e.printStackTrace();
}
Map<String,MultipartFile> param = new HashMap<>();
param.put("file", multipartFile);
String serviceUrl = Global.getConfig("platform.rest.baseurl")+"rolemgr/importRoleUserData/"+ subGuid+"/"+parentGroupId;
String result = RestUtils.postFile(serviceUrl, username, password,param);
String[] array = result.split(Global.REST_RESPONSE_SPLIT);
String respBody = array[1];
return respBody;
}
重要一点 :如何将file文件转化成multifile
/**
* Copyright ® 2015 Zhejiang Shine Technology Co., Ltd.
* All right reserved.
*/
package com.gisquest.platform.common.utils;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.io.IOUtils;
import org.apache.http.HttpEntity;
import org.apache.http.NameValuePair;
import org.apache.http.auth.AuthScope;
import org.apache.http.auth.UsernamePasswordCredentials;
import org.apache.http.client.CredentialsProvider;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpDelete;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.methods.HttpPut;
import org.apache.http.client.methods.HttpUriRequest;
import org.apache.http.entity.StringEntity;
import org.apache.http.entity.mime.MultipartEntityBuilder;
import org.apache.http.entity.mime.content.ByteArrayBody;
import org.apache.http.impl.client.BasicCredentialsProvider;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.message.BasicHeader;
import org.apache.http.protocol.HTTP;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.session.Session;
import org.apache.shiro.subject.Subject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpHeaders;
import org.springframework.web.multipart.MultipartFile;
import com.gisquest.platform.common.config.Global;
import com.gisquest.platform.common.mapper.JsonMapper;
import brave.Tracing;
import brave.httpclient.TracingHttpClientBuilder;
/**
* @Description REST服务调用工具类
*/
public class RestUtils {
private static Logger logger = LoggerFactory.getLogger(RestUtils.class);
/**
* GET请求
*
* @param serviceUrl
* @param username
* @param password
* @return response code & response body
*/
public static String get1(String serviceUrl, String username,
String password) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
logger.debug("username:[{}], password:[{}]", username, password);
}
HttpURLConnection connect = null;
try {
// 实例一个URL资源
URL url = new URL(serviceUrl);
// 实例一个HTTP CONNECT
connect = (HttpURLConnection) url.openConnection();
connect.setRequestMethod(Global.REST_METHOD_GET);
String encoded = new String(Base64.encodeBase64(new String(username
+ ":" + password).getBytes()));
connect.setRequestProperty("Authorization", "Basic " + encoded);
connect.connect();
int code = connect.getResponseCode();
// 将返回的值存入到String中
BufferedReader brd = new BufferedReader(new InputStreamReader(
connect.getInputStream()));
StringBuilder sb = new StringBuilder();
String line;
while ((line = brd.readLine()) != null) {
sb.append(line);
}
brd.close();
if(logger.isDebugEnabled()){
logger.debug("返回状态码:[{}], 返回报文:{}", code, sb.toString());
}
return code + Global.REST_RESPONSE_SPLIT + sb.toString();
} catch (Exception e) {
logger.error("通信失败:{}", e.getLocalizedMessage(), e);
return "999" + Global.REST_RESPONSE_SPLIT + e.getLocalizedMessage();
} finally {
if (connect != null) {
connect.disconnect();
}
}
}
/***
* GET请求
*
* @param serviceUrl
* 服务地址
* @param username
* 用户名
* @param password
* 密码
* @return
* @throws IOException
*/
public static String get(String serviceUrl, String username, String password) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
logger.debug("username:[{}], password:[{}]", username, password);
}
try{
HttpGet request = null;
request = new HttpGet(serviceUrl);
//请求头添加业务token
addToken(request);
request.addHeader(new BasicHeader(HttpHeaders.CONTENT_TYPE,
"application/json"));
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET, "gbk"));
String result = executeRequest(request, username, password);
if(logger.isDebugEnabled()){
logger.debug("返回报文:{}", result);
}
return result;
} catch (Exception e) {
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (request != null) {
request.releaseConnection();
}
}
}
/***
* POST请求
*
* @param serviceUrl
* 服务地址
* @param username
* 用户名
* @param password
* 密码
* @param json
* json字符串,例如: "{ \"username\":\"zhangsan\" }" ;其中属性名必须带双引号
* @return
* @throws IOException
*/
public static String post(String serviceUrl, String username,
String password, String json) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
logger.debug("上送报文:{}", json);
}
HttpPost request = null;
try {
request = new HttpPost(serviceUrl);
request.addHeader(new BasicHeader(HttpHeaders.CONTENT_TYPE,
"application/json"));
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET,
"utf-8"));
@SuppressWarnings("deprecation")
StringEntity entity = new StringEntity(json, HTTP.UTF_8);
request.setEntity(entity);
String result = executeRequest(request, username, password);
if(logger.isDebugEnabled()){
logger.debug("返回报文:{}", result);
}
return result;
} catch (Exception e) {
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (request != null) {
request.releaseConnection();
}
}
}
/***
* PUT请求
*
* @param serviceUrl
* 服务地址
* @param username
* 用户名
* @param password
* 密码
* @param json
* json字符串,例如: "{ \"username\":\"zhangsan\" }" ;其中属性名必须带双引号
* @return
* @throws IOException
*/
public static String put(String serviceUrl, String username,
String password, String json) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
logger.debug("上送报文:{}", json);
}
HttpPut request = null;
try {
request = new HttpPut(serviceUrl);
request.addHeader(new BasicHeader(HttpHeaders.CONTENT_TYPE,
"application/json"));
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET,
"utf-8"));
@SuppressWarnings("deprecation")
StringEntity entity = new StringEntity(json, HTTP.UTF_8);
request.setEntity(entity);
String result = executeRequest(request, username, password);
if(logger.isDebugEnabled()){
logger.debug("返回报文:{}", result);
}
return result;
} catch (Exception e) {
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (request != null) {
request.releaseConnection();
}
}
}
/**
* PUT请求
*
* @param serviceUrl
* 服务地址
* @param username
* 用户名
* @param password
* 密码
* @param nameValuePairs
* 键值集合
* @return
*/
public static String putList(String serviceUrl, String username,
String password, List<NameValuePair> nameValuePairs) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
logger.debug("上送报文:{}", nameValuePairs.toString());
}
HttpPut request = null;
try {
request = new HttpPut(serviceUrl);
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET,
"utf-8"));
request.setEntity(new UrlEncodedFormEntity(nameValuePairs, "UTF-8"));
String result = executeRequest(request, username, password);
if(logger.isDebugEnabled()){
logger.debug("返回报文:{}", result);
}
return result;
} catch (Exception e) {
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (request != null) {
request.releaseConnection();
}
}
}
/***
* DELETE请求
*
* @param serviceUrl
* 服务地址
* @param username
* 用户名
* @param password
* 密码
* @remark 因delete是javascript中的关键字,故通过dwr调用时不能使用此关键字,所以该方法命名为del
* @return
* @throws IOException
*/
public static String del(String serviceUrl, String username, String password) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
logger.debug("username:[{}], password:[{}]", username, password);
}
HttpDelete request = null;
try {
request = new HttpDelete(serviceUrl);
request.addHeader(new BasicHeader(HttpHeaders.CONTENT_TYPE,
"application/json"));
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET,
"utf-8"));
String result = executeRequest(request, username, password);
if(logger.isDebugEnabled()){
logger.debug("返回报文:{}", result);
}
return result;
} catch (Exception e) {
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (request != null) {
request.releaseConnection();
}
}
}
/**
* 执行请求体的通用方法
*
* @param request
* @param username
* @param password
* @return
*/
public static String executeRequest(HttpUriRequest request,
String username, String password) {
CloseableHttpClient client = null;
CloseableHttpResponse response = null;
try {
username = URLEncoder.encode(username, "utf-8");
} catch (UnsupportedEncodingException e) {
logger.error("用户名[{}]编码失败,目标编码格式:[UTF-8]", username);
}
try {
String zipkinConfig = Global.getConfig("gisq.zipkin.enable");
Tracing tracing = null;
if ("true".equals(zipkinConfig)){
tracing = SpringContextHolder.getBean("tracing");
}
LinkedList<CloseableHttpResponse> httpResponses = new LinkedList<CloseableHttpResponse>();
CredentialsProvider provider = new BasicCredentialsProvider();
UsernamePasswordCredentials credentials = new UsernamePasswordCredentials(
username, password);
provider.setCredentials(AuthScope.ANY, credentials);
if (tracing != null) {
String id = resorvleTracing(tracing);
request.setHeader("gisq_request_id",id);
client = TracingHttpClientBuilder.create(tracing).setDefaultCredentialsProvider(provider).build();
}else {
client = HttpClientBuilder.create()
.setDefaultCredentialsProvider(provider).build();
}
//请求头添加业务token
addToken(request);
response = client.execute(request);
int statusCode = response.getStatusLine().getStatusCode();
httpResponses.add(response);
InputStream inputStream = null;
String output = "";
HttpEntity entity = response.getEntity();
if (entity != null && entity.getContent() != null) {
inputStream = response.getEntity().getContent();
output = IOUtils.toString(inputStream, "UTF-8");
inputStream.close();
}
return statusCode + Global.REST_RESPONSE_SPLIT + output;
} catch (Exception e) {
logger.error("通信失败:{}", e.getLocalizedMessage(), e);
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (response != null) {
try {
response.close();
} catch (IOException e) {
logger.error(e.getMessage());
}
}
if (client != null) {
try {
client.close();
} catch (IOException e) {
logger.error(e.getMessage());
}
}
}
}
/**
* 不需要用户名和密码的get请求
*
* @param serviceUrl
* @return
*/
public static String getWithoutAuthorization(String serviceUrl) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
}
HttpGet request = null;
CloseableHttpClient httpClient = null;
CloseableHttpResponse response = null;
try {
String zipkinConfig = Global.getConfig("gisq.zipkin.enable");
Tracing tracing = null;
if ("true".equals(zipkinConfig)){
tracing = SpringContextHolder.getBean("tracing");
}
request = new HttpGet(serviceUrl);
request.addHeader(new BasicHeader(HttpHeaders.CONTENT_TYPE,
"application/json"));
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET, "gbk"));
//请求头添加业务token
addToken(request);
if (tracing != null) {
String id = resorvleTracing(tracing);
httpClient = TracingHttpClientBuilder.create(tracing).build();
request.setHeader("gisq_request_id",id);
}else {
httpClient = HttpClients.createDefault();
}
response = httpClient.execute(request);
int statusCode = response.getStatusLine().getStatusCode();
InputStream inputStream = null;
String output = "";
HttpEntity entity = response.getEntity();
if (entity != null && entity.getContent() != null) {
inputStream = response.getEntity().getContent();
output = IOUtils.toString(inputStream, "UTF-8");
inputStream.close();
}
if(logger.isDebugEnabled()){
logger.debug("返回状态码:[{}], 返回报文:{}", statusCode, output);
}
return statusCode + Global.REST_RESPONSE_SPLIT + output;
} catch (Exception e) {
logger.error("通信失败:{}", e.getLocalizedMessage(), e);
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (response != null) {
try {
response.close();
} catch (IOException e) {
logger.error(e.getMessage());
}
}
if (httpClient != null) {
try {
httpClient.close();
} catch (IOException e) {
logger.error(e.getMessage());
}
}
request.releaseConnection();
}
}
/**
* 不需要用户名和密码的post请求
*
* @param serviceUrl
* @return
*/
public static String postWithoutAuthorization(String serviceUrl, String json) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
logger.debug("上送报文:{}", json);
}
HttpPost request = null;
CloseableHttpClient httpClient = null;
CloseableHttpResponse response = null;
try {
String zipkinConfig = Global.getConfig("gisq.zipkin.enable");
Tracing tracing = null;
if ("true".equals(zipkinConfig)){
tracing = SpringContextHolder.getBean("tracing");
}
request = new HttpPost(serviceUrl);
request.addHeader(new BasicHeader(HttpHeaders.CONTENT_TYPE,
"application/json"));
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET, "gbk"));
//请求头添加业务token
addToken(request);
if(logger.isDebugEnabled()){
logger.debug("json:{}",json);
}
@SuppressWarnings("deprecation")
StringEntity postEntity = new StringEntity(json, HTTP.UTF_8);
request.setEntity(postEntity);
if (tracing != null) {
String id = resorvleTracing(tracing);
request.setHeader("gisq_request_id",id);
httpClient = TracingHttpClientBuilder.create(tracing).build();
}else {
httpClient = HttpClients.createDefault();
}
response = httpClient.execute(request);
if(logger.isDebugEnabled()){
logger.debug("Content-Type:{}",response.getHeaders("Content-Type")==null?"":response.getHeaders("Content-Type")[0].getValue());
}
int statusCode = response.getStatusLine().getStatusCode();
InputStream inputStream = null;
String output = "";
HttpEntity entity = response.getEntity();
if (entity != null && entity.getContent() != null) {
inputStream = response.getEntity().getContent();
output = IOUtils.toString(inputStream, "UTF-8");
inputStream.close();
}
if(logger.isDebugEnabled()){
logger.debug("返回状态码:[{}], 返回报文:{}", statusCode, output);
}
return statusCode + Global.REST_RESPONSE_SPLIT + output;
} catch (Exception e) {
logger.error("通信失败:{}", e.getLocalizedMessage(), e);
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (response != null) {
try {
response.close();
} catch (IOException e) {
logger.error(e.getMessage());
}
}
if (httpClient != null) {
try {
httpClient.close();
} catch (IOException e) {
logger.error(e.getMessage());
}
}
request.releaseConnection();
}
}
/**
* 不需要用户名和密码的post请求
*
* @param serviceUrl
* @return
*/
public static String postWithoutAuthorizations(String serviceUrl, String json) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
logger.debug("上送报文:{}", json);
}
HttpPost request = null;
CloseableHttpClient httpClient = null;
CloseableHttpResponse response = null;
try {
request = new HttpPost(serviceUrl);
request.addHeader(new BasicHeader(HttpHeaders.CONTENT_TYPE,
"application/json"));
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET, "gbk"));
//请求头添加业务token
addToken(request);
@SuppressWarnings("deprecation")
StringEntity postEntity = new StringEntity(json, HTTP.UTF_8);
request.setEntity(postEntity);
httpClient = HttpClients.createDefault();
response = httpClient.execute(request);
int statusCode = response.getStatusLine().getStatusCode();
InputStream inputStream = null;
String output = "";
HttpEntity entity = response.getEntity();
if (entity != null && entity.getContent() != null) {
inputStream = response.getEntity().getContent();
output = IOUtils.toString(inputStream, "UTF-8");
inputStream.close();
}
if(logger.isDebugEnabled()){
logger.debug("返回状态码:[{}], 返回报文:{}", statusCode, output);
}
return statusCode + Global.REST_RESPONSE_SPLIT + output;
} catch (Exception e) {
logger.error("通信失败:{}", e.getLocalizedMessage(), e);
return "404" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (response != null) {
try {
response.close();
} catch (IOException e) {
logger.error(e.getMessage());
}
}
if (httpClient != null) {
try {
httpClient.close();
} catch (IOException e) {
logger.error(e.getMessage());
}
}
request.releaseConnection();
}
}
/***
* @Description POST请求(支持多文件上传)
* @author zhangp
* @date 2015-12-10
*
* @param serviceUrl
* 服务地址
* @param username
* 用户名
* @param password
* 密码
* @param file
* 文件
* @return
* @throws IOException
*/
public static String postFile(String serviceUrl, String username,
String password, Map<String, MultipartFile> mpFileMap) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
logger.debug("username:[{}], password:[{}]", username, password);
}
HttpPost request = null;
try {
request = new HttpPost(serviceUrl);
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET,
"utf-8"));
//请求头添加业务token
addToken(request);
MultipartEntityBuilder entityBuilder = MultipartEntityBuilder
.create();
MultipartFile mpFile = null;
for (String key : mpFileMap.keySet()) {
mpFile = mpFileMap.get(key);
entityBuilder.addPart(
mpFile.getOriginalFilename(),
new ByteArrayBody(mpFile.getBytes(), mpFile
.getOriginalFilename()));
}
HttpEntity entity = entityBuilder.build();
request.setEntity(entity);
String result = executeRequest(request, username, password);
if(logger.isDebugEnabled()){
logger.debug("返回报文:{}", result);
}
return result;
} catch (Exception e) {
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (request != null) {
request.releaseConnection();
}
}
}
/***
* POST请求
*
* @param serviceUrl
* 服务地址
* @param username
* 用户名
* @param password
* 密码
* @param json
* json字符串,例如: "{ \"username\":\"zhangsan\" }" ;其中属性名必须带双引号
* @return
* @throws IOException
*/
public static String postWithToken(HttpPost request, String username,
String password, String json) {
try {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", request.getURI());
logger.debug("上送报文:{}", json);
logger.debug("username:[{}], password:[{}]", username, password);
}
request.addHeader(new BasicHeader(HttpHeaders.CONTENT_TYPE,
"application/json"));
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET,
"utf-8"));
//请求头添加业务token
addToken(request);
@SuppressWarnings("deprecation")
StringEntity entity = new StringEntity(json, HTTP.UTF_8);
request.setEntity(entity);
String result = executeRequest(request, username, password);
if(logger.isDebugEnabled()){
logger.debug("返回报文:{}", result);
}
return result;
} catch (Exception e) {
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (request != null) {
request.releaseConnection();
}
}
}
@SuppressWarnings("unchecked")
public static Map<String,Object> toMap(String result) {
return JsonMapper.getInstance().fromJson(result, Map.class);
}
@SuppressWarnings("unchecked")
public static List<Object> toList(String result) {
return JsonMapper.getInstance().fromJson(result, List.class);
}
public static String resorvleTracing(Tracing tracing) {
String id = null;
String tracerString = tracing.tracer().toString();
if(tracerString.indexOf("gisq_request_id") !=-1) {
int beginIndex =tracerString.indexOf("gisq_request_id");
int fistcolon =tracerString.indexOf(":", beginIndex);
int firstcomma =tracerString.indexOf(",", beginIndex);
id = tracerString.substring(fistcolon+2, firstcomma-1);
}
return id;
}
/**
* 请求头中添加token
* @param request
*/
public static void addToken(HttpUriRequest request) {
try {
//主动传递业务服务的token
Subject subject = SecurityUtils.getSubject();
Session session = subject.getSession();
String userName = (String) session.getAttribute("username_");
String token = JedisUtils.get(userName + "-Token");
if(StringUtils.isNoneBlank(token)){
if(logger.isDebugEnabled()){
logger.debug("存在用户[{}]的token, 添加token请求头", userName);
}
request.addHeader("token", token);
}else{
if(logger.isDebugEnabled()){
logger.debug("缓存中没有获取到用户[{}]的token,不添加token请求头",userName);
}
}
} catch (Exception e) {
logger.error("请求头中添加业务Token失败", e.getMessage());
}
}
public static String getWithOutToken(String serviceUrl, String username, String password) {
if(logger.isDebugEnabled()){
logger.debug("请求URL:[{}]", serviceUrl);
logger.debug("username:[{}], password:[{}]", username, password);
}
HttpGet request = null;
try {
request = new HttpGet(serviceUrl);
request.addHeader(new BasicHeader(HttpHeaders.CONTENT_TYPE,
"application/json"));
request.addHeader(new BasicHeader(HttpHeaders.ACCEPT_CHARSET, "gbk"));
String result = executeRequest(request, username, password);
if(logger.isDebugEnabled()){
logger.debug("返回报文:{}", result);
}
return result;
} catch (Exception e) {
return "ERROR" + Global.REST_RESPONSE_SPLIT
+ e.getLocalizedMessage();
} finally {
if (request != null) {
request.releaseConnection();
}
}
}
}
如何通过http请求传递文件
File readFile = new File(filePath);
FileInputStream fileInputStream = = new FileInputStream(readFile);
MultipartFile multipartFile = new MockMultipartFile(readFile.getName(), readFile.getName(),ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);
rest服务如何接收请求
@RequestMapping(value = "/rolemgr/importRoleUserData/{subGuid}/{parentGroupId}", method = RequestMethod.POST)
@ResponseBody
public ResponseBodyVo<Map<String, Object>> importRoleUser(@PathVariable String subGuid,
@PathVariable String parentGroupId, HttpServletRequest request) {
InputStream stream = null;
ResponseBodyVo<Map<String, Object>> resutlMap = new ResponseBodyVo<Map<String, Object>>();
// 记录返回提示信息
Map<String, Object> resultErr = new HashMap<String, Object>();
String message = "";
String status = "";
try {
if (request instanceof MultipartHttpServletRequest == false) {
throw new PlatformIllegalArgumentException("Multipart request is required");
}
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
if (multipartRequest.getFileMap().size() == 0) {
throw new PlatformIllegalArgumentException("Multipart request with file content is required");
}
MultipartFile file = multipartRequest.getFileMap().values().iterator().next();
stream = file.getInputStream();
XSSFWorkbook workBook = null;
workBook = new XSSFWorkbook(stream);
// 获取第一个工作簿
XSSFSheet sheet = workBook.getSheetAt(0);
// Excel数据总行数
int rowCount = sheet.getPhysicalNumberOfRows();
for (int m = 1; m < rowCount; m++) {
Row row = sheet.getRow(m);
// 判断当前行中每一列是否有值,8表示行中有8列
if (getRowContent(row, 9)) {
String roleName = ImportExcel.getCellValue(row.getCell(0), true);// 角色名称
if (StringUtils.isEmpty(roleName)) {
throw new PlatformIllegalArgumentException("第" + m + "行中角色名称不能为空");
}
String roleGroup = ImportExcel.getCellValue(row.getCell(1), true); // 角色组名称
if (StringUtils.isEmpty(roleGroup)) {
throw new PlatformIllegalArgumentException("第" + m + "行中角色组名称不能为空");
}
String deptName = ImportExcel.getCellValue(row.getCell(2),true);// 部门编码
String xzqbm = ImportExcel.getCellValue(row.getCell(3),true);// 行政区编码
String parentxzqbm = ImportExcel.getCellValue(row.getCell(4),true);// 行政区编码
if (StringUtils.isEmpty(xzqbm)) {
throw new PlatformIllegalArgumentException("第" + m + "行中部门" + deptName + "行政区编码不能为空");
}
String staffName = ImportExcel.getCellValue(row.getCell(5),true);// 用户中文名称
String userName = ImportExcel.getCellValue(row.getCell(6),true);// 用户名
if (!StringUtils.isEmpty(userName) && StringUtils.isEmpty(deptName)) {
throw new PlatformIllegalArgumentException("第" + m + "行中用户所属机构不能为空");
}
String tel =ImportExcel.getCellValue(row.getCell(7),true);// 电话号码
String userCode =ImportExcel.getCellValue(row.getCell(8),true);// 用户编码
// 保存有效的角色
creteExcelUser(subGuid, parentGroupId, roleName, roleGroup, deptName, xzqbm,parentxzqbm, staffName, userName,
tel, userCode, resultErr);
}
}
message = "导入角色用户成功";
status = ResponseStatus.OK.getStatus().toString();
} catch (Exception e) {
e.printStackTrace();
message = "导入角色用户失败";
status = String.valueOf(ResponseStatus.A900.getStatus());
} finally {
try {
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
resutlMap.setData(resultErr);
resutlMap.setMsg(message);
resutlMap.setStatus(Integer.parseInt(status));
return resutlMap;
}
Excel导入工具
package com.gisquest.platform.common.utils.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import com.gisquest.platform.common.utils.Reflections;
import com.gisquest.platform.common.utils.excel.annotation.ExcelField;
import com.google.common.collect.Lists;
/**
* 导入Excel文件(支持“XLS”和“XLSX”格式)
* @author Admin
* @version 2013-03-10
*/
public class ImportExcel {
private static Logger log = LoggerFactory.getLogger(ImportExcel.class);
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 标题行号
*/
private int headerNum;
/**
* 构造函数
* @param path 导入文件,读取第一个工作表
* @param headerNum 标题行号,数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum);
}
/**
* 构造函数
* @param path 导入文件对象,读取第一个工作表
* @param headerNum 标题行号,数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum)
throws InvalidFormatException, IOException {
this(file, headerNum, 0);
}
/**
* 构造函数
* @param path 导入文件
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum, sheetIndex);
}
/**
* 构造函数
* @param path 导入文件对象
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
}
/**
* 构造函数
* @param file 导入文件对象
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
}
/**
* 构造函数
* @param path 导入文件对象
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
if (StringUtils.isBlank(fileName)){
throw new RuntimeException("导入文档为空!");
}else if(fileName.toLowerCase().endsWith("xls")){
this.wb = new HSSFWorkbook(is);
}else if(fileName.toLowerCase().endsWith("xlsx")){
this.wb = new XSSFWorkbook(is);
}else{
throw new RuntimeException("文档格式不正确!");
}
if (this.wb.getNumberOfSheets()<sheetIndex){
throw new RuntimeException("文档中没有工作表!");
}
this.sheet = this.wb.getSheetAt(sheetIndex);
this.headerNum = headerNum;
log.debug("Initialize success.");
}
/**
* 获取行对象
* @param rownum
* @return
*/
public Row getRow(int rownum){
return this.sheet.getRow(rownum);
}
/**
* 获取数据行号
* @return
*/
public int getDataRowNum(){
return headerNum+1;
}
/**
* 获取最后一个数据行号
* @return
*/
public int getLastDataRowNum(){
return this.sheet.getLastRowNum()+headerNum;
}
/**
* 获取最后一个列号
* @return
*/
public int getLastCellNum(){
return this.getRow(headerNum).getLastCellNum();
}
/**
* 获取单元格值
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public static Object getCellValue(Row row, int column){
Object val = "";
try{
Cell cell = row.getCell(column);
if (cell != null){
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
val = cell.getNumericCellValue();
}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
val = cell.getStringCellValue();
}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
val = cell.getCellFormula();
}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
val = cell.getBooleanCellValue();
}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
val = cell.getErrorCellValue();
}
}
}catch (Exception e) {
return val;
}
return val;
}
public static String getCellValue(Cell cell, boolean treatAsStr) {
if (cell == null) {
return "";
}
String cellValue = "";
if (treatAsStr && cell.getCellType() != Cell.CELL_TYPE_STRING) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
cellValue = String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
DecimalFormat df = new DecimalFormat("0");
cellValue = String.valueOf(df.format(cell.getNumericCellValue()));
} else {
cellValue = String.valueOf(cell.getStringCellValue());
}
if (cellValue == null)
return "";
else
return cellValue.trim();
}
/**
* 获取导入数据列表
* @param cls 导入对象类型
* @param groups 导入分组
*/
public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
List<Object[]> annotationList = Lists.newArrayList();
// Get annotation field
Field[] fs = cls.getDeclaredFields();
for (Field f : fs){
ExcelField ef = f.getAnnotation(ExcelField.class);
if (ef != null && (ef.type()==0 || ef.type()==2)){
if (groups!=null && groups.length>0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ef.groups()){
if (g == efg){
inGroup = true;
annotationList.add(new Object[]{ef, f});
break;
}
}
}
}else{
annotationList.add(new Object[]{ef, f});
}
}
}
// Get annotation method
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms){
ExcelField ef = m.getAnnotation(ExcelField.class);
if (ef != null && (ef.type()==0 || ef.type()==2)){
if (groups!=null && groups.length>0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ef.groups()){
if (g == efg){
inGroup = true;
annotationList.add(new Object[]{ef, m});
break;
}
}
}
}else{
annotationList.add(new Object[]{ef, m});
}
}
}
// Field sorting
Collections.sort(annotationList, new Comparator<Object[]>() {
public int compare(Object[] o1, Object[] o2) {
return new Integer(((ExcelField)o1[0]).sort()).compareTo(
new Integer(((ExcelField)o2[0]).sort()));
};
});
//log.debug("Import column count:"+annotationList.size());
// Get excel data
List<E> dataList = Lists.newArrayList();
for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
E e = (E)cls.newInstance();
int column = 0;
Row row = this.getRow(i);
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList){
Object val = this.getCellValue(row, column++);
if (val != null){
ExcelField ef = (ExcelField)os[0];
// If is dict type, get dict value
if (StringUtils.isNotBlank(ef.dictType())){
//val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");
//log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);
}
// Get param type and type cast
Class<?> valType = Class.class;
if (os[1] instanceof Field){
valType = ((Field)os[1]).getType();
}else if (os[1] instanceof Method){
Method method = ((Method)os[1]);
if ("get".equals(method.getName().substring(0, 3))){
valType = method.getReturnType();
}else if("set".equals(method.getName().substring(0, 3))){
valType = ((Method)os[1]).getParameterTypes()[0];
}
}
//log.debug("Import value type: ["+i+","+column+"] " + valType);
try {
if (valType == String.class){
String s = String.valueOf(val.toString());
if(StringUtils.endsWith(s, ".0")){
val = StringUtils.substringBefore(s, ".0");
}else{
val = String.valueOf(val.toString());
}
}else if (valType == Integer.class){
val = Double.valueOf(val.toString()).intValue();
}else if (valType == Long.class){
val = Double.valueOf(val.toString()).longValue();
}else if (valType == Double.class){
val = Double.valueOf(val.toString());
}else if (valType == Float.class){
val = Float.valueOf(val.toString());
}else if (valType == Date.class){
val = DateUtil.getJavaDate((Double)val);
}else{
if (ef.fieldType() != Class.class){
val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
}else{
val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
"fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());
}
}
} catch (Exception ex) {
log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
val = null;
}
// set entity value
if (os[1] instanceof Field){
Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);
}else if (os[1] instanceof Method){
String mthodName = ((Method)os[1]).getName();
if ("get".equals(mthodName.substring(0, 3))){
mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
}
Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});
}
}
sb.append(val+", ");
}
dataList.add(e);
log.debug("Read success: ["+i+"] "+sb.toString());
}
return dataList;
}
}
以上只是其中的一种导入excel文件的一种方法:用户webuploder框架实现的。
还可以用<input>标签
<input type="file" id="selectFiles" οnchange="dealSelectFiles()" multiple webkitdirectory>
完全不用先上传到服务器,然后再去服务器读取上传的文件,直接用MultipartFile 接收
@ResponseBody
@RequestMapping(value = "/uploadFiles", method = RequestMethod.POST)
public void processUploadedFiles( @RequestPart("file") MultipartFile file,
HttpServletRequest request) {
//这里可以直接读取文件,将excel数据保存到数据库
}