[EasyExcel tutorial] detailed explanation reading Excel -- Reading

May you be like sunshine, bright and not sad.

1. Easyexcel & YuQue

EasyExcel is an open source excel processing framework of Alibaba, which is famous for its simplicity and memory saving. This technology rewrites POI's analysis of 07 version excel, reduces the memory from 100 megabytes to KB level, and no memory overflow will occur in any larger excel. It solves the memory consumption problem of POI technology, and provides better API use. The operation function of Excel can be realized without a lot of code. The model conversion is encapsulated in the upper layer, which makes the user more simple and convenient.

yuque is a document and knowledge management tool incubated by Alipay. yuque uses "structured knowledge base management", which is similar to the catalogue of books in form. Unlike other products, which can create documents at will, each document on yuque must belong to a knowledge base. yuque hopes to help users establish knowledge management awareness and cultivate good knowledge management habits from the source through such product design.

Learn more → EasyExcel language bird

Learn more → EasyExcel github

2. Feel quickly

2.1 reading Excel

/**
 * Simple reading excel file
 */
public void read() {
    String fileName = "demo.xlsx";
    // Here, you need to specify which class to read, and then read the first sheet file. The stream will close automatically
    // Parameter 1: read excel file path
    // Parameter 2: read a row of sheet and encapsulate the parameters in DemoData entity class
    // Parameter 3: when reading each row, the DemoDataListener listener will be executed
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

2.2 writing Excel

/**
 * Simply write data to excel
 */
public void simpleWrite() {
    String fileName = "demo.xlsx";
    // Here, you need to specify which class to read, and then write to the first sheet with the name of template, and then the file stream will be closed automatically
    // If you want to use 03 here, you can pass in the excel type parameter
    // Parameter 1: write excel file path
    // Parameter 2: the data type written is DemoData
    // The data() method is the written data, and the result is the list < demodata > set
    EasyExcel.write(fileName, DemoData.class).sheet("Template").doWrite(data());
}

2.3 Web upload and download

/**
 * excel File download
 */
@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());
}

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

3. Read Excel in detail

Dependency needs to be introduced before use. The latest stable version is 2.2.11

pom.xml

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.11</version>
</dependency>

3.1 simple reading Excel

EasyExcelData.xlsx

EasyExcelData.java
Create an entity class corresponding to the Excel title

public class EasyExcelData {
    private String stringData;
    private Date dateData;
    private Double doubleData;
    // Omit get and set
}

EasyExcelDataListener.java
Listener: there is a very important point that easyexcelddatalistener cannot be managed by spring. Each time you read excel, you need new. If spring is used, please use the parameterized construction method to pass in the class to be managed.

@Component
public class EasyExcelDataListener extends AnalysisEventListener<EasyExcelData> {

    // Used to operate every piece of data in Excel
    List<EasyExcelData> list = new ArrayList<EasyExcelData>();

    public EasyExcelDataListener() {
    }

    /**
     * If spring is used, use this constructor. Each time you create a Listener, you need to pass in the spring managed classes
     */
	//    public EasyExcelDataListener(EasyExcelDataDao easyExcelDataDao) {
	//        this.easyExcelDataDao = easyExcelDataDao;
	//    }

    /**
     * Called when parsing each piece of data
     *
     * @param easyExcelData
     * @param analysisContext
     */
    @Override
    public void invoke(EasyExcelData easyExcelData, AnalysisContext analysisContext) {
        System.out.println("Parse to a piece of data:" + JSON.toJSONString(easyExcelData));
        list.add(easyExcelData);
    }

    /**
     * It will be called after all data parsing is completed
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("All data analysis completed!" + JSON.toJSONString(list));
    }

    public List<EasyExcelData> getList() {
        return list;
    }
}

EasyExcelDataController.java
There are two ways to read Excel

@RestController
@RequestMapping("/easyExcel")
public class EasyExcelDataController extends BaseController {
    @Resource
    EasyExcelDataListener easyExcelDataListener;
    // Specify the excel file to be read
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";

    @RequestMapping("/read")
    public JsonResult<List<EasyExcelData>> simpleRead(int mode) {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));

        if (mode == 1) {
            // Writing method 1 (default first Sheet):
            // Here, you need to specify which class to use to read. After reading the first sheet file stream, it will be closed automatically
            EasyExcel.read(fileName, EasyExcelData.class, easyExcelDataListener).sheet().doRead();
        } else {
            // One file, one reader
            ExcelReader excelReader = null;
            // Write 2 (specify to read Sheet):
            try {
                excelReader = EasyExcel.read(fileName, EasyExcelData.class, easyExcelDataListener).build();
                // Build a sheet, where you can specify a name or No
                ReadSheet readSheet = EasyExcel.readSheet(0).build();
                // Read a sheet
                excelReader.read(readSheet);
            } finally {
                if (excelReader != null) {
                    // Don't forget to close it here. Temporary files will be created when reading, and the disk will collapse
                    excelReader.finish();
                }
            }
        }
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult<>(easyExcelDataListener.getList(), "read Excel Data success!");
    }
}

Browser access http://localhost:8080/easyExcel/read?mode=2

3.2 subscript or column name of specified column

EasyExcelDataIndexOrName.java
Modify the corresponding entity class

public class EasyExcelDataIndexOrName {
    /**
     * The third one is forced to read. It is not recommended to use index and name at the same time. Either an object only uses index or an object only uses name to match
     */
    @ExcelProperty(index = 2)
    private Double doubleData;
    /**
     * To match by name, it should be noted that if the name is repeated, only one field will read the data
     */
    @ExcelProperty("String title")
    private String string;
    @ExcelProperty("Date title")
    private Date date;
    // Omit get and set
}

EasyExcelDataReadController.java
The listener is only generic. Please refer to the listener above. The specific code will not be shown.

@RestController
@RequestMapping("/easyExcelRead")
public class EasyExcelDataReadController extends BaseController {
    @Resource
    EasyExcelDataReadListener easyExcelDataReadListener;
    // Specify the excel file to be read
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";

    @RequestMapping("/indexOrName")
    public JsonResult<List<EasyExcelDataIndexOrName>> indexOrNameRead() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.read(fileName, EasyExcelDataIndexOrName.class, easyExcelDataReadListener).sheet().doRead();
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult<>(easyExcelDataReadListener.getList(), "read Excel Data success! (specify the subscript or column name of the column)");
    }
}

Browser access http://localhost:8080/easyExcelRead/indexOrName

3.3 reading multiple sheet s

EasyExcelDataReadController.java
The listener is only generic. Please refer to the listener above. The specific code will not be shown.

@RestController
@RequestMapping("/easyExcelRead")
public class EasyExcelDataReadController extends BaseController {
    @Resource
    EasyExcelDataReadListener easyExcelDataReadListener;
    // Specify the excel file to be read
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";

    @RequestMapping("/multiSheets")
    public JsonResult<List<EasyExcelDataIndexOrName>> multiSheets(String model) {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        if ("all".equals(model)) {
            // Read all sheet s
            // It is important to note that the doAfterAllAnalysed of EasyExcelDataReadListener will be called once every sheet is read. Then all sheets will be written into the same easyexcelddatareadlistener
            EasyExcel.read(fileName, EasyExcelDataIndexOrName.class, easyExcelDataReadListener).doReadAll();
        } else {
            // Read Partial sheet
            ExcelReader excelReader = null;
            try {
                excelReader = EasyExcel.read(fileName).build();

                // The same head and Listener are registered here for simplicity
                ReadSheet readSheet1 =
                        EasyExcel.readSheet(0).head(EasyExcelDataIndexOrName.class).registerReadListener(easyExcelDataReadListener).build();
                ReadSheet readSheet2 =
                        EasyExcel.readSheet(1).head(EasyExcelDataIndexOrName.class).registerReadListener(easyExcelDataReadListener).build();
                // Note here that Sheet1 and SHEET2 must be transmitted together, otherwise there is a problem that version 03 excel will be read many times, wasting performance
                excelReader.read(readSheet1, readSheet2);
            } finally {
                if (excelReader != null) {
                    // Don't forget to close it here. Temporary files will be created when reading, and the disk will collapse
                    excelReader.finish();
                }
            }
        }
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult<>(easyExcelDataReadListener.getList(), "read Excel Data success! (read more than one) sheet)");
    }
}

Browser access http://localhost:8080/easyExcelRead/multiSheets

3.4 date, number or custom format conversion

EasyExcelDataConverter.java
To create a corresponding entity class, you need to specify a custom format converter

public class EasyExcelDataConverter {
    /**
     * Custom converter
     */
    @ExcelProperty(converter = CustomStringStringConverter.class)
    private String string;
    /**
     * The date can only be formatted by using string
     */
    @DateTimeFormat("yyyy year MM month dd day HH Time mm branch ss second")
    private String date;
    /**
     * Number of percent received
     */
    @NumberFormat("#.##%")
    private String doubleData;
    // Omit get and set
}

CustomStringStringConverter.java
To customize the Converter, you need to implement the Converter interface

public class CustomStringStringConverter implements Converter<String> {
    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

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

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

    /**
     * This is called when writing. Don't worry
     *
     * @param value               NotNull
     * @param contentProperty     Nullable
     * @param globalConfiguration NotNull
     * @return
     */
    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
                                       GlobalConfiguration globalConfiguration) {
        return new CellData(value);
    }
}

EasyExcelDataReadController.java
The listener is only generic. Please refer to the listener above. The specific code will not be shown.

@RestController
@RequestMapping("/easyExcelRead")
public class EasyExcelDataReadController extends BaseController {
    @Resource
    EasyExcelDataReadListener easyExcelDataReadListener;
    @Resource
    EasyExcelDataConverterListener easyExcelDataConverterListener;
    // Specify the excel file to be read
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    
    @RequestMapping("/converterRead")
    public JsonResult<List<EasyExcelDataConverter>> converterRead() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        // Note here that we can also use registerConverter to specify a custom converter (. registerConverter (New customstringconverter())), but this conversion becomes global. All java strings and excel strings will use this converter.
        // If you want to use a single field, specify the converter with @ ExcelProperty
        EasyExcel.read(fileName, EasyExcelDataConverter.class, easyExcelDataConverterListener).sheet().doRead();
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult<>(easyExcelDataConverterListener.getList(), "read Excel Data successfully! (custom format conversion)");
    }
}

Browser access http://localhost:8080/easyExcelRead/converterRead

3.5 multi line headers

EasyExcelDataReadController.java
The listener is only generic. Please refer to the listener above. The specific code will not be shown.

@RestController
@RequestMapping("/easyExcelRead")
public class EasyExcelDataReadController extends BaseController {
    @Resource
    EasyExcelDataReadListener easyExcelDataReadListener;
    @Resource
    EasyExcelDataConverterListener easyExcelDataConverterListener;
    // Specify the excel file to be read
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    
    @RequestMapping("/multiHeaders")
    public JsonResult<List<EasyExcelDataConverter>> multiHeaders() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        // You can set 1 here because the header is one line. If there are multiple line headers, you can set other values. You can also parse it without passing it in. Easyexcelddataconverter does not specify a header, that is, the default 1 line
        EasyExcel.read(fileName, EasyExcelDataConverter.class, easyExcelDataConverterListener).sheet().headRowNumber(1).doRead();
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult<>(easyExcelDataConverterListener.getList(), "read Excel Data success! (multiple line headers)");
    }
}

Browser access http://localhost:8080/easyExcelRead/multiHeaders

3.6 reading header data

EasyExcelDataReadController.java
There is one more method in the listener. Just rewrite the invokeHeadMap method.

@Override
public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
    System.out.println("Parse to a header:" + JSON.toJSONString(headMap));
}

EasyExcelDataReadController.java

@RestController
@RequestMapping("/easyExcelRead")
public class EasyExcelDataReadController extends BaseController {
    @Resource
    EasyExcelDataReadListener easyExcelDataReadListener;
    @Resource
    EasyExcelDataConverterListener easyExcelDataConverterListener;
    // Specify the excel file to be read
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    
    @RequestMapping("/headerRead")
    public JsonResult<Map<Integer, CellData>> headerRead() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.read(fileName, EasyExcelDataConverter.class, easyExcelDataConverterListener).sheet().doRead();
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult<>(easyExcelDataConverterListener.getHeadMap(), "read Excel Data success! (reading header data)");
    }
}

Browser access http://localhost:8080/easyExcelRead/headerRead

3.7 additional information (comments, hyperlinks, merged cell information)

EasyExcelData.xlsx
Add hyperlinks, comments, merged cells and other contents in Excel.

EasyExcelExtraData.java
Create corresponding entity class

public class EasyExcelExtraData {
    private String column1;

    private String column2;
    // Omit get and set
}

EasyExcelDataExtraListener.java
Listener: there is an extra method in it

@Component
public class EasyExcelDataExtraListener extends AnalysisEventListener<EasyExcelExtraData> {

    // Additional data used to manipulate Excel
    List<EasyExcelExtraData> list = new ArrayList<>();
    // Used to display details of additional data in Excel
    List<String> listExtra = new ArrayList<>();

    public EasyExcelDataExtraListener() {
    }

    /**
     * If spring is used, use this constructor. Each time you create a Listener, you need to pass in the spring managed classes
     */
//    public EasyExcelDataListener(EasyExcelDataDao easyExcelDataDao) {
//        this.easyExcelDataDao = easyExcelDataDao;
//    }

    /**
     * Called when parsing each piece of data
     *
     * @param easyExcelData
     * @param analysisContext
     */
    @Override
    public void invoke(EasyExcelExtraData easyExcelData, AnalysisContext analysisContext) {
        System.out.println("Parse to a piece of data:" + JSON.toJSONString(easyExcelData));
        list.add(easyExcelData);
    }

    /**
     * It will be called after all data parsing is completed
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("All data analysis completed!" + JSON.toJSONString(list));
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        listExtra.add("An additional message was read:" + JSON.toJSONString(extra));
        System.out.println("An additional message was read:" + JSON.toJSONString(extra));
        switch (extra.getType()) {
            case COMMENT:
                listExtra.add("Additional information is annotation, in rowIndex: " + extra.getRowIndex() + ",columnIndex: " + extra.getColumnIndex() +
                        "The content is:" + extra.getText());
                System.out.println("Additional information is annotation, in rowIndex: " + extra.getRowIndex() + ",columnIndex: " + extra.getColumnIndex() +
                        "The content is:" + extra.getText());
                break;
            case HYPERLINK:
                if ("Sheet1!A1".equals(extra.getText())) {
                    listExtra.add("Additional information is hyperlinks in rowIndex: " + extra.getRowIndex() + ",columnIndex: " + extra.getColumnIndex() +
                            "The content is:" + extra.getText());
                    System.out.println("Additional information is hyperlinks in rowIndex: " + extra.getRowIndex() + ",columnIndex: " + extra.getColumnIndex() +
                            "The content is:" + extra.getText());
                } else if ("Sheet2!A1:B2".equals(extra.getText())) {
                    listExtra.add("The additional information is hyperlinked and covers an interval in firstRowIndex: " + extra.getFirstRowIndex() + ",firstColumnIndex: "
                            + extra.getFirstColumnIndex() + ",lastRowIndex: " + extra.getLastRowIndex() + ",lastColumnIndex: "
                            + extra.getLastColumnIndex() + ",The content is:" + extra.getText());
                    System.out.println(
                            "The additional information is hyperlinked and covers an interval in firstRowIndex: " + extra.getFirstRowIndex() + ",firstColumnIndex: "
                                    + extra.getFirstColumnIndex() + ",lastRowIndex: " + extra.getLastRowIndex() + ",lastColumnIndex: "
                                    + extra.getLastColumnIndex() + ",The content is:" + extra.getText());
                } else {
                    listExtra.add("Unknown hyperlink!");
                    System.out.println("Unknown hyperlink!");
                }
                break;
            case MERGE:
                listExtra.add("The additional information is to merge cells and cover an interval in firstRowIndex: " + extra.getFirstRowIndex() + ",firstColumnIndex: "
                        + extra.getFirstColumnIndex() + ",lastRowIndex: " + extra.getLastRowIndex() + ",lastColumnIndex: "
                        + extra.getLastColumnIndex());
                System.out.println(
                        "The additional information is to merge cells and cover an interval in firstRowIndex: " + extra.getFirstRowIndex() + ",firstColumnIndex: "
                                + extra.getFirstColumnIndex() + ",lastRowIndex: " + extra.getLastRowIndex() + ",lastColumnIndex: "
                                + extra.getLastColumnIndex());
                break;
            default:
        }

    }

    public List<String> getExtraInfo() {
        listExtra.add(JSON.toJSONString(list));
        return listExtra;
    }

}

EasyExcelDataReadController.java

@RestController
@RequestMapping("/easyExcelRead")
public class EasyExcelDataReadController extends BaseController {
    @Resource
    EasyExcelDataReadListener easyExcelDataReadListener;
    @Resource
    EasyExcelDataConverterListener easyExcelDataConverterListener;
    @Resource
    EasyExcelDataExtraListener easyExcelDataExtraListener;
    // Specify the excel file to be read
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    
    @RequestMapping("/extraRead")
    public JsonResult<List<String>> extraRead() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.read(fileName, EasyExcelExtraData.class, easyExcelDataExtraListener)
                // Annotations that need to be read are not read by default
                .extraRead(CellExtraTypeEnum.COMMENT)
                // The hyperlink needs to be read. It is not read by default
                .extraRead(CellExtraTypeEnum.HYPERLINK)
                // The merged cell information needs to be read. It is not read by default
                .extraRead(CellExtraTypeEnum.MERGE).sheet().doRead();
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult<>(easyExcelDataExtraListener.getExtraInfo(), "read Excel Data success! (additional information)");
    }
}

Browser access http://localhost:8080/easyExcelRead/extraRead

3.8 reading formulas and cell types

EasyExcelData.xlsx
Add formulas and other contents in Excel.

EasyExcelExtraData.java
Create corresponding entity class

public class EasyExcelCellData {
    private CellData<String> string;
    private CellData<Date> date;
    private CellData<Double> doubleData;
    private CellData<String> formulaValue;

    // Omit get and set
}

EasyExcelDataReadController.java
The listener is only generic. Please refer to the listener above. The specific code will not be shown.

@RestController
@RequestMapping("/easyExcelRead")
public class EasyExcelDataReadController extends BaseController {
    @Resource
    EasyExcelCellDataListener easyExcelCellDataListener;
    // Specify the excel file to be read
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
 
    @RequestMapping("/cellDataRead")
    public JsonResult<List<EasyExcelCellData>> cellDataRead() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.read(fileName, EasyExcelCellData.class, easyExcelCellDataListener).sheet().doRead();
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult<>(easyExcelCellDataListener.getList(), "read Excel Data success! (read formula and cell type)");
    } 
}

Browser access http://localhost:8080/easyExcelRead/cellDataRead

3.9 exception handling such as data conversion

EasyExcelExtraData.java
Create wrong entity class (data type mismatch)

public class ExceptionEasyExcelData {
	// The first column of data is of string type, but the date type is specified
    private Date date;

    // Omit get and set
}

ExceptionEasyExcelDataListener.java
Listener: there is one more method in it. Just override the onException method

@Component
public class ExceptionEasyExcelDataListener extends AnalysisEventListener<ExceptionEasyExcelData> {

    // Used to operate every piece of data in Excel
    List<ExceptionEasyExcelData> list = new ArrayList<>();
    StringBuffer sb = new StringBuffer();
    /**
     * This interface will be called when the conversion exception gets other exceptions. Stop reading if an exception is thrown. If no exception is thrown here, continue to read the next line.
     *
     * @param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        System.out.println("Parsing failed, but continue parsing the next line:" + exception.getMessage());
        // If it is a conversion exception of a cell, the specific line number can be obtained
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            System.err.println("The first" + excelDataConvertException.getRowIndex() + "OK, No" + excelDataConvertException.getColumnIndex()
                    + "Column parsing exception");
            sb.append("Parsing failed, but continue parsing the next line:").append(exception.getMessage()).append("The first").append(excelDataConvertException.getRowIndex()).append("OK, No").append(excelDataConvertException.getColumnIndex()).append("Column parsing exception");
        }
    }
}

EasyExcelDataReadController.java

@RestController
@RequestMapping("/easyExcelRead")
public class EasyExcelDataReadController extends BaseController {
    @Resource
    ExceptionEasyExcelDataListener exceptionEasyExcelDataListener;
    // Specify the excel file to be read
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
 
    @RequestMapping("/exceptionRead")
    public JsonResult<StringBuffer> exceptionRead() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.read(fileName, ExceptionEasyExcelData.class, exceptionEasyExcelDataListener).sheet().doRead();
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult<>(exceptionEasyExcelDataListener.getExceptionInfo(), "read Excel Data success! (exception handling such as data conversion)");
    }
}

Browser access http://localhost:8080/easyExcelRead/exceptionRead

3.10 reading in Web

EasyExcelDataReadController.java
The listener is only generic. Please refer to the listener above. The specific code will not be shown.

@RestController
@RequestMapping("/easyExcelRead")
public class EasyExcelDataReadController extends BaseController {
    @Resource
    EasyExcelDataMultipartFileListener easyExcelDataMultipartFileListener;
    // Specify the excel file to be read
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
 
    @PostMapping("/multipartFileRead")
    @ResponseBody
    public JsonResult<List<EasyExcelDataMultipartFile>>  multipartFileRead(MultipartFile file) throws IOException {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.read(file.getInputStream(), EasyExcelDataMultipartFile.class, easyExcelDataMultipartFileListener).sheet().doRead();
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult<>(easyExcelDataMultipartFileListener.getList(), "read Excel Data success! ( web (read in)");
    }

Posted by Tibster on Mon, 11 Oct 2021 20:24:04 -0700

Programmer Group