Implementing Excel Reading into List<Map<String, String>> in Java

Reading an Excel File into a List of Maps Using Java

Introduction

In the world of data manipulation and analysis, Excel files remain a staple format for storing tabular data. Whether you're dealing with financial reports, customer information, or any other kind of structured data, being able to efficiently read and process Excel files is crucial. In this blog post, we will explore how to read an Excel file (.xlsx) using Java and convert its contents into a List<Map<String, String>>, which is a flexible structure that can be easily integrated into various parts of your application.

Prerequisites

Before we dive in, ensure you have the Apache POI library added to your project. If you're using Maven, add the following dependency to your pom.xml:

<!--        excel reader-start-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version> <!-- 使用你所需的版本号 -->
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.2</version> <!-- 同上 -->
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.17.1</version> <!-- 使用你所需要的版本 -->
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.17.1</version> <!-- 同上 -->
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.11.0</version> <!-- 使用你需要的版本 -->
        </dependency>
    <!--        excel reader-end -->

Step-by-Step Guide

Let's break down the process into manageable steps:

Import Necessary Packages

Start by importing the necessary packages from Apache POI.

   import org.apache.poi.ss.usermodel.*;
   import org.apache.poi.xssf.usermodel.XSSFWorkbook;
   import java.io.File;
   import java.io.FileInputStream;
   import java.util.ArrayList;
   import java.util.HashMap;
   import java.util.List;
   import java.util.Map;

Define the Read Method

Create a method to read the Excel file and return the data as a list of maps.

   public static List<Map<String, String>> readExcelFile(String filePath) throws Exception {
       List<Map<String, String>> dataList = new ArrayList<>();
       try (FileInputStream fis = new FileInputStream(new File(filePath));
            Workbook workbook = new XSSFWorkbook(fis)) {

           Sheet sheet = workbook.getSheetAt(0); // Assuming the first sheet contains the data
           Row headerRow = sheet.getRow(0); // First row as headers

           for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
               Row row = sheet.getRow(rowIndex);
               if (row == null) continue; // Skip empty rows

               Map<String, String> rowMap = new HashMap<>();
               for (Cell headerCell : headerRow) {
                   int colIndex = headerCell.getColumnIndex();
                   Cell dataCell = row.getCell(colIndex);
                   String header = headerCell.getStringCellValue();
                   String value = "";

                   if (dataCell != null) {
                       switch (dataCell.getCellType()) {
                           case STRING:
                               value = dataCell.getStringCellValue();
                               break;
                           case NUMERIC:
                               if (DateUtil.isCellDateFormatted(dataCell)) {
                                   value = dataCell.getDateCellValue().toString();
                               } else {
                                   value = String.valueOf(dataCell.getNumericCellValue());
                               }
                               break;
                           case BOOLEAN:
                               value = String.valueOf(dataCell.getBooleanCellValue());
                               break;
                           default:
                               // Handle other cell types if needed
                               break;
                       }
                   }
                   rowMap.put(header, value);
               }
               dataList.add(rowMap);
           }
       }
       return dataList;
   }

Testing the Functionality

Test the functionality by calling the read method and printing the result.

   public static void main(String[] args) {
       try {
           List<Map<String, String>> data = readExcelFile("path/to/your/excel/file.xlsx");
           System.out.println(data);
       } catch (Exception e) {
           e.printStackTrace();
       }
   }

Conclusion

By following these steps, you can effectively read Excel files in Java and convert them into a List<Map<String, String>> format. This approach provides a solid foundation for further data processing, such as filtering, sorting, or integrating the data into databases or other systems. Remember to always handle exceptions gracefully and validate your input to ensure robustness in your applications.
source code: https://github.com/bseayin/ProgrammingArsenal2/blob/main/src/main/java/com/xsz/programmingarsenal/utils/ExcelReader.java

Happy coding!