其二，多sheet页Excel文件导入

一、测试效果

二、代码实现

测试文件就是上一篇文章生成的demo.xml文件，可用自己的，也可以下载我的这个共享文件。
链接：https://pan.baidu.com/s/1JuLQdDs5c_4QKgfKABnAXQ
提取码：no9v

pom依赖：

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15-beta2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15-beta2</version>
        </dependency>

备注：它这个的缺陷是版本号相对于上一篇文章的会低一些。

ExcelReadUtil工具类：

package com.zwd.cases.demo.utilstest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel读取工具类 依赖jar包
 * <dependency>
 *		<groupId>org.apache.poi</groupId>
 * 		<artifactId>poi</artifactId>
 * 		<version>3.15-beta2</version>
 * </dependency>
 * <dependency>
 *     	<groupId>org.apache.poi</groupId>
 * 		<artifactId>poi-ooxml</artifactId>
 * 		<version>3.15-beta2</version>
 * </dependency>
 * 
 * @author zwd
 *
 */
public class ExcelReadUtil {

	/**
	 * 读取xlsx类型的Excel数据<br>
	 * 返回的数据库类型中最外层List表示Sheet的集合<br>
	 * 第二层List表示一个Sheet中每行数据Map的集合<br>
	 * 每行Map中每列Integer的数据值String
	 * 
	 * @param is
	 * @param beginRowIndex
	 *            从第几行开始解析，第一行为0
	 * @return
	 * @throws Exception
	 */
	public static List<List<Map<Integer, String>>> getXssfExcelData(InputStream is, int beginRowIndex)
			throws Exception {
		Workbook book = new XSSFWorkbook(is);
		List<List<Map<Integer, String>>> sheets = new ArrayList<List<Map<Integer, String>>>();
		// 循环工作表Sheet
		for (int sheetNum = 0; sheetNum < book.getNumberOfSheets(); sheetNum++) {
			Sheet sheet = book.getSheetAt(sheetNum);
			if (null == sheet) {
				continue;
			}
			List<Map<Integer, String>> list = parsingSheet(sheet, beginRowIndex);
			if (!list.isEmpty()) {
				sheets.add(list);
			}
		}
		book.close();
		return sheets;
	}

	/**
	 * 读取xls类型的Excel数据<br>
	 * 返回的数据库类型中最外层List表示Sheet的集合<br>
	 * 第二层List表示一个Sheet中每行数据Map的集合<br>
	 * 每行Map中每列Integer的数据值String
	 * 
	 * @param is
	 * @param beginRowIndex
	 *            从第几行开始解析，第一行为0
	 * @return
	 * @throws Exception
	 */
	public static List<List<Map<Integer, String>>> getHssfExcelData(InputStream is, int beginRowIndex)
			throws Exception {
		Workbook book = new HSSFWorkbook(is);
		List<List<Map<Integer, String>>> sheets = new ArrayList<List<Map<Integer, String>>>();
		// 循环工作表Sheet
		for (int sheetNum = 0; sheetNum < book.getNumberOfSheets(); sheetNum++) {
			Sheet sheet = book.getSheetAt(sheetNum);
			if (null == sheet) {
				continue;
			}
			List<Map<Integer, String>> list = parsingSheet(sheet, beginRowIndex);
			if (!list.isEmpty()) {
				sheets.add(list);
			}
		}
		book.close();
		return sheets;
	}

	/**
	 * 读取Excel数据<br>
	 * 返回的数据库类型中最外层List表示Sheet的集合<br>
	 * 第二层List表示一个Sheet中每行数据Map的集合<br>
	 * 每行Map中每列Integer的数据值String
	 * 
	 * @param filePath
	 * @param beginRowIndex
	 *            从第几行开始解析，第一行为0
	 * @return
	 * @throws Exception
	 */
	public static List<List<Map<Integer, String>>> getExcelData(String filePath, int beginRowIndex) throws Exception {
		Workbook book = getWorkbook(filePath);
		List<List<Map<Integer, String>>> sheets = new ArrayList<List<Map<Integer, String>>>();
		// 循环工作表Sheet
		for (int sheetNum = 0; sheetNum < book.getNumberOfSheets(); sheetNum++) {
			Sheet sheet = book.getSheetAt(sheetNum);
			if (null == sheet) {
				continue;
			}
			List<Map<Integer, String>> list = parsingSheet(sheet, beginRowIndex);
			if (!list.isEmpty()) {
				sheets.add(list);
			}
		}
		book.close();
		return sheets;
	}

	private static Workbook getWorkbook(String filePath) throws Exception {
		Workbook book = null;
		String prefix = "xlsx";
		if (filePath.endsWith(prefix)) {
			book = new XSSFWorkbook(new FileInputStream(filePath));
		} else {
			book = new HSSFWorkbook(new FileInputStream(filePath));
		}
		return book;
	}

	/**
	 * 获取每个sheet里的所有值
	 * 
	 * @param sheet
	 * @param beginRowIndex
	 *            从第几行开始读
	 * @return
	 * @throws Exception
	 */
	private static List<Map<Integer, String>> parsingSheet(Sheet sheet, int beginRowIndex) throws Exception {
		List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
		// 循环行Row
		for (int rowNum = beginRowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
			Row row = sheet.getRow(rowNum);
			if (null == row) {
				continue;
			}
			Map<Integer, String> map = parsingRow(row);
			if (!map.isEmpty()) {
				list.add(map);
			}
		}
		return list;
	}

	/**
	 * 解析行获取每行数据
	 * 
	 * @param row
	 * @return
	 * @throws Exception
	 */
	private static Map<Integer, String> parsingRow(Row row) throws Exception {
		Map<Integer, String> map = new HashMap<Integer, String>(16);
		// 循环列Cell
		for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
			Cell cell = row.getCell(cellNum);
			if (null == cell) {
				continue;
			}
			String value = getValue(cell);
			map.put(cellNum, value);
		}
		return map;
	}

	/**
	 * 获取列cell的值
	 * 
	 * @param cell
	 * @return
	 */
	private static String getValue(Cell cell) {
		String value = "";
		switch (cell.getCellType()) {
		// 文本
		case HSSFCell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		// 数字
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				if (null != date) {
					value = new SimpleDateFormat("yyyy-MM-dd").format(date);
				}
			} else {
				value = new DecimalFormat("#.00").format(cell.getNumericCellValue());
			}
			break;
		// 公式
		case HSSFCell.CELL_TYPE_FORMULA:
			value = cell.getCellFormula() + "";
			break;
		// 空值
		case HSSFCell.CELL_TYPE_BLANK:
			break;
		// 故障
		case HSSFCell.CELL_TYPE_ERROR:
			value = "非法字符";
			break;
		// Boolean
		case HSSFCell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue() + "";
			break;
		default:
			value = "未知类型";
			break;
		}
		return value;
	}

	/**
	 * 测试
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			String filePath = "C:\\Users\\CAIJIE\\Desktop\\test.xls";
			List<List<Map<Integer, String>>> sheets = getExcelData(filePath, 0);
			for (int i = 0; i < sheets.size(); i++) {
				List<Map<Integer, String>> list = sheets.get(i);

				System.out.println(list.size());

				for (int j = 0; j < list.size(); j++) {
					Map<Integer, String> map = list.get(j);
					Set<Integer> set = map.keySet();
					Iterator<Integer> it = set.iterator();
					while (it.hasNext()) {
						Integer temp = it.next();
						String value = map.get(temp);
						System.out.print(value.trim() + " ");
					}
					System.out.println();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

测试类DemoTest3：

package com.zwd.cases.demo;

import com.alibaba.fastjson.JSONObject;
import com.zwd.cases.demo.utilstest.ExcelReadUtil;
import org.junit.platform.commons.util.StringUtils;

import java.util.List;
import java.util.Map;

/**
 * @Description 多sheet页Excel文件导入
 * @Author zhengwd
 * @Date 2023/7/25 22:26
 **/
public class DemoTest3 {

    public static void main(String[] args) throws Exception {
        // 存储目录
        String destPath = "C:\\Users\\86178\\Desktop\\demo.xls";
        readData(destPath);
    }

    private static void readData(String destPath) throws Exception {
        List<List<Map<Integer, String>>> list = ExcelReadUtil.getExcelData(destPath, 1);
        if (list == null) {
            throw new Exception("无记录");
        }
        // 1.处理-sheet1数据
        List<Map<Integer, String>> dataOne = list.get(0);
        int rowOne = 0;
        for (Map<Integer, String> record : dataOne) {
            rowOne++;
            System.out.println("导入行数据：" + JSONObject.toJSONString(record));
            // 表头跳过
            if (rowOne == 1) {
                continue;
            }
            // 自定义非空校验
            if (StringUtils.isBlank(record.get(0)) || StringUtils.isBlank(record.get(1)) || StringUtils.isBlank(record.get(2))) {
                throw new Exception("第" + rowOne + "行必填项为空");
            }
        }

        // 2.处理-sheet2数据
        List<Map<Integer, String>> dataTwo = list.get(1);
        int rowTwo = 0;
        for (Map<Integer, String> record : dataTwo) {
            rowTwo++;
            System.out.println("导入行数据：" + JSONObject.toJSONString(record));
            // 表头跳过
            if (rowTwo == 1) {
                continue;
            }
            // 自定义非空校验
            if (StringUtils.isBlank(record.get(0)) || StringUtils.isBlank(record.get(1)) || StringUtils.isBlank(record.get(2))) {
                throw new Exception("第" + rowTwo + "行必填项为空");
            }
        }
    }
}

友情链接：（其一，多sheet页Excel文件导出）java导出多个数据库表数据，生成Excel文件，包含多个sheet页