Java操作Excel文档

1 导入pom依赖

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

2 具体代码

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.*;
import java.util.ArrayList;
import java.util.List;public class ExcelUtils {// xlsx文件表头private static final String[] HEADERS = {"id", "name", "age", "department"};private static void createHeaderRow(Sheet sheet) {Row headerRow = sheet.createRow(0);for (int i = 0; i < HEADERS.length; i++) {headerRow.createCell(i).setCellValue(HEADERS[i]);}}// 创建excel文件public static void createExcel(String filePath) throws IOException {try (Workbook workbook = new XSSFWorkbook()) {Sheet sheet = workbook.createSheet("雇员信息");createHeaderRow(sheet);try (FileOutputStream fos = new FileOutputStream(filePath)) {workbook.write(fos);}}}// 辅助方法private static Workbook getWorkbook(String filePath) throws IOException {File file = new File(filePath);if (!file.exists()) {createExcel(filePath);}try (FileInputStream fis = new FileInputStream(filePath)) {return WorkbookFactory.create(fis);}}private static void setCellValue(Cell cell, Object value) {if (value instanceof String) {cell.setCellValue((String) value);} else if (value instanceof Integer) {cell.setCellValue((Integer) value);} else if (value instanceof Double) {cell.setCellValue((Double) value);} else if (value instanceof Boolean) {cell.setCellValue((Boolean) value);}}private static void saveWorkbook(Workbook workbook, String filePath) throws IOException {try (FileOutputStream fos = new FileOutputStream(filePath)) {workbook.write(fos);}}private static Object getCellValue(Cell cell) {switch (cell.getCellType()) {case STRING:return cell.getStringCellValue();case NUMERIC:return cell.getNumericCellValue();case BOOLEAN:return cell.getBooleanCellValue();case FORMULA:return cell.getCellFormula();default:return "";}}// 写数据到excelpublic static void writeDataToExcel(String filePath, List<Object[]> data) throws IOException {try (Workbook workbook = getWorkbook(filePath)) {Sheet sheet = workbook.getSheetAt(0);int lastRowNum = sheet.getLastRowNum();for (Object[] rowData : data) {Row row = sheet.createRow(++lastRowNum);for (int i = 0; i < rowData.length; i++) {Cell cell = row.createCell(i);setCellValue(cell,rowData[i]);}}saveWorkbook(workbook,filePath);}}// 更新excel中的数据public static void updateDataInExcel(String filePath,int rowIndex,int colIndex,Object newValue) throws IOException {try(Workbook workbook = getWorkbook(filePath)) {Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(rowIndex);if(row==null){row = sheet.createRow(rowIndex);}Cell cell = row.getCell(colIndex,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);setCellValue(cell,newValue);saveWorkbook(workbook,filePath);}}// 读取excel数据public static List<List<Object>> readDataFromExcel(String filepath) throws IOException {List<List<Object>> data = new ArrayList<>();try(Workbook workbook = getWorkbook(filepath)){Sheet sheet = workbook.getSheetAt(0);for (Row row : sheet) {if(row.getRowNum() == 0) continue; // 跳过表头List<Object> rowData = new ArrayList<>();for (Cell cell : row) {rowData.add(getCellValue(cell));}data.add(rowData);}}return data;}// 测试用例public static void main(String[] args) {try{String filepath = "employee.xlsx";// 1 创建excel文件createExcel(filepath);System.out.println("excel文件创建成功");// 2 写入数据List<Object[]> dataToWrite = new ArrayList<>();dataToWrite.add(new Object[]{"E001","张三",28,"研发部"});dataToWrite.add(new Object[]{"E002","李四",32,"市场部"});writeDataToExcel(filepath,dataToWrite);System.out.println("数据写入成功");// 3 读取数据List<List<Object>> readData = readDataFromExcel(filepath);System.out.println("读取数据");readData.forEach(System.out::println);// 4. 更新数据updateDataInExcel(filepath, 1, 2, 33); // 将李四的年龄从32改为33System.out.println("数据更新成功");// 验证更新结果List<List<Object>> updatedData = readDataFromExcel(filepath);System.out.println("更新后的数据:");updatedData.forEach(System.out::println);} catch (IOException e) {e.printStackTrace();}}
}