Spring Boot integrated easypoi quick start Demo

1.what is the easypoi？

Easypoi function such as his name is easy，main feature is easy ，it is easy to let peple who never touch the poi can write the Excel export function ，Excel template export，Excel import，Word teamplate export，Through simple annotations and template language (familiar expression syntax), previously complex writing methods can be completed.

2.Code ProJect

experiment purpose：implement excel import and export

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>springboot-demo</artifactId>
        <groupId>com.et</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>
   <artifactId>eaypoi</artifactId>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>2.0.40</version>
        </dependency>
    </dependencies>
</project>

HelloWorldController.java

package com.et.easypoi.controller;
import com.alibaba.fastjson.JSONObject;
import com.et.easypoi.Util.FileUtil;
import com.et.easypoi.model.GoodType;
import com.et.easypoi.model.Goods;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
@RestController
public class HelloWorldController {
    @RequestMapping("/hello")
    public Map<String, Object> showHelloWorld(){
        Map<String, Object> map = new HashMap<>();
        map.put("msg", "HelloWorld");
        return map;
    }
    @RequestMapping("/exportExcel")
    public void export(HttpServletResponse response) throws Exception {
        //mock datas
        Goods goods1 = new Goods();
        List<GoodType>  goodTypeList1 = new ArrayList<>();
        GoodType goodType1  =  new GoodType();
        goodType1.setTypeId("apple-1");
        goodType1.setTypeName("apple-red");
        goodTypeList1.add(goodType1);
        GoodType goodType2  =  new GoodType();
        goodType2.setTypeId("apple-2");
        goodType2.setTypeName("apple-white");
        goodTypeList1.add(goodType2);
        goods1.setNo(110);
        goods1.setName("apple");
        goods1.setShelfLife(new Date());
        goods1.setGoodTypes(goodTypeList1);

        Goods goods2 = new Goods();
        List<GoodType>  goodTypeList2 = new ArrayList<>();
        GoodType goodType21  =  new GoodType();
        goodType21.setTypeId("wine-1");
        goodType21.setTypeName("wine-red");
        goodTypeList2.add(goodType21);
        GoodType goodType22  =  new GoodType();
        goodType22.setTypeId("wine-2");
        goodType22.setTypeName("wine-white");
        goodTypeList2.add(goodType22);
        goods2.setNo(111);
        goods2.setName("wine");
        goods2.setShelfLife(new Date());
        goods2.setGoodTypes(goodTypeList2);

        List<Goods> goodsList = new ArrayList<Goods>();
        goodsList.add(goods1);
        goodsList.add(goods2);

        for (Goods goods : goodsList) {
            System.out.println(goods);
        }
        //export
        FileUtil.exportExcel(goodsList, Goods.class,"product.xls",response);
    }
    @RequestMapping("/importExcel")
    public void importExcel() throws Exception {
        //loal file
        String filePath = "C:\\Users\\Dell\\Downloads\\product.xls";
        //anaysis excel
        List<Goods> goodsList = FileUtil.importExcel(filePath,0,1,Goods.class);
        //also use MultipartFile,invoke FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)
        System.out.println("load data count【"+goodsList.size()+"】row");
        //TODO save datas
        for (Goods goods:goodsList) {
            JSONObject.toJSONString(goods);
        }
    }
}

model

package com.et.easypoi.model;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
@Data
public class Goods implements Serializable {

    @Excel(name = "NO",needMerge = true ,width = 20)
    private Integer no;
    @Excel(name = "name",needMerge = true ,width = 20)
    private String name;
    @Excel(name = "shelfLife",width = 20,needMerge = true ,exportFormat = "yyyy-MM-dd")
    private Date shelfLife;
    @ExcelCollection(name = "goodTypes")
    private List<GoodType> goodTypes;
}

package com.et.easypoi.model;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
/**
 * @author liuhaihua
 * @version 1.0
 * @ClassName GoodType
 * @Description todo
 * @date 2024年04月15日 11:02
 */
@Data
public class GoodType {
    @Excel(name = "typeId",  width = 20,height = 8)
    private String typeId;
    @Excel(name = "typeName",  width = 20,height = 8)
    private String typeName;
}

FileUtil

General tool class, export and export encapsulation

package com.et.easypoi.Util;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.et.easypoi.service.ExcelExportStyler;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
public class FileUtil {
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws Exception {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName,  HttpServletResponse response) throws Exception {
        ExportParams exportParams = new ExportParams();
        exportParams.setStyle(ExcelExportStyler.class); // set style
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response) throws Exception {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
        defaultExport(list, fileName, response);
    }
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws Exception {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws Exception {
        OutputStream outputStream=null;
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
        } catch (IOException e) {
            throw new Exception(e.getMessage());
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }
    public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass) throws Exception {
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (NoSuchElementException e){
            throw new Exception("template not null");
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        }
        return list;
    }
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
        if (file == null){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            throw new Exception("excel file not null");
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return list;
    }
}

ExcelExportStyler

set header，cell styler

package com.et.easypoi.service;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;
public class ExcelExportStyler implements IExcelExportStyler {
    private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
    private static final short FONT_SIZE_TEN = 10;
    private static final short FONT_SIZE_ELEVEN = 11;
    private static final short FONT_SIZE_TWELVE = 12;
    /**
     * header style
     */
    private CellStyle headerStyle;
    /**
     * title style
     */
    private CellStyle titleStyle;
    /**
     * cell style
     */
    private CellStyle styles;
    public ExcelExportStyler(Workbook workbook) {
        this.init(workbook);
    }
    /**
     * init
     *
     * @param workbook
     */
    private void init(Workbook workbook) {
        this.headerStyle = initHeaderStyle(workbook);
        this.titleStyle = initTitleStyle(workbook);
    }
    @Override
    public CellStyle getHeaderStyle(short color) {
        return headerStyle;
    }

    @Override
    public CellStyle getTitleStyle(short color) {
        return titleStyle;
    }

    @Override
    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
        return styles;
    }

    @Override
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
        return getStyles(true, entity);
    }
    @Override
    public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
        return null;
    }
    /**
     * init --HeaderStyle
     *
     * @param workbook
     * @return
     */
    private CellStyle initHeaderStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
        return style;
    }
    /**
     * init-TitleStyle
     *
     * @param workbook
     * @return
     */
    private CellStyle initTitleStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
        // ForegroundColor
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }
    /**
     * BaseCellStyle
     *
     * @return
     */
    private CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        return style;
    }
    /**
     * Font
     *
     * @param size
     * @param isBold
     * @return
     */
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setBold(isBold);
        font.setFontHeightInPoints(size);
        return font;
    }
}

package com.et.easypoi.service;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import org.apache.poi.ss.usermodel.CellStyle;
public interface IExcelExportStyler {
    /**
     * heder style
     * @param headerColor
     * @return
     */
    public CellStyle getHeaderStyle(short headerColor);
    /**
     * title style
     * @param color
     * @return
     */
    public CellStyle getTitleStyle(short color);
    /**
     * getstyle
     * @param Parity
     * @param entity
     * @return
     */
    public CellStyle getStyles(boolean Parity, ExcelExportEntity entity);
}

the above are just some key codes, for all codes ,please see the code repository below

Code Reponsitory

• https://github.com/Harries/springboot-demo

3.Test

Start Spring Boot Application

Export

input http://127.0.0.1:8088/importExcel

import

input http://127.0.0.1:8088/importExcel

Goods(no=110, name=apple, shelfLife=Mon Apr 15 13:28:36 CST 2024, goodTypes=[GoodType(typeId=apple-1, typeName=apple-red), GoodType(typeId=apple-2, typeName=apple-white)])
Goods(no=111, name=wine, shelfLife=Mon Apr 15 13:28:36 CST 2024, goodTypes=[GoodType(typeId=wine-1, typeName=wine-red), GoodType(typeId=wine-2, typeName=wine-white)])
load data count【3】row

4.Referent

• http://www.ibloger.net/article/3391.html
• http://www.liuhaihua.cn/archives/710424.html
• http://doc.wupaas.com/docs/easypoi/easypoi-1c0u9a1bv66f4