Using Apache POI for Excel processing in Java API development

With the development of informatization, the importance of data processing continues to highlight. For businesses and individuals, Excel worksheets have become the most commonly used data processing method in daily work. However, although Excel worksheets are easy to use, as the amount of data and processing requirements increase, ordinary Excel processing methods are often difficult to meet the needs of practical applications. At this time, using the Apache POI library for Excel processing in Java API development has become an excellent choice.

1. Introduction to Apache POI

Apache POI (Poor Obfuscation Implementation) is a free Java library used to process and operate files in Microsoft Office format, including Word, Excel and PowerPoint, etc. document. Apache POI allows Java programmers to easily read, write, and manipulate Office files.

Apache POI can be roughly divided into three parts:

• HSSF (Horrible Spreadsheet Format): processing Excel 97-2003 files, that is, xls format;
• XSSF (XML Spreadsheet Format): Process files in Excel 2007 and above, that is, xlsx format;
• HWPF (Horrible Word Processor Format) and XWPF (XML Word Processor Format): Process files in Word 97-2003 format respectively and Word 2007 and above files.

This article mainly focuses on the use of HSSF and XSSF.

2. POI installation

Before using Apache POI for Excel processing, we need to install the library first. The installation of Apache POI is relatively simple. You only need to download the latest version of its jar package and add it to the classpath of the project. It is recommended to use tools such as Maven or Gradle to introduce POI, so as to avoid exceptions caused by issues such as version conflicts.

3. Reading Excel files

Reading Excel files is one of the most basic operations in Apache POI applications. Below is sample code to read an xlsx file.

public static void readXlsx(String filePath) throws IOException {
    FileInputStream fis = new FileInputStream(filePath);
    XSSFWorkbook workbook = new XSSFWorkbook(fis); // 创建工作簿
    XSSFSheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
    int rowStartIndex = sheet.getFirstRowNum(); // 获取第一行的行号
    int rowEndIndex = sheet.getLastRowNum(); // 获取最后一行的行号
    for (int i = rowStartIndex; i <= rowEndIndex; i++) { // 循环行
        XSSFRow row = sheet.getRow(i); // 获取当前行
        if (row == null) { // 若为空行则跳过
            continue;
        }
        int cellStartIndex = row.getFirstCellNum(); // 获取第一列的列号
        int cellEndIndex = row.getLastCellNum() - 1; // 获取最后一列的列号
        for (int j = cellStartIndex; j <= cellEndIndex; j++) { // 循环列
            XSSFCell cell = row.getCell(j); // 获取当前单元格
            String cellValue = cell.getStringCellValue(); // 获取当前单元格的值
            System.out.print(cellValue + "  "); // 输出到控制台
        }
        System.out.println();
    }
    workbook.close(); // 关闭工作簿
    fis.close(); // 关闭输入流
}

In this code, we first use FileInputStream to open the xlsx file and use XSSFWorkbook to create the workbook. We then take the first worksheet and use a loop to read the contents of the file row-by-row and column-by-column, outputting the value of each cell to the console. Finally, we need to close the workbook and input stream to release resources.

Reading xls files is basically the same as reading xlsx files. You only need to change the XSSF related code to HSSF.

4. Writing to Excel files

Writing to Excel files is also one of the commonly used operations in Apache POI applications. Below is sample code that writes to an xlsx file.

public static void writeXlsx(String filePath) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(); // 创建工作簿
    XSSFSheet sheet = workbook.createSheet("Sheet1"); // 创建工作表
    XSSFRow row0 = sheet.createRow(0); // 创建第一行
    XSSFCell cell00 = row0.createCell(0); // 创建第一个单元格
    cell00.setCellValue("姓名"); // 设置单元格的值
    XSSFCell cell01 = row0.createCell(1); // 创建第二个单元格
    cell01.setCellValue("年龄"); // 设置单元格的值
    XSSFRow row1 = sheet.createRow(1); // 创建第二行
    XSSFCell cell10 = row1.createCell(0); // 创建第一个单元格
    cell10.setCellValue("张三"); // 设置单元格的值
    XSSFCell cell11 = row1.createCell(1); // 创建第二个单元格
    cell11.setCellValue(20); // 设置单元格的值
    XSSFRow row2 = sheet.createRow(2); // 创建第三行
    XSSFCell cell20 = row2.createCell(0); // 创建第一个单元格
    cell20.setCellValue("李四"); // 设置单元格的值
    XSSFCell cell21 = row2.createCell(1); // 创建第二个单元格
    cell21.setCellValue(25); // 设置单元格的值
    FileOutputStream fos = new FileOutputStream(filePath);
    workbook.write(fos); // 输出文件
    workbook.close(); // 关闭工作簿
    fos.close(); // 关闭输出流
}

In this code, we create an xlsx file and create a worksheet named "Sheet1" in it. Next, we added three rows of data to the table, each row containing two cells. Finally, we output the file to the specified file path and close the workbook and output stream to release resources.

Writing xls files is basically the same as writing xlsx files. You only need to change the XSSF related code to HSSF.

5. Summary

Through the explanation of this article, readers can have a preliminary understanding of the use of Apache POI in Java API development, especially in Excel file processing. Whether reading or writing Excel files, Apache POI provides an easy-to-use interface that allows Java programmers to easily operate Excel files. At the same time, Apache POI also supports the processing of Office files such as Word and PowerPoint, and is an essential tool library for Java developers.

The above is the detailed content of Using Apache POI for Excel processing in Java API development. For more information, please follow other related articles on the PHP Chinese website!