前端ajax,后端springBoot
1.准备：
- 1.导入相关的依赖的依赖:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.mongodb</groupId>
<artifactId>mongodb-driver</artifactId>
</dependency>
<dependency>
<groupId>org.mongodb</groupId>
<artifactId>mongodb-driver-core</artifactId>
</dependency>
<dependency>
<groupId>org.mongodb</groupId>
<artifactId>bson</artifactId>
</dependency>
<!-- 引入poi，解析workbook视图 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!-- 处理excel和上面功能是一样的-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</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>
2.设置接收的最大容量：
在application.properties文件中：
## 设置接收文件的大小
spring.servlet.multipart.max-file-size=10MB
spring.servlet.multipart.max-request-size=20MB
2.前端代码：
<form action="/excleimport" method="POST" enctype="multipart/form-data">
<input type="file" id="file1" name="fileContent">
</form>
<button id='btn'> 提交</button>
这个是选择文件的file,注意这个input标签必须要在form表单中，而且form的enctype必须为multipart/form-data
<script src="/js/jquery-3.4.1.min.js"></script>
<script>
$("#btn").click(function() {
var formData = new FormData();
formData.append("excelFile", $("#file1")[0].files[0]);
$.ajax({
url: "/excleimport",
data: formData,
type: "post",
processData: false,
contentType: false,
success: function(data) {
alert(data.result);
},
error: function() {
alert("失败")
}
});
})
</script>
这个是js脚本内容,注意的是 processData，contentType这两个属性必须为false；
至此前端内容就结束了，可以直接copy了改就可以了
接下来再看看后端代码，我会贴出excel解析的部分，有需要的可以作为参考：
controller层
/**
* Excel表格插入数据到MongoDB
* @param excelFile
* @param response
* @return
* @throws IOException
*/
@ResponseBody
@RequestMapping(value = "/excleimport", method = RequestMethod.POST)
public Map<String, Object> excleimport(@RequestParam MultipartFile excelFile,
HttpServletResponse response) throws IOException {
response.setHeader("Access-Control-Allow-Origin", "*");
Map<String, Object> map = new HashMap<String, Object>();
String name = excelFile.getOriginalFilename();
if (!name.endsWith(".xls") && !name.endsWith(".xlsx")) {
System.out.println("文件不是excel类型");
map.put("result", "文件类型错误");
} else {
map.put("result",ExcelToMongo.getDataFromExcel(excelFile.getInputStream()));
}
return map;
}
到此所有的文件传值与接收就结束了，下面在附上ExcelToMongo.getDataFromExcel方法,也就是解析excel的方法：
package com.pmj.sign.util;
import com.mongodb.MongoClient;
import com.mongodb.MongoClientURI;
import com.mongodb.MongoCredential;
import com.mongodb.ServerAddress;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.bson.Document;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelToMongo {
public static String getDataFromExcel(InputStream inputStream) {
Integer PORT = 27017; //端口号
String IP = "localhost"; //Ip
String DATABASE = "java"; //数据库名称
String USERNAME = "pmj"; //用户名
String PASSWORD = "123456"; //密码
String COLLECTION = "sign"; //文档名称
try {
// 根据输入流导入Excel产生Workbook对象
Workbook workbook = null;
try {
workbook = new HSSFWorkbook(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
// IP，端口
ServerAddress serverAddress = new ServerAddress(IP, PORT);
List<ServerAddress> address = new ArrayList<ServerAddress>();
address.add(serverAddress);
// 用户名，数据库，密码
MongoCredential credential = MongoCredential.createCredential(USERNAME, DATABASE, PASSWORD.toCharArray());
List<MongoCredential> credentials = new ArrayList<MongoCredential>();
credentials.add(credential);
// 通过验证获取连接
MongoClient mongoClient = new MongoClient(address, credentials);
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(DATABASE);
// 连接文档
MongoCollection<Document> collection = mongoDatabase.getCollection(COLLECTION);
System.out.println("连接成功");
List<Document> documents = new ArrayList<Document>();
List<String> fieldList = new ArrayList<String>();
// 获取Excel文档中第一个表单
Sheet sheet = workbook.getSheetAt(0);
Row row0 = sheet.getRow(0);
for (Cell cell : row0) {
fieldList.add(cell.toString());
}
int rows = sheet.getLastRowNum() + 1;
int cells = fieldList.size();
for (int i = 1; i < rows; i++) {
Row row = sheet.getRow(i);
Document document = new Document();
for (int j = 0; j < cells; j++) {
Cell cell = row.getCell(j);
document.append(fieldList.get(j), cell.toString());
}
documents.add(document);
}
collection.insertMany(documents);
System.out.println("插入成功");
return "插入成功";
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
return "插入失败";
}
}
}
