ExcelUtils工具类

package com.demo.commons.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Slf4j
public class ExcelUtils implements Serializable {

    private static Workbook getWk(MultipartFile file) throws IOException {
        Workbook workbook = null;
        if (Objects.requireNonNull(file.getOriginalFilename()).endsWith("xls")) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else if (file.getOriginalFilename().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(file.getInputStream());
        }
        return workbook;
    }

    /**
     * 获取Excel数据,返回List<String[]>;
     * sheetNumber 读取工作表的下标(从1开始).可有可无,默认读取所有表单.
     */
    public static List<String[]> excelToArrayList(MultipartFile file, int sheetNumber) throws IOException {
        ArrayList<String[]> resultList = new ArrayList<>();
        try {
            Sheet sheet = getWk(file).getSheetAt(sheetNumber);
            int rsRows = sheet.getRow(0) == null ? -1 : sheet.getLastRowNum();
            for (int i = 0; i <= rsRows; i++) {// 循环行
                Row row = sheet.getRow(i);
                if (!isRowEmpty(row)) {
                    int cellCount = row.getLastCellNum();
                    if (cellCount > 0) {
                        String[] objects = new String[cellCount];
                        for (int j = 0; j < cellCount; j++) {// 读取单元格
                            objects[j] = getCellValue(row.getCell(j));
                        }
                        resultList.add(objects);
                    }
                }
            }
        } catch (Exception e) {
            log.error("Read excel error.", e);
            throw e;
        }
        return resultList;
    }

    /**
     * 根据Cell类型设置数据
     */
    private static String getCellValue(Cell cell) {
        String cellvalue;
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BLANK:
                    cellvalue = "";
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    cellvalue = Boolean.toString(cell.getBooleanCellValue());
                    break;
                // 数值
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellvalue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String temp = cell.getStringCellValue();
                        // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
                        if (temp.contains(".")) {
                            cellvalue = String.valueOf(new Double(temp)).trim();
                        } else {
                            cellvalue = temp.trim();
                        }
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellvalue = cell.getStringCellValue().trim();
                    break;
                case Cell.CELL_TYPE_ERROR:
                    cellvalue = "";
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cellvalue = cell.getStringCellValue();
                    if (cellvalue != null) {
                        cellvalue = cellvalue.replaceAll("#N/A", "").trim();
                    }
                    break;
                default:
                    cellvalue = "";
                    break;
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

    /**
     * 获取SheetName
     */
    public static String getSheetName(MultipartFile file, int num) throws IOException {
        return getWk(file).getSheetName(num);
    }

    /**
     * 判斷空行
     */
    private static boolean isRowEmpty(Row row) {
        if (null != row) {
            for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
                    return false;
            }
        }
        return true;
    }

    /**
     * Excel表格导出
     * response  HttpServletResponse对象
     * fileName  导出Excel的文件名,sheetName一致
     * title     表头集合
     * excelData Excel表格的数据，封装为List<List<String>>
     */
    public static void exportExcel(HttpServletResponse response, String fileName, List<String> title, List<List<String>> excelData) throws IOException {
        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格，设置sheet名称与文件名称一致
        HSSFSheet sheet = workbook.createSheet(fileName);
        //设置表格列宽度为10个字节
        sheet.setDefaultColumnWidth(10);
        //创建第一行表头
        HSSFRow headrow = sheet.createRow(0);
        //遍历添加表头(下面模拟遍历学生，也是同样的操作过程)
        for (int i = 0; i < title.size(); i++) {
            //创建一个单元格
            HSSFCell cell = headrow.createCell(i);
            //创建一个内容对象
            HSSFRichTextString text = new HSSFRichTextString(title.get(i));
            //将内容对象的文字内容写入到单元格中
            cell.setCellValue(text);
        }
        //模拟遍历结果集，把内容加入表格
        int rows = 1;
        for (List<String> excelDatum : excelData) {
            //创建一个row行，然后自增1
            HSSFRow row = sheet.createRow(rows++);
            for (int j = 0; j < excelDatum.size(); j++) {
                HSSFCell cell = row.createCell(j);
                HSSFRichTextString text = new HSSFRichTextString(excelDatum.get(j));
                cell.setCellValue(text);
            }
        }
        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");
        //这后面可以设置导出Excel的名称，此例中名为student.xls
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes("gb2312"), "ISO8859-1"));
        //刷新缓冲
        response.flushBuffer();
        //workbook将Excel写入到response的输出流中，供页面下载
        workbook.write(response.getOutputStream());
    }
}

Maven依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.10.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.10.1</version>
</dependency>