apachepoi教程

Apache POI (Poor Obfuscation Implementation) is a powerful Java library that allows developers to create, modify, and read Microsoft Office documents such as Excel spreadsheets, Word documents, and PowerPoint presentations. In this tutorial, we will explore the basics of Apache POI and learn how to use it to manipulate Excel files.

1. Introduction to Apache POI Apache POI is an open-source project developed by the Apache Software Foundation. It provides Java APIs for working with Microsoft Office documents, allowing developers to perform tasks such as reading, writing, and modifying Excel files without having to rely on external libraries or applications.

There are three main components of Apache POI: - HSSF (Horrible Spreadsheet Format): Used for reading and writing Excel 97-2003 files (.xls). - XSSF (XML Spreadsheet Format): Used for reading and writing Excel 2007+ files (.xlsx). - SXSSF (Streaming XML Spreadsheet Format): Used for reading and writing large Excel files without consuming excessive amounts of memory.

2. Setting up Apache POI To get started with Apache POI, you will first need to include the necessary dependencies in your project. You can do this by adding the following Maven dependencies to your pom.xml file:

```xml org.apache.poi poi 5.2.4 org.apache.poi poi-ooxml 5.2.4 ```

Alternatively, you can download the Apache POI JAR files from the official website and include them in your project manually.

3. Creating a new Excel file Now that you have set up Apache POI in your project, you can start creating and manipulating Excel files. To create a new Excel file, you can use the following code snippet:

```java import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Row;

public class ExcelWriter { public static void main(String[] args) { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("Sheet1");

Row headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("Name"); headerRow.createCell(1).setCellValue("Age");

// Write data to the Excel file

FileOutputStream fileOut = new FileOutputStream("output.xlsx"); workbook.write(fileOut); fileOut.close();

workbook.close(); } } ```

In this code snippet, we first create a new `XSSFWorkbook` object, which represents our Excel file. We then create a new sheet and a header row with two columns (Name and Age). Finally, we write the data to the Excel file and save it as `output.xlsx`.

4. Reading data from an Excel file To read data from an existing Excel file, you can use the following code snippet:

```java import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Row;

public class ExcelReader { public static void main(String[] args) { try (Workbook workbook = WorkbookFactory.create(new FileInputStream("input.xlsx"))) { Sheet sheet = workbook.getSheetAt(0);

for (Row row : sheet) { String name = row.getCell(0).getStringCellValue(); int age = (int) row.getCell(1).getNumericCellValue();

System.out.println("Name: " + name + ", Age: " + age); } } catch (IOException e) { e.printStackTrace(); } } } ```

In this code snippet, we first create a `Workbook` object by loading an existing Excel file `input.xlsx`. We then retrieve the first sheet from the workbook and iterate over its rows to read the data from each cell. Finally, we print out the Name and Age values to the console.

5. Modifying an existing Excel file To modify an existing Excel file, you can use the following code snippet:

```java import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Cell;

public class ExcelModifier { public static void main(String[] args) { try (Workbook workbook = WorkbookFactory.create(new FileInputStream("input.xlsx"))) { Sheet sheet = workbook.getSheetAt(0);

Row row = sheet.createRow(1); row.createCell(0).setCellValue("Alice"); row.createCell(1).setCellValue(30);

FileOutputStream fileOut = new FileOutputStream("output.xlsx"); workbook.write(fileOut); fileOut.close();

workbook.close(); } catch (IOException e) { e.printStackTrace(); } } } ```

In this code snippet, we load an existing Excel file `input.xlsx` and retrieve the first sheet from the workbook. We then create a new row with the Name and Age values and write the modified data to a new Excel file `output.xlsx`.

6. Conclusion In this tutorial, we have covered the basics of Apache POI and demonstrated how to create, read, and modify Excel files using the library. Apache POI provides a powerful set of APIs for working with Microsoft Office documents and can be a valuable tool for anyone working with Excel files in Java applications. If you have any questions or run into issues with Apache POI, be sure to check the official documentation and community forums for help. Happy coding!