springboot上传下载删除excel文件
在本地准备好excel文件
pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- swagger -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.swagger</groupId>
<artifactId>swagger-models</artifactId>
<version>1.5.21</version>
</dependency>
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>swagger-bootstrap-ui</artifactId>
<version>1.9.3</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.6.1</version>
</dependency>
<!--java poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>pom.xml中引入hutool工具，hutool中包含非常多的工具类，一会我会专门写一篇关于hutool的文章。用了这个工具就可以省去很多的util类
#application.properties配置文件
server.port=8005
#server.servlet.context-path=/
spring.datasource.url=jdbc:mysql://localhost:3306/excel
spring.datasource.username=root
spring.datasource.password=zhangxian11
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
mybatis.configuration.map-underscore-to-camel-case=true
#上传文件的存放地址
file-save-path=/Users/zhangxianzeng/Downloads/ui/excel/
spring.servlet.multipart.max-file-size=800MB
spring.servlet.multipart.max-request-size=800MB实体类
public class Excels {
private int id;
private String num;
private String names;
private String links;
private String passwords;
private String filename;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getNum() {
return num;
}
public void setNum(String num) {
this.num = num;
}
public String getNames() {
return names;
}
public void setNames(String names) {
this.names = names;
}
public String getLinks() {
return links;
}
public void setLinks(String links) {
this.links = links;
}
public String getPasswords() {
return passwords;
}
public void setPasswords(String passwords) {
this.passwords = passwords;
}
public String getFilename() {
return filename;
}
public void setFilename(String filename) {
this.filename = filename;
}
@Override
public String toString() {
return "Excels{" +
"id=" + id +
", num='" + num + '\'' +
", names='" + names + '\'' +
", links='" + links + '\'' +
", passwords='" + passwords + '\'' +
", filename='" + filename + '\'' +
'}';
}
}Mapper类
@Mapper
public interface ExcelMapper {
@Insert("INSERT INTO excelmysql (num,names,links,passwords,filename) VALUES (#{num},#{names},#{links},#{passwords},#{filename})")
int insert(Excels excels);
@Select("select * from excelmysql")
List<Excels> select();
@Select("select num,names,links,passwords from excelmysql where filename=#{filename}")
List<Excels> select1(String filename);
@Select("select distinct filename from excelmysql ")
List<Excels> select2();
}这里直接用注解的形式写了sql语句，大家也可以用xml的形式写
service类
@Service
public class ExcelsService {
@Autowired
private ExcelMapper excelMapper;
//导入excel到数据库并且存入到服务器某个地址一份
public int insert(Excels excels) {
return excelMapper.insert(excels);
}
//查询数据库中所以数据
public List<Excels> select() {
return excelMapper.select();
}
//查询某个文件到全部数据
public List<Excels> select1(String filename) {
return excelMapper.select1(filename);
}
//查询数据库中到所有文件到名称
public List<Excels> select2() {
return excelMapper.select2();
}
}Controller类
@Api(value = "/",description = "这是所有的接口文档")
@RestController
@RequestMapping("/act")
public class ExcelController {
@Value("${file-save-path}")
private String filePath;
@Autowired
private ExcelsService excelsService;
@ApiOperation(value = "添加文件", httpMethod = "POST")
@PostMapping("/insert")
//上传到mysql并且复制一份到服务器地址
public Map excelInsert(HttpServletRequest request, @RequestParam MultipartFile file) {
Map map = new HashMap();
InputStream is = null;
try {
File dir = new File(filePath);
if (!dir.exists()) {
dir.mkdirs();
}
String newFileName = file.getOriginalFilename();
File newFile = new File(filePath + newFileName);
//复制操作
file.transferTo(newFile);
//读取保存的文件
is = new FileInputStream((filePath + newFileName));
System.out.println(newFile.getName());
Workbook wb = null;
if ((filePath + newFileName).endsWith(".xlsx")) {
wb = new XSSFWorkbook(is);
//HSSFWorkbook不能为xls或者et结尾，如果想要那么用XSSFWorkbook表示以什么结尾的
} else if ((filePath + newFileName).endsWith(".xls") || (filePath + newFileName).endsWith(".et")) {
wb = new HSSFWorkbook(is);
}
/* 读EXCEL文字内容 */
// 获取第一个sheet表，也可使用sheet表名获取
Sheet sheet = wb.getSheetAt(0);
List<Map<String, String>> sheetList = new ArrayList<Map<String, String>>();
List<String> titles = new ArrayList<String>();
int rowSize = sheet.getLastRowNum() + 1;
for (int j = 0; j < rowSize; j++) {
Row row = sheet.getRow(j);
if (row == null) {
continue;
}
int cellSize = row.getLastCellNum();
if (j == 0) {
for (int k = 0; k < cellSize; k++) {
Cell cell = row.getCell(k);
titles.add(cell.toString());
}
} else {
Map<String, String> rowMap = new HashMap<String, String>();
for (int k = 0; k < titles.size(); k++) {
Cell cell = row.getCell(k);
String key = titles.get(k);
String value = null;
if (cell != null) {
value = cell.toString();
}
rowMap.put(key, value);
}
sheetList.add(rowMap);
//rowMap.clear();
}
}
wb.close();
is.close();
titles.clear();
String num;
String name;
String links;
String password;
String filename;
for (Map<String, String> params : sheetList) {
num = params.get("序号");
name = params.get("姓名");
links = params.get("链接");
password = params.get("密码");
Excels excels = new Excels();
excels.setNum(num);
excels.setNames(name);
excels.setLinks(links);
excels.setPasswords(password);
excels.setFilename(newFileName);
int insert = excelsService.insert(excels);
if (insert > 0) {
map.put("code", "00000");
map.put("msg", "ok");
}
}
} catch (Exception ex) {
ex.printStackTrace();
map.put("code", "-1");
map.put("msg", "eorry");
}
return map;
}
//查询所有数据
@ApiOperation(value = "查询", httpMethod = "POST")
@PostMapping("/select")
public Map select() {
Map map = new HashMap();
try {
List<Excels> list = excelsService.select();
map.put("date", list);
map.put("code", "00000");
map.put("msg", "ok");
} catch (Exception e) {
e.printStackTrace();
map.put("code", "-1");
map.put("msg", "eorry");
}
return map;
}
//查询某一个文件到数据
@ApiOperation(value = "查询某个文件的数据", httpMethod = "POST")
@PostMapping("/select1")
public Map select1(String filname) {
Map map = new HashMap();
try {
List<Excels> list = excelsService.select1(filname);
map.put("date", list);
map.put("code", "00000");
map.put("msg", "ok");
} catch (Exception e) {
e.printStackTrace();
map.put("code", "-1");
map.put("msg", "eorry");
}
return map;
}
//查询数据库中的所有xlsx文件名
@ApiOperation(value = "查询数据库中的所有xlsx文件", httpMethod = "POST")
@PostMapping("/select2")
public Map select2() {
Map map = new HashMap();
try {
List<Excels> list = excelsService.select2();
map.put("date", list);
map.put("code", "00000");
map.put("msg", "ok");
} catch (Exception e) {
e.printStackTrace();
map.put("code", "-1");
map.put("msg", "eorry");
}
return map;
}
//下面的这个些是不经过数据库的操作服务器文件夹
@ApiOperation(value = "查询上传的后的服务器文件夹下所以的文件名称", httpMethod = "POST")
@PostMapping("/select3")
public Map select3() {
File file = new File(filePath);
Map map = new HashMap();
File[] fileList = file.listFiles();
for (File f : fileList) {
if (f.isFile() && (f.getName().endsWith(".xls") || f.getName().endsWith(".xlsx"))) {
map.put("date", f.getName());
map.put("code", "00000");
map.put("msg", "ok");
}
}
return map;
}
@ApiOperation(value = "打开服务器文件夹下某个名称的文件", httpMethod = "POST")
@PostMapping("/select4")
public Map select3(String filename) {
ExcelReader reader = ExcelUtil.getReader(filePath+filename);
List<Map<String,Object>> readAll = reader.readAll();
Map map = new HashMap();
map.put("date", readAll);
map.put("code", "00000");
map.put("msg", "ok");
return map;
}
/**
* 下载
*
* @param request
* @param response
* @return
* @throws UnsupportedEncodingException
*/
@ApiOperation(value = "下载某个文件", httpMethod = "POST")
@RequestMapping("/download")
//需要从浏览器进行访问否则打不开
public Object downloadFile(HttpServletRequest request,
HttpServletResponse response, String fileFullName) throws UnsupportedEncodingException {
//String rootPath = propertiesconfig.getUploadpacketPath();//这里是我在配置文件里面配置的根路径，各位可以更换成自己的路径之后再使用（例如：D：/test）
String FullPath = filePath + fileFullName;//将文件的统一储存路径和文件名拼接得到文件全路径
File packetFile = new File(FullPath);
String fileName = packetFile.getName(); //下载的文件名
File file = new File(FullPath);
// 如果文件名存在，则进行下载
if (file.exists()) {
// 配置文件下载
//response.setHeader("Content-Type", "application/octet-stream");
response.setContentType("application/octet-stream");
// 下载文件能正常显示中文
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
// 实现文件下载
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("Download the song successfully!");
} catch (Exception e) {
System.out.println("Download the song failed!");
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
} else {//对应文件不存在
try {
//设置响应的数据类型是html文本，并且告知浏览器，使用UTF-8 来编码。
response.setContentType("text/html;charset=UTF-8");
//String这个类里面， getBytes()方法使用的码表，是UTF-8， 跟tomcat的默认码表没关系。 tomcat iso-8859-1
String csn = Charset.defaultCharset().name();
System.out.println("默认的String里面的getBytes方法使用的码表是： " + csn);
//1. 指定浏览器看这份数据使用的码表
response.setHeader("Content-Type", "text/html;charset=UTF-8");
OutputStream os = response.getOutputStream();
os.write("对应文件不存在".getBytes());
} catch (IOException e) {
e.printStackTrace();
}
// return R.error("-1","对应文件不存在");
}
return null;
}
//单个删除某个文件
@ApiOperation(value = "删除某个文件", httpMethod = "POST")
@RequestMapping("/deleteFile")
public Map delFile(String fileFullName) {
String FullPath = filePath + fileFullName;//将文件的统一储存路径和文件名拼接得到文件全路径
// File packetFile = new File(FullPath);
Map map = new HashMap();
// String fileName = packetFile.getName(); //下载的文件名
File file = new File(FullPath);
if (file.exists()){
file.delete();
map.put("msg","删除成功");
}else
{
map.put("msg","文件不存在");
}
return map;
}
@ApiOperation(value = "批量删除某些文件", httpMethod = "POST")
@RequestMapping("/deleteFilelist")
public Map delFilelist(String fileFullName) {
Map map = new HashMap();
String[] fileFullNames = fileFullName.split(",");
for (String fileFullName1 : fileFullNames) {
String FullPath = filePath + fileFullName1;//将文件的统一储存路径和文件名拼接得到文件全路径
// File packetFile = new File(FullPath);
// String fileName = packetFile.getName(); //下载的文件名
File file = new File(FullPath);
if (file.exists()) {
file.delete();
map.put("msg", "删除成功");
} else {
map.put("msg", "文件不存在");
}
}
return map;
}
}如果项目是前后端分离到项目那么需要配置一个跨域到类
//解决跨域问题
@Configuration
public class CrosConfig implements WebMvcConfigurer {
@Override
public void addCorsMappings(CorsRegistry registry) {
registry.addMapping("/**")
.allowedOrigins("*")
.allowedMethods("GET", "HEAD", "POST", "PUT", "DELETE", "OPTIONS")
.allowCredentials(true)
.maxAge(3600)
.allowedHeaders("*");
}
}配置一个Swaggerconfig
@Configuration
@EnableSwagger2
public class Swaggerconfig {
@Bean
public Docket api() {
return new Docket(DocumentationType.SWAGGER_2)
.apiInfo(apiInfo())
.pathMapping("/")
.select()
.paths(PathSelectors.regex("/.*"))
.build();
}
private ApiInfo apiInfo() {
return new ApiInfoBuilder().title("我的接口文档")
.contact(new Contact("suibianxie","","写个email"))
.description("这是我的swagger接口文档")
.version("2.6.1")
.build();
}
}如有错误请大家提出。。。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
