spark-excel

# Spark Excel Library

A library for querying Excel files with Apache Spark, for Spark SQL and DataFrames.

[![Build Status](https://github.com/nightscape/spark-excel/actions/workflows/ci.yml/badge.svg)](https://github.com/nightscape/spark-excel/actions/workflows/ci.yml)
[![Maven Central](https://img.shields.io/maven-central/v/dev.mauch/spark-excel_2.13.svg)](https://search.maven.org/artifact/dev.mauch/spark-excel_2.13)

## Co-maintainers wanted
Due to personal and professional constraints, the development of this library has been rather slow.
If you find value in this library, please consider stepping up as a co-maintainer by leaving a comment [here](https://github.dev/mauch/spark-excel/issues/191).
Help is very welcome e.g. in the following areas:

* Additional features
* Code improvements and reviews
* Bug analysis and fixing
* Documentation improvements
* Build / test infrastructure

## Requirements

This library requires Spark 3.0+.

For a list of Spark versions that are automatically tested, see [build.mill](build.mill)
For more detail, please refer to project CI: [ci.yml](https://github.dev/mauch/spark-excel/blob/main/.github/workflows/ci.yml#L10)

## Linking
You can link against this library in your program at the following coordinates:

### Scala 2.12
```
groupId: dev.mauch
artifactId: spark-excel_2.12
version: <spark-version>_0.18.0
```

## Using with Spark shell
This package can be added to  Spark using the `--packages` command line option.  For example, to include it when starting the spark shell:

```
$SPARK_HOME/bin/spark-shell --packages dev.mauch:spark-excel_<scala-binary-version>:<spark-version>_<spark-excel-version>
```

## Features
* This package allows querying Excel spreadsheets as [Spark DataFrames](https://spark.apache.org/docs/latest/sql-programming-guide.html).
* From spark-excel [0.14.0](https://github.dev/mauch/spark-excel/releases/tag/v0.14.0) (August 24, 2021), there are two implementation of spark-excel
    * Original Spark-Excel with Spark data source API 1.0
    * Spark-Excel V2 with data source API V2.0+, which supports loading from multiple files, corrupted record handling and some improvement on handling data types.
      See below for further details

To use V2 implementation, just change your .format from `.format("dev.mauch.spark.excel")` to `.format("excel")`.
See [below](#excel-api-based-on-datasourcev2) for some details

See the [changelog](CHANGELOG.md) for latest features, fixes etc.

### Scala API
__Spark 2.0+:__


#### Create a DataFrame from an Excel file

```scala
import org.apache.spark.sql._

val spark: SparkSession = ???
val df = spark.read
    .format("dev.mauch.spark.excel") // Or .format("excel") for V2 implementation
    .option("dataAddress", "'My Sheet'!B3:C35") // Optional, default: "A1"
    .option("header", "true") // Required
    .option("treatEmptyValuesAsNulls", "false") // Optional, default: true
    .option("setErrorCellsToFallbackValues", "true") // Optional, default: false, where e