preface

Thanks to alibaba project team for providing easyExcel tools, github address: github.com/alibaba/eas…

Introduction to the

EasyExcel is a Java – based simple, save – memory Excel reading and writing open source project. Save as much memory as possible to support reading and writing Excel 100 MB.

Introduction of depend on

Gradle is used for this projectCompile group: ‘com.alibaba’, name: ‘easyexcel’, version: ‘2.2.6’

Write the entity class corresponding to the fields of imported Excel

Public Class TeacherData {@excelProperty (index = 0, Value = "phone ") private String Phone; @excelProperty (index = 1, value = "name ") private String Name; @excelProperty (index = 2, value = "iD ") private String idCard; public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getIdCard() { return idCard; } public void setIdCard(String idCard) { this.idCard = idCard; }}Copy the code

Writing a listener class

public class TeacherDataListener extends AnalysisEventListener<TeacherData> { private static final Logger LOGGER = LoggerFactory.getLogger(TeacherDataListener.class); Private static final int BATCH_COUNT = 5; private static final int BATCH_COUNT = 5; List<TeacherData> list = new ArrayList<TeacherData>(); /** * If this is a DAO, of course there is business logic that this can also be a service. Of course this object doesn't work if you don't have to store it. */ private TeacherBookimportService teacherBookimportService; /** * If spring is used, use this constructor. * * @param */ public TeacherDataListener(TeacherBookimportService teacherBookimportService) { this.teacherBookimportService = teacherBookimportService; ** @param data * one row value. Is the same as {@link AnalysisContext#readRowHolder()} * @param Context */ @override public void invoke(TeacherData data, AnalysisContext context) {logger. info(" parse to a data :{}", JSON.toJSONString(data)); list.add(data); OOM if (list.size() >= BATCH_COUNT) {saveData(); if (list.size() >= BATCH_COUNT) {saveData(); List list.clear(); }} @override public void doAfterAllAnalysed(AnalysisContext) {// Save the data here too, ensuring that the last remaining data is also stored in the database saveData(); Logger. info(" All data parsed!" ); } private void saveData() {logger. info("{} "); , list.size()); teacherBookimportService.save(list); Logger. info(" Database saved successfully!" ); }}Copy the code

Write the business interface: omit the logic for entering the library

interface

public interface TeacherBookimportService {

    void save(List<TeacherData> list);
}

Copy the code

The implementation class

@Service public class teacherBookimportServiceImpl implements TeacherBookimportService { @Override public void save(List<TeacherData> list) { if (list ! = null && list.size() > 0) {for (TeacherData TeacherData: list) {system.out.println (" date: "+ TeacherData.getPhone ()); System.out.println(" 中 文 : "+ TeacherData.getName ()); System.out.println(" iD: "+ TeacherData.getidCard ()); }}}}Copy the code

Write Controller to call the method /teacherUpload/upload to upload a file

@RestController @RequestMapping("/teacherUpload") public class TeacherUploadController { @Autowired private TeacherBookimportService teacherBookimportService; /** * File download (failure will return an Excel with partial data) * <p> * 1. See {@link} * <p> * 2. * <p> * 3. */ @getMapping ("/download") public void download(HttpServletResponse Response) Throws IOException {// SetContentType ("application/vnd.ms-excel"); postman Response.setContentType ("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder. Encode (" test ", "utF-8 "). ReplaceAll ("\\+", "% 20"); response.setHeader("Content-disposition", "attachment; filename*=utf-8''" + fileName + ".xlsx"); EasyExcel. Write(Response.getOutputStream (), teacherData.class).sheet(" template ").dowrite (data()); ** @getMapping ("/downloadFailedUsingJson") public void GetMapping("/downloadFailedUsingJson") public void GetMapping("/downloadFailedUsingJson" DownloadFailedUsingJson (HttpServletResponse Response) throws IOException {// Postman try {response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder. Encode (" test ", "utF-8 "). ReplaceAll ("\\+", "% 20"); response.setHeader("Content-disposition", "attachment; filename*=utf-8''" + fileName + ".xlsx"); EasyExcel. Write (response.getOutputStream(), Teacherdata.class).autocloseStream (Boiler.false).sheet(" template ").dowrite (data()); } catch (Exception e) {// Reset response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map<String, String> map = new HashMap<String, String>(); map.put("status", "failure"); Map.put ("message", "failed to download file" + LLDB etMessage()); response.getWriter().println(JSON.toJSONString(map)); }} / file upload * * * * < p > * 1. Create excel corresponding entity object Reference to {@ link} * < p > * 2. Because the default is to read Excel line by line, you need to create an Excel line by line callback listener, see {@link} * <p> * 3. */ @postmapping ("/upload") @responseBody public String upload(@requestParam ("file") MultipartFile file) throws ResponseBody IOException { EasyExcel.read(file.getInputStream(), TeacherData.class, new TeacherDataListener(teacherBookimportService)).sheet().headRowNumber(2).doRead(); return "success"; } private List<TeacherData> data() { List<TeacherData> list = new ArrayList<TeacherData>(); for (int i = 0; i < 10; i++) { TeacherData data = new TeacherData(); list.add(data); } return list; }}Copy the code

The import of excel

Import the results

Importing a simple front-end page lets you call the back-end upload interface directly

Save the following code in TXT, TXT suffix changed to HTML, inside the call back end upload interface can be tested

< form method = "POST" enctype = "multipart/form - the data" action = "HTTP: 127.0.0.1:9090 / teacherUpload/upload" > < table > <tr><td>File to upload:</td><td><input type="file" name="file" /></td></tr> <tr><td></td><td><input type="submit" value="Upload" /></td></tr> </table> </form>Copy the code