diff --git a/apache-poi-3/pom.xml b/apache-poi-3/pom.xml index 53bccaf86aa9..675cff2fb10a 100644 --- a/apache-poi-3/pom.xml +++ b/apache-poi-3/pom.xml @@ -14,6 +14,11 @@ + + org.springframework.boot + spring-boot-starter-web + ${spring.web.version} + org.apache.poi poi-ooxml @@ -108,6 +113,7 @@ 1.5.6 1.37 2.23.1 + 3.5.7 - \ No newline at end of file + diff --git a/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/DemoApplication.java b/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/DemoApplication.java new file mode 100644 index 000000000000..63195983fe0f --- /dev/null +++ b/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/DemoApplication.java @@ -0,0 +1,16 @@ +package com.baeldung.hssfworkbook; + +import java.io.IOException; + +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.springframework.boot.SpringApplication; +import org.springframework.boot.autoconfigure.SpringBootApplication; + +@SpringBootApplication +public class DemoApplication { + + public static void main(String[] args) throws IOException { + SpringApplication.run(DemoApplication.class, args); + } + +} diff --git a/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/ExcelController.java b/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/ExcelController.java new file mode 100644 index 000000000000..59dcdc4f577d --- /dev/null +++ b/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/ExcelController.java @@ -0,0 +1,64 @@ +package com.baeldung.hssfworkbook; + +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.Sheet; +import org.springframework.http.HttpHeaders; +import org.springframework.http.MediaType; +import org.springframework.http.ResponseEntity; +import org.springframework.web.bind.annotation.GetMapping; +import org.springframework.web.bind.annotation.PostMapping; +import org.springframework.web.bind.annotation.RequestParam; +import org.springframework.web.bind.annotation.RestController; +import org.springframework.web.multipart.MultipartFile; + +import java.io.ByteArrayOutputStream; +import java.io.IOException; + +@RestController +public class ExcelController { + + @GetMapping("/download-excel") + public ResponseEntity downloadExcel() { + try { + HSSFWorkbook workbook = ExcelCreator.createSampleWorkbook(); + + try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) { + workbook.write(baos); + byte[] bytes = baos.toByteArray(); + + return ResponseEntity.ok() + .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=employee_data.xls") + .contentType(MediaType.parseMediaType("application/vnd.ms-excel")) // More specific MIME type + .body(bytes); + } + } catch (IOException e) { + System.err.println("Error generating or writing Excel workbook: " + e.getMessage()); + return ResponseEntity.internalServerError() + .build(); + } + } + + @PostMapping("/upload-excel") + public ResponseEntity uploadExcel(@RequestParam("file") MultipartFile file) { + if (file.isEmpty()) { + return ResponseEntity.badRequest() + .body("File is empty. Please upload a file."); + } + + try { + try (HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream())) { + Sheet sheet = workbook.getSheetAt(0); + + return ResponseEntity.ok("Sheet '" + sheet.getSheetName() + "' uploaded successfully!"); + } + } catch (IOException e) { + System.err.println("Error processing uploaded Excel file: " + e.getMessage()); + return ResponseEntity.internalServerError() + .body("Failed to process the Excel file."); + } catch (Exception e) { + System.err.println("An unexpected error occurred during file upload: " + e.getMessage()); + return ResponseEntity.internalServerError() + .body("An unexpected error occurred."); + } + } +} \ No newline at end of file diff --git a/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/ExcelConverter.java b/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/ExcelConverter.java new file mode 100644 index 000000000000..39db5bd2130d --- /dev/null +++ b/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/ExcelConverter.java @@ -0,0 +1,23 @@ +package com.baeldung.hssfworkbook; + +import java.io.ByteArrayInputStream; +import java.io.ByteArrayOutputStream; +import java.io.IOException; + +import org.apache.poi.hssf.usermodel.HSSFWorkbook; + +public class ExcelConverter { + + public static byte[] convertWorkbookToBytes(HSSFWorkbook workbook) throws IOException { + try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) { + workbook.write(baos); + return baos.toByteArray(); + } + } + + public static HSSFWorkbook convertBytesToWorkbook(byte[] excelBytes) throws IOException { + try (ByteArrayInputStream bais = new ByteArrayInputStream(excelBytes)) { + return new HSSFWorkbook(bais); + } + } +} diff --git a/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/ExcelCreator.java b/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/ExcelCreator.java new file mode 100644 index 000000000000..07c5dfea62cf --- /dev/null +++ b/apache-poi-3/src/main/java/com/baeldung/hssfworkbook/ExcelCreator.java @@ -0,0 +1,56 @@ +package com.baeldung.hssfworkbook; + +import org.apache.poi.hssf.usermodel.HSSFCellStyle; +import org.apache.poi.hssf.usermodel.HSSFFont; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; + +public class ExcelCreator { + + public static HSSFWorkbook createSampleWorkbook() { + HSSFWorkbook workbook = new HSSFWorkbook(); + + final String SHEET_NAME = "Employees"; + final String[] COLUMN_HEADERS = { "ID", "Name", "Department" }; + Object[][] data = { { 101, "John Doe", "Finance" }, { 102, "Jane Smith", "HR" }, { 103, "Michael Clark", "IT" } }; + + Sheet sheet = workbook.createSheet(SHEET_NAME); + + HSSFFont font = workbook.createFont(); + font.setBold(true); + HSSFCellStyle headerStyle = workbook.createCellStyle(); + headerStyle.setFont(font); + + Row header = sheet.createRow(0); + for (int i = 0; i < COLUMN_HEADERS.length; i++) { + Cell cell = header.createCell(i); + cell.setCellValue(COLUMN_HEADERS[i]); + cell.setCellStyle(headerStyle); + } + + int rowNum = 1; + for (Object[] rowData : data) { + Row row = sheet.createRow(rowNum++); + for (int i = 0; i < rowData.length; i++) { + Cell cell = row.createCell(i); + Object value = rowData[i]; + + if (value instanceof Integer) { + cell.setCellValue(((Integer) value).doubleValue()); + } else if (value instanceof Double) { + cell.setCellValue((Double) value); + } else if (value != null) { + cell.setCellValue(value.toString()); + } + } + } + + for (int i = 0; i < COLUMN_HEADERS.length; i++) { + sheet.autoSizeColumn(i); + } + + return workbook; + } +} diff --git a/apache-poi-3/src/test/java/com/baeldung/hssfworkbook/ExcelConverterUnitTest.java b/apache-poi-3/src/test/java/com/baeldung/hssfworkbook/ExcelConverterUnitTest.java new file mode 100644 index 000000000000..31240663f250 --- /dev/null +++ b/apache-poi-3/src/test/java/com/baeldung/hssfworkbook/ExcelConverterUnitTest.java @@ -0,0 +1,86 @@ +package com.baeldung.hssfworkbook; + +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.Sheet; +import org.junit.jupiter.api.DisplayName; +import org.junit.jupiter.api.Test; + +import java.io.ByteArrayOutputStream; +import java.io.IOException; +import java.io.UncheckedIOException; + +import com.baeldung.hssfworkbook.ExcelConverter; + +import static org.junit.jupiter.api.Assertions.assertArrayEquals; +import static org.junit.jupiter.api.Assertions.assertNotNull; +import static org.junit.jupiter.api.Assertions.assertThrows; +import static org.junit.jupiter.api.Assertions.assertTrue; +import static org.junit.jupiter.api.Assertions.assertEquals; + +class ExcelConverterTest { + + private HSSFWorkbook createMinimalWorkbook() { + HSSFWorkbook workbook = new HSSFWorkbook(); + Sheet sheet = workbook.createSheet("TestSheet"); + sheet.createRow(0) + .createCell(0) + .setCellValue("Test Data"); + return workbook; + } + + private byte[] convertWorkbookToBytesSafely(HSSFWorkbook workbook) { + try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) { + workbook.write(baos); + return baos.toByteArray(); + } catch (IOException e) { + throw new UncheckedIOException("Failed to create test bytes.", e); + } + } + + @Test + @DisplayName("GivenAValidHSSFWorkbook_whenConvertingToBytes_thenByteArrayIsNonEmptyAndValid") + void givenAValidHSSFWorkbook_whenConvertingToBytes_thenByteArrayIsNonEmptyAndValid() throws IOException { + HSSFWorkbook workbook = createMinimalWorkbook(); + + byte[] resultBytes = ExcelConverter.convertWorkbookToBytes(workbook); + + assertNotNull(resultBytes, "The byte array should not be null."); + assertTrue(resultBytes.length > 0, "The byte array should not be empty."); + + HSSFWorkbook convertedWorkbook = ExcelConverter.convertBytesToWorkbook(resultBytes); + assertEquals("TestSheet", convertedWorkbook.getSheetName(0)); + } + + @Test + @DisplayName("GivenAValidExcelByteArray_whenConvertingToWorkbook_thenHSSFWorkbookIsReturned") + void givenAValidExcelByteArray_whenConvertingToWorkbook_thenHSSFWorkbookIsReturned() throws IOException { + HSSFWorkbook originalWorkbook = createMinimalWorkbook(); + byte[] validExcelBytes = convertWorkbookToBytesSafely(originalWorkbook); + + HSSFWorkbook resultWorkbook = ExcelConverter.convertBytesToWorkbook(validExcelBytes); + + assertNotNull(resultWorkbook, "The resulting workbook should not be null."); + assertEquals(1, resultWorkbook.getNumberOfSheets(), "The resulting workbook should have 1 sheet."); + assertEquals("TestSheet", resultWorkbook.getSheetName(0), "Sheet name should match the original."); + assertEquals("Test Data", resultWorkbook.getSheetAt(0) + .getRow(0) + .getCell(0) + .getStringCellValue()); + } + + @Test + @DisplayName("GivenAnEmptyByteArray_whenConvertingToWorkbook_thenIOExceptionIsThrown") + void givenAnEmptyByteArray_whenConvertingToWorkbook_thenIOExceptionIsThrown() { + byte[] emptyBytes = new byte[0]; + + assertThrows(IOException.class, () -> ExcelConverter.convertBytesToWorkbook(emptyBytes), "Expected IOException for empty byte array."); + } + + @Test + @DisplayName("GivenAnInvalidByteArray_whenConvertingToWorkbook_thenIOExceptionIsThrown") + void givenAnInvalidByteArray_whenConvertingToWorkbook_thenIOExceptionIsThrown() { + byte[] invalidBytes = { 0x01, 0x02, 0x03, 0x04, 0x05 }; + + assertThrows(IOException.class, () -> ExcelConverter.convertBytesToWorkbook(invalidBytes), "Expected IOException for invalid byte array format."); + } +} \ No newline at end of file diff --git a/apache-poi-3/src/test/java/com/baeldung/hssfworkbook/ExcelCreatorUnitTest.java b/apache-poi-3/src/test/java/com/baeldung/hssfworkbook/ExcelCreatorUnitTest.java new file mode 100644 index 000000000000..ceadb2d3b296 --- /dev/null +++ b/apache-poi-3/src/test/java/com/baeldung/hssfworkbook/ExcelCreatorUnitTest.java @@ -0,0 +1,94 @@ +package com.baeldung.hssfworkbook; + +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellStyle; +import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.ss.usermodel.Font; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.junit.jupiter.api.DisplayName; +import org.junit.jupiter.api.Test; + +import com.baeldung.hssfworkbook.ExcelCreator; + +import static org.junit.jupiter.api.Assertions.assertEquals; +import static org.junit.jupiter.api.Assertions.assertNotNull; +import static org.junit.jupiter.api.Assertions.assertTrue; + +class ExcelCreatorTest { + + private static final String SHEET_NAME = "Employees"; + private static final String[] COLUMN_HEADERS = { "ID", "Name", "Department" }; + private static final Object[][] DATA = { { 101, "John Doe", "Finance" }, { 102, "Jane Smith", "HR" }, { 103, "Michael Clark", "IT" } }; + + @Test + @DisplayName("Workbook should be created and contain one sheet named 'Employees'") + void givenExcelCreator_whenCreateSampleWorkbookCalled_thenWorkbookContainsOneSheetNamedEmployees() { + HSSFWorkbook workbook = ExcelCreator.createSampleWorkbook(); + + assertNotNull(workbook, "The workbook should not be null."); + assertEquals(1, workbook.getNumberOfSheets(), "The workbook should contain exactly one sheet."); + + Sheet sheet = workbook.getSheet(SHEET_NAME); + assertNotNull(sheet, "The sheet named '" + SHEET_NAME + "' must exist."); + } + + @Test + @DisplayName("Header row should have correct content and bold style") + void givenExcelCreator_whenCreateSampleWorkbookCalled_thenHeaderRowContainsCorrectTextAndBoldStyle() { + HSSFWorkbook workbook = ExcelCreator.createSampleWorkbook(); + Sheet sheet = workbook.getSheet(SHEET_NAME); + + Row headerRow = sheet.getRow(0); + assertNotNull(headerRow, "Header row (row 0) should exist."); + + Font expectedFont = workbook.getFontAt(headerRow.getCell(0) + .getCellStyle() + .getFontIndex()); + assertTrue(expectedFont.getBold(), "Header font should be bold."); + + for (int i = 0; i < COLUMN_HEADERS.length; i++) { + Cell cell = headerRow.getCell(i); + assertNotNull(cell, "Header cell at index " + i + " should exist."); + assertEquals(COLUMN_HEADERS[i], cell.getStringCellValue(), "Header text mismatch at column " + i + "."); + + CellStyle cellStyle = cell.getCellStyle(); + assertNotNull(cellStyle, "Header cell should have a style applied."); + + Font actualFont = workbook.getFontAt(cellStyle.getFontIndex()); + assertTrue(actualFont.getBold(), "Header cell at index " + i + " style must be bold."); + } + } + + @Test + @DisplayName("Data rows should have correct content and data type handling") + void givenExcelCreator_whenCreateSampleWorkbookCalled_thenDataRowsContainCorrectContentAndTypes() { + HSSFWorkbook workbook = ExcelCreator.createSampleWorkbook(); + Sheet sheet = workbook.getSheet(SHEET_NAME); + + assertEquals(DATA.length + 1, sheet.getLastRowNum() + 1, "The sheet should contain header row + data rows."); + + for (int i = 0; i < DATA.length; i++) { + Row dataRow = sheet.getRow(i + 1); + Object[] expectedRowData = DATA[i]; + + assertNotNull(dataRow, "Data row at index " + (i + 1) + " should exist."); + + for (int j = 0; j < expectedRowData.length; j++) { + Cell cell = dataRow.getCell(j); + Object expectedValue = expectedRowData[j]; + + assertNotNull(cell, "Cell [" + (i + 1) + ", " + j + "] should exist."); + + if (expectedValue instanceof Integer) { + assertEquals(CellType.NUMERIC, cell.getCellType(), "Cell " + j + " type should be NUMERIC for Integer."); + assertEquals(((Integer) expectedValue).doubleValue(), cell.getNumericCellValue(), 0.001, "Numeric value mismatch."); + } else { + assertEquals(CellType.STRING, cell.getCellType(), "Cell " + j + " type should be STRING."); + assertEquals(expectedValue.toString(), cell.getStringCellValue(), "String value mismatch."); + } + } + } + } +} \ No newline at end of file