Java POI 导入/导出 Excel

引入依赖

# EasyExcel 也引用了 POI，所以导入这个包 EasyExcel 和 Poi 都可以使用
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.3</version>
</dependency>

POI 导入

public void import(MultipartFile file) {
		// 初始化工作簿
		Workbook workbook = null;
		
		try {
		    if (file.getName().contains("xlsx")) {
		        workbook = new XSSFWorkbook(file.getInputStream());
		    } else {
		        workbook = new HSSFWorkbook(file.getInputStream());
		    }
		
		    if (CheckUtil.objIsEmpty(workbook)) {
		        throw ServiceException.create("导入文件异常！无法进行解析");
		    }
		} catch (IOException e) {
		    throw ServiceException.create("导入文件异常！无法进行解析");
		}
		
		// 获取 Sheet
		Sheet sheet = workbook.getSheetAt(i);
		for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum ++) {
				// 获取行
				Row row = sheet.getRow(rowNum);
				for (int j = 0;j < row.getLastCellNum();j++) {
						// 获取单元格文本
						String collValue = (objIsNotEmpty(row.getCell(j)) ? row.getCell(j).getStringCellValue() : null);
				}
		}
}

public boolean objIsNotEmpty(Object... objs) {
    if (objs == null || objs.length == 0) {
        return false;
    }

    for (Object obj : objs) {
        if (Objects.isNull(obj)) {
            return false;
        } else if (obj instanceof String && strIsEmpty(obj.toString())) {
            return false;
        } else if (obj instanceof Collection && collectionIsEmpty((Collection) obj)) {
            return false;
        } else if (obj instanceof Map && mapIsEmpty((Map) obj)) {
            return false;
        }
    }

    return true;
}

POI导出（下拉框）

public void export(List<LbClass> lbClasses, List<LbClassField> lbClassFields, HttpServletResponse response) {
    // 组装数据导出
    HSSFWorkbook wb = new HSSFWorkbook();

    for (int i = 0; i < lbClasses.size(); i++) {
        // 每一级的分类都需要创建一个 Sheet 页
        LbClass cls = lbClasses.get(i);

        HSSFSheet sheet = null;

        try {
            sheet = wb.createSheet(cls.getClassName());
        } catch (IllegalArgumentException e) {
            continue;
        }

        if (CheckUtil.objIsEmpty(sheet)) {
            continue;
        }

        // 创建行
        HSSFRow row = sheet.createRow(7);

        // 设置行样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        cell = row.createCell(0);
        cell.setCellValue("正常内容");
        cell.setCellStyle(cellStyle);

        cell = row.createCell(1);
        cell.setCellValue("下拉框");
        cell.setCellStyle(cellStyle);

        Map<String, String> boxMap = new HashMap<>();
        boxMap.put("下拉框", "选项1,选项2");
        HSSFDataValidation dataValidation = createBox("下拉框", boxMap , 0 , 1 , 0 , 0);
        if(dataValidation != null) {
            sheet.addValidationData(dataValidation);
        }
        
        sheet.autoSizeColumn(0);
    }

    OutputStream outputStream = null;

    response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);

    String fileName = "文件名";

    try {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1") + ".xls");
    } catch (UnsupportedEncodingException e) {
        throw ServiceException.create("生成模板时遇到问题，请稍后重试！");
    }

    try {
        outputStream = response.getOutputStream();
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
    } catch (IOException e) {
        throw ServiceException.create("生成模板时遇到问题，请稍后重试！");
    }
}

/**
 *  excel导出，有码值的数据使用下拉框展示
 * @param col             列名
 * @param boxMap          码值集合
 * @param firstRow        插入下拉框开始行号
 * @param lastRow         插入下拉框结束行号
 * @param firstCol        插入下拉框开始列号
 * @param lastCol         插入下拉框结束行号
 *
 *
 * Map<String, String> boxMap = new HashMap<>();
 * boxMap.put("JGLX", "支持下载,不支持下载");
 *
 * HSSFDataValidation dataValidation = createBox("是否支持下载", boxMap , 2 , 10000 , 4 , 5);
 *
 * if(dataValidation != null) {
 *     sheet.addValidationData(dataValidation);
 * }
 */
private HSSFDataValidation createBox(String col, Map<String , String> boxMap , int firstRow, int lastRow, int firstCol, int lastCol) {
    HSSFDataValidation dataValidation = null;
    //查询码值表
    String cols = "";
    if(null != boxMap.get(col)) {
        cols = boxMap.get(col);
    }
    //设置下拉框
    if(cols.length() > 0 && null != cols) {
        String str[] = cols.split(",");
        //指定0-9行，0-0列为下拉框
        CellRangeAddressList cas = new CellRangeAddressList(firstRow , lastRow , firstCol , lastCol);
        //创建下拉数据列
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str);
        //将下拉数据放入下拉框
        dataValidation = new HSSFDataValidation(cas, dvConstraint);
    }
    return dataValidation;
}

EasyExcel 导入

### 创建实体
@Data
public class TestProp implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键", name = "id")
    private long id;

}

### 创建监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * 标规 Excel 解析监听器
 */
@Slf4j
public class TestExcelListener extends AnalysisEventListener<TestProp> {

    private List<TestProp> texts;

    @Override
    public void invoke(TestProp data, AnalysisContext context) {
        if (CheckUtil.collectionIsEmpty(texts)) {
            texts= new ArrayList<>();
        }

        this.texts.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }

    public List<TestProp> getTexts() {
        return this.texts;
    }
}

### 同步
TestExcelListener testExcelListener = new TestExcelListener();
log.info("正在解析 Excel 文档数据...");
EasyExcel.read(new FileInputStream(new File(path)), TestProp.class, testExcelListener).sheet().doReadSync();
log.info("解析 Excel 文档数据完成！");
return testExcelListener.getTexts();

### 异步
TestExcelListener testExcelListener = new TestExcelListener();
log.info("正在解析 Excel 文档数据...");
EasyExcel.read(new FileInputStream(new File(path)), TestProp.class, testExcelListener).sheet().doRead();

EasyExcel 导出

1. 工具类

# ExcelUtil 类
package com.yysrx.library.common.utils.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.ServletOutputStream;
import java.util.List;

public class ExcelUtil {

    /**
     * 导出
     */
    public static void export(ServletOutputStream response, List<ExcelEntity> sheetList) {
        try {
            ExcelWriter writer = EasyExcel.write(response).registerWriteHandler(setHorizontalCellStyleStrategy()).build();
            for (ExcelEntity entity : sheetList) {
                WriteSheet writeSheet = EasyExcel.writerSheet(entity.getSheetName()).build();
                writeSheet.setClazz(entity.getClazz());
                writer.write(entity.getData(), writeSheet);
            }
            writer.finish();
            response.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static HorizontalCellStyleStrategy setHorizontalCellStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        //  contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        //  contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//        //设置边框样式
//       contentWriteCellStyle.setBorderLeft(BorderStyle.MEDIUM);
        //      contentWriteCellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex()); 颜色
        //      contentWriteCellStyle.setBorderTop(DASHED);
//        contentWriteCellStyle.setBorderRight(DASHED);
        //       contentWriteCellStyle.setBorderBottom(DASHED);

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

}
# ExcelEntity 类
package com.yysrx.library.common.utils.excel;

import lombok.Data;

import java.util.List;

@Data
public class ExcelEntity<T> {
    private Class clazz;
    private List<T> data;
    private String sheetName;
}

2. 导出

// 导出
try {
    ExcelEntity<QueryKnowledgeEntriesVO> queryKnowledgeEntriesVOExcelEntity = new ExcelEntity<>();

    queryKnowledgeEntriesVOExcelEntity.setClazz(QueryKnowledgeEntriesVO.class);
    queryKnowledgeEntriesVOExcelEntity.setData(queryKnowledgeEntriesVOS);
    queryKnowledgeEntriesVOExcelEntity.setSheetName("第一页");

    List<ExcelEntity> excelEntities = new ArrayList<>();

    excelEntities.add(queryKnowledgeEntriesVOExcelEntity);

    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");

    String fileName = URLEncoder.encode("知识内容导出", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

    ServletOutputStream outputStream = response.getOutputStream();

    ExcelUtil.export(outputStream, excelEntities);
} catch (IOException e) {
    e.printStackTrace();
}