Java and EasyExcel implement file import and export, import and export is so simple

Related Tags：1.Understanding Garbage Collection in Java
2. The Clever Design of Java Map

Java and EasyExcel Introduction:

The project needs to import and export Excel files and download them. For example, importing employee information, exporting employee information, and manual input are cumbersome, so this blog post teaches you how to import and export Excel files in Java.

technology stack
Excel tool: EasyExcel
Selected framework: Spring, Spring MVC, MyBatis (SSM)
Project build management tool: Maven
needs:

1.It is required to use the excel tool to realize the import and export of employee information
2.The export requirement is to output to the specified location and download
3.After the import file is imported, it is stored in the database and displayed on the page
4.Export the file, click Export and write to the specified address, and download the file

Java and EasyExcel Renderings

Project structure

Core source code

Import Alibaba EasyExcel dependencies
< dependency >
< groupId > com.alibaba
< artifactId > easyexcel
< version > 2.1.6

EasyExcel is used here , 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 this article uses EasyExcel
EasyExcel is a product of Alibaba, POI is an open source product of Apache
Foundation, 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
@AllArgsConstructor
public 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 ( "EmployeePosition " )
private String emp_position ;

/ / Paging related, the current page and the number of data 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 > {

// employee collection
private static List employeeList = new ArrayList < >( );

// Each time the read is the same, the invoke method will be called once
@Override
public void invoke ( Employee data, AnalysisContext context) {
employeeList.add (data);
System.out.println ( "Parse to a piece of data: " + data);
}

// After reading all, this method will be called
@Override
public void doAfterAllAnalysed ( AnalysisContext context) {
System.out.println ( "All parsing completed" );
}

/**
* Return the read employee collection
* @return
*/
public static List getStudentList ( ) {
return employeeList ;
}
}

Java+EasyExcel EasyExcel import file

Java+EasyExcel,Test test class implementation file is imported and stored in the database

@Test
public void test1 () {
ExcelReaderBuilder workBook = EasyExcel.read
( "C:\Users\wanghuicall\Desktop\employee.xlsx" , Employee . class , new EmployeeReadListener ()) ;

// wrapping the 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 on the page to import the file and save it into 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 save the file to the specified location and then save the data to 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());

// 封装工作表
ExcelReaderSheetBuilder sheet1 = workBook.sheet();
// 读取
sheet1.doRead();

List studentList = EmployeeReadListener.getStudentList();
for (Employee employee : studentList) {
employee.setNoid(PbSecretUtils.uuid());
if ("男".equals(employee.getStr_emp_sex())) {
employee.setEmp_sex(1);
} else {
employee.setEmp_sex(2);
}
employeeMapper.insert (employee);
}
} catch ( IOException e) {
e.printStackTrace ();
}
return 0 ;
}

Java+EasyExcel EasyExcel export file

Test test class export file to specified file

@Test
public 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 from 0) or the name of the worksheet
workBook.sheet ( "Test Data Sheet" ). 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 ( "Beijing Chaoyang District" + i );
employee.setEmp_position ( "Java Senior Engineer" );
employeeList.add (employee);
}
return employeeList ;
}

After exporting to the specified file through the page and downloading the file

EmployeeController class
@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 method parameters: the sequence number of the worksheet (starting from 0) or the name of the worksheet
workBook.sheet ( "Employee Information" ). doWrite ( kspwStudentSeatList );
downloadTempalate (response);
System.out.println ( "Write complete!" );
} catch (Exception e) {
e.printStackTrace ();
}
}

/**
* download file
* @param response
* @throws IOException
*/
public static void downloadTempalate ( HttpServletResponse response) throws IOException {
// Tell the browser what software to use to open this file
response.setHeader ( "content-Type" , "application/vnd.ms-excel" );
// default name of the downloaded file
response.setHeader ( "Content-Disposition" , " attachment;filename =" + URLEncoder.encode ( "Employee Information.xlsx" , "utf-8" ));
//4. Create input and output streams
FileInputStream input = new FileInputStream ( GlobalSet.download_url );
ServletOutputStream sos = response.getOutputStream ();

//IO stream gets the byte stream of the file, and then responds to the browser
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 ();
}