一、依赖
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
二、实现
(1)单文件上传下载
上传:注意前端传递参数名称和后端一致
@RequestMapping(value = "/upload")
@ResponseBody
public String upload(@RequestParam("test") MultipartFile file) {
if (file.isEmpty()) {
return "文件为空";
}
// 获取文件名
String fileName = file.getOriginalFilename();
logger.info("上传的文件名为:" + fileName);
// 获取文件的后缀名
String suffixName = fileName.substring(fileName.lastIndexOf("."));
logger.info("上传的后缀名为:" + suffixName);
// 文件上传后的路径
String filePath = "D://test//";
// 解决中文问题,liunx下中文路径,图片显示问题
// fileName = UUID.randomUUID() + suffixName;
File dest = new File(filePath + fileName);
// 检测是否存在目录
if (!dest.getParentFile().exists()) {
dest.getParentFile().mkdirs();
}
try {
file.transferTo(dest);
return "上传成功";
} catch (IllegalStateException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return "上传失败";
}
下载:
// 文件下载
@RequestMapping("/download")
public String downloadFile(HttpServletRequest request, HttpServletResponse response) {
String fileName = "233EB630-7DF7-422a-A3B5-E17D68676AC7.png";
if (fileName != null) {
// 当前是从该工程的WEB-INF//File//下获取文件(该目录可以在下面一行代码配置)然后下载到C:\\users\\downloads即本机的默认下载的目录
// String realPath =
// request.getServletContext().getRealPath("//WEB-INF//");
String realPath = "D://test//";
File file = new File(realPath, fileName);
if (file.exists()) {
response.setContentType("application/force-download");// 设置强制下载不打开
response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);// 设置文件名
byte[] buffer = new byte[1024];
FileInputStream fis = null;
BufferedInputStream bis = null;
try {
fis = new FileInputStream(file);
bis = new BufferedInputStream(fis);
OutputStream os = response.getOutputStream();
int i = bis.read(buffer);
while (i != -1) {
os.write(buffer, 0, i);
i = bis.read(buffer);
}
System.out.println("success");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
return null;
}
(2)多文件上传
@RequestMapping(value = "/batch/upload", method = RequestMethod.POST)
@ResponseBody
public String handleFileUpload(HttpServletRequest request,MultipartFile[] file) {
//List<MultipartFile> files = ((MultipartHttpServletRequest) request).getFiles("file");
MultipartFile[] files = file;
MultipartFile filet = null;
BufferedOutputStream stream = null;
String filePath = "D://test//";
for (int i = 0; i < files.length; ++i) {
filet = files[i];
if (!filet.isEmpty()) {
try {
byte[] bytes = filet.getBytes();
String extName = filet.getOriginalFilename().substring(filet.getOriginalFilename().lastIndexOf("."));
stream = new BufferedOutputStream(
new FileOutputStream(new File(filePath + System.currentTimeMillis() + extName)));
stream.write(bytes);
stream.close();
} catch (Exception e) {
stream = null;
return "You failed to upload " + i + " => " + e.getMessage();
}
} else {
return "You failed to upload " + i + " because the file was empty.";
}
}
return "upload successful";
}
(3)上传解析excel
a、控制层
@PostMapping(value = "/batchImport")
@ResponseBody//这个注释加不加都可以实现,加的话跳转重定向到固定页面,不加就是ajax返回
public String batchImportUserKnowledge(@RequestParam(value="filename") MultipartFile file,
HttpServletRequest request,HttpServletResponse response,HttpSession session
// ,@SessionAttribute(Constants.ACCOUNT_SESSION_KEY) Account account
) throws IOException{
//判断文件是否为空
if(file==null){
session.setAttribute("msg","文件不能为空!");
return "redirect:toUserKnowledgeImport";
}
//获取文件名
String fileName=file.getOriginalFilename();
//验证文件名是否合格
if(!ExcelImportUtils.validateExcel(fileName)){
session.setAttribute("msg","文件必须是excel格式!");
return "redirect:toUserKnowledgeImport";
}
//进一步判断文件内容是否为空(即判断其大小是否为0或其名称是否为null)
long size=file.getSize();
if(StringUtils.isEmpty(fileName) || size==0){
session.setAttribute("msg","文件不能为空!");
return "redirect:toUserKnowledgeImport";
}
//批量导入
String message = batchImportService.batchImport(fileName,file,"admin");
session.setAttribute("msg",message);
return "redirect:toUserKnowledgeImport";
}
b、服务层
@Component
public class BatchImportServiceImpl implements BatchImportService {
@Autowired
private DimEquipmentTypeMapper mapper;
@Override
public String batchImport(String fileName, MultipartFile mfile, String userName) {
File uploadDir = new File("d:\\test");
//创建一个目录 (它的路径名由当前 File 对象指定,包括任一必须的父路径。)
if (!uploadDir.exists()) uploadDir.mkdirs();
//新建一个文件
File tempFile = new File("d:\\test\\" + new Date().getTime() + ".xlsx");
//初始化输入流
InputStream is = null;
try{
//将上传的文件写入新建的文件中
mfile.transferTo(tempFile);
//根据新建的文件实例化输入流
is = new FileInputStream(tempFile);
//根据版本选择创建Workbook的方式
Workbook wb = null;
//根据文件名判断文件是2003版本还是2007版本
if(ExcelImportUtils.isExcel2007(fileName)){
wb = new XSSFWorkbook(is);
}else{
wb = new HSSFWorkbook(is);
}
//根据excel里面的内容读取知识库信息
return readExcelValue(wb,userName,tempFile);
}catch(Exception e){
e.printStackTrace();
} finally{
if(is !=null)
{
try{
is.close();
}catch(IOException e){
is = null;
e.printStackTrace();
}
}
}
return "导入出错!请检查数据格式!";
}
/**
* 解析Excel里面的数据
* @param wb
* @return
*/
private String readExcelValue(Workbook wb,String userName,File tempFile){
//错误信息接收器
String errorMsg = "";
//得到第一个shell
Sheet sheet=wb.getSheetAt(0);
//得到Excel的行数
int totalRows=sheet.getPhysicalNumberOfRows();
//总列数
int totalCells = 0;
//得到Excel的列数(前提是有行数),从第二行算起
if(totalRows>=2 && sheet.getRow(1) != null){
totalCells=sheet.getRow(1).getPhysicalNumberOfCells();
}
List<DimEquipmentType> userKnowledgeBaseList=new ArrayList<DimEquipmentType>();
DimEquipmentType tempUserKB;
String br = "<br/>";
//循环Excel行数,从第二行开始。标题不入库
for(int r=1;r<totalRows;r++){
String rowMessage = "";
Row row = sheet.getRow(r);
if (row == null){
errorMsg += br+"第"+(r+1)+"行数据有问题,请仔细检查!";
continue;
}
tempUserKB = new DimEquipmentType();
String question = "";
String answer = "";
//循环Excel的列
for(int c = 0; c <totalCells; c++){
Cell cell = row.getCell(c);
if (null != cell){
if(c==0){
question = cell.getStringCellValue();
if(StringUtils.isEmpty(question)){
rowMessage += "问题不能为空;";
}else if(question.length()>60){
rowMessage += "问题的字数不能超过60;";
}
tempUserKB.setName(question);
}else if(c==1){
answer = cell.getStringCellValue();
if(StringUtils.isEmpty(answer)){
rowMessage += "答案不能为空;";
}else if(answer.length()>1000){
rowMessage += "答案的字数不能超过1000;";
}
tempUserKB.setPointName(answer);
}
}else{
rowMessage += "第"+(c+1)+"列数据有问题,请仔细检查;";
}
}
//拼接每行的错误提示
if(!StringUtils.isEmpty(rowMessage)){
errorMsg += br+"第"+(r+1)+"行,"+rowMessage;
}else{
userKnowledgeBaseList.add(tempUserKB);
}
}
//删除上传的临时文件
if(tempFile.exists()){
tempFile.delete();
}
//全部验证通过才导入到数据库
if(StringUtils.isEmpty(errorMsg)){
for(DimEquipmentType userKnowledgeBase : userKnowledgeBaseList){
mapper.insertSelective(userKnowledgeBase);
}
errorMsg = "导入成功,共"+userKnowledgeBaseList.size()+"条数据!";
}
return errorMsg;
}
}
c、工具类
public class ExcelImportUtils {
// @描述:是否是2003的excel,返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
// @描述:是否是2007的excel,返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
/**
* 验证EXCEL文件
*
* @param filePath
* @return
*/
public static boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
return false;
}
return true;
}
}