How to write dataset object to excel in spark java?

Question

I Am reading excel file using com.crealytics.spark.excel package. Below is the code to read an excel file in spark java.

    Dataset<Row> SourcePropertSet = sqlContext.read()
               .format("com.crealytics.spark.excel")
               .option("location", "D:\\5Kto10K.xlsx")
               .option("useHeader", "true")
               .option("treatEmptyValuesAsNulls", "true")
               .option("inferSchema", "true")
               .option("addColorColumns", "false")
               .load("com.databricks.spark.csv");

But I tried with the same (com.crealytics.spark.excel) package to write dataset object to an excel file in spark java.

    SourcePropertSet.write()
          .format("com.crealytics.spark.excel")
          .option("useHeader", "true")
          .option("treatEmptyValuesAsNulls", "true")
          .option("inferSchema", "true")
          .option("addColorColumns", "false").save("D:\\resultset.xlsx");

But i am getting below error.

java.lang.RuntimeException: com.crealytics.spark.excel.DefaultSource does not allow create table as select.

And even I tried with org.zuinnote.spark.office.excel package also. below is the code for that.

    SourcePropertSet.write()
             .format("org.zuinnote.spark.office.excel")
             .option("write.locale.bcp47", "de") 
             .save("D:\\result");

i have added following dependencies in my pom.xml

<dependency>
              <groupId>com.github.zuinnote</groupId>
              <artifactId>hadoopoffice-fileformat</artifactId>
              <version>1.0.0</version>
          </dependency>
        <dependency>
            <groupId>com.github.zuinnote</groupId>
            <artifactId>spark-hadoopoffice-ds_2.11</artifactId>
            <version>1.0.3</version>
        </dependency>

But I am getting below error.

java.lang.IllegalAccessError: tried to access method org.zuinnote.hadoop.office.format.mapreduce.ExcelFileOutputFormat.getSuffix(Ljava/lang/String;)Ljava/lang/String; from class org.zuinnote.spark.office.excel.ExcelOutputWriterFactory

Please help me to write dataset object to an excel file in spark java.

Check my answer for similar question at https://stackoverflow.com/questions/44799949/how-to-write-dataset-to-a-excel-file-using-hadoop-office-library-in-apache-spark/44806022#44806022 — abaghel, Jun 29 '17 at 09:48

score 1 · Accepted Answer · answered Jun 25 '17 at 11:15

1

Looks like the library you chose, com.crealytics.spark.excel, does not have any code related to writing excel files. Underneath it uses Apache POI for reading Excel files, there are also few examples.

The good news are that CSV is a valid Excel file, and you may use spark-csv to write it. You need to change your code like this:

sourcePropertySet.write
    .format("com.databricks.spark.csv")
    .option("header", "true")
    .save("D:\\resultset.csv");

Keep in mind that Spark makes 1 output file per partition, and you might want to do .repartition(1) to have exactly one result file.

answered Jun 25 '17 at 11:15

Nikolay Vasiliev

5,656
22
31

I have already done writing dataset object to CSV file, but CSV files don't support few formats( eg: if a single cell contains any comma or other special characters it will split that single cell as multiple cells). Hence I need to write to an excel file, is there any approaches are there to do the same using Apache Spark? – BHANUMATHI H M Jun 28 '17 at 06:28
If I understand you right and you need to write a value that contains comma (say, a tuple like this: `(1, "Hello, world!")`), then CSV standard defines escaping rules. Instead of `1, Hello, world!` you should have `1, "Hello, world!"`. CSV libraries are capable to do that, for sure `com.databricks.spark.csv` is capable of that. – Nikolay Vasiliev Jun 28 '17 at 16:16
1

Spark 2.x has in-built CSV support (com.databricks.spark.csv is for Spark 1.x). You can now use format("csv"). Another supported syntax is dataframe.write.option("header", "true").csv("D:\\resultset.csv"). – PJ Fanning Jun 29 '17 at 21:46
.coalesce(1) would be a better approach over .repartition(1) https://stackoverflow.com/questions/31610971/spark-repartition-vs-coalesce – Andrew Quaschnick Jan 04 '18 at 20:27

Jörn Franke · Answer 2 · 2017-08-01T06:07:52.017

The error you face when writing comes from an old version of the HaodoopOffice library. Please make sure that you have only version 1.0.3 or better 1.0.4 as a dependency. Can you provide your build file? The following should work:

 SourcePropertSet.write()
             .format("org.zuinnote.spark.office.excel")
             .option("spark.write.useHeader",true)
             .option("write.locale.bcp47", "us") 
             .save("D:\\result");

Version 1.0.4 of the Spark2 data source for HadoopOffice also supports inferring the schema when reading:

 Dataset<Row> SourcePropertSet = sqlContext.read()
               .format("org.zuinnote.spark.office.excel")
               .option("spark.read.useHeader", "true")
               .option("spark.read.simpleMode", "true")
               .load("D:\\5Kto10K.xlsx");

Please note that it is not recommended to mix different Excel data sources based on POI in one application.

More information here: https://github.com/ZuInnoTe/spark-hadoopoffice-ds

How to write dataset object to excel in spark java?

2 Answers2