Write HSSFWorkbook to hdfs

Question

I need to parse a csv file to xml and write it to hdfs. I managed to do the first part successfully, but get errors when writing. Here's the code.

    private static void writeToXml(String inputPath, String outputPath) throws IOException, JSchException {
        Configuration configuration = new Configuration();
        configuration.set("fs.defaultFS", "hdfs://nn");
        FileSystem fileSystem = FileSystem.get(configuration);
        Path iPath = new Path(inputPath);
        Path oPath = new Path(outputPath);
        FSDataInputStream inputStream = fileSystem.open(iPath);

        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8));

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet");
        AtomicReference<Integer> row = new AtomicReference<>(0);

        try (Stream<String> stream = bufferedReader.lines()) {
            stream.forEach(line -> {
                Row currentRow = sheet.createRow(row.getAndSet(row.get() + 1));
                String[] nextLine = line.split(";");
                Stream.iterate(0, i -> i + 1).limit(nextLine.length).forEach(i -> {
                    currentRow.createCell(i).setCellValue(nextLine[i]);
                });
            });
            FSDataOutputStream outputStream = fileSystem.create(oPath);
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            workbook.write(out);
            outputStream.write(out.toByteArray());
            outputStream.flush();
        }
    }

It fails with this error.

org.apache.oozie.action.hadoop.JavaMainException: java.lang.NoSuchMethodError: org.apache.commons.io.IOUtils.byteArray(I)[B
Caused by: java.lang.NoSuchMethodError: org.apache.commons.io.IOUtils.byteArray(I)[B
    at org.apache.commons.io.output.AbstractByteArrayOutputStream.needNewBuffer(AbstractByteArrayOutputStream.java:104)
    at org.apache.commons.io.output.UnsynchronizedByteArrayOutputStream.<init>(UnsynchronizedByteArrayOutputStream.java:51)
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.syncWithDataSource(POIFSFileSystem.java:779)
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.writeFilesystem(POIFSFileSystem.java:756)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.write(HSSFWorkbook.java:1387)
    at path.to.package.Main.writeToXml(Main.java:81)
    at path.to.package.Main.main(Main.java:24)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.oozie.action.hadoop.JavaMain.run(JavaMain.java:57)
    ... 15 more

At this line.

workbook.write(out);

Edit another snip where I try to write. Fails with the same error.

    FSDataOutputStream outputStream = fileSystem.create(oPath);
    workbook.write(outputStream);
    outputStream.flush();

What am I doing wrong?

@Gagravarr I'm using commons-io version 2.11.0. It's the last version. — gjin, Nov 28 '21 at 15:45
hadoop itself might have a dependency on an old version of commons-io — PJ Fanning, Nov 28 '21 at 19:50
Have you looked at https://github.com/ZuInnoTe/hadoopoffice ? — PJ Fanning, Nov 28 '21 at 19:53
@PJF I'm using maven shade plugin. When I package my jar I see this row `[INFO] Including commons-io:commons-io:jar:2.11.0 in the shaded jar.` I assume? this means the right version is taken. How can I check if I'm wrong? — gjin, Nov 29 '21 at 08:12
the stack trace in the issue description has unshaded package names for poi and commons-io — PJ Fanning, Nov 29 '21 at 08:25

gjin · Accepted Answer · 2021-12-02T08:24:51.423

In the end I couldn't find what's wrong with my dependencies. I rewrote this whole thing in spark using the following dependecies.

<dependencies>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-hive_2.11</artifactId>
        <version>2.4.0</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>com.crealytics</groupId>
        <artifactId>spark-excel_2.11</artifactId>
        <version>0.13.7</version>
    </dependency>
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>3.1.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>4.0.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-api</artifactId>
        <version>2.14.1</version>
    </dependency>
</dependencies>

Note that xmlbeans is version 3.1.0. Later versions did not work for me for some reason.

Also note, I only needed spark-excel_2.11 to test it localy. Others were added because it kept falling with noClassDefFound when running on cluster.

The code also ended up being a lot simplier.

spark
  .read
  .option("delimiter", ";")
  .csv("/hdfs/path/to/file.csv")
  .repartition(1)
  .write
  .format("com.crealytics.spark.excel")
  .option("header", "false")
  .mode("overwrite")
  .save("/hdfs/path/to/file.xml")

Write HSSFWorkbook to hdfs

1 Answers1