如何在Java中实现Excel操作

如何在Java中实现Excel操作

今天我们将深入探讨如何在Java中实现对Excel文件的操作，这在日常的数据处理和报表生成中非常重要。

Java中操作Excel的常用方式

在Java中，有多种方式可以操作Excel文件，主要包括使用Apache POI库和使用第三方库JExcelApi。下面我们将分别介绍这两种方式的基本用法和示例。

使用Apache POI库操作Excel

Apache POI是一个开源的Java库，提供了对Microsoft Office格式文件（如Excel）的读写操作支持。

添加Maven依赖

首先，我们需要在项目中添加Apache POI相关的依赖：

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

创建Excel文件和写入数据

以下是一个简单的示例，演示如何创建一个Excel文件并写入数据：

package cn.juwatech.rebateapp.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) {
   
        String excelFilePath = "data/sample.xlsx";

        try (Workbook workbook = new XSSFWorkbook()) {
   
            Sheet sheet = workbook.createSheet("Sheet1");

            Row headerRow = sheet.createRow(0);
            Cell headerCell = headerRow.createCell(0);
            headerCell.setCellValue("Name");

            Row dataRow = sheet.createRow(1);
            Cell dataCell = dataRow.createCell(0);
            dataCell.setCellValue("John Doe");

            FileOutputStream outputStream = new FileOutputStream(excelFilePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();

            System.out.println("Excel written successfully.");
        } catch (IOException e) {
   
            e.printStackTrace();
        }
    }
}

读取Excel文件内容

同样，Apache POI也可以用于读取现有的Excel文件内容：

package cn.juwatech.rebateapp.excel;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;

public class ExcelReader {
   
    public static void main(String[] args) {
   
        String excelFilePath = "data/sample.xlsx";

        try (Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFilePath))) {
   
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(1);
            Cell cell = row.getCell(0);
            String cellValue = cell.getStringCellValue();

            System.out.println("Value in the cell: " + cellValue);
        } catch (IOException | EncryptedDocumentException e) {
   
            e.printStackTrace();
        }
    }
}

使用JExcelApi库操作Excel

除了Apache POI，还可以使用JExcelApi来操作Excel文件。JExcelApi相对轻量级，适合简单的Excel操作需求。

添加JExcelApi依赖

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

示例：创建和读取Excel文件

package cn.juwatech.rebateapp.excel;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import java.io.File;

public class JExcelApiExample {
   
    public static void main(String[] args) {
   
        try {
   
            String excelFilePath = "data/sample.xls";

            // Create Excel file and write data
            WritableWorkbook workbook = Workbook.createWorkbook(new File(excelFilePath));
            WritableSheet sheet = workbook.createSheet("Sheet1", 0);
            Label label = new Label(0, 0, "Hello JExcelApi!");
            sheet.addCell(label);
            workbook.write();
            workbook.close();

            // Read from Excel file
            Workbook workbookRead = Workbook.getWorkbook(new File(excelFilePath));
            Sheet sheetRead = workbookRead.getSheet(0);
            Cell cell = sheetRead.getCell(0, 0);
            String content = cell.getContents();
            System.out.println("Content read from Excel: " + content);
            workbookRead.close();
        } catch (Exception e) {
   
            e.printStackTrace();
        }
    }
}

结论

通过本文的介绍，你应该对在Java中实现Excel操作有了全面的了解。无论是使用Apache POI还是JExcelApi，都能够满足不同复杂度和需求的Excel文件处理任务。