1, Import
(1) controller layer
@DataLog(operationName = "Imported going abroad for private", operationDesc = "", methodType = MethodTypeEnum.IMPORT_TYPE)
@ApiOperation(value = "Going abroad for private-Import")
@ApiOperationSupport(order = 5)
@GetMapping(Urls.Abroad.importAbroad)
public JsonObject<Object> importAbroad(String fileName, HttpServletRequest request) {
List<String> list = csAbroadService.importAbroad(fileName, request);
return new JsonSuccessObject<>(list, "Import succeeded");
}
The request method can be either post or get. Here, get is used for verification to download exception data files. fileName is the file name uploaded by the front-end through public components. The back-end can obtain the upload path. Spell it to obtain the absolute path of the uploaded file.
You can also upload in the form of multipart / form data request. At this time, the backend needs to change the following receiving method:
@DataLog(operationName = "Imported outgoing audit", operationDesc = "", methodType = MethodTypeEnum.IMPORT_TYPE)
@ApiOperation(value = "off-office auditing -Import")
@ApiOperationSupport(order = 12)
@PostMapping(Urls.OffAudit.importAudit)
public JsonObject<Object> importAudit(MultipartFile file, HttpServletRequest request) throws IOException {
List<String> list = csOffAuditService.importAudit(file, request);
return new JsonSuccessObject<>(list, "Import succeeded");
}
(2) service layer
/**
* Going abroad for private - Import
*
* @param fileName
* @param request
* @return
*/
@Override
@Transactional(rollbackFor = Exception.class)
public List<String> importAbroad(String fileName, HttpServletRequest request) {
List<String> recordIds = new ArrayList<>();
String userId = userSessionService.getCurrentUserId(request);
String nodeId = userSessionService.getCurrentUserNodeId(request);
String upload = fileConfigProperties.getUpload();
String filePath = "";
filePath = upload + File.separator + fileName;
// Read Excel
EasyExcel.read(filePath, CsA12Do.class,
new AbroadListener(recordIds, userId, nodeId, upload, this))
.sheet().doRead();
// Control synchronization function
if (recordIds.size() == 0) {
return null;
}
// by hansc 12.10 calling the personnel library interface
LambdaQueryWrapper<CsA12> wrapper = Wrappers.lambdaQuery();
wrapper.in(CsA12::getRecordid, recordIds);
List<CsA12> list = list(wrapper);
for (CsA12 item : list) {
csA01Service.insertExportInPerson(item.geta00a(), item.geta1250(), item.geta1251(), request);
}
// Sync A00
Integer a00 = csRecordService.synA00("a12", "A1250", "RECORDID",
nodeId, recordIds);
// Synchronize current positions
/*Integer job = csRecordService.synJob("a12", "A1251", "RECORDID",
nodeId, recordIds);*/
// Synchronize executive categories
csA12Mapper.synGrade(recordIds, nodeId);
// Delete file
// boolean b = DownloadUtil.deleteFile(filePath);
return recordIds;
}
filePath is the absolute path of the uploaded file. EasyExcel's read method can read the contents of Excel's sheet page. The three parameters are the absolute path of the file, a Class class and a custom listener:
1. Class corresponds to the template class of the read Excel file, which corresponds to the Excel table column:
index starts from 0. The attributes of CsA12Do template class correspond to the columns of Excel table one by one.
2. The custom listener class needs to inherit a parent class of easyExcel, AnalysisEventListener. The details continue below
3. The sheet() method specifies which sheet page to read. The parameter can fill in the page name or serial number of the sheet page. If it is not written, the default is Sheet1:
(3) Listener
1. The principle of easyExcel is to read by line, that is, create a new listener class every time you read a line, and process the read content in the listener class. Because the listener class is created through the new keyword, we can't use the injection function of springboot to inject our bean object, so we can consider using the constructor to pass the bean object we need and some custom variables into the listener.
package com.bdsoft.cs.controller.utils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.bdsoft.cs.entity.CodeValue;
import com.bdsoft.cs.entity.CsA12;
import com.bdsoft.cs.query.CsA12Do;
import com.bdsoft.cs.service.daily.CsAbroadService;
import com.bdsoft.cs.utils.ConvertUtil;
import org.springframework.beans.BeanUtils;
import org.springframework.transaction.annotation.Transactional;
import java.io.File;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
/**
* @author hanshaocong
* @since 2021-10-22
*/
public class AbroadListener extends AnalysisEventListener<CsA12Do> {
private static final int batchSize = 1000;
private LocalDateTime time = LocalDateTime.now();
private List<CsA12Do> list = new ArrayList<>(batchSize);
// Executive category GGLB_02
// private List<CodeValue> listOfA1252;
// Go to country ZB01C
private List<CodeValue> listOfA1204;
// Reasons for going abroad CGGL_DMB_01
private List<CodeValue> listOfZdyxa1202;
// Store the primary key and return it to the front end
private List<String> recordIds;
private String userId;
private String nodeId;
private String upload;
private CsAbroadService csAbroadService;
private static Boolean formatFlag = true; // Format verification switch: true on, false off
private static Boolean repeatFlag = true; // Repeat check switch
public AbroadListener(List<String> recordIds, String userId, String nodeId, String upload, CsAbroadService csAbroadService) {
this.recordIds = recordIds;
this.userId = userId;
this.nodeId = nodeId;
this.upload = upload;
this.csAbroadService = csAbroadService;
// this.listOfA1252 = csAbroadService.getEntity("GGLB_02");
this.listOfA1204 = csAbroadService.getEntity("ZB01C");
this.listOfZdyxa1202 = csAbroadService.getEntity("CGGL_DMB_01");
}
@Override
public void invoke(CsA12Do csA12Do, AnalysisContext analysisContext) {
list.add(csA12Do);
if (list.size() >= batchSize) {
this.saveData();
list.clear();
}
}
/**
* Data import logic:
* 1,Calibration; 2. Import
*/
@Transactional(rollbackFor = Exception.class)
void saveData() {
if (verify()) {
insert();
}
}
/**
* check
*
* @return Import function switch
*/
Boolean verify() {
String separator = File.separator;
String fileName = "Going abroad for private_Import data exception.xlsx"; // Error prompt file name
String filePath = upload + separator + fileName; // Absolute path of the generated file
List<CsA12Do> formatList = new ArrayList<>(); // Malformed data
List<CsA12Do> repeatList = new ArrayList<>(); // Duplicate data
// ① Date verification
if (formatFlag) {
for (CsA12Do item : list) {
CsA12Do csA12Do = new CsA12Do();
String a1201 = item.getA1201(); // Date of departure
String a1203 = item.getA1203(); // Date of return
csA12Do.setA1201(ConvertUtil.isNotTime(a1201) ? a1201 : null);
csA12Do.setA1203(ConvertUtil.isNotTime(a1203) ? a1203 : null);
csA12Do.setA00a(item.getA00a());
if (csA12Do.getA1201() != null || csA12Do.getA1203() != null) {
formatList.add(csA12Do);
}
}
}
// ② Repeatability check
if (repeatFlag) {
for (CsA12Do item : list) {
CsA12Do query = new CsA12Do();
BeanUtils.copyProperties(item, query);
query.setA1204(ConvertUtil.getDmcod(item.getA1204(), listOfA1204));
query.setZdyxa1202(ConvertUtil.getDmcod(item.getZdyxa1202(), listOfZdyxa1202));
LambdaQueryWrapper<CsA12> wrapper = Wrappers.lambdaQuery();
wrapper.eq(CsA12::geta00a, query.getA00a())
.eq(CsA12::geta1250, query.getA1250())
.eq(CsA12::geta1251, query.getA1251())
.eq(CsA12::geta1204, query.getA1204())
.apply("date_format(a1201,'%Y-%m-%d') = {0}", query.getA1201())
.apply("date_format(a1203,'%Y-%m-%d') = {0}", query.getA1203())
.eq(CsA12::getZdyxa1202, query.getZdyxa1202())
.eq(CsA12::getDeleteflag, 0);
List<CsA12> list = csAbroadService.list(wrapper);
if (list.size() > 0) {
repeatList.add(item);
}
}
}
return csAbroadService.downloadExcel(filePath, CsA12Do.class, formatList, repeatList, null);
}
/**
* Import data
*/
void insert() {
List<CsA12> csA12s = new ArrayList<>();
for (CsA12Do item : list) {
CsA12 csA12 = new CsA12();
String uuid = UUID.randomUUID().toString();
recordIds.add(uuid);
BeanUtils.copyProperties(item, csA12);
csA12.setRecordid(uuid);
csA12.setCreatetime(time);
csA12.setCreateuser(userId);
csA12.setUpdatetime(time);
csA12.setUpdateuser(userId);
csA12.setDeleteflag(0);
// Add authority
csA12.setNodeid(nodeId);
// Code table conversion: go to country / Region
csA12.seta1204Text(item.getA1204());
csA12.seta1204(ConvertUtil.getDmcod(item.getA1204(), listOfA1204));
// Executive category, save Chinese characters first and then escape
/*csA12.seta1252Text(item.getA1252());
csA12.seta1252(ConvertUtil.getDmcod(item.getA1252(), listOfA1252));*/
// Reasons for going abroad
csA12.setZdyxa1202Text(item.getZdyxa1202());
csA12.setZdyxa1202(ConvertUtil.getDmcod(item.getZdyxa1202(), listOfZdyxa1202));
// Date conversion: date of going abroad
csA12.seta1201(ConvertUtil.stringToLocalDateTime(item.getA1201()));
// Date of return
csA12.seta1203(ConvertUtil.stringToLocalDateTime(item.getA1203()));
csA12s.add(csA12);
}
// sort
int count = csAbroadService.count() + 1;
for (int i = 0; i < csA12s.size(); i++, count++) {
csA12s.get(i).setPorder(BigDecimal.valueOf(count));
}
boolean b = csAbroadService.saveBatch(csA12s);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
this.saveData();
}
}
2, the code defines the need for a custom global variable in the listener class. When the new listener is installed, the global variables of the objects of the new listener class can be assigned by calling the constructor containing the reference, so that these attributes and classes can be called in the listener object.
3. Looking at the directory structure of the class, we can find that the Listener interface is inherited by the ReadListener interface, the ReadListener interface is implemented by the analysieventlistener class, and the analysieventlistener class is inherited by our business class AbroadListener. Therefore, in our custom Listener class AbroadListener, we need to implement several methods of the ReadListener interface, as shown in the figure above.
4. The invoke method is executed first. Read a row of Excel, create a new listener class, and then execute the invoke method once.
private List<CsA12Do> list = new ArrayList<>(batchSize);
@Override
public void invoke(CsA12Do csA12Do, AnalysisContext analysisContext) {
list.add(csA12Do);
if (list.size() >= batchSize) {
this.saveData();
list.clear();
}
}
5. The global variable list stores the Excel row data we read. batchSize is our customized read capacity. Set the size according to the actual business. For example, if it is set to 100, it reads 100 rows, and then executes the saveData method.
/**
* Data import logic:
* 1,Calibration; 2. Import
*/
@Transactional(rollbackFor = Exception.class)
void saveData() {
if (verify()) {
insert();
}
}
6. In the saveData method, execute the verification method verify() first, and then execute the write data operation insert() after there is no problem.
/**
* check
*
* @return Import function switch
*/
Boolean verify() {
String separator = File.separator;
String fileName = "Going abroad for private_Import data exception.xlsx"; // Error prompt file name
String filePath = upload + separator + fileName; // Absolute path of the generated file
List<CsA12Do> formatList = new ArrayList<>(); // Malformed data
List<CsA12Do> repeatList = new ArrayList<>(); // Duplicate data
// ① Date verification
if (formatFlag) {
for (CsA12Do item : list) {
CsA12Do csA12Do = new CsA12Do();
String a1201 = item.getA1201(); // Date of departure
String a1203 = item.getA1203(); // Date of return
csA12Do.setA1201(ConvertUtil.isNotTime(a1201) ? a1201 : null);
csA12Do.setA1203(ConvertUtil.isNotTime(a1203) ? a1203 : null);
csA12Do.setA00a(item.getA00a());
if (csA12Do.getA1201() != null || csA12Do.getA1203() != null) {
formatList.add(csA12Do);
}
}
}
// ② Repeatability check
if (repeatFlag) {
for (CsA12Do item : list) {
CsA12Do query = new CsA12Do();
BeanUtils.copyProperties(item, query);
query.setA1204(ConvertUtil.getDmcod(item.getA1204(), listOfA1204));
query.setZdyxa1202(ConvertUtil.getDmcod(item.getZdyxa1202(), listOfZdyxa1202));
LambdaQueryWrapper<CsA12> wrapper = Wrappers.lambdaQuery();
wrapper.eq(CsA12::geta00a, query.getA00a())
.eq(CsA12::geta1250, query.getA1250())
.eq(CsA12::geta1251, query.getA1251())
.eq(CsA12::geta1204, query.getA1204())
.apply("date_format(a1201,'%Y-%m-%d') = {0}", query.getA1201())
.apply("date_format(a1203,'%Y-%m-%d') = {0}", query.getA1203())
.eq(CsA12::getZdyxa1202, query.getZdyxa1202())
.eq(CsA12::getDeleteflag, 0);
List<CsA12> list = csAbroadService.list(wrapper);
if (list.size() > 0) {
repeatList.add(item);
}
}
}
return csAbroadService.downloadExcel(filePath, CsA12Do.class, formatList, repeatList, null);
}
7. In the verification method, the date format and data repeatability are verified. When the date format does not meet the standard regularity or the data in Excel table is repeated with the existing data in the database, these data are recorded in Excel and downloaded through the browser.
2, Export
1. Downloaded is a public method I defined, downloadExcel()
/**
* Download exception Excel and specify exclusion columns
*
* @param filePath Template path
* @param clz Template output entity class
* @param formatList Format exception data set
* @param repeatList Duplicate data set
* @param excludeColumn Specify column names that are not exported
* @return
*/
@Override
public Boolean downloadExcel(String filePath, Class<?> clz, List<?> formatList, List<?> repeatList, List<String> excludeColumn) {
// If the abnormal data sources are all empty, the imported data is normal without exception
if (ConvertUtil.listIsNull(formatList) && ConvertUtil.listIsNull(repeatList)) {
return true;
}
Boolean flag = true; // Import function switch, on by default
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(filePath, clz).excludeColumnFiledNames(excludeColumn).build();
if (ConvertUtil.listIsNotNull(formatList)) {
WriteSheet sheet = EasyExcel.writerSheet("Format exception page").build(); // One Excel file two Sheet pages
excelWriter.write(formatList, sheet);
}
if (ConvertUtil.listIsNotNull(repeatList)) {
WriteSheet sheet = EasyExcel.writerSheet("Duplicate data page").build();
excelWriter.write(repeatList, sheet);
}
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
try {
if (ConvertUtil.listIsNotNull(formatList) || ConvertUtil.listIsNotNull(repeatList)) { // Download only when the data source is not empty
DownloadUtil.download(filePath);
flag = false; // If Excel is downloaded, the import function will not be executed, and the import function switch will be turned off
}
} catch (IOException e) {
e.printStackTrace();
} finally {
DownloadUtil.deleteFile(filePath);
}
return flag;
}
2. The write method of easyExcel, that is, the export method, is also used to download Excel.
excelWriter = EasyExcel.write(filePath, clz).excludeColumnFiledNames(excludeColumn).build();
The write method has two parameters, one is the absolute path of the generated file (including the file suffix), and the other is the template class corresponding to the generated table. Here, the CsA12Do class is still used. It shares a template class with the import:
@ColumnWidth(20)
This annotation controls the column width of the Excel table,
@ExcelProperty(index = 0, value = "Executive name")
This primary key controls the position and column name of the header column.
excludeColumnFiledNames(excludeColumn)
This method specifies which header columns are not output, and the parameter is the collection of column names (that is, the attribute name of CsA12Do)
WriteSheet sheet = EasyExcel.writerSheet("Duplicate data page").build();
The writerSheet method sets the name of the sheet page
excelWriter.write(repeatList, sheet);
The write method binds the data source to the table template, that is, fills in the data
DownloadUtil.download(filePath);
A download method is customized, and the code is attached
3. After verifying the verify method, if there is a problem, export the problem data to Excel and download it through the browser;
If there is no problem after verification, execute the insert method, that is, write operation. Write operation is not discussed.
4. The doAfterAllAnalysed method is the last method executed after the listener class is generated.
P.S.
1. Download method attached:
public static void download(String filePath) throws IOException {
HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
download(filePath, response, request);
}
public static void download(String filePath, HttpServletResponse response, HttpServletRequest request) throws IOException {
ServletOutputStream os = null;
try {
if (response == null) {
response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
}
ByteArrayOutputStream out = null;
FileInputStream inStream = null;
BufferedInputStream bis = null;
File file = new File(filePath);
String fileName = file.getName();
if (request.getHeader("User-Agent").toLowerCase().indexOf("msie") <= 0 && request.getHeader("User-Agent").indexOf("like Gecko") <= 0) {
fileName = new String(fileName.replaceAll(" ", "").getBytes("UTF-8"), "ISO8859-1");
} else {
fileName = URLEncoder.encode(fileName, "UTF-8");
}
if (file.exists()) {
response.reset();
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
os = response.getOutputStream();
out = new ByteArrayOutputStream();
inStream = new FileInputStream(filePath);
bis = new BufferedInputStream(inStream);
for(int c = bis.read(); c != -1; c = bis.read()) {
out.write(c);
}
bis.close();
inStream.close();
os.write(out.toByteArray());
}
} catch (IOException var13) {
logger.error("Abnormal information", var13);
} finally {
if (os != null) {
os.close();
}
}
}
This method cannot customize the downloaded file name. You can modify the following:
/**
* Download File
*
* @param filePath File absolute path
* @param templateName file name
* @throws IOException
*/
public static void download(String filePath, String templateName) throws IOException {
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
download(filePath, templateName, response, request);
}
/**
* Download File
*
* @param filePath File absolute path
* @param templateName file name
* @param response
* @param request
* @throws IOException
*/
public static void download(String filePath, String templateName, HttpServletResponse response, HttpServletRequest request) throws IOException {
ServletOutputStream os = null;
try {
if (response == null) {
response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
}
ByteArrayOutputStream out = null;
FileInputStream inStream = null;
BufferedInputStream bis = null;
File file = new File(filePath);
String fileName = file.getName();
if (StringUtility.isNotNull(templateName)) {
fileName = templateName;
}
if (request.getHeader("User-Agent").toLowerCase().indexOf("msie") <= 0 && request.getHeader("User-Agent").indexOf("like Gecko") <= 0) {
fileName = new String(fileName.replaceAll(" ", "").getBytes("UTF-8"), "ISO8859-1");
} else {
fileName = URLEncoder.encode(fileName, "UTF-8");
}
if (file.exists()) {
response.reset();
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
os = response.getOutputStream();
out = new ByteArrayOutputStream();
inStream = new FileInputStream(filePath);
bis = new BufferedInputStream(inStream);
for (int c = bis.read(); c != -1; c = bis.read()) {
out.write(c);
}
bis.close();
inStream.close();
os.write(out.toByteArray());
}
} catch (IOException var13) {
var13.printStackTrace();
} finally {
if (os != null) {
os.close();
}
}
}
2. Attach date verification:
// Mm / DD / yyyy, standard format: 2021-12-23, 2021-1-1
public static String regex = "^((((19|20)\\d{2})-(0?[13-9]|1[012])-(0?[1-9]|[12]\\d|30))|(((19|20)\\d{2})-(0?[13578]|1[02])-31)|(((19|20)\\d{2})-0?2-(0?[1-9]|1\\d|2[0-8]))|((((19|20)([13579][26]|[2468][048]|0[48]))|(2000))-0?2-29))$";
/**
* Date format verification: not a standard time format
*
* @param time
* @return
*/
public static boolean isNotTime(String time) {
return !isTime(time);
}
/**
* Date format regular verification: it is a standard time format
*
* @param time Date format: 2021-12-21, 2021-1-1
* @return
*/
public static boolean isTime(String time) {
return time != null && Pattern.matches(regex, time);
}
above