Repository: jtablesaw/tablesaw
Pull request #1207 (2023-04-30 21:19:08)

Excel writer
Thanks for contributing.

- [x ] Tick to sign-off your agreement to the [Developer Certificate of Origin (DCO) 1.1](https://developercertificate.org)

## Description

Add the capability to export (write) a Table to an Excel (xlsx) file.

## Testing
See tech.tablesaw.io.xlsx.XlsxWriterTest



Patch:
diff --git a/excel/src/main/java/tech/tablesaw/io/xlsx/XlsxWriteOptions.java b/excel/src/main/java/tech/tablesaw/io/xlsx/XlsxWriteOptions.java
new file mode 100644
index 00000000..bf667502
--- /dev/null
+++ b/excel/src/main/java/tech/tablesaw/io/xlsx/XlsxWriteOptions.java
@@ -0,0 +1,57 @@
+package tech.tablesaw.io.xlsx;
+
+import java.io.File;
+import java.io.OutputStream;
+import java.io.Writer;
+import tech.tablesaw.io.Destination;
+import tech.tablesaw.io.WriteOptions;
+
+public class XlsxWriteOptions extends WriteOptions {
+
+  protected XlsxWriteOptions(Builder builder) {
+    super(builder);
+  }
+
+  public static Builder builder(Destination dest) {
+    return new Builder(dest);
+  }
+
+  public static Builder builder(OutputStream dest) {
+    return new Builder(dest);
+  }
+
+  public static Builder builder(Writer dest) {
+    return new Builder(dest);
+  }
+
+  public static Builder builder(File dest) {
+    return new Builder(dest);
+  }
+
+  public static Builder builder(String fileName) {
+    return builder(new File(fileName));
+  }
+
+  public static class Builder extends WriteOptions.Builder {
+
+    protected Builder(Destination dest) {
+      super(dest);
+    }
+
+    protected Builder(OutputStream dest) {
+      super(dest);
+    }
+
+    protected Builder(Writer dest) {
+      super(dest);
+    }
+
+    protected Builder(File dest) {
+      super(dest);
+    }
+
+    public XlsxWriteOptions build() {
+      return new XlsxWriteOptions(this);
+    }
+  }
+}
diff --git a/excel/src/main/java/tech/tablesaw/io/xlsx/XlsxWriter.java b/excel/src/main/java/tech/tablesaw/io/xlsx/XlsxWriter.java
new file mode 100644
index 00000000..330dded2
--- /dev/null
+++ b/excel/src/main/java/tech/tablesaw/io/xlsx/XlsxWriter.java
@@ -0,0 +1,105 @@
+package tech.tablesaw.io.xlsx;
+
+import java.io.IOException;
+import java.io.OutputStream;
+import java.time.ZoneId;
+import java.time.ZonedDateTime;
+import java.util.GregorianCalendar;
+import java.util.List;
+import org.apache.poi.ss.usermodel.CellStyle;
+import org.apache.poi.ss.usermodel.DateUtil;
+import org.apache.poi.xssf.usermodel.XSSFSheet;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import tech.tablesaw.api.ColumnType;
+import tech.tablesaw.api.Table;
+import tech.tablesaw.io.DataWriter;
+import tech.tablesaw.io.Destination;
+import tech.tablesaw.io.RuntimeIOException;
+import tech.tablesaw.io.WriterRegistry;
+
+public class XlsxWriter implements DataWriter<XlsxWriteOptions> {
+
+  private static final XlsxWriter INSTANCE = new XlsxWriter();
+
+  static {
+    register(Table.defaultWriterRegistry);
+  }
+
+  public static void register(WriterRegistry registry) {
+    registry.registerExtension("xlsx", INSTANCE);
+    registry.registerOptions(XlsxWriteOptions.class, INSTANCE);
+  }
+
+  @Override
+  public void write(Table table, Destination dest) {
+    write(table, XlsxWriteOptions.builder(dest).build());
+  }
+
+  @Override
+  public void write(Table table, XlsxWriteOptions options) {
+    try (XSSFWorkbook workbook = new XSSFWorkbook()) {
+      CellStyle localDateStyle = workbook.createCellStyle();
+      localDateStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd"));
+      CellStyle localDateTimeStyle = workbook.createCellStyle();
+      localDateTimeStyle.setDataFormat(
+          workbook.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss"));
+      CellStyle localTimeStyle = workbook.createCellStyle();
+      localTimeStyle.setDataFormat(workbook.createDataFormat().getFormat("[h]:mm:ss"));
+      XSSFSheet sheet = workbook.createSheet(table.name());
+      int rowNum = 0;
+      List<String> columnNames = table.columnNames();
+
+      var headerRow = sheet.createRow(rowNum++);
+      int colNum = 0;
+      for (String colName : columnNames) {
+        var cell = headerRow.createCell(colNum++);
+        cell.setCellValue(colName);
+      }
+
+      for (var row : table) {
+        var excelRow = sheet.createRow(rowNum++);
+        colNum = 0;
+        for (String colName : columnNames) {
+          var cell = excelRow.createCell(colNum++);
+          var type = row.getColumnType(colName);
+
+          if (ColumnType.STRING.equals(type)) {
+            cell.setCellValue(row.getString(colName));
+          } else if (ColumnType.LOCAL_DATE.equals(type)) {
+            cell.setCellValue(row.getDate(colName));
+            cell.setCellStyle(localDateStyle);
+          } else if (ColumnType.LOCAL_DATE_TIME.equals(type)) {
+            cell.setCellValue(row.getDate(colName));
+            cell.setCellStyle(localDateTimeStyle);
+          } else if (ColumnType.LOCAL_TIME.equals(type)) {
+            double time = DateUtil.convertTime(row.getTime(colName).toString());
+            cell.setCellValue(time);
+            cell.setCellStyle(localTimeStyle);
+          } else if (ColumnType.INSTANT.equals(type)) {
+            ZonedDateTime zdt =
+                ZonedDateTime.ofInstant(row.getInstant(colName), ZoneId.systemDefault());
+            cell.setCellValue(GregorianCalendar.from(zdt));
+            cell.setCellStyle(localDateTimeStyle);
+          } else if (ColumnType.FLOAT.equals(type)) {
+            cell.setCellValue(row.getFloat(colName));
+          } else if (ColumnType.INTEGER.equals(type)) {
+            cell.setCellValue(row.getInt(colName));
+          } else if (ColumnType.DOUBLE.equals(type)) {
+            cell.setCellValue(row.getDouble(colName));
+          } else if (ColumnType.BOOLEAN.equals(type)) {
+            cell.setCellValue(row.getBoolean(colName));
+          } else {
+            cell.setCellValue(String.valueOf(row.getObject(colName)));
+          }
+        }
+      }
+
+      try (OutputStream os = options.destination().stream()) {
+        workbook.write(os);
+      }
+
+    } catch (IOException e) {
+      throw new RuntimeIOException(e);
+    }
+  }
+}


Test patch:
diff --git a/excel/src/test/java/tech/tablesaw/io/xlsx/XlsxWriterTest.java b/excel/src/test/java/tech/tablesaw/io/xlsx/XlsxWriterTest.java
new file mode 100644
index 00000000..95ca71ad
--- /dev/null
+++ b/excel/src/test/java/tech/tablesaw/io/xlsx/XlsxWriterTest.java
@@ -0,0 +1,52 @@
+package tech.tablesaw.io.xlsx;
+
+import static org.junit.jupiter.api.Assertions.assertArrayEquals;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+import java.io.File;
+import java.io.IOException;
+import java.time.LocalDate;
+import org.junit.jupiter.api.Test;
+import tech.tablesaw.api.*;
+
+public class XlsxWriterTest {
+
+  @Test
+  public void testXlsxWriter() throws IOException {
+    Table table =
+        Table.create("Employees")
+            .addColumns(
+                IntColumn.create("id", 1, 2, 3),
+                DateColumn.create(
+                    "start_date", toLocalDates("2020-01-10", "2021-12-01", "2023-04-20")),
+                DoubleColumn.create("salary", 6723.5, 4879, 5512.8));
+    File file = File.createTempFile(table.name(), ".xlsx");
+    table.write().usingOptions(XlsxWriteOptions.builder(file).build());
+
+    Table table2 =
+        Table.read()
+            .usingOptions(
+                XlsxReadOptions.builder(file)
+                    .columnTypes(
+                        new ColumnType[] {ColumnType.INTEGER, ColumnType.STRING, ColumnType.DOUBLE})
+                    .build());
+    assertEquals(3, table2.rowCount(), "Number of rows");
+    assertEquals(3, table2.columnCount(), "Number of columns");
+    assertArrayEquals(
+        new String[] {"id", "start_date", "salary"},
+        table2.columnNames().toArray(new String[] {}),
+        "Column names");
+    assertEquals(1, table2.get(0, 0), "First row. first column");
+    assertEquals("2021-12-01", table2.get(1, 1), "Second row, second column");
+    assertEquals(5512.8, table2.get(2, 2), "Third row, third column");
+    file.deleteOnExit();
+  }
+
+  LocalDate[] toLocalDates(String... dates) {
+    LocalDate[] d = new LocalDate[dates.length];
+    for (int i = 0; i < dates.length; i++) {
+      d[i] = LocalDate.parse(dates[i]);
+    }
+    return d;
+  }
+}