前端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腳本內(nèi)容,注意的是 processData，contentType這兩個屬性必須為false；
至此前端內(nèi)容就結束了，可以直接copy了改就可以了
接下來再看看后端代碼，我會貼出excel解析的部分，有需要的可以作為參考：
controller層
/**
* Excel表格插入數(shù)據(jù)到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"; //數(shù)據(jù)庫名稱
String USERNAME = "pmj"; //用戶名
String PASSWORD = "123456"; //密碼
String COLLECTION = "sign"; //文檔名稱
try {
// 根據(jù)輸入流導入Excel產(chǎn)生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);
// 用戶名，數(shù)據(jù)庫，密碼
MongoCredential credential = MongoCredential.createCredential(USERNAME, DATABASE, PASSWORD.toCharArray());
List<MongoCredential> credentials = new ArrayList<MongoCredential>();
credentials.add(credential);
// 通過驗證獲取連接
MongoClient mongoClient = new MongoClient(address, credentials);
// 連接到數(shù)據(jù)庫
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 "插入失敗";
}
}
}
