SparkでExcelファイルの入出力をする

SparkでExcelファイルを扱うためのライブラリである[spark-excel](https://github.com/crealytics/spark-excel)を紹介します。

# ライブラリの概要と利用用途
ExcelファイルをSparkデータフレームとして読み込んだり、また逆に出力したり、さらには既存のExcelファイルの特定の部分にSparkデータフレームのデータを上書きして保存するということもできます。Spark自体がビッグデータを扱うものなので、可視化という意味ではBIツールを使うことがほとんどで、Excelを使うことは多くはないと思いますが、どうしても使わなければいけない場合はこのライブラリを使うことで簡単に実現できます。

以下、Databricks環境を例にとって説明します。

## インストール

Mavenからパッケージを[クラスターにインストール](https://qiita.com/taka_yayoi/items/9407356caadea3dfb47c#%E3%82%AF%E3%83%A9%E3%82%B9%E3%82%BF%E3%83%BC%E3%81%AB%E3%82%A4%E3%83%B3%E3%82%B9%E3%83%88%E3%83%BC%E3%83%AB%E3%81%95%E3%82%8C%E3%81%A6%E3%81%84%E3%82%8B%E3%83%A9%E3%82%A4%E3%83%96%E3%83%A9%E3%83%AA)します。2022年12月現在最新安定版の以下のバージョンを指定します。
`com.crealytics:spark-excel_2.12:3.3.1_0.18.5`

![スクリーンショット 2022-12-22 20.42.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2663175/515d2e22-1854-23db-722d-4982f289e087.png)



## SparkデータフレームをExcelファイルとして出力する
[databricksサンプルデータセット](https://qiita.com/taka_yayoi/items/3f8ccad13c6efd242be1)のフライトデータから20行とって、出力用のSparkデータフレームを作成します。
```python
df_output = ( spark
       .read
       .option( "header","true" )
       .csv( "dbfs:/databricks-datasets/flights/departuredelays.csv" )
       .limit(20) )
```

このデータフレームを以下のようにして、save()で指定したファイルパスにExcelファイルを出力します。format()で `com.crealytics.spark.excel`を指定します。
```python
( df_output.write
  .format( "com.crealytics.spark.excel" )
  .option( "header", "true" )
  .mode( "overwrite" )
  .save( xlsx_file_path ) )
```
フライト情報のExcelファイルができました！
![スクリーンショット 2022-12-22 21.00.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2663175/8e0ad4d0-ef45-b2fa-2c3b-a2e5cc909bde.png)

## ExcelファイルをSparkデータフレームとして読み込む
上で出力したファイルをSparkデータフレームとして読み込んでみます。読み込みも同様にformat()で`com.crealytics.spark.excel`を指定します。
```python
df_input = ( spark.read.format( "com.crealytics.spark.excel" )
    .option( "header", "true" )
    .option( "dataAddress", "Sheet1" )
    .option( "inferSchema", "false" )
    .load( xlsx_file_path ) )
```
display()で中身を確認すると、確かにExcelファイルの中身がSparkデータフレームとして読み込まれていることがわかります。
![スクリーンショット 2022-12-22 21.02.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2663175/906939ff-55cd-c438-2a95-ec251eef95c5.png)

## 既存のExcelファイルにSparkデータフレームのデータを追加する
既存のExcelファイルの任意のセルに、Sparkデータフレームのデータを追加して上書きするということもできます。

3便のフライト情報(3行x2列)を、シートのD5の位置に挿入してみます。

```python
df_flight = spark.createDataFrame( [["HND","SFO"],["HND","CDG"],["CDG","HND"]] )
( df_flight.write
  .format( "com.crealytics.spark.excel" )
  .option( "dataAddress", "'Sheet1'!D5" )
  .option( "header", "false" )
  .mode( "append" )
  .save( xlsx_file_path ) )
```
- `.option( "dataAddress", "'Sheet1'!D5" )`で、Sheet1シートのD5セルという位置を指定しています。
- 上書きなので、`mode( "append" )`を指定しているのがポイントです。
 
出力されたExcelを確認すると、確かにデータが追加されていました！ 
![スクリーンショット 2022-12-22 21.07.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2663175/cdceeb9d-dc45-5c8c-0a58-e424917bfdae.png)

罫線とか背景色とかマクロとか色々盛り込んだExcelファイルを用意しておいて、データだけを差し込むという使い方ができて便利そうです。

## 最後に
[今回使用したノートブック](https://github.com/motokazu-ishikawa-db/public_repo/blob/main/20221219_spark_excel/Spark%E3%81%A7Excel%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%81%AE%E5%85%A5%E5%87%BA%E5%8A%9B%E5%85%A5%E5%87%BA%E5%8A%9B.py)は、[Repo機能](https://qiita.com/taka_yayoi/items/b89f199ff0d3a4c16140)を使って簡単にDatabricksにクローンして試してみることができます。