注意事项:上传文件大小限制(不设置的话,文件超过1M会报错):
spring:
#数据源
datasource:
url: jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
servlet:
multipart:
# 最大文件大小。值可以使用后缀“MB”或“KB”。指示兆字节或千字节大小。
max-file-size: 20MB
# # 最大请求大小可以是mb也可以是kb
max-request-size: 200MB
1.上传excel文件,保存在硬盘中:
/**
* 实现文件上传
* */
@RequestMapping("fileUpload")
@ResponseBody
public String fileUpload(@RequestParam("fileName") MultipartFile file){
if(file.isEmpty()){
return "false";
}
String fileName = file.getOriginalFilename();
int size = (int) file.getSize();
System.out.println(fileName + "-->" + size);
String path = "F:/test" ;
File dest = new File(path + "/" + fileName);
if(!dest.getParentFile().exists()){ //判断文件父目录是否存在
dest.getParentFile().mkdir();
}
try {
file.transferTo(dest); //保存文件
return "true";
} catch (IllegalStateException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return "false";
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return "false";
}
}
2.上传excel文件。保存在硬盘中,并且保证该目录只有一份相同名字的文件。
文件工具类
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
/**
* @description: 文件上传下载
* @author: Administrator
* @date: 2019-05-31 11:15
*/
public class FileAdminUtils {
public static Log log = LogFactory.getLog(ExcelUtil.class);
private static String filePath="G:/test";
public static String multifileUpload(MultipartFile file,String newFileName){
if(file.isEmpty()){
return "false";
}
// String fileName = file.getOriginalFilename();
int size = (int) file.getSize();
log.info("文件名字=="+newFileName+"==文件大小=="+size);
// log.info("文件名字=="fileName + "-->" + size);
String path = filePath;
File dest = new File(path + "/" + newFileName);
if(!dest.getParentFile().exists()){ //判断文件父目录是否存在
dest.getParentFile().mkdir();
}
try {
//保存前先删除重复文件
String del_result=delFile(newFileName);
log.info("删除结果=="+del_result);
file.transferTo(dest); //保存文件
return "true";
} catch (IllegalStateException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return "false";
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return "false";
}
}
// 删除文件
public static String delFile(String fileName) {
String resultInfo=null;
String sb=filePath+"/"+fileName;
File file = new File(sb);
if (file.exists()) {
if (file.delete()) {
resultInfo = "1-删除成功";
} else {
resultInfo = "0-删除失败";
}
} else {
resultInfo = "文件不存在!";
}
return resultInfo;
}
//文件下载
public static String downLoad(HttpServletResponse response,String filename){
File file = new File(filePath + "/" + filename);
if(file.exists()){ //判断文件父目录是否存在
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
// response.setContentType("application/force-download");
try {
response.setHeader("Content-Disposition", "attachment;fileName=" + java.net.URLEncoder.encode(filename,"UTF-8"));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
byte[] buffer = new byte[1024];
FileInputStream fis = null; //文件输入流
BufferedInputStream bis = null;
OutputStream os = null; //输出流
try {
os = response.getOutputStream();
fis = new FileInputStream(file);
bis = new BufferedInputStream(fis);
int i = bis.read(buffer);
while(i != -1){
os.write(buffer);
i = bis.read(buffer);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("----------file download---" + filename);
try {
bis.close();
fis.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
}
上传测试:
//上传excel表
@PostMapping("/upload")
public String upload(MultipartFile file) {
String newfileName="test.xls";//保存的文件名字
String result= FileAdminUtils.multifileUpload(file,newfileName);
System.out.println("保存文件结果=="+result);
return result;
}
3.下载excel文件:
@RequestMapping("/down")
public String downLoad(HttpServletResponse response) throws UnsupportedEncodingException {
return FileAdminUtils.downLoad(response,"test.xls");
}
web调用下载接口:
window.open("http://xxxx/down?");
4.查询表中的数据,返回给前端excel:
@RequestMapping("/test")
public void downAll(HttpServletResponse response,String id) throws IOException {
List<List<String>> excelData = new ArrayList<>();
List<String> head = new ArrayList<>();
head.add("ID");
head.add("部门");
head.add("名字");
excelData.add(head);
//查询表中的数据
List<MyData> myDatas=myService.list();
log.info("==有"+myDatas.size()+"条数据");
if (myDatas.size()>0){
for (int i=0;i<myDatas.size();i++){
MyData myData=myDatas.get(i);
List<String> data = new ArrayList<>();
data.add(myData.getId());
data.add(myData.getDp());
data.add(myData.getName());
excelData.add(data);
}
}
String sheetName = "sheet表";
String fileName = "test.xls";
// log.info("导出全部excel表");
ExcelOutUtils.exportExcel(response, excelData, sheetName, fileName, 15);
log.info("导出全部excel表成功");
}
excel工具类:
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
/**
* @description:
* @author: Administrator
* @date: 2019-05-29 14:31
*/
public class ExcelOutUtils {
public static Log log = LogFactory.getLog(ExcelOutUtils.class);
/**
* Excel表格导出
* @param response HttpServletResponse对象
* @param excelData Excel表格的数据,封装为List<List<String>>
* @param sheetName sheet的名字
* @param fileName 导出Excel的文件名
* @param columnWidth Excel表格的宽度,建议为15
* @throws IOException 抛IO异常
*/
public static void exportExcel(HttpServletResponse response,
List<List<String>> excelData,
String sheetName,
String fileName,
int columnWidth) throws IOException {
//声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//生成一个表格,设置表格名称
HSSFSheet sheet = workbook.createSheet(sheetName);
//设置表格列宽度
sheet.setDefaultColumnWidth(columnWidth);
//写入List<List<String>>中的数据
int rowIndex = 0;
for(List<String> data : excelData){
//创建一个row行,然后自增1
HSSFRow row = sheet.createRow(rowIndex++);
//遍历添加本行数据
for (int i = 0; i < data.size(); i++) {
//创建一个单元格
HSSFCell cell = row.createCell(i);
//创建一个内容对象
HSSFRichTextString text = new HSSFRichTextString(data.get(i));
//将内容对象的文字内容写入到单元格中
cell.setCellValue(text);
}
}
//准备将Excel的输出流通过response输出到页面下载
//八进制输出流
response.setContentType("application/octet-stream");
// 告诉浏览器用什么软件可以打开此文件
response.setHeader("content-Type","application/vnd.ms-excel");
//设置导出Excel的名称
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//刷新缓冲
response.flushBuffer();
log.info("workbook将Excel写入到response的输出流中,供页面下载该Excel文件");
//workbook将Excel写入到response的输出流中,供页面下载该Excel文件
workbook.write(response.getOutputStream());
log.info("关闭workbook");
//关闭workbook
workbook.close();
}
}
web调用:
window.open("http://xxxx/down?id=1");
另一种下载方式:不知道为啥有时候不行,报Resource interpreted as Document but transferred with MIME type application/json ...:
var $eleForm = $("<form method='get'></form>");
$eleForm.attr("action",down_all_url);
//$eleForm.append("<input name='id' type='hidden' value='"+1+"'>");
//$(document.body).append($eleForm);
//提交表单,实现下载
$eleForm.submit();
console.log("提交表单,实现下载==");
$eleForm.remove();
另外一种导出excel方法(我觉得比较好):https://blog.csdn.net/l1028386804/article/details/79659605