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 "";
        }
    }

    // 写数据到excel
    public 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改为33
            System.out.println("数据更新成功");

            // 验证更新结果
            List<List<Object>> updatedData = readDataFromExcel(filepath);
            System.out.println("更新后的数据:");
            updatedData.forEach(System.out::println);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}