一、什么是POI 

POI全称PoorObfuscation Implementation ，是Apache组件的一个开源项目，可以对微软的Office一系列办公软件进行读写操作。本文实例对应的POI版本为3.9。

二、生成Excel 工具类  

package org.zhao.component;
 
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import java.io.*;
import java.util.*;
 
/**
 * 生成Excel
 *
 * @author Administrator
 * @date 2022年03月11日
 */
public class WriteExcelComponent {
 
 
    private static String getFileExtname(String filename) {
        return filename.substring(filename.lastIndexOf(".") + 1).toLowerCase();
    }
 
 
    @SuppressWarnings("unchecked")
    public static void writeExcel(String filePath, List<Map<String, Object>> dataList) throws Exception {
        File file = new File(filePath);
        if (file.exists()) {
            return;
        }
        String fileName = file.getName();
        String fileSuffix = getFileExtname(fileName);
        Workbook workbook = null;
        if ("xls".equals(fileSuffix)) {
            workbook = new HSSFWorkbook();
        }
        if ("xlsx".equals(fileSuffix)) {
            workbook = new XSSFWorkbook();
        }
        if (workbook == null) {
            return;
        }
        for (int sheetIndex = 0; sheetIndex < dataList.size(); sheetIndex++) {
            Sheet sheet = workbook.createSheet();
            Map<String, Object> data = dataList.get(sheetIndex);
            String sheetName = "Sheet" + (sheetIndex + 1);
            String dataSheetName = MapUtils.getString(data, "sheetName", "");
            if (dataSheetName != null && dataSheetName.length() != 0) {
                sheetName = dataSheetName;
            }
            workbook.setSheetName(sheetIndex, sheetName);
            List<String> sheetHeader = (List<String>) MapUtils.getObject(data, "sheetHeader", new ArrayList<>());
            int startRowIndex = 0;
            if (CollectionUtils.isNotEmpty(sheetHeader)) {
                CellStyle cellStyle = workbook.createCellStyle();
                Font font = workbook.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                cellStyle.setFont(font);
                Row row = sheet.createRow(0);
                for (int headerIndex = 0; headerIndex < sheetHeader.size(); headerIndex++) {
                    Cell cell = row.createCell(headerIndex, Cell.CELL_TYPE_STRING);
                    cell.setCellValue(String.valueOf(sheetHeader.get(headerIndex)));
                    cell.setCellStyle(cellStyle);
                }
                startRowIndex = 1;
            }
            List<List<String>> rows = (List<List<String>>) MapUtils.getObject(data, "sheetRows", new ArrayList<>());
            for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
                Row row = sheet.createRow(startRowIndex + rowIndex);
                List<String> cellItems = rows.get(rowIndex);
                for (int cellIndex = 0; cellIndex < cellItems.size(); cellIndex++) {
                    Cell cell = row.createCell(cellIndex, Cell.CELL_TYPE_STRING);
                    cell.setCellValue(cellItems.get(cellIndex));
                    sheet.setColumnWidth(cellIndex, 80 * 50);
                }
            }
        }
        FileOutputStream fos = new FileOutputStream(filePath);
        workbook.write(fos);
        fos.close();
    }
 
 
 
}

三、读取Excel 工具类 

package org.zhao.component;
 
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import java.io.*;
import java.util.*;
 
/**
 * Excel 读取excel中的数据
 *
 * @author Administrator
 * @date 2022年03月11日
 */
public class ReadExcelComponent {
 
 
    private static String getFileExtname(String filename) {
        return filename.substring(filename.lastIndexOf(".") + 1).toLowerCase();
    }
 
    /**
     * 读取某个sheet中的数据
     *
     * @param path     文件路径
     * @param sheetNum sheet号
     */
    public static Map<String, Object> readExcelData(String path, int sheetNum) {
        List<Map<String, Object>> dataList = readExcelDatas(path);
        if (dataList.size() == 0) {
            return null;
        }
        return dataList.stream().filter(map -> MapUtils.getIntValue(map, "sheetIndex", 0) == sheetNum).findAny().orElse(null);
    }
 
    /**
     * 读取Excel中所有数据 多个sheet
     *
     * @param path 文件路径
     */
    public static List<Map<String, Object>> readExcelDatas(String path) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        if (path == null || path.length() == 0) {
            return dataList;
        }
        File file = new File(path);
        String fileName = file.getName();
        String fileSuffix = getFileExtname(fileName);
        Workbook workbook = null;
        try {
            if ("xls".equals(fileSuffix)) {
                workbook = new HSSFWorkbook(new FileInputStream(file));
            }
            if ("xlsx".equals(fileSuffix)) {
                workbook = new XSSFWorkbook(new FileInputStream(file));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return dataList;
        }
 
        List<Sheet> workbookSheets = getWorkbookSheetList(workbook);
        for (int sheetIndex = 0; sheetIndex < workbookSheets.size(); sheetIndex++) {
            Sheet sheet = workbookSheets.get(sheetIndex);
            List<Row> sheetRowList = getSheetRowList(sheet);
            String sheetName = sheet.getSheetName();
            Map<String, Object> sheetDataList = new LinkedHashMap<>();
            List<List<String>> rows = new ArrayList<>();
            sheetRowList.forEach(row -> {
                short firstCellNum = row.getFirstCellNum();
                short lastCellNum = row.getLastCellNum();
                List<String> cellDataList = new ArrayList<>();
                for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    cellDataList.add(getCellValue(cell));
                }
                rows.add(cellDataList);
            });
            sheetDataList.put("sheetName", sheetName);
            sheetDataList.put("sheetIndex", sheetIndex + 1);
            sheetDataList.put("sheetRows", rows);
            dataList.add(sheetDataList);
        }
        return dataList;
    }
 
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return "";
        }
        try {
            int cellType = cell.getCellType();
 
            /*Numeric 数字类型*/
            if (HSSFCell.CELL_TYPE_NUMERIC == cellType) {
                cellValue = String.valueOf(cell.getNumericCellValue());
            }
 
            /*字符串*/
            if (HSSFCell.CELL_TYPE_STRING == cellType) {
                cellValue = cell.getStringCellValue();
            }
            /*公式 Formula*/
            if (HSSFCell.CELL_TYPE_FORMULA == cellType) {
                cellValue = cell.getCellFormula();
            }
            /*空行*/
            if (HSSFCell.CELL_TYPE_BLANK == cellType) {
                cellValue = "";
            }
            /*布尔类型*/
            if (HSSFCell.CELL_TYPE_BOOLEAN == cellType) {
                cellValue = String.valueOf(cell.getBooleanCellValue());
            }
            /*Error Cell type */
            if (HSSFCell.CELL_TYPE_ERROR == cellType) {
                cellValue = String.valueOf(cell.getErrorCellValue());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
 
        return cellValue;
 
    }
 
    /*获取sheet 中行数据*/
    private static List<Row> getSheetRowList(Sheet sheet) {
        List<Row> items = new ArrayList<>();
        if (sheet == null) {
            return items;
        }
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        for (int rowIndex = 0; rowIndex < physicalNumberOfRows; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            items.add(row);
        }
        return items;
    }
 
    /*获取excel 中 sheet*/
    private static List<Sheet> getWorkbookSheetList(Workbook workbook) {
        List<Sheet> items = new ArrayList<>();
        if (workbook == null) {
            return items;
        }
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            items.add(sheet);
        }
        return items;
    }
 
}

四、运行实例 

package org.zhao;
 
import org.zhao.component.ReadExcelComponent;
import org.zhao.component.WriteExcelComponent;
 
import java.util.*;
 
/**
 * @author Administrator
 * @date 2022年03月11日
 */
public class Main {
 
    public static void writeDataToExcel() {
        List<Map<String, Object>> dataList = new ArrayList<>();
        Map<String, Object> sheet_1 = new HashMap<>();
        sheet_1.put("sheetName", "语文成绩");
        sheet_1.put("sheetHeader", Arrays.asList("学号", "姓名", "分数"));
        List<List<String>> rows = new ArrayList<>();
        rows.add(Arrays.asList("0001", "小张", "60"));
        rows.add(Arrays.asList("0002", "小明", "80"));
        rows.add(Arrays.asList("0003", "小王", "90"));
        sheet_1.put("sheetRows", rows);
        dataList.add(sheet_1);
 
        Map<String, Object> sheet_2 = new HashMap<>();
        sheet_2.put("sheetName", "数学成绩");
        sheet_2.put("sheetHeader", Arrays.asList("学号", "姓名", "分数"));
        List<List<String>> rows2 = new ArrayList<>();
        rows2.add(Arrays.asList("0001", "小张", "90"));
        rows2.add(Arrays.asList("0002", "小明", "86"));
        rows2.add(Arrays.asList("0003", "小王", "95"));
        sheet_2.put("sheetRows", rows2);
        dataList.add(sheet_2);
 
        try {
            WriteExcelComponent.writeExcel("D:/成绩单.xls", dataList);
            System.out.println("创建成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    public static void readExcelData() {
        List<Map<String, Object>> allItems = ReadExcelComponent.readExcelDatas("D:/成绩单.xls");
        System.out.println("allItems = " + allItems);
 
        Map<String, Object> sheet2Items = ReadExcelComponent.readExcelData("D:/成绩单.xls", 2);
        System.out.println("sheet2Items = " + sheet2Items);
 
    }
 
    public static void main(String[] args) {
        //        writeDataToExcel();
        readExcelData();
    }
}