SparkSQL导入导出Excel文件

Leefs 2021-12-14 PM 2470℃ 0条

[TOC]

### 前言

本篇使用的环境是：

+ spark版本：3.0.0
+ scala版本：2.12

### 一、导入依赖

```xml

<dependency>
    <groupId>com.crealytics</groupId>
    <artifactId>spark-excel_2.12</artifactId>
    <version>0.14.0</version>
</dependency>
```

注意：如果使用scala 2.11版本需要使用如下依赖,不然会出现依赖版本不匹配

```xml
<dependency>
    <groupId>com.crealytics</groupId>
    <artifactId>spark-excel_2.11</artifactId>
    <version>0.13.1</version>
</dependency>
```

### 二、读取Excel核心代码

```sql
def importExcelFunction(spark:SparkSession,path:String): DataFrame ={
    spark.read
      .format("com.crealytics.spark.excel")
      .option("header","true")
      .option("sheetName", "Sheet1")
      .option("treatEmptyValuesAsNulls", "true")
      .option("inferSchema", "true")
      .option("addColorColumns", "False")
      .schema(schema)
      .load(path)
  }
```

**说明**

| 参数                    | 是否必须 | 说明                                              |
| ----------------------- | -------- | ------------------------------------------------- |
| format                  | 是       | 加载驱动                                          |
| header                  | 是       | 是否加载表头信息（true:是，false:否）             |
| sheetName               | 否       | 需要加载的工作表名称（如果有多个工作表时使用）    |
| treatEmptyValuesAsNulls | 否       | 是否将空值处理为null（true:是，false:否）         |
| inferSchema             | 否       | 是否自动推断属性列的数据类型（true:是，false:否） |
| addColorColumns         | 否       | 默认false               |
| schema                  | 否       | 自定义数据结构和类型                              |
| load                    | 是       | 根据文件地址加载excel                             |

### 三、导出Excel文件核心代码

```sql
def exportExcelFunction(sparkData:DataFrame,savePath:String): Unit ={
    sparkData.write
      .format("com.crealytics.spark.excel")
      .option("header","true")
//      .mode("append")
      .mode("overwrite")
      .save(savePath)
  }
```

| 参数   | 是否必须 | 说明                                                     |
| ------ | -------- | -------------------------------------------------------- |
| format | 是       | 加载驱动                                                 |
| header | 是       | 是否指定表头                                             |
| mode   | 否       | 数据导出模式(append:在原来数据基础上追加,overwrite:覆盖) |
| save   | 是       | 保存文件                                                 |

### 四、示例

#### 4.1 数据准备

+ **student.xlsx**

![17.SparkSQL导入导出Excel文件01.jpg](https://lilinchao.com/usr/uploads/2021/12/2263714416.jpg)

#### 4.2 需求

> 按照班级进行分组，将不同班级的学生名称按照逗号分隔，然后将结果导出到新的Excel文件

**得到结果如下**

![17.SparkSQL导入导出Excel文件02.jpg](https://lilinchao.com/usr/uploads/2021/12/4117219043.jpg)

#### 4.3 完整代码

```sql
import org.apache.spark.sql.types.{IntegerType, StringType, StructField, StructType}
import org.apache.spark.sql.{DataFrame, SparkSession}

/**
  * @author lilinchao
  * @date 2021/12/14
  * @description Excel导入导出
  **/
object ExcelImportAndExport {

def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("ExcelImportAndExport").master("local[1]").getOrCreate()

val filePath = ClassLoader.getSystemResource("student.xlsx").getFile
    val frameData: DataFrame = importExcelFunction(spark,filePath)
//    frameData.show(10,false)
    frameData.createOrReplaceTempView("student")

exportExcelFunction(frame,"D://BC//student2.xlsx")
    frame.show(10,false)

spark.stop()
  }
  //定义表头结构
  val schema = StructType(List(
      StructField("id", StringType, nullable = false),
      StructField("name", StringType, nullable = false),
      StructField("age", IntegerType, nullable = false),
      StructField("sex", StringType, nullable = false),
      StructField("cls", StringType, nullable = false)
    )
  )

/**
    * 加载Excel数据
    * @param spark
    * @param path
    * @return
    */
  def importExcelFunction(spark:SparkSession,path:String): DataFrame ={
    spark.read
      .format("com.crealytics.spark.excel")//加载驱动
      .option("header","true")//是否加载表头信息
      .option("sheetName", "Sheet1")//需要加载工作表名称（如果有多个工作表时使用）
      .option("treatEmptyValuesAsNulls", "true") //是否将空值处理为null
      .option("inferSchema", "true") //是否自动推断属性列的数据类型
      .option("addColorColumns", "False") //是否添加颜色列
      .schema(schema)//自定义数据结构和类型
      .load(path)//根据文件地址加载excel
  }

/**
    * 将DataFrame数据导出到Excel
    * @param sparkData
    * @param savePath
    */
  def exportExcelFunction(sparkData:DataFrame,savePath:String): Unit ={
    sparkData.write
      .format("com.crealytics.spark.excel")
      .option("header","true")
//      .mode("append")
//      .mode("overwrite")
      .save(savePath)
  }
}
```

*注意：student.xlsx文件放到resources根目录下*

标签: Spark, Spark SQL

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://lilinchao.com/archives/1746.html

上一篇 18.Hive正则表达式详解

下一篇 19.Hive自定义UDF函数

SparkSQL导入导出Excel文件

评论已关闭

栏目分类

标签云

友情链接申请

SparkSQL导入导出Excel文件

评论已关闭

 栏目分类

标签云

友情链接申请

栏目分类

标签云

友情链接申请