[Spring] 엑셀 데이터 검증 ( Excel Data Validation )

DataValidationConstraint 10건 이상 오류시 시트1 첫번째 행에 시트2 참조해서 유효성 검사하는 Java 코드 샘플입니다.

 

gradle dependencies

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.apache.poi:poi:5.0.0'
    implementation 'org.apache.poi:poi-ooxml:5.0.0'
}

 

Java Source

예제

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.*;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.*;

@RestController
public class MainController {


    @GetMapping("/")
    public ResponseEntity<?> getExcel(){

        try (
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            Workbook workbook = new XSSFWorkbook();
        ) {

            int optionSize = 100;
            int ONE_FONT_PX_SIZE = 256;

            String DataSheetName = "Sheet1";
            String validationSheetName = "Sheet2";

            Sheet sheet1 = workbook.createSheet(DataSheetName);
            Sheet sheet2 = workbook.createSheet(validationSheetName);

            DataValidationHelper validationHelper = sheet1.getDataValidationHelper();

            HttpHeaders httpHeaders = new HttpHeaders();

            int lastRow = workbook.getSpreadsheetVersion().getLastRowIndex();

            for (int i = 1; i <= optionSize; i++) {

                Row row = sheet2.createRow(i - 1);
                Cell cell = row.createCell(0);
                cell.setCellValue("Option" + i);
            }

            DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(validationSheetName + "!$A$1:$A$" + String.valueOf(optionSize));

            CellRangeAddressList addressList = new CellRangeAddressList(0, lastRow, 0, 0);

            DataValidation validation = validationHelper.createValidation(constraint, addressList);

            sheet1.addValidationData(validation);

            sheet1.setColumnWidth(0, 20 * ONE_FONT_PX_SIZE);

            workbook.write(bos);

            httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            httpHeaders.setContentDisposition(ContentDisposition.builder("attatchment").filename("example.xlsx").build());

            return ResponseEntity.ok()
                    .headers(httpHeaders)
                    .body(
                            new InputStreamResource(
                                    new ByteArrayInputStream(bos.toByteArray())
                            )
                    );

        } catch (Exception e) {
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(e.getMessage());
        }

    }

}