Excel的导入V2优化版
有些时候文件上传这一步骤由前端来处理,只将上传后的 URL 传输给后端(可以参考上一文中的图片上传功能),也就是导入请求中并不会直接处理 MultipartFile 对象,而是通过文件 URL 将其下载,之后进行文件流处理,具体过程如下:
点击 “ 导入V2 ” 按钮,与 V1 按钮不同,此时出现的不是文件选择框,而是一个上传文件的弹框,点击弹框里的 “ 上传 Excel 文件 ” 按钮才会出现文件选择框,选择正确的文件后点击确定,请求完成后同 V1 版本的导入功能效果一样。
前端实现
HTML页面
<!-- Main content -->
<div class="content">
<div class="row">
<div class="col-12">
<div class="card">
<div class="card-body">
<div class="grid-btn">
<button class="btn btn-info" onclick="userAdd()">
<i class="fa fa-plus"></i> 新增
</button>
<button class="btn btn-success" onclick="userEdit()">
<i class="fa fa-plus"></i> 编辑
</button>
<button class="btn btn-danger" onclick="deleteUser()">
<i class="fa fa-remove"></i> 删除
</button>
<button class="btn btn-default" id="importV1Button">
<i class="fa fa-upload"></i> 导入ExcelV1
</button>
<button class="btn btn-default" onclick="importV2()"><i
class="fa fa-upload"></i> 导入ExcelV2
</button>
<button class="btn btn-primary" onclick="window.location.href='/users/export'">
<i class="fa fa-download"></i> 导出
</button>
</div>
<table id="jqGrid" class="table table-bordered">
</table>
<div id="jqGridPager"></div>
</div>
</div>
</div>
</div>
</div>
</div>
HTML模态框(这里用的是Custombox)
<!--导入Excel模态框-->
<div class="content">
<div class="modal" id="importV2Modal" tabindex="-1" role="dialog" aria-labelledby="importV2ModalLabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h6 class="modal-title" id="importV2ModalLabel">用户导入</h6>
</div>
<div class="modal-body">
<div class="form-group">
<input type="hidden" id="fileUrl" value="">
<div class="col-sm-10">
<a class="btn btn-default" id="uploadExcelV2">
<i class="fa fa-file">
上传Excel文件
</i>
</a>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" id="cancelImportV2">取消</button>
<button type="button" class="btn btn-primary" id="importV2Button">确认</button>
</div>
</div>
</div>
</div>
</div>
导入的JS
// 用户导入V2
function importV2() {
var modal = new Custombox.modal({
content: {
effect: 'fadein',
target: '#importV2Modal'
}
});
modal.open();
}
new AjaxUpload('#uploadExcelV2', {
action: 'upload/file',
name: 'file',
autoSubmit: 'true',
responseType: 'json',
onSubmit: function (file, extension) {
if (!(extension && /^(xlsx)$/.test(extension.toLowerCase()))) {
alert('只支持xlsx格式的文件!', {
icon: "error",
});
return false;
}
},
onComplete: function (file, r) {
if (r.resultCode == 200) {
$("#fileUrl").val(r.data);
$("#uploadExcelV2").attr('class','btn-info');
alert("上传Excel成功,请点击确认添加数据");
return false;
} else {
alert(r.message);
}
}
});
});
这里还是通过上传获取文件的url再通过流处理转换成File类型并把对应的Excel下载到服务器,其他的和V1版本是一样的
当点击确定导入的时候,如果之前上传没错,再访问一次后台
$("#importV2Button").click(function () {
var fileUrl = $("#fileUrl").val();
$.ajax({
type: 'post',
dataType: 'json',
url: 'users/importV2?fileUrl=' + fileUrl,
contentType:'application/json',
success:function (result) {
if (result.resultCode==200){
closeModal();
reload();
alert("成功导入"+result.data+"条记录!");
}else {
closeModal();
alert(result.message);
};
},
error:function () {
reset();
alert("操作失败!");
}
});
});
后端逻辑
控制层
package com.ssm.demo.controller;
import com.ssm.demo.common.Result;
import com.ssm.demo.common.ResultGenerator;
import com.ssm.demo.controller.enums.UploadFileTypeEnum;
import com.ssm.demo.utils.FileUtil;
import org.apache.commons.io.FileUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;
import static com.ssm.demo.common.Constants.FILE_PRE_URL;
/**
* Created by 13 on 2017/7/17.
*/
@Controller
@RequestMapping("/upload")
public class UploadFileController {
/**
* 通用 文件上传接口(可以上传图片、视频、excel等文件,具体格式可在UploadFileTypeEnum中进行配置)
*
* @return
*/
@RequestMapping(value = "/file", method = RequestMethod.POST)
@ResponseBody
public Result uploadFile(HttpServletRequest request, @RequestParam("file") MultipartFile file) {
ServletContext sc = request.getSession().getServletContext();
String type = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1, file.getOriginalFilename().length());
String fileName = null;
UploadFileTypeEnum uploadFileTypeEnum = UploadFileTypeEnum.getFileEnumByType(type);
if (uploadFileTypeEnum == UploadFileTypeEnum.ERROR_TYPE) {
//格式错误则不允许上传,直接返回错误提示
return ResultGenerator.genFailResult("请检查文件格式!");
} else {
//生成文件名称通用方法
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd_HHmmss");
Random r = new Random();
StringBuilder tempName = new StringBuilder();
tempName.append(sdf.format(new Date())).append(r.nextInt(100)).append(".").append(type);
fileName = tempName.toString();
}
try {
String dir = sc.getRealPath("/upload");
FileUtils.writeByteArrayToFile(new File(dir, fileName), file.getBytes());
} catch (IOException e) {
//文件上传异常
return ResultGenerator.genFailResult("文件上传失败!");
}
Result result = ResultGenerator.genSuccessResult();
//返回文件的全路径
StringBuilder fileUrl = new StringBuilder();
fileUrl.append(FILE_PRE_URL).append("/upload/").append(fileName);
result.setData(fileUrl.toString());
return result;
}
/**
* @param chunks 当前所传文件的分片总数
* @param chunk 当前所传文件的当前分片数
* @return
* @Description: 大文件上传前分片检查
* @author: 13
*/
@ResponseBody
@RequestMapping(value = "/checkChunk")
public Result checkChunk(HttpServletRequest request, String guid, Integer chunks, Integer chunk, String fileName) {
try {
String uploadDir = FileUtil.getRealPath(request);
String ext = fileName.substring(fileName.lastIndexOf("."));
// 判断文件是否分块
if (chunks != null && chunk != null) {
//文件路径
StringBuilder tempFileName = new StringBuilder();
tempFileName.append(uploadDir).append(File.separator).append("temp").append(File.separator).append(guid).append(File.separator).append(chunk).append(ext);
File tempFile = new File(tempFileName.toString());
//是否已存在分片,如果已存在分片则返回SUCCESS结果
if (tempFile.exists()) {
return ResultGenerator.genSuccessResult("分片已经存在!跳过此分片!");
}
}
} catch (Exception ex) {
ex.printStackTrace();
return ResultGenerator.genFailResult("error");
}
return ResultGenerator.genNullResult("不存在分片");
}
/**
* @param chunks 当前所传文件的分片总数
* @param chunk 当前所传文件的当前分片数
* @return
* @Description: 大文件分片上传
* @author: 13
*/
@ResponseBody
@RequestMapping(value = "/files")
public Result upload(HttpServletRequest request, String guid, Integer chunks, Integer chunk, String name, MultipartFile file) {
String filePath = null;
//上传存储路径
String uploadDir = FileUtil.getRealPath(request);
//后缀名
String ext = name.substring(name.lastIndexOf("."));
StringBuilder tempFileName = new StringBuilder();
//等价于 uploadDir + "\\temp\\" + guid + "\\" + chunk + ext
tempFileName.append(uploadDir).append(File.separator).append("temp").append(File.separator).append(guid).append(File.separator).append(chunk).append(ext);
File tempFile = new File(tempFileName.toString());
// 判断文件是否分块
if (chunks != null && chunk != null) {
//根据guid 创建一个临时的文件夹
if (!tempFile.exists()) {
tempFile.mkdirs();
}
try {
//保存每一个分片
file.transferTo(tempFile);
} catch (Exception e) {
e.printStackTrace();
}
//如果当前是最后一个分片,则合并所有文件
if (chunk == (chunks - 1)) {
StringBuilder tempFileFolder = new StringBuilder();
//等价于 uploadDir + "\\temp\\" + guid + File.separator
tempFileFolder.append(uploadDir).append(File.separator).append("temp").append(File.separator).append(guid).append(File.separator);
String newFileName = FileUtil.mergeFile(chunks, ext, tempFileFolder.toString(), request);
filePath = "upload/chunked/" + newFileName;
}
} else {
//不用分片的文件存储到files文件夹中
StringBuilder destPath = new StringBuilder();
destPath.append(uploadDir).append(File.separator).append("files").append(File.separator);
String newName = System.currentTimeMillis() + ext;// 文件新名称
try {
FileUtil.saveFile(destPath.toString(), newName, file);
} catch (IOException e) {
e.printStackTrace();
}
filePath = "upload/files/" + newName;
}
Result result = ResultGenerator.genSuccessResult();
result.setData(filePath);
return result;
}
}
FileUtil工具类
package com.ssm.demo.utils;
import org.apache.commons.io.FileUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.net.URL;
import java.net.URLConnection;
import java.util.UUID;
public class FileUtil {
/**
* 转换MultipartFile对象为java.io.File类型
*
* @param multipartFile
* @return
*/
public static File convertMultipartFileToFile(MultipartFile multipartFile) {
File result = null;
try {
/**
* UUID.randomUUID().toString()是javaJDK提供的一个自动生成主键的方法。
* UUID(Universally Unique Identifier)全局唯一标识符,是指在一台机器上生成的数字,
* 它保证对在同一时空中的所有机器都是唯一的,是由一个十六位的数字组成,表现出来的形式。
* 由以下几部分的组合:当前日期和时间(UUID的第一个部分与时间有关,如果你在生成一个UUID之后,
* 过几秒又生成一个UUID,则第一个部分不同,其余相同),时钟序列,
* 全局唯一的IEEE机器识别号(如果有网卡,从网卡获得,没有网卡以其他方式获得),
* UUID的唯一缺陷在于生成的结果串会比较长。
*
*
* File.createTempFile和File.createNewFile()的区别:
* 后者只是创建文件,而前者可以给文件名加前缀和后缀
*/
//这里对生成的文件名加了UUID随机生成的前缀,后缀是null
result = File.createTempFile(UUID.randomUUID().toString(), null);
multipartFile.transferTo(result);
result.deleteOnExit();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 根据url获取文件对象
*
* @param fileUrl
* @return
*/
public static File downloadFile(String fileUrl) {
File result = null;
try {
result = File.createTempFile(UUID.randomUUID().toString(), null);
URL url = new URL(fileUrl);
URLConnection connection = url.openConnection();
connection.setConnectTimeout(3000);
BufferedInputStream bis = new BufferedInputStream(connection.getInputStream());
BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(result));
byte[] car = new byte[1024];
int l = 0;
while ((l = bis.read(car)) != -1) {
bos.write(car, 0, l);
}
bis.close();
bos.close();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* @param request
* @return
*/
public static String getRealPath(HttpServletRequest request) {
ServletContext sc = request.getSession().getServletContext();
String uploadDir = sc.getRealPath("/upload");
return uploadDir;
}
public static boolean saveFile(String savePath, String fileFullName, MultipartFile file) throws IOException {
File uploadFile = new File(savePath + fileFullName);
FileUtils.writeByteArrayToFile(new File(savePath, fileFullName), file.getBytes());
return uploadFile.exists();
}
public static String mergeFile(int chunksNumber, String ext, String uploadFolderPath,
HttpServletRequest request) {
//合并分片流
String mergePath = uploadFolderPath;
String destPath = getRealPath(request);// 文件路径
String newName = System.currentTimeMillis() + ext;// 文件新名称
SequenceInputStream s;
InputStream s1;
try {
s1 = new FileInputStream(mergePath + 0 + ext);
String tempFilePath;
InputStream s2 = new FileInputStream(mergePath + 1 + ext);
s = new SequenceInputStream(s1, s2);
for (int i = 2; i < chunksNumber; i++) {
tempFilePath = mergePath + i + ext;
InputStream s3 = new FileInputStream(tempFilePath);
s = new SequenceInputStream(s, s3);
}
//分片文件存储到/upload/chunked目录下
StringBuilder filePath = new StringBuilder();
filePath.append(destPath).append(File.separator).append("chunked").append(File.separator);
saveStreamToFile(s, filePath.toString(), newName);
// 删除保存分块文件的文件夹
deleteFolder(mergePath);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return newName;
}
private static boolean deleteFolder(String mergePath) {
File dir = new File(mergePath);
File[] files = dir.listFiles();
if (files != null) {
for (File file : files) {
try {
file.delete();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return dir.delete();
}
private static void saveStreamToFile(SequenceInputStream inputStream, String filePath, String newName)
throws Exception {
File fileDirectory = new File(filePath);
synchronized (fileDirectory) {
if (!fileDirectory.exists()) {
if (!fileDirectory.mkdir()) {
throw new Exception("文件夹创建失败,路径为:" + fileDirectory);
}
}
if (!fileDirectory.exists()) {
if (!fileDirectory.mkdir()) {
throw new Exception("文件夹创建失败,路径为:" + fileDirectory);
}
}
}
OutputStream outputStream = new FileOutputStream(filePath + newName);
byte[] buffer = new byte[1024];
int len = 0;
try {
while ((len = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, len);
outputStream.flush();
}
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
outputStream.close();
inputStream.close();
}
}
}
其他的和V1版本是一样的
mapper
<update id="deleteUser">
update tb_admin_user set is_deleted = 1
where id in
<foreach collection="array" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</update>
效果展示
Excel的导出实现
前端实现
<button class="btn btn-primary"
onclick="window.location.href='/users/export'">
<i class="fa fa-download"></i> 导出
</button>
点击的时候会直接访问到Controller层来获取下载
后端逻辑
/**
* 导出功能
*/
@RequestMapping(value = "/export", method = RequestMethod.GET)
public void exportUsers(HttpServletRequest request, HttpServletResponse response) {
List<AdminUser> userList = adminUserService.getUsersForExport();
//单元格表头
String[] excelHeader = {"用户id", "用户名", "账号状态", "添加时间"};
//字段名称
String[] fileds = {"userId", "userName", "status", "createTime"};
//单元格宽度内容格式
int[] formats = {4, 2, 1, 1};
//单元格宽度
int[] widths = {256 * 14, 512 * 14, 256 * 14, 512 * 14};
try {
List<Map<String, Object>> excelData = new ArrayList<Map<String, Object>>();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if (CollectionUtils.isNotEmpty(userList)) {
for (AdminUser user : userList) {
Map<String, Object> map = new HashMap<>();
map.put("userId", user.getId());
map.put("userName", user.getUserName());
map.put("status", user.getIsDeleted() == 0 ? "正常账号" : "废弃账号");
map.put("createTime", formatter.format(user.getCreateTime()));
excelData.add(map);
}
}
String excelName = "用户数据_" + System.currentTimeMillis();
PoiUtil.exportFile(excelName, excelHeader, fileds, formats, widths, excelData, request, response);
} catch (Exception e) {
e.printStackTrace();
}
}
注:这里的一些工具类在上一篇写过了