Shulou(Shulou.com)06/02 Report--

This article will explain in detail how Java+EasyExcel implements the import and export of files. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Introduction

The project needs to import and export Excel files Excel and download, for example, import employee information, export employee information, manual input is more cumbersome, so this blog post teaches you how to import Excel files and export Excel files in Java

Technology stack

Excel tool: EasyExcel

Choose frames: Spring, Spring MVC, MyBatis (SSM)

Project Construction Management tool: Maven

Demand:

1. It is required to use excel tools to import and export employee information.

two。 Export is required to export to a specified location and download

3. After the import file is imported, it is saved in the database and displayed on the page

4. Export the file, click the export and write to the specified address, and download the file

Effect picture

Project structure

Core source code

Import Alibaba EasyExcel dependency

Com.alibaba easyexcel 2.1.6

EasyExcel is used here, so why not POI?

Because EasyExcel is an upgrade to POI, POI is relatively bulky, and EasyExcel removes some cumbersome things about POI, so EasyExcel is relatively lightweight, so EasyExcel is used in this paper.

EasyExcel is a product of Alibaba, POI is an open source product of the Apache Foundation, and EasyExcel has made an upgrade to POI.

Core entity class package com.wanshi.spring.entity;import com.alibaba.excel.annotation.ExcelIgnore;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.write.style.ColumnWidth;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data@NoArgsConstructor@AllArgsConstructorpublic class Employee {@ ExcelIgnore private String noid; @ ColumnWidth (20) @ ExcelProperty ("employee name") private String emp_name @ ColumnWidth (20) @ ExcelProperty ("employee age") private Integer emp_age; @ ExcelIgnore private Integer emp_sex; / / redundant fields @ ColumnWidth (20) @ ExcelProperty ("employee gender") private String str_emp_sex; @ ColumnWidth (20) @ ExcelProperty ("employee salary") private Double emp_salary; @ ColumnWidth (20) @ ExcelProperty ("employee address") private String emp_address @ ColumnWidth (20) @ ExcelProperty ("employee posts") private String emp_position; / / related to paging. The current page is related to the number of data entries per page @ ExcelIgnore private Integer pageNum; @ ExcelIgnore private Integer pageSize;} core listener class

EmployeeListener class:

Package com.wanshi.spring.listener;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.wanshi.spring.entity.Employee;import java.util.ArrayList;import java.util.List;public class EmployeeReadListener extends AnalysisEventListener {/ / employee collection private static List employeeList = new ArrayList (); / / the invoke method is called once for each read, @ Override public void invoke (Employee data, AnalysisContext context) {employeeList.add (data) System.out.println ("parse to a piece of data:" + data);} / / after reading all, the method @ Override public void doAfterAllAnalysed (AnalysisContext context) {System.out.println ("parsing all is complete") is called;} / * returns the read employee collection * @ return * / public static List getStudentList () {return employeeList }} EasyExcel Import File

Test test class implementation files are imported and stored in the database

@ Testpublic void test1 () {ExcelReaderBuilder workBook = EasyExcel.read ("C:\\ Users\\ Wang will call\\ Desktop\\ employee .xlsx", Employee.class, new EmployeeReadListener ()); / / encapsulate worksheet ExcelReaderSheetBuilder sheet1 = workBook.sheet (); / / read sheet1.doRead (); / / write to database List studentList = EmployeeReadListener.getStudentList () For (Employee employee: studentList) {employee.setNoid (PbSecretUtils.uuid ()); employeeMapper.insert (employee);}}

Click to import the file through the page and save it to the database

EmployeeController class:

@ PostMapping ("/ import_employee_excel") public String importEmployeeExcel (MultipartFile emp_excel) {employeeService.importExcel (emp_excel); return "redirect:/employee/list";}

EmployeeService class:

/ * get the file selected by the user and store the file in the specified location and then store the data in the database * @ param emp_excel * @ return * / public Integer importExcel (MultipartFile emp_excel) {try {String fileName = FileUploadUtil.upload (emp_excel, "); ExcelReaderBuilder workBook = EasyExcel.read (GlobalSet.upload_url+fileName, Employee.class, new EmployeeReadListener ()) / / Encapsulation worksheet ExcelReaderSheetBuilder sheet1 = workBook.sheet (); / / read sheet1.doRead (); List studentList = EmployeeReadListener.getStudentList (); for (Employee employee: studentList) {employee.setNoid (PbSecretUtils.uuid ()); if ("male" .equals (employee.getStr_emp_sex () {employee.setEmp_sex (1) } else {employee.setEmp_sex (2);} employeeMapper.insert (employee);}} catch (IOException e) {e.printStackTrace ();} return 0;} EasyExcel export file

Test test class export file to specified file

@ Testpublic void test2 () throws FileNotFoundException {List employeeList = initData (); ExcelWriterBuilder workBook = EasyExcel.write (GlobalSet.download_url, Employee.class); / / sheet method parameters: the sequence number of the worksheet (starting with 0) or the name of the worksheet workBook.sheet ("Test data Table") .doWrite (employeeList); System.out.println ("write complete!") ;} / * generate test data * @ return * / public List initData () {List employeeList = new ArrayList (); for (int I = 1; I

< 100; i++) { Employee employee = new Employee(); employee.setEmp_name("小王说："+i); employee.setEmp_age(19); if (i % 10 == 0) { employee.setEmp_sex(1); } else { employee.setEmp_sex(2); } employee.setEmp_salary(19999.00+i); employee.setEmp_address("北京市朝阳区"+i); employee.setEmp_position("Java高级工程师"); employeeList.add(employee); } return employeeList;} 通过页面导出到指定文件后并下载文件 EmployeeController类 @GetMapping("/export_employee_excel") public void exportEmployeeExcel(HttpServletResponse response) { try { employeeService.exportEmployeeExcel(response); } catch (IOException e) { e.printStackTrace(); } } EmployeeService类： public void exportEmployeeExcel(HttpServletResponse response) throws IOException { List kspwStudentSeatList = list(); try { ExcelWriterBuilder workBook = EasyExcel.write(GlobalSet.download_url, Employee.class); // sheet方法参数： 工作表的顺序号（从0开始）或者工作表的名字 workBook.sheet("员工信息").doWrite(kspwStudentSeatList); downloadTempalate(response); System.out.println("写入完成！"); } catch (Exception e) { e.printStackTrace(); } } /** * 下载文件 * @param response * @throws IOException */ public static void downloadTempalate(HttpServletResponse response) throws IOException { // 告诉浏览器用什么软件可以打开此文件 response.setHeader("content-Type", "application/vnd.ms-excel"); // 下载文件的默认名称 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("员工信息.xlsx", "utf-8")); //4. 创建输入、输出流 FileInputStream input = new FileInputStream(GlobalSet.download_url); ServletOutputStream sos = response.getOutputStream(); //IO流获取文件的字节流，然后再响应给浏览器 byte[] arr = new byte[1024]; int res = 0; while((res = input.read(arr)) >

0) {/ / output the read content to the output stream sos.write (arr, 0, res);} input.close (); sos.close () } this is the end of the article on "how to import and export Java+EasyExcel files". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.