eugenp · eric-martin · Sep 30, 2024 · Sep 10, 2024
diff --git a/apache-poi-3/pom.xml b/apache-poi-3/pom.xml
@@ -39,6 +39,11 @@
             <artifactId>poi-ooxml-schemas</artifactId>
             <version>${poi-ooxml-schemas.version}</version>
         </dependency>
+        <dependency>
+            <groupId>com.h2database</groupId>
+            <artifactId>h2</artifactId>
+            <version>${h2.version}</version>
+        </dependency>
         <dependency>
             <groupId>org.apache.xmlbeans</groupId>
             <artifactId>xmlbeans</artifactId>
@@ -82,7 +87,8 @@
     </dependencies>
 
     <properties>
-        <poi.version>5.2.5</poi.version>
+        <h2.version>2.3.232</h2.version>
+        <poi.version>5.3.0</poi.version>
         <poi-ooxml-schemas.version>4.1.2</poi-ooxml-schemas.version>
         <poiji.version>4.2.0</poiji.version>
         <xmlbeans.version>5.2.0</xmlbeans.version>

diff --git a/apache-poi-3/src/test/java/com/baeldung/resultset/DataPreparer.java b/apache-poi-3/src/test/java/com/baeldung/resultset/DataPreparer.java
@@ -0,0 +1,56 @@
+package com.baeldung.resultset;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+public class DataPreparer {
+
+    private static final String SELECT_SQL = "SELECT id, name, category, price FROM products ";
+
+    private Connection connection = null;
+
+    public DataPreparer(Connection connection) {
+        this.connection = connection;
+    }
+
+    private void prepareTable() throws SQLException {
+        String createSql = "CREATE TABLE products (" +
+                "id INT AUTO_INCREMENT PRIMARY KEY, " +
+                "name VARCHAR(255) NOT NULL, " +
+                "category VARCHAR(255), " +
+                "price DECIMAL(10, 2) )";
+
+        try (Statement statement = connection.createStatement()) {
+            statement.execute(createSql);
+        }
+    }
+
+    private void prepareData() throws SQLException {
+        String insertSql = "INSERT INTO products(name, category, price) " +
+                "VALUES ('%s', '%s', %s) ";
+
+        try (Statement statement = connection.createStatement()) {
+            statement.executeUpdate(String.format(insertSql, "Chocolate", "Confectionery", "2.99"));
+            statement.executeUpdate(String.format(insertSql, "Fruit Jellies", "Confectionery", "1.5"));
+            statement.executeUpdate(String.format(insertSql, "Crisps", "Snacks", "1.69"));
+            statement.executeUpdate(String.format(insertSql, "Walnuts", "Snacks", "5.95"));
+            statement.executeUpdate(String.format(insertSql, "Orange Juice", "Juices", "2.19"));
+        }
+    }
+
+    public void prepare() {
+        try {
+            prepareTable();
+            prepareData();
+        }
+        catch (SQLException se) {
+            se.printStackTrace();
+        }
+    }
+
+    public String getSelectSql() {
+        return SELECT_SQL;
+    }
+
+}
diff --git a/apache-poi-3/src/test/java/com/baeldung/resultset/ResultSet2Workbook.java b/apache-poi-3/src/test/java/com/baeldung/resultset/ResultSet2Workbook.java
@@ -0,0 +1,52 @@
+package com.baeldung.resultset;
+
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+
+import java.io.IOException;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+
+public class ResultSet2Workbook {
+
+    private final ResultSet resultSet;
+
+    private final ResultSetMetaData resultSetMetaData;
+
+    private int numOfColumns = 0;
+
+    public ResultSet2Workbook(ResultSet rs) throws SQLException {
+        this.resultSet = rs;
+        this.resultSetMetaData = rs.getMetaData();
+        this.numOfColumns = resultSetMetaData.getColumnCount();
+    }
+
+    private void createHeaderRow(Sheet sheet) throws SQLException {
+        Row row = sheet.createRow(sheet.getLastRowNum()+1);
+        for (int n=0; n<numOfColumns; n++) {
+            String label = resultSetMetaData.getColumnLabel(n+1);
+            Cell cell = row.createCell(n);
+            cell.setCellValue(label);
+        }
+    }
+
+    private void createDataRows(Sheet sheet) throws SQLException {
+        while (resultSet.next()) {
+            Row row = sheet.createRow(sheet.getLastRowNum()+1);
+            for (int n=0; n<numOfColumns; n++) {
+                Cell cell = row.createCell(n);
+                cell.setCellValue(resultSet.getString(n+1));
+            }
+        }
+    }
+
+    public void write(Workbook workbook) throws IOException, SQLException {
+        Sheet sheet = workbook.createSheet("data");
+        createHeaderRow(sheet);
+        createDataRows(sheet);
+    }
+
+}
diff --git a/apache-poi-3/src/test/java/com/baeldung/resultset/ResultSetExcelUnitTest.java b/apache-poi-3/src/test/java/com/baeldung/resultset/ResultSetExcelUnitTest.java
@@ -0,0 +1,77 @@
+package com.baeldung.resultset;
+
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Test;
+
+import java.io.*;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+class ResultSetExcelUnitTest {
+
+    private static final String JDBC_CONNECTION_URL = "jdbc:h2:mem:testdb";
+    private static final String JDBC_USERNAME = "sa";
+    private static final String JDBC_PASSWORD = "";
+
+    private static DataPreparer dataPreparer;
+    private static Connection connection;
+
+
+    private static Connection getConnection() throws SQLException {
+        return DriverManager.getConnection(JDBC_CONNECTION_URL, JDBC_USERNAME, JDBC_PASSWORD);
+    }
+
+    @BeforeAll
+    static void setup() throws SQLException {
+        connection = getConnection();
+        dataPreparer = new DataPreparer(connection);
+        dataPreparer.prepare();
+    }
+
+    @Test
+    void whenExportDataToExcel_thenFileContainsCorrectData() throws IOException, SQLException {
+        Workbook workbook = new XSSFWorkbook();
+
+        try (Connection connection = getConnection();
+             Statement statement = connection.createStatement();
+             ResultSet rs = statement.executeQuery(dataPreparer.getSelectSql());) {
+
+            ResultSet2Workbook conversion = new ResultSet2Workbook(rs);
+            conversion.write(workbook);
+        }
+
+        // Write the workbook to a file
+        File excelFile = File.createTempFile("test", ".xlsx");
+        try (OutputStream outputStream = new BufferedOutputStream(new FileOutputStream(excelFile))) {
+            workbook.write(outputStream);
+            workbook.close();
+        }
+
+        assertExcelFileContent(excelFile);
+    }
+
+    private void assertExcelFileContent(File excelFile) throws IOException {
+        try (InputStream inputStream = new FileInputStream(excelFile);
+             Workbook readWorkbook = new XSSFWorkbook(inputStream)) {
+            Sheet sheet = readWorkbook.getSheetAt(0);
+            assertThat(sheet.getPhysicalNumberOfRows()).isEqualTo(6);
+        }
+    }
+
+    @AfterAll
+    static void tearDown() throws SQLException {
+        if (connection != null && !connection.isClosed()) {
+            connection.close();
+        }
+    }
+
+}