Java will convert Excel to list set or JSON, export excel file to local, excel import and export, easyexcel tool class

Introducing Maven coordinates

        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>        
        <!--easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.8</version>
        </dependency>

According to the file path, the excel file is parsed into a list

    /**
     * Parsing Excel files into Lists
     *
     * @param pathName file path (e.g. D:\test\xzh.xlsx)
     * @param head Table header
     * @param <T> generic
     * @return
     */
    public static <T> List<T> upload(String pathName, Class<T> head) {
        List<T> list = new ArrayList<>();
        AnalysisEventListener<T> analysisEventListener = new AnalysisEventListener<T>() {
            // This will be called for every data parsing
            @Override
            public void invoke(T data, AnalysisContext context) {
                log.info("Parsing a piece of data:{}", JSON.toJSONString(data));
                list.add(data);
                // TODO Here you can also manipulate the data
            }

            // All the data parsing is done and will be called
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("All data parsing is complete!") ;
                // TODO Here you can also manipulate the data
            }
        };
        EasyExcel.read(pathName, head, analysisEventListener).sheet().doRead();
        return list;
    }

According to the file stream, the excel file is parsed into a list

    /**
     * Parsing Excel files into Lists
     *
     * @param file File
     * @param head table header
     * @param <T> generic
     * @return
     */
    public static <T> List<T> upload(MultipartFile file, Class<T> head) {
        List<T> list = new ArrayList<>();
        AnalysisEventListener<T> analysisEventListener = new AnalysisEventListener<T>() {
            // This will be called every time the data is parsed
            @Override
            public void invoke(T data, AnalysisContext context) {
                log.info("Parsed a piece of data: {}", JSON.toJSONString(data));
                list.add(data);
                // TODO You can also manipulate the data here
            }

            // All the data parsing is done and it will call
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("All data parsing is complete!") ;
                // TODO Here you can also manipulate the data
            }
        };
        InputStream inputStream;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            log.error("upload-InputStream-Exception:", e);
            return null;
        }
        EasyExcel.read(inputStream, head, analysisEventListener).sheet().doRead();
        return list;
    }

Download excel file to file path

    /**
     * Excel file download
     *
     * @param pathName File path
     * @param sheetName Worksheet name
     * @param data data
     * @param head Table header
     * @param <T> generic
     */
    public static <T> void download(String pathName, String sheetName, List<T> data, Class<T> head) {
        EasyExcel.write(pathName, head).sheet(sheetName).doWrite(data);
    }

Excel file download to file path response body response

    /**
     * Excel file download
     *
     * @param response response body
     * @param excelName File name
     * @param sheetName Worksheet name
     * @param data data
     * @param head table header
     * @param <T> generic
     */
    public static <T> void download(HttpServletResponse response, String excelName, String sheetName, List<T> data, Class<T> head) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        try {
            String fileName = URLEncoder.encode(excelName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), head).sheet(sheetName).doWrite(data);
        } catch (IOException e) {
            log.error("download-Exception:", e);
        }
    }

GitHub: https://github.com/xxiangzh/xzh-excel/tree/easyexcel


Read More: