Java导入导出Excel文件

话不多说，上代码。
首先是POM的配置：

 <!-- POI导入导出 -->
        <!-- 操作以 .xls 为后缀的 Excel -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <!-- 操作以 .xlsx 为后缀的 Excel -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>

然后就是我抽出来的Excel解析工具类：

package com.yinhai.his.bmp.basedata.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * Excel工具
 */
public class ExcelUtil {

    /**
     * 导出数据为Excel
     */
    public static void exportExcel(String place, List<String> namelist, String fileName, ArrayList<List<String>> dataList) {
        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("0");
        //设置列宽度
        for (int i = 0; i < namelist.size(); i++) {
            sheet.setColumnWidth(i, namelist.size() * 256);
        }
        /**
         * 单元格 样式
         */
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 设置单元格内容水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格内容垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        /**
         * 标题样式 样式
         */
        XSSFFont titleFont = wb.createFont();
        titleFont.setFontHeight(24);
        titleFont.setBold(true);
        CellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setBorderTop(BorderStyle.THIN);
        titleCellStyle.setBorderBottom(BorderStyle.THIN);
        titleCellStyle.setBorderLeft(BorderStyle.THIN);
        titleCellStyle.setBorderRight(BorderStyle.THIN);
        titleCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        titleCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        titleCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        titleCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 设置单元格内容水平居中
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格内容垂直居中
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleCellStyle.setFont(titleFont);

        /**
         * 主 标题 在这里插入主标题
         */
        Row titleRow;
        Cell titleCell;
        sheet.addMergedRegion(new CellRangeAddress((short) 0, (short) 2, (short) 0, (short) namelist.size() - 1));
        for (int i = 0; i <= 2; i++) {
            titleRow = sheet.createRow(i);
            for (int j = 0; j < namelist.size(); j++) {
                titleCell = titleRow.createCell(j);
                titleCell.setCellStyle(titleCellStyle);
                titleCell.setCellValue(fileName);
            }
        }

        /**
         * 列 标题 在这里插入标题
         */
        Row rowLabel;
        Cell cellLabel;
        for (int i = 3; i < 4; i++) {
            rowLabel = sheet.createRow(i);
            for (int j = 0; j < namelist.size(); j++) {
                cellLabel = rowLabel.createCell(j);
                cellLabel.setCellStyle(cellStyle);
                cellLabel.setCellValue(namelist.get(j));
            }
        }

        /**
         * 列 数据 在这里插入数据
         */
        Row rowCheck;
        Cell cellCheck;
        for (int i = 0; i < dataList.size(); i++) {
            rowCheck = sheet.createRow((i + 4));
            for (int j = 0; j < namelist.size(); j++) {
                cellCheck = rowCheck.createCell(j);
                cellCheck.setCellStyle(cellStyle);
                cellCheck.setCellValue(dataList.get(i).get(j));
            }
        }

        /**
         * 页脚
         */
        setExcelFooterName(fileName, 0, wb);

        /**
         * 进行导出
         */
        exportOutPutExcel(place + "\\" + fileName + ".xlsx", wb);

    }

    /**
     * 设置Excel页脚
     */
    public static void setExcelFooterName(String customExcelFooterName, int setExcelFooterNumber, XSSFWorkbook wb) {
        wb.setSheetName(setExcelFooterNumber, customExcelFooterName);
    }

    /**
     * 输出流 导出Excel到桌面
     */
    public static void exportOutPutExcel(String exportPositionPath, XSSFWorkbook wb) {
        try {
            File file = new File(exportPositionPath);
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            wb.write(fileOutputStream);
            fileOutputStream.close();
        } catch (IOException e) {
            System.err.println(e.getMessage());
        }
    }

    /**
     * 导入Excel数据
     * @param fileName
     * @return
     */
    public static List<String> importExcel(String fileName) {
        Workbook workbook = readExcel(fileName);
        //获得sheet的数量(sheet的index是从0开始的)
        int sheetCount = workbook.getNumberOfSheets();
        String excels = new String();
        //遍历Sheet
        //创建list存储
        ArrayList<String> list = new ArrayList<>();
        //得到Sheet
        Sheet sheet = workbook.getSheetAt(0);
        //得到每个Sheet的行数,此工作表中包含的最后一行(Row的index是从0开始的)
        int rowCount = sheet.getLastRowNum();
        //遍历Row
        for (int j = 4; j <= rowCount; j++) {
            //得到Row
            Row row = sheet.getRow(j);
            if (row == null) {
                continue;
            }
            //创建string用于拼接值
            String str = new String();
            //得到每个Row的单元格数
            int cellCount = row.getLastCellNum();
            //获取每个单元格的值
            for (int k = 0; k < cellCount; k++) {
                Cell cell = row.getCell(k);
                Object value = getCellFormatValue(cell);
                if (value.equals("") || value == null) value = "undefind";
                if (str.length() == 0) {
                    str = String.valueOf(value);
                } else {
                    str = str + " " + value.toString();
                }
            }
            //组成list数组传输
            list.add(str);
        }
        return list;
    }


    /**
     * 根据文件地址，解析后缀返回不同的Workbook对象
     *
     * @param filePath 文件地址
     * @return Excel文档对象Workbook
     */
    public static Workbook readExcel(String filePath) {

        if (filePath == null || filePath.equals("")) {
            return null;
        }
        //得到文件后缀
        String suffix = filePath.substring(filePath.lastIndexOf("."));
        System.out.println(suffix);
        try {
            InputStream is = new FileInputStream(filePath);
            if (".xls".equals(suffix)) {
                return new HSSFWorkbook(is);
            }
            if (".xlsx".equals(suffix)) {
                return new XSSFWorkbook(is);
            }
            return null;

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取Excel的值
     *
     * @param cell
     * @return
     */
    public static Object getCellFormatValue(Cell cell) {
        Object cellValue;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
                //空值单元格
                case BLANK: {
                    cellValue = "";
                    break;
                }
                //数值型单元格 getNumericCellValue()以数字形式获取单元格的值。
                case NUMERIC: {
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        //cellValue = cell.getDateCellValue();
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        cellValue = dateFormat.format(date);
                    } else {
                        //数字
                        System.out.println("数字格式");
                        cellValue = cell.getNumericCellValue();
                    }
                    break;
                }
                //公式型单元格getCellFormula()返回单元格的公式
                case FORMULA: {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                //字符串单元格
                case STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                //布尔值型单元格
                case BOOLEAN: {
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

}

要记住POM版本一定要对应，不然框架就会有报错。另外这个开发手册可以参考3.9的POI文档。