The article directories

• preface
• steps
• • Lead the pom
  • To build the Model
  • Build Excel utility classes (for importing Excel)
  • Export Excel
  • Import Excel

preface

Java parsing, Excel generation frameworks are well known Apache POI, JXL. Poi has a SET of SAX mode API, which can solve some problems of memory overflow to a certain extent. However, POI still has some defects. For example, the decompression and storage after decompression of 07 Version Excel are completed in memory, which still consumes a lot of memory. Easyexcel rewrites poI on 07 version of Excel parsing, can originally a 3M Excel with POI SAX still need about 100M memory reduced to a few M, and no matter how large Excel memory overflow, 03 version depends on POI SAX mode. (It’s an open source project written by Ali.)

• Making address: Github.com/alibaba/eas…
  
  

steps

Lead the pom

	<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.23.</version>
    </dependency>
Copy the code

To build the Model

@contentrowheight (20), @headrowheight (20), @columnWidth (15), @excelProperty (value = “course code”, index = 0) Value Corresponding title name 】

@EqualsAndHashCode(callSuper = true)
@Data
@ContentRowHeight(20)
@HeadRowHeight(20)
@ColumnWidth(15)
public class CourseExportTemplate extends BaseRowModel {

    /** * course code */
    @excelProperty (value = "course code ", index = 0)
    private String code ;

    /** * Course name */
    @excelProperty (value = "course name ", index = 1)
    private String name;

    /** * Course category */
    @excelProperty (value = "class ", index = 2)
    private String courseCategory;


    /** * Course type */
    @excelProperty (value = "course type ", index = 3)
    private String courseType;


    /** * Course nature */
    @excelProperty (value = "course property ", index = 4)
    private String courseNature;


    /** * college */
    @excelProperty (value = "school ", index = 5)
    private String academyName;

    /** ** ** */
    @excelProperty (value = "class ", index = 6)
    private String classHour;

    /** ** ** */
    @excelProperty (value = "credit ", index = 7)
    private String classPoint;

    /** * course score */
    @excelProperty (value = "course score ", index = 8)
    private String score;

    /** * error cause */
    @excelProperty (value = "error ", index = 9)
    private String failReason;
}
Copy the code

Build Excel utility classes (for importing Excel)

@Slf4j
public class ExcelListener extends AnalysisEventListener {

    private List<Object> datas = new ArrayList<>();

    @Override
    public void invoke(Object object, AnalysisContext analysisContext) {
        log.info("Parse to a single data :{}", JSON.toJSONString(object));
        datas.add(object);
    }

    /** * when all data is parsed, call **@param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("All data parsed!");
    }

    public List<Object> getDatas(a){
        return datas;
    }

    public void setDatas(List<Object> datas){
        this.datas = datas; }}Copy the code

Export Excel

public boolean downLoadTemplate(HttpServletResponse response) {
        CourseExportTemplate courseExportTemplate = new CourseExportTemplate();
        courseExportTemplate.setAcademyName(academyName);
        courseExportTemplate.setClassHour(classHour);
        courseExportTemplate.setClassPoint(classPoint);
        courseExportTemplate.setCode(code);
        courseExportTemplate.setCourseCategory(courseCategory);
        courseExportTemplate.setCourseNature(courseNature);
        courseExportTemplate.setCourseType(courseType);
        courseExportTemplate.setName(name);
        courseExportTemplate.setScore(score);
        List<CourseExportTemplate> list = new ArrayList<>();
        list.add(courseExportTemplate);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try {
            // URLEncoder. Encode can prevent Chinese gibberish
            String cFileName = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-disposition"."attachment; filename=" + cFileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), CourseExportTemplate.class).sheet().doWrite(list);
        } catch (IOException e) {
            log.error("Excel download failed: error message: {}", e.toString());
            return false;
        }
        return true;
    }
Copy the code

Import Excel

public String importCourse(MultipartFile file) {
        // Get the file name of the imported Excel file
        String fileName = file.getOriginalFilename();
        if (StringUtils.isBlank(fileName)) {
            log.error("Import failed, file name empty");
        }
        ExcelListener listener = new ExcelListener();
        try {
            EasyExcel.read(file.getInputStream(), CourseExportTemplate.class, listener).sheet().doRead();
        } catch (IOException e) {
            log.error("Excel write failure");
            e.printStackTrace();
        }
        // Get parsed course data
        List<CourseExportTemplate> courseList = JSON.parseArray(JSON.toJSONString(listener.getDatas()), CourseExportTemplate.class);
        // The general Mapper method is called to store the database
        //... Can add some checks...
        TtCourse ttCourse = new TtCourse();
        BeanUtils.copyProperties(course, ttCourse);
        / / inventory
        courseDao.insert(ttCourse);
        return "Import successful";
    }
Copy the code