Join in
<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.2.0</version> </dependency>
Write import and export model
public class PersonMode implements IExcelModel {
@Excel(name = "error message",orderNum = "3")
private String errorMsg;
/**
* Full name
*/
@Excel(name = "Full name", orderNum = "0")
@Pattern(regexp = "[\u4E00-\u9FA5]*", message = "Name is not Chinese")
private String name;
/**
* ID number
*/
@Excel(name = "ID number", orderNum = "1")
@NotEmpty(message = "ID card No. cannot be empty")
private String cardno;
/**
* number
*/
@Excel(name = "number", orderNum = "2")
private String code;
public String getErrorMsg() {
return errorMsg;
}
public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCardno() {
return cardno;
}
public void setCardno(String cardno) {
this.cardno = cardno;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
}
IExcelModel is a tag class. The source code is as follows. Inherit this class to get the validation error information when importing
/**
* Excel Tag class
* @author JueYue
* 2015 9:31:47 PM, October 31, 2010
*/
public interface IExcelModel {
/**
* Get error data
* @return
*/
public String getErrorMsg();
/**
* Set error message
* @param errorMsg
*/
public void setErrorMsg(String errorMsg);
}
Add in the import model class: this must be added in the import model, otherwise the error information of validation cannot be obtained
@Excel(name = "error message",orderNum = "3")
private String errorMsg;
public String getErrorMsg() {
return errorMsg;
}
public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}
Note: @ Excel(name = error message, orderNum = 3 "), where name is the specified column name and orderNum is the column number
Note: @ Pattern(regexp = "[\ u4E00-\u9FA5] *", message = "name is not Chinese") passes the regular verification data, and message is the error message
Note: @ NotEmpty(message = "ID card number cannot be empty") not empty verify message is an error message
The verification package of javax.validation used in the verification part can be seen in the document
Add import / export tool class
/**
* excel Export tool class
*/
public class ExcelExportUtil {
private ExcelExportUtil() {
}
/**
* @param entity
* Table Title Properties
* @param pojoClass
* Excel Object Class
* @param dataSet
* Excel Object data List
*/
public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
Collection<?> dataSet) {
ExcelBatchExportService batchService = ExcelBatchExportService
.getExcelBatchExportService(entity, pojoClass);
return batchService.appendData(dataSet);
}
public static Workbook exportBigExcel(ExportParams entity, List<ExcelExportEntity> excelParams,
Collection<?> dataSet) {
ExcelBatchExportService batchService = ExcelBatchExportService
.getExcelBatchExportService(entity, excelParams);
return batchService.appendData(dataSet);
}
public static void closeExportBigExcel() {
ExcelBatchExportService batchService = ExcelBatchExportService.getCurrentExcelBatchExportService();
if(batchService != null) {
batchService.closeExportBigExcel();
}
}
/**
* @param entity
* Table Title Properties
* @param pojoClass
* Excel Object Class
* @param dataSet
* Excel Object data List
*/
public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass,
Collection<?> dataSet) {
Workbook workbook = getWorkbook(entity.getType(),dataSet.size());
new ExcelExportService().createSheet(workbook, entity, pojoClass, dataSet);
return workbook;
}
private static Workbook getWorkbook(ExcelType type, int size) {
if (ExcelType.HSSF.equals(type)) {
return new HSSFWorkbook();
} else if (size < 100000) {
return new XSSFWorkbook();
} else {
return new SXSSFWorkbook();
}
}
/**
* Create corresponding Excel according to Map
* @param entity
* Table Title Properties
* @param entityList
* Map Object list
* @param dataSet
* Excel Object data List
*/
public static Workbook exportExcel(ExportParams entity, List<ExcelExportEntity> entityList,
Collection<?> dataSet) {
Workbook workbook = getWorkbook(entity.getType(),dataSet.size());;
new ExcelExportService().createSheetForMap(workbook, entity, entityList, dataSet);
return workbook;
}
/**
* Creating multiple sheet s with one excel
*
* @param list
* Multiple map key titles correspond to table Title key entity correspond to table key data
* Collection data
* @return
*/
public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {
Workbook workbook = getWorkbook(type,0);
for (Map<String, Object> map : list) {
ExcelExportService service = new ExcelExportService();
service.createSheet(workbook, (ExportParams) map.get("title"),
(Class<?>) map.get("entity"), (Collection<?>) map.get("data"));
}
return workbook;
}
/**
* The exported file is parsed through the template. This is not recommended. It is recommended that all files be processed through the template
*
* @param params
* Export parameter class
* @param pojoClass
* Corresponding entity
* @param dataSet
* Entity set
* @param map
* Template set
* @return
*/
@Deprecated
public static Workbook exportExcel(TemplateExportParams params, Class<?> pojoClass,
Collection<?> dataSet, Map<String, Object> map) {
return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, pojoClass, dataSet,
map);
}
/**
* The exported file is resolved by template, only template, no collection
*
* @param params
* Export parameter class
* @param map
* Template set
* @return
*/
public static Workbook exportExcel(TemplateExportParams params, Map<String, Object> map) {
return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, null, null, map);
}
/**
* The exported file is resolved by template, only template, no collection
* Each sheet corresponds to a map, where the key is the NUM of the sheet
* @param params
* Export parameter class
* @param map
* Template set
* @return
*/
public static Workbook exportExcel(Map<Integer, Map<String, Object>> map,
TemplateExportParams params) {
return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, map);
}
}
Calling tool class to complete import and export
@PostMapping("/import-person")
public void importByExcel(@RequestParam("file") MultipartFile file) throws IOException {
RequestAttributes requestAttributes = RequestContextHolder.currentRequestAttributes();
HttpServletResponse response = ((ServletRequestAttributes) requestAttributes).getResponse();
try{
//Parsing excel
ExcelImportResult<PersonMode> importList = ExcelUtil.importExcel(file.getInputStream(),0,1, WastePersonMode.class);
List<PersonMode> successList = importList.getList(); //Import successful data
List<PersonMode> failList = importList.getFailList(); //Validation failed data
//Export data with validation failure
if (failList.size()>0) {
ExportParams exportParams = new ExportParams();
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, WastePersonMode.class, failList);
// Tell the browser what software to use to open this file
response.setHeader("content-Type", "application/vnd.ms-excel;charset=utf-8");
// Default name of the download file
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("Error message table", "UTF-8") + ".xls");
//Code
response.setCharacterEncoding("UTF-8");
workbook.write(response.getOutputStream());
workbook.close();
}
if(successList.size() > 0){
//TODO processes data that passes validation
}
}catch (Exception e){
e.printStackTrace();
throw new Exception("Import personnel table exception",e);
}
}
This completes import validation and error message export
ExcelUtil.importExcel(file.getInputStream(),0,1, WastePersonMode.class); import
ExcelExportUtil.exportExcel(exportParams, WastePersonMode.class, failList); export