当前位置：首页 > Java

java如何导入excel数据

2026-03-03 14:28:59Java

使用Apache POI库导入Excel数据

Apache POI是Java处理Microsoft Office文档的流行库，支持Excel文件的读写操作。

确保项目中包含Apache POI依赖。Maven项目中添加以下依赖：

<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>

读取Excel文件的基本示例代码：

import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;

public class ExcelReader {
    public static void readExcel(String filePath) {
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = WorkbookFactory.create(fis)) {

            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                        case NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case BOOLEAN:
                            System.out.print(cell.getBooleanCellValue() + "\t");
                            break;
                        default:
                            System.out.print("-\t");
                    }
                }
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

使用EasyExcel库处理大数据量

EasyExcel是阿里巴巴开源的Excel处理工具，特别适合处理大数据量情况。

添加EasyExcel依赖：

java如何导入excel数据

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>

定义数据模型类：

@Data
public class DemoData {
    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("邮箱")
    private String email;
}

使用监听器模式读取Excel：

public class DemoDataListener extends AnalysisEventListener<DemoData> {
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println("解析到一条数据:" + data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("所有数据解析完成");
    }
}

public class ReadExcel {
    public static void main(String[] args) {
        String fileName = "demo.xlsx";
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }
}

处理不同Excel版本

对于.xls格式(HSSF)：

java如何导入excel数据

Workbook workbook = new HSSFWorkbook(new FileInputStream("old.xls"));

对于.xlsx格式(XSSF)：

Workbook workbook = new XSSFWorkbook(new FileInputStream("new.xlsx"));

使用WorkbookFactory自动判断文件类型：

Workbook workbook = WorkbookFactory.create(new File("file.xlsx"));

处理日期和特殊格式

解析日期单元格：

if (DateUtil.isCellDateFormatted(cell)) {
    Date date = cell.getDateCellValue();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    System.out.print(sdf.format(date));
}

处理公式单元格：

if (cell.getCellType() == CellType.FORMULA) {
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    CellValue cellValue = evaluator.evaluate(cell);
    switch (cellValue.getCellType()) {
        case NUMERIC:
            System.out.print(cellValue.getNumberValue());
            break;
        case STRING:
            System.out.print(cellValue.getStringValue());
            break;
    }
}