java如何excel

读取Excel文件

使用Apache POI库读取Excel文件。确保在项目中添加POI依赖。对于Maven项目，在pom.xml中添加以下依赖：

<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 org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelReader {
    public static void main(String[] args) {
        try (FileInputStream fis = new FileInputStream("example.xlsx");
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    System.out.print(cell.toString() + "\t");
                }
                System.out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

写入Excel文件

使用Apache POI库创建和写入Excel文件。以下代码示例展示了如何创建一个新的Excel文件并写入数据：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriter {
    public static void main(String[] args) {
        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream fos = new FileOutputStream("output.xlsx")) {
            Sheet sheet = workbook.createSheet("Sheet1");
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("Hello, Excel!");
            workbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

处理Excel公式

在Excel单元格中设置公式并计算其结果。以下代码示例展示了如何设置公式并获取计算结果：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelFormula {
    public static void main(String[] args) {
        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream fos = new FileOutputStream("formula.xlsx")) {
            Sheet sheet = workbook.createSheet("Sheet1");
            Row row = sheet.createRow(0);
            row.createCell(0).setCellValue(10);
            row.createCell(1).setCellValue(20);
            Cell cell = row.createCell(2);
            cell.setCellFormula("A1+B1");
            workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
            workbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

使用EasyExcel处理大数据量

对于大数据量的Excel文件，可以使用EasyExcel库，它提供了更高效的内存管理。在Maven项目中添加EasyExcel依赖：

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

读取大数据量Excel文件的代码示例：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;

public class EasyExcelReader {
    public static void main(String[] args) {
        String fileName = "large_file.xlsx";
        EasyExcel.read(fileName, new ReadListener<Object>() {
            @Override
            public void invoke(Object data, AnalysisContext context) {
                System.out.println(data);
            }
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                System.out.println("Read complete");
            }
        }).sheet().doRead();
    }
}

导出Excel文件

使用EasyExcel导出数据到Excel文件。以下代码示例展示了如何将数据导出为Excel文件：

import com.alibaba.excel.EasyExcel;
import java.util.ArrayList;
import java.util.List;

public class EasyExcelWriter {
    public static void main(String[] args) {
        String fileName = "export.xlsx";
        List<List<String>> data = new ArrayList<>();
        List<String> row = new ArrayList<>();
        row.add("Name");
        row.add("Age");
        data.add(row);
        row = new ArrayList<>();
        row.add("Alice");
        row.add("25");
        data.add(row);
        EasyExcel.write(fileName).sheet("Sheet1").doWrite(data);
    }
}