Springboot整合EasyPOI操作Excel文件

配合官网阅读本文源码 POI官网文档

效果图：

---

源码已经上传到了 https://download.csdn.net/download/weixin_40516924/13996398 domain目录里面有两个类没有用，纯属是博主忘记删除了。

---

本文只做一个案列的操作：

pom依赖：

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--EasyPoi导入导出-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.3</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
        </dependency>

FileController 源码：

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 com.poi.domain.StudentEntity;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.*;

@RestController
public class FileController {

    /**
     * 下载Excel文件
     *
     * @param response
     * @throws Exception
     */
    @GetMapping("downloadFile")
    public void file(HttpServletResponse response) throws Exception {
    
        // 设置响应头
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("文件名.xls", "UTF-8"));
        response.setHeader("content-Type", "application/vnd.ms-excel");
        
        // 模拟数据 StudentEntity学生对象
        List<StudentEntity> list = new ArrayList<>();
        list.add(new StudentEntity("1", "zhansan", 1, new Date(), new Date()));
        list.add(new StudentEntity("2", "zhansan1", 1, new Date(), new Date()));
        list.add(new StudentEntity("3", "zhansan2", 1, new Date(), new Date()));
        list.add(new StudentEntity("4", "zhansan3", 1, new Date(), new Date()));

        // 将数据转换成 Workbook对象
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(),
                StudentEntity.class, list);

		/****************************可选（设置单元格下拉框）*********************************/
		List<String> dataList = new ArrayList<>();
        for(int i = 0; i < 10000; i++){
            dataList.add("xx省xx市区_" + i);
        }
        // 设置表格中 哪些单元格显示下拉框，以及下拉框的选项可以去查数据库 参数 workbook对象，开始行，结束行，开始列，结束列,下拉框内容的数据阵列  —— ExcelSelectListUtil类自定义下面有源码
        ExcelSelectListUtil.selectList(workbook, 1,100,0, 0, dataList.toArray(new String[dataList.size()]));
		/**********************************************************************************/
		
		/****************************可选（设置单元格样式）*********************************/
		// 1. 获取第几个sheet页 
		// Sheet sheet = workbook.getSheetAt(0);  
		
		// 2.定义单元格样式（CellStyle 表示一个样式，可以创建多个样式给不同的单元格设置不同的样式）
		// CellStyle cellStyle = workbook.createCellStyle();
		// cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充图案
		// cellStyle.setAlignment(HorizontalAlignment.CENTER); //设置对齐
		// cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐
		// cellStyle.setBorderBottom(BorderStyle.THIN); //设置边框底
		// cellStyle.setBorderLeft(BorderStyle.THIN);
		// cellStyle.setBorderTop(BorderStyle.THIN);
		// cellStyle.setBorderRight(BorderStyle.THIN);
		// cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()); //设置单元格背景颜色
		
		// 3.循环遍历每一列的单元格 ， 给单元格添加样式
		// int contentRow = 2; // 读取的行
		//  for (int i = 0; i < rowNumList.get(rowNum); i++) {
		// 		row = sheet.getRow(contentRow++);  // 获取第2行数据
		//  	totalCells = row.getPhysicalNumberOfCells();  // 获取当前行的列（有数据的列）
		//		for (int j = 0; j < totalCells; j++) {
        //           row.getCell(j).setCellStyle(cellStyle);
        //      }
		// }
		// 		
		/**********************************************************************************/

        // 将Workbook对象写入到响应头
        workbook.write(response.getOutputStream());
    }

    /**
     * 读取上传的Excel文件
     *
     * @param file
     * @return
     * @throws Exception
     */
    @PostMapping("/uploadFile")
    public Object upFIle(MultipartFile file) throws Exception {
        InputStream inputStream = file.getInputStream();
        ImportParams params = new ImportParams();
        // 因为Excel是从第2行开始才有正式数据,所以我们这里从第1行开始读取数据
        params.setHeadRows(1);
        // 读取 Excel文件的内容  StudentEntity学生对象
        List<StudentEntity> StudentEntityList = ExcelImportUtil.importExcel(inputStream, StudentEntity.class, params);
        return null;
    }

}

学生对象StudentEntity ：

@Excel 注解有很多属性，常用的还有 mergeVertical 该参数描述是否需要纵向合并内容相同的单元格操作 等等。

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.util.Date;

@Data
public class StudentEntity {
    /**
     * id
     */
    private String        id;
    /**
     * 学生姓名
     */
    @Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true_st" ,mergeVertical=true)
    private String        name;
    /**
     * 学生性别
     */
    @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生", isImportField = "true_st")
    private int           sex;

    @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd HH:mm:ss", isImportField = "true_st", width = 20)
    private Date birthday;

    @Excel(name = "进校日期", width = 100 , databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd HH:mm:ss")
    private Date registrationDate;

    public StudentEntity(String id, String name, int sex, Date birthday, Date registrationDate) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.birthday = birthday;
        this.registrationDate = registrationDate;
    }

    public StudentEntity() {
    }
}

ExcelSelectListUtil类（设置哪些单元格有下拉框和下拉框的内容）

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

public final class ExcelSelectListUtil {

    public static void selectList(Workbook workbook, int firstRow, int lastRow, int firstCol, int lastCol, String[] dataArray){
        Sheet sheet = workbook.getSheetAt(0);
        Sheet hidden = workbook.createSheet("hidden");
        Cell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++)
        {
            String name = dataArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = workbook.createName();
        namedCell.setNameName("hidden");
        namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");

        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);

        //将第二个sheet设置为隐藏
        workbook.setSheetHidden(1, true);
        if (null != validation)
        {
            sheet.addValidationData(validation);
        }
    }

}