Java Excel导入导出数据校验

Java Excel导入导出

导入Excel

1、使用Apache POI库

Apache POI是一个用于操作Microsoft Office文档的Java库，支持读取和写入Excel文件。

步骤：

1、1 添加依赖

在项目的pom.xml文件中添加Apache POI依赖：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poiooxml</artifactId>
    <version>4.1.2</version>
</dependency>

1、2 读取Excel文件

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class ExcelReader {
    public static void main(String[] args) {
        String filePath = "example.xlsx";
        try {
            FileInputStream inputStream = new FileInputStream(new File(filePath));
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            List<List<Object>> data = readData(sheet);
            for (List<Object> row : data) {
                for (Object cell : row) {
                    System.out.print(cell + "t");
                }
                System.out.println();
            }
            workbook.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    private static List<List<Object>> readData(Sheet sheet) {
        List<List<Object>> data = new ArrayList<>();
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> rowData = new ArrayList<>();
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    rowData.add("");
                } else {
                    rowData.add(getCellValue(cell));
                }
            }
            data.add(rowData);
        }
        return data;
    }
    private static Object getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return cell.getNumericCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            default:
                return null;
        }
    }
}

2、使用EasyExcel库（推荐）

EasyExcel是一个基于Apache POI的轻量级读写Excel库，简化了Excel操作，推荐使用EasyExcel进行Excel导入导出操作。

步骤：

2、1 添加依赖（以Maven为例）

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

2、2 读取Excel文件（以读取为例）

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.read.metadata.ReadWorkbook;
import com.alibaba.excel.read.metadata.holder.*;
import com.alibaba.excel.read.metadata.holder.ReadSheetHolder;
import com.alibaba.excel.read.metadata.holder.ReadTableHolder;
import com.alibaba.excel.read.metadata.holder.ReadWorkbookHolder;
import com.alibaba.excel.read.metadata.holder.*; // 注意这里要导入多个包，否则会报错！因为每个包下面都有ReadSheetHolder类，需要指定具体的包名，comma, colon, semicolon, slash, backslash, question, exclamation, period, asterisk, underscore, plus, minus, equals, greater, less, double_quote, single_quote, left_parenthesis, right_parenthesis, vertical_bar, ampersand, pipe, caret, dollar, percent, hash, at, left_brace, right_brace, left_bracket, right_bracket, backtick, quotemark, apostrophe, single_quoted_argument, double_quoted_argument, whitespace, space, tab, form_feed, newline, carriage_return, horizontal_tab, vertical_tab, backslashN, backspace