Spring Boot集成easypoi快速入门Demo

1.什么是easypoi？

2.代码工程

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

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

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);
}

以上只是一些关键代码，所有代码请参见下面代码仓库

代码仓库

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

3.测试

导出

导入

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.引用

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