Using excel file to import data and integrating mongodb to implement punch in system
Environmental parameters
- Development tools: IDEA
- Basic environment: Maven+JDK8
- Main technologies: SpringBoot, Mongodb
- Spring boot version: 2.2.6
The implementation steps are as follows:
1. Add dependency
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- excel tool -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</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>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
2. Entity layer
3. Business service layer
4. service implementation layer
package com.ckf.mongodb_punch.service.impl;
import com.ckf.mongodb_punch.mapper.AttendRepository;
import com.ckf.mongodb_punch.entity.Attend;
import com.ckf.mongodb_punch.service.AttendService;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@Service
public class AttendServiceImpl implements AttendService {
@Autowired
private AttendRepository attendRepository;
@Autowired
private MongoTemplate mongoTemplate;
/**
* Upload file
* @param classes
* @param nameListExcel
* @return
*/
@Override
public String upload(String classes, MultipartFile nameListExcel) {
String result = "no";
if (nameListExcel == null) {
return result;
}
//Instanced object list for storing Excel Data in
List<Attend> attendList = new ArrayList<Attend>();
//Read file object nameListExcel Data in (read Excel Each row of data in, save to object, save to object list)
try {
//Get this operation according to the path excel Examples of
HSSFWorkbook wb = new HSSFWorkbook(nameListExcel.getInputStream());
//According to page index Obtain sheet page
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = null;
//loop sesheet The data in the page starts from the second row, the first row is the title
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
//Get each row of data
row = sheet.getRow(i);
Attend attend = new Attend();
//Below cellnum Corresponding to subscript, id The first digit corresponds to a subscript of 0, name Is the second corresponding subscript 1,Wait..
attend.setId(Integer.valueOf((int) row.getCell(0).getNumericCellValue()));
attend.setName(row.getCell(1).getStringCellValue());
attend.setSign(Integer.valueOf((int) row.getCell(2).getNumericCellValue()));
attendList.add(attend);
}
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("analysis Excel Data in:" + attendList);
/**
* If it succeeds, write it to mongodb
*/
attendRepository.saveAll(attendList);
result = "ok";
return result;
}
/**
* Sign in
* @param name
* @return
*/
@Override
public String sign(String name) {
Query query = Query.query(Criteria.where("name").is(name));
//Contents of partial modification
Update update = new Update();
update.set("sign", 1);
//attend Set name the set name of the corresponding entity
mongoTemplate.updateFirst(query, update, "attend");
return "ok";
}
/**
* Full query of student information
* @param sign
* @return
*/
@Override
public List<Attend> findAllBySign(Integer sign) {
return attendRepository.findAllBySign(sign);
}
}
5.controller layer
package com.ckf.mongodb_punch.controller;
import com.ckf.mongodb_punch.entity.Attend;
import com.ckf.mongodb_punch.service.AttendService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
public class AttendController {
@Autowired
private AttendService attendService;
@GetMapping("/sign")
public String sign(String name){
/**
* Pass the name to the service layer, and mongodb modifies the login status
*/
attendService.sign(name);
return "ok";
}
/**
* Upload file
* @param classes
* @param nameListExcel
* @return
*/
@PostMapping("/upload")
public String upload(String classes, MultipartFile nameListExcel){
/**
* Receive the file object from the foreground and submit it to the service layer or Excel tool class to parse the data
* System.out.println("Receive the submission data of foreground form: "+ classes + namelistexcel";
*/
String result = attendService.upload(classes,nameListExcel);
return result;
}
/**
* Check the students who have not signed in and who have signed in
* @return
*/
@GetMapping("/list")
public Map list(){
Map result = new HashMap<String,Object>();
/**
* Signed in
*/
List<Attend> complete = attendService.findAllBySign(1);
result.put("complete",complete);
/**
* Not signed in
*/
List<Attend> incomplete = attendService.findAllBySign(0);
result.put("incomplete",incomplete);
return result;
}
}
6.application.yml
The security authentication configuration of mongodb is used here
spring:
data:
mongodb:
uri: mongodb://ckf_user:123456@192.168.85.154:27017/attend_db
The default single example configuration is as follows
spring:
data:
mongodb:
uri: mongodb://localhost:27017/attend_db
Asynchronous implementation is used here
7.list.html code is as follows
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Attendance management page</title>
<style>
#complete,#incomplete{
width: 50%;
float: left;
}
</style>
<script src="http://apps.bdimg.com/libs/jquery/2.1.1/jquery.min.js" type="text/javascript" charset="utf-8"></script>
</head>
<body>
<h3>Import list</h3>
//Class name:
<input type="text" name="classes" id="classes"/>
//Please select an import file
<input type="file" name="nameList" id="nameList"/>
<input type="button" id="upload" value="upload">
<hr/>
<div id="incomplete">
<h3>Not signed in</h3>
<p></p>
</div>
<div id="complete">
<h3>Signed in</h3>
<p></p>
</div>
</body>
<script type="text/javascript">
$(function () {
//Initialize page query results
$.ajax({
type:"get",
url:"/list",
success:function(data){
console.log(data);
var complete ="";
var incomplete ="";
$.each(data.complete,function (index,object) {
complete += object.id +" " +object.name +"<br/>";
})
$("#complete p").html(complete);
$.each(data.incomplete,function (index,object) {
incomplete += object.id +" " +object.name +"<br/>";
})
$("#incomplete p").html(incomplete);
}
});
$("body").on("click","#upload",function(){
//Package data into formData In object
var formData = new FormData();
formData.append("classes",$("#classes").val());
formData.append("nameListExcel",$("#nameList")[0].files[0]);
$.ajax({
type:"post",
url:"/upload",
//dataType:"json",
data:formData,
processData: false,
contentType: false,
success:function(data){
console.log(data);
if(data=="ok"){
alert("Upload succeeded, page will be refreshed soon")
//Refresh current page
location.reload();
}else {
alert("Upload failed, please upload again")
}
}
});
})
})
</script>
</html>
Check in and punch in code is as follows:
8.sign-in.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Sign in page</title>
<script src="http://apps.bdimg.com/libs/jquery/2.1.1/jquery.min.js" type="text/javascript" charset="utf-8"></script>
</head>
<body>
//Please enter your name:<input type="text" id="name"/>
<input type="button" id="sign" value="Sign in"/>
</body>
<script type="text/javascript">
$(function () {
$("body").on("click","#sign",function(){
$.ajax({
type:"get",
url:"/sign",
data:{"name":$("#name").val()},
success:function(data){
console.log(data);
if(data=="ok"){
alert("Sign in succeeded, return to the sign in page")
//Refresh current page
location.reload();
}else {
alert("Sign in succeeded, please sign in again")
}
}
});
})
})
</script>
</html>
list.html page rendering
The remote tool queries the data just imported as follows
The path with package behind the data is because mongodb configuration class is not added when importing data, and there is no added configuration class.
Remember to leave a message below if you don't understand.
Project managed code cloud
Address: https://gitee.com/ckfeng/mongodb_punch.git
To be updated