Apache POI读写Excel

Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java对Microsoft Office格式档案读和写的功能。POI为"Poor Obfuscation Implementation"的首字母缩写，意为“简洁版的模糊实现”。

准备

pom依赖

<dependency>
     <groupId>org.slf4j</groupId>
     <artifactId>slf4j-api</artifactId>
     <version>1.7.36</version>
 </dependency>
 <dependency>
     <groupId>ch.qos.logback</groupId>
     <artifactId>logback-core</artifactId>
     <version>1.2.10</version>
 </dependency>
 <dependency>
     <groupId>ch.qos.logback</groupId>
     <artifactId>logback-classic</artifactId>
     <version>1.2.10</version>
 </dependency>

 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>5.2.0</version>
 </dependency>
 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-scratchpad</artifactId>
     <version>5.2.0</version>
 </dependency>
 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>5.2.0</version>
 </dependency>
 
<dependency>
 	<groupId>javax.servlet</groupId>
 	<artifactId>javax.servlet-api</artifactId>
 	<version>3.1.0</version>
 	<scope>provided</scope>
 </dependency>

工具类

主要包括创建Excel，读取sheet页，写入sheet页

public class ExcelUtil {

	private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
	private static DecimalFormat df = new DecimalFormat("0");

	private ExcelUtil() {
	}

	/**
	 * 功能描述: <br>
	 * 根据不同文件类型和流，生成Workbook
	 *
	 * @param type (xlsx文件)，其他(xls文件)
	 * @param in   文件流
	 * @return
	 * @see [相关类/方法](可选)
	 * @since [产品/模块版本](可选)
	 */
	public static Workbook createWorkbook(String type, InputStream in) {
		try {
			if ("xlsx".equals(type)) {
				return new XSSFWorkbook(in);
			} else if ("xls".equals(type)) {
				return new HSSFWorkbook(in);
			}
		} catch (Exception e) {
			logger.error("生成Workbook异常", e);
		}
		return null;
	}

	/**
	 * 根据类型创建不同的excel文件
	 *
	 * @param type xlsx，xls
	 * @return
	 */
	public static Workbook createWorkbook(String type) {
		if ("xlsx".equals(type)) {
			return new XSSFWorkbook();
		} else if ("xls".equals(type)) {
			return new HSSFWorkbook();
		}
		return null;
	}

	/**
	 * 从excel指定sheet中读取数据，第一行不读
	 *
	 * @param workbook
	 * @param sheetNo  sheet下标
	 * @param colSize  列数
	 * @return
	 */
	public static List<String[]> readExcel(Workbook workbook, int sheetNo, int colSize) {
		List<String[]> res = new ArrayList<>();
		if (workbook == null || workbook.getNumberOfSheets() - 1 < sheetNo) {
			logger.error("workbook为空或者非法sheet页下标");
			return res;
		}
		try {
			Sheet sheet = workbook.getSheetAt(sheetNo);
			// 循环读取每一行记录
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				// 空行时过滤掉
				if (row == null) {
					continue;
				}
				int realCol = row.getLastCellNum();
				if (realCol > colSize) {
					realCol = colSize;
				}
				if (realCol > 0) {
					String[] arr = new String[colSize];
					// 读取一行的每一列
					for (int k = 0; k < realCol; k++) {
						Cell cell = row.getCell(k);
						arr[k] = getCellStringVal(cell);
					}
					res.add(arr);
				}
			}
		} catch (Exception e) {
			logger.error("读取sheet页数据异常", e);
		}
		return res;
	}

	public static List<Map<String, Object>> readExcel(Workbook workbook, int sheetNo, String[] keys) {
		List<Map<String, Object>> res = new ArrayList<>();
		if (workbook == null || workbook.getNumberOfSheets() - 1 < sheetNo) {
			logger.error("workbook为空或者非法sheet页下标");
			return res;
		}
		try {
			Sheet sheet = workbook.getSheetAt(sheetNo);
			int len = keys.length;
			// 循环读取每一行记录
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				// 空行时过滤掉
				if (row == null) {
					continue;
				}
				int realCol = row.getLastCellNum();
				if (realCol > len) {
					realCol = len;
				}
				if (realCol > 0) {
					Map<String, Object> map = new HashMap<>();
					// 读取一行的每一列
					for (int k = 0; k < realCol; k++) {
						Cell cell = row.getCell(k);
						map.put(keys[k], getCellVal(cell));
					}
					res.add(map);
				}
			}
		} catch (Exception e) {
			logger.error("读取sheet页数据异常", e);
		}
		return res;
	}

	public static String getCellStringVal(Cell cell) {
		String retValue = null;
		if (cell != null) {
			if (cell.getCellType() != CellType.STRING) {
				cell.setCellType(CellType.STRING);
			}
			retValue = cell.getStringCellValue();
		}
		return retValue;
	}

	public static Object getCellVal(Cell cell) {
		if (cell == null) {
			return null;
		}
		CellType cellType = cell.getCellType();
		if (cellType == CellType.BOOLEAN) {
			return cell.getBooleanCellValue();
		} else if (cellType == CellType.NUMERIC) {
			return df.format(cell.getNumericCellValue());
		} else {
			if (cellType == CellType.STRING) {
				return cell.getStringCellValue();
			} else {
				return "";
			}
		}
	}

	/**
	 * 往对应的sheet里面写入数据
	 *
	 * @param sheet    sheet页
	 * @param head     表头
	 * @param keys     数据key集合
	 * @param dataList 数据
	 */
	public static void writeSheetData(Sheet sheet, String[] head, String[] keys, List<Map<String, Object>> dataList) {
		// sheet为null，直接返回
		if (sheet == null) {
			logger.error("sheet页为空");
			return;
		}
		// 如果行数为0,并且第一行为null，需要设置标题头
		if (sheet.getLastRowNum() <= 0 && sheet.getRow(0) == null) {
			Row titelRow = sheet.createRow(0);
			for (int i = 0; i < head.length; i++) {
				Cell cell = titelRow.createCell(i);
				cell.setCellValue(head[i]);
			}
		}

		int startRowNum = sheet.getLastRowNum();
		// 给所有的行赋值,把数据写入sheet
		for (int i = 0; i < dataList.size(); i++) {
			Row row = sheet.createRow(startRowNum + i + 1);
			Map<String, Object> map = dataList.get(i);
			for (int j = 0; j < keys.length; j++) {
				Cell cell = row.createCell(j);
				Object value = map.get(keys[j]);
				cell.setCellValue(value == null ? "" : value.toString());
			}
		}
	}

	/**
	 * 获取excel中指定sheet页的总行数，excel为空或指定位置sheet不存在，返回-1
	 *
	 * @param workbook
	 * @param sheetNo
	 * @return
	 */
	public static int getExcelRowsNum(Workbook workbook, int sheetNo) {
		int rowCount = -1;
		int sheetIndex = sheetNo < 0 ? 0 : sheetNo;
		if (workbook != null && workbook.getNumberOfSheets() > sheetIndex) {
			rowCount = workbook.getSheetAt(sheetIndex).getPhysicalNumberOfRows();
		}
		return rowCount;
	}

	/**
	 * 功能描述: <br>
	 * 导出Excel数据
	 *
	 * @param fileName
	 * @param response
	 * @param workbook
	 * @see [相关类/方法](可选)
	 * @since [产品/模块版本](可选)
	 */
	public static void export(String fileName, HttpServletResponse response, Workbook workbook) {
		try (OutputStream os = response.getOutputStream()) {
			response.setContentType("application/msexcel;charset=UTF-8");
			response.addHeader("Content-Disposition", "attachment;filename=\""
					+ new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.UTF_8) + "\"");
			workbook.write(os);
			os.flush();
		} catch (IOException e) {
			logger.error("导出异常", e);
		}
	}
}

测试

简单读写测试，info.xlsx已存在，内容列为编码，姓名，年龄。

public static void main(String[] args) {
	try (FileInputStream fis = new FileInputStream("info.xlsx");
			FileOutputStream fos = new FileOutputStream("new.xlsx");
			Workbook workbook = ExcelUtil.createWorkbook("xlsx", fis);
			Workbook newWorkbook = ExcelUtil.createWorkbook("xlsx");) {

		// 指定列数
		int col = 3;
		List<String[]> res = ExcelUtil.readExcel(workbook, 0, col);
		StringBuilder sb = new StringBuilder();
		String[] arr = res.get(0);
		for (int i = 0; i < col; i++) {
			sb.append(arr[i]).append(",");
		}
		System.out.println(sb);

		// 指定列字段
		String[] keys = { "code", "name", "age" };
		List<Map<String, Object>> result = ExcelUtil.readExcel(workbook, 0, keys);
		System.out.println(result.get(0));

		// 写数据
		Sheet sheet = newWorkbook.createSheet();
		String[] head = { "编码", "姓名", "年龄" };
		ExcelUtil.writeSheetData(sheet, head, keys, result);
		newWorkbook.write(fos);
		fos.flush();
	} catch (Exception e) {
		e.printStackTrace();
	}
}