Spark操作Excel最佳实践

1、背景描述

数据开发中通常会涉及到Excel的处理。Java和Apache Spark都不支持读取Excel文件，除了使用Python外，Spark操作Excel一般有两个框架：Apache POI和Spark-Excel

2、Apache POI与Spark-Excel

2.1、Apache POI简介

Apache POI（Poor Obfuscation Implementation）是Apache软件基金会的一个开源项目，提供了用于读写Microsoft Office格式文件（如Word、Excel、PowerPoint）的Java API

POI虽然支持多种文档格式的读写，但是我们最常用的还是对Excel文件的读写。POI技术使得Java开发人员能够轻松地操作和处理Office文档，它为我们提供了丰富的功能和灵活性

Apache POI针对Excel文件的读写提供了三个API：

• HSSF：HSSF是POI项目对Excel 1997~2007文件格式（xls）的纯Java实现
• XSSF：XSSF是POI项目对Excel 2007 OOXML文件格式（xlsx）的纯Java实现
• SXSSF：SXSSF是XSSF的API兼容流扩展，可用于非常大的电子表格且堆空间有限的情况。SXSSF通过限制对滑动窗口内的行的访问来实现其低内存占用

API中的对象及解释如下：

• Workbook：工作簿
• Sheet：工作表
• Row：行
• Cell：单元格

Apache POI官网：http://poi.apache.org/index.html

Apache POI官方文档：https://poi.apache.org/apidocs/index.html

2.2、Spark-Excel简介

Spark-Excel是一个强大的开源库，‌专为Apache Spark设计，‌用于在大数据环境中高效地读取、‌写入和操作Excel文件。‌该项目由Crealytics团队开发并维护，‌为那些需要在大规模数据分析中与Excel文件打交道的用户提供了一种灵活而高效的选择

Spark-Excel无缝集成了Apache Spark框架，‌允许用户直接在DataFrame或Dataset上操作Excel数据，‌无需先将其转换为其他格式。‌它充分利用了Spark的分布式计算能力，‌能够处理大规模的数据集

Spark-Excel内部使用了流行的Apache POI库来解析和生成Excel文件，‌提供了对老版本（‌xls）‌和新版本（‌xlsx）‌的兼容性，‌并支持复杂的Excel特性，‌例如表格样式、‌公式和超链接等

Spark-Excel实现了高效的内存管理和缓存策略，‌减少了不必要的磁盘IO，‌提高了整体性能。‌对于大型数据集，‌它可以分块读写，‌避免一次性加载整个文件导致的内存压力

Spark-Excel官方文档：https://index.scala-lang.org/crealytics/spark-excel

3、Spark读取Excel

3.1、导入依赖

首先导入所需依赖。除了Spark-Core、Spark-SQL和Spark-Hive依赖外，本次使用到的其它核心依赖如下：

<!-- Java-Excel依赖 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<!-- Spark-Excel依赖 -->
<dependency>
    <groupId>com.crealytics</groupId>
    <artifactId>spark-excel_2.12</artifactId>
    <version>0.13.7</version>
</dependency>
<!--单元测试-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.11</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

3.2、使用Apache POI

可以使用Apache POI先将Excel文件转换成CSV或TXT文件，然后再使用Spark读取CSV或TXT文件

为方便使用，我们封装Apache POI读取和转换Excel文件的工具类POIUtils.java

1）工具类POIUtils.java

package com.cc;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Lis
1
2
3
4
5
6
7
8
9
10
11
12