如何通过Spark Java API从DataSet生成Excel文件？求示例代码指引

Hey there! Generating Excel files from a Spark DataSet using the Java API is straightforward once you have the right tools in place. Let me break this down for you with a complete, runnable example.

Spark doesn’t include built-in support for writing Excel files, so we’ll use the popular com.crealytics:spark-excel library, which integrates seamlessly with Spark SQL. Here’s what you need to add to your Maven pom.xml:

<dependencies>
    <!-- Spark Core & SQL -->
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-core_2.12</artifactId>
        <version>3.3.0</version> <!-- Match your Spark version -->
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-sql_2.12</artifactId>
        <version>3.3.0</version> <!-- Match your Spark version -->
    </dependency>
    <!-- Spark Excel Library -->
    <dependency>
        <groupId>com.crealytics</groupId>
        <artifactId>spark-excel_2.12</artifactId>
        <version>3.3.0_0.18.5</version> <!-- Compatible with Spark 3.3.0 -->
    </dependency>
</dependencies>

Pro tip: Make sure the spark-excel version matches your Spark version. Just cross-check the library’s version compatibility if you’re using a different Spark release.

This example creates a sample DataSet, verifies its contents, and writes it to an Excel file with custom headers and a specified sheet name:

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.RowFactory;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.types.DataTypes;
import org.apache.spark.sql.types.StructField;
import org.apache.spark.sql.types.StructType;

import java.util.Arrays;
import java.util.List;

public class SparkDataSetToExcel {
    public static void main(String[] args) {
        // Initialize SparkSession (adjust master for production)
        SparkSession spark = SparkSession.builder()
                .appName("DataSetToExcelDemo")
                .master("local[*]") // Use local mode for testing; remove in cluster
                .getOrCreate();

        // Create sample data rows
        List<Row> sampleRows = Arrays.asList(
                RowFactory.create("Alice", 30, "New York"),
                RowFactory.create("Bob", 25, "London"),
                RowFactory.create("Charlie", 35, "Paris"),
                RowFactory.create("Diana", 28, "Tokyo")
        );

        // Define the schema for our DataSet
        StructType dataSchema = DataTypes.createStructType(new StructField[]{
                DataTypes.createStructField("name", DataTypes.StringType, false),
                DataTypes.createStructField("age", DataTypes.IntegerType, false),
                DataTypes.createStructField("city", DataTypes.StringType, false)
        });

        // Create the DataSet from sample data and schema
        Dataset<Row> userDataSet = spark.createDataFrame(sampleRows, dataSchema);

        // Optional: Print the DataSet to confirm data is correct
        System.out.println("Sample DataSet:");
        userDataSet.show();

        // Write the DataSet to an Excel file
        userDataSet.write()
                .format("com.crealytics.spark.excel")
                .option("header", "true") // Include column headers in the output
                .option("dataAddress", "'User Data'!A1") // Specify sheet name and starting cell
                .option("dateFormat", "yyyy-MM-dd") // Optional: Set date format if needed
                .mode("overwrite") // Overwrite existing file (use "append" to add data)
                .save("./output/user_data.xlsx"); // Output file path

        // Clean up: Stop the SparkSession
        spark.stop();
        System.out.println("Excel file generated successfully!");
    }
}

• Cluster Deployment: When running on a Spark cluster, use the --packages flag during submission to ensure all nodes have the spark-excel dependency:

  spark-submit --packages com.crealytics:spark-excel_2.12:3.3.0_0.18.5 --class SparkDataSetToExcel your-application.jar
  

• Write Modes: Choose the right mode for your use case:
  • overwrite: Replace existing file
  • append: Add data to an existing file
  • ignore: Do nothing if the file exists
  • errorifexists: Throw an error if the file exists (default)
• Advanced Configs: You can add options like password to protect the Excel file, or timestampFormat for timestamp columns—check the library’s documentation for more details.

内容的提问来源于stack exchange，提问作者OOvic