This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

EasyExcel tutorial

The technology used in this paper is the open source EasyExcel technology of Alibaba Group, which is the encapsulation and optimization of Apache POI technology, mainly solves the memory consumption problem of POI technology, and provides better API use.

  • Cumbersome steps in use
  • Writing out Excel operations dynamically is cumbersome
  • For beginners, it’s hard to get started in a short time
  • A large amount of data needs to be read or written. An OOM occurs when a large amount of data is generated

Maven rely on

  <! -- EasyExcel dependency -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>
Copy the code

EasyExcel API analysis introduction

  • EasyExcel entry class, used to build start various operations

  • ExcelWriterBuilder ExcelWriterBuilder builds a ReadWorkbook and WriteWorkbook, which can be interpreted as an Excel object

  • ExcelReaderSheetBuilder builds a ReadSheet WriteSheet object, which can be thought of as a page in Excel, one for each page

  • ReadListener is called after each row is read to process the data

  • WriteHandler calls WriteHandler for every operation, including cell creation, table creation, etc

  • With EasyExcel, you can set the parameters of the Workbook using EasyExcel. If you set the parameters with EasyExcel, you can set the parameters with EasyExcel. The sheet() method is scoped to the entire sheet before it is scoped to a single sheet

EasyExcel annotations

  • ΓΈ ExcelProperty Specifies the column in Excel that the current field corresponds to. Matches can be made by name or Index. You don’t have to write it, but by default the first field is index=0, and so on. Make sure you don’t write anything at all, use index at all, or match everything by name. Never mix three together, unless you know how to sort three together in the source code.

  • ΓΈ ExcelIgnore by default, all fields will match excel. If this annotation is added, this field will be ignored

  • DateTimeFormat Date conversion. This annotation is used to receive data in Excel date format with a String. In the value of reference to Java. Text. SimpleDateFormat

  • This annotation is called when you use String to receive excel NumberFormat data. See java.text.decimalFormat for the value inside

  • ExcelIgnoreUnannotated if ExcelProperty is not annotated by default, it will not be annotated

General parameters

  • ΓΈ Both ReadWorkbook and ReadSheet have parameters. If this parameter is null, the upper level is used by default.

  • ΓΈ Converter, many converters are loaded by default. It can also be customized.

  • ΓΈ readListener is a listener that is continuously called during the process of reading data.

  • HeadRowNumber Specifies the number of rows of data to be read from the table. By default, there is a line header, that is, the second line is considered to start as data.

  • ΓΈ Head or Clazz. Reading the list corresponding to the file header will match the data according to the list. Class is recommended.

  • ΓΈ Clazz or head can be selected. Read the class corresponding to the header of the file, or use annotations. If neither is specified, all data is read.

  • ΓΈ autoTrim Data such as string and header are automatically trimmed

  • ΓΈ Password Specifies whether to use a password for reading data

ReadWorkbook (understood as Excel object) parameter

  • ΓΈ excelType The current Excel type is automatically determined by default

  • ΓΈ inputStream or file can be selected. Read the stream of the file. If the received stream is a stream, use the file parameter. Because using inputStream easyExcel will help create temporary files, and ultimately files

  • ΓΈ File or inputStream Optional. The file that reads the file.

  • ΓΈ autoCloseStream automatically closes the stream.

  • ΓΈ if the readCache value is smaller than 5M by default, memory is used. If the readCache value is larger than 5M, EhCache is used.

ReadSheet (which is an Excel Sheet) parameter

  • SheetNo requires the code to read the Sheet. We recommend using this to specify which Sheet to read

  • Excel 2003 does not support Sheet matching by name

annotations

  • ExcelProperty index specifies the number of columns to write to. By default, it is sorted by member variables. Value specifies the name to write to, the name of the default member variable, and multiple values can refer to the complex header in Quick Start

  • ΓΈ ExcelIgnore All fields are written to Excel by default. This annotation will ignore this field

  • DateTimeFormat Date conversion, writing Date to Excel will call this annotation. In the value of reference to Java. Text. SimpleDateFormat

  • This annotation is called when you write Excel with Number. See java.text.decimalFormat for the value inside

  • ExcelIgnoreUnannotated if ExcelProperty is not annotated by default, it will not be annotated

parameter

General parameters

  • WriteWorkbook,WriteSheet, and WriteTable all have their own parameters. If they are null, the default values are higher.

  • ΓΈ Converter, many converters are loaded by default. It can also be customized.

  • ΓΈ writeHandler Indicates the write processor. Can realize WorkbookWriteHandler SheetWriteHandler, RowWriteHandler CellWriteHandler, will call in the different stages of writing excel

  • ΓΈ relativeHeadRowIndex how many rows away to start. That’s the first few lines

  • ΓΈ needHead Specifies whether to export the header

  • ΓΈ Head or Clazz. Write to the header list of the file. Class is recommended.

  • ΓΈ Clazz or head can be selected. Write to the class corresponding to the header of the file, or use annotations.

  • ΓΈ autoTrim Data such as string and header are automatically trimmed

WriteWorkbook (understood as excel object) parameter

  • ΓΈ excelType The default Excel type is XLSX

  • ΓΈ Choose either outputStream or file. A stream that writes to a file

  • ΓΈ File or outputStream Optional. Written file

  • ΓΈ templateInputStream Specifies the file flow of the template

  • ΓΈ templateFile specifies the templateFile

  • ΓΈ autoCloseStream automatically closes the stream.

  • ΓΈ Password Specifies whether to use a password when writing data

  • ΓΈ useDefaultStyle Specifies whether to use the default header when writing

The WriteSheet (which is an Excel Sheet) parameter

  • ΓΈ sheetNo Specifies the code to write to. The default 0

  • SheetName Specifies the required Sheet name (sheetNo)

The WriteTable parameter

  • ΓΈ tableNo Code that needs to be written. The default 0

EasyExcel usage Guide

Simply read excel files
public void read(a) {
    String fileName = "demo.xlsx";
    // We need to specify which class is used to read the sheet, and then the first read stream is automatically closed
    // Parameter one: the path of the Excel file to read
    // Argument 2: Reads a line of the sheet and encapsulates the arguments in the DemoData entity class
    // Argument 3: The DemoDataListener listener is executed when each row is read
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
Copy the code
Simply write to excel file
@Test
public void simpleWrite(a) {
    String fileName = "demo.xlsx";
    // Here we need to specify which class to read the write, then write to the first sheet, name it template and the stream will close automatically
    // If you want to use 03, pass in the excelType argument
    // Parameter one: write the excel file path
    // Parameter two: Write data type is DemoData
    // The data() method is the data being written, resulting in a List
      
        collection
      
    EasyExcel.write(fileName, DemoData.class).sheet("Template").doWrite(data());
}
Copy the code
Web upload and download
/** Download excel files */
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    response.setHeader("Content-disposition"."attachment; filename=demo.xlsx");
    EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("Template").doWrite(data());
}

/** Upload excel file */
@PostMapping("upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(), DemoData.class, new DemoDataListener()).sheet().doRead();
    return "success";
}
Copy the code

Read Excel in detail

The object model
// Unless otherwise specified, the following examples will use this entity class by default
public class DemoData {
    It is not recommended to use both index and name. Either an object uses index or an object uses name to match */
    @ExcelProperty(index = 2)
	// I want to receive the percentage number
	@NumberFormat("#.##%")
	@ ExcelProperty (value = "title" floating point, the converter = CustomStringConverter. Class)
    private Double doubleData;
    /** * only one field will be read */ if the name is repeated
	@ ExcelProperty (value = "title" string, the converter = CustomStringConverter. Class)
	// Converter attribute defines its own string converter
    private String string;
	
	@datetimeformat (" YYYY ")
    @excelProperty (" Date title ")
	// Use string to connect the date
    private Date date;
}
Copy the code
The listener
// Unless otherwise specified, the following cases will use this listener by default
public class DemoDataListener extends AnalysisEventListener<DemoData> {

    List<DemoData> list = new ArrayList<DemoData>();
    /** * If spring is used, use this constructor. Each time you create a Listener, you need to pass in the Spring-managed classes */
    public DemoDataListener(a) {}
    / * * * the each data parsing would call for * *@param data
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println("Parse to a single data :{}", JSON.toJSONString(data));
        list.add(data);
    }

    /** * when all data is parsed, call **@param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) { System.out.println(JSON.toJSONString(list)); }}Copy the code
code
@Test
public void simpleRead(a) {
    // write method 1:
    String fileName = "demo.xlsx";
    // We need to specify which class is used to read the sheet, and then the first read stream is automatically closed
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    // write method 2:
    fileName = "demo.xlsx";
    ExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
    // Do not forget to close this file, temporary files will be created while reading, and the disk will crash
    excelReader.finish();
}
Copy the code
Read multiple sheets
@Test
public void repeatedRead(a) {
    String fileName = "demo.xlsx";
    // Read all the sheets
    // Note that DemoDataListener's doAfterAllAnalysed is called once after each sheet is read. And then all the sheets will be written to the same DemoDataListener
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();
    // Read part sheet
    fileName = "demo.xlsx";
    ExcelReader excelReader = EasyExcel.read(fileName).build();
    // For simplicity, register the same head and Listener to use a different Listener
    The readSheet parameter sets the sequence number of the readSheet
    ReadSheet readSheet1 =
        EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
    ReadSheet readSheet2 =
        EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
    // Sheet1 sheet2 must be uploaded together. Otherwise, excel 03 will read sheet1 sheet2 many times, wasting performance
    excelReader.read(readSheet1, readSheet2);
    // Do not forget to close this file, temporary files will be created while reading, and the disk will crash
    excelReader.finish();
}
Copy the code
Custom format conversion
public class CustomStringStringConverter implements Converter<String> {
    
	@Override
    public Class supportJavaTypeKey(a) {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey(a) {
        return CellDataTypeEnum.STRING;
    }

    /** ** * is called when reading@param cellData
     *            NotNull
     * @param contentProperty
     *            Nullable
     * @param globalConfiguration
     *            NotNull
     * @return* /
    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return "Custom:" + cellData.getStringValue();
    }

    /** ** * /** **@param value
     *            NotNull
     * @param contentProperty
     *            Nullable
     * @param globalConfiguration
     *            NotNull
     * @return* /
    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return newCellData(value); }}Copy the code

More wardrobe

@Test
public void complexHeaderRead(a) {
    String fileName = "demo.xlsx";
    // We need to specify which class to read and then read the first sheet
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet()
        // This can be set to 1 because the header is a line. If you have multiple rows, you can set other values. No default 1 line is passed
        .headRowNumber(1).doRead();
}
Copy the code

Read header data

Override the listener invokeHeadMap method

/** * the header is returned line by line * The listener just needs to override this method to read the header *@param headMap
 * @param context
 */
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
    LOGGER.info("Parse to a header :{}", JSON.toJSONString(headMap));
}
Copy the code

Exception handling

Override the listener onException method

/** * the listener implements this method to get an exception message when reading data */
@Override
public void onException(Exception exception, AnalysisContext context) {
    LOGGER.error("Parsing failed, but proceed to the next line :{}", exception.getMessage());
    // If it is a conversion exception of a cell, the specific line number can be obtained
    // Use invokeHeadMap if you want to get the header information
    if (exception instanceofExcelDataConvertException) { ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;  LOGGER.error("{} line, {} column parsing exception", excelDataConvertException.getRowIndex(), excelDataConvertException.getColumnIndex()); }}Copy the code

Exports the specified column

@Test
public void excludeOrIncludeWrite(a) {
	
    String fileName = "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
    // Ignore date and do not export
    Set<String> excludeColumnFiledNames = new HashSet<String>();
    excludeColumnFiledNames.add("date");
    // You need to specify which class to write to, then write to the first sheet, name it template and the stream will automatically close
    EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("Ignore the date")
        .doWrite(data());
    fileName = "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
    // Based on the user-passed field, suppose we just export date
    Set<String> includeColumnFiledNames = new HashSet<String>();
    includeColumnFiledNames.add("date");
    // You need to specify which class to write to, then write to the first sheet, name it template and the stream will automatically close
    EasyExcel.write(fileName, DemoData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("The export date")
        .doWrite(data());
}
Copy the code

Adjusts the order of the specified columns

public class IndexData {
    /** * The exported Excel columns 2 and 4 will be empty */
    @excelProperty (value = "string title ", index = 0)
    private String string;
    @excelProperty (value = "date title ", index = 2)
    private Date date;
    @excelProperty (value = "float ", index = 4)
    private Double doubleData;
}

Copy the code

Complex head write

public class ComplexHeadData {
    /** * The main header will be consolidated into a single cell as follows: * -- -- -- -- -- -- -- -- -- -- -- -- - * | main title | * -- -- -- -- -- -- -- -- -- -- -- -- - * | | string title date headlines | | digital * -- -- -- -- -- -- -- -- -- -- -- -- - * /
    @excelProperty ({" main title ", "string title "})
    private String string;
    @excelProperty ({" main title ", "date title "})
    private Date date;
    @excelProperty ({" main title ", "number title "})
    private Double doubleData;
}
Copy the code

The front belongs to the main heading and the back belongs to the subheading

Image export

@Data
@ContentRowHeight(200)
@ColumnWidth(200 / 8)
public class ImageData {
    // There are five ways to export images
    private File file;
    private InputStream inputStream;
    /** * If the string type must specify a converter, string is converted to string by default, which is the officially supported */
    @ExcelProperty(converter = StringImageConverter.class)
    private String string;
    private byte[] byteArray;
    /** * Only version 2.1.1 supports this mode */
    private URL url;
}

 @Test
public void imageWrite(a) throws Exception {
    String fileName = "imageWrite" + System.currentTimeMillis() + ".xlsx";
    // Close the stream if you use it
    InputStream inputStream = null;
    try {
        List<ImageData> list = new ArrayList<ImageData>();
        ImageData imageData = new ImageData();
        list.add(imageData);
        String imagePath = "converter" + File.separator + "img.jpg";
        // Add five types of images according to the actual use only one can be selected
        imageData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
        imageData.setFile(new File(imagePath));
        imageData.setString(imagePath);
        inputStream = FileUtils.openInputStream(new File(imagePath));
        imageData.setInputStream(inputStream);
        imageData.setUrl(new URL(
            "https://raw.githubusercontent.com/alibaba/easyexcel/master/src/test/resources/converter/img.jpg"));
        EasyExcel.write(fileName, ImageData.class).sheet().doWrite(list);
    } finally {
        if(inputStream ! =null) { inputStream.close(); }}}Copy the code

Row width, row height

@Data
@ContentRowHeight(10)
@HeadRowHeight(20)
@ColumnWidth(25)
public class WidthAndHeightData {
    @excelProperty (" String title ")
    private String string;
    @excelProperty (" Date title ")
    private Date date;
    /** * the width is 50, covering the top width of 25 */
    @ColumnWidth(50)
    @excelProperty (" Number title ")
    private Double doubleData;
}
Copy the code
  • @headrowheight (value = 35
  • ContentRowHeight(value = 25) // Content line height
  • @columnwidth (value = 50) // ColumnWidth

There’s also adaptive width, but that’s not very precise

@Test
void contextLoads(a) {
    EasyExcel.write("Adaptive. XLSX", Student.class)
        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        .sheet()
        .doWrite(getData());
}
Copy the code

Dynamic header

@Test
public void dynamicHeadWrite(a) {
    String fileName = "dynamicHeadWrite" + System.currentTimeMillis() + ".xlsx";
    EasyExcel.write(fileName)
        // Put the dynamic header here
        .head(head()).sheet("Template")
        List
      
        .doWrite(data());
}
List
      
       >
      
private List<List<String>> head() {
    List<List<String>> list = new ArrayList<List<String>>();
    List<String> head0 = new ArrayList<String>();
    head0.add("String" + System.currentTimeMillis());
    List<String> head1 = new ArrayList<String>();
    head1.add("Digital" + System.currentTimeMillis());
    List<String> head2 = new ArrayList<String>();
    head2.add("Date" + System.currentTimeMillis());
    list.add(head0);
    list.add(head1);
    list.add(head2);
    return list;
}
Copy the code

Merged cell

 @Test
 public void mergeWrite(a) {
     String fileName = "mergeWrite" + System.currentTimeMillis() + ".xlsx";
     LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2.0);
     EasyExcel.write(fileName, DemoData.class).registerWriteHandler(loopMergeStrategy).sheet("Merge cells")
         .doWrite(data());
 }
Copy the code

  • It’s going to merge every two rows and we’re going to set eachColumn to 3 which is the length of our data, so it’s going to merge the first column. Of course, other merge strategies can be written yourself

  • So you specify which class you’re going to write to, and then you’re going to write to the first sheet, name it template and then the stream will automatically close

Web data writing

@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // Here URLEncoder. Encode can prevent Chinese garbled of course and easyExcel has no relationship
    String fileName = URLEncoder.encode("Data write"."UTF-8");
    response.setHeader("Content-disposition"."attachment; filename=" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("Template").doWrite(data());
}
Copy the code

Template Export

If you want a landscape fill, you just need to set up the template.

Simple Excel template

public class FillData {
    private String name;
    private double number;
    // getting setting
}
Copy the code
Implementing template fill
@Test
public void simpleFill(a) {
    String templateFileName = "simple.xlsx";

    // Option 1 is populated by objects
    String fileName = System.currentTimeMillis() + ".xlsx";
    // This will fill the first sheet and the stream will close automatically
    FillData fillData = new FillData();
    fillData.setName("Knowing spring and Autumn");
    fillData.setNumber(25);
    EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(fillData);

    // Plan 2 is populated according to Map
    fileName = System.currentTimeMillis() + ".xlsx";
    // This will fill the first sheet and the stream will close automatically
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("name"."Knowing spring and Autumn");
    map.put("number".25);
    EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);
}
Copy the code
  • Templates use {} to indicate which variable you want to use. If you already have “{“,”}” special characters, use “{“,”}” instead

Complex filling

Write data in batches using the List collection method. Dot indicates that the parameter is a collection

@Test
public void complexFill(a) {
    String templateFileName = "complex.xlsx";
    String fileName = System.currentTimeMillis() + ".xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    // If the data volume is large, the list is not the last line and refer to the next one
    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
    excelWriter.fill(data(), fillConfig, writeSheet);
    excelWriter.fill(data(), fillConfig, writeSheet);
    // Other parameters can be encapsulated using Map
    Map<String, Object> map = new HashMap<String, Object>();
    excelWriter.fill(map, writeSheet);
    excelWriter.finish();
}
Copy the code

  • Templates use {} to indicate which variable you want to use. If you already have “{“,”}” special characters, use “{“,”}” instead

  • {.} is a list variableCopy the code

  • // Note that forceNewRow is used to indicate that a row is created when a list is written, regardless of whether there are empty rows below the list, and then the data below is moved backwards. The default is false, and the next line is used directly, or created if not.

  • // If forceNewRow is set to true, it has the disadvantage of storing all data in memory, so use it with cautionCopy the code
  • // If your template has a list and the list is not the last row, you must set forceNewRow=true, but this will put all the data into memoryCopy the code

Summarize a utility class


public class ExcelUtil {
    /** * write an Excel file to the local * <br /> * add the type all@ExcelPropertyAll attributes of an annotation are written * *@paramFileName Do not end with *@paramSheetName sheet name *@paramData Indicates the written data *@paramClazz writes an object of type * for the data Class@param<T> Write the data type */
    public static <T> void writeExcel(String fileName, String sheetName, List<T> data, Class<T> clazz) {
        writeExcel(null, fileName, sheetName, data, clazz);
    }


    /** * Write an Excel ** with the specified property name@paramAttrName Specifies an attribute name that must be the same as the attribute name of the data type *@paramFileName Do not end with *@paramSheetName sheet name *@paramData Indicates the data to be written@paramClazz writes an object of type * for the data Class@param<T> The type of data to write */
    public static <T> void writeExcel(Set<String> attrName, String fileName, String sheetName, List<T> data, Class<T> clazz) {
        fileName = StringUtils.isBlank(fileName) ? Student Management System : fileName;
        sheetName = StringUtils.isBlank(sheetName) ? "sheet0" : sheetName;

        try(FileOutputStream fos = new FileOutputStream(fileName)) {
            write(fos,attrName,sheetName,data,clazz);
        } catch(Exception exception) { exception.printStackTrace(); }}/** * Read the excel document in the specified format **@paramFileName fileName *@paramClazz data type class object *@param<T> Data type *@return* /
    public static <T> List<T> readExcel(String fileName, Class<T> clazz) {
        
        return readExcel(fileName, clazz, null);
    }

    /** * Retrieve the excel document in the specified format ** Note that once the custom listener is passed in, the list returned is empty and the data needs to be retrieved from the custom listener **@paramFileName fileName *@paramClazz data type class object *@paramReadListener Custom listener *@param<T> Data type *@return* /
    public static <T> List<T> readExcel(String fileName, Class<T> clazz, ReadListener<T> readListener) {
  

        try(FileInputStream fis = new FileInputStream(fileName)) {
            return read(fis,clazz,readListener);
        } catch(Exception exception) { exception.printStackTrace(); }}/** * Export an Excel file * export all data in Excel file *@param response
     * @paramFileName should be an English fileName and do not have the suffix *@paramSheetName sheet name *@paramData Indicates the data to be written@paramClazz writes an object of type * for the data Class@param<T> The type of data to write */
    public static <T> void export(HttpServletResponse response, String fileName, String sheetName, List<T> data, Class<T> clazz) {
        export(response, null, fileName, sheetName, data, clazz);
    }

    /** * Write an Excel ** with the specified property name@param response
     * @paramAttrName Specifies an attribute name that must be the same as the attribute name of the data type *@paramFileName should be an English fileName without the suffix *@paramSheetName sheet name *@paramData Indicates the data to be written@paramClazz writes an object of type * for the data Class@param<T> The type of data to write */
    public static <T> void export(HttpServletResponse response, Set<String> attrName, String fileName, String sheetName, List<T> data, Class<T> clazz) {
      
        fileName = StringUtils.isBlank(fileName) ? "student-system-manager" : fileName;
        sheetName = StringUtils.isBlank(sheetName) ? "sheet0" : sheetName;

        response.setContentType("application/vnd.ms-excel; charset=utf-8");
        response.setCharacterEncoding("utf-8");
        response.addHeader("Content-disposition"."attachment; filename=" + fileName + ExcelTypeEnum.XLSX.getValue());

        try(OutputStream os = response.getOutputStream()) {
            write(os,attrName,sheetName,data,clazz);
        } catch(IOException e) { e.printStackTrace(); }}/** * Receive an Excel file and parse it * Note that once the custom listener is passed in, the list returned is empty and the data needs to be retrieved from the custom listener *@paramMultipartFile Excel file *@paramClazz data type class object *@paramReadListener listener *@param <T>
     * @return* /
    public static <T> List<T> importExcel(MultipartFile multipartFile,Class<T> clazz,ReadListener<T> readListener) {
  
  
        try(InputStream inputStream = multipartFile.getInputStream()) {
            return read(inputStream,clazz,readListener);
        } catch(IOException e) { e.printStackTrace(); }}private static <T> void write(OutputStream os, Set<String> attrName, String sheetName, List<T> data, Class<T> clazz) {
        ExcelWriterBuilder write = EasyExcel.write(os, clazz);
        If you do not specify which attribute data to write, write all
        if(! CollectionUtils.isEmpty(attrName)) { write.includeColumnFiledNames(attrName); } write.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName).doWrite(data);
    }


    private static <T> List<T> read(InputStream in,Class<T> clazz, ReadListener<T> readListener) {
        List<T> list = new ArrayList<>();

        Optional<ReadListener> optional = Optional.ofNullable(readListener);

        EasyExcel.read(in, clazz, optional.orElse(new AnalysisEventListener<T>() {

            @Override
            public void invoke(T data, AnalysisContext context) {
                list.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                  System.out.println("Parsing done")οΌ›
            }
        })).sheet().doRead();

        returnlist; }}Copy the code

The resources

  • Github.com/alibaba/eas… The official API
  • Github.com/alibaba/eas… Easyexcel making address
  • Blog.csdn.net/sinat_32366… Easyexcel summary
  • Alibaba – easyexcel. Making. IO/index. HTML official sample